在實際的生產(chǎn)環(huán)境中，給MySQL的字段加索引讓查詢的效率更高，我們往往會給MySQL的表字段加索引。MySQL具體選擇哪個索引是有MySQL的優(yōu)化器決定的。不知道小伙伴們有沒有遇到一種情況，就是實際的查詢中速度應(yīng)該很快才對的，但因為MySQL選錯的索引，導(dǎo)致查詢的速度變慢了。至此，我們來看一下下面的例子。
牛刀小試
首先，我們簡單創(chuàng)建一張表，表里面有兩個字段，分別是a和b，并且我們給a和b加上索引，SQL語句執(zhí)行如下：
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萬條數(shù)據(jù)，此處使用存儲過程，代碼如下所示：
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();
然后我們使用執(zhí)行計劃運行一條簡單的SQL語句，代碼如下：
explain select * from t where a between 15000 and 20000
運行之后，如下所示：
可以看到，執(zhí)行這條語句的時候，MySQL使用了a上面的索引，只掃描(row)了5000條記錄。想必這樣的結(jié)果大家都是可以猜到的。
在這里，也許小伙伴們就會有疑問了，MySQL是怎么計算出row數(shù)的？其實，MySQL大部分時候并不能很精準(zhǔn)的統(tǒng)計出這個row數(shù)，MySQL在這里主要采用的方式是抽樣的方式進行計算的，即 row數(shù) = （N個數(shù)據(jù)頁不同值個數(shù)平均值） ×索引 頁數(shù)。一個索引上不同的值越多，這個索引的區(qū)分度就越好。而一個索引上的不同值的個數(shù)就被稱為基數(shù)。所以，row數(shù)其實嚴(yán)格意義上來說是不會100%精準(zhǔn)的。
那么，我們再執(zhí)行如下的SQL語句，看結(jié)果如何，代碼如下：
explain select * from t where a between 5000 and 15000 and b between 10000 and 100000 order by b limit 100
其結(jié)果如下所示：
從這次的分析可以看出，MySQL最后選擇了b索引，掃描了50233行數(shù)據(jù)。從這結(jié)果來看，我們不難猜出MySQL選錯索引了。在MySQL中，order by 和 limit 結(jié)合使用，如果where 字段，order by字段上都有索引，那么有l(wèi)imit時索引會使用order by字段所在的索引，沒有l(wèi)imit會使用where 條件的索引。造成這種原因主要是b字段上面存在索引，所以MySQL的優(yōu)化器認(rèn)為使用b索引可以避免排序，直接遍歷然后取出相應(yīng)的limit的條數(shù)就可以了，所以這里MySQL掃描的行數(shù)雖然多，但仍然被優(yōu)化器認(rèn)為是代價最小的。
在這里的話，由于where條件中a的范圍要比b的要小，所以，這里如果使用a作為索引的話，肯定查詢的速度會比b的快，所以，我們執(zhí)行如下的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;
其結(jié)果如下圖所示：
從兩圖可以看出，使用a索引所消耗的時間要比b索引所消耗的時間少。
優(yōu)化
其實，MySQL在查詢的過程中，大部分時候索引的選擇都是正確的，只有偶爾會遇到以上的情況，那么，如何解決以上問題的發(fā)生呢？
1. 使用force index的形式來矯正MySQL索引的選擇。不過，挺多程序員實際中并不會使用強制索引，主要是因為如果索引改了名字，這個語句也得改，顯得很麻煩。而且如果以后遷移到別的數(shù)據(jù)庫的話，這個語法還可能會不兼容
2. 修改MySQL語句，引導(dǎo)優(yōu)化器去選擇索引。例如在上面的例子中，可以把 order by b limit 100 改成 order by b,a limit 100,這樣就意味著兩個索引都要排序，優(yōu)化器就會選擇row行數(shù)比較少的索引。
3. 刪除無效的索引，或者新建更優(yōu)的索引。
