MySQL 8.0视图过滤条件下推优化
👉 本文共约807个字，系统预计阅读时间或需4分钟。
现象
线上使用MySQL8.0.25的数据库，通过监控发现数据库在查询一个视图(80张表的union all)时内存和cpu均明显上升。
在8.0.25 MySQL Community Server官方版本测试发现：只能在视图上进行数据过滤，不能将视图上的过滤条件下推到视图内的表上进行数据过滤。8.0.29以后的版本已解决该问题。
MySQL视图访问原理
下面是在8.0.25 MySQL Community Server上做的测试
使用sysbench 构造4张1000000的表
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | mysql> select count(*) from sbtest1; +----------+ | count(*) | +----------+ | 1000000 | +----------+ 1 row in set (1.44 sec) mysql> show create table sbtest1; | Table | Create Table | sbtest1 | CREATE TABLE `sbtest1` ( `id` int NOT NULL AUTO_INCREMENT, `k` int NOT NULL DEFAULT '0', `c` char(120) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '', `pad` char(60) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '', PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=2000000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin | +---------+----------------------------------------------------------------------------------- 1 row in set (0.00 sec) |
手工收集表统计信息
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> analyze table sbtest1,sbtest2 ,sbtest3,sbtest4; +----------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+---------+----------+----------+ | sbtest.sbtest1 | analyze | status | OK | | sbtest.sbtest2 | analyze | status | OK | | sbtest.sbtest3 | analyze | status | OK | | sbtest.sbtest4 | analyze | status | OK | +----------------+---------+----------+----------+ 4 rows in set (0.17 sec) |
创建视图
1 2 3 4 5 6 7 8 9 10 11 | drop view view_sbtest1 ; Create view view_sbtest1 as select * from sbtest1 union all select * from sbtest2 union all select * from sbtest3 union all select * from sbtest4; |
查询视图
1 2 3 4 5 6 7 8 9 10 11 12 | Select * from view_sbtest1 where id=1; mysql> Select id ,k,left(c,20) from view_sbtest1 where id=1; +----+--------+----------------------+ | id | k | left(c,20) | +----+--------+----------------------+ | 1 | 434041 | 61753673565-14739672 | | 1 | 501130 | 64733237507-56788752 | | 1 | 501462 | 68487932199-96439406 | | 1 | 503019 | 18034632456-32298647 | +----+--------+----------------------+ 4 rows in set (1 min 8.96 sec) |
通过主键查询数据， 查询返回4条数据，耗时1分8.96秒
查看执行计划
从执行计划上看，先对视图内的表进行全表扫描，最后在视图上过滤数据。
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信db_bao，谢谢！
1 2 3 4 5 6 7 8 9 10 11 | mysql> explain Select id ,k,left(c,20) from view_sbtest1 where id=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 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 | | 4 | UNION | sbtest3 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL | | 5 | UNION | sbtest4 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 5 rows in set, 1 warning (0.07 sec) |
添加hint后的执行计划
添加官方的 merge hint 进行视图合并(期望视图不作为一个整体，让where上的过滤条件能下推到视图中的表)，不能改变sql执行计划，优化器需要先进行全表扫描在对结果集进行过滤。sql语句的执行时间基本不变
1 2 3 4 5 6 7 8 9 10 11 | mysql> explain Select /*+ merge(t1) */ id ,k,left(c,20) from view_sbtest1 t1 where id=1; +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ | 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 10 | 100.00 | NULL | | 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 | | 4 | UNION | sbtest3 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL | | 5 | UNION | sbtest4 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 100.00 | NULL | +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+ 5 rows in set, 1 warning (0.00 sec) |
创建视图(过滤条件在视图内)
1 2 3 4 5 6 7 8 9 10 11 | mysql> drop view view_sbtest3; ERROR 1051 (42S02): Unknown table 'sbtest.view_sbtest3' mysql> Create view view_sbtest3 as select * from sbtest1 where id=1 union all select * from sbtest2 where id=1 union all select * from sbtest3 where id=1 union all select * from sbtest4 where id=1; Query OK, 0 rows affected (0.02 sec) |
查询视图(过滤条件在视图上)
1 2 3 4 5 6 7 8 9 10 11 12 | Select id ,k,left(c,20) from view_sbtest3 where id=1; mysql> Select id ,k,left(c,20) from view_sbtest3 where id=1; +----+--------+----------------------+ | id | k | left(c,20) | +----+--------+----------------------+ | 1 | 501462 | 68487932199-96439406 | | 1 | 434041 | 61753673565-14739672 | | 1 | 501130 | 64733237507-56788752 | | 1 | 503019 | 18034632456-32298647 | +----+--------+----------------------+ 4 rows in set (0.01 sec) |
直接运行sql语句
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | mysql> select id ,k,left(c,20) from sbtest1 where id=1 -> union all -> select id ,k,left(c,20) from sbtest2 where id=1 -> union all -> select id ,k,left(c,20) from sbtest3 where id=1 -> union all -> select id ,k,left(c,20) from sbtest4 where id=1; +----+--------+----------------------+ | id | k | left(c,20) | +----+--------+----------------------+ | 1 | 501462 | 68487932199-96439406 | | 1 | 434041 | 61753673565-14739672 | | 1 | 501130 | 64733237507-56788752 | | 1 | 503019 | 18034632456-32298647 | +----+--------+----------------------+ 4 rows in set (0.01 sec) |
直接运行sql语句或者把过滤条件放到视图内均能很快得到数据。
8.0.32版本测试
新的MySQL8.0.32版本 已解决掉该问题，视图上的过滤条件能下推到表上。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | Server version: 8.0.32 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> use sbtest; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> Select id ,k,left(c,20) from view_sbtest1 where id=1; +----+--------+----------------------+ | id | k | left(c,20) | +----+--------+----------------------+ | 1 | 501462 | 68487932199-96439406 | | 1 | 434041 | 61753673565-14739672 | | 1 | 501130 | 64733237507-56788752 | | 1 | 503019 | 18034632456-32298647 | +----+--------+----------------------+ 4 rows in set (0.01 sec) mysql> Select id ,k,left(c,20) from view_sbtest3 where id=1; +----+--------+----------------------+ | id | k | left(c,20) | +----+--------+----------------------+ | 1 | 501462 | 68487932199-96439406 | | 1 | 434041 | 61753673565-14739672 | | 1 | 501130 | 64733237507-56788752 | | 1 | 503019 | 18034632456-32298647 | +----+--------+----------------------+ 4 rows in set (0.00 sec) |
