快捷導(dǎo)航

淺談MySQL為什么會選錯索引

更新時間：2023年03月20日 11:09:59 作者：XHHP

本文主要介紹了淺談MySQL為什么會選錯索引，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧

1.引例

首先創(chuàng)建一張表，并對字段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，沒什么問題。

我們再來執(zhí)行如下操作：

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

為了比較使用索引和不使用的查詢性能對比，執(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é)果對比：

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

那為什么會選錯索引呢？

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

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

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

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

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