MySQL 5.7 SYS SCHEMA

---

---

官方地址：https://dev.mysql.com/doc/refman/5.7/en/sys-schema.html

---

---

1、performance schema：介紹

在MySQL5.7中，performance schema有很大改進，包括引入大量新加入的監控項、降低佔用空間和負載，以及通過新的sys schema機制顯著提升易用性。在監控方面，performance schema有如下功能：

①：後設資料鎖：

對於瞭解會話之間後設資料鎖的依賴關係至關重要。從MySQL5.7.3開始，就可以通過metadata_locks表來了解後設資料鎖的相關資訊;

--哪些會話擁有哪些後設資料鎖

--哪些會話正在等待後設資料鎖

--哪些請求由於死鎖被殺掉，或者鎖等待超時而被放棄

②：進度跟蹤：

跟蹤長時間操作的進度(比如alter table)，從MySQL5.7.7開始，performance schema自動提供了語句進度資訊。我們可以通過events_stages_current表來檢視當前事件的進度資訊;

③：事務：

監控服務層和儲存引擎層事務的全部方面。從MySQL5.7.3開始，新增了 events_transactions_current表，可以通過setup_consumers、setup_instruments表開啟事務監控，通過該表查詢到當前事務的狀態。如果線上資料庫遇到undo log大量增長、資料庫效能急劇下降的情況，可以通過該表查詢當前是否存在處於未提交狀態的事務。如果發現的確有大量事務的state處於active，這時可以確定資料庫有大量的事務未提交;

④：記憶體使用：

提供記憶體使用資訊統計，有利於瞭解和調整伺服器的記憶體消耗。從MySQL5.7.2開始，performance schema新增記憶體有關的統計資訊，分別從賬戶、訪問主機、執行緒、使用者及事件的角度統計了記憶體的使用過程;

⑤：儲存程式：

儲存過程、儲存方法、事件排程器和表觸發器的檢測器。在MySQL5.7中的setup_objects表中，新增了event、function、procedure、trigger的檢測器。performance schema用於檢測該表中匹配object_schema和object_name的物件;

2、sys schema介紹：

在MySQL5.7中新增的sys schema。是由一系列物件(檢視、儲存過程、儲存方法、表和觸發器)組成的schema，它本身不採集和儲存什麼資訊，而是將performance_schema 和 information_schema中的資料以更容易理解的方式總結出來歸納為“檢視”。

---sys schema可用於典型的調優和診斷用例，這些物件包括如下三個：

①：將效能模式資料彙總到更容易理解的檢視;

②：諸如效能模式配置和生成診斷報告等操作的儲存過程

③：用於查詢效能模式配置並提供格式化服務的儲存函式

---sys schema在查詢中的功能，可以檢視資料庫服務資源的使用情況?哪些主機對資料庫伺服器的訪問量最大?例項上的記憶體使用情況?

3、sys schema裡面的表的分類：

①：主機相關資訊：

以host_summary開頭的檢視，主要彙總了IO延遲的資訊，從主機、檔案事件型別、語句型別等角度展示檔案IO的資訊;

②：innodb相關資訊：

以innodb開頭的檢視，彙總了innodb buffer page資訊和事務等待innodb鎖資訊;

③：IO使用情況：

以IO開頭的檢視，總結了IO使用者的資訊，包括等待IO的情況、IO使用量情況，從各個角度分組展示;

④：記憶體使用情況：

以memory開頭的檢視，從主機、執行緒、使用者、事件角度展示記憶體使用情況;

⑤：連線與會話資訊：

其中，processlist 和 session相關的檢視，總結了會話相關的資訊;

⑥：表相關資訊：

以schema_table開頭的檢視，從全表掃描、innodb緩衝池等方面展示了表統計資訊;

⑦：索引資訊：

其中包含index的檢視，統計了索引使用的情況，以及重複索引和未使用的索引情況;

⑧：語句相關資訊：

以statement開頭的檢視，統計的規範化後的語句使用情況，包括錯誤數、警告數、執行全表掃描的、使用臨時表、執行排序等資訊;

⑨：使用者相關資訊：

以user開頭的檢視，統計了使用者使用的檔案IO，執行的語句統計資訊等;

⑨：等待事件相關資訊

：

以wait開頭的檢視，從主機和事件角度展示等待類事件的延遲情況;

4、sys schema使用列子：

在說明系統資料庫之前，先來看下MySQL在資料字典方面的演變歷史：
MySQL4.1 提供了information_schema 資料字典。從此可以很簡單的用SQL語句來檢索需要的系統後設資料了。
MySQL5.5 提供了performance_schema 效能字典。 但是這個字典比較專業，一般人可能也就看看就不了了之了。
MySQL5.7 提供了 sys系統資料庫。 sys資料庫裡面包含了一系列的儲存過程、自定義函式以及檢視來幫助我們快速的瞭解系統的後設資料資訊。


sys系統資料庫結合了information_schema和performance_schema的相關資料，讓我們更加容易的檢索後設資料。 現在呢，我就示範下幾種場景下如何快速的使用。


第一，
比如之前想要知道某個表是否存在與否，可以用以下兩種方法：
A， 悲觀的方法，寫SQL從information_schema中拿資訊：




B，樂觀的方法，假設表存在，寫一個儲存過程：




現在來呼叫：




現在我們直接用sys資料庫裡面現有的儲存過程來進行呼叫，




第二，獲取沒有使用過的索引。






第三， 檢索指定資料庫下面的表掃描資訊，過濾出執行次數大於10的查詢，






第四， 同樣繼續上面的，過濾出有臨時表的查詢，






可以看到上面查詢詳細的詳細，再也不用執行show status 手工去過濾了。




第五， 檢索執行次數排名前五的語句，






示例我就寫這麼多了，詳細的去看使用手冊並且自己摸索去吧。

---

---

CREATE TABLE `sys_config` (   `variable` varchar(128) NOT NULL,   `value` varchar(128) DEFAULT NULL,   `set_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,   `set_by` varchar(128) DEFAULT NULL,   PRIMARY KEY (`variable`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8

        variable 配置選項名稱

---

---

About Me

............................................................................................................................................. ● 本文整理自網路 ● 本文在itpub（http://blog.itpub.net/26736162/abstract/1/）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新 ● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/ ● 本文部落格園地址：http://www.cnblogs.com/lhrbest ● 本文pdf版、個人簡介及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/ ● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/ ● DBA寶典今日頭條號地址：http://www.toutiao.com/c/user/6401772890/#mid=1564638659405826 ............................................................................................................................................. ● QQ群號：230161599（滿）、618766405 ● 微信群：可加我微信，我拉大家進群，非誠勿擾 ● 聯絡我請加QQ好友（646634621），註明新增緣由 ● 於 2017-08-01 09:00 ~ 2017-08-31 22:00 在魔都完成 ● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解 ● 版權所有，歡迎分享本文，轉載請保留出處 ............................................................................................................................................. ● 小麥苗的微店：https://weidian.com/s/793741433?wfr=c&ifr=shopdetail ● 小麥苗出版的資料庫類叢書：http://blog.itpub.net/26736162/viewspace-2142121/ ............................................................................................................................................. 使用微信客戶端掃描下面的二維碼來關注小麥苗的微信公眾號（xiaomaimiaolhr）及QQ群（DBA寶典），學習最實用的資料庫技術。    小麥苗的微信公眾號      小麥苗的DBA寶典QQ群1     小麥苗的DBA寶典QQ群2        小麥苗的微店 .............................................................................................................................................

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2143988/，如需轉載，請註明出處，否則將追究法律責任。