查看索引

show index from 表名;

刪除索引

drop index 索引名 on 表名;

主鍵索引

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

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

普通索引

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

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

唯一索引

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

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

普通組合索引

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

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

唯一組合索引

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

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

explain

id

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

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

select_type

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

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

type

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

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

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

table

?說明數(shù)據(jù)來自哪張表

partitions

?匹配的分區(qū)

possible_keys

?對于建索引有參考價值,可能在這個sql查詢中使用的索引

key

?說明這條sql查詢實際使用的索引

key_len

?索引字段最大的可能長度

ref

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

rows

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

filtered

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

Extra

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

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

索引失效場景

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

示例：id主鍵索引，s_name, age 各有一個索引
-- 未命中索引
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，但查詢時用的是int，會發(fā)生類型轉(zhuǎn)化，因此查詢不走索引

2、查詢中包含 or

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

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

3、like通配符 % 的錯誤使用

示例：id主鍵索引，s_name, age 各有一個索引
-- 不走索引
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%';

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

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

最左原則：

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

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

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

示例：s_name, age兩個字段創(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;
-- 不走索引 沒有遵循最左原則
explain select id, s_name, age, create_by, create_time from student where age = 20;
-- 走索引 因為查詢列為覆蓋索引，但若查詢列中加入一個沒有索引的字段，則不走索引
explain select id, s_name, age from student where s_name = 'zs' or age = 20;

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

示例：id主鍵索引，s_name, age 各有一個索引，create_by, create_time沒有索引
-- 不走索引
-- 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';
查詢時使用了mysql內(nèi)置的函數(shù)，導(dǎo)致了索引命中失敗

6、索引列存在計算 （+ 、-、*、/）

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

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

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

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

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

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

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

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

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

示例：s_name, age兩個字段創(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 進行分組的字段未遵循最左匹配原則，索引將失效

此處延伸出個問題：

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

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

解決辦法：

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

2、編輯MySQL配置文件：

windows：

編輯 mysql 配置文件 my.ini，在尾部添加以下內(nèi)容，重新啟動 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)容，重新啟動 mysql 即可：

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

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

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

12、范圍查找索引失敗

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

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

?總結(jié)

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

最新評論