ToB企服应用市场:ToB评测及商务社交产业平台

标题: MySQL一次大量内存消耗的跟踪 [打印本页]

---

作者: 灌篮少年    时间: 2023-5-6 13:04
标题: 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的表

1. mysql> select count(*) from sbtest1;
3. +----------+
4. | count(*) |
5. +----------+
6. |  1000000 |
7. +----------+
9. 1 row in set (1.44 sec)
10. mysql> show create table sbtest1;
12. | Table   | Create Table  | sbtest1 |
13. CREATE TABLE `sbtest1` (
15.   `id` int NOT NULL AUTO_INCREMENT,
17.   `k` int NOT NULL DEFAULT '0',
19.   `c` char(120) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
21.   `pad` char(60) COLLATE utf8mb4_0900_bin NOT NULL DEFAULT '',
23.   PRIMARY KEY (`id`)
25. ) ENGINE=InnoDB AUTO_INCREMENT=2000000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_bin |
27. +---------+-----------------------------------------------------------------------------------
28. 1 row in set (0.00 sec)

复制代码

手工收集表统计信息

1. mysql> analyze table sbtest1,sbtest2 ,sbtest3,sbtest4;
3. +----------------+---------+----------+----------+
4. | Table          | Op      | Msg_type | Msg_text |
5. +----------------+---------+----------+----------+
6. | sbtest.sbtest1 | analyze | status   | OK       |
7. | sbtest.sbtest2 | analyze | status   | OK       |
8. | sbtest.sbtest3 | analyze | status   | OK       |
9. | sbtest.sbtest4 | analyze | status   | OK       |
10. +----------------+---------+----------+----------+
12. 4 rows in set (0.17 sec)

复制代码

创建视图

1. drop view view_sbtest1 ;
3. Create view view_sbtest1  as
5. select * from sbtest1
6. union all
7. select * from sbtest2
8. union all
9. select * from sbtest3
10. union all
11. select * from sbtest4;

复制代码

查询视图

1. Select * from view_sbtest1 where id=1;
3. mysql> Select id ,k,left(c,20) from view_sbtest1 where id=1;
4. +----+--------+----------------------+
5. | id | k      | left(c,20)           |
6. +----+--------+----------------------+
7. |  1 | 434041 | 61753673565-14739672 |
8. |  1 | 501130 | 64733237507-56788752 |
9. |  1 | 501462 | 68487932199-96439406 |
10. |  1 | 503019 | 18034632456-32298647 |
11. +----+--------+----------------------+
12. 4 rows in set (1 min 8.96 sec)

复制代码

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

查看执行计划

从执行计划上看，先对视图内的表进行全表扫描，最后在视图上过滤数据。

1. mysql> explain Select id ,k,left(c,20) from view_sbtest1 where id=1;
2. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3. | id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
4. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
5. |  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |     10 |   100.00 | NULL  |
6. |  2 | DERIVED     | sbtest1    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
7. |  3 | UNION       | sbtest2    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
8. |  4 | UNION       | sbtest3    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
9. |  5 | UNION       | sbtest4    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
10. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
11. 5 rows in set, 1 warning (0.07 sec)  

复制代码

添加hint后的执行计划

添加官方的 merge hint 进行视图合并(期望视图不作为一个整体，让where上的过滤条件能下推到视图中的表)，不能改变sql执行计划，优化器需要先进行全表扫描在对结果集进行过滤。sql语句的执行时间基本不变

1. mysql> explain Select /*+  merge(t1) */ id ,k,left(c,20) from view_sbtest1 t1 where id=1;
2. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
3. | id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows   | filtered | Extra |
4. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
5. |  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 4       | const |     10 |   100.00 | NULL  |
6. |  2 | DERIVED     | sbtest1    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
7. |  3 | UNION       | sbtest2    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
8. |  4 | UNION       | sbtest3    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
9. |  5 | UNION       | sbtest4    | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  | 986400 |   100.00 | NULL  |
10. +----+-------------+------------+------------+------+---------------+-------------+---------+-------+--------+----------+-------+
11. 5 rows in set, 1 warning (0.00 sec)

复制代码

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

1. mysql> drop view view_sbtest3;
2. ERROR 1051 (42S02): Unknown table 'sbtest.view_sbtest3'
3. mysql> Create view view_sbtest3 as
4.      select * from sbtest1 where id=1
5.       union all
6.       select * from sbtest2 where id=1
7.      union all
8.     select * from sbtest3  where id=1
9.      union all
10.     select * from sbtest4 where id=1;
11. Query OK, 0 rows affected (0.02 sec)

复制代码

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

1. Select id ,k,left(c,20) from view_sbtest3 where id=1;
3. mysql>  Select id ,k,left(c,20) from view_sbtest3 where id=1;
4. +----+--------+----------------------+
5. | id | k      | left(c,20)           |
6. +----+--------+----------------------+
7. |  1 | 501462 | 68487932199-96439406 |
8. |  1 | 434041 | 61753673565-14739672 |
9. |  1 | 501130 | 64733237507-56788752 |
10. |  1 | 503019 | 18034632456-32298647 |
11. +----+--------+----------------------+
12. 4 rows in set (0.01 sec)

复制代码

直接运行sql语句

1. mysql> select id ,k,left(c,20) from sbtest1 where id=1  
2.     ->  union all
3.     ->  select id ,k,left(c,20) from sbtest2 where id=1  
4.     ->  union all
5.     ->  select id ,k,left(c,20) from sbtest3 where id=1
6.     ->  union all
7.     ->  select id ,k,left(c,20) from sbtest4 where id=1;
8. +----+--------+----------------------+
9. | id | k      | left(c,20)           |
10. +----+--------+----------------------+
11. |  1 | 501462 | 68487932199-96439406 |
12. |  1 | 434041 | 61753673565-14739672 |
13. |  1 | 501130 | 64733237507-56788752 |
14. |  1 | 503019 | 18034632456-32298647 |
15. +----+--------+----------------------+
16. 4 rows in set (0.01 sec)

复制代码

直接运行sql语句或者把过滤条件放到视图内均能很快得到数据。
8.0.32

新的MySQL8.0.32版本 已解决掉该问题，视图上的过滤条件能下推到表上。

1. Server version: 8.0.32 MySQL Community Server - GPL
3. Copyright (c) 2000, 2023, Oracle and/or its affiliates.
5. Oracle is a registered trademark of Oracle Corporation and/or its
6. affiliates. Other names may be trademarks of their respective
7. owners.
9. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
11. mysql> use sbtest;
12. Reading table information for completion of table and column names
13. You can turn off this feature to get a quicker startup with -A
15. Database changed
16. mysql> Select id ,k,left(c,20) from view_sbtest1 where id=1;
17. +----+--------+----------------------+
18. | id | k      | left(c,20)           |
19. +----+--------+----------------------+
20. |  1 | 501462 | 68487932199-96439406 |
21. |  1 | 434041 | 61753673565-14739672 |
22. |  1 | 501130 | 64733237507-56788752 |
23. |  1 | 503019 | 18034632456-32298647 |
24. +----+--------+----------------------+
25. 4 rows in set (0.01 sec)
27. mysql> Select id ,k,left(c,20) from view_sbtest3 where id=1;
28. +----+--------+----------------------+
29. | id | k      | left(c,20)           |
30. +----+--------+----------------------+
31. |  1 | 501462 | 68487932199-96439406 |
32. |  1 | 434041 | 61753673565-14739672 |
33. |  1 | 501130 | 64733237507-56788752 |
34. |  1 | 503019 | 18034632456-32298647 |
35. +----+--------+----------------------+
36. 4 rows in set (0.00 sec)

复制代码

Enjoy GreatSQL
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！

---

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)

Powered by Discuz! X3.4