日常维护MySQL常用sql

1.慢sql情况查询：

  可以使用以下三种方式查询，第一种是了解MySQL进程大概情况；第二种是按照影响时间倒序的，可以查询到目前最慢的一条sql；第三种是防止sql 的info消息过长而无法显示完整。

-- usual
show processlist;
-- extend
SELECT * FROM information_schema.`PROCESSLIST` WHERE info IS NOT NULL ORDER BY TIME DESC;
-- full info
show full processlist;

  补充一下，若出现大量慢sql，在不影响业务的前提下，可以将一些select先kill掉，然后来缓冲一下MySQL的性能问题

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

2.连接数的查询：

  可以使用以下sql查询到当前实例下所有库的连接数（由于该sql是根据同一个host来判断的，所以可能存在一个ip，不同端口有多个连接）

SELECT db,COUNT(1) FROM information_schema.`PROCESSLIST` GROUP BY db ORDER BY 2 DESC;
-- 若想查询到完整的host
SELECT db,host FROM information_schema.`PROCESSLIST` 
-- where db = dbname;
-- 若想查询有哪些host连接到当前实例，而不考虑host端口
SELECT SUBSTRING_INDEX(HOST,':',1) ip,COUNT(1) FROM information_schema.`PROCESSLIST`
where db = dbname GROUP BY ip ORDER BY 2 DESC;

3.磁盘空间的预估：

  主要是根据数据量和索引量来对一个实例来进行预估磁盘容量，以下sql统计出来的结果都是以MB为单位

-- 查某个数据库的总容量
SELECT ROUND(SUM(data_length+index_length)/1024/1024,2) AS total_db_mb FROM information_schema.tables
WHERE table_schema  LIKE 'dbname%';

-- 查磁盘告警的rds的表容量
SELECT table_schema,table_name,table_rows,ROUND((data_length+index_length)/1024/1024,2) AS total_mb FROM information_schema.tables
WHERE -- table_schema  IN('dbname') and 
table_schema NOT IN ('mysql','information_schema','performance_schema','sys_info')
-- AND table_name IN('tbname1','tbname2') 
ORDER BY total_mb DESC;