写点什么

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

作者:GreatSQL
  • 2023-05-06
    福建
  • 本文字数:4074 字

    阅读完需:约 13 分钟

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;
+----------+| 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 - GPL
Copyright (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 -A
Database 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)
复制代码


发布于: 刚刚阅读数: 2
用户头像

GreatSQL

关注

GreatSQL社区 2023-01-31 加入

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。 社区:https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL

评论

发布
暂无评论
MySQL一次大量内存消耗的跟踪_GreatSQL_InfoQ写作社区