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

heatdog

发布于 2023-6-9 11:57

浏览

0收藏

线上使用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)1.
2.
3.
4.
5.
6.
7.
8.
9.

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.
13.
14.
15.
16.
17.
18.
19.

手工收集表统计信息

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.
12.

创建视图

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.

查询视图

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

通过主键查询数据，查询返回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)  1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.

添加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)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.

查询视图(过滤条件在视图上)

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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.

直接运行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)1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.

直接运行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 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)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.

文章转载自公众号：GreatSQL社区

分类

数据库

标签

GreatSQL

已于2023-6-9 11:57:30修改

51CTO

51CTO博客

51CTO学堂

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

MySQL视图访问原理

使用sysbench 构造4张1000000的表

手工收集表统计信息

创建视图

查询视图

通过主键查询数据，查询返回4条数据，耗时1分8.96秒

查看执行计划

添加hint后的执行计划

创建视图(过滤条件在视图内)

8.0.32

目录

订阅鸿蒙技术特刊，精选内容抢先看

51CTO

51CTO博客

51CTO学堂

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

MySQL视图访问原理

使用sysbench 构造4张1000000的表

手工收集表统计信息

创建视图

查询视图

通过主键查询数据， 查询返回4条数据，耗时1分8.96秒

查看执行计划

添加hint后的执行计划

创建视图(过滤条件在视图内)

8.0.32

目录

订阅鸿蒙技术特刊，精选内容抢先看

通过主键查询数据，查询返回4条数据，耗时1分8.96秒