談到索引失效，大家可能都能列舉出幾個場景，比如：后模糊查詢、條件中帶函數、索引中斷等等。今天我想和你分享另一個場景：索引成本分析。
我先用一個具體的例子來描述一下這個場景。
案例場景
假設現在我們有一張人物表，建表語句如下：
create TABLE `person` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`score` int(11) NOT NULL,
`create_time` timestamp NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
并創建兩個索引：
KEY `name_score` (`name`,`score`) USING BTREE,
KEY `create_time` (`create_time`) USING BTREE
然后插入 10 萬條數據：
create DEFINER=`root`@`%` PROCEDURE `insert_person`()
begin
declare c_id integer default 1;
while c_id<=100000 do
insert into person values(c_id, concat('name',c_id), c_id+100, date_sub(NOW(), interval c_id second));
-- 需要注意，因為使用的是now()，所以對于后續的例子，使用文中的SQL你需要自己調整條件，否則可能看不到文中的效果
set c_id=c_id+1;
end while;
end
數據插入后，我們用下面的 SQL 進行查詢：
explain select * from person where NAME>'name84059' and create_time>'2020-01-24 05:00:00'
通過上面的執行計劃可以看到：type=All，說明是全表掃描。
接著我們把 create_time 條件中的 5 點改為 6 點：
explain select * from person where NAME>'name84059' and create_time>'2020-01-24 06:00:00'
執行計劃顯示：type=range，key=create_time，走了 create_time 索引，而不是 name_score 聯合索引。
看到這里，你是不是很詫異？接下來，我們就一起來分析一下這背后的原因。
原因分析
MySQL 在查詢數據之前，會先對可能的方案做執行計劃，然后依據成本決定走哪個執行計劃。這里的成本，包括 IO 成本和 CPU 成本：
- • IO 成本，是從磁盤把數據加載到內存的成本。默認情況下，讀取數據頁的 IO 成本常數是 1（也就是讀取 1 個頁成本是 1）。
- • CPU 成本，是檢測數據是否滿足條件和排序等 CPU 操作的成本。默認情況下，檢測記錄的成本是 0.2。
MySQL 維護了表的統計信息，可以使用下面的命令查看：
SHOW TABLE STATUS LIKE 'person'
從圖中可以看到，總行數是 100086 行，由于 MySQL 的統計信息是一個估算，這里多了 86 行是正常的。CPU 成本是 100086*0.2=20017 左右。
數據長度是 4734976 字節。對于 InnoDB 來說，4734976 就是聚簇索引占用的空間，等于聚簇索引的頁數量 * 每個頁面的大小。InnoDB 每個頁面的大小是 16KB，大概計算出頁的數量是 289，因此 IO 成本是 289 左右。
所以，全表掃描的總成本是 20306 左右。
在 MySQL 5.6 及之后的版本中，我們可以使用 optimizer trace 功能查看優化器生成執行計劃的整個過程。
SET optimizer_trace="enabled=on";
explain select * from person where NAME >'name84059' and create_time>'2020-01-24 05:00:00';
select * from information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
對于按照 create_time>'2020-01-24 05:00:00’ 條件走全表掃描的 SQL，我從 OPTIMIZER_TRACE 的執行結果中，摘出了幾個重要片段來重點分析：
1、使用 name_score 對 name84059<name 條件進行索引掃描需要掃描 25362 行，成本是 30435。
{
"index": "name_score",
"ranges": [
"name84059 < name"
],
"rows": 25362,
"cost": 30435,
"chosen": false,
"cause": "cost"
}
30435 是查詢二級索引的 IO 成本和 CPU 成本之和，再加上回表查詢聚簇索引的 IO 成本和 CPU 成本之和。
2、使用 create_time 進行索引掃描需要掃描 23758 行，成本是 28511。
{
"index": "create_time",
"ranges": [
"0x5e2a79d0 < create_time"
],
"rows": 23758,
"cost": 28511,
"chosen": false,
"cause": "cost"
}
3、全表掃描 100086 條記錄的成本是 20306。（和上面計算的一致）
{
"considered_execution_plans": [{
"table": "`person`",
"best_access_path": {
"considered_access_paths": [{
"rows_to_scan": 100086,
"access_type": "scan",
"resulting_rows": 100086,
"cost": 20306,
"chosen": true
}]
},
"rows_for_plan": 100086,
"cost_for_plan": 20306,
"chosen": true
}]
}
所以 MySQL 最終選擇了全表掃描方式作為執行計劃。
把 SQL 中的 create_time 條件從 05:00 改為 06:00，再次分析 OPTIMIZER_TRACE 可以看到：
{
"index": "create_time",
"ranges": [
"0x5e2a87e0 < create_time"
],
"rows": 16588,
"cost": 19907,
"chosen": true
}
因為是查詢更晚時間的數據，走 create_time 索引需要掃描的行數從 23758 減少到了 16588。這次走這個索引的成本 19907 小于全表掃描的 20306，更小于走 name_score 索引的 30435。
所以這次執行計劃選擇的是走 create_time 索引。
解決方案
有時會因為統計信息的不準確或成本估算的問題，實際開銷會和 MySQL 統計出來的差距較大，導致 MySQL 選擇錯誤的索引或是直接選擇走全表掃描，這個時候就需要人工干預，使用強制索引了。
比如，像這樣強制走 name_score 索引：
explain select * from person FORCE INDEX(name_score) where NAME >'name84059' and create_time>'2020-01-24 00:00:00'
小結
本文通過一個例子，談到了 MySQL 還有另外一個索引失效的場景，即分析器成本分析。
對于是否走索引，我們要學會使用 explain 進行分析。另外，在 MySQL 5.6 及之后的版本中，我們可以使用 optimizer trace 功能查看優化器生成執行計劃的整個過程。
整理自極客時間《JAVA開發常見錯誤》學習筆記
公眾號：楊同學technotes
