日常維護MySQL常用sql

1.慢sql情況查詢：

  可以使用以下三種方式查詢，第一種是瞭解MySQL進程大概情況；第二種是按照影響時間倒序的，可以查詢到目前最慢的一條sql；第三種是防止sql 的info消息過長而無法顯示完整。

1 -- usual
2 show processlist;
3 -- extend
4 SELECT * FROM information_schema.`PROCESSLIST` WHERE info IS NOT NULL ORDER BY TIME DESC;
5 -- full info
6 show full processlist;

  補充一下，若出現大量慢sql，在不影響業務的前提下，可以將一些select先kill掉，然後來緩衝一下MySQL的性能問題

1 -- kill id
2 SELECT concat('kill ',id,' ;') FROM information_schema.`PROCESSLIST` WHERE info like 'select%' and time > 3 ORDER BY TIME DESC;

2.連接數的查詢：

  可以使用以下sql查詢到當前實例下所有庫的連接數（由於該sql是根據同一個host來判斷的，所以可能存在一個ip，不同埠有多個連接）

1 SELECT db,COUNT(1) FROM information_schema.`PROCESSLIST` GROUP BY db ORDER BY 2 DESC;
2 -- 若想查詢到完整的host
3 SELECT db,host FROM information_schema.`PROCESSLIST` 
4 -- where db = dbname;
5 -- 若想查詢有哪些host連接到當前實例，而不考慮host埠
6 SELECT SUBSTRING_INDEX(HOST,':',1) ip,COUNT(1) FROM information_schema.`PROCESSLIST`
7 where db = dbname GROUP BY ip ORDER BY 2 DESC;

3.磁碟空間的預估：

  主要是根據數據量和索引量來對一個實例來進行預估磁碟容量，以下sql統計出來的結果都是以MB為單位

 1 -- 查某個資料庫的總容量
 2 SELECT ROUND(SUM(data_length+index_length)/1024/1024,2) AS total_db_mb FROM information_schema.tables
 3 WHERE table_schema  LIKE 'dbname%';
 4 
 5 -- 查磁碟告警的rds的表容量
 6 SELECT table_schema,table_name,table_rows,ROUND((data_length+index_length)/1024/1024,2) AS total_mb FROM information_schema.tables
 7 WHERE -- table_schema  IN('dbname') and 
 8 table_schema NOT IN ('mysql','information_schema','performance_schema','sys_info')
 9 -- AND table_name IN('tbname1','tbname2') 
10 ORDER BY total_mb DESC;