Mysql性能监控常用查询命令_mysql监控命令

1. 查询缓存情况 SHOW VARIABLES LIKE '%cache%';

2. 查询慢查询情况
   SHOW VARIABLES LIKE '%slow%';
SHOW GLOBAL STATUS LIKE '%slow%';

3. 查看最大链接数
   SHOW VARIABLES LIKE 'max_connections';

4. 查看最大链接过的用户数
   SHOW GLOBAL STATUS LIKE 'max_used_connections';

5. 显示用户正在运行的线程
   SHOW PROCESSLIST;
   或
   SELECT * FROM information_schema.processlist;
   参考：mysql: show processlist 详解

   如果出现有表锁定，无法查询的情况，可以使用 该命令来查询出任务的线程的id，然后kill id，即可结束线程(注意不要随便在运行的服务上，做alter table操作，包含更改表备注，不然可能会导致表锁定，深刻体会)。参考：MySQL出现Waiting for table metadata lock的原因以及解决方法

6. 查询出innodb的引擎的情况 SHOW ENGINE INNODB STATUS
   参考：mysql性能监控指标及分析

7. 在线alter表的注意事项及注意的坑。
   7.1 查询事务：

   5.5 版本

SELECT
*
/**
    a.trx_id,
    a.trx_state,
    a.trx_started,
    a.trx_query,
    b.ID,
    b. USER,
    b. HOST,
    b.DB,
    b.COMMAND,
    b.TIME,
    b.STATE,
    b.INFO
    
**/
FROM
    information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
WHERE
    b.COMMAND = 'Sleep';
    
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

5.6版本（支持在线ddl）

SELECT
     a.trx_id,
     a.trx_state,
     a.trx_started,
     a.trx_query,
     b.ID,
     b.USER,
     b.DB,
     b.COMMAND,
     b.TIME,
     b.STATE,
     b.INFO,
     c.PROCESSLIST_USER,
     c.PROCESSLIST_HOST,
     c.PROCESSLIST_DB,
     d.SQL_TEXT
FROM
     information_schema.INNODB_TRX a
LEFT JOIN information_schema.PROCESSLIST b ON a.trx_mysql_thread_id = b.id
AND b.COMMAND = 'Sleep'
LEFT JOIN PERFORMANCE_SCHEMA.threads c ON b.id = c.PROCESSLIST_ID
LEFT JOIN PERFORMANCE_SCHEMA.events_statements_current d ON d.THREAD_ID = c.THREAD_ID;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

8. 查询某个表的索引情况，一般查询太慢都是没有命中联合索引.

SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name
having a.TABLE_NAME='dm_yy_kxd_nationnalver_ztsj_xg_day'
1
2
3
4
5
6
7

9. Mysql备份数据库：mysqldump -h 127.0.0.1 -P 3306 -u root -ppsd --databases samego > /home/alic/MySQL/samego.sql

   其中，127.0.0.1 为数据库地址，3306为数据库端口，root为数据库账号，psd 为数据库账号密码，samego为数据库的名称，/home/alic/MySQL/samego.sql 为备份的数据库的文件的地址.

   参考：MySQL在线DDL修改表结构的简单经验分享

10. 如果查询某个表，看起来数量很少，但是实际查询很慢，可能是因为有些数据还没实际删除。一般我们执行 delete 操作并不会实际删除表在磁盘上的数据，实际删除表在磁盘上的数据需要执行以下两个命令，optimize table t或alter table t engine=InnoDB 。

    注意，如果是Mysql 5.6 版本以下的会锁表。

    参考：mysql删除操作其实是假删除
    MySQL 官方文档

11. 查询数据库或表的容量
    查询数据库的容量

    SELECT 
    table_schema AS '数据库',
    SUM(TRUNCATE(data_length/1024/1024, 2)) AS '数据容量(MB)',
    SUM(TRUNCATE(data_free/1024/1024,2)) AS '空间碎片(MB)',
    SUM(TRUNCATE(index_length/1024/1024, 2)) AS '索引容量(MB)'
    FROM information_schema.tables
    GROUP BY table_schema 
    1
    2
    3
    4
    5
    6
    7

    查询表的容量

    SELECT 
    table_schema AS '数据库',
    table_name AS '表名',
    table_rows AS '记录数',
    TRUNCATE(data_length/1024/1024, 2) AS '数据容量(MB)',
    TRUNCATE(data_free/1024/1024,2) AS '空间碎片(MB)',
    TRUNCATE(index_length/1024/1024, 2) AS '索引容量(MB)'
    FROM information_schema.tables
    ORDER BY data_length DESC, index_length DESC;
    1
    2
    3
    4
    5
    6
    7
    8
    9

    2020-11-22 删除查询表容量中的 max_data_length 字段，该字段含义表示“表数据的最大容量”，参考，《高性能MySQL》第三版

12. 查询数据库的 bin_log 日志情况 show global variables like '%log_bin%';

以下为没有开bin-log日志的

以下为开了binlog日志的，binlog并且binlog日志，在log_bin_basename 对应的value目录中

13. 查询语句没有走对索引，可以执行一下 ANALYZE TABLE tbl_name 来重新调整表，不过注意执行这个的时候可能会加读锁，会影响写操作，和并发读操作。 参考：MySQL5.6 官方文档 , MySQL为什么有时候会选错索引？

14. ALTER TABLE test_table ENGINE=INNODB; MySQL 5.6 清除表的空间碎片，注意小心操作，如果表比较大，会耗费MySQL比较多资源 和可能锁表

15. sudo mysqlbinlog -vv --base64-output=decode-rows /data/zhangcanlong/binlog/mysql-bin.003303 --database=test_db --start-datetime='2022-03-22 14:00:00' --stop-datetime='2022-03-30 22:00:00'| grep -A 200 zhangcanlong_test_t 恢复指定时间下，指定库 test_db，指定表zhangcanlong_test_t 的binlog日志信息

16. SELECT * FROM INFORMATION_SCHEMA.TABLES 查询mysql表的各种信息，例如：创建时间，更新时间(这个表的最后更新时间，如果缓存不够，可能不会更新)，表行数等；SELECT * FROM mysql.innodb_table_stats WHERE database_name IN ('test_db') ORDER BY last_update DESC – 其中last_update 为最后更新时间 ，这个表的准确一些

参考

1. MySQL查看数据库表容量大小
2. 记一次 mysql 磁盘满解决过程
3. 打钱！我的数据库被黑客勒索了！
4. information_schema.tables 视图中，表的最后修改时间靠谱吗？