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

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

SELECT COUNT( * ) FROM t

是個再常見不過的 SQL 需求了。在 MySQL 的使用規範中，我們一般使用事務引擎 InnoDB 作為(一般業務)表的存儲引擎，在此前提下，

COUNT( * )

操作的時間複雜度為

O(N)

，其中 N 為表的行數。

而

MyISAM

表中可以快速取到表的行數。這些實踐經驗的背後是怎樣的機制，以及為什麼需要/可以是這樣，就是此文想要探讨的。

先來看一下概況:

MySQL COUNT( * )

在 2 種存儲引擎中的部分問題：

下面就帶着這些問題，以

InnoDB

存儲引擎為主來進行讨論。

一、InnoDB 全表 COUNT( * )

主要問題:

執行過程是怎樣的？
如何計算 count ？影響 count 結果的因素有哪些？
count 值存在哪裡？涉及的資料結構是怎樣的？
為什麼 InnoDB 隻能通過掃表來實作 count( * ) ？(見本文最後的問題)
全表 COUNT( * ) 作為 table scan 類型操作的一個 case ，有什麼風險？
COUNT(* ) 操作是否會像 SELECT * 一樣可能讀取大字段涉及的溢出頁？

1. 執行架構 – 循環: 讀取 + 計數

1.1 基本結論

全表掃描，一個循環解決問題。
循環内: 先讀取一行，再決定該行是否計入 count 。
循環内是一行一行進行計數處理的。

1.2 說明

簡單

SELELCT-SQL

的執行架構，類比

INSERT INTO … SELECT

是同樣的過程。

下面會逐漸細化如何讀取與計數 (

count++

) 。

2. 執行過程

引述: 執行過程部分，分為 4 個部分:

COUNT( * ) 前置流程: 從 Client 端發 SQL 語句，到 MySQL-Server 端執行 SELECT 之前，為後面的一些闡述做一鋪墊。
COUNT( * ) 流程: 簡要給出代碼層面的流程架構及 2 個核心步驟的重點調用棧部分。
讀取一行: 可見性及 row_search_mvcc 函數，介紹可見性如何影響 COUNT( * ) 結果。
計數一行: Evaluate_join_record 與列是否為空，介紹計數過程如何影響 COUNT( * ) 結果。

如果讀者希望直接看如何進行

COUNT( * )

，那麼也可以忽略 (1)，而直接跳到 (2) 開始看。

2.1 COUNT( * ) 前置流程回憶 – 從 Client 端發 SQL 到 sub_select 函數

為了使看到的調用過程不太突兀，我們還是先回憶一下如何執行到

sub_select

函數這來的：

MySQL-Client 端發送 SQL 語句，根據 MySQL 通信協定封包發送。
Mysql-Server 端接收資料包，由協定解析出 command 類型 ( QUERY ) 及 SQL 語句 ( 字元串 ) 。
SQL 語句經過解析器解析輸出為 JOIN 類的對象，用于結構化地表達該 SQL 語句。

PS: 這裡的 JOIN 結構，不僅僅是純文法結構，而是已經進行了語義處理，粗略地說，彙總了表的清單 ( table_list )、目标列的清單 ( target_list )、 WHERE
條件、子查詢等文法結構。

在全表
COUNT( * )-case 中， table_list = [表“t”(别名也是“t”)] ， target_list = [目标列對象(列名為“COUNT( * )”)] ，當然這裡沒有 WHERE 條件、子查詢等結構。

JOIN 對象有 2 個重要的方法: JOIN::optimize() , JOIN::exec() ，分别用于進行查詢語句的優化和查詢語句的執行。

join->optimize()，優化階段 (稍後 myisam 下全表 count( * )
操作會涉及這裡的一點内容)。

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

join->exec() 經過若幹調用，将調用到 sub_select 函數來執行簡單 SQL，包括 COUNT( * ) 。
END of sub_select

2.2 COUNT( * ) 流程 ( 于 sub_select 函數中 )

上層的流程與代碼是比較簡單的，集中在

sub_select

函數中，其中 2 類函數分别對應于前面”執行架構”部分所述的 2 個步驟 – 讀取、計數。先給出結論如下：

讀取一行：從相對頂層的 sub_select 函數經過一番調用，最終所有分支将調用到 row_search_mvcc 函數中，該函數就是用于從 InnoDB 存儲引擎所存儲的 B+-tree 結構中讀取一行到記憶體中的一個 buf (uchar * )
中，待後續處理使用。

這裡會涉及行鎖的擷取、MVCC 及行可見性的問題。當然對于
SELECT COUNT( * ) 這類快照讀而言，隻會涉及 MVCC 及其可見性，而不涉及行鎖。詳情可跳至“可見性與 row_search_mvcc 函數”部分。
計數一行: 代碼層面，将會在 evaluate_join_record 函數中對所讀取的行進行評估，看其是否應當計入 count 中 ( 即是否要 count++ )。

簡單來說，

COUNT(arg)

本身為 MySQL 的函數操作，對于一行來說，若括号内的參數

arg ( 某列或整行 )

的值若不是 NULL，則

count++

，否則對該行不予計數。詳情可跳至“ Evaluate_join_record 與列是否為空”部分。

這兩個階段對

COUNT( * )

結果的影響如下: (兩層過濾)

SQL 層流程架構相關代碼摘要如下:

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;// 第一步，從存儲引擎中擷取一行；1268     if (in_first_read)1269     {1270       in_first_read= false;// 第一步，首次讀取，掃描第一個滿足條件的記錄；// 初始化cursor，從”頭”掃描到某個位置// 類似: SELECT id FROM t LIMIT 1;1271       error= (*qep_tab->read_first_record)(qep_tab);1272     }1273     else// 第一步，後續讀取，在前次掃描的位置上繼續周遊，找到一個滿足條件的記錄；// 類似: SELECT id FROM t WHERE id > $last_id LIMIT 1;1274       error= info->read_record(info);... ... // 此處省略1000字// 第二步，處理剛剛取出的一行1291       rc= evaluate_join_record(join, qep_tab);... ... // 此處省略1000字1303   DBUG_RETURN(rc);1304 }

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

A：從

InnoDB

接口層面考慮，分為 “讀第一行” 和 “讀下一行”，是 2 個不同的執行過程，讀第一行需要找到一個 (

cursor

) 位置并做一些初始化工作讓後續的過程可遞歸。

正如我們如果用腳本/程式來進行逐行的掃表操作，實作上就會涉及下面 2 個 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 層到存儲引擎層的調用關系，讀取階段的調用棧如下：(供參考)

sub_select 函數中從 SQL 層到 InnoDB 層的函數調用關系：(同顔色、同縮進 表示同一層)Ø  (*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并将其放到一個有效的初始位置上;Ø  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()                        “向前”移動一次cursor;

我們可以看到，無論是哪一個分支的讀取，最終都殊途同歸于

row_search_mvcc

函數。

以上是對 LOOP 中的代碼做一些簡要的說明，下面來看

row_search_mvcc

與

evaluate_join_record

如何輸出最終的

count

2.3 行可見性及 row_search_mvcc 函數

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

Q：對于

SELECT COUNT( * ) FROM t

或者

SELECT MIN(id) FROM t

操作，第一次的讀行操作讀到的是表 t 中 ( B+ 樹最左葉節點 page 内 ) 的最小記錄嗎？(

ha_index_first

為何也調用

row_search_mvcc

來擷取最小 key 值？)

A：不一定。即使是

MIN ( id )

也不一定就讀取的是 id 最小的那一行，因為也同樣有行可見性的問題，實際上

index_read

取到的是目前事務内語句可見的最小 index 記錄。這也反映了前面提到的

join_read_first

與

join_read_next

“殊途同歸”到

row_search_mvcc

是理所應當的。

Q：針對圖中最後一問，如果事務 X 是

RU ( Read-Uncommitted )

隔離級别，且

C-Insert ( 100 )

的完成是在

X-count( * )

執行過程中 ( 僅掃描到 5 或 10 這條記錄 ) 完成的，那麼

X-count( * )

在事務

C-Insert ( 100 )

完成後，能否在之後的讀取過程中看到 100 這條記錄呢？

A：MySQL 采取”讀到什麼就是什麼”的政策，即

X-count( * )

在後面可以讀到 100 這條記錄。

2.4 evaluate_join_record 與列是否為空

Q：某一行如何計入 count？

A：兩種情況會将所讀的行計入 count:

1、如果 COUNT 函數中的參數是某列，則會判斷所讀行中該列定義是否

Nullable

以及該列的值是否為

NULL

；若兩者均為是，則不會計入 count，否則将計入 count。

e.g. SELECT COUNT(col_name) FROM t
col_name 可以是主鍵、唯一鍵、非唯一鍵、非索引字段

2、如果 COUNT 中帶有 * ，則會判斷這部分的整行是否為 NULL，如果判斷參數為 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：特别地，對于

SELECT COUNT(id) FROM t

，其中 id 字段是表 t 的主鍵，則如何？

A：效果上等價于

COUNT( * )

。因為無論是

COUNT( * )

，還是

COUNT ( pk_col )

都是因為有主鍵進而充分斷定索取資料不為

NULL

，這類 COUNT 表達式可以用于擷取目前可見的表行數。

Q：使用者層面對

InnoDB COUNT( * )

的優化操作問題

A：這個問題是業界熟悉的一個問題，掃描非空唯一鍵可得到表行數，但所涉及的位元組數可能會少很多(在表的行長與主鍵、唯一鍵的長度相差較多時)，相對的 IO 代價小很多。

相關調用棧參考如下:

參考一: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++;

二、資料結構:

Q：count 值存儲在哪個記憶體變量裡？

A：SQL 解析後，存儲于表達

COUNT( * )

這一項中，

((Item_sum_count*)item_sum)->count

如下圖所示回顧我們之前“

COUNT( * )

前置流程”部分提到的 JOIN 結構。

即 SQL 解析器為每個 SQL 語句進行結構化，将其放在一個

JOIN

對象 ( join ) 中來表達。在該對象中建立并填充了一個清單

result_field_list

用于存放結果列，清單中每個元素則是一個結果列的 (

Item_result_field*

) 對象 ( 指針 ) 。

在

COUNT( * )-case

中，結果列清單隻包含一個元素，(

Item_sum_count: public Item_result_field

) 類型對象 (

name = “COUNT( * )

”)，其中該類所特有的成員變量 count即為所求。

三、MyISAM 全表 COUNT( * )

由于

MyISAM

引擎并不常用于實際業務中，僅做簡要描述如下：

MyISAM-COUNT( * ) 操作是 O(1) 時間複雜度的操作。
每張 MyISAM 表中存放了一個 meta 資訊-count 值，在記憶體中與檔案中各有一份，記憶體中的 count 變量值通過讀取檔案中的 count 值來進行初始化。
SELECT COUNT( * ) FROM t 會直接讀取記憶體中的表 t 對應的 count 變量值。
記憶體中的 count 值與檔案中的 count 值由寫操作來進行更新，其一緻性由表級鎖來保證。
表級鎖保證的寫入串行化使得，同一時刻所有使用者線程的讀操作要麼被鎖，要麼隻會看到一種資料狀态。

四、幾個問題

Q：

MyISAM

InnoDB 在 COUNT( * )

操作的執行過程在哪裡開始分道揚镳？

共性：共性存在于 SQL 層，即 SQL 解析之後的資料結構是一緻的，count 變量都是存在于作為結果列的 Item_sum_count 類型對象中；傳回給用戶端的過程也類似 – 對該 count 變量進行指派并經由 MySQL 通信協定傳回給用戶端。
差別： InnoDB 的 count 值計算是在 SQL 執行階段進行的；而 MyISAM 表本身在記憶體中有一份包含了表 row_count 值的 meta 資訊，在 SQL 優化階段通過存儲引擎的标記給優化器一個 hint ，表明該表所用的存儲引擎儲存了精确行數，可以直接擷取到，無需再進入執行器。

Q：InnoDB 中為何無法向 MyISAM 一樣維護住一個 row_count 變量？

A：從 MVCC 機制與行可見性問題中可得到原因，每個事務所看到的行可能是不一樣的，其

count( * )

結果也可能是不同的；反過來看，則是

MySQL-Server

端無法在同一時刻對所有使用者線程提供一個統一的讀視圖，也就無法提供一個統一的

count

值。

PS: 對于多個通路 MySQL 的使用者線程 ( COUNT( * ) ) 而言，決定它們各自的結果的因素有幾個:

一組事務執行前的資料狀态(初始資料狀态)。
有時間重疊的事務們的執行序列 (操作時序，事務理論表明并發事務操作的可串行化是正确性的必要條件)。
事務們各自的隔離級别(每個操作的輸入)。

其中 1、2 對于 Server 而言都是全局或者說可控的，隻有 3 是每個使用者線程中事務所獨有的屬性，這是 Server 端不可控的因素，是以 Server 端也就對每個

COUNT( * )

結果不可控了。

InnoDB-COUNT( * )

屬

table scan

操作，是否會将現有

Buffer Pool

中其它使用者線程所需熱點頁從

LRU-list

中擠占掉，進而其它使用者線程還需從磁盤

load

一次，突然加重 IO 消耗，可能對現有請求造成阻塞？

A：MySQL 有這樣的優化政策，将掃表操作所

load

的

page

放在

LRU-list

的

oung/old

的交界處 ( LRU 尾部約 3/8 處 )。這樣使用者線程所需的熱點頁仍然在

LRU-list-young

區域，而掃表操作不斷 load 的頁則會不斷沖刷

old

區域的頁，這部分的頁本身就是被認為非熱點的頁，是以也相對符合邏輯。

PS: 個人認為還有一種類似的優化思路，是限定掃描操作所使用的 Buffer Pool 的大小為 O(1) 級别，但這樣做需要付出額外的記憶體管理成本。

InnoDB-COUNT( * )

是否會像

SELECT * FROM t

那樣讀取存儲大字段的溢出頁(如果存在)？

A：否。因為

InnoDB-COUNT( * )

隻需要數行數，而每一行的主鍵肯定不是

NULL

，是以隻需要讀主鍵索引頁内的行資料，而無需讀取額外的溢出頁。

據說，帥的人已經将這個公衆号設為星标

·END·

程式員的成長之路

路雖遠，行則必至

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

一、InnoDB 全表 COUNT( * )

主要問題:

1. 執行架構 – 循環: 讀取 + 計數

1.1 基本結論

1.2 說明

2. 執行過程

2.1 COUNT( * ) 前置流程回憶 – 從 Client 端發 SQL 到 sub_select 函數

2.2 COUNT( * ) 流程 ( 于 sub_select 函數中 )

2.3 行可見性及 row_search_mvcc 函數

2.4 evaluate_join_record 與列是否為空

二、資料結構:

三、MyISAM 全表 COUNT( * )

四、幾個問題

繼續閱讀

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Java經典面試題詳解：帶你手撸紅黑樹總結

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

面試題解析：你接口測試是怎麼做的？

sqlServer根據經緯查距離