前言：

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

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

1.sys庫(kù)總覽

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

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

下面我們將按類(lèi)別來(lái)分析以字母開(kāi)頭的52個(gè)視圖：

• host_summary：這個(gè)是服務(wù)器層面的，以IP分組，比如里面的視圖hostsummarybyfileio；
• user_summary：這個(gè)是用戶層級(jí)的，以用戶分組，比如里面的視圖usersummarybyfileio；
• innodb：這個(gè)是InnoDB層面的，比如視圖innodbbufferstatsbyschema；
• io：這個(gè)是I/O層的統(tǒng)計(jì)，比如視圖ioglobalbyfileby_bytes；
• memory：關(guān)于內(nèi)存的使用情況，比如視圖memorybyhostbycurrent_bytes；
• schema：關(guān)于schema級(jí)別的統(tǒng)計(jì)信息，比如schematablelock_waits；
• session：關(guān)于會(huì)話級(jí)別的，這類(lèi)視圖少一些，只有session和sessionsslstatus；
• statement：關(guān)于語(yǔ)句級(jí)別的，比如statementswitherrorsorwarnings；
• wait：關(guān)于等待的，比如視圖waitsbyhostbylatency。

2.常用查詢介紹

1,查看每個(gè)客戶端IP過(guò)來(lái)的連接消耗了多少資源。

mysql> select * from host_summary;

2,查看某個(gè)數(shù)據(jù)文件上發(fā)生了多少I(mǎi)O請(qǐng)求。

mysql> select * from io_global_by_file_by_bytes;

3,查看每個(gè)用戶消耗了多少資源。

mysql> select * from user_summary;

4,查看總共分配了多少內(nèi)存。

mysql> select * from memory_global_total;

5,數(shù)據(jù)庫(kù)連接來(lái)自哪里，以及這些連接對(duì)數(shù)據(jù)庫(kù)的請(qǐng)求情況是怎樣的？查看當(dāng)前連接情況。mysql> select host, currentconnections, statements from hostsummary;

6,查看當(dāng)前正在執(zhí)行的SQL和執(zhí)行show full processlist的效果相當(dāng)。

mysql> select connid, user, currentstatement, last_statement from session;

7,數(shù)據(jù)庫(kù)中哪些SQL被頻繁執(zhí)行？執(zhí)行下面命令查詢TOP 10最熱SQL。

mysql> select db,execcount,query from statementanalysis order by exec_count desc limit 10;

8,哪個(gè)文件產(chǎn)生了最多的IO，讀多，還是寫(xiě)的多？

mysql> select * from io_global_by_file_by_bytes limit 10;

9,哪個(gè)表上的IO請(qǐng)求最多？

mysql> select * from io_global_by_file_by_bytes where file like ‘%ibd’ order by total desc limit 10;

10,哪個(gè)表被訪問(wèn)的最多？先訪問(wèn)statement_analysis，根據(jù)熱門(mén)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語(yǔ)句使用了臨時(shí)表，又有哪些用到了磁盤(pán)臨時(shí)表？查看statementanalysis中哪個(gè)SQL的tmptables 、tmpdisktables值大于0即可。

mysql> select db, query, tmptables, tmpdisktables from statementanalysis where tmptables>0 or tmpdisktables >0 order by (tmptables+tmpdisktables) desc limit 20;

13,哪個(gè)表占用了最多的buffer pool？

mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10;

14,每個(gè)庫(kù)（database）占用多少buffer pool？

mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;

15,每個(gè)連接分配多少內(nèi)存？利用session表和memorybythreadbycurrent_bytes分配表進(jìn)行關(guān)聯(lián)查詢。

mysql> select b.user, currentcountused, currentallocated, currentavgalloc, currentmaxalloc, totalallocated,currentstatement from memorybythreadbycurrentbytes a, session b where a.threadid = b.thdid;

16,MySQL自增長(zhǎng)字段的最大值和當(dāng)前已經(jīng)使用到的值？

mysql> select * from schema_auto_increment_columns;

17,MySQL有哪些冗余索引和無(wú)用索引？

mysql> select * from schema_redundant_indexes;

mysql> select * from schema_unused_indexes;

18,查看事務(wù)等待情況

mysql> select * from innodb_lock_waits；

總結(jié)：

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

參考資料：

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

歡迎關(guān)注個(gè)人公眾號(hào)『MySQL技術(shù)』