2. 慢查询成因分析：索引缺失、数据量过大、锁竞争、SQL编写不当

慢查询是数据库性能劣化的核心诱因，也是触发应用层 Circuit Breaker 降级的直接导火索。只有精准识别慢查询的根因，才能制定有效的连接池调优与熔断策略。本章从四个维度剖析慢查询的典型成因，并给出诊断方法与量化指标。

2.1 索引缺失：最普遍的“隐形杀手”

索引缺失导致数据库执行全表扫描（Full Table Scan），当表数据量超过百万行时，单次查询的 I/O 开销会从毫秒级飙升到秒级甚至分钟级。

场景 无索引执行计划 有索引执行计划 性能差异（百万行）
等值查询（WHERE id = ?） TABLE ACCESS FULL INDEX UNIQUE SCAN 1000x ~ 10000x
范围查询（WHERE date > ?） TABLE ACCESS FULL INDEX RANGE SCAN 100x ~ 500x
排序（ORDER BY col） FILESORT（磁盘临时表） INDEX SCAN（有序输出） 50x ~ 200x
JOIN 关联 NESTED LOOPS（驱动表全表） INDEX JOIN 100x ~ 1000x
诊断命令：MySQL 使用 EXPLAIN 查看 type 字段，若为 ALL 或 index（全索引扫描）则代表索引缺失或设计不当；PostgreSQL 使用 EXPLAIN ANALYZE 关注 Seq Scan 节点。
避坑指南：并非所有列都适合建索引。高选择性列（如用户ID、订单号）收益最大；低选择性列（如性别、状态码）建索引反而可能因回表开销劣化性能。复合索引应遵循“最左前缀”原则。
索引缺失 → 全表扫描 → 慢查询链路 无索引查询 WHERE id = 100 全表扫描 扫描 500 万行 磁盘 I/O 爆炸 读取 2GB 数据页 查询耗时 > 5s 诊断方法： MySQL: EXPLAIN SELECT ... → 关注 type=ALL; PostgreSQL: EXPLAIN (ANALYZE, BUFFERS) → 关注 Seq Scan 行数

2.2 数据量过大：分页与聚合的陷阱

当单表数据量突破千万级甚至亿级时，即使有索引，深度分页（LIMIT 100000, 20）和全表聚合（COUNT(*)、SUM()）依然会引发严重的性能问题。

  • 深度分页原理：数据库需要扫描并丢弃前 N 行，偏移量越大，扫描行数越多。例如 LIMIT 1000000, 10 需要扫描 1,000,010 行。
  • 聚合函数陷阱：COUNT(*) 在 InnoDB 中无法像 MyISAM 那样直接读取行数，必须走索引扫描，亿级表耗时可达数十秒。
  • 临时表溢出：GROUP BY 或 DISTINCT 操作若无法使用索引，会在磁盘创建临时表，性能急剧下降。
数据量级 查询类型 典型耗时 连接池影响
100 万行 简单索引查询 < 10ms 几乎无影响
1000 万行 深度分页（偏移 50 万） 500ms ~ 2s 连接占用时间延长
1 亿行 COUNT(*) 5s ~ 30s 连接池快速耗尽
1 亿行 GROUP BY 无索引 10s ~ 60s 触发 Circuit Breaker
生产案例：某电商订单表 8000 万行，后台管理页面使用 LIMIT 200000, 20 分页查询，单次查询耗时 12 秒，导致 50 个连接全部被阻塞，前端接口超时率达到 70%。解决方案：改用游标分页（WHERE id > ? LIMIT 20），耗时降至 3ms。

2.3 锁竞争：并发写入的“交通堵塞”

锁竞争在高并发写入场景下尤为突出。行锁、间隙锁、意向锁、元数据锁（MDL）之间的相互等待，会导致查询被阻塞，形成“雪崩式”慢查询。

锁竞争导致查询阻塞的典型时序 事务A: UPDATE 持有行锁 (id=1) 事务B: UPDATE 等待行锁 (id=1) 事务C: SELECT 被 MDL 阻塞 锁等待超时 innodb_lock_wait_timeout 事务回滚 应用层重试 查询超时 触发 Circuit Breaker
锁监控命令： 
-- MySQL 查看当前锁等待
SELECT * FROM information_schema.INNODB_TRX\G
SELECT * FROM performance_schema.data_lock_waits\G

-- PostgreSQL 查看锁等待
SELECT blocked_locks.pid AS blocked_pid,
       blocking_locks.pid AS blocking_pid
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_locks blocking_locks ON ...;

2.4 SQL 编写不当：隐式转换与函数陷阱

不规范的 SQL 写法会导致索引失效，即使表上有索引，查询优化器也无法使用。常见陷阱包括：

  • 隐式类型转换：字段类型为 varchar，传入数字 123，MySQL 会将字段全部转为数字再比较，索引失效。
  • 函数包裹索引列：WHERE DATE(create_time) = '2024-01-01' 导致无法使用 create_time 索引，应改为 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02'。
  • 前导通配符：LIKE '%keyword' 无法使用 B+Tree 索引，应改用全文索引或搜索引擎。
  • OR 条件不当：WHERE a = 1 OR b = 2 若 a、b 均有独立索引，优化器可能选择全表扫描，应改为 UNION ALL。
错误写法 索引失效原因 正确写法 性能提升
WHERE phone = 13800138000 隐式转换（varchar→int） WHERE phone = '13800138000' 100x+
WHERE DATE(create_time) = '2024-01-01' 函数包裹索引列 WHERE create_time >= '2024-01-01' AND create_time < '2024-01-02' 50x+
WHERE name LIKE '%张三' 前导通配符 全文索引 MATCH(name) AGAINST('张三') 100x+
WHERE a = 1 OR b = 2 OR 导致索引合并低效 SELECT ... WHERE a = 1 UNION ALL SELECT ... WHERE b = 2 10x+
生产案例：某金融系统查询语句 WHERE trade_time >= NOW() - INTERVAL 7 DAY 使用了函数 NOW()，导致优化器无法预估行数，选择了错误的执行计划。改写为 WHERE trade_time >= '2024-06-01 00:00:00'（应用层计算好时间常量）后，查询耗时从 3.2s 降至 12ms。

2.5 慢查询成因总结与优先级排序

在实际生产环境中，慢查询往往由多个因素叠加导致。以下按影响范围和修复成本给出优先级排序：

优先级 成因 影响范围 修复成本 推荐动作
P0 索引缺失 全局 低（加索引） 立即执行
P1 SQL 编写不当 局部 低（改写 SQL） 代码审查 + 慢查询日志
P2 数据量过大 局部 中（分库分表/归档） 制定数据生命周期策略
P3 锁竞争 全局 高（业务逻辑改造） 缩短事务 + 降低隔离级别
实战建议：在开启慢查询日志（slow_query_log = 1，long_query_time = 1）后，使用 pt-query-digest 或 mysqldumpslow 工具定期分析慢查询分布，优先处理出现频率最高的 TOP 10 查询。这是连接池调优和 Circuit Breaker 阈值设定的前置条件。
← 上一章 📖 返回目录 下一章 →