MySQL選錯(cuò)索引的原因是什么

本篇內(nèi)容介紹了“MySQL選錯(cuò)索引的原因是什么”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

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;

然后往表中，插入十萬(wàn)行數(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();

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

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

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

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

我們?cè)賮?lái)執(zhí)行如下操作：

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

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

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;

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

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

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

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

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

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

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

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

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

在MySQL中，有兩種存儲(chǔ)索引統(tǒng)計(jì)的方式，可以通過(guò)設(shè)置innodb_stats_persisten來(lái)設(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è)賮?lái)比較兩個(gè)語(yǔ)句預(yù)估的查詢(xún)行數(shù)，如下圖：

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

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

3.解決辦法

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

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

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

“MySQL選錯(cuò)索引的原因是什么”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！