索引失效场景全梳理：从执行计划到最左前缀原则

在数据库优化中，索引是降低查询延迟最直接的手段，但索引并非创建后就能一直生效。许多开发者在字段上建立了索引，却依然看到几十毫秒甚至秒级的查询，根源在于隐式类型转换、函数运算、非最左匹配等操作破坏了索引结构的有序性。例如在 where 条件中对索引列使用 `DATE_FORMAT(create_time) = '2024-01-01'`，即使是 range 查询也会退化为全表扫描。同样，对字符串列与数值常量直接比较会导致隐式类型转换，使 MySQL 放弃索引。要准确判断索引是否被利用，必须借助 `EXPLAIN` 查看 type 列——从 system、const、ref、range 到 index、ALL，每一种扫描方式都对应不同的延迟量级。一个典型的优化场景是复合索引 `(user_id, status, create_time)`，如果查询条件只包含 `status` 和 `create_time`，则无法使用该索引，因为跳过了最左列。解决思路是调整索引列顺序或拆分为单列索引。实践中还应关注回表代价：即使 type 为 ref，若 select 的列不在索引覆盖范围内，每次命中都要回主表读取数据页，当命中行数超过阈值时，优化器可能放弃索引改为全表扫描。因此建议定期使用 `SHOW INDEX` 结合 cardinality 基数检查索引区分度，对于重复率超过 15% 的列，索引收益会大幅下降。最容易被忽略的是联合索引中的排序字段——如果 order by 字段不在索引中，MySQL 会使用 filesort，产生临时文件与额外 IO，延迟可能增加十倍。通过精简查询列、去除函数包裹、保持字段类型统一，再配合执行计划逐条验证，才能让索引真正成为低延迟的基石。

深分页性能陷阱与延迟消除实战

当业务表积累到千万级数据时，经典的 `LIMIT offset, size` 分页会随着页码增加而急剧恶化。原因在于数据库必须扫描并丢弃前 offset 行，再向后读取 size 行，offset 越大，无效扫描就越长。比如 `SELECT * FROM orders ORDER BY id LIMIT 1000000, 20` 可能耗时 2 秒以上，而查询前 20 条仅需 10 毫秒。深层原因是行定位与回表双重开销：排序、扫描、回表三个动作层层叠加。解决深分页延迟的核心思路是**延迟关联**与**游标分页**。延迟关联的做法是先利用覆盖索引快速定位起始主键，再与原表 join 取出完整数据，例如 `SELECT o.* FROM orders o JOIN (SELECT id FROM orders WHERE status=1 ORDER BY id LIMIT 1000000, 20) t ON o.id=t.id`，子查询只在辅助索引上扫描，避免回表。更理想的方案是游标分页——前端基于上一页最后一条记录的主键或时间戳，使用 `WHERE id > last_id ORDER BY id LIMIT 20`，此时数据库可命中等值或 range 索引，每页扫描固定 20 行，时间不会随页码增长而累积。对于不支持改前端的分页请求，可在后端缓存最大最后一页的 id 或使用批处理预取。此外，分页延迟常与排序字段不稳定有关，如果 order by 的字段不是唯一索引，相同值在不同页间会产生重复或遗漏，导致前端反复请求而无法缓存。建议强制加入唯一主键作为次级排序条件，确保排序结果稳定。值得注意的是，深分页也常因 `WHERE` 条件缺乏索引关联而全表扫描，此时先针对过滤字段建立复合索引，再用上述策略，延迟可从秒级降至毫秒级。

缓存策略与查询改写：让重复查询延迟趋近于零

即使索引优化到位，热点数据的高频重复查询仍然会压垮数据库。例如商品详情、用户配置等 QPS 过万的只读请求，每次命中 MySQL 都会产生解析、计划、锁与网络开销。引入缓存并非简单加一个 Redis，而是要区分**缓存什么、缓存多久、如何失效**。最直接的方式是在应用层维护本地缓存（如 Caffeine）或分布式缓存，以“查询条件+版本号”为 key，value 存放序列化结果。在数据库层面，MySQL 自带查询缓存已在 8.0 移除，说明缓存策略必须前移。实际案例中，某订单接口延迟从 80ms 下降至 2ms，就是因为在 service 层缓存了用户最近 20 条订单 ID 列表，再通过批量主键查询填充完整数据。另一种有效的查询改写是将多个单次查询合并为一次批量查询，用 `IN` 替换多个 `=` 查询，减少网络往返。但 `IN` 列表也有限制——当列表超过 500 项时，索引扫描会退化为多次 eq_ref，且复制给优化器的代价较大，建议分段并发。对于统计类慢查询，可预先运行聚合任务并存储结果，而不是实时 `COUNT(*)`。缓存失效策略上，延迟极低的业务优先采用主动失效：在写操作后通过消息队列删除相关缓存，而不是依赖短 TTL 导致的缓存击穿。若无法做到主动失效，则需引入布隆过滤器拦截不存在数据的恶意查询，防止穿透到数据库。更进阶的缓存方案是使用读写分离的 MySQL 从库作为“二级缓存”，将报表、搜索等只读业务路由到从库，减少主库压力；同时开启慢查询日志定期分析，找出本应命中缓存却未命中的 SQL，补充缓存 key。最终目标是让核心查询的 P99 延迟低于 20ms，并将数据库 IOPS 占用控制在一个较低水位。

从单机到分布式：读写分离与分库分表的延迟代价

当单库无法支撑流量时，常见的扩展手段是读写分离与分库分表，但两种方案并不总是降低延迟，如果设计不当，反而带来更高的网络开销与数据一致性延迟。读写分离通过一主多从复制，将读请求分摊到从库，减少主库锁竞争。但主从复制存在天然延迟，通常为毫秒级到秒级，若刚写入的数据立刻被读取，可能因从库尚未同步而返回旧数据。解决方法是设置“主库路由标记”——对刚执行写操作的会话在一段时间内强制走主库，或等待安全时间后再读。更关键的是，读写分离必须配合连接池与负载均衡策略，将长事务与复杂报表单独隔离，否则一个慢查询会占满从库连接，殃及普通查询。分库分表则采用分片键对数据水平拆分，看似分摊了单表压力，却引入了跨库 join、分布式事务和聚合影响。一个订单表按 user_id 分片后，若运营后台按 `order_no` 查询，则不得不遍历所有分片，延迟随分片数量成倍增长。因此分片键的选择决定了绝大多数核心查询的路由范围。可采用双写冗余表或分片索引表来满足非分片键查询。在跨分片分页中，原单库的 `ORDER BY create_time LIMIT 10` 要求每个分片各自取前 10 条，再归并排序取最终 10 条，这里总扫描记录数为分片数×10，如果分片很多则成本上升。一种实践是放弃全量精确分页，仅支持上一页游标，例如按 create_time 全局唯一序列号配合二级索引，将归并操作限制在少量数据上。同时，分布式环境下应不再依赖数据库自增主键，使用雪花算法等生成全局有序 ID，可明显提升插入与范围查询效率。引入 ShardingSphere 或 MyCat 后，务必监控聚合节点的 CPU 与内存，因为归并排序与 in 查询改写会增加额外延迟。最后，对于跨片聚合的低频报表，建议通过离线数仓或 ETL 同步到分析型数据库执行，避免在线业务被沉重的 group by 拖垮。从单机到分布式并非一条直线，每种架构选择都会产生新的延迟因子，必须在容量预估与业务读模式间反复权衡，并通过压测得出各分片链路下的真实 P99 值。