时间戳

首页 > 解决方案 > 相同记录集上的相同 UPDATE SQL 似乎与 MariaDB 行为不端

mysql - 相同记录集上的相同 UPDATE SQL 似乎与 MariaDB 行为不端

问题描述

问题：逻辑似乎与 MariaDB 不符。

目标：创建一个自引用 ID 列值来引用前一条记录。

描述：我们有一个非常大的记录表，其中 ID 作为主键和记录的时间，分布在终端之间。我们要创建一个列来保存同一终端的当前记录的前一条记录的 ID 值，具体取决于记录的时间，我们将使用该记录的时间进行自连接以选择当前记录的上一条记录在同一记录集中，为了提高查询性能，而不是在每次查询期间动态选择以前的记录。

期望：了解实际发生的情况以及跨数据库引擎（至少 MySQL 和 MariaDB）兼容 UPDATE 查询并具有最佳性能。

UPDATE tst_terminaldata SET PreviousID = NULL;

UPDATE tst_terminaldata AS TD
SET TD.PreviousID =
    (SELECT TDP.ID FROM
        (SELECT TDP2.ID, TDP2.Time, TDP2.TerminalID
                         FROM tst_terminaldata AS TDP2
                         ORDER BY 
  TDP2.Time DESC, TDP2.ID DESC /* Works for- Windows: MySQL 8.0.16, 5.7.11; Linux: MySQL 8.0.13, 8.0, 5.7.20 */
  TDP2.Time ASC, TDP2.ID ASC /* Works for- Windows: MariaDB 10.4.7; Linux: MariaDB 10.2.25, 10.1.40 */
        ) AS TDP
        WHERE TDP.TerminalID = TD.TerminalID
          AND TDP.Time < TD.Time
        LIMIT 1
    ) 
WHERE TD.PreviousID IS NULL;

SELECT TD.* FROM tst_terminaldata AS TD
      ORDER BY TD.TerminalID ASC, TD.Time DESC, TD.ID DESC;

案例：MySQL SQL 在我看来是合乎逻辑的，而不是 MariaDB。但两者都适用于各自的情况（如 SQL 中所述），这不应该。

样本数据：

ID  TerminalID  Time    PreviousID
1   1   2019-07-29 13:56:37 NULL
2   1   2019-07-29 13:56:52 NULL
3   2   2019-07-29 13:57:01 NULL
4   1   2019-07-29 13:57:02 NULL
5   2   2019-07-29 13:57:08 NULL
6   1   2019-07-29 13:57:17 NULL
7   2   2019-07-29 13:57:23 NULL
8   1   2019-07-29 13:57:32 NULL

标签： mysqlsqlmariadb

解决方案

  • ORDER BYMySQL 和 MariaDB 都忽略了 Derived table 中的内容（至少在您尝试过的版本中）。
  • 优化器可以自由地以任何顺序呈现行。
  • MySQL 和 MariaDB 的优化器代码在这方面的差异约为 5.6 / 10.0。

这应该可以解释为什么您似乎会根据ORDER BY和 版本获得不同的结果。

如果您每秒插入 1700 行，我希望您对它们进行批处理并使用 SSD。

我不知道查询的目标是什么，但我建议在数据到达时对其进行消化，而不是在“滞后”类型的查询中苦苦挣扎。

推荐阅读