GreatSQL茶话会——分享一下你的五一假期

GreatSQL社区»论坛问答 › GreatSQL › 互助交流区

[已解决] mysql8的select count(*) 反而慢了

473 7 2023-7-20 09: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:  [gugauat]> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27093343 |
+----------+
1 row in set (33.46 sec)
root@mysqldb 22:21:  [gugauat]>
root@mysqldb 22:21:  [gugauat]> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27093343 |
+----------+
1 row in set (33.49 sec)

root@mysqldb 22:22:  [gugauat]> 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:  [gugauat]> select @@version
-> ;
+-----------+
| @@version |
+-----------+
| 8.0.34 |
+-----------+
1 row in set (0.00 sec)

root@mysqldb 22:23:  [gugauat]>

#####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)

全部回复(7)

马天艺 2023-7-26 15:20:25

yejr 发表于 2023-7-20 13:37
几个问题补充
1. 是否完全相同运行环境
2. innodb_buffer_pool_size设置多大，以及其他几个buffer size

mysql5.7 的是虚拟机，mysql8 的是物理机
####mysql5.7
mysql>  select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27507736 |
+----------+
1 row in set (11.43 sec)

mysql>  show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name          | Value    |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.09 sec)

mysql> explain select count(*) from scanrecord;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
| id | select_type | table    | partitions | type  | possible_keys | key       | key_len | ref  | rows    | filtered | Extra    |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
|  1 | SIMPLE    | scanrecord | NULL    | index | NULL       | CREATETIME | 5    | NULL | 17466894 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

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

####mysql8
root@mysqldb 15:08:  [gugauat]> explain analyze select count(*) from scanrecord;
+-------------------------------------------------------------------------+
| EXPLAIN                                                                |
+-------------------------------------------------------------------------+
| -> Count rows in scanrecord  (actual time=32421..32421 rows=1 loops=1)
|
+-------------------------------------------------------------------------+
1 row in set (32.42 sec)

root@mysqldb 15:13:  [gugauat]> show variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name          | Value    |
+-------------------------+------------+
| innodb_buffer_pool_size | 6442450944 |
+-------------------------+------------+
1 row in set (0.00 sec)

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

root@mysqldb 15:15:  [gugauat]> select count(*) from scanrecord;
+----------+
| count(*) |
+----------+
| 27093343 |
+----------+
1 row in set (31.46 sec)

回复采纳

马天艺 2023-7-27 10:08:27

yejr 发表于 2023-7-26 22:08
8.0场景下，运行下面SQL看执行计划
explain select count(*) from scanrecord;

explain select count(*) from scanrecord;
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | scanrecord | NULL | index | NULL | CREATETIME | 5 | NULL | 23081395 | 100.00 | Using index |
+----+-------------+------------+------------+-------+---------------+------------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

回复采纳

马天艺

3 主题	0 博客	14 贡献

Rank: 1

积分: 25

互动与交流: 社区活动; 论坛问答; 加入社区; 贡献者指南

学习与应用: 文章博客; 文档资料; 视频资料; 常见问题

发现社区: 社区准则; 建议反馈; 社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号

社区小助手

QQ群

友情链接: 万里数据库开源中国墨天轮开放原子开源基金会 OpenEuler

©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )

GMT+8, 2024-5-16 21:59 , Processed in 0.020495 second(s), 15 queries , Redis On.

快速回复 返回顶部 返回列表