MySQL選錯索引的原因是什么

本篇內容介紹了“MySQL選錯索引的原因是什么”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

1.引例

首先創建一張表，并對字段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;

然后往表中，插入十萬行數據，值按整數遞增：(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();

接下來，我們執行一條sql：

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

執行結果：

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

我們再來執行如下操作：

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

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

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;

下面是兩種慢查詢日志中的結果對比：

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

那為什么會選錯索引呢？

2.優化器的邏輯

選擇索引是優化器的工作，優化器選擇索引的目的，就是想要找到一個最優的執行方案，并用最小的代價去執行。

在數據庫里面，掃描行數是影響執行代價的因素之一。掃描行數越少，意味著訪問磁盤次數越少。但是掃描行數并不是唯一的評價標準，還會考慮臨時表，是否排序等因素。

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

我們可以用show index的方法看到不同索引的基數值,但是可以看到統計信息并不是太準確。 可以使用analyze table t來重新統計，但是也不一定準確。

那MySQL是如何得到索引的基數呢？
答案是MySQL會采取采樣統計的方法，默認會選擇N個數據頁，統計這些頁面上的不同值，得到平均值，再乘以總的頁面數。

在MySQL中，有兩種存儲索引統計的方式，可以通過設置innodb_stats_persisten來設置：

• 設置為on的時候，表示統計信息會持久化存儲。這時，默認的N是20，M是10

• 設置為off的時候，表示統計信息只存儲在內存中。這時，默認的N是8，M是16

我們再來比較兩個語句預估的查詢行數，如下圖：

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

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

3.解決辦法

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

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

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

“MySQL選錯索引的原因是什么”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！