深度解讀GaussDB(for MySQL)與MySQL的COUNT查詢並行最佳化策略

华为云开发者联盟發表於2024-07-25

本文分享自華為雲社群《【華為雲MySQL技術專欄】GaussDB(for MySQL)與MySQL的COUNT查詢並行最佳化策略》，作者：GaussDB 資料庫。

1.背景介紹

統計表的行數(COUNT)是客戶應用和DBA運維常用的操作。MySQL雖是業界廣泛使用的OLTP資料庫，但大表執行COUNT操作非常耗時，原因在於：

(1) COUNT操作需要遍歷表的全量資料來獲取精確的行數，當表資料量較大或部分資料不在Buffer Pool時，查詢操作很耗時。

(2) MySQL 8.0.14之前的版本無並行查詢技術，只能序列執行SQL語句，無法利用多核技術進行加速。

(3) MySQL 8.0.14及後續版本InnoDB儲存引擎支援並行掃描主鍵，但不支援並行掃描二級索引，在主鍵很大、二級索引較小的場景下，相比老版本(MySQL 5.7)序列掃描二級索引，社群版本並行掃描可能出現效能劣化，並且不支援關閉並行掃描主鍵特性。

GaussDB(for MySQL)透過自研並行查詢(PQ)和計算下推(NDP)特性，解決了大表COUNT慢的問題，典型場景下，相比MySQL並行掃描主鍵效能可提升超過80倍。

2. MySQL COUNT並行介紹

MySQL8.0.14版本InnoDB儲存引擎支援並行掃描主鍵，這樣可以利用並行的能力對COUNT操作進行加速，特性說明參見圖1。

圖1 MySQL 8.0 InnoDB儲存引擎並行掃描主鍵特性

2.1原理介紹

MySQL COUNT並行在InnoDB儲存引擎層實現的框架圖參見圖2。最佳化器決策走COUNT並行後，生成COUNT並行運算元“UnqualifiedCountIterator”，呼叫handler API介面“handler::ha_records”，InnoDB層在函式“Parallel_reader::parallel_read”中排程worker執行緒進行拆分、掃描、計數彙總。

圖2 InnoDB 並行掃描排程邏輯

下面基於MySQL 8.0.14原始碼，介紹COUNT並行在SQL引擎和InnoDB儲存引擎中的實現。

2.1.1 COUNT並行在SQL引擎中的實現

（1）SQL引擎層在最佳化階段判斷SQL是否為簡單的COUNT，記錄在變數“JOIN:: select_count”中，變數的定義參見下方程式碼。

/*
  When join->select_count is set, tables will not be optimized away.
  The call to records() will be delayed until the execution phase and  the counting will be done on an index of Optimizer's choice.
  The index will be decided in find_shortest_key(), called from
  optimize_aggregated_query().
*/
bool JOIN::select_count{false};

（2）SQL引擎層在生成執行計劃階段，判斷變數“JOIN::select_count”的值，如果變數值為TRUE，則生成並行COUNT運算元“UnqualifiedCountIterator”，使用者可以透過“EXPLAIN FORMAT=TREE”或“EXPLAIN ANALYZE”命令檢視執行計劃，如果包含“Count rows”關鍵字說明 COUNT 並行生效，參見下面的執行計劃。

mysql> explain format=tree select  count(*) from lineitem\G
*************************** 1. row  ***************************
EXPLAIN: -> Count rows in lineitem

2.1.2 COUNT並行在InnoDB 儲存引擎中的實現

(1) SQL引擎呼叫handler API 介面“handler::ha_records”，傳遞最佳化器選擇的索引給InnoDB儲存引擎，獲取COUNT結果。

(2) InnoDB儲存引擎只支援主鍵的並行掃描，函式“ha_innobase::records_from_index”忽略索引資訊，強制選擇主鍵進行並行掃描。

(3) InnoDB儲存引擎在函式“Parallel_reader::parallel_read”中對主鍵索引進行初步分片，並排程 worker 執行緒對分片進一步拆分、掃描、計數。

(4) 我們把InnoDB中響應“handler::ha_records”介面並排程worker進行工作的的執行緒稱為leader執行緒，leader執行緒呼叫堆疊資訊如下：

UnqualifiedCountIterator::Read
   get_exact_record_count
      handler::ha_records
        ha_innobase::records_from_index
         ha_innobase::records
           row_scan_index_for_mysql
             row_mysql_parallel_select_count_star
              Parallel_reader::run
                 Parallel_reader::parallel_read

(5) 我們把InnoDB中響應“Parallel_reader::worker”介面並進行掃描、計數工作的執行緒稱為worker執行緒，worker執行緒的併發度可以透過引數“ innodb_parallel_read_threads”控制，worker執行緒呼叫堆疊資訊如下：

Parallel_reader::worker
   Parallel_reader::Ctx::traverse
     Parallel_reader::Ctx::traverse_recs

2.2 效能提升效果

我們使用4U16G規格ECS例項，部署MySQL Community 8.0.14版本，innodb_buffer_pool_size設定為8GB。採用TPC-H測試模型，Scale Factor(Gigabytes)為20，lineitem表主鍵大小約17.4GB，二級索引i_l_orderkey大小約2.3GB，二級索引i_l_partkey_suppkey大小約3.3GB，表結構如下：

mysql> show create table lineitem\G
*************************** 1. row  ***************************
        Table: lineitemCreate Table: 
CREATE TABLE `lineitem` (
   `L_ORDERKEY` bigint NOT NULL,
   `L_PARTKEY` int NOT NULL,
   `L_SUPPKEY` int NOT NULL,
   `L_LINENUMBER` int NOT NULL,
   `L_QUANTITY` decimal(15,2) NOT NULL,
   `L_EXTENDEDPRICE` decimal(15,2) NOT NULL,
   `L_DISCOUNT` decimal(15,2) NOT NULL,
   `L_TAX` decimal(15,2) NOT NULL,
   `L_RETURNFLAG` char(1) NOT NULL,
   `L_LINESTATUS` char(1) NOT NULL,
   `L_SHIPDATE` date NOT NULL,
   `L_COMMITDATE` date NOT NULL,
   `L_RECEIPTDATE` date NOT NULL,
   `L_SHIPINSTRUCT` char(25) NOT NULL,
   `L_SHIPMODE` char(10) NOT NULL,
   `L_COMMENT` varchar(44) NOT NULL,
   PRIMARY KEY (`L_ORDERKEY`,`L_LINENUMBER`),
  KEY  `i_l_orderkey` (`L_ORDERKEY`),
  KEY  `i_l_partkey_suppkey` (`L_PARTKEY`,`L_SUPPKEY`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4  COLLATE=utf8mb4_0900_ai_ci

lineitem表的主鍵約17GB，無法全部載入到Buffer Pool中，每次COUNT執行觸發的磁碟IO基本相同(約82萬次)。在這個場景下，提升InnoDB並行掃描併發度(innodb_parallel_read_threads)，COUNT效能可以線性提升，1併發執行時間約585秒，2併發執行時間約300秒，4併發執行時間約145秒，資料參見圖3。

圖3 MySQL 8.0 COUNT並行提升效果

2.3 約束限制

(1) 社群MySQL COUNT並行在InnoDB儲存引擎實現，只支援主鍵的並行掃描，忽略了最佳化器選擇的最佳索引。當一個表主鍵很大、二級索引較小，相比老版本(MySQL 5.7)序列掃描二級索引，社群並行無最佳化效果。

(2) 社群MySQL COUNT並行只支援無WHERE條件的COUNT，原因在於InnoDB儲存無法進行過濾計算。

(3) 當掃描主鍵資料量很大時，可能會淘汰Buffer Pool中的熱資料，導致後續的效能波動。

(4) 社群MySQL COUNT並行強制生效，無法關閉，當遇到(1)中的效能問題時，無法回退至序列掃描二級索引。

使用2.2節相同的測試環境和測試模型，執行“SELECT COUNT(*) FROM lineitem” SQL語句，對比MySQL 5.7.44版本與MySQL 8.0.14版本執行時間，資料參見表1。

表1 MySQL 5.7.44與8.0.14版本COUNT執行時間對比

在這個場景下，MySQL 8.0版本使用4併發掃描主鍵，但是由於掃描的資料量較大，觸發大量的磁碟IO，導致效能差於MySQL 5.7序列掃描二級索引。

3. GaussDB(for MySQL) COUNT 最佳化

針對MySQL COUNT並行存在的問題，GaussDB(for MySQL)進行了針對性最佳化，透過自研的並行查詢(PQ)和計算下推(NDP)特性，實現了三層並行，加快COUNT執行。框架圖參見圖4。

第一層並行: SQL引擎層，透過自研並行查詢，利用多核計算加速；
第二層並行：InnoDB儲存引擎層，透過自研計算下推特性，觸發批次讀請求，SAL層將批次讀的Page組裝、打包，併發將讀請求傳送至分散式儲存(Page Store)；
第三層並行：Page Store接受到讀請求後，每個Page Store內部併發響應讀請求，待頁面掃描、過濾、聚合操作完成後，將結果返回至計算層。

圖4 GaussDB(for MySQL) COUNT並行最佳化

3.1 原理介紹

下面介紹下GaussDB(for MySQL) COUNT最佳化細節。

3.1.1 支援動態關閉社群MySQL COUNT並行

當遇到2.3節的效能問題時，可以透過調整引數“innodb_parallel_select_count”動態關閉或開啟MySQL COUNT並行功能，使用方法如下：

mysql> SET  innodb_parallel_select_count=OFF;mysql> EXPLAIN FORMAT=TREE SELECT  COUNT(*) FROM lineitem\G
*************************** 1. row ***************************
EXPLAIN: -> Aggregate: count(0)
     -> Index scan on lineitem using i_l_orderkey  (cost=12902405.32 rows=118641035)

3.1.2 GaussDB(for MySQL)並行查詢特性

GaussDB(for MySQL)支援並行查詢(PQ)[1]，用以降低分析型查詢場景的處理時間，滿足企業級應用對查詢低時延的要求。相比社群MySQL並行查詢的諸多限制，GaussDB(for MySQL)自研的並行查詢支援主鍵、二級索引多種掃描方式，適用於大部分SELECT語句。

針對COUNT操作，可以利用PQ特性，並行掃描二級索引，提升查詢效能。

使用者可以透過Hint的方式開啟PQ，當執行計劃中出現Parallel、Gather關鍵字時，說明PQ特性生效。使用方法如下：

mysql>  EXPLAIN FORMAT=TREE SELECT/*+ PQ() */ COUNT(*) FROM lineitem\G
***************************  1. row ***************************
EXPLAIN:  -> Aggregate: count(`<temporary>`.`0`)
    -> Gather: 4 workers, parallel scan on  lineitem
        -> Aggregate: count(`<temporary>`.`0`)
            -> Parallel index scan on lineitem using i_l_orderkey  (cost=4004327.70 rows=29660259)

3.1.3 GaussDB(for MySQL)計算下推特性

計算下推(Near Data Processing)[2]是GaussDB(for MySQL)提高資料複雜查詢效率的解決方案。針對資料密集型查詢，將列投影、聚合運算、條件過濾等操作從計算節點向下推送給分散式儲存層的多個節點，並行執行。透過計算下推方法，提升了並行處理能力，減少網路流量和計算節點的壓力，提高了查詢處理執行效率。

針對COUNT操作，可以利用NDP特性，將聚合操作下推至分散式儲存，減少網路流量，提升查詢效能。

使用者可以透過Hint的方式開啟NDP，執行計劃中出現NDP 關鍵字時，說明此特性生效。使用方法如下：

mysql> EXPLAIN FORMAT=TREE SELECT/*+  PQ() NDP_PUSHDOWN() */ COUNT(*) FROM lineitem\G
*************************** 1. row  ***************************
EXPLAIN: -> Aggregate:  count(`<temporary>`.`0`)
     -> Gather: 4 workers, parallel scan on lineitem
         -> Aggregate:  count(`<temporary>`.`0`)
             -> Parallel index scan  on lineitem using i_l_orderkey Using  pushed NDP (aggregate)   (cost=4046562.45 rows=29047384)

3.2效能最佳化效果

使用2.2節相同的測試環境和測試模型，執行“SELECT COUNT(*) FROM lineitem” SQL語句，對比GaussDB(for MySQL)開啟PQ特性與開啟PQ+NDP特性的執行時間，參見表2。

表2 GaussDB(for MySQL) COUNT操作執行時間

從測試結果看：只開啟PQ特性，並行查詢併發度設定為4，磁碟IO約13萬次，查詢耗時約31秒；

同時開啟PQ和NDP特性，並行查詢併發度設定為4，NDP透過IO合併和計算下推，大幅減少了磁碟IO，查詢耗時只有1.7秒，相比社群MySQL 8.0.22 執行耗時145秒，COUNT效能提升超過80倍。

圖5 GaussDB(for MySQL) COUNT最佳化提升效果

4.總結

社群MySQL 8.0引入了並行掃描主鍵功能，但不支援並行掃描二級索引，導致在大表或冷資料場景(表頁面資料不在Buffer Pool)反而出現劣化，GaussDB(for MySQL)透過並行查詢(PQ)和計算下推(NDP)特性，解決了大表COUNT慢的問題，典型場景下相比社群並行，效能提升超過80倍，為使用者提供更加極致的體驗。

5.相關參考

[1] 並行查詢（PQ）https://support.huaweicloud.com/usermanual-gaussdbformysql/gaussdbformysql_05_0150.html

[2] 運算元下推（NDP）https://support.huaweicloud.com/usermanual-gaussdbformysql/gaussdbformysql_05_0129.html

點選關注，第一時間瞭解華為雲新鮮技術~

【GaussDB(for MySQL)】 Big IN查詢最佳化
2024-05-09
MySql
MySQL優化COUNT()查詢
2019-01-14
MySql優化
mysql group by 執行原理及千萬級別count 查詢最佳化
2023-11-17
MySql
從Purge機制說起，詳解GaussDB(for MySQL)的最佳化策略
2024-06-17
MySql
深度解讀 Cascades 查詢最佳化器
2023-12-12
MySQL查詢最佳化
2024-05-17
MySql
MYSQL count標量子查詢改left join
2020-02-17
MySql
解讀GaussDB(for MySQL)靈活多維的二級分割槽表策略
2024-07-19
MySql
[Mysql]慢查詢最佳化
2024-09-07
MySql
MySQL查詢效能最佳化
2024-07-25
MySql
MySQL-10.索引最佳化與查詢最佳化
2024-05-23
MySql索引
華為雲資料庫核心專家為您揭秘：GaussDB(for MySQL)並行查詢有多快？
2021-09-11
資料庫MySql並行
詳解GaussDB(for MySQL)服務：複製策略與可用性分析
2020-08-06
MySql
技術解讀GaussDB (for MySQL)流控機制
2024-10-08
MySql
MySQL 如何最佳化大分頁查詢?
2023-02-23
MySql
深度詳解GaussDB bufferpool快取策略
2020-09-15
快取
MySQL 8.0新特性-並行查詢innodb_parallel_read_threads
2022-03-23
MySql並行Parallelthread
MySQL索引原理及慢查詢最佳化
2020-09-20
MySql索引
mysql count函式與分頁功能極限最佳化
2021-01-14
MySql函式
MySQL查詢最佳化的5個實用技巧
2021-09-09
MySql
MySQL索引與查詢優化
2019-04-13
MySql索引優化
mySQL多表查詢與事務
2019-05-11
MySql
MySQL查詢
2024-07-09
MySql
詳解MySQL--慢查詢
2020-12-05
MySql
MySQL的COUNT語句--count(*)、 count(常量)、 count(列名)
2019-10-21
MySql
遇到慢查詢怎麼辦？一文解讀MySQL 8.0查詢分析工具
2024-10-31
MySql
mysql多表多欄位查詢並去重
2019-03-15
MySql
MySQL 並列排名和順序排名查詢
2019-04-17
MySql
MySQL最佳化之如何查詢SQL效率低的原因
2021-09-09
MySql
MySQL學習 - 查詢的執行過程
2019-05-04
MySql
MySQL 查詢常用操作(0) —— 查詢語句的執行順序
2023-03-01
MySql
mysql查詢模型
2019-03-12
MySql模型
MYsql 子查詢
2018-05-15
MySql
【MySQL】多表查詢
2018-06-18
MySql
mysql多表查詢
2024-05-09
MySql
MySQL子查詢
2019-11-29
MySql
MySQL慢查詢
2020-11-08
MySql
MySQL 多表查詢
2024-10-28
MySql