MySQL索引失效的典型案例
典型案例
有兩張表，表結構如下：
CREATETABLE`student_info`( `id`int(11)NOTNULL, `name`varchar(10)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_name`(`name`) )ENGINE=InnoDBDEFAULTCHARSET=utf8mb4 CREATETABLE`student_score`( `id`int(11)NOTNULL, `name`varchar(10)DEFAULTNULL, `score`int(11)DEFAULTNULL, PRIMARYKEY(`id`), KEY`idx_name`(`name`) )ENGINE=InnoDBDEFAULTCHARSET=utf8
其中一張是info表，一張是score表，其中score表比info表多了一列score字段。
插入數(shù)據(jù)：
mysql>insertintostudent_infovalues(1,'zhangsan'),(2,'lisi'),(3,'wangwu'),(4,'zhaoliu'); QueryOK,4rowsaffected(0.01sec) Records:4Duplicates:0Warnings:0 mysql>insertintostudent_scorevalues(1,'zhangsan',60),(2,'lisi',70),(3,'wangwu',80),(4,'zhaoliu',90); QueryOK,4rowsaffected(0.01sec) Records:4Duplicates:0Warnings:0 mysql>select*fromstudent_info; +----+----------+ |id|name| +----+----------+ |2|lisi| |3|wangwu| |1|zhangsan| |4|zhaoliu| +----+----------+ 4rowsinset(0.00sec) mysql>select*fromstudent_score; +----+----------+-------+ |id|name|score| +----+----------+-------+ |1|zhangsan|60| |2|lisi|70| |3|wangwu|80| |4|zhaoliu|90| +----+----------+-------+ 4rowsinset(0.00sec)
當我們進行下面的語句時：
mysql>explainselectB.* from student_infoA,student_scoreB whereA.name=B.nameandA.id=1; +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ |1|SIMPLE|A|NULL|const|PRIMARY,idx_name|PRIMARY|4|const|1|100.00|NULL| |1|SIMPLE|B|NULL|ALL|NULL|NULL|NULL|NULL|4|100.00|Usingwhere| +----+-------------+-------+------------+-------+------------------+---------+---------+-------+------+----------+-------------+ 2rowsinset,1warning(0.00sec)
為什么B.name上有索引，但是執(zhí)行計劃里面第二個select表B的時候，沒有使用索引，而用的全表掃描？？？
解析：
該SQL會執(zhí)行三個步驟：
1、先過濾A.id=1的記錄，使用主鍵索引，只掃描1行LA
2、從LA這一行中找到name的值“zhangsan”，
3、根據(jù)LA.name的值在表B中進行查找，找到相同的值zhangsan，并返回。
其中，第三步可以簡化為：
select * from student_score where name=$LA.name
這里，因為LA是A表info中的內(nèi)容，而info表的字符集是utf8mb4，而B表score表的字符集是utf8。
所以
在執(zhí)行的時候相當于用一個utf8類型的左值和一個utf8mb4的右值進行比較，因為utf8mb4完全包含utf8類型(長字節(jié)包含短字節(jié))，MySQL會將utf8轉換成utf8mb4(不反向轉換，主要是為了防止數(shù)據(jù)截斷).
因此，相當于執(zhí)行了：
select*fromstudent_scorewhereCONVERT(nameUSINGutf8mb4)=$LA.name
而我們知道，當索引字段一旦使用了隱式類型轉換，那么索引就失效了，MySQL優(yōu)化器將會使用全表掃描的方式來執(zhí)行這個SQL。
要解決這個問題，可以有以下兩種方法：
a、修改字符集。
b、修改SQL語句。
給出修改字符集的方法：
mysql>altertablestudent_scoremodifynamevarchar(10)charactersetutf8mb4; QueryOK,4rowsaffected(0.03sec) Records:4Duplicates:0Warnings:0 mysql>explainselectB.*fromstudent_infoA,student_scoreBwhereA.name=B.nameandA.id=1; +----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ |1|SIMPLE|A|NULL|const|PRIMARY,idx_name|PRIMARY|4|const|1|100.00|NULL| |1|SIMPLE|B|NULL|ref|idx_name|idx_name|43|const|1|100.00|NULL| +----+-------------+-------+------------+-------+------------------+----------+---------+-------+------+----------+-------+ 2rowsinset,1warning(0.01sec)
修改SQL的方法，大家可以自己嘗試。
附：常見索引失效的情況
一、對列使用函數(shù)，該列的索引將不起作用。
二、對列進行運算(+，-，*，/，! 等)，該列的索引將不起作用。
三、某些情況下的LIKE操作，該列的索引將不起作用。
四、某些情況使用反向操作，該列的索引將不起作用。
五、在WHERE中使用OR時，有一個列沒有索引，那么其它列的索引將不起作用。
六、隱式轉換導致索引失效.這一點應當引起重視.也是開發(fā)中經(jīng)常會犯的錯誤。
七、使用not in ,not exist等語句時。
八、當變量采用的是times變量，而表的字段采用的是date變量時.或相反情況。
九、當B-tree索引 is null不會失效,使用is not null時,會失效,位圖索引 is null,is not null 都會失效。
十、聯(lián)合索引 is not null 只要在建立的索引列（不分先后）都會失效。
以上就是MySQL索引失效的典型案例的詳細內(nèi)容，更多關于MySQL索引失效的資料請關注本站其它相關文章！
版權聲明：本站文章來源標注為YINGSOO的內(nèi)容版權均為本站所有，歡迎引用、轉載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權，請聯(lián)系alex-e#qq.com處理。
關注官方微信