CPU 成本：將數(shù)據(jù)讀入內(nèi)存后，還要檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本，顯然它與行數(shù)有關(guān)，默認情況下，檢測記錄的成本是 0.2。
實例說明
為了根據(jù)以上兩個成本來算出使用索引的最終成本，我們先準備一個表（以下操作基于 MySQL 5.7.18）
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 DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
KEY name_score (name(191),score),
KEY create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
這個表除了主鍵索引之外，還有另外兩個索引, name_score 及 create_time。然后我們在此表中插入 10 w 行數(shù)據(jù)，只要寫一個存儲過程調(diào)用即可，如下:
CREATE 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));
set c_id=c_id+1;
end while;
end
從結(jié)果上看它選擇了 create_time 輔助索引，顯然 MySQL 認為使用此索引進行查詢成本最小，這也是符合我們的預期，使用輔助索引來查詢確實是性能最高的！
我們再來看以下 SQL 會使用哪個索引
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
用了全表掃描！理論上應(yīng)該用 name_score 或者 create_time 索引才對，從 WHERE 的查詢條件來看確實都能命中索引，那是否是使用 SELECT * 造成的回表代價太大所致呢，我們改成覆蓋索引的形式試一下
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'
結(jié)果 MySQL 依然選擇了全表掃描！這就比較有意思了，理論上采用了覆蓋索引的方式進行查找性能肯定是比全表掃描更好的，為啥 MySQL 選擇了全表掃描呢，既然它認為全表掃描比使用覆蓋索引的形式性能更好，那我們分別用這兩者執(zhí)行來比較下查詢時間吧
-- 全表掃描執(zhí)行時間: 4.0 ms
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
-- 使用覆蓋索引執(zhí)行時間: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
從實際執(zhí)行的效果看使用覆蓋索引查詢比使用全表掃描執(zhí)行的時間快了一倍！說明 MySQL 在查詢前做的成本估算不準！我們先來看看 MySQL 做全表掃描的成本有多少。
前面我們說了成本主要 IO 成本和 CPU 成本有關(guān)，對于全表掃描來說也就是分別和聚簇索引占用的頁面數(shù)和表中的記錄數(shù)。執(zhí)行以下命令
SHOW TABLE STATUS LIKE 'person'
可以發(fā)現(xiàn)
行數(shù)是 100264，我們不是插入了 10 w 行的數(shù)據(jù)了嗎，怎么算出的數(shù)據(jù)反而多了，其實這里的計算是估算，也有可能這里的行數(shù)統(tǒng)計出來比 10 w 少了，估算方式有興趣大家去網(wǎng)上查找，這里不是本文重點，就不展開了。得知行數(shù)，那我們知道 CPU 成本是 100264 * 0.2 = 20052.8。
這個結(jié)果對不對呢，我們可以用一個工具驗證一下。在 MySQL 5.6 及之后的版本中，我們可以用 optimizer trace 功能來查看優(yōu)化器生成計劃的整個過程 ，它列出了選擇每個索引的執(zhí)行計劃成本以及最終的選擇結(jié)果，我們可以依賴這些信息來進一步優(yōu)化我們的 SQL。
optimizer_trace 功能使用如下
SET optimizer_trace="enabled=on";
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18';
SELECT * FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";
從以上分析可以看出， MySQL 選擇的執(zhí)行計劃未必是最佳的，原因有挺多，就比如上文說的行數(shù)統(tǒng)計信息不準，再比如 MySQL 認為的最優(yōu)跟我們認為不一樣，我們可以認為執(zhí)行時間短的是最優(yōu)的，但 MySQL 認為的成本小未必意味著執(zhí)行時間短。
總結(jié)
本文通過一個例子深入剖析了 MySQL 的執(zhí)行計劃是如何選擇的，以及為什么它的選擇未必是我們認為的最優(yōu)的，這也提醒我們，在生產(chǎn)中如果有多個索引的情況，使用 WHERE 進行過濾未必會選中你認為的索引，我們可以提前使用 EXPLAIN, optimizer trace 來優(yōu)化我們的查詢語句。
