MySQL高级特性之：`MySQL`的`Window Functions`：`RANGE`与`ROWS`在窗口函数中的性能差异。

MySQL Window Functions: RANGE vs. ROWS – A Performance Deep Dive

各位听众，大家好！今天我们来深入探讨MySQL窗口函数中RANGE和ROWS的性能差异。窗口函数是MySQL 8.0引入的强大特性，它们允许我们在结果集的窗口（一组相关的行）上执行计算，而无需使用子查询或自连接。RANGE和ROWS是定义窗口大小的关键子句，但它们的工作方式不同，性能影响也大相径庭。理解它们的区别，对于编写高效的SQL查询至关重要。

1. 窗口函数基础回顾

在深入RANGE和ROWS之前，我们先快速回顾一下窗口函数的基本概念。一个典型的窗口函数语法如下：

window_function(argument1, argument2, ...) OVER (
    [PARTITION BY column1, column2, ...]
    [ORDER BY column3, column4, ...]
    [frame_clause]
)

• window_function: 这是我们要执行的函数，例如SUM(), AVG(), RANK(), ROW_NUMBER()等。
• PARTITION BY: 将结果集分成多个分区。窗口函数将在每个分区内独立计算。
• ORDER BY: 定义每个分区内行的排序方式。这对于某些窗口函数（如RANK()）是必需的，并且对于RANGE子句至关重要。
• frame_clause: 定义窗口的大小或范围。这就是我们今天要重点讨论的RANGE和ROWS子句所在的位置。

2. ROWS子句：基于物理行数

ROWS子句定义窗口的大小，它基于结果集中行的物理顺序。它指定了当前行之前和之后要包含多少行。ROWS子句的语法如下：

ROWS BETWEEN preceding AND following

其中preceding和following可以是以下值之一：

• UNBOUNDED PRECEDING: 窗口从分区的开始处开始。
• UNBOUNDED FOLLOWING: 窗口延伸到分区的结束处。
• CURRENT ROW: 窗口包括当前行。
• n PRECEDING: 窗口包括当前行之前的 n 行。
• n FOLLOWING: 窗口包括当前行之后的 n 行。

示例：

假设我们有一个名为sales的表，包含以下数据：

sale_date	product_id	quantity	price
2023-01-01	1	2	10
2023-01-01	2	1	20
2023-01-02	1	3	10
2023-01-02	2	2	20
2023-01-03	1	1	10
2023-01-03	2	3	20

以下查询使用ROWS子句计算每个产品的三日滚动销量：

SELECT
    sale_date,
    product_id,
    quantity,
    SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM
    sales;

结果如下：

sale_date	product_id	quantity	rolling_sum
2023-01-01	1	2	2
2023-01-02	1	3	5
2023-01-03	1	1	6
2023-01-01	2	1	1
2023-01-02	2	2	3
2023-01-03	2	3	6

在这个例子中，ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示对于每一行，窗口包括当前行以及之前的两行（如果存在）。

3. RANGE子句：基于逻辑值范围

RANGE子句也定义窗口的大小，但它不是基于物理行数，而是基于ORDER BY子句中指定的列的值的范围。这意味着具有相同ORDER BY值的行将被视为同一组，并包含在同一个窗口中。RANGE子句的语法与ROWS类似：

RANGE BETWEEN preceding AND following

其中preceding和following的取值略有不同：

• UNBOUNDED PRECEDING: 窗口从分区的开始处开始。
• UNBOUNDED FOLLOWING: 窗口延伸到分区的结束处。
• CURRENT ROW: 窗口包括与当前行具有相同ORDER BY值的行。
• value PRECEDING: 窗口包括ORDER BY值在当前行值减去value范围内的行。
• value FOLLOWING: 窗口包括ORDER BY值在当前行值加上value范围内的行。

注意： RANGE子句的value只能用于数值或日期/时间类型，并且必须是常量表达式。不能是列名或复杂的计算表达式。

示例：

我们继续使用sales表，并使用RANGE子句计算每个产品在前后一天内的总销量：

SELECT
    sale_date,
    product_id,
    quantity,
    SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING) AS range_sum
FROM
    sales;

结果如下：

sale_date	product_id	quantity	range_sum
2023-01-01	1	2	5
2023-01-01	2	1	3
2023-01-02	1	3	6
2023-01-02	2	2	6
2023-01-03	1	1	4
2023-01-03	2	3	5

在这个例子中，RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING表示对于每一行，窗口包括sale_date在当前日期前后一天内的所有行。 例如，对于2023-01-01的product_id=1,窗口包括2023-01-01 (quantity=2)和2023-01-02 (quantity=3),总和为5. 对于2023-01-03的product_id=1, 窗口包括2023-01-02 (quantity=3)和2023-01-03 (quantity=1),总和为4.

关键区别：

• ROWS基于物理行数，而RANGE基于ORDER BY列的值范围。
• 如果ORDER BY列有重复值，RANGE CURRENT ROW会将所有具有相同值的行包含在窗口中，而ROWS CURRENT ROW仅包含当前行。
• RANGE子句中的value只能是常量表达式，而ROWS没有这个限制。

4. 性能差异分析

现在我们来讨论RANGE和ROWS的性能差异。一般来说，ROWS子句通常比RANGE子句更有效率。 这是因为：

1. 索引利用率： ROWS子句通常可以更好地利用索引。当使用ROWS子句时，MySQL可以根据ORDER BY列的索引，直接定位到需要包含的行，而不需要进行范围扫描。

2. 范围计算复杂度： RANGE子句需要进行范围计算，这会增加计算复杂度，尤其是当ORDER BY列是日期/时间类型，并且范围较大时。MySQL需要为每一行计算范围，并找到符合条件的行。

3. 重复值处理： 当ORDER BY列有大量重复值时，RANGE子句可能会导致窗口包含大量的行，从而降低性能。ROWS子句则不受此影响，因为它只考虑物理行数。

为了更直观地了解性能差异，我们创建一个包含大量数据的测试表test_sales：

CREATE TABLE test_sales (
    sale_date DATE,
    product_id INT,
    quantity INT,
    price DECIMAL(10, 2)
);

-- 插入大量数据 (例如 100 万行)
INSERT INTO test_sales (sale_date, product_id, quantity, price)
SELECT
    DATE(DATE_ADD('2023-01-01', INTERVAL (FLOOR(RAND() * 365)) DAY)),
    FLOOR(RAND() * 100) + 1,
    FLOOR(RAND() * 10) + 1,
    (FLOOR(RAND() * 100) + 1) * 1.5
FROM
    information_schema.COLUMNS
LIMIT 1000000;

-- 创建索引
CREATE INDEX idx_sale_date ON test_sales (sale_date);
CREATE INDEX idx_product_id_sale_date ON test_sales (product_id, sale_date);

然后，我们分别使用ROWS和RANGE子句执行类似的查询，并比较它们的执行时间：

使用 ROWS 子句：

SELECT
    sale_date,
    product_id,
    quantity,
    SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM
    test_sales;

使用 RANGE 子句：

SELECT
    sale_date,
    product_id,
    quantity,
    SUM(quantity) OVER (PARTITION BY product_id ORDER BY sale_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS range_sum
FROM
    test_sales;

通过使用EXPLAIN语句分析查询计划，我们可以看到ROWS子句通常能够更好地利用索引，而RANGE子句可能会导致全表扫描。

表格对比：

特性	ROWS	RANGE
定义窗口	基于物理行数	基于ORDER BY列的值范围
索引利用率	通常更好	可能较差
计算复杂度	较低	较高
重复值影响	影响较小	可能导致窗口包含大量行
适用场景	需要基于物理行数的滑动窗口计算	需要基于值范围的滑动窗口计算，且ORDER BY列重复值较少
性能	通常更高效	可能较慢
value限制	无	只能是常量表达式，且适用于数值或日期/时间类型

5. 何时使用 RANGE？

尽管ROWS通常更有效率，但在某些情况下，RANGE是更合适的选择：

• 逻辑范围需求： 当我们需要基于ORDER BY列的值范围来定义窗口时，RANGE是唯一的选择。例如，计算前后一周内的平均温度，或者计算特定时间窗口内的总销售额。
• 重复值处理： 当我们需要将具有相同ORDER BY值的行视为同一组时，RANGE CURRENT ROW可以方便地实现这一需求。
• 数据稀疏性： 如果ORDER BY列的值分布稀疏，RANGE子句可能不会包含大量的行，从而避免性能问题。

6. 优化策略

无论使用RANGE还是ROWS，都可以通过以下策略来优化性能：

• 创建合适的索引： 确保PARTITION BY和ORDER BY列都有索引，以便MySQL能够快速定位到需要的行。
• 避免不必要的排序： 如果不需要排序，可以省略ORDER BY子句。
• 缩小窗口大小： 窗口越小，计算复杂度越低。
• 使用EXPLAIN分析查询计划： 通过EXPLAIN语句，我们可以了解MySQL如何执行查询，并识别潜在的性能瓶颈。
• 数据类型优化： 选择合适的数据类型，特别是对于日期/时间类型，可以提高范围计算的效率。

7. 案例分析

假设我们有一个存储股票交易数据的表stock_trades，包含以下字段：

• trade_date (DATE): 交易日期
• stock_symbol (VARCHAR): 股票代码
• trade_price (DECIMAL): 交易价格

我们希望计算每只股票的7日移动平均交易价格。

使用 ROWS 的错误示例：

SELECT
    trade_date,
    stock_symbol,
    trade_price,
    AVG(trade_price) OVER (PARTITION BY stock_symbol ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_average
FROM
    stock_trades;

这个查询的问题在于，如果某一天没有交易数据，窗口仍然会包含前6天的数据，导致计算结果不准确。

使用 RANGE 的正确示例：

SELECT
    trade_date,
    stock_symbol,
    trade_price,
    AVG(trade_price) OVER (PARTITION BY stock_symbol ORDER BY trade_date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW) AS moving_average
FROM
    stock_trades;

这个查询使用RANGE子句，确保窗口只包含trade_date在当前日期前6天内的交易数据，即使某些日期没有交易数据，也能得到正确的移动平均值。

8. 总结：选择合适的窗口定义方式

在MySQL窗口函数中，RANGE和ROWS都是用于定义窗口大小的重要子句。ROWS基于物理行数，通常更有效率，适用于需要基于物理行数的滑动窗口计算。RANGE基于ORDER BY列的值范围，适用于需要基于值范围的滑动窗口计算，但需要注意其性能开销。在实际应用中，我们需要根据具体的需求和数据特点，选择合适的窗口定义方式，并结合索引优化和其他性能优化策略，编写高效的SQL查询。 理解它们的区别，选择合适的子句，能让你写出更高效，更符合逻辑的SQL查询。

感谢大家的聆听！