🔇 👊 👌🏿 在MySQL 8.0中使用窗口函数和CTE来实现累计总数而不会造成黑客攻击 👞 🎾 👨🏻‍🔧

大约翻译：在本文中，英国公司Ticketsolve的团队负责人分享了针对他非常具体的问题的解决方案，同时展示了使用现代MySQL 8.0功能创建所谓的累积函数的一般方法。它的清单清晰明了，并提供了详细的说明，即使对于那些没有那么深入了解的人，也有助于理解问题的实质。

在MySQL中使用累积函数执行更新的一种常见策略是使用自定义变量和模式UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar, mycol))。

这种模式不适用于优化程序（导致不确定性行为），因此他们决定放弃它。结果是一种空虚，因为（相对）复杂的逻辑现在更难以实现（至少具有相同的简单性）。

本文将讨论实现它的两种方法：使用窗口函数（规范方法）和使用递归CTE（通用表表达式）。

要求和背景

尽管CTE非常直观，但是对于不熟悉它们的人，我建议参考我以前关于该主题的文章。

窗口函数也是如此：我将对查询/概念进行详细评论，但是总体思路仍然没有任何问题。大量的书籍和出版物致力于窗口功能（这就是为什么我仍然没有写它们的原因）；但是，在大多数示例中，都是根据财务结果或人口统计指标进行计算。但是，在本文中，我将使用实际情况。

在软件方面，我建议使用MySQL 8.0.19（但不是必需的）。所有表达式必须在同一控制台中执行才能重用@venue_id。

在软件世界中，存在着一个著名的体系结构难题：逻辑应该在应用程序级别还是在数据库级别实现？尽管这是一个完全正确的问题，但在我们的情况下，我假设逻辑应保留在基本级别上；原因可能是速度要求（例如我们的情况）。

任务

在此任务中，我们在某个大厅（剧院）中分配座位。

出于商业目的，需要为每个位置分配一个所谓的“分组”-代表它的附加数字。

这是确定分组值的算法：

从0开始并在左上方；
如果当前和前一个之间有一个空白空间，或者这是一个新行，那么我们将2加上前一个值（如果这不是绝对的第一位），否则，将值增加1；否则，我们将值增加1。
为一个地点分配一个分组；
转到同一行或下一行的新位置（如果上一行结束），然后从第2点开始重复；我们将继续一切，直到这些地方用完为止。

伪代码算法：

current_grouping = 0

for each row:
  for each number:
    if (is_there_a_space_after_last_seat or is_a_new_row) and is_not_the_first_seat:
      current_grouping += 2
    else
      current_grouping += 1

    seat.grouping = current_grouping

在现实生活中，我们希望左侧的配置给出右侧所示的值：

 x→  0   1   2        0   1   2
y   ╭───┬───┬───╮    ╭───┬───┬───╮
↓ 0 │ x │ x │   │    │ 1 │ 2 │   │
    ├───┼───┼───┤    ├───┼───┼───┤
  1 │ x │   │ x │    │ 4 │   │ 6 │
    ├───┼───┼───┤    ├───┼───┼───┤
  2 │ x │   │   │    │ 8 │   │   │
    ╰───┴───┴───╯    ╰───┴───┴───╯

训练

让基表具有以下简约结构：

CREATE TABLE seats (
  id         INT AUTO_INCREMENT PRIMARY KEY,
  venue_id   INT,
  y          INT,
  x          INT,
  `row`      VARCHAR(16),
  number     INT,
  `grouping` INT,
  UNIQUE venue_id_y_x (venue_id, y, x)
);

我们真的不需要row和列number。另一方面，我们不想使用其记录完全包含在索引中的表（只是为了更接近实际问题）。

根据上图，每个位置的坐标为（y，x）：

（0，0），（0，1）
（1、0），（1、2）
（20）

请注意，我们使用y作为第一个坐标，因为它可以更轻松地跟踪行。

您必须加载足够多的记录，以防止优化器发现意外的短路径。当然，我们使用递归CTE：

INSERT INTO seats(venue_id, y, x, `row`, number)
WITH RECURSIVE venue_ids (id) AS
(
  SELECT 0
  UNION ALL
  SELECT id + 1 FROM venue_ids WHERE id + 1 < 100000
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 1M) */
  v.id,
  c.y, c.x,
  CHAR(ORD('A') + FLOOR(RAND() * 3) USING ASCII) `row`,
  FLOOR(RAND() * 3) `number`
FROM venue_ids v
     JOIN (
       VALUES
         ROW(0, 0),
         ROW(0, 1),
         ROW(1, 0),
         ROW(1, 2),
         ROW(2, 0)
     ) c (y, x)
;

ANALYZE TABLE seats;

一些注意事项：

在这里，CTE的使用方式很有趣（希望如此！）：每个循环代表一个placen_id，但是由于我们希望为每个场所生成多个位置，因此我们对包含这些位置的表进行了交叉联接。
v8.0.19（VALUES ROW()...）行构造函数用于表示（可联接）表，而无需实际创建它。
为行和数字生成随机值作为占位符。
为了简单起见，我们没有进行任何优化（例如，数据类型超出了必要的范围；在插入记录之前添加了索引等）。

旧方法

好的旧方法非常简单明了：

SET @venue_id = 5000; --  venue id;  () id 

SET @grouping = -1;
SET @y = -1;
SET @x = -1;

WITH seat_groupings (id, y, x, `grouping`, tmp_y, tmp_x) AS
(
  SELECT
    id, y, x,
    @grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
    @y := seats.y,
    @x := seats.x
  FROM seats
  WHERE venue_id = @venue_id
  ORDER BY y, x
)
UPDATE
  seats s
  JOIN seat_groupings sg USING (id)
SET s.grouping = sg.grouping
;

-- Query OK, 5 rows affected, 3 warnings (0,00 sec)

好吧，这很容易（但请不要忘记警告）！

一个小题外话：在这种情况下，我使用布尔算术的属性。以下表达式是等效的：

SELECT seats.x > @x + 1 OR seats.y != @y `increment`;

SELECT IF (
  seats.x > @x + 1 OR seats.y != @y,
  1,
  0
) `increment`;

有些人觉得这很直观，而另一些人则没有。这是一个品味问题。从现在开始，我将使用一个更紧凑的表达式。

让我们看一下结果：

SELECT id, y, x, `grouping` FROM seats WHERE venue_id = @venue_id ORDER BY y, x;

-- +-------+------+------+----------+
-- | id    | y    | x    | grouping |
-- +-------+------+------+----------+
-- | 24887 |    0 |    0 |        1 |
-- | 27186 |    0 |    1 |        2 |
-- | 29485 |    1 |    0 |        4 |
-- | 31784 |    1 |    2 |        6 |
-- | 34083 |    2 |    0 |        8 |
-- +-------+------+------+----------+

好办法！

las，它有一个“次要”缺点：它工作得很好，除非它不起作用...

事实是查询优化器不一定从左到右执行计算，因此赋值（：=）可以以错误的顺序执行导致错误的结果。人们在更新MySQL之后经常会遇到此问题。

在MySQL 8.0中，确实不推荐使用此功能：

--    UPDATE.
--
SHOW WARNINGS\G
-- *************************** 1. row ***************************
--   Level: Warning
--    Code: 1287
-- Message: Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'.
-- [...]

好吧，让我们解决这种情况！

现代方法1：开窗函数

在MySQL世界中，引入窗口函数一直是备受期待的事件。

一般而言，窗口函数的“滑动”性质与累积函数配合得很好。但是，某些复杂的累积函数需要最后一个表达式的结果-窗口函数不支持的功能，因为它们在列上运行。

这并不意味着问题无法解决；仅需重新考虑。

在我们的案例中，任务可以分为两部分。每个位置的分组可以视为两个值的总和：

每个地方的序列号
此位置之前所有位置的增量的累积值。

那些熟悉窗口功能的人会在这里认识到典型的模式。

每个座位的序列号是一个内置功能：

ROW_NUMBER() OVER <window>

但是有了累积值，一切都会变得更加有趣。要进行计算，我们执行两个操作：

计算每个位置的增量并将其记入表格（或CTE），
然后，对于每个位置，我们使用window函数求和该位置的增量。

让我们看一下SQL：

WITH
increments (id, increment) AS
(
  SELECT
    id,
    x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw
  FROM seats
  WHERE venue_id = @venue_id
  WINDOW tzw AS (ORDER BY y, x)
)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw + SUM(increment) OVER tzw `grouping`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+---+---+----------+
-- | id    | y | x | grouping |
-- +-------+---+---+----------+
-- | 24887 | 0 | 0 |        1 |
-- | 27186 | 0 | 1 |        2 |
-- | 29485 | 1 | 0 |        4 |
-- | 31784 | 1 | 2 |        6 |
-- | 34083 | 2 | 1 |        8 |
-- +-------+---+---+----------+

大！

（请注意，为简便起见，我从现在开始省略UPDATE。）

让我们分析一下请求。

高级逻辑

以下CTE （已编辑）：

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1 OR y != LAG(y, 1, y) OVER tzw `increment`
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)
;

-- +-------+-----------+
-- | id    | increment |
-- +-------+-----------+
-- | 24887 |         0 |
-- | 27186 |         0 |
-- | 29485 |         1 |
-- | 31784 |         1 |
-- | 34083 |         1 |
-- +-------+-----------+

…计算每个位置相对于上一个位置的增量（LAG()稍后再讨论）。它适用于每条记录及其前面的记录，并且不是累积的。

现在，要计算累积增量，我们将仅使用窗口函数来计算每个位置（包括每个位置）的总和：

-- (CTE here...)
SELECT
  s.id, y, x,
  ROW_NUMBER() OVER tzw `pos.`,
  SUM(increment) OVER tzw `cum.incr.`
FROM seats s
     JOIN increments i USING (id)
WINDOW tzw AS (ORDER BY y, x);

-- +-------+---+---+------+-----------+
-- | id    | y | x | pos. | cum.incr. | (grouping)
-- +-------+---+---+------+-----------+
-- | 24887 | 0 | 0 |    1 |         0 | = 1 + 0 (curr.)
-- | 27186 | 0 | 1 |    2 |         0 | = 2 + 0 (#24887) + 0 (curr.)
-- | 29485 | 1 | 0 |    3 |         1 | = 3 + 0 (#24887) + 0 (#27186) + 1 (curr.)
-- | 31784 | 1 | 2 |    4 |         2 | = 4 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (curr.)
-- | 34083 | 2 | 1 |    5 |         3 | = 5 + 0 (#24887) + 0 (#27186) + 1 (#29485) + 1 (#31784)↵
-- +-------+---+---+------+-----------+     + 1 (curr.)

LAG（）窗口功能

LAG函数以最简单的形式（LAG(x)）返回给定列的先前值。此类功能的经典不便之处在于处理窗口中的第一个条目。由于没有先前的记录，因此它们返回NULL。对于LAG，可以将所需值指定为第三个参数：

LAG(x, 1, x - 1) --    `x -1`
LAG(y, 1, y)     --    `y`

通过指定默认值，我们确保窗口边界中的第一个位置与其他位置（x-1）之后的位置具有相同的逻辑，并且不更改行（y）。

一种替代解决方案是使用IFNULL，但是表达式非常麻烦：

--  ,  !
--
IFNULL(x > LAG(x) OVER tzw + 1 OR y != LAG(y) OVER tzw, 0)
IFNULL(x > LAG(x) OVER tzw + 1, FALSE) OR IFNULL(y != LAG(y) OVER tzw, FALSE)

第二个参数LAG()是窗口内要移回的位置数。1是先前的值（也是默认值）。

技术方面

命名窗口

我们的查询多次使用同一窗口。以下两个查询在形式上是等效的：

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x);

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER (ORDER BY y, x) + 1
    OR y != LAG(y, 1, y) OVER (ORDER BY y, x)
FROM seats
WHERE venue_id = @venue_id;

但是，第二个可能导致次优的行为（至少在过去，我已经遇到过）：优化程序可以将窗口视为独立窗口，并分别计算每个窗口。因此，我建议您始终使用命名窗口（至少在重复使用它们时）。

PARTITION BY陈述式

通常，窗口功能是在分区上执行的。在我们的情况下，它将如下所示：

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (PARTITION BY venue_id ORDER BY y, x); -- !

由于窗口与完整的记录集匹配（由条件过滤WHERE），因此我们不需要指定它（分区）。

但是，如果必须在整个表上运行此查询seats，则必须这样做，以便为所有人重置窗口venue_id。

排序

该请求ORDER BY是在窗口级别设置的：

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS (ORDER BY y, x)

在这种情况下，窗口排序与SELECT分开。这是非常重要的！此请求的行为：

SELECT
  id,
  x > LAG(x, 1, x - 1) OVER tzw + 1
    OR y != LAG(y, 1, y) OVER tzw
FROM seats
WHERE venue_id = @venue_id
WINDOW tzw AS ()
ORDER BY y, x

…未定义。我们来看一下手册：

查询结果字符串是在执行WHERE，GROUP BY和HAVING子句之后从FROM子句确定的，并且在ORDER BY，LIMIT和SELECT DISTINCT之前在窗口内执行。

一些注意事项

一般而言，对于此类问题，有必要计算每条记录的状态变化，然后对其求和-而不是将每条记录表示为前一条的函数。

该解决方案比其替代的功能复杂，但同时又可靠。las，这种方法并非总是可行或容易实现。这就是递归CTE发挥作用的地方。

现代方法2：递归CTE

由于MySQL中CTE的功能有限，因此此方法需要一些技巧。另一方面，它是一种万能的直接解决方案，因此不需要重新考虑全局方法。

让我们从最终请求的简化版本开始：

-- `p_`  `Previous`    
--
WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s
  WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
  ORDER BY s.venue_id, s.y, s.x
  LIMIT 1
)
SELECT * FROM groupings;

答对了！该查询（相对）简单，但更重要的是，它以最简单的方式表示累积分组功能：

p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)

--   :

@grouping := @grouping + 1 + (seats.x > @x + 1 OR seats.y != @y),
@y := seats.y,
@x := seats.x

即使对于不太熟悉CTE的人，逻辑也很清楚。第一排是大厅的第一个座位，顺序为：

SELECT id, venue_id, y, x, 1
FROM seats
WHERE venue_id = @venue_id
ORDER BY y, x
LIMIT 1

在递归部分，我们迭代：

SELECT
  s.id, s.venue_id, s.y, s.x,
  p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
FROM groupings, seats s
WHERE s.venue_id = p_venue_id AND (s.y, s.x) > (p_y, p_x)
ORDER BY s.venue_id, s.y, s.x
LIMIT 1

条件WHERE与运营商一起ORDER BY，并LIMIT简单地寻找下一个地方，用同样的地方venue_id，但所用的序列中lshimi坐标（X，Y）（venue_id，X，Y）。排序表达式中

的部分s.venue_id非常重要！它允许我们使用索引。

操作员SELECT：

执行累积（计算(p_)grouping），
为当前位置（值s.id，s.venue_id，s.y，s.x在下一周期）。

我们选择FROM groupings满足CTE递归性的要求。

这里有趣的是，我们使用递归CTE作为迭代器，从groupings递归子查询中的表中获取seats数据，然后将其联接以查找数据以进行进一步处理。

JOIN是正式的交叉，但由于有运算符，LIMIT仅返回一个记录。

工作版本

不幸的是，上述查询不起作用，因为ORDER BY递归子查询当前不支持该查询。另外，LIMIT这里使用的语义不同于应用于外部查询的典型语义：

现在支持LIMIT [..]对结果数据集的影响与将LIMIT与外部SELECT一起使用是相同的

但是，这不是一个严重的问题。让我们看一个工作版本：

WITH RECURSIVE groupings (p_id, p_venue_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, venue_id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.venue_id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT si.id
    FROM seats si
    WHERE si.venue_id = p_venue_id AND (si.y, si.x) > (p_y, p_x)
    ORDER BY si.venue_id, si.y, si.x
    LIMIT 1
  )
)
SELECT * FROM groupings;

-- +-------+------+------+------------+
-- | p_id  | p_y  | p_x  | p_grouping |
-- +-------+------+------+------------+
-- | 24887 |    0 |    0 |          1 |
-- | 27186 |    0 |    1 |          2 |
-- | 29485 |    1 |    0 |          4 |
-- | 31784 |    1 |    2 |          6 |
-- | 34083 |    2 |    0 |          8 |
-- +-------+------+------+------------+

使用子查询有点不舒服，但是这种方法有效，并且这里的样板最少，因为无论如何都需要多个表达式。

在这里，而不是做了排序，并限制与工会相关的groupings和seats，我们这样做的子查询中，并把它传递给外部查询，然后只选择目标记录。

对绩效的思考

让我们使用EXPLAIN ANALYZE检查查询执行计划：

mysql> EXPLAIN ANALYZE WITH RECURSIVE groupings [...]

-> Table scan on groupings  (actual time=0.000..0.001 rows=5 loops=1)
    -> Materialize recursive CTE groupings  (actual time=0.140..0.141 rows=5 loops=1)
        -> Limit: 1 row(s)  (actual time=0.019..0.019 rows=1 loops=1)
            -> Index lookup on seats using venue_id_y_x (venue_id=(@venue_id))  (cost=0.75 rows=5) (actual time=0.018..0.018 rows=1 loops=1)
        -> Repeat until convergence
            -> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
                -> Scan new records on groupings  (cost=2.73 rows=2) (actual time=0.001..0.001 rows=2 loops=2)
                -> Filter: (s.id = (select #5))  (cost=0.30 rows=1) (actual time=0.020..0.020 rows=1 loops=5)
                    -> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
                    -> Select #5 (subquery in condition; dependent)
                        -> Limit: 1 row(s)  (actual time=0.007..0.008 rows=1 loops=9)
                            -> Filter: ((si.y,si.x) > (groupings.p_y,groupings.p_x))  (cost=0.75 rows=5) (actual time=0.007..0.007 rows=1 loops=9)
                                -> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)

该计划符合预期。在这种情况下，最佳计划的基础在于索引搜索：

-> Nested loop inner join  (cost=3.43 rows=2) (actual time=0.017..0.053 rows=2 loops=2)
-> Single-row index lookup on s using PRIMARY (id=(select #5))  (cost=0.30 rows=1) (actual time=0.014..0.014 rows=1 loops=5)
-> Index lookup on si using venue_id_y_x (venue_id=groupings.p_venue_id)  (cost=0.75 rows=5) (actual time=0.006..0.006 rows=4 loops=9)

……至关重要。如果您扫描索引（即线性扫描索引记录而不是寻找正确的记录），性能将大大下降。

因此，对于这一战略工作，链接索引必须到位，并通过优化尽可能高效地使用。

如果将来解除限制，则将无需使用子查询，这将大大简化优化程序的任务。

次优计划的替代方案

如果无法确定最佳计划，请使用临时表：

CREATE TEMPORARY TABLE selected_seats (
  id INT NOT NULL PRIMARY KEY,
  y INT,
  x INT,
  UNIQUE (y, x)
)
SELECT id, y, x
FROM seats WHERE venue_id = @venue_id;

WITH RECURSIVE
groupings (p_id, p_y, p_x, p_grouping) AS
(
  (
    SELECT id, y, x, 1
    FROM seats
    WHERE venue_id = @venue_id
    ORDER BY y, x
    LIMIT 1
  )

  UNION ALL

  SELECT
    s.id, s.y, s.x,
    p_grouping + 1 + (s.x > p_x + 1 OR s.y != p_y)
  FROM groupings, seats s WHERE s.id = (
    SELECT ss.id
    FROM selected_seats ss
    WHERE (ss.y, ss.x) > (p_y, p_x)
    ORDER BY ss.y, ss.x
    LIMIT 1
    )
)
SELECT * FROM groupings;

即使在该查询中通过了索引扫描，由于表selected_seats很小，因此它们也花费很多钱。

结论

我感到非常高兴的是，高效但有缺陷的工作流程现在可以被MySQL 8.0中引入的相当简单的功能所取代。

同时，针对8.0的新功能的开发仍在继续，这使得已经成功发行的版本变得更好。

成功递归！

译者的PS

另请参阅我们的博客：

在MySQL 8.0中使用窗口函数和CTE来实现累计总数而不会造成黑客攻击