之前在《MySQL sys Schema 简单介绍-1》中简单的介绍了，sys Schema库中的表。那么这些表都可以查询些什么信息呢？接下来本文将做下介绍。
1. 表的情况
1.1 统计下哪些表访问量占前十位
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;
+--------------+----------------------+----------+
| table_schema | table_name | io |
+--------------+----------------------+----------+
| tb0 | tb0 | 16148445 |
| tb1 | tb1 | 394803 |
| tb2 | tb2 | 30842 |
| tb3 | tb3 | 30544 |
| tb4 | tb4 | 30505 |
| tb5 | tb5 | 30041 |
| tb6 | tb6 | 30011 |
| tb7 | tb7 | 29836 |
| tb8 | tb8 | 29730 |
| tb9 | tb9 | 29603 |
+--------------+----------------------+----------+
1.2 哪个表占用了最多的buffer pool
mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 1\G
*************************** 1. row ***************************
object_schema: db
object_name: db1
allocated: 96.00 KiB
data: 59.53 KiB
pages: 6
pages_hashed: 0
pages_old: 3
rows_cached: 797
1 row in set (1.11 sec)
1.3 查看表的全表扫描情况，看看哪些表需要做下优化
mysql> select * from schema_tables_with_full_table_scans limit 5 \G
*************************** 1. row ***************************
object_schema: db0
object_name: tb0
rows_full_scanned: 21072153342
latency: 1.28 d
*************************** 2. row ***************************
object_schema: db1
object_name: tb1
rows_full_scanned: 19108957224
latency: 1.55 d
*************************** 3. row ***************************
object_schema: db2
object_name: tb2
rows_full_scanned: 9778136634
latency: 17.10 h
*************************** 4. row ***************************
object_schema: db3
object_name : tb3
rows_full_scanned: 8340304503
latency: 4.79 h
*************************** 5. row ***************************
object_schema: db4
object_name: tb4
rows_full_scanned: 6355100618
latency: 11.86 h
5 rows in set (1.70 sec)
2. 索引的情况
2.1 有哪些无用的索引(即冗余索引)
mysql> select * from schema_redundant_indexes \G
*************************** 1. row ***************************
table_schema: tb1
table_name: tb1
redundant_index_name: xxx
redundant_index_columns: xxx_prefix
redundant_index_non_unique: 1
dominant_index_name: xxx_prefix
dominant_index_columns: xxx_prefix,xxxx
dominant_index_non_unique: 0
subpart_exists: 0
sql_drop_index: ALTER TABLE `db1`.`tb1` DROP INDEX `xxx`
2.2 哪些索引没有用到
mysql> select * from schema_unused_indexes limit 2 \G
*************************** 1. row ***************************
object_schema: db1
object_name: tb1
index_name: xxxxx
*************************** 2. row ***************************
object_schema: db2
object_name: tb2
index_name: xxxxxx
2 rows in set (0.04 sec)
2.3 查看索引的select \update\delete\insert情况
mysql> select * from schema_index_statistics limit 1 \G
*************************** 1. row ***************************
table_schema: tb1
table_name: tb1
index_name: xxxxxxx
rows_selected: 376085833
select_latency: 1.58 h
rows_inserted: 0
insert_latency: 0 ps
rows_updated: 39676093
update_latency: 28.00 m
rows_deleted: 0
delete_latency: 0 ps
1 row in set (0.04 sec)
3. 语句相关
3.1 数据库中哪些SQL被频繁执行
mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10;
+-------------+------------+-------------------------------------------------------------------+
| db | exec_count | query |
+-------------+------------+-------------------------------------------------------------------+
| db1 | 20174 | SELECT `date_format` ( `locati ... AL ? SQL_TSI_SECOND ) LIMIT ? |
| db2 | 19461 | SELECT `date_format` ( `locati ... AL ? SQL_TSI_SECOND ) LIMIT ? |
+-------------+------------+-------------------------------------------------------------------+
3.2 哪些SQL执行了全表扫描或执行了排序操作
mysql> select * from statements_with_sorting limit 1 \G
*************************** 1. row ***************************
query: SELECT `xxxx` FROM `xxx ... ER BY COUNT ( `xxxx` ) DESC
db: db1
exec_count: 85
total_latency: 1.87 m
sort_merge_passes: 0
avg_sort_merges: 0
sorts_using_scans: 85
sort_using_range: 0
rows_sorted: 2295
avg_rows_sorted: 27
first_seen: 2018-07-27 11:59:17
last_seen: 2018-09-01 18:59:19
digest: 1419efc6ce6a95e654f128cded18e49a
mysql> select * from statements_with_full_table_scans limit 1 \G
*************************** 1. row ***************************
query: SELECT `xxx` ( `xxx` , ? ... . `xxx` ORDER BY `xxx`
db: db1
exec_count: 1
total_latency: 998.92 us
no_index_used_count: 1
no_good_index_used_count: 0
no_index_used_pct: 100
rows_sent: 48
rows_examined: 192
rows_sent_avg: 48
rows_examined_avg: 192
first_seen: 2018-10-15 16:58:03
last_seen: 2018-10-15 16:58:03
digest: 1c7129051c2dbad8dfc34ad6009eda7f
1 row in set (0.19 sec)
3.3 哪些SQL语句使用了临时表，又有哪些用到了磁盘临时表
mysql> select db, query, tmp_tables, tmp_disk_tables from statement_analysis where tmp_tables>0 or tmp_disk_tables >0 order by (tmp_tables+tmp_disk_tables) desc limit 3;
+-------+-------------------------------------------------------------------+------------+-----------------+
| db | query | tmp_tables | tmp_disk_tables |
+-------+-------------------------------------------------------------------+------------+-----------------+
| xxxxx | SHOW TABLES LIKE ? | 54917 | 0 |
| xxxxx | SELECT `xxxx` ( `xxxx` , ? ... . `xxx` ORDER BY `xxxxxxxxxxxx` | 14957 | 0 |
| xxxxx | SELECT `xxxx` ( `xxxx` , ? ... . `xxx` ORDER BY `xxxxxxxxxxxx` | 14957 | 0 |
+-------+-------------------------------------------------------------------+------------+-----------------+
3 rows in set (0.03 sec)
4. 用户相关
4.1 排查每个host的资源消耗情况
mysql> select * from host_summary \G
*************************** 1. row ***************************
host: xxxx
statements: 2048
statement_latency: 4.81 s
statement_avg_latency: 2.35 ms
table_scans: 917
file_ios: 7882
file_io_latency: 2.51 s
current_connections: 0
total_connections: 6
unique_users: 1
current_memory: 0 bytes
total_memory_allocated: 0 bytes
4.2 每个host的主要是在什么文件类型上耗费IO资源
mysql> select * from host_summary_by_file_io_type limit 10;
+----------------+--------------------------------------+-------+---------------+-------------+
| host | event_name | total | total_latency | max_latency |
+----------------+--------------------------------------+-------+---------------+-------------+
| xxxxxxxxxxxxx | wait/io/file/sql/FRM | 2868 | 1.62 s | 71.06 ms |
| xxxxxxxxxxxxx | wait/io/file/innodb/innodb_data_file | 387 | 870.14 ms | 55.22 ms |
| xxxxxxxxxxxxx | wait/io/file/myisam/dfile | 4627 | 29.50 ms | 8.70 ms |
| xxxxxxxxxxxxx | wait/io/file/sql/FRM | 24 | 115.50 ms | 48.00 ms |
| xxxxxxxxxxxxx | wait/io/file/myisam/dfile | 145 | 105.90 ms | 56.94 ms |
| xxxxxxxxxxxxx | wait/io/file/sql/FRM | 45 | 162.71 ms | 28.65 ms |
| xxxxxxxxxxxxx | wait/io/file/myisam/dfile | 581 | 61.59 ms | 30.83 ms |
| xxxxxxxxxxxxx | wait/io/file/myisam/kfile | 8 | 2.28 ms | 1.37 ms |
| xxxxxxxxxxxxx | wait/io/file/sql/FRM | 1107 | 1.08 s | 66.09 ms |
| xxxxxxxxxxxxx | wait/io/file/myisam/dfile | 3321 | 39.72 ms | 34.06 ms |
+----------------+--------------------------------------+-------+---------------+-------------+
10 rows in set (0.02 sec)
4.3 查看每个host的语句执行情况
mysql> select * from host_summary_by_statement_latency limit 1 ;
+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| localhost | 3958366834 | 5.98 d | 20.24 s | 5.10 h | 692352951 | 1155909355 | 92068180 | 26536936 |
+-----------+------------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
1 row in set (0.02 sec)
4.4 查看某个host 执行的语句在各个阶段的耗时
mysql> select * from host_summary_by_statement_type where host = 'xxxxxxxxxxxxxx' ;
+----------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| host | statement | total | total_latency | max_latency | lock_latency | rows_sent | rows_examined | rows_affected | full_scans |
+----------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
| xxxxxxxxxxxxxx | select | 6684 | 1.30 m | 39.87 ms | 1.75 s | 2313061 | 25859716 | 0 | 6660 |
| xxxxxxxxxxxxxx | show_tables | 6687 | 4.97 s | 3.66 ms | 723.36 ms | 13962 | 13962 | 0 | 6687 |
| xxxxxxxxxxxxxx | begin | 346 | 1.36 s | 37.39 ms | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | insert | 692 | 167.14 ms | 436.55 us | 88.72 ms | 0 | 0 | 692 | 0 |
| xxxxxxxxxxxxxx | delete | 34 | 117.19 ms | 67.60 ms | 67.61 ms | 0 | 5874 | 690 | 0 |
| xxxxxxxxxxxxxx | Field List | 271 | 57.89 ms | 527.03 us | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | set_option | 240 | 18.77 ms | 133.34 us | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | show_warnings | 116 | 9.57 ms | 149.68 us | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | commit | 2 | 7.69 ms | 4.58 ms | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | show_databases | 1 | 1.41 ms | 1.41 ms | 585.00 us | 7 | 7 | 0 | 1 |
| xxxxxxxxxxxxxx | Quit | 49 | 429.66 us | 20.43 us | 0 ps | 0 | 0 | 0 | 0 |
| xxxxxxxxxxxxxx | Init DB | 1 | 49.35 us | 49.35 us | 0 ps | 0 | 0 | 0 | 0 |
+----------------+----------------+-------+---------------+-------------+--------------+-----------+---------------+---------------+------------+
12 rows in set (0.01 sec)
5. innodb 相关
5.1 查看下各个数据库的内存占用情况
mysql> select * from innodb_buffer_stats_by_schema ;
+---------------+------------+------------+-------+--------------+-----------+-------------+
| object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached |
+---------------+------------+------------+-------+--------------+-----------+-------------+
| XXXXXXXXXXX | 344.08 MiB | 293.56 MiB | 22021 | 13900 | 6250 | 973010 |
| XXXXXXXXXXX | 129.67 MiB | 77.38 MiB | 8299 | 2639 | 4874 | 1022327 |
| XXXXXXXXXXX | 48.55 MiB | 39.89 MiB | 3107 | 0 | 1375 | 552809 |
| XXXXXXXXXXX | 24.81 MiB | 22.56 MiB | 1588 | 28 | 61 | 25596 |
| XXXXXXXXXXX | 944.00 KiB | 537.57 KiB | 59 | 44 | 50 | 5227 |
| XXXXXXXXXXX | 128.00 KiB | 24.60 KiB | 8 | 3 | 6 | 157 |
| XXXXXXXXXXX | 64.00 KiB | 4.34 KiB | 4 | 0 | 4 | 25 |
| XXXXXXXXXXX | 16.00 KiB | 1.50 KiB | 1 | 0 | 0 | 17 |
| XXXXXXXXXXX | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 |
+---------------+------------+------------+-------+--------------+-----------+-------------+
9 rows in set (0.74 sec)
5.2 查看各个表内存占用情况
mysql> select * from innodb_buffer_stats_by_table limit 2 \G;
*************************** 1. row ***************************
object_schema: db1
object_name: tb1
allocated: 213.92 MiB
data: 175.95 MiB
pages: 13691
pages_hashed: 12123
pages_old: 2306
rows_cached: 1154997
*************************** 2. row ***************************
object_schema: db2
object_name: tb2
allocated: 82.75 MiB
data: 76.26 MiB
pages: 5296
pages_hashed: 0
pages_old: 2183
rows_cached: 1065643
2 rows in set (0.96 sec)
6.结束语
上述是我整理的一些常见的查询。当然还有很多的相关运用，大家可以查询下mysql的文档。