淺談MySQL為什麼會選錯索引

2023-03-21 06:02:34

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為什麼會選錯索引的文章就介紹到這了,更多相關MySQL 選錯索引內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

淺談MySQL為什麼會選錯索引

目錄

1.引例

2.優化器的邏輯

3.解決辦法

熱門文章