前言

SELECTCOUNT(*)會不會導(dǎo)致全表掃描引起慢查詢呢？

SELECTCOUNT(*)FROMSomeTable

網(wǎng)上有一種說法，針對無where_clause的COUNT(*)，MySQL 是有優(yōu)化的，優(yōu)化器會選擇成本最小的輔助索引查詢計(jì)數(shù)，其實(shí)反而性能最高，這種說法對不對呢

針對這個疑問，我首先去生產(chǎn)上找了一個千萬級別的表使用 EXPLAIN來查詢了一下執(zhí)行計(jì)劃

EXPLAINSELECTCOUNT(*)FROMSomeTable

結(jié)果如下

如圖所示: 發(fā)現(xiàn)確實(shí)此條語句在此例中用到的并不是主鍵索引，而是輔助索引，實(shí)際上在此例中我試驗(yàn)了，不管是COUNT(1)，還是COUNT(*)，MySQL 都會用成本最小的輔助索引查詢方式來計(jì)數(shù)，也就是使用COUNT(*)由于 MySQL 的優(yōu)化已經(jīng)保證了它的查詢性能是最好的！隨帶提一句，COUNT(*)是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語法，并且效率高，所以請直接使用COUNT(*)查詢表的行數(shù)！

所以這種說法確實(shí)是對的。但有個前提，在 MySQL 5.6 之后的版本中才有這種優(yōu)化。

那么這個成本最小該怎么定義呢，有時候在 WHERE 中指定了多個條件，為啥最終 MySQL 執(zhí)行的時候卻選擇了另一個索引，甚至不選索引？

本文將會給你答案，本文將會從以下兩方面來分析

• SQL 選用索引的執(zhí)行成本如何計(jì)算

• 實(shí)例說明

SQL 選用索引的執(zhí)行成本如何計(jì)算

就如前文所述，在有多個索引的情況下， 在查詢數(shù)據(jù)前，MySQL 會選擇成本最小原則來選擇使用對應(yīng)的索引，這里的成本主要包含兩個方面。

• IO 成本: 即從磁盤把數(shù)據(jù)加載到內(nèi)存的成本，默認(rèn)情況下，讀取數(shù)據(jù)頁的 IO 成本是 1，MySQL 是以頁的形式讀取數(shù)據(jù)的，即當(dāng)用到某個數(shù)據(jù)時，并不會只讀取這個數(shù)據(jù)，而會把這個數(shù)據(jù)相鄰的數(shù)據(jù)也一起讀到內(nèi)存中，這就是有名的程序局部性原理，所以 MySQL 每次會讀取一整頁，一頁的成本就是 1。所以 IO 的成本主要和頁的大小有關(guān)

• CPU 成本：將數(shù)據(jù)讀入內(nèi)存后，還要檢測數(shù)據(jù)是否滿足條件和排序等 CPU 操作的成本，顯然它與行數(shù)有關(guān)，默認(rèn)情況下，檢測記錄的成本是 0.2。

實(shí)例說明

為了根據(jù)以上兩個成本來算出使用索引的最終成本，我們先準(zhǔn)備一個表（以下操作基于 MySQL 5.7.18）

CREATETABLE`person`(
`id`bigint(20)NOTNULLAUTO_INCREMENT,
`name`varchar(255)NOTNULL,
`score`int(11)NOTNULL,
`create_time`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP,
PRIMARYKEY(`id`),
KEY`name_score`(`name`(191),`score`),
KEY`create_time`(`create_time`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8mb4;

這個表除了主鍵索引之外，還有另外兩個索引,name_score及create_time。然后我們在此表中插入 10 w 行數(shù)據(jù)，只要寫一個存儲過程調(diào)用即可，如下:

CREATEPROCEDUREinsert_person()
begin
declarec_idintegerdefault1;
whilec_id<=100000?do
insertintopersonvalues(c_id,concat('name',c_id),c_id+100,date_sub(NOW(),intervalc_idsecond));
setc_id=c_id+1;
endwhile;
end

插入之后我們現(xiàn)在使用 EXPLAIN 來計(jì)算下統(tǒng)計(jì)總行數(shù)到底使用的是哪個索引

EXPLAINSELECTCOUNT(*)FROMperson

從結(jié)果上看它選擇了create_time輔助索引，顯然 MySQL 認(rèn)為使用此索引進(jìn)行查詢成本最小，這也是符合我們的預(yù)期，使用輔助索引來查詢確實(shí)是性能最高的！

我們再來看以下 SQL 會使用哪個索引

SELECT*FROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'

用了全表掃描！理論上應(yīng)該用name_score或者create_time索引才對，從 WHERE 的查詢條件來看確實(shí)都能命中索引，那是否是使用SELECT *造成的回表代價太大所致呢，我們改成覆蓋索引的形式試一下

SELECTcreate_timeFROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'

結(jié)果 MySQL 依然選擇了全表掃描！這就比較有意思了，理論上采用了覆蓋索引的方式進(jìn)行查找性能肯定是比全表掃描更好的，為啥 MySQL 選擇了全表掃描呢，既然它認(rèn)為全表掃描比使用覆蓋索引的形式性能更好，那我們分別用這兩者執(zhí)行來比較下查詢時間吧

--全表掃描執(zhí)行時間:4.0ms
SELECTcreate_timeFROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'

--使用覆蓋索引執(zhí)行時間:2.0ms
SELECTcreate_timeFROMpersonforceindex(create_time)WHERENAME>'name84059'ANDcreate_time>'2020-05-231418'

從實(shí)際執(zhí)行的效果看使用覆蓋索引查詢比使用全表掃描執(zhí)行的時間快了一倍！說明 MySQL 在查詢前做的成本估算不準(zhǔn)！我們先來看看 MySQL 做全表掃描的成本有多少。

前面我們說了成本主要 IO 成本和 CPU 成本有關(guān)，對于全表掃描來說也就是分別和聚簇索引占用的頁面數(shù)和表中的記錄數(shù)。執(zhí)行以下命令

SHOWTABLESTATUSLIKE'person'

可以發(fā)現(xiàn)

1. 行數(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。

2. 數(shù)據(jù)長度是 5783552，InnoDB 每個頁面的大小是 16 KB，可以算出頁面數(shù)量是 353。

也就是說全表掃描的成本是20052.8 + 353 = 20406。

這個結(jié)果對不對呢，我們可以用一個工具驗(yàn)證一下。在 MySQL 5.6 及之后的版本中，我們可以用optimizer trace功能來查看優(yōu)化器生成計(jì)劃的整個過程 ，它列出了選擇每個索引的執(zhí)行計(jì)劃成本以及最終的選擇結(jié)果，我們可以依賴這些信息來進(jìn)一步優(yōu)化我們的 SQL。

optimizer_trace功能使用如下

SEToptimizer_trace="enabled=on";
SELECTcreate_timeFROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418';
SELECT*FROMinformation_schema.OPTIMIZER_TRACE;
SEToptimizer_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 是查詢二級索引的 IO 成本和 CPU 成本之和，再加上回表查詢聚簇索引的 IO 成本和 CPU 成本之和。

再來看下使用create_time索引執(zhí)行的的預(yù)估執(zhí)行成本:

{
"index":"create_time",
"ranges":[
"0x5ec8c516
],
"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，與我們之前算出來的完全一樣！這個值在以上三者算出的執(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í)行時間短的是最優(yōu)的，但 MySQL 認(rèn)為的成本小未必意味著執(zhí)行時間短。

總結(jié)

本文通過一個例子深入剖析了 MySQL 的執(zhí)行計(jì)劃是如何選擇的，以及為什么它的選擇未必是我們認(rèn)為的最優(yōu)的，這也提醒我們，在生產(chǎn)中如果有多個索引的情況，使用 WHERE 進(jìn)行過濾未必會選中你認(rèn)為的索引，我們可以提前使用 EXPLAIN,optimizer trace來優(yōu)化我們的查詢語句。

審核編輯 ：李倩