数据库索引与慢查询优化实战从EXPLAIN到亿级数据调优引言系统上线初期一张表几千条数据SQL怎么写都飞快。等到数据涨到千万、亿级一个看似人畜无害的SELECT ... WHERE create_time ? ORDER BY id DESC LIMIT 20可能直接把数据库 CPU 打到 100%接口 RT 从 20ms 飙升到 8 秒。慢查询不是写得不对而是索引没站对位置。绝大多数线上性能问题根因都在索引设计该建的没建、建了的没用上、用上的却选错了。本文将从一个真实慢查询案例出发系统讲透索引的底层原理、EXPLAIN 怎么读、常见索引失效场景以及亿级数据下的调优实战。一、索引的本质用空间换时间索引Index本质上是一棵BTree。它把全表逐行扫描变成在有序结构中二分定位把 O(N) 降到 O(logN)。核心认知索引不是越多越好——每个索引都会拖慢写入INSERT/UPDATE/DELETE 要维护索引树。索引是为查询模式服务的不是为表结构服务的。先有查询后有索引。主键索引聚簇索引叶子节点存整行数据二级索引叶子节点存主键值回表才能拿到完整行。二、EXPLAIN慢查询的第一现场拿到一条慢 SQL第一步永远是EXPLAIN不要靠猜。EXPLAINSELECT*FROMordersWHEREuser_id10086ANDstatus1ORDERBYcreate_timeDESCLIMIT20;重点看这几列字段含义关注点type访问类型从好到差system const eq_ref ref range index ALL。出现 ALL 就是全表扫描必须重视key实际用到的索引为 NULL 说明没走索引rows预估扫描行数越大越慢Extra额外信息Using filesort额外排序、Using temporary用临时表、Using where回表过滤都是危险信号经验法则线上核心查询的type至少要到range最好到ref及以上Extra里尽量不要出现Using filesort和Using temporary。三、联合索引与最左前缀80% 的索引问题出在联合索引的使用上。-- 建立联合索引ALTERTABLEordersADDINDEXidx_user_status_time(user_id,status,create_time);最左前缀原则索引(user_id, status, create_time)能加速以下查询WHERE user_id ?用到了第 1 列WHERE user_id ? AND status ?用到了第 1、2 列WHERE user_id ? AND status ? AND create_time ?三列全用且create_time可用于范围排序但以下查询用不到或只用部分索引WHERE status ?跳过最左列 user_id全表扫描WHERE user_id ? AND create_time ?用到 user_id但 status 缺失create_time 无法参与索引过滤只能用于排序关键技巧 —— 索引列顺序等值条件列放前面user_id、status 是放前面。范围条件列放后面create_time 是放最后避免阻断后续列。ORDER BY 的列紧跟范围列可避免Using filesort。四、那些让索引失效的坑索引建对了但查询写错了索引依然用不上。4.1 对索引列做函数/运算-- 失效对 create_time 包了函数SELECT*FROMordersWHEREDATE(create_time)2026-08-21;-- 改写范围查询索引可用SELECT*FROMordersWHEREcreate_time2026-08-21 00:00:00ANDcreate_time2026-08-22 00:00:00;4.2 隐式类型转换-- user_id 是 BIGINT传入字符串会触发隐式转换索引失效SELECT*FROMordersWHEREuser_id10086;-- 正确保持类型一致SELECT*FROMordersWHEREuser_id10086;4.3 前导模糊查询-- 失效LIKE 以 % 开头无法走 BTree 有序性SELECT*FROMarticlesWHEREtitleLIKE%架构实战%;-- 仅后缀模糊可用索引SELECT*FROMarticlesWHEREtitleLIKE架构实战%;全文检索需求应交给 Elasticsearch不要把%keyword%压在 MySQL 上。4.4 OR 连接非索引列WHERE a 1 OR b 2如果 b 无索引整条查询很可能放弃索引走全表。可用UNION ALL拆分或给 b 也建索引。五、回表与覆盖索引-- 二级索引只存 (user_id, status, create_time, id)-- 但 SELECT * 需要取其他列 → 回表性能损耗SELECT*FROMordersWHEREuser_id10086ANDstatus1ORDERBYcreate_timeDESCLIMIT20;覆盖索引Covering Index让索引本身包含查询所需的所有列避免回表。-- 只取索引里已有的列无需回表SELECTid,user_id,status,create_timeFROMordersWHEREuser_id10086ANDstatus1ORDERBYcreate_timeDESCLIMIT20;当查询列较多时可以适度把高频查询列加入联合索引末尾以空间换回表开销但要权衡写入成本。覆盖索引是亿级表优化 RT 的杀手锏。六、亿级数据调优实战6.1 场景订单表 3 亿行按用户查最近订单慢-- 原始慢 SQL全表扫描RT 8sSELECT*FROMordersWHEREuser_id?ORDERBYidDESCLIMIT20;优化步骤EXPLAIN确认typeALL、rows≈3亿。建联合索引(user_id, id)id 是主键DESC 排序可直接走索引顺序。改写SELECT仅取必要列 → 覆盖索引消除回表。复测typerefrows≈20RT 降到 15ms。6.2 冷热分离亿级表里 90% 的查询只涉及最近 3 个月数据。把历史数据归档到orders_history按年分表主表只留热数据索引体积和查询成本同时下降。-- 按时间分表orders_2026_q1/orders_2026_q2/...-- 或用 MySQL 8.0 分区表按 RANGE COLUMNS(create_time)6.3 深分页优化-- 灾难LIMIT 1000000, 20 要扫描 100 万行SELECT*FROMordersORDERBYidDESCLIMIT1000000,20;-- 游标分页用上一页最后一条的 id 做起点SELECT*FROMordersWHEREid?ORDERBYidDESCLIMIT20;游标分页seek method把 O(N) 扫描变成 O(20) 定位是列表页翻页的正确姿势。七、索引治理的常态化机制慢查询日志常态化开启slow_query_loglong_query_time1s每天review Top 10 慢 SQL。生产索引评审新加查询字段前先评估是否需要新索引避免上线即慢。冗余索引清理用sys.schema_unused_indexesMySQL 8.0找出从不使用的索引删掉减负写入。定期 ANALYZE TABLE统计信息过期会导致优化器选错索引定期更新。八、总结慢查询优化不是玄学是有方法论的工程先 EXPLAIN再动手——一切以执行计划为准不靠直觉。联合索引按等值在前、范围在后、排序收尾排吃透最左前缀。避开失效陷阱函数运算、隐式转换、前导模糊、OR 非索引列。覆盖索引消回表是亿级表降 RT 的关键。冷热分离 游标分页让大数据量也能保持毫秒级响应。一个真实的教训曾经一条WHERE DATE(create_time)?的慢 SQL 把主库 CPU 打满整个下单链路雪崩。改成范围查询 建对联合索引后RT 从 6 秒降到 30 毫秒。问题不在于 MySQL 不行而在于我们没把索引摆对位置。参考资料《高性能 MySQL》- Baron Schwartz 等索引与 EXPLAIN 权威指南MySQL 8.0 Reference Manual - EXPLAIN Output Format《数据库索引设计与优化》- Tapio Lahdenmäki《Designing Data-Intensive Applications》- Martin Kleppmann作者架构实战团队日期2026-08-21标签#数据库索引 #慢查询优化 #EXPLAIN #联合索引 #覆盖索引 #MySQL #性能调优