MariaDB中用户变量计数器排序异常问题排查

环境信息 #

当前使用MariaDB版本：

mariadb --version
mariadb  Ver 15.1 Distrib 10.6.11-MariaDB, for debian-linux-gnu (x86_64) using  EditLine wrapper

问题场景 #

从MySQL迁移到MariaDB后，遇到用户变量在排序后逐行递增异常的问题。需求是所有行排序完成后，逐行更新用户变量test：

• 在MySQL中，执行指定查询时test会从1开始按排序后的行逐行递增；
• 在MariaDB中，无LIMIT时test数值混乱，添加LIMIT后则能正常从1递增。

无LIMIT的查询语句 #

SET @test := 0;
SELECT
    *,
    @test := @test + 1 AS `test`
FROM (
    SELECT
        `g_sales`.`sale`,
        `g_sales`.`date`
    FROM
        `g_sales`
    ORDER BY
        `g_sales`.`date`
) AS `t` ORDER BY `t`.`date`;

无LIMIT时MariaDB返回结果 #

+------+------------+------+
| sale | date       | test |
+------+------------+------+
|  106 | 2019-06-19 | 2703 |
|   85 | 2019-10-11 | 2685 |
|   81 | 2019-11-12 | 2681 |
|   96 | 2019-12-09 | 2695 |
|  104 | 2020-03-26 | 2701 |
|   87 | 2020-04-06 | 2687 |
|   94 | 2020-05-15 | 2693 |
|  107 | 2020-05-18 | 2704 |
|   98 | 2020-05-28 | 2697 |
|  103 | 2020-05-28 | 2700 |
|  ... | .......... | .... |
+------+------------+------+

添加LIMIT的查询语句 #

SET @test := 0;
SELECT
    *,
    @test := @test + 1 AS `test`
FROM (
    SELECT
        `g_sales`.`sale`,
        `g_sales`.`date`
    FROM
        `g_sales`
    ORDER BY
        `g_sales`.`date`
    LIMIT 10 OFFSET 0
) AS `t`;

添加LIMIT后MariaDB返回结果 #

+------+------------+------+
| sale | date       | test |
+------+------------+------+
|  106 | 2019-06-19 |    1 |
|   85 | 2019-10-11 |    2 |
|   81 | 2019-11-12 |    3 |
|   96 | 2019-12-09 |    4 |
|  104 | 2020-03-26 |    5 |
|   87 | 2020-04-06 |    6 |
|   94 | 2020-05-15 |    7 |
|  107 | 2020-05-18 |    8 |
|   98 | 2020-05-28 |    9 |
|  103 | 2020-05-28 |   10 |
+------+------------+------+

问题 #

1. 如何在不添加LIMIT的情况下，让MariaDB实现用户变量test按排序后的行正常递增？
2. 添加LIMIT后结果恢复正常的原因是什么？

解答 #

1. 无LIMIT时的解决方法 #

方法一：调整变量赋值时机，移除外层重复排序

把变量递增逻辑直接放在外层查询，同时移除外层的ORDER BY（子查询已完成排序，重复排序会触发优化打乱赋值顺序）：

SET @test := 0;
SELECT
    `sale`,
    `date`,
    @test := @test + 1 AS `test`
FROM (
    SELECT
        `g_sales`.`sale`,
        `g_sales`.`date`
    FROM
        `g_sales`
    ORDER BY
        `g_sales`.`date`
) AS `t`;

方法二：使用窗口函数（推荐，MariaDB 10.2+支持）

用标准的ROW_NUMBER()窗口函数替代用户变量，这是更可靠的实现方式，避免优化器带来的不确定性：

SELECT
    `sale`,
    `date`,
    ROW_NUMBER() OVER (ORDER BY `date`) AS `test`
FROM `g_sales`;

2. 添加LIMIT后恢复正常的原因 #

MariaDB的查询优化器处理无LIMIT的子查询时，可能会将子查询的排序和外层查询的排序合并优化，导致变量赋值操作在最终排序完成前就执行，或者赋值顺序与输出顺序不匹配。

添加LIMIT后，优化器会强制子查询先完成排序并生成指定范围的临时结果集，外层查询直接读取这个已排序的临时集，此时变量会按临时集的顺序逐行递增，因此结果恢复正常。

内容的提问来源于stack exchange，提问作者piece