導(dǎo)致MySQL做全表掃描的幾種情況
這兩天看到了兩種可能會(huì)導(dǎo)致全表掃描的sql，這里給大家看一下，希望可以避免踩坑：
情況1：
強(qiáng)制類(lèi)型轉(zhuǎn)換的情況下，不會(huì)使用索引，會(huì)走全表掃描。
舉例如下：
首先我們創(chuàng)建一個(gè)表
CREATETABLE`test`( `id`int(11)NOTNULLAUTO_INCREMENT, `age`int(11)DEFAULTNULL, `score`varchar(20)NOTNULLDEFAULT'', PRIMARYKEY(`id`), KEY`idx_score`(`score`) )ENGINE=InnoDBAUTO_INCREMENT=12DEFAULTCHARSET=utf8
我們可以看到，這個(gè)表有三個(gè)字段，其中兩個(gè)int類(lèi)型，一個(gè)varchar類(lèi)型。varchar類(lèi)型的字段score是一個(gè)索引，而id是主鍵。
然后我們給這個(gè)表里面插入一些數(shù)據(jù)，插入數(shù)據(jù)之后的表如下：
mysql：yeyztest 21:43:12>>select*fromtest; +----+------+-------+ |id|age|score| +----+------+-------+ |1|1|5| |2|2|10| |5|5|25| |8|8|40| |9|2|45| |10|5|50| |11|8|55| +----+------+-------+ 7rowsinset(0.00sec)
這個(gè)時(shí)候，我們使用explain語(yǔ)句來(lái)查看兩條sql的執(zhí)行情況，分別是：
explain select * from test where score ='10'; explain select * from test where score =10;
結(jié)果如下：
mysql:yeyztest21:42:29>>explainselect*fromtestwherescore='10'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ |1|SIMPLE|test|NULL|ref|idx_score|idx_score|62|const|1|100.00|NULL| +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1rowinset,1warning(0.00sec) mysql:yeyztest21:43:06>>explainselect*fromtestwherescore=10; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |1|SIMPLE|test|NULL|ALL|idx_score|NULL|NULL|NULL|7|14.29|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1rowinset,3warnings(0.00sec)
可以看到，如果我們使用的是varchar類(lèi)型的值，那么結(jié)果中掃描的行數(shù)rows就是1，而當(dāng)我們使用的是整數(shù)值10的時(shí)候，掃描行數(shù)變?yōu)榱?，證明，如果出現(xiàn)了強(qiáng)制類(lèi)型轉(zhuǎn)換，則會(huì)導(dǎo)致索引失效。
情況2：
反向查詢(xún)不能使用索引，會(huì)導(dǎo)致全表掃描。
創(chuàng)建一個(gè)表test1，它的主鍵是score，然后插入6條數(shù)據(jù)：
CREATETABLE`test1`( `score`varchar(20) not null default '' , PRIMARYKEY(`score`) )ENGINE=InnoDBDEFAULTCHARSET=utf8 mysql:yeyztest22:09:37>>select*fromtest1; +-------+ |score| +-------+ |111| |222| |333| |444| |555| |666| +-------+ 6rowsinset(0.00sec)
當(dāng)我們使用反向查找的時(shí)候，不會(huì)使用到索引，來(lái)看下面兩條sql：
explain select * from test1 where score='111'; explain select * from test1 where score!='111';
mysql:yeyztest22:13:01>>explainselect*fromtest1wherescore='111'; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ |1|SIMPLE|test1|NULL|const|PRIMARY|PRIMARY|62|const|1|100.00|Usingindex| +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 1rowinset,1warning(0.00sec) mysql:yeyztest22:13:08>>explainselect*fromtest1wherescore!='111'; +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ |1|SIMPLE|test1|NULL|index|PRIMARY|PRIMARY|62|NULL|6|100.00|Usingwhere;Usingindex| +----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+ 1rowinset,1warning(0.00sec)
可以看到，使用！=作為條件的時(shí)候，掃描的行數(shù)是表的總記錄行數(shù)。因此如果想要使用索引，我們就不能使用反向匹配規(guī)則。
情況3：
某些or值條件可能導(dǎo)致全表掃描。
首先我們創(chuàng)建一個(gè)表，并插入幾條數(shù)據(jù)：
CREATETABLE`test4`( `id`int(11)DEFAULTNULL, `name`varchar(20)DEFAULTNULL, KEY`idx_id`(`id`) )ENGINE=InnoDBDEFAULTCHARSET=utf8 1rowinset(0.00sec) mysql--dba_admin@127.0.0.1:yeyztest22:23:44>>select*fromtest4; +------+------+ |id|name| +------+------+ |1|aaa| |2|bbb| |3|ccc| |4|yeyz| |NULL|yeyz| +------+------+ 5rowsinset(0.00sec)
其中表test4包含兩個(gè)字段，id字段是一個(gè)索引，而name字段是varchar類(lèi)型，我們來(lái)看下面三個(gè)語(yǔ)句的掃描行數(shù)：
explain select * from test4 where id=1; explain select * from test4 where id is null; explain select * from test4 where id=1 or id is null;
mysql:yeyztest22:24:12>>explainselect*fromtest4whereidisnull; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ |1|SIMPLE|test4|NULL|ref|idx_id|idx_id|5|const|1|100.00|Usingindexcondition| +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+ 1rowinset,1warning(0.00sec) mysql:yeyztest22:24:17>>explainselect*fromtest4whereid=1; +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ |1|SIMPLE|test4|NULL|ref|idx_id|idx_id|5|const|1|100.00|NULL| +----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+ 1rowinset,1warning(0.00sec) mysql:yeyztest22:24:28>>explainselect*fromtest4whereid=1oridisnull; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ |1|SIMPLE|test4|NULL|ALL|idx_id|NULL|NULL|NULL|5|40.00|Usingwhere| +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1rowinset,1warning(0.00sec)
可以看到單獨(dú)使用id=1和id is null，都只會(huì)掃描一行記錄，而使用or將二者連接起來(lái)就會(huì)導(dǎo)致掃描全表而不使用索引。
簡(jiǎn)單總結(jié)一下：
1.強(qiáng)制類(lèi)型轉(zhuǎn)換的情況下，不會(huì)使用索引，會(huì)走全表掃描
2.反向查詢(xún)不能使用索引，會(huì)導(dǎo)致全表掃描。
3.某些or值條件可能導(dǎo)致全表掃描。
以上就是導(dǎo)致MySQL做全表掃描的幾種情況的詳細(xì)內(nèi)容，更多關(guān)于MySQL 全表掃描的資料請(qǐng)關(guān)注本站其它相關(guān)文章！
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信