MySQL DBA 日常运维常用命令总结

1. 查看当前数据库中的会话状态

show processlist;

在这里插入图片描述

2. 查看当前数据库中的活动会话（排除掉空闲Sleep状态的会话）

select * from performance_schema.processlist where command <> 'Sleep';#排除掉自己的会话连接
select * from information_schema.processlist where command <> 'Sleep' and id <> connection_id();

在这里插入图片描述

3. 查看数据库的总大小

select round(sum(data_length+index_length)/1024/1024/1024,2) as 'DBSIZE_GB' 
from information_schema.tables;

在这里插入图片描述

4. 查看数据库中各个库的大小合计

select table_schema,round(sum(data_length+index_length)/1024/1024/1024,3) as 'SIZE_GB' 
from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema')
group by table_schema ;

在这里插入图片描述

5. 查看数据库中的TOP 30大表信息

select table_schema,table_name,round((data_length+index_length)/1024/1024,2) as 'SIZE_MB',table_rows,engine 
from information_schema.tables 
where table_schema not in ('sys','mysql','information_schema','performance_schema') 
order by 3 desc limit 30 ;

在这里插入图片描述

6. 查看表和索引的统计信息：

#表统计
select * from mysql.innodb_table_stats where database_name='数据库名' and table_name='表名';#索引统计
select * from mysql.innodb_index_stats where database_name='数据库名' and table_name='表名' and index_name='索引名';

在这里插入图片描述

7. 查询锁等待时持续间大于20秒的SQL信息

SELECT trx_mysql_thread_id AS PROCESSLIST_ID,NOW(),TRX_STARTED,TO_SECONDS(now())-TO_SECONDS(trx_started) AS TRX_LAST_TIME ,USER,HOST,DB,TRX_QUERY
FROM INFORMATION_SCHEMA.INNODB_TRX trx
JOIN sys.innodb_lock_waits lw ON trx.trx_mysql_thread_id=lw.waiting_pid
JOIN INFORMATION_SCHEMA.processlist pcl ON trx.trx_mysql_thread_id=pcl.id
WHERE trx_mysql_thread_id != connection_id()AND TO_SECONDS(now())-TO_SECONDS(trx_started) >= 20 ;

8. 查询MySQL锁等待表的详细信息

#sys库
select * from sys.innodb_lock_waits\G

9. 查询长事务SQL

#--长事务（包含未关闭的事务）
SELECT thr.processlist_id AS mysql_thread_id,concat(PROCESSLIST_USER,'@',PROCESSLIST_HOST) User,Command,FORMAT_PICO_TIME(trx.timer_wait) AS trx_duration,current_statement as `latest_statement`FROM performance_schema.events_transactions_current trxINNER JOIN performance_schema.threads thr USING (thread_id)LEFT JOIN sys.processlist p ON p.thd_id=thread_idWHERE thr.processlist_id IS NOT NULL AND PROCESSLIST_USER IS NOT NULL AND trx.state = 'ACTIVE'GROUP BY thread_id, timer_wait ORDER BY TIMER_WAIT DESC LIMIT 10;

在这里插入图片描述

10. 查看当前DDL执行的进度

select * from performance_schema .setup_instruments where name like 'stage/innodb/alter%';
select * from performance_schema .setup_consumers where name like '%stages%';#如果上面查询结果为NO，则需要做如下配置：
update setup_instrucments set enabled = 'YES' where name like 'stage/innodb/alter%';
update setup_consumers set enabled = 'YES' where name like '%stages%';#查询DDL
select stmt.sql_text,stage.event_name,concat(work_completed, '/', work_estimated) as progress,concat(round(100 * work_completed / work_estimated, 2), ' %') as processing_pct,sys.format_time(stage.timer_wait) as time_costs,concat(round((stage.timer_end - stmt.timer_start) / 1e12 *(work_estimated - work_completed) / work_completed,2),' s') as remaining_secondsfrom performance_schema.events_stages_current     stage,performance_schema.events_statements_current stmtwhere stage.thread_id = stmt.thread_idand stage.nesting_event_id = stmt.event_id\G

11. 执行次数最多的TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN 
FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC LIMIT 10;

在这里插入图片描述

12. 平均响应时间最长的TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,AVG_TIMER_WAIT 
FROM events_statements_summary_by_digest ORDER BY AVG_TIMER_WAIT DESC limit 10;

在这里插入图片描述

13. 排序次数最多的TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_SORT_ROWS 
FROM events_statements_summary_by_digest ORDER BY SUM_SORT_ROWS DESC LIMIT 10;

在这里插入图片描述

14. 扫描记录数最多的 TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_ROWS_EXAMINED 
FROM events_statements_summary_by_digest ORDER BY SUM_ROWS_EXAMINED DESC LIMIT 10;

在这里插入图片描述

15. 使用临时表最多的TOP 10 SQL

SELECT QUERY_SAMPLE_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES 
FROM events_statements_summary_by_digest ORDER BY SUM_CREATED_TMP_TABLES DESC LIMIT 10;

在这里插入图片描述

16. 查询从未使用过的索引

select * from sys.schema_unused_indexes where object_schema not in ('performance_schema');

在这里插入图片描述

17. 查询冗余索引

select * from sys.schema_redundant_indexes\G

在这里插入图片描述

18. 查询数据库中没有主键的表

SELECT A.table_schema, A.table_name FROM information_schema.tables AS A LEFT JOIN (SELECT table_schema, table_name FROM information_schema.statistics WHERE index_name = 'PRIMARY') AS B ON A.table_schema = B.table_schema AND A.table_name = B.table_name WHERE A.table_schema NOT IN ('information_schema' , 'mysql','performance_schema', 'sys') AND A.table_type='BASE TABLE' AND B.table_name IS NULL;

在这里插入图片描述

19. 查询非InnoDB表

SELECT table_schema,table_name,engine 
FROM information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema') and engine!='InnoDB';

20. 查询从库状态信息（主从状态，延迟）

#主从状态: (Slave_IO_Running和Slave_SQL_Running 都为YES 且Seconds_Behind_Master 为0)
show slave status\G

21. 查看慢日志信息：是否开启及慢日志的位置

#查看日志状态和位置
show global variables like 'slow%';+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_launch_time    | 2                                 |
| slow_query_log      | OFF                               |
| slow_query_log_file | /var/lib/mysql/localhost-slow.log |
+---------------------+-----------------------------------+
3 rows in set (0.01 sec)#确认慢日志记录的时间阈值：
show global variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+