深度解读 GaussDB(for MySQL) 与 MySQL 的 COUNT 查询并行优化策略

本文分享自华为云社区《【华为云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

点击关注，第一时间了解华为云新鲜技术~