运维工作中你遇到过哪些神奇事件？

GreatSQL社区

搜索

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

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

MySQL
全部回复(7)
只看楼主
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-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)
回复 采纳

举报

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-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)
回复 采纳

举报

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条件为查询辅助索引，如下面的例子：
  1. # 根据辅助索引列查询更高效
  2. greatsql> show global status like 'innodb%data%read%';
  3. +---------------------------+-------------+
  4. | Variable_name             | Value       |
  5. +---------------------------+-------------+
  6. | Innodb_data_pending_reads | 0           |
  7. | Innodb_data_read          | 17106242560 |
  8. | Innodb_data_reads         | 1043933     |
  9. +---------------------------+-------------+
  10. 3 rows in set (0.00 sec)

  12. greatsql> select count(*) from sbtest12 where k>=0;
  13. +----------+
  14. | count(*) |
  15. +----------+
  16. | 12500000 |
  17. +----------+
  18. 1 row in set (1.30 sec)

  20. greatsql> show global status like 'innodb%data%read%';
  21. +---------------------------+-------------+
  22. | Variable_name             | Value       |
  23. +---------------------------+-------------+
  24. | Innodb_data_pending_reads | 0           |
  25. | Innodb_data_read          | 17358130176 |
  26. | Innodb_data_reads         | 1059314     |
  27. +---------------------------+-------------+

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

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

  49. greatsql> select count(*) from sbtest20 where id>=0;
  50. +----------+
  51. | count(*) |
  52. +----------+
  53. | 12500000 |
  54. +----------+
  55. 1 row in set (3.88 sec)

  57. greatsql> show global status like 'innodb%data%read%';
  58. +---------------------------+-------------+
  59. | Variable_name             | Value       |
  60. +---------------------------+-------------+
  61. | Innodb_data_pending_reads | 0           |
  62. | Innodb_data_read          | 20162317312 |
  63. | Innodb_data_reads         | 1230142     |
  64. +---------------------------+-------------+
复制代码
回复 已采纳

举报

马天艺

3

主题

0

博客

14

贡献

新手上路

Rank: 1

积分
25
发消息
互动与交流
社区活动
论坛问答
加入社区
贡献者指南
学习与应用
文章博客
文档资料
视频资料
常见问题
发现社区
社区准则
建议反馈
社区之星

合作电话：010-64087828

社区邮箱：greatsql@greatdb.com

社区公众号
社区小助手
QQ群
友情链接
万里数据库开源中国墨天轮开放原子开源基金会OpenEuler
©2022 GreatSQL All Rights Reserved. ( 京ICP备06057874号 )
GMT+8, 2024-4-30 01:28 , Processed in 0.020976 second(s), 19 queries , Redis On.
快速回复 返回顶部 返回列表