MySQL一次大量内存消耗跟踪

bisal的个人杂货铺 2023-05-17

502

社群推送的这篇文章《MySQL一次大量内存消耗的跟踪》介绍一个和视图检索相关的SQL调优的案例，学习借鉴一下。

线上使用MySQL8.0.25的数据库，通过监控发现数据库在查询一个视图（80张表的union all）时内存和cpu均明显上升。

在8.0.25 MySQL Community Server官方版本测试发现：只能在视图上进行数据过滤，不能将视图上的过滤条件下推到视图内的表上进行数据过滤。8.0.29以后的版本已解决该问题。

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语句或者将过滤条件放到视图内均能很快得到数据。

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 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)
复制