MySQL 8.0新特性专栏目录
《MySQL开发规范》过时了,视图查询性能提升了一万倍
你真的会用EXPLAIN么,SQL性能优化王者晋级之路
索引三剑客之降序索引和不可见索引
千呼万唤始出来,MySQL 8.0索引三剑客之函数索引
双重密码,MySQL 8.0创新特性
sql_mode兼容性,MySQL 8.0 升级踩过的坑
警惕参数变化,MySQL 8.0 升级避免再次踩坑
《MySQL开发规范》过时了,视图的查询性能提升了一万倍
前言
视图在数据库中是非常普及的功能。但是长期以来,大多数互联网公司的《MySQL开发规范》中都有一条规范:在MySQL中禁止(或建议不要)使用视图
。究其原因,主要是由于在MySQL中视图的查询性能不好,同时带来了管理维护上的高成本。
不过随着MySQL 8.0中派生条件下推特性的引入,尤其是最近GA的MySQL 8.0.29版本中对于包含union子句的派生条件下推优化,MySQL中视图查询的性能得到了质的提升。
《MySQL开发规范》已经过时了,DBA该考虑考虑将禁止使用视图的规定重新修订一下了。
1. 派生条件下推优化特性
1.1 什么是派生条件下推优化
在讨论视图之前,我们先了解一下什么是派生条件下推优化。派生条件下推优化,是在MySQL 8.0中引入的一项针对优化器的优化特性,对于存在物化派生表的SQL查询,可以实现派生条件下推优化,即将外层查询子句的过滤条件下推到派生表内部,以减少派生表返回行数,同时可以利用派生表上对应的索引以提高查询效率。
如果派生表上没有使用聚合或者是窗口函数,那么可以直接将外层过滤条件下推到派生表的where条件上过滤;如果派生表上使用了聚合查询(group by),那么一般情况下可以将外层过滤条件下推到派生表聚合之后的having子句;如果派生表上使用了窗口函数,那么可以将外层过滤条件下推到派生表的窗口函数的partition子句(视具体情况而定)。
在MySQL 8.0中派生条件下推是默认开启的,由optimizer_switch系统变量的derived_condition_pushdown标志控制。
1.2 派生条件下推的限制条件
派生条件下推的限制:
- 当派生表上使用了limit限制返回行数时,将无法使用派生条件下推;
- 外层条件包含子查询时不能使用派生条件下推;
- 如果派生表是外连接的内表,则不能使用派生条件下推优化;
- 如果物化派生表是通用表表达式,它会被多次引用,则不会将外层条件下推到通用表表达式;
- 从MySQL 8.0.28开始,如果派生表的SELECT列表包含对用户变量的任何赋值,则条件不能被下推。
1.3 优化器应用派生条件下推的几个场景
场景一: SQL查询的派生表上没有使用聚合或者窗口函数
例如:
# 原始SQL
SELECT * FROM (SELECT f1, f2 FROM t1) AS dt WHERE f1 < 3 AND f2 > 11
# 优化器转换后SQL
SELECT f1, f2 FROM (SELECT f1, f2 FROM t1 WHERE f1 < 3 AND f2 > 11) AS dt
场景二:SQL查询的派生表上使用了group by分组,并且外层过滤条件不是group by分组字段的一部分
例如:
# 原始SQL
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j) AS dt WHERE sum > 100
# 优化器转换后SQL
SELECT * FROM (SELECT i, j, SUM(k) AS sum FROM t1 GROUP BY i, j HAVING sum > 100) AS dt
场景三:SQL查询的派生表使用了group by分组,并且外城过滤条件是 group by分组字段的一部分
例如:
# 原始SQL
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 GROUP BY i,j) AS dt WHERE i > 10
# 优化器转换后SQL
SELECT * FROM (SELECT i,j, SUM(k) AS sum FROM t1 WHERE i > 10 GROUP BY i,j) AS dt
1.4 派生条件下推优化的一个特例
在MySQL 8.0.29之前,如果派生表上使用了union聚合,那么派生条件下推特性将失效。不过从新发布的MySQL 8.0.29开始,即使在派生表上使用了union聚合,MySQL依旧能够使用派生条件下推特性对SQL查询进行优化。
我们照常来举一个实际的例子,这个例子是MySQL 8.0.29的官方文档上的例子的一个改良版(原版的示例是不太合适的,我已经跟官方提了建议)。
示例:
我们先定义一个对两张基表取并集的简单的视图,如下:
# 视图定义
CREATE TABLE t1 (
id INT NOT NULL