帶你認(rèn)識MySQL sys schema

前言：?

MySQL 5.7中引入了一個新的sys schema，sys是一個MySQL自帶的系統(tǒng)庫，在安裝MySQL 5.7以后的版本，使用mysqld進(jìn)行初始化時，會自動創(chuàng)建sys庫。

sys庫里面的表、視圖、函數(shù)、存儲過程可以使我們更方便、快捷的了解到MySQL的一些信息，比如哪些語句使用了臨時表、哪個SQL沒有使用索引、哪個schema中有冗余索引、查找使用全表掃描的SQL、查找用戶占用的IO等，sys庫里這些視圖中的數(shù)據(jù)，大多是從performance_schema里面獲得的。目標(biāo)是把performance_schema的復(fù)雜度降低，讓我們更快的了解DB的運行情況。

1.sys庫總覽

本篇文章是基于MySQL 5.7.23版本實驗的。打開sys庫(希望你跟著我一起做哦)，我們會發(fā)現(xiàn)sys schema里包含1個表，100個視圖，存儲過程及函數(shù)共48個，如下圖所示：

cdn.nlark.com/yuque/0/2019/png/119537/1569308615078-35b82427-93e5-4abe-b1e9-8c832f7faabc.png">

其實我們經(jīng)常用到的是sys schema下的視圖，下面將主要介紹各個視圖的作用，我們發(fā)現(xiàn)sys schema里的視圖主要分為兩類，一類是正常以字母開頭的，共52個，一類是以 x$ 開頭的，共48個。字母開頭的視圖顯示的是格式化數(shù)據(jù)，更易讀，而 x$ 開頭的視圖適合工具采集數(shù)據(jù)，顯示的是原始未處理過的數(shù)據(jù)。

下面我們將按類別來分析以字母開頭的52個視圖：

• host_summary：這個是服務(wù)器層面的，以IP分組，比如里面的視圖host_summary_by_file_io；
• user_summary：這個是用戶層級的，以用戶分組，比如里面的視圖user_summary_by_file_io；
• innodb：這個是InnoDB層面的，比如視圖innodb_buffer_stats_by_schema；
• io：這個是I/O層的統(tǒng)計，比如視圖io_global_by_file_by_bytes；
• memory：關(guān)于內(nèi)存的使用情況，比如視圖memory_by_host_by_current_bytes；
• schema：關(guān)于schema級別的統(tǒng)計信息，比如schema_table_lock_waits；
• session：關(guān)于會話級別的，這類視圖少一些，只有session和session_ssl_status；
• statement：關(guān)于語句級別的，比如statements_with_errors_or_warnings；
• wait：關(guān)于等待的，比如視圖waits_by_host_by_latency。

2.常用查詢介紹

1,查看每個客戶端IP過來的連接消耗了多少資源。
mysql> select * from host_summary;

2,查看某個數(shù)據(jù)文件上發(fā)生了多少IO請求。
mysql> select * from io_global_by_file_by_bytes;

3,查看每個用戶消耗了多少資源。
mysql> select * from user_summary;

4,查看總共分配了多少內(nèi)存。
mysql> select * from memory_global_total;

5,數(shù)據(jù)庫連接來自哪里，以及這些連接對數(shù)據(jù)庫的請求情況是怎樣的？
查看當(dāng)前連接情況。
mysql> select host, current_connections, statements from host_summary;

6,查看當(dāng)前正在執(zhí)行的SQL和執(zhí)行show full processlist的效果相當(dāng)。
mysql> select conn_id, user, current_statement, last_statement from session;

7,數(shù)據(jù)庫中哪些SQL被頻繁執(zhí)行？
執(zhí)行下面命令查詢TOP 10最熱SQL。
mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10;

8,哪個文件產(chǎn)生了最多的IO，讀多，還是寫的多？
mysql> select * from io_global_by_file_by_bytes limit 10;

9,哪個表上的IO請求最多？
mysql> select * from io_global_by_file_by_bytes where file like ‘%ibd’ order by total desc limit 10;

10,哪個表被訪問的最多？先訪問statement_analysis，根據(jù)熱門SQL排序找到相應(yīng)的數(shù)據(jù)表。
mysql> select * from statement_analysis order by avg_latency desc limit 10;

11,哪些SQL執(zhí)行了全表掃描或執(zhí)行了排序操作？
mysql> select from statements_with_sorting;
mysql> select from statements_with_full_table_scans;

12,哪些SQL語句使用了臨時表，又有哪些用到了磁盤臨時表？
查看statement_analysis中哪個SQL的tmp_tables 、tmp_disk_tables值大于0即可。
mysql> select db, query, tmp_tables, tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 20;

13,哪個表占用了最多的buffer pool？
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10;

14,每個庫（database）占用多少buffer pool？
mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;

15,每個連接分配多少內(nèi)存？
利用session表和memory_by_thread_by_current_bytes分配表進(jìn)行關(guān)聯(lián)查詢。
mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;

16,MySQL自增長字段的最大值和當(dāng)前已經(jīng)使用到的值？
mysql> select * from schema_auto_increment_columns;

17,MySQL有哪些冗余索引和無用索引？
mysql> select from schema_redundant_indexes;
mysql> select from schema_unused_indexes;

18,查看事務(wù)等待情況
mysql> select * from innodb_lock_waits；

總結(jié)：?

本文主要介紹sys庫相關(guān)內(nèi)容，其實sys庫有很多有用的查詢，可以幫助你輕松了解數(shù)據(jù)庫的運行情況，原本需要查找performance_schema中多個表才能獲得的數(shù)據(jù)，現(xiàn)在查詢一個視圖即可滿足。當(dāng)然，sys庫需要你詳細(xì)去了解，總結(jié)出你需要的查詢方法。

參考資料：?

• https://blog.csdn.net/l1028386804/article/details/89521908