轉(zhuǎn)自：https://blog.csdn.net/tomorrow_fine/article/details/78337735

1.MySQL在創(chuàng)建數(shù)據(jù)表的時(shí)候創(chuàng)建索引

在MySQL中創(chuàng)建表的時(shí)候，可以直接創(chuàng)建索引。基本的語法格式如下：

CREATE TABLE 表名(字段名 數(shù)據(jù)類型 [完整性約束條件],[UNIQUE | FULLTEXT | SPATIAL] INDEX | KEY[索引名](字段名1 [(長(zhǎng)度)] [ASC | DESC]) );

UNIQUE： 可選。表示索引為唯一性索引。
FULLTEXT； 可選。表示索引為全文索引。
SPATIAL： 可選。表示索引為空間索引。
INDEX和KEY： 用于指定字段為索引，兩者選擇其中之一就可以了，作用是一樣的。
索引名： 可選。給創(chuàng)建的索引取一個(gè)新名稱。
字段名1： 指定索引對(duì)應(yīng)的字段的名稱，該字段必須是前面定義好的字段。
長(zhǎng)度： 可選。指索引的長(zhǎng)度，必須是字符串類型才可以使用。
ASC： 可選。表示升序排列。
DESC： 可選。表示降序排列。

1.1 MySQL創(chuàng)建普通索引

創(chuàng)建一個(gè)普通索引時(shí)，不需要加任何UNIQUE、FULLTEXT或者SPATIAL參數(shù)。

實(shí)例：創(chuàng)建一個(gè)名為index1的數(shù)據(jù)表，在表內(nèi)的id字段上建立一個(gè)普通索引。

1. 創(chuàng)建普通索引的SQL代碼如下：

CREATE TABLE index1(id INT,name VARCHAR(20),sex BOOLEAN,INDEX(id) );

查看MySQL創(chuàng)建普通索引的操作效果。如下圖所示：

從上圖中可以看出，運(yùn)行結(jié)果顯示普通索引創(chuàng)建成功。

2. 使用SHOW CREATE TABLE語句查看表的結(jié)構(gòu)。如下圖所示：
或者通過1步驟，可以看出，在id字段上已經(jīng)建立了一個(gè)名為id的普通索引。語句：

KEY `id` (`id`)

圓括號(hào)內(nèi)的id是字段名稱，圓括號(hào)左側(cè)外面的id是索引名稱。

3. 使用EXPLAIN語句查看索引是否被使用。SQL代碼如下：

EXPLAIN SELECT * FROM index1 WHERE id = 1;

使用EXPLAIN語句查看索引是否被使用的操作效果。如下圖所示：

上圖中的結(jié)果顯示，possible_keys和key的值都為id。說明id索引已經(jīng)存在，并且查詢時(shí)已經(jīng)使用了索引。

1.2 MySQL創(chuàng)建唯一性索引

如果使用UNIQUE參數(shù)進(jìn)行約束，則可以創(chuàng)建唯一性索引。

實(shí)例：創(chuàng)建一個(gè)名為index2的數(shù)據(jù)表，在表內(nèi)的id字段上建立一個(gè)唯一性索引，并且設(shè)置id字段以升序的形式排列。
1. 創(chuàng)建一個(gè)唯一性索引的SQL代碼如下：

CREATE TABLE index2( id INT UNIQUE, NAME VARCHAR(20), UNIQUE INDEX index2_id(id ASC) );

index2_id是為唯一性索引起的一個(gè)新名字。

查看MySQL創(chuàng)建唯一性索引的操作效果。如下圖所示：

從上圖中可以看出，運(yùn)行結(jié)果顯示創(chuàng)建成功。

2. 使用SHOW CREATE TABLE語句查看表的結(jié)構(gòu)。SQL代碼如下：

SHOW CREATE TABLE index2 \G
在DOS提示符窗口中查看使用SHOW CREATE TABLE語句查看表的結(jié)構(gòu)的效果。也可以通過上面的1步驟看到，在id字段上建立了名為id和index2_id的兩個(gè)唯一性索引。這樣做，可以提高數(shù)據(jù)的查詢速度。

如果在創(chuàng)建index2表時(shí)，id字段沒有進(jìn)行唯一性結(jié)束。如下所示：

CREATE TABLE index2( id INT, name VARCHAR(20), UNIQUE INDEX index2_id(id ASC) );

則也可以在id字段上成功創(chuàng)建名為index2_id的唯一性索引。但是，這樣可能達(dá)不到提高查詢速度的目的。

1.3 MySQL創(chuàng)建全文索引

全文索引使用FULLTEXT參數(shù)，并且只能在CHAR、VARCHAR或TEXT類型的字段上創(chuàng)建。
全文索引可以用于全文搜索。
現(xiàn)在，MyISAM存儲(chǔ)引擎和InnoDB存儲(chǔ)引擎都支持全文索引。
實(shí)例：創(chuàng)建一個(gè)名為index3的數(shù)據(jù)表，在表中的info字段上建立名為index3_info的全文索引。

1. 創(chuàng)建全文索引的SQL代碼如下：

CREATE TABLE index3(id INT, info VARCHAR(20), FULLTEXT INDEX index3_info(info) )ENGINE=MyISAM;

如果設(shè)置ENGINE=InnoDB，則可以在InnoDB存儲(chǔ)引擎上創(chuàng)建全文索引。

查看MySQL創(chuàng)建全文索引的操作效果。如下圖所示：

從上圖中可以看出，代碼的執(zhí)行結(jié)果顯示創(chuàng)建成功。

2. 使用SHOW CREATE TABLE語句查看index3數(shù)據(jù)表的結(jié)構(gòu)。如下圖所示：
從上圖中可以看出，在info字段上已經(jīng)建立了一個(gè)名為index3_info的全文索引。
注意
我使用的是MySQL 5.6.19版本，已經(jīng)可以在InnoDB存儲(chǔ)引擎中創(chuàng)建全文索引了。

全文索引非常適合于大型數(shù)據(jù)集，對(duì)于小的數(shù)據(jù)集，它的用處可能比較小。

1.4 MySQL創(chuàng)建單列索引

單列索引是在數(shù)據(jù)表的單個(gè)字段上創(chuàng)建的索引。一個(gè)表中可以創(chuàng)建多個(gè)單列索引。唯一性索引和普通索引等都為單列索引。

實(shí)例：創(chuàng)建一個(gè)名為index4的數(shù)據(jù)表，在表中的subject字段上建立名為index4_st的單列索引。

1. 創(chuàng)建單列索引的SQL代碼如下：

CREATE TABLE index4( id INT, subject VARCHAR(30), INDEX index4_st(subject(10)) );

查看MySQL創(chuàng)建單列索引的操作效果。如下圖所示：

從上圖中可以看出，代碼執(zhí)行的結(jié)果顯示創(chuàng)建成功。

2. 使用SHOW CREATE TABLE語句 或 manage index 查看index4數(shù)據(jù)表的結(jié)構(gòu)。如下圖所示：

從上圖中可以看出，在subject字段上已經(jīng)建立了一個(gè)名為index4_st的單列索引。

注意：subject字段長(zhǎng)度為30，而index4_st設(shè)置的索引長(zhǎng)度只有10，這樣做是為了提高查詢速度。對(duì)于字符型的數(shù)據(jù)，可以不用查詢?nèi)啃畔?#xff0c;而只查詢它前面的若干字符信息。

1.5 MySQL創(chuàng)建多列索引

創(chuàng)建多列索引是在表的多個(gè)字段上創(chuàng)建一個(gè)索引。

實(shí)例：創(chuàng)建一個(gè)名為index5的數(shù)據(jù)表，在表中的name和sex字段上建立名為index5_ns的多列索引。

1. 創(chuàng)建多列索引的SQL代碼如下：

CREATE TABLE index5(id INT,
name VARCHAR(20),
sex CHAR(4),

INDEX index5_ns(name,sex)

);
在DOS提示符窗口中或可視化工具中查看MySQL創(chuàng)建多列索引的操作效果。如下圖所示：

從上圖中可以看出，代碼的執(zhí)行結(jié)果顯示index5_ns索引創(chuàng)建成功。

從上圖中可以看出，name和sex字段上已經(jīng)建立了一個(gè)名為index5_ns的多列索引。

2. 多列索引中，只有查詢條件中使用了這些字段中第一個(gè)字段時(shí)，索引才會(huì)被使用。

先在index5數(shù)據(jù)表中添加一些數(shù)據(jù)記錄，然后使用EXPLAIN語句可以查看索引的使用情況。如果只是使用name字段作為查詢條件進(jìn)行查詢。如下圖所示：

EXPLAIN SELECT id,NAME,sex FROM index5;

EXPLAIN SELECT id,NAME,sex FROM index5 WHERE id = 3;

從上圖中可以看出，possible_keys和key的值都是index5_ns。Extra（額外信息）顯示正在使用索引。這說明使用name字段進(jìn)行索引時(shí)，索引index5_ns已經(jīng)被使用。

4. 如果只使用sex字段作為查詢條件進(jìn)行查詢。如下圖所示：

EXPLAIN SELECT id,NAME,sex FROM index5 WHERE sex = '女';

從上圖中可以看出，possible_keys和key的值都是NULL。Extra（額外信息）顯示正在使用where條件查詢，而未使用索引。

提示

使用多列索引時(shí)一定要特別注意，只有使用了索引中的第一個(gè)字段時(shí)才會(huì)觸發(fā)索引。如果沒有使用索引中的第一個(gè)字段，那么這個(gè)多列索引就不會(huì)起作用。因此，在優(yōu)化查詢速度時(shí)，可以考慮優(yōu)化多列索引。

1.5 MySQL創(chuàng)建空間索引

使用SPATIAL參數(shù)能夠創(chuàng)建空間索引。創(chuàng)建空間索引時(shí)，表的存儲(chǔ)引擎必須是MyISAM類型。而且，索引字段必須有非空約束。

實(shí)例：創(chuàng)建一個(gè)名為index6的數(shù)據(jù)表，在表中的space字段上建立名為index6_sp的空間索引。

1. 創(chuàng)建空間索引的SQL代碼如下：

CREATE TABLE index6 ( id INT, SPACE GEOMETRY NOT NULL, SPATIAL INDEX index6_sp(SPACE) ) ENGINE=MYISAM;

在DOS提示符窗口中或可視化工具中查看MySQL創(chuàng)建空間索引的操作效果。如下圖所示：

從上圖可以看出，代碼執(zhí)行的結(jié)果顯示空間索引創(chuàng)建成功。

使用SHOW CREATE TABLE語句可看index6數(shù)據(jù)表的結(jié)構(gòu)。如下圖所示：
從上圖中可以看出，在space字段上已經(jīng)建立了一個(gè)名為index6_sp的空間索引。從上圖中可以看出，在space字段上已經(jīng)建立了一個(gè)名為index6_sp的空間索引。

注意，space字段是非空的，而且數(shù)據(jù)類型是GEOMETRY類型。這個(gè)類型是空間數(shù)據(jù)類型。

空間數(shù)據(jù)類型包括GEOMETRY、POINT、LINESTRING和POLYGON類型等。這些空間數(shù)據(jù)類型平時(shí)很少用到

2 添加索引

1.添加PRIMARY KEY（主鍵索引）
mysql>ALTER TABLE table_name ADD PRIMARY KEY ( column )
2.添加UNIQUE(唯一索引)
mysql>ALTER TABLE table_name ADD UNIQUE (
column
)
3.添加INDEX(普通索引)
mysql>ALTER TABLE table_name ADD INDEX index_name ( column )
4.添加FULLTEXT(全文索引)
mysql>ALTER TABLE table_name ADD FULLTEXT ( column)
5.添加多列索引
mysql>ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

3 建立索引常用的規(guī)則

轉(zhuǎn)：https://www.cnblogs.com/JimLy-BUG/p/6812682.html
建立索引常用的規(guī)則如下：
1、表的主鍵、外鍵必須有索引；
2、數(shù)據(jù)量超過300萬的表應(yīng)該有索引；
3、經(jīng)常與其他表進(jìn)行連接的表，在連接字段上應(yīng)該建立索引；
4、經(jīng)常出現(xiàn)在Where子句中的字段，非凡是大表的字段，應(yīng)該建立索引；
5、索引應(yīng)該建在選擇性高的字段上（枚舉型字段不建索引）；
6、索引應(yīng)該建在小字段上，對(duì)于大的文本字段甚至超長(zhǎng)字段，不要建索引；
7、復(fù)合索引的建立需要進(jìn)行仔細(xì)分析；盡量考慮用單字段索引代替：
A、正確選擇復(fù)合索引中的主列字段，一般是選擇性較好的字段；
B、復(fù)合索引的幾個(gè)字段是否經(jīng)常同時(shí)以AND方式出現(xiàn)在Where子句中？單字段查詢是否極少甚至沒有？假如是，則可以建立復(fù)合索引；否則考慮單字段索引；
C、假如復(fù)合索引中包含的字段經(jīng)常單獨(dú)出現(xiàn)在Where子句中，則分解為多個(gè)單字段索引；
D、假如復(fù)合索引所包含的字段超過3個(gè)，那么仔細(xì)考慮其必要性，考慮減少復(fù)合的字段；
E、假如既有單字段索引，又有這幾個(gè)字段上的復(fù)合索引，一般可以刪除復(fù)合索引；
8、頻繁進(jìn)行數(shù)據(jù)操作的表，不要建立太多的索引；
9、刪除無用的索引，避免對(duì)執(zhí)行計(jì)劃造成負(fù)面影響；
以上是一些普遍的建立索引時(shí)的判定依據(jù)。一言以蔽之，索引的建立必須慎重，對(duì)每個(gè)索引的必要性都應(yīng)該經(jīng)過仔細(xì)分析，要有建立的依據(jù)。因?yàn)樘嗟乃饕c不充分、不正確的索引對(duì)性能都毫無益處：在表上建立的每個(gè)索引都會(huì)增加存儲(chǔ)開銷，索引對(duì)于插入、刪除、更新操作也會(huì)增加處理上的開銷。另外，過多的復(fù)合索引，在有單字段索引的情況下，一般都是沒有存在價(jià)值的；相反，還會(huì)降低數(shù)據(jù)增加刪除時(shí)的性能，凡是對(duì)頻繁更新的表來說，負(fù)面影響更大

總結(jié)

以上是生活随笔為你收集整理的MySQL索引介绍，普通索引，全文索引，空间索引，多列索引使用原则，建立索引常用的规则的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。