詳解MySQL數(shù)據(jù)庫(kù)常見(jiàn)的索引問(wèn)題：無(wú)索引，隱式轉(zhuǎn)換，附實(shí)例說(shuō)明
概述
在這些年的工作之中，由于SQL問(wèn)題導(dǎo)致的數(shù)據(jù)庫(kù)故障層出不窮，而索引問(wèn)題是SQL問(wèn)題中出現(xiàn)頻率最高的，常見(jiàn)的索引問(wèn)題包括：無(wú)索引，隱式轉(zhuǎn)換。
索引問(wèn)題
1、無(wú)索引
當(dāng)數(shù)據(jù)庫(kù)中出現(xiàn)訪問(wèn)表的SQL無(wú)索引導(dǎo)致全表掃描，如果表的數(shù)據(jù)量很大，掃描大量的數(shù)據(jù)，應(yīng)用請(qǐng)求變慢占用數(shù)據(jù)庫(kù)連接，連接堆積很快達(dá)到數(shù)據(jù)庫(kù)的最大連接數(shù)設(shè)置，新的應(yīng)用請(qǐng)求將會(huì)被拒絕導(dǎo)致故障發(fā)生。
2、隱式轉(zhuǎn)換
隱式轉(zhuǎn)換是指SQL查詢(xún)條件中的傳入值與對(duì)應(yīng)字段的數(shù)據(jù)定義不一致導(dǎo)致索引無(wú)法使用。常見(jiàn)隱士轉(zhuǎn)換如字段的表結(jié)構(gòu)定義為字符類(lèi)型，但SQL傳入值為數(shù)字;或者是字段定義collation為區(qū)分大小寫(xiě)，在多表關(guān)聯(lián)的場(chǎng)景下，其表的關(guān)聯(lián)字段大小寫(xiě)敏感定義各不相同。隱式轉(zhuǎn)換會(huì)導(dǎo)致索引無(wú)法使用，進(jìn)而出現(xiàn)上述慢SQL堆積數(shù)據(jù)庫(kù)連接數(shù)跑滿(mǎn)的情況。
無(wú)索引實(shí)例
表結(jié)構(gòu)：
執(zhí)行計(jì)劃：
從上面的SQL看到執(zhí)行計(jì)劃中ALL，代表了這條SQL執(zhí)行計(jì)劃是全表掃描，每次執(zhí)行需要掃描707250行數(shù)據(jù)，這是非常消耗性能的，該如何進(jìn)行優(yōu)化?添加索引。
驗(yàn)證mo字段的過(guò)濾性:
可以看到mo字段的過(guò)濾性是非常高的，進(jìn)一步驗(yàn)證可以通過(guò)select count(*) as all_count,count(distinct mo) as distinct_cnt from user，通對(duì)比 all_count和distinct_cnt這兩個(gè)值進(jìn)行對(duì)比，如果all_cnt和distinct_cnt相差甚多，則在mo字段上添加索引是非常有效的。
添加索引
- mysql> alter table user add index ind_mo(mo);
- mysql>SELECT uid FROM `user` WHERE mo=13772556391 LIMIT 0,1;
執(zhí)行計(jì)劃：
隱式轉(zhuǎn)換
表結(jié)構(gòu)：
執(zhí)行計(jì)劃：
- mysql> explain extended select uid from`user` where mo=13772556391 limit 0,1;
- mysql> show warnings;
- Warning1：Cannot use index 'ind_mo' due to type or collation conversion on field 'mo'
- Note：select `user`.`uid` AS `uid` from `user` where (`user`.`mo` = 13772556391) limit 0,1
如何解決：
上述案例中由于表結(jié)構(gòu)定義mo字段后字符串?dāng)?shù)據(jù)類(lèi)型，而應(yīng)用傳入的則是數(shù)字，進(jìn)而導(dǎo)致了隱式轉(zhuǎn)換，索引無(wú)法使用，所以有兩種方案：
第一，將表結(jié)構(gòu)mo修改為數(shù)字?jǐn)?shù)據(jù)類(lèi)型。
第二，修改應(yīng)用將應(yīng)用中傳入的字符類(lèi)型改為數(shù)據(jù)類(lèi)型。
總結(jié)
在使用索引時(shí)，我們可以通過(guò)explain+extended查看SQL的執(zhí)行計(jì)劃，判斷是否使用了索引以及發(fā)生了隱式轉(zhuǎn)換。
由于常見(jiàn)的隱式轉(zhuǎn)換是由字段數(shù)據(jù)類(lèi)型以及collation定義不當(dāng)導(dǎo)致，因此我們?cè)谠O(shè)計(jì)開(kāi)發(fā)階段，要避免數(shù)據(jù)庫(kù)字段定義，避免出現(xiàn)隱式轉(zhuǎn)換。
