概述

分享一些mysql常用的一些sql，主要是在排查问题和做优化时用到。

实用sql

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 TABLE_SCHEMA,TABLE_NAME,TABLE_ROWS from information_schema.tables where TABLE_NAME='表名';

3、查看表碎片，是否需要整理表释放物理空间

select table_name,table_rows,concat(round(DATA_LENGTH/1024/1024, 2), 'MB') as size,DATA_FREE/1024/1024 AS data_free_MB from information_schema.TABLES where table_schema='库名' order by DATA_LENGTH desc;

4、当前有没有锁

select * from information_schema.innodb_locks;

5、当前锁堵塞情况

select * from information_schema.innodb_lock_waits;

6、当前锁等待详细信息

select it.trx_mysql_thread_id, il.lock_id, il.lock_table, il.lock_mode, il.lock_type, it.trx_state, pl.USER||'@'||pl.HOST as user_host, pl.db, pl.command, pl.info, it.trx_started, it.trx_wait_started, now()-trx_wait_started as wait_seconds, il.lock_index, it.trx_weight, it.trx_rows_locked, it.trx_rows_modified from information_schema.INNODB_TRX it,information_schema.innodb_locks il,information_schema.processlist pl where it.trx_id = il.lock_trx_id and it.trx_mysql_thread_id = pl.id\G

7、最近一次死锁、未提交事物、CHECKPIONT、BUFFER POOL等

show engine innodb status\G

8、过滤无用线程信息可用pager

pager grep -v Sleep;show processlist;

9、查看当前运行的详细SQL

SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE info is not null\G

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

set global profiling=on;

11、查看用户信息

select user,host,authentication_string from mysql.user group by user;

12、查看哪些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;

13、哪个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\G

14、哪个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\G

15、哪个SQL返回的结果集最多(net消耗)

SELECT SCHEMA_NAME,DIGEST_TEXT,COUNT_STAR,SUM_ROWS_SENT,SUM_ROWS_SENT,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_SENT desc LIMIT 1\G

16、哪个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\G

篇幅有限，关于MySQL在排查问题和优化时涉及的一些sql就介绍到这了，大家也可以自己测试一下。后面小编会分享更多DBA方面内容，感兴趣的朋友走一波关注哩~