來自公眾號(hào)：碼海
作者：碼海
前言
上篇 SQL 進(jìn)階技巧（下)中提到使用以下 sql 會(huì)導(dǎo)致慢查詢
SELECT COUNT(*) FROM SomeTable
SELECT COUNT(1) FROM SomeTable
原因是會(huì)造成全表掃描羞芍，有位讀者說這種說法是有問題的堂竟，實(shí)際上針對(duì)無 where_clause 的 COUNT()*魂毁，MySQL 是有優(yōu)化的，優(yōu)化器會(huì)選擇成本最小的輔助索引查詢計(jì)數(shù)出嘹，其實(shí)反而性能最高席楚，這位讀者的說法對(duì)不對(duì)呢
針對(duì)這個(gè)疑問，我首先去生產(chǎn)上找了一個(gè)千萬級(jí)別的表使用 EXPLAIN 來查詢了一下執(zhí)行計(jì)劃
EXPLAIN SELECT COUNT(*) FROM SomeTable
結(jié)果如下
如圖所示: 發(fā)現(xiàn)確實(shí)此條語句在此例中用到的并不是主鍵索引税稼，而是輔助索引烦秩，實(shí)際上在此例中我試驗(yàn)了垮斯，不管是 COUNT(1)，還是 COUNT()只祠，MySQL 都會(huì)用成本最小的輔助索引查詢方式來計(jì)數(shù)兜蠕，也就是使用 COUNT() 由于 MySQL 的優(yōu)化已經(jīng)保證了它的查詢性能是最好的！隨帶提一句抛寝，COUNT()是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法熊杨，并且效率高，所以請(qǐng)直接使用COUNT()查詢表的行數(shù)盗舰！
所以這位讀者的說法確實(shí)是對(duì)的晶府。但有個(gè)前提，在 MySQL 5.6 之后的版本中才有這種優(yōu)化钻趋。
那么這個(gè)成本最小該怎么定義呢川陆，有時(shí)候在 WHERE 中指定了多個(gè)條件，為啥最終 MySQL 執(zhí)行的時(shí)候卻選擇了另一個(gè)索引蛮位，甚至不選索引较沪？
本文將會(huì)給你答案，本文將會(huì)從以下兩方面來分析
- SQL 選用索引的執(zhí)行成本如何計(jì)算
- 實(shí)例說明
SQL 選用索引的執(zhí)行成本如何計(jì)算
就如前文所述失仁，在有多個(gè)索引的情況下尸曼， 在查詢數(shù)據(jù)前，MySQL 會(huì)選擇成本最小原則來選擇使用對(duì)應(yīng)的索引陶因，這里的成本主要包含兩個(gè)方面骡苞。
- IO 成本: 即從磁盤把數(shù)據(jù)加載到內(nèi)存的成本，默認(rèn)情況下楷扬，讀取數(shù)據(jù)頁(yè)的 IO 成本是 1解幽，MySQL 是以頁(yè)的形式讀取數(shù)據(jù)的，即當(dāng)用到某個(gè)數(shù)據(jù)時(shí)烘苹，并不會(huì)只讀取這個(gè)數(shù)據(jù)躲株，而會(huì)把這個(gè)數(shù)據(jù)相鄰的數(shù)據(jù)也一起讀到內(nèi)存中，這就是有名的程序局部性原理镣衡，所以 MySQL 每次會(huì)讀取一整頁(yè)霜定，一頁(yè)的成本就是 1。所以 IO 的成本主要和頁(yè)的大小有關(guān)
- CPU 成本：將數(shù)據(jù)讀入內(nèi)存后廊鸥，還要檢測(cè)數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本望浩，顯然它與行數(shù)有關(guān)，默認(rèn)情況下惰说，檢測(cè)記錄的成本是 0.2磨德。
實(shí)例說明
為了根據(jù)以上兩個(gè)成本來算出使用索引的最終成本，我們先準(zhǔn)備一個(gè)表（以下操作基于 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;
這個(gè)表除了主鍵索引之外，還有另外兩個(gè)索引, name_score 及 create_time典挑。然后我們?cè)诖吮碇胁迦?10 w 行數(shù)據(jù)酥宴，只要寫一個(gè)存儲(chǔ)過程調(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
插入之后我們現(xiàn)在使用 EXPLAIN 來計(jì)算下統(tǒng)計(jì)總行數(shù)到底使用的是哪個(gè)索引
EXPLAIN SELECT COUNT(*) FROM person
從結(jié)果上看它選擇了 create_time 輔助索引您觉，顯然 MySQL 認(rèn)為使用此索引進(jìn)行查詢成本最小拙寡，這也是符合我們的預(yù)期，使用輔助索引來查詢確實(shí)是性能最高的琳水！
我們?cè)賮砜匆韵?SQL 會(huì)使用哪個(gè)索引
SELECT * FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
用了全表掃描肆糕！理論上應(yīng)該用 name_score 或者 create_time 索引才對(duì)，從 WHERE 的查詢條件來看確實(shí)都能命中索引在孝，那是否是使用 **SELECT *** 造成的回表代價(jià)太大所致呢擎宝，我們改成覆蓋索引的形式試一下
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time > '2020-05-23 14:39:18'
結(jié)果 MySQL 依然選擇了全表掃描！這就比較有意思了浑玛，理論上采用了覆蓋索引的方式進(jìn)行查找性能肯定是比全表掃描更好的，為啥 MySQL 選擇了全表掃描呢噩咪，既然它認(rèn)為全表掃描比使用覆蓋索引的形式性能更好顾彰，那我們分別用這兩者執(zhí)行來比較下查詢時(shí)間吧
-- 全表掃描執(zhí)行時(shí)間: 4.0 ms
SELECT create_time FROM person WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
-- 使用覆蓋索引執(zhí)行時(shí)間: 2.0 ms
SELECT create_time FROM person force index(create_time) WHERE NAME >'name84059' AND create_time>'2020-05-23 14:39:18'
從實(shí)際執(zhí)行的效果看使用覆蓋索引查詢比使用全表掃描執(zhí)行的時(shí)間快了一倍！說明 MySQL 在查詢前做的成本估算不準(zhǔn)胃碾！我們先來看看 MySQL 做全表掃描的成本有多少涨享。
前面我們說了成本主要 IO 成本和 CPU 成本有關(guān)，對(duì)于全表掃描來說也就是分別和聚簇索引占用的頁(yè)面數(shù)和表中的記錄數(shù)仆百。執(zhí)行以下命令
SHOW TABLE STATUS LIKE 'person'
可以發(fā)現(xiàn)
行數(shù)是 100264厕隧，我們不是插入了 10 w 行的數(shù)據(jù)了嗎，怎么算出的數(shù)據(jù)反而多了俄周，其實(shí)這里的計(jì)算是估算吁讨，也有可能這里的行數(shù)統(tǒng)計(jì)出來比 10 w 少了，估算方式有興趣大家去網(wǎng)上查找峦朗，這里不是本文重點(diǎn)建丧，就不展開了。得知行數(shù)波势，那我們知道 CPU 成本是 100264 * 0.2 = 20052.8翎朱。
數(shù)據(jù)長(zhǎng)度是 5783552，InnoDB 每個(gè)頁(yè)面的大小是 16 KB尺铣，可以算出頁(yè)面數(shù)量是 353拴曲。
也就是說全表掃描的成本是 20052.8 + 353 = 20406。
這個(gè)結(jié)果對(duì)不對(duì)呢凛忿，我們可以用一個(gè)工具驗(yàn)證一下澈灼。在 MySQL 5.6 及之后的版本中，我們可以用 optimizer trace 功能來查看優(yōu)化器生成計(jì)劃的整個(gè)過程 侄非，它列出了選擇每個(gè)索引的執(zhí)行計(jì)劃成本以及最終的選擇結(jié)果蕉汪，我們可以依賴這些信息來進(jìn)一步優(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";
執(zhí)行之后我們主要觀察使用 name_score，create_time 索引及全表掃描的成本者疤。
先來看下使用 name_score 索引執(zhí)行的的預(yù)估執(zhí)行成本:
{
"index": "name_score",
"ranges": [
"name84059 <= name"
],
"index_dives_for_eq_ranges": true,
"rows": 25372,
"cost": 30447
}
可以看到執(zhí)行成本為 30447福澡，高于我們之前算出來的全表掃描成本：20406。所以沒選擇此索引執(zhí)行
注意：這里的 30447 是查詢二級(jí)索引的 IO 成本和 CPU 成本之和驹马，再加上回表查詢聚簇索引的 IO 成本和 CPU 成本之和革砸。
再來看下使用 create_time 索引執(zhí)行的的預(yù)估執(zhí)行成本:
{
"index": "create_time",
"ranges": [
"0x5ec8c516 < create_time"
],
"index_dives_for_eq_ranges": true,
"rows": 50132,
"cost": 60159,
"cause": "cost"
}
可以看到成本是 60159,遠(yuǎn)大于全表掃描成本 20406，自然也沒選擇此索引糯累。
再來看計(jì)算出的全表掃描成本：
{
"considered_execution_plans": [
{
"plan_prefix": [
],
"table": "`person`",
"best_access_path": {
"considered_access_paths": [
{
"rows_to_scan": 100264,
"access_type": "scan",
"resulting_rows": 100264,
"cost": 20406,
"chosen": true
}
]
},
"condition_filtering_pct": 100,
"rows_for_plan": 100264,
"cost_for_plan": 20406,
"chosen": true
}
]
}
注意看 cost：20406算利，與我們之前算出來的完全一樣！這個(gè)值在以上三者算出的執(zhí)行成本中最小泳姐，所以最終 MySQL 選擇了用全表掃描的方式來執(zhí)行此 SQL效拭。
實(shí)際上 optimizer trace 詳細(xì)列出了覆蓋索引，回表的成本統(tǒng)計(jì)情況胖秒，有興趣的可以去研究一下缎患。
從以上分析可以看出， MySQL 選擇的執(zhí)行計(jì)劃未必是最佳的阎肝，原因有挺多挤渔，就比如上文說的行數(shù)統(tǒng)計(jì)信息不準(zhǔn)，再比如 MySQL 認(rèn)為的最優(yōu)跟我們認(rèn)為不一樣风题，我們可以認(rèn)為執(zhí)行時(shí)間短的是最優(yōu)的判导，但 MySQL 認(rèn)為的成本小未必意味著執(zhí)行時(shí)間短。
總結(jié)
本文通過一個(gè)例子深入剖析了 MySQL 的執(zhí)行計(jì)劃是如何選擇的沛硅，以及為什么它的選擇未必是我們認(rèn)為的最優(yōu)的眼刃，這也提醒我們，在生產(chǎn)中如果有多個(gè)索引的情況稽鞭，使用 WHERE 進(jìn)行過濾未必會(huì)選中你認(rèn)為的索引鸟整，我們可以提前使用 EXPLAIN, optimizer trace 來優(yōu)化我們的查詢語句。
