mysql sys schema_MySQL sys Schema 简单介绍-2

之前在《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的文档。