select count(*) 底層究竟做了什么？

閱讀本文大概需要 6.6 分鐘。

SELECT COUNT( * ) FROM t是個(gè)再常見不過的 SQL 需求了。在 MySQL 的使用規(guī)范中，我們一般使用事務(wù)引擎 InnoDB 作為(一般業(yè)務(wù))表的存儲(chǔ)引擎，在此前提下，COUNT( * )操作的時(shí)間復(fù)雜度為?O(N)，其中 N 為表的行數(shù)。

而?MyISAM?表中可以快速取到表的行數(shù)。這些實(shí)踐經(jīng)驗(yàn)的背后是怎樣的機(jī)制，以及為什么需要/可以是這樣，就是此文想要探討的。

先來看一下概況:?MySQL COUNT( * )?在 2 種存儲(chǔ)引擎中的部分問題：

下面就帶著這些問題，以?InnoDB?存儲(chǔ)引擎為主來進(jìn)行討論。

一、InnoDB 全表 COUNT( * )

主要問題:

執(zhí)行過程是怎樣的？

如何計(jì)算?count？影響?count?結(jié)果的因素有哪些？

count?值存在哪里？涉及的數(shù)據(jù)結(jié)構(gòu)是怎樣的？

為什么?InnoDB?只能通過掃表來實(shí)現(xiàn)?count( * )？(見本文最后的問題)

全表COUNT( * )作為?table scan?類型操作的一個(gè)?case，有什么風(fēng)險(xiǎn)？

COUNT(* )操作是否會(huì)像SELECT *一樣可能讀取大字段涉及的溢出頁？

1. 執(zhí)行框架 – 循環(huán): 讀取 + 計(jì)數(shù)

全表掃描，一個(gè)循環(huán)解決問題。

循環(huán)內(nèi): 先讀取一行，再?zèng)Q定該行是否計(jì)入?count。

循環(huán)內(nèi)是一行一行進(jìn)行計(jì)數(shù)處理的。

簡(jiǎn)單?SELELCT-SQL?的執(zhí)行框架，類比?INSERT INTO … SELECT?是同樣的過程。

下面會(huì)逐步細(xì)化如何讀取與計(jì)數(shù) (?count++?) 。

2. 執(zhí)行過程

引述: 執(zhí)行過程部分，分為 4 個(gè)部分:

COUNT( * )前置流程: 從?Client?端發(fā) SQL 語句，到?MySQL-Server端執(zhí)行?SELECT?之前，為后面的一些闡述做一鋪墊。

COUNT( * )?流程: 簡(jiǎn)要給出代碼層面的流程框架及 2 個(gè)核心步驟的重點(diǎn)調(diào)用棧部分。

讀取一行: 可見性及?row_search_mvcc函數(shù)，介紹可見性如何影響?COUNT( * )?結(jié)果。

計(jì)數(shù)一行:?Evaluate_join_record與列是否為空，介紹計(jì)數(shù)過程如何影響?COUNT( * )結(jié)果。

如果讀者希望直接看如何進(jìn)行?COUNT( * )，那么也可以忽略 (1)，而直接跳到 (2) 開始看。

為了使看到的調(diào)用過程不太突兀，我們還是先回憶一下如何執(zhí)行到?sub_select函數(shù)這來的：

MySQL-Client?端發(fā)送 SQL 語句，根據(jù) MySQL 通信協(xié)議封包發(fā)送。

Mysql-Server端接收數(shù)據(jù)包，由協(xié)議解析出?command?類型 (?QUERY?) 及 SQL 語句 ( 字符串 ) 。

SQL 語句經(jīng)過解析器解析輸出為?JOIN類的對(duì)象，用于結(jié)構(gòu)化地表達(dá)該 SQL 語句。

PS: 這里的?JOIN?結(jié)構(gòu)，不僅僅是純語法結(jié)構(gòu)，而是已經(jīng)進(jìn)行了語義處理，粗略地說，匯總了表的列表 (table_list?)、目標(biāo)列的列表 (target_list?)、WHERE?條件、子查詢等語法結(jié)構(gòu)。

在全表?COUNT( * )-case?中，table_list = [表“t”(別名也是“t”)]，target_list = [目標(biāo)列對(duì)象(列名為“COUNT( * )”)]，當(dāng)然這里沒有?WHERE?條件、子查詢等結(jié)構(gòu)。

JOIN對(duì)象有 2 個(gè)重要的方法:?JOIN::optimize(),?JOIN::exec()，分別用于進(jìn)行查詢語句的優(yōu)化 和 查詢語句的執(zhí)行。

join->optimize()，優(yōu)化階段 (稍后?myisam?下全表?count( * )操作會(huì)涉及這里的一點(diǎn)內(nèi)容)。

join->exec()，執(zhí)行階段 ( 重點(diǎn) )，包含了?InnoDB?下全表count( * )?操作的執(zhí)行流程。

join->exec() 經(jīng)過若干調(diào)用，將調(diào)用到sub_select函數(shù)來執(zhí)行簡(jiǎn)單 SQL，包括?COUNT( * )?。

END of sub_select?。

上層的流程與代碼是比較簡(jiǎn)單的，集中在?sub_select?函數(shù)中，其中 2 類函數(shù)分別對(duì)應(yīng)于前面”執(zhí)行框架”部分所述的 2 個(gè)步驟 – 讀取、計(jì)數(shù)。先給出結(jié)論如下：

讀取一行：從相對(duì)頂層的?sub_select?函數(shù)經(jīng)過一番調(diào)用，最終所有分支將調(diào)用到?row_search_mvcc?函數(shù)中，該函數(shù)就是用于從?InnoDB?存儲(chǔ)引擎所存儲(chǔ)的B+-tree結(jié)構(gòu)中讀取一行到內(nèi)存中的一個(gè)?buf (uchar * )?中，待后續(xù)處理使用。

這里會(huì)涉及行鎖的獲取、MVCC 及行可見性的問題。當(dāng)然對(duì) 于?SELECT COUNT( * )?這類快照讀而言，只會(huì)涉及 MVCC 及其可見性，而不涉及行鎖。詳情可跳至“可見性與?row_search_mvcc?函數(shù)”部分。

計(jì)數(shù)一行: 代碼層面，將會(huì)在?evaluate_join_record函數(shù)中對(duì)所讀取的行進(jìn)行評(píng)估，看其是否應(yīng)當(dāng)計(jì)入?count中 ( 即是否要count++?)。

簡(jiǎn)單來說，COUNT(arg)?本身為 MySQL 的函數(shù)操作，對(duì)于一行來說，若括號(hào)內(nèi)的參數(shù)?arg ( 某列或整行 )的值若不是 NULL，則?count++，否則對(duì)該行不予計(jì)數(shù)。詳情可跳至“ Evaluate_join_record 與列是否為空”部分。

這兩個(gè)階段對(duì)?COUNT( * )結(jié)果的影響如下: (兩層過濾)

SQL 層流程框架相關(guān)代碼摘要如下:

1210?enum_nested_loop_state1211?sub_select(JOIN?*join,?QEP_TAB?*const?qep_tab,bool?end_of_records)1212?{1213???DBUG_ENTER("sub_select");...?...?//?此處省略1000字1265???while?(rc?==?NESTED_LOOP_OK?&&?join->return_tab?>=?qep_tab_idx)1266???{1267?????int?error;//?第一步，從存儲(chǔ)引擎中獲取一行；1268?????if?(in_first_read)1269?????{1270???????in_first_read=?false;//?第一步，首次讀取，掃描第一個(gè)滿足條件的記錄；//?初始化cursor，從”頭”掃描到某個(gè)位置//?類似:?SELECT?id?FROM?t?LIMIT?1;1271???????error=?(*qep_tab->read_first_record)(qep_tab);1272?????}1273?????else//?第一步，后續(xù)讀取，在前次掃描的位置上繼續(xù)遍歷，找到一個(gè)滿足條件的記錄；//?類似:?SELECT?id?FROM?t?WHERE?id?>?$last_id?LIMIT?1;1274???????error=?info->read_record(info);...?...?//?此處省略1000字//?第二步，處理剛剛?cè)〕龅囊恍?291???????rc=?evaluate_join_record(join,?qep_tab);...?...?//?此處省略1000字1303???DBUG_RETURN(rc);1304?}

Q： 代碼層面，第一步驟(讀取一行)有 2 個(gè)分支，為什么？

A：從?InnoDB?接口層面考慮，分為 “讀第一行” 和 “讀下一行”，是 2 個(gè)不同的執(zhí)行過程，讀第一行需要找到一個(gè) (?cursor?) 位置并做一些初始化工作讓后續(xù)的過程可遞歸。

正如我們?nèi)绻媚_本/程序來進(jìn)行逐行的掃表操作，實(shí)現(xiàn)上就會(huì)涉及下面 2 個(gè) SQL：

//?SELECT?id?FROM?t?LIMIT?1;?OR?SELECT?MIN(id)-1?FROM?t;?->?$last_id//?SELECT?id?FROM?t?WHERE?id?>?$last_id?LIMIT?1;

具體涉及到此例的代碼，SQL 層到存儲(chǔ)引擎層的調(diào)用關(guān)系，讀取階段的調(diào)用棧如下：(供參考)

sub_select?函數(shù)中從?SQL?層到?InnoDB?層的函數(shù)調(diào)用關(guān)系：(同顏色、同縮進(jìn)?表示同一層)???(*qep_tab->read_first_record)?()|?--?>?join_read_first(tab)????|?--?>?tab->read_record.read_record=join_read_next;????|?--?>?table->file->ha_index_init()????????|?--?>?handler::ha_index_init(uint?idx,?bool?sorted)????????????|?--?>?ha_innobase::index_init()????|?--?>?table->file->ha_index_first()????????|?--?>?handler::ha_index_first(uint?idx,?bool?sorted)????????????|?--?>?ha_innobase::index_first()????????????????|?--?>?ha_innobase::index_read()????????????????????|?--?>?row_search_mvcc()????????????????????初始化cursor并將其放到一個(gè)有效的初始位置上;???info->read_record?(info)|?--?>?join_read_next(info)????|?--?>?info->table->file->ha_index_next(info->record))????????|?--?>?handler::ha_index_next(uchar?*?buf)????????????|?--?>?ha_innobase::index_next(uchar?*?buf)????????????????|?--?>?general_fetch(buf,?ROW_SEL_NEXT,?0)????????????????????|?--?>?row_search_mvcc()????????????????????????“向前”移動(dòng)一次cursor;

我們可以看到，無論是哪一個(gè)分支的讀取，最終都殊途同歸于?row_search_mvcc函數(shù)。

以上是對(duì) LOOP 中的代碼做一些簡(jiǎn)要的說明，下面來看?row_search_mvcc與?evaluate_join_record?如何輸出最終的?count?結(jié)果。

這里我們主要通過一組 case 和幾個(gè)問題來看行可見性對(duì) COUNT( * ) 的影響。

Q：對(duì)于SELECT COUNT( * ) FROM t或者SELECT MIN(id) FROM t操作，第一次的讀行操作讀到的是表 t 中 ( B+ 樹最左葉節(jié)點(diǎn) page 內(nèi) ) 的最小記錄嗎？(?ha_index_first?為何也調(diào)用?row_search_mvcc?來獲取最小 key 值？)

A：不一定。即使是MIN ( id )?也不一定就讀取的是 id 最小的那一行，因?yàn)橐餐瑯佑行锌梢娦缘膯栴}，實(shí)際上?index_read?取到的是 當(dāng)前事務(wù)內(nèi)語句可見的最小 index 記錄。這也反映了前面提到的?join_read_first?與?join_read_next?“殊途同歸”到?row_search_mvcc?是理所應(yīng)當(dāng)?shù)摹?/p>

Q：針對(duì)圖中最后一問，如果事務(wù) X 是?RU ( Read-Uncommitted )?隔離級(jí)別，且?C-Insert ( 100 )?的完成是在?X-count( * )執(zhí)行過程中 ( 僅掃描到 5 或 10 這條記錄 ) 完成的，那么?X-count( * )?在事務(wù)?C-Insert ( 100 )?完成后，能否在之后的讀取過程中看到 100 這條記錄呢？

A：MySQL 采取”讀到什么就是什么”的策略，即X-count( * )在后面可以讀到 100 這條記錄。

Q：某一行如何計(jì)入 count？

A：兩種情況會(huì)將所讀的行計(jì)入 count:

1、如果 COUNT 函數(shù)中的參數(shù)是某列，則會(huì)判斷所讀行中該列定義是否?Nullable以及該列的值是否為?NULL；若兩者均為是，則不會(huì)計(jì)入 count，否則將計(jì)入 count。

e.g.?SELECT COUNT(col_name) FROM t

col_name可以是主鍵、唯一鍵、非唯一鍵、非索引字段

2、如果 COUNT 中帶有 * ，則會(huì)判斷這部分的整行是否為 NULL，如果判斷參數(shù)為 NULL，則忽略該行，否則?count++。

e.g-1.?SELECT COUNT(*) FROM t

e.g-2.?SELECT COUNT(B.*) FROM A LEFT JOIN B ON A.id = B.id

Q： 特別地，對(duì)于?SELECT COUNT(id) FROM t，其中 id 字段是表 t 的主鍵，則如何？

A：效果上等價(jià)于?COUNT( * )。因?yàn)闊o論是?COUNT( * )，還是?COUNT ( pk_col )?都是因?yàn)橛兄麈I從而充分?jǐn)喽ㄋ魅?shù)據(jù)不為?NULL，這類 COUNT 表達(dá)式可以用于獲取當(dāng)前可見的表行數(shù)。

Q： 用戶層面對(duì)?InnoDB COUNT( * )?的優(yōu)化操作問題

A：這個(gè)問題是業(yè)界熟悉的一個(gè)問題，掃描非空唯一鍵可得到表行數(shù)，但所涉及的字節(jié)數(shù)可能會(huì)少很多(在表的行長(zhǎng)與主鍵、唯一鍵的長(zhǎng)度相差較多時(shí))，相對(duì)的 IO 代價(jià)小很多。

相關(guān)調(diào)用棧參考如下:

參考一:evaluate_join_record()|?--?>?rc=?(*qep_tab->next_select)(join,?qep_tab+1,?0);????|?--?>?end_send_group(...)????????|?--?>?init_sum_functions(join->sum_funcs,?join->sum_funcs_end[idx+1]))????????????|?--?>?(*func_ptr)->reset_and_add()????????????????|?--?>?Item_sum::aggregator_clear()????????????????|?--?>?Item_sum::aggregator_add()????????|?--?>?update_sum_func(Item_sum?**func_ptr)????????????|?--?>?(*func_ptr)->add()????????????????|?--?>?Item_sum::aggregator_add()參考二:?(Item_sum::aggregator_add)((Item_sum?*)?(*func_ptr))->aggregator_add()|?--?>?(Item_sum?*)this->aggr->add()????|?--?>?((Aggregator_simple?*)?aggr)->item_sum->add()????????|?--?>?if?(!?aggr->arg_is_null(false))????????|?------?>?((Item_sum_count?*)aggr->item_sum)->count++;

二、數(shù)據(jù)結(jié)構(gòu):

Q：count 值存儲(chǔ)在哪個(gè)內(nèi)存變量里？

A：SQL 解析后，存儲(chǔ)于表達(dá)?COUNT( * )?這一項(xiàng)中，((Item_sum_count*)item_sum)->count

如下圖所示回顧我們之前“COUNT( * )前置流程”部分提到的 JOIN 結(jié)構(gòu)。

即 SQL 解析器為每個(gè) SQL 語句進(jìn)行結(jié)構(gòu)化，將其放在一個(gè)?JOIN?對(duì)象 ( join ) 中來表達(dá)。在該對(duì)象中創(chuàng)建并填充了一個(gè)列表?result_field_list?用于存放結(jié)果列，列表中每個(gè)元素則是一個(gè)結(jié)果列的 (?Item_result_field*) 對(duì)象 ( 指針 ) 。

在?COUNT( * )-case?中，結(jié)果列列表只包含一個(gè)元素，(?Item_sum_count: public Item_result_field?) 類型對(duì)象 (?name = “COUNT( * )”)，其中該類所特有的成員變量 count即為所求。

三、MyISAM 全表 COUNT( * )

由于?MyISAM引擎并不常用于實(shí)際業(yè)務(wù)中，僅做簡(jiǎn)要描述如下：

MyISAM-COUNT( * )?操作是?O(1)?時(shí)間復(fù)雜度的操作。

每張MyISAM表中存放了一個(gè)?meta?信息-count 值，在內(nèi)存中與文件中各有一份，內(nèi)存中的 count 變量值通過讀取文件中的 count 值來進(jìn)行初始化。

SELECT COUNT( * ) FROM t?會(huì)直接讀取內(nèi)存中的表 t 對(duì)應(yīng)的 count 變量值。

內(nèi)存中的 count 值與文件中的 count 值由寫操作來進(jìn)行更新，其一致性由表級(jí)鎖來保證。

表級(jí)鎖保證的寫入串行化使得，同一時(shí)刻所有用戶線程的讀操作要么被鎖，要么只會(huì)看到一種數(shù)據(jù)狀態(tài)。

四、幾個(gè)問題

Q：MyISAM?與?InnoDB 在 COUNT( * )?操作的執(zhí)行過程在哪里開始分道揚(yáng)鑣？

共性：共性存在于 SQL 層，即 SQL 解析之后的數(shù)據(jù)結(jié)構(gòu)是一致的，count 變量都是存在于作為結(jié)果列的?Item_sum_count?類型對(duì)象中；返回給客戶端的過程也類似 – 對(duì)該 count 變量進(jìn)行賦值并經(jīng)由 MySQL 通信協(xié)議返回給客戶端。

區(qū)別：InnoDB?的 count 值計(jì)算是在 SQL 執(zhí)行階段進(jìn)行的；而?MyISAM表本身在內(nèi)存中有一份包含了表?row_count?值的?meta?信息，在 SQL 優(yōu)化階段通過存儲(chǔ)引擎的標(biāo)記給優(yōu)化器一個(gè)?hint，表明該表所用的存儲(chǔ)引擎保存了精確行數(shù)，可以直接獲取到，無需再進(jìn)入執(zhí)行器。

Q：InnoDB 中為何無法向 MyISAM 一樣維護(hù)住一個(gè) row_count 變量？

A：從 MVCC 機(jī)制與行可見性問題中可得到原因，每個(gè)事務(wù)所看到的行可能是不一樣的，其?count( * )結(jié)果也可能是不同的；反過來看，則是?MySQL-Server?端無法在同一時(shí)刻對(duì)所有用戶線程提供一個(gè)統(tǒng)一的讀視圖，也就無法提供一個(gè)統(tǒng)一的?count?值。

PS: 對(duì)于多個(gè)訪問 MySQL 的用戶線程?( COUNT( * ) )?而言，決定它們各自的結(jié)果的因素有幾個(gè):

一組事務(wù)執(zhí)行前的數(shù)據(jù)狀態(tài)(初始數(shù)據(jù)狀態(tài))。

有時(shí)間重疊的事務(wù)們的執(zhí)行序列 (操作時(shí)序，事務(wù)理論表明 并發(fā)事務(wù)操作的可串行化是正確性的必要條件)。

事務(wù)們各自的隔離級(jí)別(每個(gè)操作的輸入)。

其中 1、2 對(duì)于 Server 而言都是全局或者說可控的，只有 3 是每個(gè)用戶線程中事務(wù)所獨(dú)有的屬性，這是 Server 端不可控的因素，因此 Server 端也就對(duì)每個(gè)?COUNT( * )?結(jié)果不可控了。

Q：InnoDB-COUNT( * )?屬?table scan?操作，是否會(huì)將現(xiàn)有?Buffer Pool?中其它用戶線程所需熱點(diǎn)頁從?LRU-list?中擠占掉，從而其它用戶線程還需從磁盤?load一次，突然加重 IO 消耗，可能對(duì)現(xiàn)有請(qǐng)求造成阻塞？

A：MySQL 有這樣的優(yōu)化策略，將掃表操作所?load的?page?放在?LRU-list?的?oung/old?的交界處 ( LRU 尾部約 3/8 處 )。這樣用戶線程所需的熱點(diǎn)頁仍然在?LRU-list-young?區(qū)域，而掃表操作不斷 load 的頁則會(huì)不斷沖刷old區(qū)域的頁，這部分的頁本身就是被認(rèn)為非熱點(diǎn)的頁，因此也相對(duì)符合邏輯。

PS: 個(gè)人認(rèn)為還有一種類似的優(yōu)化思路，是限定掃描操作所使用的?Buffer Pool?的大小為 O(1) 級(jí)別，但這樣做需要付出額外的內(nèi)存管理成本。

Q：InnoDB-COUNT( * )?是否會(huì)像?SELECT * FROM t?那樣讀取存儲(chǔ)大字段的溢出頁(如果存在)？

A：否。因?yàn)?InnoDB-COUNT( * )?只需要數(shù)行數(shù)，而每一行的主鍵肯定不是?NULL，因此只需要讀主鍵索引頁內(nèi)的行數(shù)據(jù)，而無需讀取額外的溢出頁。

據(jù)說，帥的人已經(jīng)將這個(gè)公眾號(hào)設(shè)為星標(biāo)

·END·

程序員的成長(zhǎng)之路

路雖遠(yuǎn)，行則必至

SQL MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。