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

Enjoy GreatSQL ??

關于 GreatSQL

GreatSQL是由萬里資料庫維護的MySQL分支，專注于提升MGR可靠性及性能，支持InnoDB并行查詢特性，是適用于金融級應用的MySQL分支版本，

相關鏈接： GreatSQL社區 Gitee GitHub Bilibili

GreatSQL社區：

社區博客有獎征稿詳情：https://greatsql.cn/thread-100-1-1.html

技術交流群：

微信：掃碼添加GreatSQL社區助手微信好友，發送驗證資訊加群，

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/551845.html

標籤：MySQL

上一篇：由淺入深學MYSQL之--MySQL分組查詢詳解

下一篇：返回列表

MySQL一次大量記憶體消耗的跟蹤