首页 > 数据库 >为什么升级到 MySQL 8.x 后，带有多个 IN 值的查询会更昂贵？

为什么升级到 MySQL 8.x 后，带有多个 IN 值的查询会更昂贵？

时间：2024-07-01 19:53:16浏览次数：1  
标签：read 查询 昂贵 Handler MySQL id select row

 

MySQL的范围优化

有多个 IN 值的查询在 MySQL 查询优化器中，会使用 "等值范围优化"（Equality Range Optimization）。假设我们的查询是这样的：

SELECT COUNT(*) FROM test.sbtest1 WHERE id IN (10,50,200,...,30822);

比较列表中有一万个值。在 MySQL 5.7 下运行这个程序会得到以下执行统计结果：

mysql57 > source query1.sql
+----------+
| count(*) |
+----------+
|    17433 |
+----------+
1 row in set (0.16 sec)

对应的慢查询日志(为了便于阅读做了截取)：

# Time: 2024-06-23T13:34:05.912909Z
# User@Host: msandbox[msandbox] @ localhost []  Id:     6
# Query_time: 0.161071  Lock_time: 0.021591 Rows_sent: 1  Rows_examined: 17433
SET timestamp=1719149645;
select count(*) from test.sbtest1 where id in (...);

handler 的状态信息表明优化器恰当的使用了索引，优化起了作用：

mysql57 > show status like 'ha%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 2     |
| Handler_mrr_init           | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 17433 |
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
18 rows in set (0.00 sec)

查看 EXPLAIN 可以确认范围类型和索引：

mysql57 > source explain1.sql
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 17433
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.06 sec)

然而，在我们的升级实例中，使用相同的表，我们最终得到了以下结果：

mysql80 > source query1.sql
+----------+
| count(*) |
+----------+
|    17433 |
+----------+
1 row in set, 1 warning (0.38 sec)

mysql80 > show warnings;
*************************** 1. row ***************************
  Level: Warning
   Code: 3170
Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.
1 row in set (0.00 sec)

上述警告已经告诉我们一些问题，这些问题会导致更糟糕的优化方案，如下所示：

# Time: 2024-06-23T13:44:51.610282Z
# User@Host: msandbox[msandbox] @ localhost []  Id:    18
# Query_time: 0.385067  Lock_time: 0.000004 Rows_sent: 1  Rows_examined: 1200000
SET timestamp=1719150291;
select count(*) from test.sbtest1 where id in (...);

mysql80 > show status like 'ha%';
+----------------------------+---------+
| Variable_name              | Value   |
+----------------------------+---------+
| Handler_commit             | 1       |
| Handler_delete             | 0       |
| Handler_discover           | 0       |
| Handler_external_lock      | 2       |
| Handler_mrr_init           | 0       |
| Handler_prepare            | 0       |
| Handler_read_first         | 1       |
| Handler_read_key           | 1       |
| Handler_read_last          | 0       |
| Handler_read_next          | 1200000 |
| Handler_read_prev          | 0       |
| Handler_read_rnd           | 0       |
| Handler_read_rnd_next      | 0       |
| Handler_rollback           | 0       |
| Handler_savepoint          | 0       |
| Handler_savepoint_rollback | 0       |
| Handler_update             | 0       |
| Handler_write              | 0       |
+----------------------------+---------+
18 rows in set (0.00 sec)

查询计划出人意料地显示了不同列上的二级索引，而且几乎扫描了所有表行：

mysql80 > source explain1.sql
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: k
      key_len: 4
          ref: NULL
         rows: 1183608
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 2 warnings (0.05 sec)

范围优化需要的内存限制由 range_optimizer_max_mem_size 变量定义，但在 MySQL 5.7 和 8.0 中是相同的（8MB）！

那么，为什么在新版本中不能对同一个表进行相同的查询呢？

让我们检查一下 Performance Schema 中的相关内存占用情况。在 5.7 版本中（截断 Performance Schema 表并运行查询后获得），内存占用约为 5.5 MB：

mysql57 > select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=(select THREAD_ID from performance_schema.threads where processlist_id=CONNECTION_ID()) and
    event_name='memory/sql/test_quick_select'G
*************************** 1. row ***************************
                   THREAD_ID: 32
                  EVENT_NAME: memory/sql/test_quick_select
                 COUNT_ALLOC: 104
                  COUNT_FREE: 104
   SUM_NUMBER_OF_BYTES_ALLOC: 5705856
    SUM_NUMBER_OF_BYTES_FREE: 5705856
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 104
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 5705856
1 row in set (0.00 sec)

但在 MySQL 8.0 ，它要大得多，约为 11.5 MB，高于允许的 8 MB 限制：

mysql8.0 > select * from performance_schema.memory_summary_by_thread_by_event_name where thread_id=PS_CURRENT_THREAD_ID() and event_name='memory/sql/test_quick_select'G
*************************** 1. row ***************************
                   THREAD_ID: 47
                  EVENT_NAME: memory/sql/test_quick_select
                 COUNT_ALLOC: 18
                  COUNT_FREE: 18
   SUM_NUMBER_OF_BYTES_ALLOC: 12099576
    SUM_NUMBER_OF_BYTES_FREE: 12099576
              LOW_COUNT_USED: 0
          CURRENT_COUNT_USED: 0
             HIGH_COUNT_USED: 18
    LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
   HIGH_NUMBER_OF_BYTES_USED: 12099576
1 row in set (0.00 sec)

增加变量的值，可修复查询计划：

mysql80 > set range_optimizer_max_mem_size=12*1024*1024;
Query OK, 0 rows affected (0.00 sec)

mysql80 > source query1.sql
+----------+
| count(*) |
+----------+
|    17433 |
+----------+
1 row in set (0.10 sec)

Przemysław Malkowski 认为，这种内存需求差异是不合理的。因此，他报告了这一回归 bug：

https://bugs.mysql.com/bug.php?id=115327

 

另外，Przemysław Malkowski 还报告了一个误导性文档错误：

https://bugs.mysql.com/bug.php?id=115062

 

表上的索引越多，这种回归的影响就越大，因为 MySQL 8.0 也受到了另一个相关错误的影响：

https://bugs.mysql.com/bug.php?id=104000

 

最后

升级到 MySQL 8.x 可能具有挑战性，在实施前应进行充分测试，以避免出现一些非常糟糕的意外情况，如上述情况！不过，从长远来看，升级是不可避免的，因为 5.7 已经到了 EOL。

 

另外，有一个工具可以用来检查任何查询在新版本中是否有不同的表现。下面是一个快速更新，说明了它是如何发现额外警告的：

$ pt-upgrade h=127.0.0.1,P=5756,u=msandbox,p=***,D=test h=127.0.0.1,P=8055,u=msandbox,p=msandbox,D=test slow57.log

#-----------------------------------------------------------------------
# Logs
#-----------------------------------------------------------------------

File: slow57.log
Size: 141681

#-----------------------------------------------------------------------
# Hosts
#-----------------------------------------------------------------------

host1:

  DSN:       h=127.0.0.1,P=5756
  hostname:  przemek-dbg
  MySQL:     MySQL Community Server (GPL) 5.7.44

host2:

  DSN:       h=127.0.0.1,P=8055
  hostname:  przemek-dbg
  MySQL:     MySQL Community Server - GPL 8.0.37

########################################################################
# Query class F4A5056EC85D02D0
########################################################################

Reporting class because it has diffs, but hasn't been reported yet.

Total queries      1
Unique queries     1
Discarded queries  0

select count(*) from test.sbtest? where id in(?+)

##
## Warning diffs: 1
##

-- 1.

No warning 3170

vs.

   Code: 3170
  Level: Warning
Message: Memory capacity of 8388608 bytes for 'range_optimizer_max_mem_size' exceeded. Range optimization was not done for this query.

select count(*) from test.sbtest1 where id in (90, ... ,13668,15161)

#-----------------------------------------------------------------------
# Stats
#-----------------------------------------------------------------------

failed_queries        0
not_select            0
queries_filtered      0
queries_no_diffs      0
queries_read          1
queries_with_diffs    1
queries_with_errors   0

标签：read,查询,昂贵,Handler,MySQL,id,select,row
From： https://www.cnblogs.com/abclife/p/18276662

相关文章

  • 慢查询、pipline、发布订阅、 bitmap位图、 hyperloglog、geo、持久化
    【慢查询】1#1我们配置一个时间，如果查询时间超过了我们设置的时间，我们就认为这是一个慢查询2#2慢查询是一个先进先出的队列,固定长度,保存在内存中--->通过设置慢查询，以后超过我们设置时间的命令，就会放在这个队列中3#3后期我们通过查询这个队列，过滤出慢命令--》......
  • FlinkCDCSQL数据同步mysql->clickhouse
    FlinkCDC(ChangeDataCapture)SQL用于实现数据库的数据变更捕获，并通过SQL接口进行处理。以下是一个基本的示例，全量+增量数据mysql同步到clickhouse，展示如何使用FlinkCDCSQL进行数据同步。首先，确保你有Flink和FlinkCDC的环境配置好。1.mysql测试source表（准备......
  • MySQL 使用 ALTER TABLE 语句修改表结构的方法
    MySQL使用ALTERTABLE语句修改表结构的方法基本概念与作用作用说明完整代码示例示例一：添加新列示例二：修改列的数据类型示例三：删除列示例四：重命名列示例五：添加约束示例六：删除约束示例七：更改表名示例八：更改表的存储引擎功能使用思路实际工作开发技巧在数据库开发......
  • MySQL Public Key Retrieval is not allowed 解决指南
    MySQLPublicKeyRetrievalisnotallowed解决指南基本概念与作用说明完整代码示例与解决方案示例一：检查用户权限示例二：检查KMS配置示例三：检查加密列定义示例四：重置密钥功能使用思路与最佳实践实际工作开发技巧在现代数据库管理中，加密和密钥管理是保障数据安全......
  • MySQL主从复制与读写分离
    一、MySQL主从复制概述1.MySQL主从复制原理MySQL的主从复制和读写分离紧密相连，首先部署主从复制，才能在此基础上进行读写分离。2.MySQL支持的复制类型基于语句的复制：在主服务器上执行的语句，在从服务器上执行同样语句。MySQL默认采用该语句，效率较高。基于行的复制：把改变的......
  • MySQL中update语法的使用（超详细）
    在MySQL中，UPDATE 语句用于修改已存在的表中的记录。以下是对 UPDATE 语句的详细解释和使用方法：语法UPDATEtable_nameSETcolumn1=value1,column2=value2,...WHEREcondition;table_name：要更新的表名。SET：用于指定要更新的列和新的值。column1,column2,.........
  • mysql用户
    一、数据库用户管理DCL：数据控制语言，用于设置或者更改数据库用户或角色权限1.新建用户createuser'用户名'@'源地址'identifiedby'密码';'用户名'指定将创建的用户名'来源地址'指定新创建的用户可在哪些主机上登录可使用IP地址、网段、主机名的形式本地用户可用l......
  • 深入MySQL锁机制：原理、死锁解决及Java防范技巧
    引言在数据库系统中，锁机制是为了保证数据一致性和完整性的重要手段。MySQL作为广泛使用的关系型数据库管理系统，其锁机制尤为重要。本文将详细介绍MySQL的锁机制原理及实现，并说明在生产环境中如何解决死锁问题，以及在后续开发中如何编写Java代码避免死锁。MySQL锁机制概述MySQ......
  • 大厂面试官问我：在同步binlog的时候主库是一个时间，从库是一个时间，底层是怎么解决的？【后
    本文为【Mysql日志八股文合集（2）】初版，后续还会进行优化更新，欢迎大家关注交流~大家第一眼看到这个标题，不知道心中是否有答案了？在面试当中，面试官经常对项目亮点进行深挖，来考察你对这个项目亮点的理解以及思考！这个时候，你如果可以回答出面试官的问题，甚至是主动说出自己的思考，那在......
  • mybatis 查询原理
    mybatis所有操作都会通过sqlSession来完成publicinterfaceSqlSessionextendsCloseable{<T>TselectOne(Stringvar1);<T>TselectOne(Stringvar1,Objectvar2);<E>List<E>selectList(Stringvar1);<E>List<E>......

赞助商

阅读排行

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

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

Copyright © 2020-2023 IPS99 版权所有 IPS99