为什么升级到 MySQL 8.x 后，带有多个 IN 值的查询会更昂贵？
2024-07-01 19:46 abce 阅读(146) 评论(0) 编辑 收藏 举报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
