mysql诊断调优常用SQL语句

在很多时候，我们需要通过SQL语句来查看MySQL执行SQL的情况，例如查看SQL执行队列，是否存在慢查询等等。

先看下基础配置，监控mysql执行的sql语句需要先开启相关日志

linux系统

可以在/etc/mysqld中添加如下：

指定日志路径

log =/usr/local/mysql/mysql.log （这个路径自定义即可）

就可以使用：

tail -f mysql.log

如果需要监控慢查询可以添加如下内容：

添加慢查下记录

log-slow-queries = /usr/local/mysql/slowquery.log（这个路径自定义即可）

long_query_time = 1

windows系统

修改my.ini，在mysqld下添加log一行，

[mysqld]

log = "D:/tmp/mysql_log/mysql_log.sql"  （这里路径自定义即可）

然后，重新启动mysql，就可以实时看到myql服务器当前正在执行的语句了。

常用的SQL，用于诊断排查问题

注：下列SQL在执行时，可能还需要你开启MySQL相应的配置项，请根据提示进行配置

#1. 查看当前应用连接，连接数突增排查

select user,SUBSTRING_INDEX(host,':',1) as ip , count(*) as count,db from information_schema.processlist where host not in ('localhost') and user not in ('replicater') group by ip order by count;

#2. 当前有没有锁

select * from information_schema.innodb_locks;

#3. 查看哪些sql执行最多

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY COUNT_STAR desc LIMIT 1;

#4. 哪个SQL扫描的行数最多(IO消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT,SUM_ROWS_SENT,SUM_ROWS_EXAMINED,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_ROWS_EXAMINED desc LIMIT 1

#5. 哪个SQL使用的临时表最多

SELECT SCHEMA_NAME,DIGEST_TEXT,SUM_CREATED_TMP_DISK_TABLES,SUM_CREATED_TMP_TABLES,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_CREATED_TMP_DISK_TABLES desc LIMIT 1

#6. 哪个SQL排序数最多(CPU消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_SORT_ROWS,FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where SCHEMA_NAME is not null and SCHEMA_NAME !='information_schema' ORDER BY SUM_SORT_ROWS desc LIMIT 1

#7. 哪个索引使用最多

SELECT OBJECT_NAME, INDEX_NAME, COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE FROM performance_schema.table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC limit 1;

#8. 哪个索引没有使用过

SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME FROM performance_schema.table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

#9. 哪个表、文件逻辑IO最多（热数据）

SELECT FILE_NAME,EVENT_NAME,COUNT_READ,SUM_NUMBER_OF_BYTES_READ,COUNT_WRITE,SUM_NUMBER_OF_BYTES_WRITE FROM performance_schema.file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ+SUM_NUMBER_OF_BYTES_WRITE DESC LIMIT 2

#10. 查看某条sql各阶段执行时间，可开启profiling功能

set global profiling=on;

该文件转载自苦叶子公众号，见二维码