首页 › MySQL › 运维与部署（在新窗口打开） › 监控

MySQL 监控是整个运维体系的"眼睛"。没有监控，你无法知道数据库何时会出问题。

内置监控命令

SHOW GLOBAL STATUS

最常用的 MySQL 状态变量查询：

-- 连接相关
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW GLOBAL STATUS LIKE 'Connection_errors%';

-- InnoDB Buffer Pool 命中率
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- 命中率 = Innodb_buffer_pool_read_requests /
--          (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)

-- 查询与事务
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

-- 临时表
SHOW GLOBAL STATUS LIKE 'Created_tmp%';
-- Created_tmp_disk_tables 如果占比高，需要调大 tmp_table_size

SHOW PROCESSLIST

查看当前正在执行的连接：

SHOW PROCESSLIST;
-- 或
SELECT * FROM information_schema.PROCESSLIST;

-- 找出运行时间最长的查询（可能有问题的慢查询）
SELECT id, user, host, db, command, time, state, info
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep'
ORDER BY time DESC
LIMIT 10;

-- 找出锁等待的查询（Waiting for table metadata lock 等）
SELECT * FROM information_schema.PROCESSLIST
WHERE state LIKE '%lock%' OR state LIKE '%wait%';

SHOW ENGINE INNODB STATUS

SHOW ENGINE INNODB STATUS\G

关注以下段落：

段落	关注点
TRANSACTIONS	当前活跃事务、锁等待
ROW OPERATIONS	行操作统计
BUFFER POOL AND MEMORY	Buffer Pool 大小、命中率、脏页比例
LATEST DETECTED DEADLOCK	最近一次死锁详情

Performance Schema

Performance Schema 是 MySQL 5.5+ 引入的细粒度性能监控框架，默认为 ON。

常用查询

-- 按等待时间排序，找出最热的表
SELECT OBJECT_SCHEMA, OBJECT_NAME,
       COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA = 'demo'
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 最近 10 条执行时间最长的 SQL
SELECT digest_text, count_star, avg_timer_wait
FROM performance_schema.events_statements_summary_by_digest
ORDER BY avg_timer_wait DESC
LIMIT 10;

sys schema

sys schema 提供了一系列易读的视图，封装了 Performance Schema 的复杂查询：

-- 查看全表扫描的查询
SELECT * FROM sys.statements_with_full_table_scans
LIMIT 10;

-- 查看使用文件排序的查询
SELECT * FROM sys.statements_with_sorting
LIMIT 10;

-- 查看 IO 最热的文件
SELECT * FROM sys.io_global_by_file_by_bytes
WHERE file LIKE '%demo%'
LIMIT 10;

-- 查看内存使用（MySQL 8.0）
SELECT * FROM sys.memory_global_total;

-- 查看 InnoDB 指标
SELECT * FROM sys.innodb_buffer_stats_by_schema
WHERE object_schema = 'demo';

-- 查看索引未被使用的表
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema = 'demo';

诊断慢查询根因

-- 哪个查询消耗了最多的 IO
SELECT * FROM sys.statement_analysis
ORDER BY avg_latency DESC
LIMIT 10;

-- 查看特定表的索引使用情况
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'demo' AND table_name = 'employee';

外部监控方案

Prometheus + mysqld_exporter + Grafana

这是目前最流行的 MySQL 监控方案：

# 安装 mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz

# 创建监控账号（在 MySQL 中）
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'exporter_pass';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';

# 启动 exporter
./mysqld_exporter \
    --config.my-cnf=/etc/mysql_exporter/my.cnf \
    --web.listen-address=:9104

# Prometheus 配置 scrape target
# - job_name: 'mysql'
#   static_configs:
#     - targets: ['localhost:9104']

Grafana 推荐 Dashboard：MySQL Overview（ID: 7362）。

关键监控指标

指标	告警阈值	说明
Threads_connected	> max_connections × 0.8	连接池接近上限
Innodb_buffer_pool_reads	> 0（间歇性 > 0 且持续）	缓存命中率下降
Slow_queries（速率）	> 10/分钟	性能下降
Seconds_Behind_Master	> 30	复制延迟
磁盘使用率	> 80%	空间不足

监控指标 SQL 查询

-- QPS 与 TPS
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

-- 当前活跃连接
SELECT COUNT(*) AS active_connections
FROM information_schema.PROCESSLIST
WHERE command != 'Sleep';

-- 慢查询数量
SHOW GLOBAL STATUS LIKE 'Slow_queries';

-- Buffer Pool 命中率
SELECT
    (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
          WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
        / (SELECT VARIABLE_VALUE FROM performance_schema.global_status
           WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
    ) * 100 AS buffer_pool_hit_ratio;

-- 表大小排名
SELECT TABLE_SCHEMA, TABLE_NAME,
       ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS total_size_mb,
       ROUND(DATA_LENGTH / 1024 / 1024, 2) AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS index_mb,
       TABLE_ROWS
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY total_size_mb DESC
LIMIT 20;

小结

• 内置监控：SHOW GLOBAL STATUS（连接/缓存/查询统计）、SHOW PROCESSLIST（活跃连接）、SHOW ENGINE INNODB STATUS（事务与锁）。
• Performance Schema + sys schema 提供细粒度的性能分析视图。
• 外部方案：Prometheus + mysqld_exporter + Grafana 是主流选择。
• 关键指标：连接数、QPS、Buffer Pool 命中率、慢查询、复制延迟、磁盘空间。

上一节：分库分表

贡献者: 布欧-Lewyon