发帖
翼度科技»论坛 › 编程开发 › mysql › 查看内容

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

天国

7

主题

7

帖子

21

积分

新手上路

Rank: 1

积分
21
显示全部楼层

  • 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
来源:https://www.cnblogs.com/greatsql/p/17376056.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！

上一篇: 由浅入深学MYSQL之--MySQL分组查询详解

下一篇: 水平分库分表排雷帖

发表于 2023-5-6 12:20:01

举报 回复 使用道具

返回列表 发新帖

Archiver|手机版|小黑屋|翼度科技

Copyright © 2001-2020, Tencent Cloud.　Powered by Discuz! X3.4