视图在数据库中是非常普及的功能。但是长期以来,大多数互联网公司的《MySQL开发规范》中都有一条规范:在MySQL中禁止(或建议不要)使用视图
。究其原因,主要是由于在MySQL中视图的查询性能不好,同时带来了管理维护上的高成本。
不过随着MySQL 8.0中派生条件下推特性的引入,尤其是最近GA的MySQL 8.0.29版本中对于包含union子句的派生条件下推优化,MySQL中视图查询的性能得到了质的提升。
《MySQL开发规范》已经过时了,DBA该考虑考虑将禁止使用视图的规定重新修订一下了。
在讨论视图之前,我们先了解一下什么是派生条件下推优化。派生条件下推优化,是在MySQL 8.0中引入的一项针对优化器的优化特性,对于存在物化派生表的SQL查询,可以实现派生条件下推优化,即将外层查询子句的过滤条件下推到派生表内部,以减少派生表返回行数,同时可以利用派生表上对应的索引以提高查询效率。
如果派生表上没有使用聚合或者是窗口函数,那么可以直接将外层过滤条件下推到派生表的where条件上过滤;如果派生表上使用了聚合查询(group by),那么一般情况下可以将外层过滤条件下推到派生表聚合之后的having子句;如果派生表上使用了窗口函数,那么可以将外层过滤条件下推到派生表的窗口函数的partition子句(视具体情况而定)。 在MySQL 8.0中派生条件下推是默认开启的,由optimizer_switch系统变量的derived_condition_pushdown标志控制。
派生条件下推的限制:
例如:
# 原始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
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
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
在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)
MySQL的视图查询性能一直以来是一个让开发人员很头疼的问题。以往在很多场景下,譬如MySQL视图的定义中包含了group by 或者union等聚合条件,那么视图上的查询就无法使用到基表的索引,而是对所有基表进行全表扫描后,将返回结果保存到临时表,再进行过滤,这也就直接导致了视图的查询性能非常之差。
视图查询性能的鸡肋,加上管理维护成本,导致大多数互联网公司的《MySQL开发规范》都有一条,那就是不允许或者不建议使用视图。
不过随着MySQL 8.0中派生条件下推特性的引入,这一条规范估计要改写;尤其是MySQL 8.0.29 之后即使视图定义中使用了union子句的派生表,也可以应用派生条件下推的特性来提升视图的查询性能。
派生条件下推这个特性的引入彻底解决了MySQL视图的性能瓶颈。
如上所述,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该考虑考虑将禁止使用视图的规定重新修订一下了。