《MySQL开发规范》过时了，视图的查询性能提升了一万倍
《MySQL开发规范》过时了，视图的查询性能提升了一万倍
《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 dt1.4 派生条件下推优化的一个特例
在MySQL 8.0.29之前，如果派生表上使用了union聚合，那么派生条件下推特性将失效。不过从新发布的MySQL 8.0.29开始，即使在派生表上使用了union聚合，MySQL依旧能够使用派生条件下推特性对SQL查询进行优化。
我们照常来举一个实际的例子，这个例子是MySQL 8.0.29的官方文档上的例子的一个改良版（原版的示例是不太合适的，我已经跟官方提了建议）。
示例： 我们先定义一个对两张基表取并集的简单的视图，如下：
# 视图定义
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 INT,
c2 varchar(32) DEFAULT NULL,
KEY i1 (c1)
);
CREATE TABLE t2 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
c1 INT,
c2 varchar(32) DEFAULT NULL,
KEY i1 (c1)
);
CREATE OR REPLACE VIEW v AS
SELECT id, c1, c2 FROM t1
UNION ALL
SELECT id, c1, c2 FROM t2;然后，对这个视图进行一次检查的过滤查询，根据explain显示的执行计划，我们可以看到对这个视图的查询使用到了派生条件下推特性，将查询条件c1=12下推到了基表上，并且使用了基表上的二级索引。
# 视图上的查询，使用到了派生条件下推的特性，将查询条件下推到视图的基表上，以使用基表的索引。
mysql> EXPLAIN FORMAT=TREE SELECT * FROM v WHERE c1 = 12\G
*************************** 1. row ***************************
EXPLAIN: -> Table scan on v (cost=1.26..2.52 rows=2)
-> Union materialize (cost=2.16..3.42 rows=2)
-> Covering index lookup on t1 using i1 (c1=12) (cost=0.35 rows=1)
-> Covering index lookup on t2 using i1 (c1=12) (cost=0.35 rows=1)
1 row in set (0.00 sec)2. 视图查询性能提升一万倍
2.1 MySQL 的视图查询性能
MySQL的视图查询性能一直以来是一个让开发人员很头疼的问题。以往在很多场景下，譬如MySQL视图的定义中包含了group by 或者union等聚合条件，那么视图上的查询就无法使用到基表的索引，而是对所有基表进行全表扫描后，将返回结果保存到临时表，再进行过滤，这也就直接导致了视图的查询性能非常之差。
视图查询性能的鸡肋，加上管理维护成本，导致大多数互联网公司的《MySQL开发规范》都有一条，那就是不允许或者不建议使用视图。
不过随着MySQL 8.0中派生条件下推特性的引入，这一条规范估计要改写；尤其是MySQL 8.0.29 之后即使视图定义中使用了union子句的派生表，也可以应用派生条件下推的特性来提升视图的查询性能。
派生条件下推这个特性的引入彻底解决了MySQL视图的性能瓶颈。
2.2 MySQL 8.0前后版本的视图查询性能对比
如上所述，MySQL 8.0中引入的派生条件下推特性，尤其是MySQL 8.0.29 之后即使视图定义中使用了union子句的派生表也可以应用派生条件下推的特性，使得MySQL 8.0中视图查询性能有了质的飞跃。
对比MySQL 5.7.26 和 MySQL 8.0.29 版本，我们创建一个视图，基于两张sysbench的测试表的union结果；然后在视图上使用where条件过滤查询，对比不同版本的执行计划的区别和查询性能差异。
# 分别在MySQL 5.7.26 和 MySQL 8.0.29 中创建视图v_sbtest，基于两张100w条记录的sysbench测试表
create or replace view v_sbtest as
select k, c from sbtest1
union all
select k, c from sbtest2;首先，在MySQL 5.7.26中查询视图v_sbtest，使用过滤条件where k between 100000 and 200000，可以看到该查询条件无法下推到基表，需要对派生表sbtest1和sbtest2分别进行全表扫描，构建临时表，然后再对返回结果进行过滤。这次查询耗时13.9秒。
[MySQL 5.7.26][test]> explain select * from v_sbtest where k between 100000 and 200000;
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 1972800 | 11.11 | Using where |
| 2 | DERIVED | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL |
| 3 | UNION | sbtest2 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)
[MySQL 5.7.26][test]> select * from v_sbtest where k between 100000 and 200000;
28 rows in set (13.90 sec)然后，在MySQL 8.0.29中查询视图v_sbtest，使用过滤条件 where k between 100000 and 200000，可以看到这次该查询条件被下推到两张基表sbtest1和sbtest2，并且使用到了基表上的索引。查询耗时仅0.221毫秒。
# MySQL 8.0.29 中查询SQL的执行计划，使用到了基表sbtest1和sbtest2上的索引`k_11`和`k_2`
[MySQL 8.0.29][test]> explain select * from v_sbtest where k between 100000 and 200000;
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 32 | 100.00 | NULL |
| 2 | DERIVED | sbtest1 | NULL | range | k_1 | k_1 | 4 | NULL | 18 | 100.00 | Using index condition |
| 3 | UNION | sbtest2 | NULL | range | k_2 | k_2 | 4 | NULL | 14 | 100.00 | Using index condition |
+----+-------------+------------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
3 rows in set, 1 warning (0.00 sec)
[MySQL 8.0.29][test]> explain analyze select * from v_sbtest where k between 100000 and 200000;
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Table scan on v_sbtest (cost=0.09..2.90 rows=32) (actual time=0.001..0.006 rows=32 loops=1)
-> Union materialize (cost=18.21..21.02 rows=32) (actual time=0.203..0.215 rows=32 loops=1)
-> Index range scan on sbtest1 using k_1 over (100000 <= k <= 200000), with index condition: (sbtest1.k between 100000 and 200000) (cost=8.36 rows=18) (actual time=0.026..0.112 rows=18 loops=1)
-> Index range scan on sbtest2 using k_2 over (100000 <= k <= 200000), with index condition: (sbtest2.k between 100000 and 200000) (cost=6.56 rows=14) (actual time=0.009..0.067 rows=14 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+对比同一个视图的查询，在MySQL 5.7.26 和 MySQL 8.0.29不同版本间，前者耗时13.9秒，后者耗时0.221毫秒，查询效率相差6万倍。主要原因就是，MySQL 8.0.29中使用到了派生条件下推特性，利用基表上的索引提前过滤数据，从而大大提升了视图的查询效率。
总结
MySQL 8.0的优化器中，实现了对派生条件的下推优化，可以将外层查询的过滤条件下推到派生表内部，以提前过滤派生表的返回数据，同时可以使用到派生表上的索引以优化查询性能。尤其是MySQL 8.0.29 开始支持对包含union聚合的派生表使用派生条件下推优化。
派生条件下推优化，从根本上解决MySQL 视图的查询无法使用基表的索引导致性能低下这一顽疾。在MySQL 8.0中，《MySQL开发规范》已经过时了，DBA该考虑考虑将禁止使用视图的规定重新修订一下了。