MySQL派生条件下推优化引发自定义变量结果偏差探究

MySQL派生条件下推优化引发自定义变量结果异常探究

1. 问题呈现

在现场运行的MySQL 8.0.25版本中，发现含有用户变量的SQL执行结果与预期不符。复现场景如下：

-- 连接MySQL 8.0.25数据库
mysql -uroot -S /var/lib/mysql/mysql-8.0.25-linux-x86_64/mysql-test/var/tmp/mysqld.1.sock -p
-- 执行相关SQL
SELECT version();
CREATE database testdb;
use testdb
CREATE TABLE t1(c1 int);
INSERT INTO t1 VALUES(100),(200),(300),(400),(500);
SELECT * FROM (
  SELECT c1, (@rownum_r:=@rownum_r+1) AS r 
  FROM t1, (SELECT @rownum_r:=0) AS b
) AS q WHERE q.c1=300;   -- 实际结果中rownum为1，而预期应为3

分析执行结果中rownum返回为1，推测是派生表返回的行数少于预期，导致rownum值偏小。于是查看优化器改写后的SQL：

mysql> explain SELECT * FROM (SELECT c1, (@rownum_r := @rownum_r + 1) AS r FROM  t1, (SELECT @rownum_r := 0) AS b) AS q WHERE q.c1 = 300;
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table      | partitions | type   | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL | NULL    | NULL |    2 |   100.00 | NULL           |
|  2 | DERIVED     | <derived3> | NULL       | system | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL           |
|  2 | DERIVED     | t1         | NULL       | ALL    | NULL          | NULL | NULL    | NULL |    5 |    20.00 | Using where    |
|  3 | DERIVED     | NULL       | NULL       | NULL   | NULL          | NULL | NULL    | NULL | NULL |     NULL | No tables used |
+----+-------------+------------+------------+--------+---------------+------+---------+------+------+----------+----------------+
4 rows in set, 3 warnings (0.01 sec)

mysql> SHOW warnings;
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                       |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
.......
| Note    | 1003 | /* select#1 */ select `q`.`c1` AS `c1`,`q`.`r` AS `r` from (/* select#2 */ select `testdb`.`t1`.`c1` AS `c1`,(@rownum_r:=((@`rownum_r`) + 1)) AS `r` from `testdb`.`t1` where (`testdb`.`t1`.`c1` = 300)) `q` |
+---------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

印证了之前的推断，优化器将WHERE条件下推到派生表中，减少了派生表返回行数，从而影响了rownum的递增情况。由此可知这属于优化器的Bug，在Bug库中找到了现象匹配的问题：Derived condition pushdown rewrite ignores user variables https://bugs.mysql.com/bug.php?id=104918

2. 问题剖析

2.1 派生条件下推的含义

引自 10.2.2.5 Derived Condition Pushdown Optimization 的相关说明：

对于类似SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant 的子查询语句，多数情况下可以把外层的where条件下推到派生表内部，改写为SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt，以此减少派生表返回的数据量，提升执行效率。

例如以下场景适用WHERE条件下推：

• 当派生表不使用聚合或窗口函数时，可直接向下推送外部WHERE条件。包含多个用AND、OR或两者结合的谓词的WHERE条件也适用。比如SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11可改写为SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt
• 当派生表有GROUP BY且不使用窗口函数时，引用不属于GROUP BY的列的外部WHERE条件可作为HAVING条件下推到派生表中。例如SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100可改写为SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt
• 当派生表用GROUP BY且外部WHERE条件中的列是GROUP BY列时，引用这些列的WHERE条件可直接下推到派生表中。比如SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10可改写为SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt

derived_condition_pushdown开关在optimizer_switch参数中设置，也可通过相关hint在语句级生效。方法如下：

-- 开启：
SET optimizer_switch="derived_condition_pushdown=on";
SELECT /*+ DERIVED_CONDITION_PUSHDOWN() */ ... FROM ...

-- 关闭：
SET optimizer_switch="derived_condition_pushdown=off";
SELECT /*+ NO_DERIVED_CONDITION_PUSHDOWN() */ ... FROM ...

2.2 Bug影响范围情况

查阅发布日志可知，条件下推在8.0.22中引入，修复版本为8.0.28

-- 8.0.22发布日志：
MySQL now implements derived condition pushdown for eligible queries. What this means is that, for a query such as SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i > constant, it is now possible in many cases to push the outer WHERE condition down to the derived table, in this case resulting in SELECT * FROM (SELECT i, j FROM t1 WHERE i > constant) AS dt. Previously, if the derived table was materialized and not merged, MySQL materialized the entire table—in this case t1—then qualified the rows with the WHERE condition.

-- 8.0.28修复日志：
Documented fix as follows in the MySQL 8.0.28 changelog:

    When a condition was pushed down, the result of evaluating
    assignments to user variables in the SELECT list of the subquery
    were sometimes affected. For this reason, we now prevent
    condition pushdown for statements with assignments to user
    variables.

3. 业务规避办法

1. 升级到MySQL 8.0.28及以上版本，或者GreatSQL分支版本 https://gitee.com/GreatSQL/GreatSQL/releases/tag/GreatSQL-8.0.32-27。
2. 在数据库实例级别关闭该优化，执行set optimizer_switch="derived_condition_pushdown=off";
3. 在涉及的SQL语句上添加hint /*+ NO_DERIVED_CONDITION_PUSHDOWN() */，在语句级别禁用derived_condition_pushdown优化，可规避MySQL Bug 104918 。

参考文章

1. MySQL 8.0.22 releasenote：https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-22.html
2. Derived condition pushdown rewrite ignores user variables https://bugs.mysql.com/bug.php?id=104918
3. Derived Condition Pushdown Optimization https://dev.mysql.com/doc/refman/8.4/en/derived-condition-pushdown-optimization.html
4. GreatSQL分支版本 https://gitee.com/GreatSQL/GreatSQL/releases/tag/GreatSQL-8.0.32-27

Enjoy GreatSQL 😃

关于 GreatSQL

GreatSQL是面向金融级应用的国产自主开源数据库，具备高性能、高可靠、高易用性、高安全等多重核心特性，可作为MySQL或Percona Server的替代方案应用于生产环境，且完全免费并兼容MySQL或Percona Server。

相关链接： GreatSQL社区
Gitee
GitHub
Bilibili

GreatSQL社区：

社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html

技术交流群：

微信：扫码添加GreatSQL社区助手微信好友，发送验证信息加群。