概述

performance_schema提供監控策略及大量監控項，包括：元數據鎖、進度跟蹤、事務、內存使用及存儲程序等。但是，performance_schema又過于復雜，操作不便，所以5.7新增了 sys schema，基礎數據來自于 performance 跟 information_shcema兩個庫，本身數據庫不存儲及集采數據。

---

一、sys schema里面視圖的分類

那么sys schema在查詢中的功能，哪些可以查看數據庫服務資源的使用情況？哪些主機對數據庫服務器的訪問量最大？實際上的內存使用情況？這就要了解里面的視圖了。

1、主機相關信息

以host_summary開頭的視圖，主要匯總了IO延遲的信息，從主機、文件事件類型、語句類型等角度展示文件IO的信息；

2、innodb相關信息

以innodb開頭的視圖，匯總了innodb buffer page信息和事務等待innodb鎖信息；

3、IO使用情況

以IO開頭的視圖，總結了IO使用者的信息，包括等待IO的情況、IO使用量情況，從各個角度分組展示；

4、內存相關信息

以memory開頭的視圖，從主機、線程、用戶、事件角度展示內存使用情況；

5、連接與會話相關信息

其中，processlist 和 session相關的視圖，總結了會話相關的信息；

6、表相關信息

以schema_table開頭的視圖，從全表掃描、innodb緩沖池等方面展示了表統計信息；

7、索引相關信息

其中包含index的視圖，統計了索引使用的情況，以及重復索引和未使用的索引情況；

8、語句相關信息

以statement開頭的視圖，統計的規范化后的語句使用情況，包括錯誤數、警告數、執行全表掃描的、使用臨時表、執行排序等信息；

9、用戶相關信息

以user開頭的視圖，統計了用戶使用的文件IO，執行的語句統計信息等；

10、等待事件相關信息

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

---

二、sys schema日常應用

1、查看process

1.1、常用的3個查詢

有以下3個查詢：

其中，show processlist為簡要查看當前連接數據庫情況，包含SQL語句的statement列僅提供部分SQL，而show full processlist則提供完整的SQL 語句，information_schema.processlist的內容與show full processlist 內容一致，但是可以以表格查詢的形式添加where條件，達到自己的使用需求。

show processlist; 
show full processlist; 
select * from information_schema.processlist where state!='';

 

1.2、sys的四個視圖

除此之外，sys提供以下四個視圖查看 連接情況，這四個則更為詳細的提供了 行數情況、臨時表情況、當前SQL以及最后提交SQL（即使是sleep狀態，這里也有最后提交的SQL可以查看）等信息。

若想詳細查看，可以通過 `performance_schema`.`events_statements_current` 表格查看，通過sys.processlist 的thd_id關聯查看。

select * from sys.processlist;
select * from sys.session;
select * from sys.x$processlist;
select * from sys.x$session;

2、查看表訪問量

SELECT
	table_schema,
	table_name,
	sum( io_read_requests + io_write_requests ) io 
FROM
	schema_table_statistics 
GROUP BY
	table_schema,
	table_name 
ORDER BY
	io DESC 
	LIMIT 10;

 

3、冗余索引與未使用索引

--冗余索引查看 
select table_schema,table_name,redundant_index_name,redundant_index_columns,dominant_index_name,dominant_index_columns from sys.schema_redundant_indexes; 
--未使用索引查看 
select * from sys.schema_unused_indexes;

 

4、表自增ID監控

select * from sys.schema_auto_increment_columns;

 

5、監控全表掃描的sql語句

 select * from sys.statements_with_full_table_scans where db = 'pas_prod' order by exec_count desc;

 

6、查看實際消耗磁盤IO的文件

 select file,avg_read+avg_write as avg_io from io_global_by_file_by_bytes order by avg_io desc limit 10;

 

---

三、sys schema視圖一覽表

sys schema只有一張表sys_config，一般關注statement_truncate_len列就行（影響函數format_statement()截斷SQL后的長度，即最后SQL語句顯示的總長度，默認為64），最重要的就是里面的視圖了。

sys庫的視圖分為 帶x$跟不帶x$前綴的視圖，這兩種沒啥實質性區別，不帶 x$ 的視圖是人性化的結果展示，會有一些單位換算，而帶x$前綴的則是原始數據單位，未經換算。

1、主機相關

以host_summary開頭的視圖，提供IO延遲等相關信息

1.1、視圖一覽

• The host_summary and x$host_summary Views
• The host_summary_by_file_io and x$host_summary_by_file_io Views
• The host_summary_by_file_io_type and x$host_summary_by_file_io_type Views
• The host_summary_by_stages and x$host_summary_by_stages Views
• The host_summary_by_statement_latency and x$host_summary_by_statement_latency Views
• The host_summary_by_statement_type and x$host_summary_by_statement_type Views

1.2、應用場景

1.2.1、host_summary(常用)

日常中主要使用的是host_summary視圖，可以根據連接數據庫的host總的執行sql數目、執行時長、表掃描、文件IO、連接情況、用戶情況及內存分布情況，可以讓DBA快速定位到是哪臺host最耗費數據庫資源，對連接數據庫的所有host有一個大致的資源使用情況的了解。

 select * from sys.host_summary;

 

1.2.2、host_summary_by_file_io_type

詳細查看每個host的主要是在什么文件類型上耗費IO資源，可以查看 host_summary_by_file_io_type視圖

 

1.2.3、host_summary_by_file_io

僅查看每臺host總的IO情況，則可以查看視圖host_summary_by_file_io

 select * from sys.host_summary_by_file_io;

 

2、innodb相關

以innodb開頭的視圖，匯總了innodb buffer page信息和事務等待innodb鎖信息

2.1、視圖一覽

• The innodb_buffer_stats_by_schema and x$innodb_buffer_stats_by_schema Views
• The innodb_buffer_stats_by_table and x$innodb_buffer_stats_by_table Views
• The innodb_lock_waits and x$innodb_lock_waits Views

2.2、應用場景

2.2.1、 innodb_buffer_stats_by_schema

當一個實例中有多個業務庫，由于性能問題，可能想查看下各個數據庫的內存占用情況，可以使用視圖 innodb_buffer_stats_by_schema，但是少用慎用，因為會掃描整個buffer pool來統計，如果所在實例buffer pool非常大，那么這是一個極為耗費資源的查詢，建議慎用。這個視圖實際上是通過 視圖 innodb_buffer_stats_by_table的數據做了group by object_schema得到的。

 select * from sys.innodb_buffer_stats_by_schema;

 

2.2.2、 innodb_buffer_stats_by_table（常用，慎用）

在某種情況下，需要查詢表格在內存中的占用情況，可以通過視圖 innodb_buffer_stats_by_table來查詢，也是掃描整個buffer pool統計，少用慎用。

 select * from sys.innodb_buffer_stats_by_table;

 

3、IO相關

以IO開頭的視圖，等待IO情況/IO使用情況

3.1、視圖一覽

• The io_by_thread_by_latency and x$io_by_thread_by_latency Views
  • 各個IO線程的使用情況
• The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Views
  • 各個數據庫文件的IO情況
• The io_global_by_file_by_latency and x$io_global_by_file_by_latency Views
  • 各個數據庫文件的IO耗時情況
• The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Views
  • 數據庫事件IO等待情況
• The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Views
  • 數據庫事件IO等待耗時情況
• The latest_file_io and x$latest_file_io Views
  • 當前正在讀寫文件的情況

3.2、應用場景

3.2.1、io_global_by_file_by_bytes（常用）

查看數據庫實例的IO分布情況，及著重優化對象，可以使用 io_global_by_file_by_bytes

 select * from sys.io_global_by_file_by_bytes order by count_read  desc;

 

4、內存相關

以memory開頭的視圖，從主機/線程/用戶等角度展示內存的使用情況

4.1、視圖一覽

• The memory_by_host_by_current_bytes and x$memory_by_host_by_current_bytes Views
• The memory_by_thread_by_current_bytes and x$memory_by_thread_by_current_bytes Views
• The memory_by_user_by_current_bytes and x$memory_by_user_by_current_bytes Views
• The memory_global_by_current_bytes and x$memory_global_by_current_bytes Views
• The memory_global_total and x$memory_global_total Views

4.2、應用場景

4.2.1、memory_by_host_by_current_bytes、memory_by_thread_by_current_bytes 、memory_by_user_by_current_bytes

當前內存使用情況，從 host、thread、user等角度來分別查看，對應各自的視圖即可。

 select * from sys.memory_by_host_by_current_bytes;

 

5、連接與會話相關

含有processlist和session的視圖，顯示會話相關的信息

5.1、視圖一覽

• The processlist and x$processlist Views
• The session and x$session Views
• The session_ssl_status View

5.2、應用場景

5.2.1、processlist(常用)

查看連接使用情況，session的結果跟processlist類似。查看連接情況，有非常多種方式，每種方式都有各自的使用情況。

 select * from sys.processlist;

 

6、表相關

以schema_table開頭的視圖，從全表掃描/innodb緩沖池表現表統計信息

6.1、視圖一覽

• The schema_table_lock_waits and x$schema_table_lock_waits Views
• The schema_table_statistics and x$schema_table_statistics Views
• The schema_table_statistics_with_buffer and x$schema_table_statistics_with_buffer Views
• The schema_tables_with_full_table_scans and x$schema_tables_with_full_table_scans Views
• The schema_auto_increment_columns View

6.2、應用場景

6.2.1、schema_table_statistics（常用）

查看表格的update、delete、insert、select的IO情況，可以使用schema_table_statistics視圖

 select * from sys.schema_table_statistics;

 

6.2.2、schema_tables_with_full_table_scans（常用）

查看表格的全表掃描情況，抓取需要重點優化的對象，可以使用視圖schema_tables_with_full_table_scans

 select * from sys.schema_tables_with_full_table_scans;

 

6.2.3、schema_auto_increment_columns（常用）

查看表格的自增長是否快達到瓶頸了，有些表格存在頻繁的刪除操作，可能導致自增ID的最大值跟表格數量極不相符合，為了避免問題，可以通過視圖 schema_auto_increment_columns，查看有哪些表格快要達到自增的瓶頸值

 select * from sys.schema_auto_increment_columns order by auto_increment_ratio desc;

 

7、索引相關

含有index的視圖

7.1、視圖一覽

• The schema_object_overview View
• The schema_redundant_indexes and x$schema_flattened_keys Views
• The schema_unused_indexes View
• The schema_index_statistics and x$schema_index_statistics Views

7.2、應用場景

7.2.1、schema_object_overview（常用）

查看當前實例內各個數據的對象及索引分布情況，可以使用 schema_object_overview

 select * from sys.schema_object_overview where db='pas_prod';

 

7.2.2、schema_redundant_indexes（常用）

查看數據庫的冗余索引情況，可以通過視圖 schema_redundant_indexes，但不是所有冗余索引都要刪除，需要衡量實際的使用情況、索引大小、索引掃描情況后再決定。

 select * from sys.schema_redundant_indexes;

 

7.2.3、schema_unused_indexes（常用）

查看數據庫沒有使用的索引，可以使用 schema_unused_indexes

 select * from sys.schema_unused_indexes;

 

7.2.4、schema_index_statistics（常用）

查看索引的select \updatedeleteinsert情況，可以使用schema_index_statistics

 select * from sys.schema_index_statistics;

 

8、語句相關

以statement開頭的視圖，顯示錯誤數、警告數、執行全表掃描、使用臨時表、執行排序等信息

8.1、視圖一覽

• The statement_analysis and x$statement_analysis Views（常用）
• The statements_with_errors_or_warnings and x$statements_with_errors_or_warnings Views（常用）
• The statements_with_full_table_scans and x$statements_with_full_table_scans Views（常用）
• The statements_with_runtimes_in_95th_percentile and x$statements_with_runtimes_in_95th_percentile Views
• The statements_with_sorting and x$statements_with_sorting Views（常用）
• The statements_with_temp_tables and x$statements_with_temp_tables Views（常用）

8.2、應用場景

匯總SQL中錯誤數、警告數、執行全表掃描、使用臨時表、執行排序等信息，sql語句也是使用 format_statement() 函數做了長度限制，如果想查看完整的SQL，可以通過 這個表格的這一列查看performance_schema`.`events_statements_summary_by_digest`.`DIGEST_TEXT`，關聯的添加列是 DIGEST

9、用戶相關

以user開頭的視圖，顯示用戶使用的文件IO/執行語句的統計信息

9.1、視圖一覽

• The user_summary and x$user_summary Views （常用）
• The user_summary_by_file_io and x$user_summary_by_file_io Views
• The user_summary_by_file_io_type and x$user_summary_by_file_io_type Views
• The user_summary_by_stages and x$user_summary_by_stages Views
• The user_summary_by_statement_latency and x$user_summary_by_statement_latency Views
• The user_summary_by_statement_type and x$user_summary_by_statement_type Views

9.2、應用場景

從用戶的角度，分別統計文件的IO情況、sql執行情況，如果數據庫的用戶是按照業務模塊來劃分的，那么則可以清晰的看到哪些業務耗費資源較多

10、等待信息

以wait開頭的視圖

10.1、視圖一覽

• The wait_classes_global_by_avg_latency and x$wait_classes_global_by_avg_latency Views
  • 按事件event分組，統計各個event的平均延遲時長
• The wait_classes_global_by_latency and x$wait_classes_global_by_latency Views
  • 按事件event分組，統計各個event的總延遲時長
• The waits_by_host_by_latency and x$waits_by_host_by_latency Views
• The waits_by_user_by_latency and x$waits_by_user_by_latency Views
• The waits_global_by_latency and x$waits_global_by_latency Views
  • 所有event的延遲情況

10.2、應用場景

等待類視圖，分別從事件、主機、用戶等角度，進行查詢分析。

---

sys schema上的視圖對于做性能分析是很重要的，建議大家重點掌握~

后面會分享更多devops和DBA方面內容，感興趣的朋友可以關注下！