快捷的了解到MySQL的一些信息

sys库里面的表、视图、函数、存储过程可以使我们更方便、快捷的了解到MySQL的一些信息,比如哪些语句使用了临时表、哪个SQL没有使用索引、哪个schema中有冗余索引、查找使用全表扫描的SQL、查找用户占用的IO等,sys库里这些视图中的数据,大多是从performance_schema里面获得的。目标是把performance_schema的复杂度降低,让我们更快的了解DB的运行情况。

以字母开头的52个视图:

  • host_summary:这个是服务器层面的,以IP分组,比如里面的视图host_summary_by_file_io;
  • user_summary:这个是用户层级的,以用户分组,比如里面的视图user_summary_by_file_io;
  • innodb:这个是InnoDB层面的,比如视图innodb_buffer_stats_by_schema;
  • io:这个是I/O层的统计,比如视图io_global_by_file_by_bytes;
  • memory:关于内存的使用情况,比如视图memory_by_host_by_current_bytes;
  • schema:关于schema级别的统计信息,比如schema_table_lock_waits;
  • session:关于会话级别的,这类视图少一些,只有session和session_ssl_status;
  • statement:关于语句级别的,比如statements_with_errors_or_warnings;
  • wait:关于等待的,比如视图waits_by_host_by_latency。

常用查询介绍

查看每个客户端IP过来的连接消耗了多少资源。

mysql> select * from host_summary;

查看某个数据文件上发生了多少IO请求。

mysql> select * from io_global_by_file_by_bytes;

查看每个用户消耗了多少资源。

mysql> select * from user_summary;

查看总共分配了多少内存。

mysql> select * from memory_global_total;

查看当前连接情况。

mysql> select host, current_connections, statements from host_summary;

查看当前正在执行的SQL和执行show full processlist的效果相当。

mysql> select conn_id, user, current_statement, last_statement from session;

执行下面命令查询TOP 10最热/最频繁SQL。

mysql> select db,exec_count,query from statement_analysis order by exec_count desc limit 10;

哪个文件产生了最多的IO,读多,还是写的多?

mysql> select * from io_global_by_file_by_bytes limit 10;

哪个表上的IO请求最多?

mysql> select * from io_global_by_file_by_bytes where file like ‘%ibd’ order by total desc limit 10;

哪个表被访问的最多?先访问statement_analysis,根据热门SQL排序找到相应的数据表。

mysql> select * from statement_analysis order by avg_latency desc limit 10;

哪些SQL执行了全表扫描或执行了排序操作?

mysql> select * from statements_with_sorting;
mysql> select * from statements_with_full_table_scans;

哪些SQL语句使用了临时表,又有哪些用到了磁盘临时表? 查看statement_analysis中哪个SQL的tmp_tables 、tmp_disk_tables值大于0即可。

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 20;

哪个表占用了最多的buffer pool?

mysql> select * from innodb_buffer_stats_by_table order by allocated desc limit 10;

每个库(database)占用多少buffer pool?

mysql> select * from innodb_buffer_stats_by_schema order by allocated desc limit 10;

每个连接分配多少内存? 利用session表和memory_by_thread_by_current_bytes分配表进行关联查询。

mysql> select b.user, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated,current_statement from memory_by_thread_by_current_bytes a, session b where a.thread_id = b.thd_id;

MySQL自增长字段的最大值和当前已经使用到的值?

mysql> select * from schema_auto_increment_columns;

MySQL有哪些冗余索引和无用索引?

mysql> select * from schema_redundant_indexes;
mysql> select * from schema_unused_indexes;

查看事务等待情况

mysql> select * from innodb_lock_waits;