sys

[toc]

主机相关

root@[localhost]: (sys) 15:51:54 >show tables like 'host_summar%';
+-----------------------------------+
| Tables_in_sys (host_summar%)      |
+-----------------------------------+
| host_summary                      |# 查看当前连接情况
| host_summary_by_file_io           |
| host_summary_by_file_io_type      |
| host_summary_by_stages            |
| host_summary_by_statement_latency |
| host_summary_by_statement_type    |
+-----------------------------------+
6 rows in set (0.01 sec)

Innodb相关

root@[localhost]: (sys) 16:01:05 >show tables like 'innodb_%';
+-------------------------------+
| Tables_in_sys (innodb_%)      |
+-------------------------------+
| innodb_buffer_stats_by_schema |# 每个库（database）占用多少buffer pool（慎用）
| innodb_buffer_stats_by_table  |# 哪个表占用了最多的buffer pool（慎用）
| innodb_lock_waits             |# 锁相关
+-------------------------------+
3 rows in set (0.03 sec)

IO相关

root@[localhost]: (sys) 16:11:45 >show tables like 'io_%';
+------------------------------+
| Tables_in_sys (io_%)         |
+------------------------------+
| io_by_thread_by_latency      |
| io_global_by_file_by_bytes   |# 哪个文件产生了最多的IO
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes   |
| io_global_by_wait_by_latency |
+------------------------------+
5 rows in set (0.02 sec)

内存相关

root@[localhost]: (sys) 16:13:04 >show tables like 'mem_%';
+-----------------------------------+
| Tables_in_sys (mem_%)             |
+-----------------------------------+
| memory_by_host_by_current_bytes   |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes   |
| memory_global_by_current_bytes    |
| memory_global_total               |# 查看总共分配了多少内存
+-----------------------------------+
5 rows in set (0.03 sec)

连接会话相关

root@[localhost]: (sys) 16:16:12 >show tables like 'session%';
+--------------------------+
| Tables_in_sys (session%) |
+--------------------------+
| session                  |# 和执行show full processlist的效果相当
| session_ssl_status       |
+--------------------------+
2 rows in set (0.00 sec)

root@[localhost]: (sys) 16:16:41 >show tables like 'processlist';
+-----------------------------+
| Tables_in_sys (processlist) |
+-----------------------------+
| processlist                 |
+-----------------------------+
1 row in set (0.01 sec)

表相关

root@[localhost]: (sys) 16:17:50 >show tables like 'schema_%';
+-------------------------------------+
| Tables_in_sys (schema_%)            |
+-------------------------------------+
| schema_auto_increment_columns       |# 自增主键监控
| schema_index_statistics             |# 查看索引的CRUD情况
| schema_object_overview              |
| schema_redundant_indexes            |# 查看冗余索引
| schema_table_lock_waits             |
| schema_table_statistics             |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |# 查看表格的全表扫描情况
| schema_unused_indexes               |# 查看无用索引
+-------------------------------------+
9 rows in set (0.01 sec)

语句相关

root@[localhost]: (sys) 16:22:12 >show tables like 'statement_%';
+---------------------------------------------+
| Tables_in_sys (statement_%)                 |
+---------------------------------------------+
| statement_analysis                          |# 查询热sql
| statements_with_errors_or_warnings          |
| statements_with_full_table_scans            |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting                     |# 查看语句的排序情况
| statements_with_temp_tables                 |# SQL语句中生成的临时表
+---------------------------------------------+
6 rows in set (0.01 sec)

用户相关

root@[localhost]: (sys) 16:26:27 >show tables like 'user_summary%';
+-----------------------------------+
| Tables_in_sys (user_summary_%)    |
+-----------------------------------+
| user_summary                      |# 每个用户消耗了多少资源
| user_summary_by_file_io           |
| user_summary_by_file_io_type      |
| user_summary_by_stages            |
| user_summary_by_statement_latency |
| user_summary_by_statement_type    |
+-----------------------------------+
5 rows in set (0.00 sec)

其他

• 查看没有主键的表

SELECT DISTINCT t.table_schema, t.table_name
      FROM information_schema.tables AS t
      LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema AND t.table_name = c.table_name AND c.column_key = "PRI"
     WHERE t.table_schema NOT IN ('information_schema', 'mysql', 'performance_schema')
       AND c.table_name IS NULL AND t.table_type != 'VIEW';

• 查看是谁创建的临时表

    SELECT user, host, event_name, count_star AS cnt, sum_created_tmp_disk_tables AS tmp_disk_tables, sum_created_tmp_tables AS tmp_tables
      FROM performance_schema.events_statements_summary_by_account_by_event_name
     WHERE sum_created_tmp_disk_tables > 0
        OR sum_created_tmp_tables > 0 ;

• 没有正确关闭数据库连接的用户

SELECT ess.user, ess.host
         , (a.total_connections - a.current_connections) - ess.count_star as not_closed
         , ((a.total_connections - a.current_connections) - ess.count_star) * 100 /
           (a.total_connections - a.current_connections) as pct_not_closed
      FROM performance_schema.events_statements_summary_by_account_by_event_name ess
      JOIN performance_schema.accounts a on (ess.user = a.user and ess.host = a.host)
     WHERE ess.event_name = 'statement/com/quit'
       AND (a.total_connections - a.current_connections) > ess.count_star ;