查看索引

show index from 表名;

刪除索引

drop index 索引名 on 表名;

主鍵索引

主鍵索引是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵，一般以表的id字段為主鍵

ALTER TABLE 表名 ADD PRIMARY KEY ( 列名 );

普通索引

可以加速查詢，但不能約束數(shù)據(jù)唯一性，可以在查詢和插入操作的時(shí)候使用普通索引來(lái)提升性能

create index 索引名 on 表名(列名);
create index 索引名 on 表名(列名(長(zhǎng)度));
如果是CHAR,VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度，此時(shí)可省略不指定
如果是BLOB 和 TEXT 類型，必須指定length，

唯一索引

會(huì)強(qiáng)制保證數(shù)據(jù)的唯一性，允許有空值。如果是組合索引，則列值的組合必須唯一，再次插入該列的相同數(shù)據(jù)時(shí)會(huì)報(bào)錯(cuò)

create unique index 索引名 on 表名(列名);
create unique index 索引名 on 表名(列名(長(zhǎng)度));
如果是CHAR,VARCHAR類型，length可以小于字段實(shí)際長(zhǎng)度，此時(shí)可省略不指定
如果是BLOB 和 TEXT 類型，必須指定length，

普通組合索引

多個(gè)字段組合在一起組成一個(gè)索引，類似普通索引，加速查詢，但不能約束數(shù)據(jù)唯一性

create index 索引名 on 表名(列名1,列名2);

唯一組合索引

多個(gè)字段組合在一起組成一個(gè)索引，但這幾個(gè)字段組合在一起可以約束數(shù)據(jù)唯一性，再次插入這幾個(gè)列組合相同數(shù)據(jù)時(shí)會(huì)報(bào)錯(cuò)

create unique index 索引名 on 表名(列名1,列名2);

explain

id

?這一行說(shuō)明了sql執(zhí)行的順序，在 join 查詢或子查詢時(shí)，通過(guò)這個(gè)參數(shù)可以很清晰的看到 mysql 是通過(guò)怎樣的順序執(zhí)行我們給定 sql 的。

注：id值越大，說(shuō)明執(zhí)行的順序越靠前

select_type

?說(shuō)明了執(zhí)行這條sql時(shí)的查詢類型

• SIMPLE：簡(jiǎn)單查詢，不包含子查詢或Union查詢    
• PRIMARY：最外面的SELECT
• SUBQUERY：包含子查詢
• DERIVED：在FROM列表中包含的子查詢被標(biāo)記為 DERIVED
• UNION：包含 union 查詢

type

最重要的分析字段之一，下面是性能由最差到最好

在阿里巴巴要求，sql 性能優(yōu)化的目標(biāo)至少要達(dá)到 range 級(jí)別

• ALL：遍歷全表以找到匹配行
• INDEX：和 ALL 一樣，都是全表掃描，區(qū)別是 index 掃描表時(shí)是按索引次序進(jìn)行而不是行
• range：只搜索給定范圍的行，通常出現(xiàn)在 in、between、<>
• index_merge：表示使用了索引合并(對(duì)多個(gè)索引分別進(jìn)行條件掃描，然后將它們各自的結(jié)果進(jìn)行合并)
• ref_or_null：類似ref，但是可以搜索值為NULL的行
• ref：非唯一性索引掃描，返回匹配某個(gè)單獨(dú)值的所有行
• eq_ref ：唯一性索引掃描，(在使用主鍵或唯一性索引查找時(shí)看到，最多只返回一條記錄)
• const：只通過(guò)索引，就找到結(jié)果了(不用再去數(shù)據(jù)表中掃描了)
• null：在優(yōu)化階段分解查詢語(yǔ)句，在執(zhí)行階段用不著再訪問(wèn)表或索引(常見(jiàn)于只進(jìn)行min或max 查詢)

table

?說(shuō)明數(shù)據(jù)來(lái)自哪張表

partitions

?匹配的分區(qū)

possible_keys

?對(duì)于建索引有參考價(jià)值,可能在這個(gè)sql查詢中使用的索引

key

?說(shuō)明這條sql查詢實(shí)際使用的索引

key_len

?索引字段最大的可能長(zhǎng)度

ref

?顯示索引的哪一列被使用了，如果可能的話，是一個(gè)常數(shù)，哪些列或常量被用于查找索引列上的值

rows

?根據(jù)表統(tǒng)計(jì)信息及索引選用情況，大致估算出找到所需的記錄所需讀取的行數(shù)

filtered

?查詢的行數(shù)占數(shù)據(jù)表總行數(shù)的百分比

Extra

不適合在其它列中顯示，但十分重要的額外信息

• Using filesort：MySQL 對(duì)結(jié)果使用一個(gè)外部索引排序，而不是按照數(shù)據(jù)表本身的索引排序
• Using index：使用了覆蓋索引(只通過(guò)索引就查到結(jié)果集了),避免訪問(wèn)了表的數(shù)據(jù)行，效率不錯(cuò)
• Using temporary：使用了臨時(shí)表保存中間結(jié)果，常見(jiàn)于 order by 和 group by
• Using where：使用了where條件
• Using join buffer：使用了連接緩存

索引失效場(chǎng)景

1、字段類型不一致，發(fā)生了隱式轉(zhuǎn)化

示例：id主鍵索引，s_name, age 各有一個(gè)索引
-- 未命中索引
explain select id, s_name, age from student where s_name = 100;
-- 命中索引
explain select id, s_name, age from student where s_name = '100';

表中s_name字段類型為varchar，但查詢時(shí)用的是int，會(huì)發(fā)生類型轉(zhuǎn)化，因此查詢不走索引

2、查詢中包含 or

示例：id主鍵索引，s_name, age 各有一個(gè)索引，create_by沒(méi)有索引
-- 不走索引
explain select id, s_name, age from student where s_name = '100' or create_by = 'admin';
create_by未創(chuàng)建索引，當(dāng)查詢語(yǔ)句where后過(guò)濾條件包含該字段不走索引；

-- 走索引
explain select id, s_name, age from student where s_name = '100' or age = 18;
s_name, age有各自的索引，查詢語(yǔ)句會(huì)將索引合并，參考explain執(zhí)行結(jié)果type字段的值：index_merge

3、like通配符 % 的錯(cuò)誤使用

示例：id主鍵索引，s_name, age 各有一個(gè)索引
-- 不走索引
explain select id, s_name, age from student where s_name like '%20';
-- 不走索引
explain select id, s_name, age from student where s_name like '%20%';
以上兩種情況均為通配符 % 在前面

-- 走索引，取消在前面的通配符 % 
explain select id, s_name, age from student where s_name like '20%';

-- 走索引，注意這里只查詢了一個(gè)字段，且是where后過(guò)濾的字段
explain select s_name from student where s_name like '%20%';

4、聯(lián)合索引最左匹配原則

最左原則：

假設(shè)組合索引為：a,b,c

• 當(dāng)SQL中對(duì)應(yīng)有：a、或者a，b、或者a，b，c的時(shí)候，可稱為完全滿足最左原則；
• 當(dāng)SQL中查詢條件對(duì)應(yīng)只有a，c的時(shí)候，可稱為部分滿足最左原則；
• 當(dāng)SQL中沒(méi)有a的時(shí)候，可稱為不滿足最左原則。

注：MySQL5.7開(kāi)始，會(huì)自動(dòng)優(yōu)化，如：會(huì)把c，b，a優(yōu)化為a，b，c使之完全遵循最左原則；會(huì)把c，a優(yōu)化為a，c，使之部分遵循最左原則。即：SQL語(yǔ)句中的對(duì)應(yīng)條件的先后順序無(wú)關(guān)。

示例：s_name, age兩個(gè)字段創(chuàng)建普通組合索引
-- 走索引 遵循最左原則
explain select id, s_name, age, create_by, create_time from student where s_name = 'zs';
-- 走索引 遵循最左原則
explain select id, s_name, age, create_by, create_time from student where s_name = 'zs' and age = 20;
-- 不走索引 沒(méi)有遵循最左原則
explain select id, s_name, age, create_by, create_time from student where age = 20;
-- 走索引 因?yàn)椴樵兞袨楦采w索引，但若查詢列中加入一個(gè)沒(méi)有索引的字段，則不走索引
explain select id, s_name, age from student where s_name = 'zs' or age = 20;

5、索引列使用mysql函數(shù)

示例：id主鍵索引，s_name, age 各有一個(gè)索引，create_by, create_time沒(méi)有索引
-- 不走索引
-- substr(s_name,1,3) = 'zss'：將s_name列字符串從第一位截取到第三位，然后結(jié)果是 zss
explain select id, s_name, age, create_by, create_time from student where substr(s_name,1,3) = 'zss';
查詢時(shí)使用了mysql內(nèi)置的函數(shù)，導(dǎo)致了索引命中失敗

6、索引列存在計(jì)算 （+ 、-、*、/）

示例：id主鍵索引，s_name, age 各有一個(gè)索引，create_by, create_time沒(méi)有索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student where age - 1 = 19;
查詢條件中包含索引列計(jì)算，導(dǎo)致索引未命中

7、使用!= 、<>、not in 可能會(huì)導(dǎo)致索引失效

?注意：是可能會(huì)使索引失效，不是絕對(duì)，當(dāng)前尚未遇到該情況

8、使用is null 、is not null 導(dǎo)致索引失效

?(1)若絕大多數(shù)行都是非null，則查詢is null 走二級(jí)索引，查詢is not null走全表掃描；

(2)若絕大多數(shù)行都是null，則查詢is not null走索引，is null 也走索引；

9、左連接或右連接字段編碼不一致

?例如：表一有s_name字段，表二有s_name字段，且在各自的表里該字段都建立了索引，當(dāng)兩個(gè)表根據(jù)s_name字段做左連接或右連接時(shí)，如果這個(gè)字段在各自的標(biāo)中字符編碼不一致時(shí)，索引不會(huì)生效，若想使索引生效，將字符編碼改為一致即可

10、group by 未遵循最左匹配原則

示例：s_name, age兩個(gè)字段創(chuàng)建普通組合索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student group by age;
-- 不走索引
explain select ANY_VALUE(s_name),age from student group by age;

使用group by 進(jìn)行分組的字段未遵循最左匹配原則，索引將失效

此處延伸出個(gè)問(wèn)題：

如果你的MySQL版本大于等于 5.7，你會(huì)發(fā)現(xiàn)上面第一條語(yǔ)句可能執(zhí)行失敗

從 MySQL 5.7.5 開(kāi)始，默認(rèn) SQL 模式包括 ONLY_FULL_GROUP_BY。 （在 5.7.5 之前，MySQL 不檢測(cè)函數(shù)依賴，并且默認(rèn)不啟用 ONLY_FULL_GROUP_BY）這可能會(huì)導(dǎo)致一些sql語(yǔ)句失效。

解決辦法：

1、要么像上述第二個(gè)語(yǔ)句，將查詢的另一個(gè)字段放入ANY_VALUE()中，分組字段可不放入，但是只能放進(jìn)一個(gè)字段，若有其余需要查詢的字段就不能用了；

2、編輯MySQL配置文件：

windows：

編輯 mysql 配置文件 my.ini，在尾部添加以下內(nèi)容，重新啟動(dòng) mysql 即可：

[mysql] 
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

linux：

輯 /etc/my.cnf 文件，在尾部添加以下內(nèi)容，重新啟動(dòng) mysql 即可：

[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

11、兩個(gè)字段對(duì)比導(dǎo)致索引未命中

示例：id為主鍵索引，age為普通索引
-- 不走索引
explain select id, s_name, age, create_by, create_time from student where age > id;

12、范圍查找索引失敗

?如果查找的數(shù)據(jù)通過(guò)索引查找超出全表的10%-30%，DBMS發(fā)現(xiàn)全表掃描比走索引效率更高，

因此就放棄了走索引，而使用全表掃描

?總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論