MySQL一次大量内存消耗的跟踪

• 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;

+---------+-----------------------------------------------------------------------------------
1 row in set (0.00 sec)

手工收集表统计信息

mysql> analyze table sbtest1,sbtest2 ,sbtest3,sbtest4;

4 rows in set (0.17 sec)

创建视图

drop view view_sbtest1 ;

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 | | NULL | ref | | | 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 | | NULL | ref | | | 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 - GPL

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)