运维与监控

日常运维盯什么

数据库跑起来之后，运维的工作可以拆成四块：

找慢 SQL：业务反馈「页面卡」，八成是某条 SQL 扫了百万行。慢查询日志是第一入口。
看现场：连接数、锁等待、事务、大表增长，这些是判断「现在有没有事」的即时快照。
看趋势：QPS、TPS、命中率、复制延迟，这些指标画成曲线才能发现「慢慢变坏」。
做巡检：每周/每月固定查一批项，把问题消灭在告警之前。

这篇把四块都过一遍：先讲慢查询怎么抓、怎么分析，再列关键 SQL 和指标，最后接 Prometheus 做长期监控。

慢查询日志

开启

慢查询日志默认是关闭的，long_query_time 默认 10 秒，实际使用必须显式开：

-- 动态开启（重启后失效，先这样验证）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL min_examined_row_limit = 100;

永久生效写进 my.cnf：

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = ON
min_examined_row_limit = 100

参数含义和取舍：

参数	作用	建议
`long_query_time`	超过多少秒算慢查询（支持小数）	生产 1 秒，核心业务可以 0.5 秒
`log_queries_not_using_indexes`	没走索引的语句也记	临时开，常开会把日志刷爆
`min_examined_row_limit`	扫描行数低于这个值不记	配合上一条用，过滤掉小表
`log_slow_admin_statements`	记录 `ALTER`/`ANALYZE` 等管理语句	排查 DDL 卡顿时开
`log_output`	输出到 `FILE`、`TABLE` 或两者	一般 `FILE`，需要 SQL 查询时用 `TABLE`

别在高峰期开 log_queries_not_using_indexes

它会把所有没索引的语句都记下来，包括每秒几千次的小查询。日志文件几分钟涨到几个 G，磁盘先满了。要开就配上 min_examined_row_limit，并且限定时段。

用 mysqldumpslow 粗筛

慢日志是纯文本，量大了没法看。mysqldumpslow 是 MySQL 自带的小工具，先把同类 SQL 聚合成「模式」（数字变成 N、字符串变成 S）：

# 按总耗时排序，取前 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 按出现次数排序
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按平均耗时排序，只看 SELECT
mysqldumpslow -s at -t 20 -g 'SELECT' /var/log/mysql/slow.log

# 看某天的日志
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log.2024-01-01

它快、轻、不用装东西，适合「先看看有没有明显的大头」。缺点是统计维度少、不区分时间窗口、看不出单次执行的上下文。

用 pt-query-digest 精查

Percona Toolkit 里的 pt-query-digest 是正经的分析工具，输出按「总耗时占比」排序，一眼看出谁在吃资源：

# 分析慢日志，取 TOP 10
pt-query-digest --limit 10 /var/log/mysql/slow.log

# 只看某个时间段（定位故障窗口）
pt-query-digest --since '2024-01-01 14:00:00' --until '2024-01-01 15:00:00' /var/log/mysql/slow.log

# 从 performance_schema 直接分析（无需日志文件）
pt-query-digest --processlist h=127.0.0.1,u=root,p=xxx

# 输出到文件，方便对比两次优化前后
pt-query-digest /var/log/mysql/slow.log > /tmp/slow_report.txt

报告里重点看三块：

Response time：总耗时占比，排第一的往往就是元凶。
Calls：执行次数。耗时占比高但次数少，说明单条慢；次数多而单条不慢，说明调用太频繁（该加缓存）。
Rows examine / Rows sent：扫描行数和返回行数。比例悬殊（比如扫 100 万返 10 行）说明索引不对。

Query 1: 0.42s total, 12 calls, 35ms avg
   SELECT * FROM orders WHERE status = ? AND created_at > ?
   Rows examine: 240000 avg, Rows sent: 12 avg, Ratio: 20000:1

Rows examine / Rows sent 到了两万比一，基本可以断定 status 列区分度太差又不走联合索引——补一个 (status, created_at) 的联合索引就能救。

优化要有前后对比

改索引/改 SQL 之前先存一份报告，改完再跑一份对比。没有对比就没有说服力，也容易「以为优化了其实只是流量降了」。

现场快照：performance_schema 与 information_schema

慢日志是「事后」，现场排查要的是「此刻」。下面这些查询是值班时最常用的，建议存成脚本。

当前连接与线程

-- 连接数、运行线程、历史峰值
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Threads_running';
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
SHOW VARIABLES LIKE 'max_connections';

-- 连接使用率（超过 80% 就该扩容或查泄漏）
SELECT
  (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected') AS connected,
  (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections') AS max_conn,
  ROUND(
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Threads_connected')
    / (SELECT VARIABLE_VALUE FROM performance_schema.global_variables WHERE VARIABLE_NAME = 'max_connections') * 100, 2
  ) AS pct;

-- 谁在占着连接：按来源 IP 和状态聚合
SELECT SUBSTRING_INDEX(HOST, ':', 1) AS client_ip, COMMAND, COUNT(*) AS cnt
FROM information_schema.PROCESSLIST
GROUP BY client_ip, COMMAND
ORDER BY cnt DESC;

Threads_running 才是「真正在干活的线程数」。它突然飙到几百，说明有大量查询在排队（通常是锁等待或全表扫），而 Threads_connected 高可能只是一堆 Sleep 的连接池连接。

谁在等锁

-- 正在等待的锁：谁等谁，等多久
SELECT
  r.trx_id              AS waiting_trx,
  r.trx_mysql_thread_id AS waiting_thread,
  r.trx_query           AS waiting_query,
  b.trx_id              AS blocking_trx,
  b.trx_mysql_thread_id AS blocking_thread,
  b.trx_query           AS blocking_query,
  TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_seconds
FROM performance_schema.data_lock_waits w
JOIN information_schema.INNODB_TRX r ON r.trx_id = w.REQUESTING_ENGINE_TRANSACTION_ID
JOIN information_schema.INNODB_TRX b ON b.trx_id = w.BLOCKING_ENGINE_TRANSACTION_ID;

-- 长事务：跑超过 60 秒还没提交的
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS seconds,
       trx_rows_locked, trx_rows_modified, trx_query
FROM information_schema.INNODB_TRX
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY seconds DESC;

8.0 里 information_schema.INNODB_LOCKS 和 INNODB_LOCK_WAITS 已经废弃，换成 performance_schema.data_locks 和 data_lock_waits。

TOP SQL 与表大小

-- 按总耗时排 TOP 10 的 SQL 模板（performance_schema 自带聚合）
SELECT DIGEST_TEXT,
       COUNT_STAR                                     AS calls,
       ROUND(SUM_TIMER_WAIT / 1e12, 2)                AS total_sec,
       ROUND(AVG_TIMER_WAIT / 1e9, 2)                 AS avg_ms,
       SUM_ROWS_EXAMINED                              AS rows_examined,
       SUM_ROWS_SENT                                  AS rows_sent
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- 最大的 20 张表（数据 + 索引 + 碎片）
SELECT TABLE_SCHEMA, TABLE_NAME,
       TABLE_ROWS,
       ROUND(DATA_LENGTH / 1024 / 1024, 1)  AS data_mb,
       ROUND(INDEX_LENGTH / 1024 / 1024, 1) AS index_mb,
       ROUND(DATA_FREE / 1024 / 1024, 1)    AS free_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA NOT IN ('mysql', 'sys', 'performance_schema', 'information_schema')
ORDER BY DATA_LENGTH + INDEX_LENGTH DESC
LIMIT 20;

-- 装了 sys schema 的话，这些视图更好用
SELECT * FROM sys.statement_analysis LIMIT 10;   -- 等价于上面的 TOP SQL
SELECT * FROM sys.schema_unused_indexes;         -- 从没被用过的索引
SELECT * FROM sys.innodb_lock_waits;             -- 锁等待，比手写 JOIN 省事

sys 库是 MySQL 自带的「人话版 performance_schema」，5.7 起就有。很多排查用它一行就够，不用自己拼 SQL。

核心指标

要长期看趋势，就得把这几个数字采集起来。它们分成四类：

QPS 与 TPS

-- QPS：每秒查询数（Questions 包含所有语句，Com_select 只算 SELECT）
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Com_select';

-- TPS：每秒事务数，近似等于 commit + rollback
SHOW GLOBAL STATUS LIKE 'Com_commit';
SHOW GLOBAL STATUS LIKE 'Com_rollback';

QPS 是累计值，要算速率：(当前值 - 上一分钟值) / 60。经验值上 QPS 突然翻倍只有三种可能：业务涨了、缓存挂了、有人压测。

连接使用率

Threads_connected / max_connections。超过 80% 就该处理，超过 90% 随时可能拒连。还要看 Aborted_connects（连接失败数）的增长率，它涨说明有人在暴力重试。

Buffer Pool 命中率

-- 命中率 = 1 - 物理读 / 逻辑读，低于 99% 基本就是 Buffer Pool 太小
SELECT
  ROUND(
    (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, 2
  ) AS hit_pct;

同时看 Innodb_buffer_pool_pages_free 和 Innodb_buffer_pool_wait_free：后者大于 0 说明 Buffer Pool 在等空闲页（脏页刷不过来），是危险信号。

主从延迟

-- 从库上执行
SHOW REPLICA STATUS\G
-- 关注 Seconds_Behind_Source、Relay_Log_Space、Retrieved_Gtid_Set 与 Executed_Gtid_Set 的差

Seconds_Behind_Source 有局限：主库长时间没写入时它显示 0，但其实从库可能已经停了；大事务执行中它也不准。生产上更可靠的是 pt-heartbeat（在主库定期写时间戳，从库读出来算差），或者用 Executed_Gtid_Set 与主库 gtid_executed 的集合差来估算。

指标要配告警，不能只看板

看板是给人看的，告警是给系统看的。没有告警的指标等于没有监控——半夜挂了没人知道。阈值建议：连接使用率 80%、复制延迟 30 秒、慢查询数每分钟超过 10 条、Buffer Pool 命中率低于 99%。

接 Prometheus

手工 SHOW GLOBAL STATUS 只能看当前，要做趋势和告警得靠时序库。MySQL 的标准方案是 mysqld_exporter，它把 SHOW GLOBAL STATUS 翻译成 Prometheus 指标。监控体系的基本概念（拉模型、指标类型、PromQL）见 Prometheus 核心概念与架构。

建监控账号

不要用 root 跑 exporter，建一个最小权限账号：

CREATE USER 'exporter'@'10.0.0.%' IDENTIFIED BY 'Exporter@2024' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'10.0.0.%';

PROCESS 让它能看 PROCESSLIST，REPLICATION CLIENT 让它能读复制状态，SELECT 用于采集表大小一类的信息。三个权限就够了，不要图省事直接给 ALL。

启动 exporter

docker run -d \
  --name mysqld-exporter \
  -p 9104:9104 \
  -e DATA_SOURCE_NAME='exporter:Exporter%402024@(10.0.0.11:3306)/' \
  prom/mysqld-exporter:v0.15.1

密码里的特殊字符要做 URL 编码（@ 是 %40）。启动后验证：

curl -s http://127.0.0.1:9104/metrics | grep -E '^mysql_(up|global_status_threads_connected)'

看到 mysql_up 1 就说明连上了。如果一直 0，先看 exporter 日志，常见原因是账号权限不够或 bind-address 没放开。

配置抓取与常用查询

scrape_configs:
  - job_name: "mysql"
    scrape_interval: 15s
    static_configs:
      - targets: ["10.0.0.11:9104"]
        labels:
          instance: "mysql-master"

接上之后，几个最常用的 PromQL：

# QPS
rate(mysql_global_status_queries[5m])

# 连接使用率（百分比）
mysql_global_status_threads_connected / mysql_global_variables_max_connections * 100

# Buffer Pool 命中率
(1 - rate(mysql_global_status_innodb_buffer_pool_reads[5m])
   / rate(mysql_global_status_innodb_buffer_pool_read_requests[5m])) * 100

# 主从延迟（秒）
mysql_slave_status_seconds_behind_master

# 慢查询增长速率
rate(mysql_global_status_slow_queries[5m])

# 实例是否存活
mysql_up

指标名以实际输出为准

上面用的是 mysqld_exporter v0.15 的命名（mysql_slave_status_*）。新版本 exporter 逐步把 slave 改成 replica，同一个含义可能叫 mysql_replica_status_seconds_behind_source。接监控时先 curl 一下 /metrics 看真实指标名，别照抄了旧名字然后发现图是空的。

对应的告警规则（片段）：

groups:
  - name: mysql
    rules:
      - alert: MySQLDown
        expr: mysql_up == 0
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "MySQL 实例不可达"

      - alert: MySQLReplicationLag
        expr: mysql_slave_status_seconds_behind_master > 30
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "主从延迟超过 30 秒"

指标太多就瘦身

mysqld_exporter 默认吐几百个指标，大部分用不上。用 --collect. 系列参数只开需要的 collector（global_status、global_variables、slave_status、innodb_metrics），能显著降低采集压力和 Prometheus 的序列数。

巡检清单

每周固定跑一遍，比等告警强：

# 1. 错误日志有没有新报错
grep -iE 'error|warning' /var/log/mysql/error.log | tail -50

# 2. 备份是否成功、大小是否正常
ls -lh /backup/ | tail -7

# 3. 磁盘剩余空间（低于 20% 就该清 binlog）
df -h /data

# 4. 复制状态（从库）
mysql -e "SHOW REPLICA STATUS\G" | grep -E "Running|Seconds_Behind|Last_.*Error"

# 5. 无主键表（会影响主从一致性和在线 DDL）
mysql -e "SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS c
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
WHERE t.TABLE_SCHEMA NOT IN ('mysql','sys','information_schema','performance_schema')
  AND c.CONSTRAINT_NAME IS NULL AND t.TABLE_TYPE = 'BASE TABLE';"

小结

慢查询分两步：mysqldumpslow 粗筛模式，pt-query-digest 看耗时占比、调用次数、扫描/返回比；日志参数注意 long_query_time 和 log_queries_not_using_indexes 的副作用。
现场排查三组 SQL：连接与线程、锁等待与长事务、TOP SQL 与表大小；performance_schema 是主战场，sys 库是省事的外壳。
四个必盯指标：QPS/TPS、连接使用率、Buffer Pool 命中率、主从延迟；Seconds_Behind_Source 有盲区，重要场景上 pt-heartbeat。
接监控用 mysqld_exporter，建最小权限账号，mysql_up 验活，PromQL 做趋势，阈值告警兜底。
巡检清单固定下来：错误日志、备份、磁盘、复制、无主键表，每周跑一遍。

监控能告诉你「不正常」，但不一定告诉你「为什么」。下一篇《故障排查与最佳实践》把最常见的事故场景逐个拆开：连接数打满、死锁、主从延迟、磁盘满。

日常运维盯什么​

慢查询日志​

开启​

用 mysqldumpslow 粗筛​

用 pt-query-digest 精查​

现场快照：performance_schema 与 information_schema​

当前连接与线程​

谁在等锁​

TOP SQL 与表大小​

核心指标​

QPS 与 TPS​

连接使用率​

Buffer Pool 命中率​

主从延迟​

接 Prometheus​

建监控账号​

启动 exporter​

配置抓取与常用查询​

巡检清单​

小结​