《MySQL开发规范》过时了，视图的查询性能提升了一万倍

MySQL 8.0新特性专栏目录

《MySQL开发规范》过时了，视图查询性能提升了一万倍
你真的会用EXPLAIN么，SQL性能优化王者晋级之路
索引三剑客之降序索引和不可见索引
千呼万唤始出来，MySQL 8.0索引三剑客之函数索引
双重密码，MySQL 8.0创新特性
sql_mode兼容性，MySQL 8.0 升级踩过的坑
警惕参数变化，MySQL 8.0 升级避免再次踩坑

---

---

前言

视图在数据库中是非常普及的功能。但是长期以来，大多数互联网公司的《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