日志与慢查询优化

undo log、redo log 与 binlog

三者所属层级、记录形式和用途不同：

日志	所属	大致内容	主要用途
undo log	InnoDB 引擎	数据修改前的逻辑版本信息	回滚、MVCC 读取历史版本
redo log	InnoDB 引擎	对数据页修改的物理/逻辑重做信息	WAL、崩溃恢复、保证持久性
binlog	MySQL Server 层	语句或行变更等逻辑变更事件	主从复制、时间点恢复、审计

WAL 与两阶段提交

InnoDB 修改页先在 Buffer Pool 中变脏，提交时通常先保证 redo log 按策略落盘，而不是立刻把脏数据页刷盘，这就是 Write-Ahead Logging。之后后台再把脏页刷回表空间。

当同时开启 binlog 时，MySQL 通过 redo log 与 binlog 的两阶段提交协调，尽量保证两份日志的一致性，避免主从复制与崩溃恢复出现已提交状态分歧。

什么是慢查询

执行时间超过 long_query_time 且满足记录条件的 SQL 会记入慢查询日志；它不必然等于业务“超时”。应结合调用频率、P95/P99 延迟、扫描行数、锁等待和资源占用判断优先级。

常见原因

• 缺少索引、索引选择性差或查询条件破坏索引使用；
• 扫描或返回数据量过大，如无约束分页、SELECT *；
• 多表 JOIN 顺序/关联列索引不合理，子查询或排序聚合代价高；
• 锁等待、长事务、事务隔离冲突；
• 统计信息陈旧，优化器选择错误计划；
• CPU、磁盘 I/O、内存、网络或连接池资源不足。

优化步骤

1. 采集证据：开启/分析慢日志，按总耗时、平均耗时和调用次数排序；关联应用 trace。
2. 复现与解释计划：使用 EXPLAIN ANALYZE，关注访问类型、实际行数与预估行数、索引、临时表、排序和循环次数。
3. 先减少工作量：只取必要列、添加合理过滤、限制结果集、避免深分页；大分页可用基于索引键的 seek/keyset pagination。
4. 优化索引与 SQL：依据 WHERE、JOIN、ORDER BY/GROUP BY 设计联合索引；保证关联列类型一致；避免对索引列函数计算。
5. 处理事务与锁：缩短事务、尽早提交、统一访问顺序，诊断阻塞链和死锁。
6. 最后考虑架构：读写分离、分区、分库分表、预聚合、缓存或硬件扩容；先验证瓶颈确实在数据库。

ORDER BY ... LIMIT 的提示

不要机械地“让排序表优先查”。重点是让过滤和排序尽量使用同一个合适索引，减少排序前的行数；必要时重写查询或建立覆盖/联合索引。每次改动都要用真实数据和执行计划验证。