深入理解select count(*)

it智能化專欄 發佈 2022-07-05T16:09:33.385696+00:00

深入理解select count(*)底層究竟做了什麼2019-06-11 12:58·Java技術架構專注於Java領域優質技術,歡迎關注作者:賈春生SELECT COUNT( * ) FROM t是個再常見不過的 SQL 需求了。

深入理解select count(*)底層究竟做了什麼

2019-06-11 12:58·Java技術架構

專注於Java領域優質技術,歡迎關注

作者:賈春生

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函數這來的:

1.MySQL-Client 端發送 SQL 語句,根據 MySQL 通信協議封包發送。

2.Mysql-Server端接收數據包,由協議解析出 command 類型 ( QUERY ) 及 SQL 語句 ( 字符串 ) 。

3.SQL 語句經過解析器解析輸出為 JOIN類的對象,用於結構化地表達該 SQL 語句。

PS: 這裡的 JOIN 結構,不僅僅是純語法結構,而是已經進行了語義處理,粗略地說,匯總了表的列表 (table_list )、目標列的列表 (target_list )、WHERE 條件、子查詢等語法結構。
在全表 COUNT( * )-case 中,table_list = [表「t」(別名也是「t」)],target_list = [目標列對象(列名為「COUNT( * )」)],當然這裡沒有 WHERE 條件、子查詢等結構。

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


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

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

5.join->exec() 經過若干調用,將調用到sub_select函數來執行簡單 SQL,包括 COUNT( * ) 。

6.END of sub_select 。

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

上層的流程與代碼是比較簡單的,集中在 sub_select 函數中,其中 2 類函數分別對應於前面」執行框架」部分所述的 2 個步驟 – 讀取、計數。先給出結論如下:

  1. 讀取一行:從相對頂層的 sub_select 函數經過一番調用,最終所有分支將調用到 row_search_mvcc 函數中,該函數就是用於從 InnoDB 存儲引擎所存儲的B+-tree結構中讀取一行到內存中的一個 buf (uchar * ) 中,待後續處理使用。
  2. 這裡會涉及行鎖的獲取、MVCC 及行可見性的問題。當然對 於 SELECT COUNT( * ) 這類快照讀而言,只會涉及 MVCC 及其可見性,而不涉及行鎖。詳情可跳至「可見性與 row_search_mvcc 函數」部分。
  3. 計數一行: 代碼層面,將會在 evaluate_join_record函數中對所讀取的行進行評估,看其是否應當計入 count中 ( 即是否要count++ )。

簡單來說,COUNT(arg) 本身為 MySQL 的函數操作,對於一行來說,若括號內的參數 arg ( 某列或整行 )的值若不是 NULL,則 count++,否則對該行不予計數。詳情可跳至「 Evaluate_join_record 與列是否為空」部分。

這兩個階段對 COUNT( * )結果的影響如下: (兩層過濾)

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

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 層到存儲引擎層的調用關係,讀取階段的調用棧如下:(供參考)

我們可以看到,無論是哪一個分支的讀取,最終都殊途同歸於 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 代價小很多。

相關調用棧參考如下:


二、數據結構:

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引擎並不常用於實際業務中,僅做簡要描述如下:

  1. MyISAM-COUNT( * ) 操作是 O(1) 時間複雜度的操作。
  2. 每張MyISAM表中存放了一個 meta 信息-count 值,在內存中與文件中各有一份,內存中的 count 變量值通過讀取文件中的 count 值來進行初始化。
  3. SELECT COUNT( * ) FROM t 會直接讀取內存中的表 t 對應的 count 變量值。
  4. 內存中的 count 值與文件中的 count 值由寫操作來進行更新,其一致性由表級鎖來保證。
  5. 表級鎖保證的寫入串行化使得,同一時刻所有用戶線程的讀操作要麼被鎖,要麼只會看到一種數據狀態。

四、幾個問題

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( * ) 結果不可控了。

Q: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) 級別,但這樣做需要付出額外的內存管理成本。

Q:InnoDB-COUNT( * ) 是否會像 SELECT * FROM t 那樣讀取存儲大欄位的溢出頁(如果存在)?

A:否。因為 InnoDB-COUNT( * ) 只需要數行數,而每一行的主鍵肯定不是 NULL,因此只需要讀主鍵索引頁內的行數據,而無需讀取額外的溢出頁。

blog.didiyun.com/index.php/2019/01/08/mysql-count/

關鍵字:

看過「產後掉髮」多誇張嗎…

2021-10-04T10:00:15.722371+00:00

記得生完第一次洗頭毛,浴室大淹水,往下一看…蝦米

我的秀髮啊!(我老公疏通劑通了一個小時才解決,辛苦他了…)

明顯的分線、扁塌的髮根,生完像老十歲;我也不是沒想過髮根燙、生髮水,但產後頭皮癢痛又敏感,更怕化學藥劑讓小朋友「哺毒乳」

前陣子,看到好多媽咪分享『重啟頭髮生命期』的無藥性洗髮乳,到底何方神聖→https://www.cashin.tw/product/000000000034640

一般洗髮精只是「帶走髒污」,頭髮當然一起被沖走。但這款很不同:

 

【溫和初乳精萃-乳鐵蛋白胜肽】洗髮=頭皮營養的關鍵!

專為虛弱髮設計,添加市面最高乳鐵蛋白+維他命B5等養髮因子,不管產前產後、敏弱掉髮都可使用。

 

剛開始有點頭皮屑,就是在汰舊換新中!

我大約用兩三週,不只敏弱狀況改善,頭髮也不再一洗就掉。而且!頭頂默默長出小碎毛,髮根也跟著澎起來誒!

(我老公也偷用了,原本的臭油頭變超蓬鬆的,但別浪費我的洗髮乳好嗎

 

用過才懂為什麼媽咪都推~而且香味屬於中性溫柔香,全家都適合。別拖著不呵護頭皮,否則到時連用生髮水都沒用

 

商品資訊

 

━━ \專為虛弱髮設計/ ━━

無藥性配方X市面最高含量

《Magicom乳鐵蛋白專業養護洗髮乳》

✔不含矽靈、皂鹼  ✔全家適用

✔天然潔淨成分  ✔敏弱OK 

「乳鐵蛋白」就是戰勝弱髪的關鍵,

  我們要重新定義你的頭皮! 

https://www.cashin.tw/product/000000000034640