22.MySQL优化Window Function Optimization

最新推荐文章于 2025-05-22 09:04:46 发布

原创最新推荐文章于 2025-05-22 09:04:46 发布 · 477 阅读

1 ·

CC 4.0 BY-SA版权

MySql 专栏收录该内容

75 篇文章

订阅专栏

本文探讨了窗口函数在SQL查询中的使用及其对优化程序策略的影响，包括派生表的使用、执行计划的变化、窗口函数与聚合函数的区别，以及窗口函数评估如何受系统变量影响。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

介绍

窗口函数会影响优化程序考虑的策略：

假如一个子查询具有窗口函数则关闭Derived table（派生表），子查询始终使用materialized。举例如下：

  EXPLAIN SELECT * 
  FROM 
  (SELECT * FROM city WHERE population > 10*1000) AS big_city
  WHERE 
  big_city.country='Germany';

在不使用Derived table的时候，执行计划为：

  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows | Extra       |
  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  |  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL | 4068 | Using where |
  |  2 | DERIVED     | City       | ALL  | Population    | NULL | NULL    | NULL | 4079 | Using where |
  +----+-------------+------------+------+---------------+------+---------+------+------+-------------+
  2 rows in set (0.60 sec)

再使用Derived table的时候执行计划为：

  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  | id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
  |  1 | SIMPLE      | city  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
  +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

Semi-join不适合于窗口函数优化，因为semi-joins用于子查询的WHERE 和 JOIN … ON中无法包含窗口函数。
优化器按顺序处理具有相同排序要求的多个窗口，因此可以在第一个窗口之后跳过排序。
优化器不会去合并可以再一个步骤计算完成的窗口函数，例如当多个窗口函数包含相同的OVER列。解决方法是在WINDOW子句中定义窗口并引用OVER子句中的窗口名称。

一个聚合函数不能用作窗口函数，在查询的最外层聚合。举例，在下面的例子中，MySQL观察到COUNT(t1.b)在外查询中一些不存在的东西，因为他放在了WHERE子句中。

SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);

因此，MySQL在子查询中聚合，将t1.b视为常量并返回t2的行数。

假如WHERE用HAVING替换，则出现如下错误：

mysql> SELECT * FROM t1 HAVING t1.a = (SELECT COUNT(t1.b) FROM t2);
ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1
of SELECT list contains nonaggregated column 'test.t1.a'; this is
incompatible with sql_mode=only_full_group_by

发生错误是因为COUNT(t1.b)可以存在HAVING，因此使外部查询聚合。

窗口函数（包括被用作聚合函数的窗口函数），没有前面的复杂性。它们总是在编写它们的子查询中聚合，而不是在外部查询中聚合。

窗口函数评估可能受windowing_use_high_precision 系统变量值的影响，该值确定是否在不损失精度的情况下计算窗口操作。默认情况下， windowing_use_high_precision 已启用。

对于某些移动帧聚合，可以应用反聚合函数从聚合中移除值。这可以提高性能，但可能会导致精度下降。例如，将一个非常小的浮点值添加到一个非常大的值会导致非常小的值被大值“ 隐藏 ”。当稍后反转大值时，小值的效果会丢失。（这个问题参见IEEE浮点数）。

由于反向聚合导致的精度损失仅是对浮点（近似值）数据类型的操作的因素。对于其他类型，反向聚合是安全的，这包括DECIMAL（它允许一个小数部分，但它是一个精确的值类型。）

为了更快的查询，MySQL会在反向聚合安全时始终使用反向聚合。

对于浮点值，反向聚合并不总是安全的，可能会导致精度损失。默认避免反向聚合，这样效率较低但保留精度。如果允许反向聚合，可以禁用windowing_use_high_precision 选项。
对于非浮点数据类型，反向聚合始终是安全的，无论windowing_use_high_precision 值如何都使用。
windowing_use_high_precision 对任何情况下都不进行反向聚合的MIN()和 MAX()没有影响。

通过计算方差函数 STDDEV_POP(), STDDEV_SAMP(), VAR_POP(), VAR_SAMP(),和他们的同义词，优化器可以再优化模式和默认模式下切换。优化模式可能会在最后有效数字中产生略微不同的结果。如果允许这样的差异，可以禁用windowing_use_high_precision以允许优化模式。

因为EXPLAIN，窗口执行计划信息过于庞大，无法以传统输出格式显示。要查看窗口信息，请使用 EXPLAIN FORMAT=JSON并查找 windowing元素。