代码改变世界

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

  abce  阅读(218)  评论(0)  编辑  收藏  举报

 

MySQL的范围优化

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

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

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

?
1
2
3
4
5
6
7
mysql57 > source query1.sql
+----------+
| count(*) |
+----------+
|    17433 |
+----------+
1 row in set (0.16 sec)

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

?
1
2
3
4
5
# 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 的状态信息表明优化器恰当的使用了索引，优化起了作用：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
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 可以确认范围类型和索引：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
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)

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 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)

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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 限制：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
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)

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

?
1
2
3
4
5
6
7
8
9
10
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。

 

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

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
$ 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
相关博文：
· MySQL的Equality Range Optimization of Many-Valued Comparisons
· MariaDB/MySQL的null值条件和索引
· MySQL 8.0 执行COUNT()很慢原因分析
· MySQL中WHERE后跟着N多个OR条件会怎样...
· SQL优化系列之 in与range 查询
阅读排行：
· 分享4款.NET开源、免费、实用的商城系统
· 全程不用写代码，我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了，比商业数据库还牛
· 白话解读 Dapr 1.15：你的「微服务管家」又秀新绝活了
· 上周热点回顾（2.24-3.2）
历史上的今天：
2023-07-01 使用MySQL Shell备份和还原MySQL
2022-07-01 【PostgreSQL】PostgreSQL的vacuum调优和客户化调度vacuum任务
2016-07-01 mysql参数sql_log_bin
2016-07-01 mysql -prompt选项
2016-07-01 Starting MySQL...The server quit without updating PID file
2015-07-01 RMAN兼容性列表
2015-07-01 修改RMAN list命令输出的时间格式
点击右上角即可分享
微信分享提示