前言
SELECTCOUNT(*)
會(huì)不會(huì)導(dǎo)致全表掃描引起慢查詢呢?
SELECTCOUNT(*)FROMSomeTable
網(wǎng)上有一種說(shuō)法,針對(duì)無(wú)where_clause
的COUNT(*)
,MySQL 是有優(yōu)化的,優(yōu)化器會(huì)選擇成本最小的輔助索引查詢計(jì)數(shù),其實(shí)反而性能最高,這種說(shuō)法對(duì)不對(duì)呢
針對(duì)這個(gè)疑問(wèn),我首先去生產(chǎn)上找了一個(gè)千萬(wàn)級(jí)別的表使用 EXPLAIN
來(lái)查詢了一下執(zhí)行計(jì)劃
EXPLAINSELECTCOUNT(*)FROMSomeTable
結(jié)果如下
如圖所示: 發(fā)現(xiàn)確實(shí)此條語(yǔ)句在此例中用到的并不是主鍵索引,而是輔助索引,實(shí)際上在此例中我試驗(yàn)了,不管是COUNT(1)
,還是COUNT(*)
,MySQL 都會(huì)用成本最小的輔助索引查詢方式來(lái)計(jì)數(shù),也就是使用COUNT(*)
由于 MySQL 的優(yōu)化已經(jīng)保證了它的查詢性能是最好的!隨帶提一句,COUNT(*)
是 SQL92 定義的標(biāo)準(zhǔn)統(tǒng)計(jì)行數(shù)的語(yǔ)法,并且效率高,所以請(qǐng)直接使用COUNT(*)
查詢表的行數(shù)!
所以這種說(shuō)法確實(shí)是對(duì)的。但有個(gè)前提,在 MySQL 5.6 之后的版本中才有這種優(yōu)化。
那么這個(gè)成本最小該怎么定義呢,有時(shí)候在 WHERE 中指定了多個(gè)條件,為啥最終 MySQL 執(zhí)行的時(shí)候卻選擇了另一個(gè)索引,甚至不選索引?
本文將會(huì)給你答案,本文將會(huì)從以下兩方面來(lái)分析
-
SQL 選用索引的執(zhí)行成本如何計(jì)算
-
實(shí)例說(shuō)明
SQL 選用索引的執(zhí)行成本如何計(jì)算
就如前文所述,在有多個(gè)索引的情況下, 在查詢數(shù)據(jù)前,MySQL 會(huì)選擇成本最小原則來(lái)選擇使用對(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í)例說(shuō)明
為了根據(jù)以上兩個(gè)成本來(lái)算出使用索引的最終成本,我們先準(zhǔn)備一個(gè)表(以下操作基于 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;
這個(gè)表除了主鍵索引之外,還有另外兩個(gè)索引,name_score
及create_time
。然后我們?cè)诖吮碇胁迦?10 w 行數(shù)據(jù),只要寫一個(gè)存儲(chǔ)過(guò)程調(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 來(lái)計(jì)算下統(tǒng)計(jì)總行數(shù)到底使用的是哪個(gè)索引
EXPLAINSELECTCOUNT(*)FROMperson
從結(jié)果上看它選擇了create_time
輔助索引,顯然 MySQL 認(rèn)為使用此索引進(jìn)行查詢成本最小,這也是符合我們的預(yù)期,使用輔助索引來(lái)查詢確實(shí)是性能最高的!
我們?cè)賮?lái)看以下 SQL 會(huì)使用哪個(gè)索引
SELECT*FROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'
用了全表掃描!理論上應(yīng)該用name_score
或者create_time
索引才對(duì),從 WHERE 的查詢條件來(lái)看確實(shí)都能命中索引,那是否是使用SELECT *
造成的回表代價(jià)太大所致呢,我們改成覆蓋索引的形式試一下
SELECTcreate_timeFROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'
結(jié)果 MySQL 依然選擇了全表掃描!這就比較有意思了,理論上采用了覆蓋索引的方式進(jìn)行查找性能肯定是比全表掃描更好的,為啥 MySQL 選擇了全表掃描呢,既然它認(rèn)為全表掃描比使用覆蓋索引的形式性能更好,那我們分別用這兩者執(zhí)行來(lái)比較下查詢時(shí)間吧
--全表掃描執(zhí)行時(shí)間:4.0ms
SELECTcreate_timeFROMpersonWHERENAME>'name84059'ANDcreate_time>'2020-05-231418'
--使用覆蓋索引執(zhí)行時(shí)間:2.0ms
SELECTcreate_timeFROMpersonforceindex(create_time)WHERENAME>'name84059'ANDcreate_time>'2020-05-231418'
從實(shí)際執(zhí)行的效果看使用覆蓋索引查詢比使用全表掃描執(zhí)行的時(shí)間快了一倍!說(shuō)明 MySQL 在查詢前做的成本估算不準(zhǔn)!我們先來(lái)看看 MySQL 做全表掃描的成本有多少。
前面我們說(shuō)了成本主要 IO 成本和 CPU 成本有關(guān),對(duì)于全表掃描來(lái)說(shuō)也就是分別和聚簇索引占用的頁(yè)面數(shù)和表中的記錄數(shù)。執(zhí)行以下命令
SHOWTABLESTATUSLIKE'person'
可以發(fā)現(xiàn)
-
行數(shù)是 100264,我們不是插入了 10 w 行的數(shù)據(jù)了嗎,怎么算出的數(shù)據(jù)反而多了,其實(shí)這里的計(jì)算是估算,也有可能這里的行數(shù)統(tǒng)計(jì)出來(lái)比 10 w 少了,估算方式有興趣大家去網(wǎng)上查找,這里不是本文重點(diǎn),就不展開(kāi)了。得知行數(shù),那我們知道 CPU 成本是
100264 * 0.2 = 20052.8
。 -
數(shù)據(jù)長(zhǎng)度是 5783552,InnoDB 每個(gè)頁(yè)面的大小是 16 KB,可以算出頁(yè)面數(shù)量是 353。
也就是說(shuō)全表掃描的成本是20052.8 + 353 = 20406
。
這個(gè)結(jié)果對(duì)不對(duì)呢,我們可以用一個(gè)工具驗(yàn)證一下。在 MySQL 5.6 及之后的版本中,我們可以用optimizer trace
功能來(lái)查看優(yōu)化器生成計(jì)劃的整個(gè)過(guò)程 ,它列出了選擇每個(gè)索引的執(zhí)行計(jì)劃成本以及最終的選擇結(jié)果,我們可以依賴這些信息來(lái)進(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
索引及全表掃描的成本。
先來(lái)看下使用name_score
索引執(zhí)行的的預(yù)估執(zhí)行成本:
{
"index":"name_score",
"ranges":[
"name84059<=?name"
],
"index_dives_for_eq_ranges":true,
"rows":25372,
"cost":30447
}
可以看到執(zhí)行成本為 30447,高于我們之前算出來(lái)的全表掃描成本:20406。所以沒(méi)選擇此索引執(zhí)行
注意:這里的 30447 是查詢二級(jí)索引的 IO 成本和 CPU 成本之和,再加上回表查詢聚簇索引的 IO 成本和 CPU 成本之和。
再來(lái)看下使用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,自然也沒(méi)選擇此索引。
再來(lái)看計(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,與我們之前算出來(lái)的完全一樣!這個(gè)值在以上三者算出的執(zhí)行成本中最小,所以最終 MySQL 選擇了用全表掃描的方式來(lái)執(zhí)行此 SQL。
實(shí)際上optimizer trace
詳細(xì)列出了覆蓋索引,回表的成本統(tǒng)計(jì)情況,有興趣的可以去研究一下。
從以上分析可以看出, MySQL 選擇的執(zhí)行計(jì)劃未必是最佳的,原因有挺多,就比如上文說(shuō)的行數(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é)
本文通過(guò)一個(gè)例子深入剖析了 MySQL 的執(zhí)行計(jì)劃是如何選擇的,以及為什么它的選擇未必是我們認(rèn)為的最優(yōu)的,這也提醒我們,在生產(chǎn)中如果有多個(gè)索引的情況,使用 WHERE 進(jìn)行過(guò)濾未必會(huì)選中你認(rèn)為的索引,我們可以提前使用 EXPLAIN
,optimizer trace
來(lái)優(yōu)化我們的查詢語(yǔ)句。
審核編輯 :李倩
-
MySQL
+關(guān)注
關(guān)注
1文章
794瀏覽量
26362 -
索引
+關(guān)注
關(guān)注
0文章
59瀏覽量
10455
原文標(biāo)題:SELECT COUNT(*) 會(huì)造成全表掃描?回去等通知吧
文章出處:【微信號(hào):AndroidPush,微信公眾號(hào):Android編程精選】歡迎添加關(guān)注!文章轉(zhuǎn)載請(qǐng)注明出處。
發(fā)布評(píng)論請(qǐng)先 登錄
相關(guān)推薦
評(píng)論