**Mysql5.7新特性之—– 淺談Sys庫**

紅隱發表於2018-01-07
MySql

**Mysql5.7新特性之—–

    淺談Sys庫**

隨著mysql5.7的逐漸升溫，人們對其也越來越感興趣，我最近又重新學習了一下5.7版本的 SYS庫。

**SYS庫介紹
什麼是sys庫？**
MySQL 5.7開始引入了一個新的sys schema，sys是一個MySQL自帶的系統庫，在安裝MySQL 5.7以後的版本，使用mysqld進行初始化時，會自動建立sys庫，
sys庫裡面的表、檢視、函式、儲存過程可以使我們更方便、快捷的瞭解到MySQL的一些資訊，比如哪些語句使用了臨時表、哪個SQL沒有使用索引、哪個schema中有冗餘索引、查詢使用全表掃描的SQL、查詢使用者佔用的IO等.
Sys庫的資料來源：
sys庫裡這些檢視中的資料，都是從information_schema裡面獲得的，目標是把performance_schema的把複雜度降低，讓DBA能更好的閱讀這個庫裡的內容。讓DBA更快的瞭解DB的執行情況。
Sys庫下有兩種表
字母開頭： 適合人閱讀，顯示是格式化的數
x$開頭 ： 適合工具採集資料，原始類資料
這些資訊都可以通過show tables;檢視或者在information_schema中檢視：
select table_name,table_type,engine from
information_schema.tables where
table_schema=`sys` order by table_name;
13
14

每類表大概介紹
sys_開頭是庫裡的配置表：
sys_config用於sys schema庫的配置

檢視：
host : 以IP分組相關的統計資訊
innodb : innodb buffer 相關資訊
io : 資料內不同維度展的IO相關的資訊
memory : 以IP，連線，使用者，分配的型別分組及總的佔用顯示記憶體的使用
metrics : DB的內部的統計值
processlist : 執行緒相關的資訊(包含內部執行緒及使用者連線）
ps_ : 沒有工具統計的一些變數（沒看出來存在的價值）
schema : 表結構相關的資訊，例如： 自增，索引， 表裡的每個欄位型別，等待的鎖等等
session : 使用者連線相關的資訊
statement : 基於語句的統計資訊（重點）
statements_ : 出錯的語句，進行全表掃描， 執行時間超長，排序相等（重點）
user_ : 和host_開頭的相似，只是以使用者分組統計
wait : 等待事件，比較專業。
waits : 以IP，使用者分組統計出來的一些延遲事件，有一定的參考價值。

**那麼sys庫到底有哪些功能呢？我們可以具體利用它什麼地方呢？
以下是我整理的 部分的功能：**

  1. 誰使用了最多的資源？ 基於IP或是使用者？
    對於該問題可以從host, user, io三個方面去了解，大概誰的請求最多。對於使用資源問題可以直接從下面四個檢視裡有一個大概的瞭解。

Select*from host_summary limit 1G
Select*from io_global_by_file_by_bytes limit 1G
Select*from user_summary limit 1G
Select*from memory_global_total;

注意記憶體部分，不包括innodbbuffer pool。只是server 層申請的記憶體

  1. 大部分連線來自哪裡及傳送的SQL情況
    檢視當前連線情況：

select host, current_connections,statements from host_summary;
檢視當前正在執行的SQL：
select conn_id, user, current_statement, last_statement from session;

  1. 機器執行最多的SQL語句是什麼樣?
    例如查一下系統裡執行最多的TOP 10 SQL。

SQL如下：
select * from statement_analysis order byexec_count desc limit 10G;

  1. 哪張表的IO最多？哪張表訪問次數最多
    • from io_global_by_file_by_byteslimit 10;(參見上面表格說明)

哪張表訪問次數最多，可以參考上面先查詢執行最多的語句，然後查詢對應的表。
SQL如下：
select * from statement_analysis order byexec_count desc limit 10G;

  1. 哪些語句延遲比較嚴重
    statement_analysis中avg_latency的最高的。(參考上面寫法)

SQL語句：
select * from statement_analysis order byavg_latency desc limit 10;

  1. 哪些SQL語句使用了磁碟臨時表
    利用statement_analysis 中tmp_tables ，tmp_disk_tables 進行計算。(參考上面寫法)

參考SQL：
select db, query, tmp_tables,tmp_disk_tables from statement_analysiswhere tmp_tables>0 or tmp_disk_tables >0 order by(tmp_tables+tmp_disk_tables) desc limit 20；

  1. 哪張表佔用了最多的buffer pool
    例如查詢在buffer pool中佔用前10的表。

SQL如下：
select * from innodb_buffer_stats_by_tableorder by pages desc limit 10;

  1. 每個庫佔用多少buffer pool
    SQL如下：

select * frominnodb_buffer_stats_by_schema;

  1. 每個連線分配多少記憶體
    利用session表和memory_by_thread_by_current_bytes分配表進行關聯查詢。

SQL如下：
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;

  1. MySQL內部現在有多個執行緒在執行
    MySQL內部的執行緒型別及數量：

select user, count(*) from processlistgroup by user;

當然要理解上面的問題與方法還需要對一些檢視的欄位理解其中的意思
例如：

host_summary
欄位名 意義
host 從哪個伺服器上連過來。如果是NULL，表示內部的程式
Statements 這臺伺服器共執行了多少語句（從啟動開始統計？）
Statement_latency 這臺伺服器發來等待語句執行的時間
Statement_avg_latency 該伺服器等待語句執行的平均時間
Table_scans 該伺服器掃描表的次數（非全表）
File_io 該伺服器IO事件請求的次數
File_io_latency 該伺服器請求等待IO的時間
Current_connections 該伺服器當前的連線數
Total_connections 該伺服器總連線DB共連線多少次
Unique_user 該伺服器上有幾個不同使用者名稱的賬戶連線過來
Current_memory 該伺服器上當前連線等佔用的記憶體
Total_memory_allocated 該伺服器上的請求總共使用的記憶體

Io_global_by_file_by_bytes
欄位名 意義
File 被操作的檔名
Count_read 總共有多少次讀
Total_read 總共讀了多少位元組
Avg_read 平均每次讀多少位元組
Count_write 總共多少次寫
Total_written 總共寫了多少位元組
Avg_write 平均每次寫的位元組大學
Total 讀和寫總共的IO大學
Write_pct 寫佔total裡的百分比

當然，要全面理解SYS庫的所有檢視的含義 並不是一個簡短的工程，我提供給大家的只是一個理解的思路，與我個人的一些見解，如果有錯的地方，希望大家可以指出，互相學習一下。


相關文章