日常操作

zsh~發表於2024-03-21
索引情況

#1. 查詢冗餘索引
select * from sys.schema_redundant_indexes;
#2. 查詢未使用過的索引
select * from sys.schema_unused_indexes;
#3. 查詢索引的使用情況
select index_name,rows_selected,rows_inserted,rows_updated,rows_deleted
from sys.schema_index_statistics where table_schema='dbname' ;
表相關

# 1. 查詢表的訪問量
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;
# 2. 查詢佔用bufferpool較多的表
select object_schema,object_name,allocated,data
from sys.innodb_buffer_stats_by_table order by allocated limit 10;
# 3. 檢視錶的全表掃描情況
select * from sys.statements_with_full_table_scans where db='dbname';
語句相關

#1. 監控SQL執行的頻率
select db,exec_count,query from sys.statement_analysis
order by exec_count desc;
#2. 監控使用了排序的SQL
select db,exec_count,first_seen,last_seen,query
from sys.statements_with_sorting limit 1;
#3. 監控使用了臨時表或者磁碟臨時表的SQL
select db,exec_count,tmp_tables,tmp_disk_tables,query
from sys.statement_analysis where tmp_tables>0 or tmp_disk_tables >0
order by (tmp_tables+tmp_disk_tables) desc;
IO相關
#1. 檢視消耗磁碟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_read limit 10;
Innodb 相關
#1. 行鎖阻塞情況
select * from sys.innodb_lock_waits;

相關文章