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

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: 1
i1: 0
c1: aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
c2: bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
c3: cccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc
c4: dddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd
c5: eeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee
c6: ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff
c7: gggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggggg
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

gr