選錯索引的原因
選擇索引是優(yōu)化器的工作。而優(yōu)化器選擇索引的目的，是找到一個最優(yōu)的執(zhí)行方案，并用最小的代價去執(zhí)行語句。在數(shù)據(jù)庫里面，掃描行數(shù)是影響執(zhí)行代價的因素之一。掃描的行數(shù)越少，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源越少。
當然，掃描行數(shù)并不是唯一的判斷標準，優(yōu)化器還會結合是否使用臨時表、是否排序，是否需要回表查詢等因素進行綜合判斷。
新建一張表
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；
然后，我們往表 t 中插入 10 萬行記錄，取值按整數(shù)遞增，即：(1,1,1)，(2,2,2)，(3,3,3) 直到 (100000,100000,100000)。執(zhí)行如下語句：
看一個例子：
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
從條件上看，這個查詢沒有符合條件的記錄，因此會返回空集合。如果你來選擇索引，會選擇哪一個呢？
如果使用索引 a 進行查詢，那么就是掃描索引 a 的前 1000 個值，然后取到對應的 id，再到主鍵索引上去查出每一行，然后根據(jù)字段 b 來過濾。顯然這樣需要掃描 1000 行。
如果使用索引 b 進行查詢，那么就是掃描索引 b 的最后 50001 個值，與上面的執(zhí)行過程相同，也是需要回到主鍵索引上取值再判斷，所以需要掃描 50001 行。所以你一定會想，如果使用索引 a 的話，執(zhí)行速度明顯會快很多。那么，下面我們就來看看到底是不是這么一回事兒。
通過explain命令查看語句執(zhí)行情況
key的這一列，表示的是優(yōu)化器選擇的索引列?？梢钥吹?，返回結果中 key 字段顯示，這次優(yōu)化器選擇了索引 b，而 rows 字段顯示需要掃描的行數(shù)是 50198。
這里優(yōu)化器選擇使用索引 b，是因為它認為使用索引 b 可以避免排序（b 本身是索引，已經(jīng)是有序的了，如果選擇索引 b 的話，不需要再做排序，只需要遍歷），所以即使掃描行數(shù)多，也判定為代價更小。
解決方法
對于由于索引統(tǒng)計信息不準確導致的問題，通過analyze table tableName命令，可以重新統(tǒng)計索引信息，這個可以解決大部分情況的問題。使用之后再次使用explain命令查看。
而對于其他優(yōu)化器誤判的情況，可以在應用端用 force index 來強行指定索引，也可以通過修改語句來引導優(yōu)化器，還可以通過增加或者刪除索引來繞過這個問題。
set long_query_time=0;
select * from t where a between 10000 and 20000;
//強行是下面的語句走a這個索引。
select * from t force index(a) where a between 10000 and 20000;
