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

吹水老王

已于 2022-05-04 18:11:15 修改

阅读量1.2w

点赞数 3

CC 4.0 BY-SA版权

分类专栏： MySQL 8.0 文章标签： mysql 数据库开发数据库团队开发 dba

于 2022-05-04 18:09:53 首次发布

本文链接：https://blog.youkuaiyun.com/wangzihuacool/article/details/124573551

MySQL 8.0 专栏收录该内容

9 篇文章

订阅专栏

本文探讨MySQL 8.0中的新特性，特别是派生条件下推优化和视图性能提升。派生条件下推优化能减少派生表返回行数，提高查询效率。在MySQL 8.0.29中，即使视图包含union，也能应用此优化。视图查询性能以前因无法使用基表索引而低下，但在新版本中得到了显著改善，查询速度提升可达6万倍。这使得过去禁止使用视图的《MySQL开发规范》需要重新审视。

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

MySQL 8.0新特性专栏目录

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

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

MySQL 8.0新特性专栏目录
前言
1. 派生条件下推优化特性
2. 视图查询性能提升一万倍
- 2.1 MySQL 的视图查询性能
- 2.2 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 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)
 |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+