概述

performance_schema提供監(jiān)控策略及大量監(jiān)控項(xiàng)，包括：元數(shù)據(jù)鎖、進(jìn)度跟蹤、事務(wù)、內(nèi)存使用及存儲(chǔ)程序等。但是，performance_schema又過于復(fù)雜，操作不便，所以5.7新增了 sys schema，基礎(chǔ)數(shù)據(jù)來自于 performance 跟 information_shcema兩個(gè)庫，本身數(shù)據(jù)庫不存儲(chǔ)及集采數(shù)據(jù)。

一、sys schema里面視圖的分類

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

1、主機(jī)相關(guān)信息

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

2、innodb相關(guān)信息

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

3、IO使用情況

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

4、內(nèi)存相關(guān)信息

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

5、連接與會(huì)話相關(guān)信息

其中，processlist 和 session相關(guān)的視圖，總結(jié)了會(huì)話相關(guān)的信息；

6、表相關(guān)信息

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

7、索引相關(guān)信息

其中包含index的視圖，統(tǒng)計(jì)了索引使用的情況，以及重復(fù)索引和未使用的索引情況；

8、語句相關(guān)信息

以statement開頭的視圖，統(tǒng)計(jì)的規(guī)范化后的語句使用情況，包括錯(cuò)誤數(shù)、警告數(shù)、執(zhí)行全表掃描的、使用臨時(shí)表、執(zhí)行排序等信息；

9、用戶相關(guān)信息

以user開頭的視圖，統(tǒng)計(jì)了用戶使用的文件IO，執(zhí)行的語句統(tǒng)計(jì)信息等；

10、等待事件相關(guān)信息

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

二、sys schema日常應(yīng)用

1、查看process

1.1、常用的3個(gè)查詢

有以下3個(gè)查詢：

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

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

MySQL- 5.7數(shù)據(jù)庫sys schema總結(jié)--性能優(yōu)化必備

1.2、sys的四個(gè)視圖

除此之外，sys提供以下四個(gè)視圖查看連接情況，這四個(gè)則更為詳細(xì)的提供了行數(shù)情況、臨時(shí)表情況、當(dāng)前SQL以及最后提交SQL（即使是sleep狀態(tài)，這里也有最后提交的SQL可以查看）等信息。

若想詳細(xì)查看，可以通過 `performance_schema`.`events_statements_current` 表格查看，通過sys.processlist 的thd_id關(guān)聯(lián)查看。

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監(jiān)控

select * from sys.schema_auto_increment_columns;

5、監(jiān)控全表掃描的sql語句

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

6、查看實(shí)際消耗磁盤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，一般關(guān)注statement_truncate_len列就行（影響函數(shù)format_statement()截?cái)郤QL后的長度，即最后SQL語句顯示的總長度，默認(rèn)為64），最重要的就是里面的視圖了。

sys庫的視圖分為帶x$跟不帶x$前綴的視圖，這兩種沒啥實(shí)質(zhì)性區(qū)別，不帶 x$ 的視圖是人性化的結(jié)果展示，會(huì)有一些單位換算，而帶x$前綴的則是原始數(shù)據(jù)單位，未經(jīng)換算。

1、主機(jī)相關(guān)

以host_summary開頭的視圖，提供IO延遲等相關(guān)信息

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、應(yīng)用場景

1.2.1、host_summary(常用)

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

 select * from sys.host_summary;

1.2.2、host_summary_by_file_io_type

詳細(xì)查看每個(gè)host的主要是在什么文件類型上耗費(fèi)IO資源，可以查看 host_summary_by_file_io_type視圖

1.2.3、host_summary_by_file_io

僅查看每臺(tái)host總的IO情況，則可以查看視圖host_summary_by_file_io

 select * from sys.host_summary_by_file_io;

2、innodb相關(guān)

以innodb開頭的視圖，匯總了innodb buffer page信息和事務(wù)等待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、應(yīng)用場景

2.2.1、 innodb_buffer_stats_by_schema

當(dāng)一個(gè)實(shí)例中有多個(gè)業(yè)務(wù)庫，由于性能問題，可能想查看下各個(gè)數(shù)據(jù)庫的內(nèi)存占用情況，可以使用視圖 innodb_buffer_stats_by_schema，但是少用慎用，因?yàn)闀?huì)掃描整個(gè)buffer pool來統(tǒng)計(jì)，如果所在實(shí)例buffer pool非常大，那么這是一個(gè)極為耗費(fèi)資源的查詢，建議慎用。這個(gè)視圖實(shí)際上是通過視圖 innodb_buffer_stats_by_table的數(shù)據(jù)做了group by object_schema得到的。

 select * from sys.innodb_buffer_stats_by_schema;

2.2.2、 innodb_buffer_stats_by_table（常用，慎用）

在某種情況下，需要查詢表格在內(nèi)存中的占用情況，可以通過視圖 innodb_buffer_stats_by_table來查詢，也是掃描整個(gè)buffer pool統(tǒng)計(jì)，少用慎用。

 select * from sys.innodb_buffer_stats_by_table;

3、IO相關(guān)

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

3.1、視圖一覽

The io_by_thread_by_latency and x$io_by_thread_by_latency Views
- 各個(gè)IO線程的使用情況
The io_global_by_file_by_bytes and x$io_global_by_file_by_bytes Views
- 各個(gè)數(shù)據(jù)庫文件的IO情況
The io_global_by_file_by_latency and x$io_global_by_file_by_latency Views
- 各個(gè)數(shù)據(jù)庫文件的IO耗時(shí)情況
The io_global_by_wait_by_bytes and x$io_global_by_wait_by_bytes Views
- 數(shù)據(jù)庫事件IO等待情況
The io_global_by_wait_by_latency and x$io_global_by_wait_by_latency Views
- 數(shù)據(jù)庫事件IO等待耗時(shí)情況
The latest_file_io and x$latest_file_io Views
- 當(dāng)前正在讀寫文件的情況

3.2、應(yīng)用場景

3.2.1、io_global_by_file_by_bytes（常用）

查看數(shù)據(jù)庫實(shí)例的IO分布情況，及著重優(yōu)化對象，可以使用 io_global_by_file_by_bytes

 select * from sys.io_global_by_file_by_bytes order by count_read  desc;

4、內(nèi)存相關(guān)

以memory開頭的視圖，從主機(jī)/線程/用戶等角度展示內(nèi)存的使用情況

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、應(yīng)用場景

4.2.1、memory_by_host_by_current_bytes、memory_by_thread_by_current_bytes 、memory_by_user_by_current_bytes

當(dāng)前內(nèi)存使用情況，從 host、thread、user等角度來分別查看，對應(yīng)各自的視圖即可。

 select * from sys.memory_by_host_by_current_bytes;

5、連接與會(huì)話相關(guān)

含有processlist和session的視圖，顯示會(huì)話相關(guān)的信息

5.1、視圖一覽

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

5.2、應(yīng)用場景

5.2.1、processlist(常用)

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

 select * from sys.processlist;

6、表相關(guān)

以schema_table開頭的視圖，從全表掃描/innodb緩沖池表現(xiàn)表統(tǒng)計(jì)信息

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、應(yīng)用場景

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（常用）

查看表格的全表掃描情況，抓取需要重點(diǎn)優(yōu)化的對象，可以使用視圖schema_tables_with_full_table_scans

 select * from sys.schema_tables_with_full_table_scans;

6.2.3、schema_auto_increment_columns（常用）

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

 select * from sys.schema_auto_increment_columns order by auto_increment_ratio desc;

7、索引相關(guān)

含有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、應(yīng)用場景

7.2.1、schema_object_overview（常用）

查看當(dāng)前實(shí)例內(nèi)各個(gè)數(shù)據(jù)的對象及索引分布情況，可以使用 schema_object_overview

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

7.2.2、schema_redundant_indexes（常用）

查看數(shù)據(jù)庫的冗余索引情況，可以通過視圖 schema_redundant_indexes，但不是所有冗余索引都要?jiǎng)h除，需要衡量實(shí)際的使用情況、索引大小、索引掃描情況后再?zèng)Q定。

 select * from sys.schema_redundant_indexes;

7.2.3、schema_unused_indexes（常用）

查看數(shù)據(jù)庫沒有使用的索引，可以使用 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、語句相關(guān)

以statement開頭的視圖，顯示錯(cuò)誤數(shù)、警告數(shù)、執(zhí)行全表掃描、使用臨時(shí)表、執(zhí)行排序等信息

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、應(yīng)用場景

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

9、用戶相關(guān)

以user開頭的視圖，顯示用戶使用的文件IO/執(zhí)行語句的統(tǒng)計(jì)信息

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、應(yīng)用場景

從用戶的角度，分別統(tǒng)計(jì)文件的IO情況、sql執(zhí)行情況，如果數(shù)據(jù)庫的用戶是按照業(yè)務(wù)模塊來劃分的，那么則可以清晰的看到哪些業(yè)務(wù)耗費(fèi)資源較多

10、等待信息

以wait開頭的視圖

10.1、視圖一覽

The wait_classes_global_by_avg_latency and x$wait_classes_global_by_avg_latency Views
- 按事件event分組，統(tǒng)計(jì)各個(gè)event的平均延遲時(shí)長
The wait_classes_global_by_latency and x$wait_classes_global_by_latency Views
- 按事件event分組，統(tǒng)計(jì)各個(gè)event的總延遲時(shí)長
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、應(yīng)用場景

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

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

后面會(huì)分享更多devops和DBA方面內(nèi)容，感興趣的朋友可以關(guān)注下！