日常维护MySQL常用sql

king 监控 2023-02-26 800浏览 0

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;