Routine maintenance MySQL common SQL, routine maintenance mysql

Routine maintenance MySQL common SQL, routine maintenance mysql

1. Slow SQL query:

You can use the following three methods to query. The first method is to understand the general situation of the MySQL process; the second is to query the slowest SQL statement according to the reverse order of the affected time; the third is to prevent the SQL info message from being too long to be fully displayed.

1 -- usual show processlist; 3 -- extend4 SELECT * FROM information_schema. 'processlist' WHERE info is not null order by time desc; 5 -- full info6 show full PROCESSLIST;

In addition, if a large number of slow SQL statements occur, you can kill some select statements without affecting the service, and then buffer the MySQL performance problems.

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

 

2. query the number of connections:

You can use the following SQL statement to query the number of connections of all databases in the current instance (because the SQL statement is determined based on the same host, one ip may exist and multiple connections may exist on different ports)

1 SELECT db, COUNT (1) FROM information_schema. 'processlist' group by db order by 2 DESC; 2 -- if you want to query the complete host3 SELECT db, host FROM information_schema. 'processlist' 4 -- where db = dbname; 5 -- if you want to query which hosts are connected to the current instance, do not consider host port 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. Estimated disk space:

The disk capacity is estimated based on the data volume and index volume. The following SQL statements show that the disk capacity is measured in MB.

1 -- query the total capacity of a database 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 -- query the table capacity of the apsaradb for rds for disk alerts 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 _ scheme', 'performance _ scheme ', 'sys _ info') 9 -- AND table_name IN ('tbname1', 'tbname2') 10 order by total_mb DESC;