mysql8的select count(*) 反而慢了

马天艺 发表于 2023-7-20 09:54:54

本帖最后由马天艺于 2023-7-20 10:18 编辑

mysql8的select count(*) 反而慢了，之前字符集是utf8mb3更换为utf8mb4 也是一样的

   Table: scanrecord
Create Table: CREATE TABLE `scanrecord` (
`SCANRECORD_ID` varchar(20) CHARACTER SET utf8mb3 NOT NULL,
`SN` varchar(150) CHARACTER SET utf8mb3 NOT NULL,
`PRODUCTTYPE` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`MODEL` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`CREATETIME` datetime NOT NULL,
`LASTUPDATE` datetime NOT NULL,
`QUANTITY` int DEFAULT '1',
`CUSTOMER_ID` varchar(20) CHARACTER SET utf8mb3 NOT NULL,
`OUTERMODEL` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`STATUS` int DEFAULT '1',
`MSG` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`destination_country` varchar(150) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`integral_open` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`integral_role_id` varchar(20) CHARACTER SET utf8mb3 DEFAULT '' COMMENT '绉垎瑙勫垯琛↖D',
`retry_flag` varchar(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`retry_date` datetime DEFAULT NULL COMMENT 'Retry鏃堕棿',
`user_id` varchar(20) CHARACTER SET utf8mb3 DEFAULT '' COMMENT '鐢ㄦ埛ID',
`code` int DEFAULT '1' COMMENT '鎵爜缁撴灉鐘舵€?1:鎴愬姛',
`sale_region_name` varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`sn_data` varchar(4000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`transactionas_last_date` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`transactionas_date` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`integral_rule_id` bigint DEFAULT NULL COMMENT '绉垎瑙勫垯琛↖D',
`integral_rule_scancode_id` bigint DEFAULT NULL COMMENT '鎵爜绉垎瑙勫垯琛↖D',
`integral` int DEFAULT NULL COMMENT '绉垎鏁?,
`dup_scan` varchar(60) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`bus_country_code` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`promotion_name` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`present_integral` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`activity_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
`language_field` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (`SCANRECORD_ID`),
KEY `CUSTOMER_ID` (`CUSTOMER_ID`),
KEY `scanrecord_ibfk_2` (`user_id`),
KEY `integral_role_id` (`integral_role_id`),
KEY `CREATETIME` (`CREATETIME`) USING BTREE,
KEY `idx_SN` (`SN`,`STATUS`),
KEY `idx_scanrecord_sn` (`SN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

#mysql8.0 上的执行

root@mysqldb 22:19:> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27093343 |
+----------+
1 row in set (33.46 sec)
root@mysqldb 22:21:>
root@mysqldb 22:21:> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27093343 |
+----------+
1 row in set (33.49 sec)

root@mysqldb 22:22:> explain format=json select count(*) from scanrecord;
{
"query_block": {
"select_id": 1,
"cost_info": {
   "query_cost": "3293518.66"
},
"table": {
   "table_name": "scanrecord",
   "access_type": "index",
   "key": "CREATETIME",
   "used_key_parts": [
   "CREATETIME"
   ],
   "key_length": "5",
   "rows_examined_per_scan": 23081395,
   "rows_produced_per_join": 23081395,
   "filtered": "100.00",
   "using_index": true,
   "cost_info": {
   "read_cost": "985379.16",
   "eval_cost": "2308139.50",
   "prefix_cost": "3293518.66",
   "data_read_per_join": "560G"
   }
}
}
} |
1 row in set, 1 warning (0.00 sec)

root@mysqldb 22:22:> select @@version
-> ;
+-----------+
| @@version |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)

root@mysqldb 22:23:>

#####mysql5.7上的执行

mysql> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27270612 |
+----------+
1 row in set (10.41 sec)

mysql> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27270626 |
+----------+
1 row in set (3.75 sec)

mysql> explain format=json select count(*) from scanrecord;
{
"query_block": {
"select_id": 1,
"cost_info": {
   "query_cost": "4314379.40"
},
"table": {
   "table_name": "scanrecord",
   "access_type": "index",
   "key": "CREATETIME",
   "used_key_parts": [
   "CREATETIME"
   ],
   "key_length": "5",
   "rows_examined_per_scan": 17408057,
   "rows_produced_per_join": 17408055,
   "filtered": "100.00",
   "using_index": true,
   "cost_info": {
   "read_cost": "832768.00",
   "eval_cost": "3481611.19",
   "prefix_cost": "4314379.40",
   "data_read_per_join": "320G"
   }
}
}
} |
1 row in set, 1 warning (0.00 sec)

mysql> select @@version;
+------------+
| @@version|
+------------+
| 5.7.25-log |
+------------+
1 row in set (0.00 sec)

yejr 发表于 2023-7-20 13:37:53

几个问题补充
1. 是否完全相同运行环境
2. innodb_buffer_pool_size设置多大，以及其他几个buffer size
3. explain analyze执行计划贴出来

马天艺 发表于 2023-7-26 15:20:25

yejr 发表于 2023-7-26 22:08:01

马天艺发表于 2023-7-26 15:20
mysql5.7 的是虚拟机，mysql8 的是物理机
####mysql5.7
mysql>select count(*) from scanrecord;

8.0场景下，运行下面SQL看执行计划
explain select count(*) from scanrecord;

马天艺 发表于 2023-7-27 10:08:27

yejr 发表于 2023-8-4 14:24:31

马天艺发表于 2023-7-27 10:08
explain select count(*) from scanrecord;
+----+-------------+------------+------------+-------+-- ...

执行计划看起来一样的。
5.7的ibp比8.0的还要低，select速度反倒更快的确不太合理。
你可以试试多次select看下耗时，第一次肯定比较慢，也看看之后的耗时是多久。
另外，也要检查运行环境是否受到其他因素影响，例如同时还有其他高负载进程在跑，或者底层物理I/O设备性能不同等等。

fander 发表于 2023-8-4 17:56:07

这是一个bug。https://bugs.mysql.com/bug.php?id=111969

yejr 发表于 2023-8-6 15:59:28

fander 发表于 2023-8-4 17:56
这是一个bug。https://bugs.mysql.com/bug.php?id=111969

验证器确认了，在8.0下的全表count(*)确实有问题
解决办法：
启用GreatSQL的并行查询 force_parallel_execute = ON
修改where条件为查询辅助索引，如下面的例子：
# 根据辅助索引列查询更高效
greatsql> show global status like 'innodb%data%read%';
+---------------------------+-------------+
| Variable_name          | Value    |
+---------------------------+-------------+
| Innodb_data_pending_reads | 0       |
| Innodb_data_read       | 17106242560 |
| Innodb_data_reads       | 1043933 |
+---------------------------+-------------+
3 rows in set (0.00 sec)

greatsql> select count(*) from sbtest12 where k>=0;
+----------+
| count(*) |
+----------+
| 12500000 |
+----------+
1 row in set (1.30 sec)

greatsql> show global status like 'innodb%data%read%';
+---------------------------+-------------+
| Variable_name          | Value    |
+---------------------------+-------------+
| Innodb_data_pending_reads | 0       |
| Innodb_data_read       | 17358130176 |
| Innodb_data_reads       | 1059314 |
+---------------------------+-------------+

# 查看执行计划
greatsql> explain select count(*) from sbtest4 where k>=0;
+----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------------------------+
| id | select_type | table | partitions | type| possible_keys | key| key_len | ref| rows | filtered | Extra                      |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------------------------+
|1 | SIMPLE    | <gather2> | NULL    | ALL | NULL       | NULL | NULL | NULL | 6165144 | 100.00 | Parallel execute (23 workers) |
|2 | SIMPLE    | sbtest4 | NULL    | range | k_4       | k_4| 4    | NULL | 6165144 | 100.00 | Using where; Using index    |
+----+-------------+-----------+------------+-------+---------------+------+---------+------+---------+----------+-------------------------------+

# 根据主键列查询会全表扫描
greatsql> show global status like 'innodb%data%read%';
+---------------------------+-------------+
| Variable_name          | Value    |
+---------------------------+-------------+
| Innodb_data_pending_reads | 0       |
| Innodb_data_read       | 17358130176 |
| Innodb_data_reads       | 1059314 |
+---------------------------+-------------+
3 rows in set (0.00 sec)

greatsql> select count(*) from sbtest20 where id>=0;
+----------+
| count(*) |
+----------+
| 12500000 |
+----------+
1 row in set (3.88 sec)

greatsql> show global status like 'innodb%data%read%';
+---------------------------+-------------+
| Variable_name          | Value    |
+---------------------------+-------------+
| Innodb_data_pending_reads | 0       |
| Innodb_data_read       | 20162317312 |
| Innodb_data_reads       | 1230142 |
+---------------------------+-------------+

页: [1]

GreatSQL社区's Archiver

mysql8的select count(*) 反而慢了