2. 慢查询成因分析：SQL编写不当、索引缺失、数据量过大、锁竞争、硬件瓶颈
慢查询是数据库性能劣化的核心表象，其成因可归纳为五个维度。理解这些成因是设计连接池与熔断策略的前提——不同成因对连接池的冲击模式不同，熔断的触发阈值与恢复策略也应随之调整。
2.1 成因全景图
以下SVG图展示了慢查询的五类成因及其在数据库系统中的影响链路，从应用层到硬件层逐级传导。
2.2 各成因深度分析
2.2.1 SQL编写不当
SQL编写不当是慢查询最直接的成因，通常表现为查询计划未走索引或扫描行数远超预期。
| 反模式 | 示例 | 影响 |
|---|---|---|
| SELECT * 返回多余列 | SELECT * FROM orders WHERE user_id = 100 | 增加网络传输与内存消耗 |
| 函数包裹索引列 | WHERE DATE(create_time) = '2024-01-01' | 索引失效，触发全表扫描 |
| 隐式类型转换 | WHERE phone = 13800138000（phone为varchar） | 索引失效 |
| 非等值连接条件 | ON a.id != b.id | 笛卡尔积 |
| 子查询优化不佳 | WHERE id IN (SELECT id FROM ...) 未做半连接优化 | 重复扫描驱动表 |
排查建议：使用
EXPLAIN 分析执行计划，重点关注 type（ALL 表示全表扫描）、rows（扫描行数）、Extra（Using filesort、Using temporary 等警告）。
2.2.2 索引缺失与失效
索引是加速查询的核心手段，但缺失或失效的索引反而会带来额外的维护开销。
- 索引缺失：高频查询条件列未建立索引，导致每次查询都走全表扫描。
- 索引选择性差：重复值过多的列（如性别、状态码）不适合建索引，优化器可能放弃使用。
- 索引失效场景：
- 索引列参与运算：
WHERE age + 1 = 30 - LIKE 以通配符开头：
WHERE name LIKE '%张' - 联合索引未遵循最左前缀原则：索引 (a,b,c)，查询条件只用到 b 和 c
- 索引列参与运算：
2.2.3 数据量过大
数据量增长是慢查询的渐进式诱因。当单表行数超过千万级，即使走了索引，B+树层数增加也会导致IO次数上升。
| 数据规模 | 典型表现 | 应对策略 |
|---|---|---|
| 百万级 | 索引查询通常 < 10ms | 常规索引优化即可 |
| 千万级 | 索引查询 10~100ms，全表扫描秒级 | 分库分表、归档历史数据 |
| 亿级 | 索引查询可能 > 100ms，写入性能下降 | 分片 + 读写分离 + 缓存 |
注意：数据量过大不仅影响查询，还会导致索引维护成本上升、缓冲池命中率下降、备份恢复时间延长。连接池中的长连接可能因慢查询堆积而耗尽。
2.2.4 锁竞争
锁竞争在高并发写入场景下尤为突出，表现为查询等待锁释放，导致响应时间飙升。
- 行锁升级：InnoDB 行锁升级为表锁（如大量行锁超过阈值）
- 间隙锁冲突：RR 隔离级别下，范围查询会锁定间隙，阻塞其他插入
- 死锁重试：死锁检测触发回滚，应用层重试进一步加剧竞争
- DDL 阻塞 DML：ALTER TABLE 等 DDL 操作需要元数据锁，阻塞后续查询
以下SVG图展示了锁竞争导致慢查询的典型链路：
2.2.5 硬件瓶颈
硬件瓶颈是慢查询的底层限制因素，通常表现为资源饱和后的性能断崖式下降。
| 资源类型 | 瓶颈表现 | 典型阈值 | 对连接池的影响 |
|---|---|---|---|
| 磁盘IOPS | 查询等待IO，avg-latency > 50ms | SSD 通常 10K~100K IOPS | 查询响应变慢，连接占用时间延长 |
| 内存 | 缓冲池命中率下降，频繁LRU淘汰 | InnoDB buffer pool < 70% 命中率 | 物理读增加，查询延迟上升 |
| CPU | CPU使用率持续 > 80%，上下文切换频繁 | 单核超过 80% 持续 5 分钟 | 连接处理能力下降，队列堆积 |
| 网络 | TCP重传率 > 1%，延迟抖动 | 内网延迟 > 2ms | 连接建立/释放变慢，池化收益降低 |
实战要点：硬件瓶颈往往与SQL问题叠加出现。例如，索引缺失导致大量全表扫描，进而耗尽磁盘IOPS和CPU。排查时应优先排除SQL和索引问题，再评估硬件扩容的必要性。
2.3 成因关联与排查优先级
五类成因并非孤立存在，它们之间存在因果传导关系。建议按以下优先级排查：
- SQL编写不当 —— 最易修复，收益最高
- 索引缺失/失效 —— 通过慢查询日志 + EXPLAIN 快速定位
- 数据量过大 —— 检查表行数与数据分布
- 锁竞争 —— 通过
SHOW ENGINE INNODB STATUS或 performance_schema 分析 - 硬件瓶颈 —— 监控系统指标，确认资源是否饱和
工具推荐：使用
pt-query-digest 分析慢查询日志，按查询指纹聚合排序；使用 sys.schema_unused_indexes 查找冗余索引；使用 performance_schema.events_statements_summary_by_digest 定位高频慢查询。