- GreatSQL社区原创内容未经受权不得随便应用，转载请分割小编并注明起源。
- GreatSQL是MySQL的国产分支版本，应用上与MySQL统一。
- 文章起源：GreatSQL社区原创
线上应用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的表
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)手工收集表统计信息
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)创立视图
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;查问视图
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秒
查看执行打算
从执行打算上看，先对视图内的表进行全表扫描，最初在视图上过滤数据。
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语句的执行工夫根本不变
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)创立视图(过滤条件在视图内)
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)查问视图(过滤条件在视图上)
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语句
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版本 已解决掉该问题，视图上的过滤条件能下推到表上。
Server version: 8.0.32 MySQL Community Server - GPLCopyright (c) 2000, 2023, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.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 namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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)Enjoy GreatSQL :)
## 对于 GreatSQL
GreatSQL是由万里数据库保护的MySQL分支，专一于晋升MGR可靠性及性能，反对InnoDB并行查问个性，是实用于金融级利用的MySQL分支版本。
相干链接： GreatSQL社区 Gitee GitHub Bilibili
GreatSQL社区：
社区博客有奖征稿详情：https://greatsql.cn/thread-100-1-1.html
技术交换群：
微信：扫码增加GreatSQL社区助手微信好友，发送验证信息加群。