1.引例

首先創(chuàng)建一張表，并對(duì)字段a，b分別建立索引：

create table t (
    id int(11) not null,
    a int(11) default null,
    b int(11) default null,
    primary key (id),
    key a(a),
    key b(b)
)engine=InnoDB;

然后往表中，插入十萬行數(shù)據(jù)，值按整數(shù)遞增：(1,1,1)、(2,2,2)、(3,3,3)…

delimiter ;;
create PROCEDURE insertdata()
begin 
	declare i int;
	set i=1;
	while(i<=100000) DO
		insert into t values(i,i,i);
		set i = i+1;
	end while;
end;;
delimiter ;
call insertdata();

接下來，我們執(zhí)行一條sql：

mysql >explain select * from t where a between 10000 and 20000;

執(zhí)行結(jié)果：

結(jié)果中的“key”字段就代表了查詢中使用的索引。所以這條語句走了索引a，沒什么問題。

我們?cè)賮韴?zhí)行如下操作：

但是這個(gè)時(shí)候session B的查詢語句select * from t where a between 10000 and 20000就不會(huì)再選擇索引a。

為了比較使用索引和不使用的查詢性能對(duì)比，執(zhí)行下面的語句：

set long_query_time=0;
select * from t where a between 10000 and 20000;
select * from t force(a) where a between 10000 and 20000;

下面是兩種慢查詢?nèi)罩局械慕Y(jié)果對(duì)比：

第一個(gè)查詢查找了十萬行，第二個(gè)查詢走了索引，查找了一萬行，速度明顯比較快。

那為什么會(huì)選錯(cuò)索引呢？

2.優(yōu)化器的邏輯

選擇索引是優(yōu)化器的工作，優(yōu)化器選擇索引的目的，就是想要找到一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行。

在數(shù)據(jù)庫里面，掃描行數(shù)是影響執(zhí)行代價(jià)的因素之一。掃描行數(shù)越少，意味著訪問磁盤次數(shù)越少。但是掃描行數(shù)并不是唯一的評(píng)價(jià)標(biāo)準(zhǔn)，還會(huì)考慮臨時(shí)表，是否排序等因素。

那掃描行數(shù)是如何判斷的？
MySQL在真正執(zhí)行之前，只能根據(jù)統(tǒng)計(jì)信息來估算記錄數(shù)。這個(gè)統(tǒng)計(jì)信息就是索引的“區(qū)分度”。 一個(gè)索引上不同的值越多，這個(gè)索引的區(qū)分度就越好。而一個(gè)索引上不同的值的個(gè)數(shù)，我們稱之為“基數(shù)”(cardinality)。也就是說，這個(gè)基數(shù)越大，索引的區(qū)分度越好。

我們可以用show index的方法看到不同索引的基數(shù)值,但是可以看到統(tǒng)計(jì)信息并不是太準(zhǔn)確。 可以使用analyze table t來重新統(tǒng)計(jì)，但是也不一定準(zhǔn)確。

那MySQL是如何得到索引的基數(shù)呢？
答案是MySQL會(huì)采取采樣統(tǒng)計(jì)的方法，默認(rèn)會(huì)選擇N個(gè)數(shù)據(jù)頁，統(tǒng)計(jì)這些頁面上的不同值，得到平均值，再乘以總的頁面數(shù)。

在MySQL中，有兩種存儲(chǔ)索引統(tǒng)計(jì)的方式，可以通過設(shè)置innodb_stats_persisten來設(shè)置：

• 設(shè)置為on的時(shí)候，表示統(tǒng)計(jì)信息會(huì)持久化存儲(chǔ)。這時(shí)，默認(rèn)的N是20，M是10
• 設(shè)置為off的時(shí)候，表示統(tǒng)計(jì)信息只存儲(chǔ)在內(nèi)存中。這時(shí)，默認(rèn)的N是8，M是16

我們?cè)賮肀容^兩個(gè)語句預(yù)估的查詢行數(shù)，如下圖：

圖中的row字段就代表預(yù)估的查詢行數(shù)。對(duì)于第一條語句，預(yù)估的查詢行數(shù)是104620.第二條語句，預(yù)估的查詢行數(shù)是37116。明顯第二條語句的查詢行數(shù)少，那為什么沒有選擇索引a呢？

這是因?yàn)?，如果使用索引a，每次從索引a上拿到一個(gè)值，都要回表查詢。而如果選擇掃描十萬行的語句，則不需要回表。因此優(yōu)化器評(píng)估這兩條語句時(shí)，覺得回表查詢更耗費(fèi)時(shí)間，所以沒有使用索引。但是實(shí)際中，這種方式并不是最優(yōu)的。

3.解決辦法

第一種解決辦法是和第二條語句一樣，采用force index強(qiáng)行選擇一個(gè)索引。如果force index指定的索引在候選索引列表中，就直接選擇這個(gè)索引，而不再去評(píng)估執(zhí)行代價(jià)。但是這種方式不太優(yōu)雅，而且改了索引名，語句也要改

第二種解決辦法是考慮修改sql語句，引導(dǎo)MySQL使用我們期望的索引。

第三種解決辦法是新建一個(gè)更合適的索引，刪除掉誤用的索引。

到此這篇關(guān)于淺談MySQL為什么會(huì)選錯(cuò)索引的文章就介紹到這了,更多相關(guān)MySQL 選錯(cuò)索引內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論