首页 > 数据库 >MySQL 8.0 执行 COUNT () 很慢原因分析

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

时间：2024-11-09 17:58:01浏览次数：1  
标签：COUNT 8.0 10 08 30 bk MySQL com

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 复现准备
创建表并初始化数据
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: 1
i1: 0
c1: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
c2: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
c3: cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
c4: dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
c5: eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
c6: ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
c7: gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg
bk.re-shake.com/D30eq.wAP
bk.sdymsxfh.com/cliq/0wSwf.Wap
bk.qcbysq.com/D30eq.wAP
bk.62nsfs.com/cliq/c5W4R.WAp
bk.jinduoceramics.com/dcwl/fX8Xw.WaP
bk.zcyxsm.com/D30eq.wAP
bk.sdymsxfh.com/dcwl/D30eq.wAP
bk.62nsfs.com/asd7/f8W4d.wAP
bk.huanbao580.com/f8W4d.wAP
bk.hndsedu.com/beyn/f8W4d.wAP
1 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)
修改配置文件，设置 innodb_buffer_pool_load_at_startup=OFF
重启数据库，确保下次查询时从磁盘加载，systemctl restart mysql3307
1.2.2 8.0.25 的测试结果
执行计划显示走的是二级索引
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)
执行很慢，需要 8 秒
greatsql> SELECT COUNT(1) FROM t0;
+----------+
| count(1) |
+----------+
| 2097152 |
+----------+
1 row in set (8.07 sec)
执行期间的 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:util
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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 的测试结果
执行计划显示走的是二级索引
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)
执行很快，0.81 秒就执行完成
greatsql> SELECT COUNT(1) FROM t0;
+----------+
| count(1) |
+----------+
| 2097152 |
+----------+
1 row in set (0.81 sec)
执行期间的 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:util
30-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: 0
30-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: 0
30-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: 0
30-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: 0
30-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
bk.vwotech.com/jasl/c5W4R.WAp
bk.jiaforhui.com/deyz/fX8Xw.WaP
bk.gzysart.com/cliq/c4FSw.wAp
bk.jyh01.com/c5W4R.WAp
bk.51yjjy.com/0wSwf.Wap
bk.szlcdpq.com/jasl/0wSwf.Wap
bk.jyh01.com/asd7/f8W4d.wAP
bk.yjh9988.com/fX8Xw.WaP
bk.shuixitech.com/fX8Xw.WaP
bk.kfamaw.com/dcwl/c4FSw.wAp
1.2.4 复现结论
通过以上 8.0.25 和 5.7.21 的对比测试，我们发现尽管两者 explain 的执行计划中都声明采用的是二级索引 idx_i1 ，但是实际执行中，8.0.25 还是用的聚簇索引，资源占用高并且执行慢；而 5.7.21 真实的走二级索引，资源占用低并且执行很快

这带来了两个缺陷：

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

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

问题分析 =======

在 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 以为执行器是用二级索引树执行的扫描。

file

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 22768a0f830c5be769bea0c464a8721ec266beef
tree 4fca26e08bdacb88c31588110f3f614a08b2ebc6
parent 76eeb8ffbf4eb7cf927715a98fe2af5333d8e360
author Sreeharsha Ramanavarapu [email protected] 1526702382 +0530
committer Sreeharsha Ramanavarapu [email protected] 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 / 优化器 的建议可以使用二级索引扫描。

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

参考文章 =======

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

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

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

Enjoy GreatSQL

标签：COUNT,8.0,10,08,30,bk,MySQL,com
From： https://www.cnblogs.com/XX-SHE/p/18537051

相关文章

  • MySQL快速入门，一篇搞定
    MySQL1.初识MySQL1.1.为什么学数据库数据库几乎是软件体系中最核心的一个存在1.2.什么是数据库数据库（Database，简称DB）概念：长期存放在计算机内,有组织,可共享的大量数据的集合,是一个数据"仓库"作用：保存,并能安全管理数据(如:增删改查等),减少冗余...数据库总览:关......
  • mysql 查询月份数据.
    //查看本月数据SELECT*FROMcontent_publishWHEREdate_format(publish_time,'%Y%m')=date_format(DATE_SUB(curdate(),INTERVAL0MONTH),'%Y%m') //查看上个月数据SELECT*FROMcontent_publishWHEREdate_format(publish_time,'%Y%m')=date_for......
  • 一文彻底弄懂JUC工具包的CountDownLatch的设计理念与底层原理
    CountDownLatch是Java并发包（java.util.concurrent）中的一个同步辅助类，它允许一个或多个线程等待一组操作完成。一、设计理念CountDownLatch是基于AQS（AbstractQueuedSynchronizer）实现的。其核心思想是维护一个倒计数，每次倒计数减少到零时，等待的线程才会继续执行。它的主要设......
  • 直播短视频系统，Mysql执行顺序代码解析
    直播短视频系统，Mysql执行顺序代码解析MySQL执行顺序FROM<left_table>ON<join_condition><join_type>JOIN<right_table>WHERE<where_condition>GROUPBY<group_by_list>HAVING<having_condition>SELECTDISTINCT<select_list&......
  • 1.存储引擎：深入解析 MySQL 存储引擎与 InnoDB 文件结构
    MySQL提供了多种存储引擎，适用于不同的业务场景。每种引擎在文件结构上设计独特，以便优化性能和功能。本文将详细介绍MySQL中常用存储引擎的文件结构，尤其是InnoDB引擎的多种文件类型及其作用，以帮助更深入地理解和选择适合的存储引擎。一、MySQL存储引擎概述与常用存储......
  • 2. MySQL 索引分类
    MySQL中的索引是提高数据查询速度的重要工具，就像一本书的目录，可以帮助我们快速定位到所需的内容。选择适合的索引类型对数据库设计和性能优化至关重要。本文将详细介绍MySQL中常见的索引类型，并重点讲解聚集索引和二级索引的概念及应用。1.主键索引（PrimaryKeyIndex）概......
  • 关于MySQL表设计，测试人员可以关注哪些点
    测试人员关注数据库表设计是“测试左移”的一种手段，可以把有关数据库的潜在bug消灭在系统测试之前，从而提高交付效率。以MySQL为例，QA可从以下方面对数据库表设计做测试的左移：1、数据表功能表结构确保每张表都有主键，且主键值唯一且非空，以保证表中每行数据的唯一性和可识别性检......
  • mysql ubuntu 卸载
    mysql卸载：引用https://developer.aliyun.com/article/1306777在Ubuntu系统中，MySQL是一种常用的关系型数据库服务器。有时，我们可能需要完全卸载MySQL服务器，包括所有配置文件和数据，以便重新安装或切换到其他数据库服务器。本文将详细介绍在Ubuntu中如何完全卸载MySQL服......
  • MySQLMonitor： 黑盒测试Mysql实时监控辅助工具
    MySQLMonitorMySQL实时监控工具（代码审计、黑盒测试辅助工具）使用1.自行打包使用gitclonehttps://github.com/fupinglee/MySQLMonitorcdMySQLMonitormvncleanpackage-DskipTests=true打开target下的jar文件即可执行2.直接下载使用https://github.com/fupinglee/......
  • 初始mysql以及创建
    mysql是一个客户端服务器结构的程序mysql的服务器是真正的本体，负责保存和管理数据，数据都是保存在硬盘上数据库服务器上可以把很多有业务上联系的表放在一起，构成一个逻辑上的数据集合 登陆mysql打开终端输入mysql-uroot-p，然后显示这个就说明mysql客户端连接到了服务器 ......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99