這期內(nèi)容當(dāng)中小編將會給大家?guī)碛嘘P(guān)如何深入理解select count(*)，文章內(nèi)容豐富且以專業(yè)的角度為大家分析和敘述，閱讀完這篇文章希望大家可以有所收獲。

創(chuàng)新互聯(lián)公司專業(yè)為企業(yè)提供青銅峽網(wǎng)站建設(shè)、青銅峽做網(wǎng)站、青銅峽網(wǎng)站設(shè)計、青銅峽網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁設(shè)計與制作、青銅峽企業(yè)網(wǎng)站模板建站服務(wù)，十年青銅峽做網(wǎng)站經(jīng)驗，不只是建網(wǎng)站，更提供有價值的思路和整體網(wǎng)絡(luò)服務(wù)。

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

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

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

下面就帶著這些問題，以 InnoDB 存儲引擎為主來進行討論。

一、InnoDB 全表 COUNT( * )

主要問題:

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

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

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

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

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

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

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

1.1 基本結(jié)論

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

循環(huán)內(nèi): 先讀取一行，再決定該行是否計入 count。

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

1.2 說明

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

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

2. 執(zhí)行過程

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

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

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

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

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

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

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

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

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

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

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

PS: 這里的 JOIN 結(jié)構(gòu)，不僅僅是純語法結(jié)構(gòu)，而是已經(jīng)進行了語義處理，粗略地說，匯總了表的列表 (table_list )、目標列的列表 (target_list )、WHERE 條件、子查詢等語法結(jié)構(gòu)。在全表 COUNT( * )-case 中，table_list = [表“t”(別名也是“t”)]，target_list = [目標列對象(列名為“COUNT( * )”)]，當(dāng)然這里沒有 WHERE 條件、子查詢等結(jié)構(gòu)。

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

join->optimize()，優(yōu)化階段 (稍后 myisam 下全表 count( * )操作會涉及這里的一點內(nèi)容)。  join->exec()，執(zhí)行階段 ( 重點 )，包含了 InnoDB 下全表count( * ) 操作的執(zhí)行流程。

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

6.END of sub_select 。

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

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

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

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

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

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

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

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

Q： 代碼層面，***步驟(讀取一行)有 2 個分支，為什么?

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

正如我們?nèi)绻媚_本/程序來進行逐行的掃表操作，實現(xiàn)上就會涉及下面 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 層到存儲引擎層的調(diào)用關(guān)系，讀取階段的調(diào)用棧如下：(供參考)

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

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

2.3 行可見性及 row_search_mvcc 函數(shù)

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

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

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

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

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

2.4 evaluate_join_record 與列是否為空

Q：某一行如何計入 count?

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

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

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

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

2、如果 COUNT 中帶有 * ，則會判斷這部分的整行是否為 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： 特別地，對于 SELECT COUNT(id) FROM t，其中 id 字段是表 t 的主鍵，則如何?

A：效果上等價于 COUNT( * )。因為無論是 COUNT( * )，還是 COUNT ( pk_col ) 都是因為有主鍵從而充分斷定索取數(shù)據(jù)不為  NULL，這類 COUNT 表達式可以用于獲取當(dāng)前可見的表行數(shù)。

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

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

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

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

Q：count 值存儲在哪個內(nèi)存變量里?

A：SQL 解析后，存儲于表達 COUNT( * ) 這一項中，((Item_sum_count*)item_sum)->count

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

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

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

三、MyISAM 全表 COUNT( * )

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

1. 鴻蒙官方戰(zhàn)略合作共建——HarmonyOS技術(shù)社區(qū)

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

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

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

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

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

四、幾個問題

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

上述就是小編為大家分享的如何深入理解select count(*)了，如果剛好有類似的疑惑，不妨參照上述分析進行理解。如果想知道更多相關(guān)知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道。