全部标签
写点什么
OpenHarmony算法元宇宙MySQL移动开发学习方法学习方法Web3.0高效工作数据库Python音视频前端AI大数据团队管理程序员运维深度思考低代码redisgolang微服务架构flutter 查看更多

MySQL 8.0 执行 COUNT() 很慢原因分析

作者：GreatSQL
  • 2024-11-06
    福建

  • 本文字数：5232 字

    阅读完需：约 17 分钟

MySQL 8.0 执行 COUNT()很慢原因分析

1.1 问题描述

线上 MySQL8.0.32 环境在执行 SELECT COUNT (1) FROM t0 获取表行数很慢，同样场景下该 SQL 在 MySQL5.7 环境很快就能拿到结果

1.2 问题复现

测试版本：8.0.25 MySQL Community Server - GPL 和 5.7.21-log MySQL Community Server (GPL)

1.2.1 复现准备

  1. 创建表并初始化数据


greatsql> DROP TABLE if EXISTS t0;Query OK, 0 rows affected (0.05 sec)
greatsql> CREATE TABLE `t0` (  `id` int NOT NULL AUTO_INCREMENT,  `i1` int NOT NULL DEFAULT '0',  `c1` varchar(300) NOT NULL DEFAULT 'fander',  `c2` varchar(300) NOT NULL DEFAULT 'fander',  `c3` varchar(300) NOT NULL DEFAULT 'fander',  `c4` varchar(300) NOT NULL DEFAULT 'fander',  `c5` varchar(300) NOT NULL DEFAULT 'fander',  `c6` varchar(300) NOT NULL DEFAULT 'fander',  `c7` varchar(300) NOT NULL DEFAULT 'fander',  PRIMARY KEY (`id`) USING BTREE,  KEY `idx_i1` (`i1`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;Query OK, 0 rows affected (0.05 sec)
greatsql> INSERT INTO t0 VALUES(1,0,REPEAT('a', 100),REPEAT('b', 100),REPEAT('c', 100),REPEAT('d', 100),REPEAT('e', 100),REPEAT('f', 100),REPEAT('g', 100));Query OK, 1 row affected (0.02 sec)
greatsql> SELECT * FROM t0\G*************************** 1. row ***************************id: 1i1: 0c1: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaac2: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbc3: ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc4: ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddc5: eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeec6: ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffc7: gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg1 row in set (0.00 sec)
greatsql> INSERT INTO t0(i1,c1,c2,c3,c4,c5,c6,c7) SELECT i1,c1,c2,c3,c4,c5,c6,c7 FROM t0;Query OK, 1 row affected (0.02 sec)Records: 1  Duplicates: 0  Warnings: 0
Repeatedly execute the forementioned SQL 21 times, until:greatsql> INSERT INTO t0(i1,c1,c2,c3,c4,c5,c6,c7) SELECT i1,c1,c2,c3,c4,c5,c6,c7 FROM t0;Query OK, 1048576 rows affected (29.15 sec)Records: 1048576  Duplicates: 0  Warnings: 0
greatsql> SELECT COUNT(1) FROM t0;+----------+| count(1) |+----------+|  2097152 |+----------+1 row in set (6.72 sec)
复制代码


  1. 修改配置文件，设置 innodb_buffer_pool_load_at_startup=OFF

  2. 重启数据库，确保下次查询时从磁盘加载，systemctl restart mysql3307

1.2.2 8.0.25 的测试结果

  1. 执行计划显示走的是二级索引


greatsql> EXPLAIN SELECT COUNT(1) FROM t0;+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | t0    | NULL       | index | NULL          | idx_i1 | 4       | NULL | 1963965 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码


  1. 执行很慢，需要 8 秒


greatsql> SELECT COUNT(1) FROM t0;+----------+| count(1) |+----------+|  2097152 |+----------+1 row in set (8.07 sec)
复制代码


  1. 执行期间的 top 显示 CPU 冲高到 200%+，磁盘 I/O 也很高，说明扫描了聚簇索引树，启用了并行查询


CPU 监控


  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND                20094 mysql     20   0 4977160   2.5g  17936 S  240.0 16.4    0:34.02 mysqld
复制代码


磁盘监控


----system---- ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- ------memory-usage----- ----swap--- sda- sr1-     time     |usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw | used  buff  cach  free| used  free|util:util30-08 10:32:05|  1   0  99   0   0   0|   0     0 |  12k 4344B|   0     0 |1391  1842 |3116M  265M 11.3G  933M|   0     0 |   0:   030-08 10:32:06|  1   0  99   0   0   0|   0     0 |9125B  214B|   0     0 |1598  2051 |3117M  265M 11.3G  932M|   0     0 |   0:   030-08 10:32:07|  7  10  83   0   0   0| 233M    0 |8856B  556B|   0     0 |  49k   59k|3347M  265M 11.3G  701M|   0     0 |95.5:   030-08 10:32:08|  5   9  82   4   0   0| 211M   68k|9500B 1187B|   0     0 |  42k   53k|3559M  265M 11.3G  490M|   0     0 |98.4:   030-08 10:32:09|  8  10  82   0   0   1| 210M    0 |9042B   15k|   0     0 |  43k   52k|3771M  265M 11.3G  277M|   0     0 |98.4:   030-08 10:32:10|  6  18  76   0   0   1| 181M    0 |8685B  476B|   0     0 |  40k   47k|3953M  264M 11.2G  181M|   0     0 |93.3:   030-08 10:32:11|  7  11  82   0   0   1| 182M    0 |8696B   13k|   0     0 |  39k   48k|4133M  263M 11.0G  176M|   0     0 |98.0:   030-08 10:32:12|  8  13  78   0   0   1| 171M    0 |8648B 2130B|   0     0 |  34k   42k|4302M  261M 10.9G  179M|   0     0 |97.2:   030-08 10:32:13|  5  10  84   0   0   1| 161M    0 |  13k  778B|   0     0 |  34k   41k|4462M  253M 10.7G  162M|   0     0 |95.3:   030-08 10:32:14|  6  11  76   6   0   1| 180M   56k|  10k   15k|   0     0 |  37k   45k|4642M  252M 10.6G  183M|   0     0 |97.8:   030-08 10:32:15|  4   6  90   0   0   0| 111M    0 |  12k 4410B|   0     0 |  23k   29k|4753M  251M 10.5G  170M|   0     0 |28.0:   030-08 10:32:16|  1   1  99   0   0   0| 876k    0 |8976B   66B|   0     0 |1860  2390 |4756M  251M 10.5G  167M|   0     0 |7.30:   030-08 10:32:17|  0   0  99   0   0   0|   0     0 |  10k  278B|   0     0 |1108  1443 |4756M  251M 10.5G  167M|   0     0 |   0:   0
复制代码

1.2.3 5.7.21 的测试结果

  1. 执行计划显示走的是二级索引


greatsql> EXPLAIN SELECT COUNT(1) FROM t0;+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref  | rows    | filtered | Extra       |+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+|  1 | SIMPLE      | t0    | NULL       | index | NULL          | idx_i1 | 4       | NULL | 1992321 |   100.00 | Using index |+----+-------------+-------+------------+-------+---------------+--------+---------+------+---------+----------+-------------+1 row in set, 1 warning (0.00 sec)
复制代码


  1. 执行很快，0.81 秒就执行完成


greatsql> SELECT COUNT(1) FROM t0;+----------+| count(1) |+----------+|  2097152 |+----------+1 row in set (0.81 sec)
复制代码


  1. 执行期间的 top 显示 CPU 只有 20%+，磁盘 I/O 也很低，说明根本没通过聚簇索引


CPU 监控


  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND               28155 mysql     20   0 5238280   2.5g  17788 S  20.7 16.3   0:35.20 mysqld
复制代码


磁盘监控


----system---- ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- ------memory-usage----- ----swap--- sda- sr1-     time     |usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw | used  buff  cach  free| used  free|util:util30-08 10:41:37|  1   1  99   0   0   0|   0     0 |9820B   16k|   0     0 |2078  2877 |4340M  204M 8434M 2907M|   0     0 |   0:   030-08 10:41:38|  0   0  99   0   0   0|   0    64k|9320B  344B|   0     0 |1125  1579 |4340M  204M 8434M 2907M|   0     0 |0.30:   030-08 10:41:39|  2   1  96   1   0   0|9808k    0 |9206B 7890B|   0     0 |2650  3146 |4350M  204M 8434M 2897M|   0     0 |9.30:   030-08 10:41:40|  4   0  94   1   0   0|  18M    0 |8579B  344B|   0     0 |4197  4183 |4368M  204M 8434M 2879M|   0     0 |12.2:   030-08 10:41:41|  1   1  99   0   0   0|   0     0 |  10k   14k|   0     0 |2218  3058 |4369M  204M 8434M 2878M|   0     0 |   0:   0
复制代码

1.2.4 复现结论

通过以上 8.0.25 和 5.7.21 的对比测试，我们发现尽管两者 explain 的执行计划中都声明采用的是二级索引 idx_i1 ，但是实际执行中，8.0.25 还是用的聚簇索引，资源占用高并且执行慢；而 5.7.21 真实的走二级索引，资源占用低并且执行很快


这带来了两个缺陷：


  1. 实际的执行计划和 explain 的结果不一致，会给 SQL 排查带来干扰。需要将 explain 的 key 列改成 PRIMARY

  2. 采用的索引不是最优，导致执行得很慢

2. 问题分析

在 8.0.17 版本中引入了 records_from_index(ha_rows *num_rows, uint) 函数，该函数忽略了上层传入的 index 参数，直接调用 InnoBase::records()让 InnoDB 自己计算行数并返回，并且强制写了走主键索引的逻辑，导致的结果是无法选择最小索引树来实现遍历，实际执行中只能用到主键索引，即使 SQL 中加了使用二级索引的 hint 也不行。当然，等二级索引支持并行查询后就可以在调用 records_from_index 时实际用到传入的 index，但是在 8.0.17 至 8.0.36 之间的版本执行 select count 都会造成很大的执行代价，并且执行计划还会误导 DBA 以为执行器是用二级索引树执行的扫描。



MySQL 8.0.37 中做了优化，解决方式是在 sql/handler.cc 中添加 handler::records_from_index(ha_rows *num_rows, uint index) 使用具体的二级索引来执行查询，详细结果见 https://gitee.com/mirrors/mysql-server/commit/22768a0f830c5be769bea0c464a8721ec266beef


commit 22768a0f830c5be769bea0c464a8721ec266beeftree 4fca26e08bdacb88c31588110f3f614a08b2ebc6parent 76eeb8ffbf4eb7cf927715a98fe2af5333d8e360author Sreeharsha Ramanavarapu <sreeharsha.ramanavarapu@oracle.com> 1526702382 +0530committer Sreeharsha Ramanavarapu <sreeharsha.ramanavarapu@oracle.com> 1526702382 +0530
    WL#10398: Improve SELECT COUNT(*) performance by using              handler::records_from_index(*num_rows, index)              in execution phase
复制代码


同时在 MySQL 8.0.37 的 changelog https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-37.html 中有这样的描述：


InnoDB: MySQL no longer ignores the optimizer hint to use a secondary index scan, which instead forced a clustered (parallel) index scan. (Bug #100597, Bug #112767, Bug #31791868, Bug #35952353)


因此，从 MySQL 8.0.37 及以后的版本中，不再强制使用聚集索引的并行查询，而是遵循 hint/优化器 的建议可以使用二级索引扫描。

3. 解决方案和优化建议

最直接的建议是升级到 MySQL 8.0.37，但是也要注意不要使用 MySQL 8.0.38/8.4.1/9.0.0 版本，因为这三个版本中存在致命 Bug #36808732 (当创建表超过 8000 以后启动失败)，不过这三个版本已经下载不到了，只是 tag 还保留着。

4. 参考文章

  1. MySQL 8.0.37 的发布文档 https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-37.html

  2. INDEX hint does not affect count(*) execution https://bugs.mysql.com/bug.php?id=100597

  3. The performance of version 8.0 when using count(1) is significantly lower compar https://bugs.mysql.com/bug.php?id=111969


划线
评论
复制
发布于: 刚刚阅读数: 3
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
Copyright © 2024, Geekbang Technology Ltd. All rights reserved. 极客邦控股（北京）有限公司 | 京 ICP 备 16027448 号 - 5京公网安备京公网安备 11010502039052号 | 产品资质
MySQL 8.0 执行COUNT()很慢原因分析_GreatSQL_InfoQ写作社区