监控MySQL状态的一些SQL

监控MySQL状态的一些SQL

-- 查询未使用索引
SELECT * FROM sys.schema_unused_indexes;
-- 查询表的访问量
SELECT table_schema,TABLE_NAME,SUM(io_read_requests + io_write_requests) AS IO FROM sys.schema_table_statistics 
GROUP BY table_schema,TABLE_NAME ORDER BY IO DESC;
-- 查询占用bufferpool较多的表
SELECT object_schema,object_name,allocated,DATA FROM sys.innodb_buffer_stats_by_table ORDER BY allocated LIMIT 10;
-- 查看表的全表扫描情况
SELECT * FROM sys.statements_with_full_table_scans WHERE db='demo' ORDER BY total_latency ASC;
-- 监控使用了排序的sql
SELECT db,exec_count,first_seen,last_seen,QUERY FROM sys.statements_with_sorting;
-- 查看消耗磁盘IO的文件
SELECT FILE,avg_read,avg_write,avg_read+avg_write AS avg_io FROM sys.io_global_by_file_by_bytes ORDER BY avg_write;
-- 行锁阻塞情况
SELECT * FROM sys.innodb_lock_waits;