為何MySQL會選錯索引

MySQL

在實際的生產環境中，給MySQL的字段加索引讓查詢的效率更高，我們往往會給MySQL的表字段加索引。MySQL具體選擇哪個索引是有MySQL的優化器決定的。不知道小夥伴們有沒有遇到一種情況，就是實際的查詢中速度應該很快才對的，但因為MySQL選錯的索引，導致查詢的速度變慢了。至此，我們來看一下下面的例子。

牛刀小試

首先，我們簡單創建一張表，表裡面有兩個字段，分別是a和b，並且我們給a和b加上索引，SQL語句執行如下：

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

接下來我們使用存儲過程或者是跑代碼的形式往表裡插入10萬條數據，此處使用存儲過程，代碼如下所示：

delimiter ;;
create procedure idata()
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 idata();

然後我們使用執行計劃運行一條簡單的SQL語句，代碼如下：

explain select * from t where a between 15000 and 20000

運行之後，如下所示：

簡單MySQL語句

可以看到，執行這條語句的時候，MySQL使用了a上面的索引，只掃描(row)了5000條記錄。想必這樣的結果大家都是可以猜到的。

在這裡，也許小夥伴們就會有疑問了，MySQL是怎麼計算出row數的？其實，MySQL大部分時候並不能很精準的統計出這個row數，MySQL在這裡主要採用的方式是抽樣的方式進行計算的，即 row數 = （N個數據頁不同值個數平均值） ×索引 頁數。一個索引上不同的值越多，這個索引的區分度就越好。而一個索引上的不同值的個數就被稱為基數。所以，row數其實嚴格意義上來說是不會100%精準的。

那麼，我們再執行如下的SQL語句，看結果如何，代碼如下：

explain select * from t where a between 5000 and 15000 and b between 10000 and 100000 order by b limit 100

其結果如下所示：

使用b索引進行查詢

從這次的分析可以看出，MySQL最後選擇了b索引，掃描了50233行數據。從這結果來看，我們不難猜出MySQL選錯索引了。在MySQL中，order by 和 limit 結合使用，如果where 字段，order by字段上都有索引，那麼有limit時索引會使用order by字段所在的索引，沒有limit會使用where 條件的索引。造成這種原因主要是b字段上面存在索引，所以MySQL的優化器認為使用b索引可以避免排序，直接遍歷然後取出相應的limit的條數就可以了，所以這裡MySQL掃描的行數雖然多，但仍然被優化器認為是代價最小的。

在這裡的話，由於where條件中a的範圍要比b的要小，所以，這裡如果使用a作為索引的話，肯定查詢的速度會比b的快，所以，我們執行如下的SQL語句，進行分析下：

explain select * from t force index(a) where a between 5000 and 15000 and b between 10000 and 100000 order by b limit 100;

其結果如下圖所示：

使用a索引查詢

從兩圖可以看出，使用a索引所消耗的時間要比b索引所消耗的時間少。

優化

其實，MySQL在查詢的過程中，大部分時候索引的選擇都是正確的，只有偶爾會遇到以上的情況，那麼，如何解決以上問題的發生呢？

1. 使用force index的形式來矯正MySQL索引的選擇。不過，挺多程序員實際中並不會使用強制索引，主要是因為如果索引改了名字，這個語句也得改，顯得很麻煩。而且如果以後遷移到別的數據庫的話，這個語法還可能會不兼容
2. 修改MySQL語句，引導優化器去選擇索引。例如在上面的例子中，可以把 order by b limit 100 改成 order by b,a limit 100,這樣就意味著兩個索引都要排序，優化器就會選擇row行數比較少的索引。
3. 刪除無效的索引，或者新建更優的索引。

最後，喜歡的小夥伴可以給我點贊或者關注我哦。