MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
Window functions affect the strategies the optimizer considers:
Derived table merging for a subquery is disabled if the subquery has window functions. The subquery is always materialized.
Semijoins基于“增大化现实”技术e not applicable to window function optimization because semijoins apply to subqueries in
WHEREandJOIN ... ON, which cannot contain window functions.The optimizer processes multiple windows that have the same ordering requirements in sequence, so sorting can be skipped for windows following the first one.
The optimizer makes no attempt to merge windows that could be evaluated in a single step (for example, when multiple
OVERclauses contain identical window definitions). The workaround is to define the window in aWINDOWclause and refer to the window name in theOVERclauses.
An aggregate function not used as a window function is aggregated in the outermost possible query. For example, in this query, MySQL sees thatCOUNT(t1.b)is something that cannot exist in the outer query because of its placement in theWHEREclause:
SELECT * FROM t1 WHERE t1.a = (SELECT COUNT(t1.b) FROM t2);Consequently, MySQL aggregates inside the subquery, treatingt1.bas a constant and returning the count of rows oft2.
ReplacingWHEREwithHAVINGresults in an error:
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_byThe error occurs becauseCOUNT(t1.b)can exist in theHAVING, and so makes the outer query aggregated.
Window functions (including aggregate functions used as window functions) do not have the preceding complexity. They always aggregate in the subquery where they are written, never in the outer query.
Window function evaluation may be affected by the value of thewindowing_use_high_precisionsystem variable, which determines whether to compute window operations without loss of precision. By default,windowing_use_high_precisionis enabled.
For some moving frame aggregates, the inverse aggregate function can be applied to remove values from the aggregate. This can improve performance but possibly with a loss of precision. For example, adding a very small floating-point value to a very large value causes the very small value to be“hidden”by the large value. When inverting the large value later, the effect of the small value is lost.
Loss of precision due to inverse aggregation is a factor only for operations on floating-point (approximate-value) data types. For other types, inverse aggregation is safe; this includesDECIMAL, which permits a fractional part but is an exact-value type.
For faster execution, MySQL always uses inverse aggregation when it is safe:
For floating-point values, inverse aggregation is not always safe and might result in loss of precision. The default is to avoid inverse aggregation, which is slower but preserves precision. If it is permissible to sacrifice safety for speed,
windowing_use_high_precisioncan be disabled to permit inverse aggregation.For nonfloating-point data types, inverse aggregation is always safe and is used regardless of the
windowing_use_high_precisionvalue.windowing_use_high_precisionhas no effect onMIN()andMAX(), which do not use inverse aggregation in any case.
For evaluation of the variance functionsSTDDEV_POP(),STDDEV_SAMP(),VAR_POP(),VAR_SAMP(), and their synonyms, evaluation can occur in optimized mode or default mode. Optimized mode may produce slightly different results in the last significant digits. If such differences are permissible,windowing_use_high_precisioncan be disabled to permit optimized mode.
ForEXPLAIN, windowing execution plan information is too extensive to display in traditional output format. To see windowing information, use解释格式= JSONand look for thewindowingelement.