开头想从一个真实的排查场景说起。前阵子帮某在线商城系统做慢查询分析发现一个用户订单列表接口在数据量突破千万级后响应时间从 200ms 直接飙到 3s 以上。当时我第一反应是“这 SQL 肯定没走索引”结果EXPLAIN一看索引用的好好的key列明明有值。后来翻到rows列才发现单次查询扫描了 80 多万行几乎等于全表扫描。这个案例特别典型不是索引没建而是建了没用好。2025 年这个节点MySQL 主流版本已经从 8.0 过渡到 8.4 LTS9.x 创新版本也在持续推进索引底层的实现逻辑没有天翻地覆的变化但优化器的行为、新特性函数索引、降序索引、隐藏索引的成熟度已经和五六年前有了明显差异。这篇文章我不会去抄官方文档而是把我在几个生产项目里踩过的坑、验证过的方案以及几条真正经得起数据量考验的索引技巧按实际排查的思路整理出来。内容更适合后端开发、DBA 以及所有需要自己优化 SQL 的读者直接照着实操能省下不少排查时间。1. 索引加了还失效慢查询背后最常见的六个坑很多人以为建了索引就万事大吉实际上 MySQL 的优化器没你想象的那么“智能”。它在决定走不走索引时要估算行数、回表成本、排序成本一旦发现索引扫描的成本高于全表扫描就会直接弃用索引。下面这六种情况是我在真实业务里反复遇到的几乎每次排查慢查询都能对上号。1.1 前导模糊查询与隐式字符编码转换先说模糊匹配。LIKE %关键词这种写法索引必然失效因为 B 树索引结构本身就是按照从左到右的字符顺序排列的你跳过第一个字符去匹配树无法按序定位。但LIKE 关键词%是可以走索引的这一点老生常谈实际操作中真正坑人的是“前后都带百分号”的需求。比如搜索商品名称包含“蓝牙耳机”的记录业务上无法避免%蓝牙耳机%此时正确做法是考虑全文索引FULLTEXT或者配合 ES 这类搜索引擎而不是硬扛 MySQL。再说隐式转换。最经典的是phone字段是VARCHAR但查询时传入了整数SELECT * FROM user WHERE phone 13800138000;MySQL 会把字段值转换成数字再比较导致phone列上的索引被隐式函数包裹索引失效。反过来如果字段是INT传入字符串则不会出问题因为优化器会尝试把字符串转成数字这属于可以接受的隐式转换。排查技巧很简单用SHOW WARNINGS查看 MySQL 改写后的 SQL看到CAST或CONVERT就说明触发了隐式转换。还有字符集不一致的问题。两个表关联查询时一张表utf8mb4、另一张表latin1关联列上的索引因为字符集转换被“包”了一层函数照样失效。1.2 函数包裹列与 OR 条件滥用对索引列做函数运算是让索引失效最彻底的方式。比如查询最近一周的订单SELECT * FROM orders WHERE DATE(create_time) 2025-01-10;DATE()函数把create_time这一列整个包裹起来B 树无法利用原有索引顺序。优化方案有两个方向一是改用范围查询create_time 2025-01-10 AND create_time 2025-01-11二是建立函数索引后面单独讲。顺便提一句很多人写日期条件时喜欢WHERE create_time BETWEEN 2025-01-10 00:00:00 AND 2025-01-10 23:59:59这个写法在 MySQL 8.0 里同样能有效利用索引但要注意BETWEEN下界默认包含、上界默认也包含如果时间精度高端点处理容易揩边出错。OR的问题也很好解释。WHERE a 1 OR b 2意味着 MySQL 要么分别走a和b的索引再合并结果要么退化成全表扫描。只有当两个条件列各自有索引且优化器评估index_merge成本更低时才会走索引合并。实际操作中我很少依赖index_merge更推荐把这种 SQL 拆成两个查询再合并或者改用UNION ALL因为OR场景下索引合并对优化器估算的稳定性要求太高数据分布一变就容易踩坑。1.3 范围查询后的条件对联合索引不友好这是联合索引里最隐蔽的坑。假设有一个联合索引(category_id, price, status)SELECT * FROM products WHERE category_id 3 AND price BETWEEN 10 AND 50 AND status 1;MySQL 在联合索引中按照“等值优先、范围其次、非等值条件无法继续精确匹配”的原则使用索引。category_id等值命中price范围命中但status无法继续在索引内部精确过滤。也就是说索引最多帮你定位到category_id3且price在区间内的所有记录status只能等回表后再过滤。这种情况优化思路是“把范围条件放最后”联合索引调整成(category_id, status, price)让两个等值条件先精确到位剩下的范围条件再扫描扫描的叶子节点数量会显著减少。下面整理了一份失效场景速查表方便日常排查时对照失效场景典型写法根因推荐处理方式前导模糊LIKE %abc无法按序定位节点全文索引或搜索服务隐式转换字符串列比较数值索引列被函数包裹应用层规范参数类型函数包裹DATE(create_time)...索引列被计算范围查询或函数索引OR 拼接a1 OR b2合并成本不确定拆查询或 UNION ALL范围后条件联合索引中范围后加过滤索引序无法继续匹配调整索引列顺序字符集不一致关联列字符集不同隐式转换统一字符集2. 回表为什么慢联合索引与覆盖索引的取舍逻辑索引失效只是问题的一面另一面是“索引生效了但依然慢”。这就要理解 MySQL 的索引组织方式完整的执行路径是通过 B 树定位到叶子节点拿到主键聚簇索引键再用主键去聚簇索引中回表查完整数据行。回表一次很快回表十万次就很致命。2.1 聚簇索引与非聚簇索引的存储差异InnoDB 中每张表只有一个聚簇索引一般是主键叶子节点直接存储整行数据其他索引称为二级索引叶子节点只存储索引列和主键值。所以二级索引查询天然要经历“索引定位 → 拿主键 → 回聚簇索引查全行”的过程。这个机制带来的直接推论是能用主键直接查询永远是最快的路径二级索引查询的行数越多回表成本越高。曾经有个报表统计需求要从一个两千万行的流水表里统计每天的交易总额原始 SQL 是SELECT trade_date, SUM(amount) FROM流水表 WHERE trade_date BETWEEN ... GROUP BY trade_date表上只有主键和trade_date的单列索引。分析后发现二级索引只存储trade_date和主键无法覆盖amount所以每一行匹配结果都要回表统计一次要执行上百万次回表操作。改造方式就是加联合索引(trade_date, amount)让查询在二级索引内部直接拿到所有数据extra 显示为Using index不再回表整条 SQL 的执行耗时从 4.8s 降到了 0.7s。2.2 覆盖索引的设计边界覆盖索引是指查询的所有字段都能从二级索引叶子节点直接获取不需要回表。它适合“高频、单表、字段有限”的查询场景。但覆盖索引不是越多越好——索引列越多写入时 B 树节点越庞大更新成本越高。设计时需要算一笔账查询频率是不是足够高值得用写入成本换查询收益覆盖字段长度是否可控TEXT、大VARCHAR不适合放进索引是否真的能覆盖查询列而不是只覆盖 WHERE 条件列。我习惯把高频列表查询的排序字段、筛选字段和返回字段放在一起推演比如订单列表页查询条件是user_id status create_time返回字段是order_no amount status那么联合索引(user_id, status, create_time, order_no, amount)就能把主查询彻底包住。注意不是越多越好amount这类定长数值加入索引收益明显但order_no如果是 32 位字符就要计算索引空间成本是否值得。2.3 索引条件下推ICP到底帮你省了什么MySQL 5.6 引入的索引条件下推Index Condition PushdownICP是个容易被人忽略的优化。没有 ICP 时二级索引范围扫描出的每一条记录都要立刻回表再在聚簇索引上做 WHERE 过滤启用 ICP 后可以在二级索引扫描过程中直接判断 WHERE 里的部分条件提前过滤不满足的记录减少回表次数。判断是否启用 ICP看EXPLAIN的Extra列是否出现Using index condition。不过要清醒一点ICP 只针对“索引列本身的条件下推”它并不能替代联合索引设计。比如联合索引(a, b)遇到WHERE a 100 AND b 2b2可以借助 ICP 在索引扫描阶段过滤但如果你改成联合索引(b, a)b2就能作为索引精确等值条件效率更高。ICP 是“补救机制”字段顺序设计才是根本。3. MySQL 8.0 以降的索引新特性哪些值得真正落地2025 年的实践视角绕不开 MySQL 8.0 系列和 8.4 LTS。很多团队还在沿用 5.7 时代的索引习惯但实际上新版本提供了不少能直接优化业务的功能这里挑三个我认为最值得落地的方向。3.1 降序索引别再手动造冗余字段5.7 里定义INDEX idx_create_time (create_time DESC)其实是不生效的索引内部仍然按升序存储反向扫描也能用但面对多列混合排序一列升序、一列降序时效率很差。MySQL 8.0 开始真正支持降序索引B 树内部按降序排列可以直接支持ORDER BY a ASC, b DESC这类混合排序避免文件排序filesort。我之前遇到过库存列表需要按“销量降序、上架时间升序”排序5.7 里查询计划始终带Using filesort后来升级 8.0 后建立(sales_volume DESC, online_time ASC)联合索引Filesort 直接消失。需要注意两点一是降序索引只对 InnoDB 有效MyISAM 依然老逻辑二是混合排序的两个方向索引列顺序要和 ORDER BY 顺序完全一致否则优化器依然要排序。3.2 函数索引解决“无法建索引”的最后一公里函数索引Functional Index本质上是把函数计算的结果存进索引列而不是对原列做运算。它的语法比较特殊需要用括号把表达式包起来CREATE INDEX idx_create_date ON orders ((DATE(create_time)));这种索引我不建议滥用但有两个场景非常值得一是按日期分组或精确匹配的查询替代范围查询写法二是 JSON 字段提取后的条件查询。比如日志表里按JSON_EXTRACT(payload, $.user_id)过滤记录没有函数索引时只能全表扫描有了函数索引后可以和普通索引一样走范围扫描。还要提醒一点函数索引的表达式必须在查询中保持精确一致稍有不同比如多套一层DATE_FORMAT就无法命中。3.3 隐藏索引给索引下线前的“灰度观察期”隐藏索引Invisible Index是 8.0 推出的运维利器。你可以把一个索引设置为对优化器不可见但 InnoDB 存储引擎仍然在维护它这样就能验证移除索引后 SQL 性能是否受影响给足观察期再真正DROP INDEX。ALTER TABLE orders ALTER INDEX idx_user_id INVISIBLE; -- 观察一段时间后 ALTER TABLE orders ALTER INDEX idx_user_id VISIBLE;大部分索引误删事故都能用这个功能避免。生产环境我建议凡是准备下线的索引先隐藏一到两周观察慢查询和监控指标确认没有波动再物理删除。3.4 版本选型对索引实践的影响MySQL 8.4 LTS 是当前最稳的长期支持版本8.0.x 系列偏向过渡版本9.x 是创新版本功能迭代快但推荐生产环境谨慎使用。由于成本原因很多云数据库厂商还大量提供 5.7 兼容实例如果你的项目还在 5.7上面说的降序索引、函数索引、隐藏索引都无法体验只能退回“冗余字段 定时更新”的模式如维护一个create_date列用来建立普通索引。特性5.78.0/8.4/9.x生产建议降序索引不支持真正生效混合排序场景用函数索引不支持支持JSON 提取或日期函数场景用隐藏索引不支持支持索引下线必备索引合并优化可用优化器调控更稳定不依赖能拆就拆4. 用 EXPLAIN 看懂 MySQL 的真实执行路径索引优化绕不开EXPLAIN。但很多开发者只知道看key有没有值忽略了几列真正的核心信息。我排查慢查询时重点看四个字段type、key_len、rows、Extra。4.1 type 字段的递进关系type是访问类型的直接体现从好到差大致是const主键或唯一索引等值查询最多返回一行速度最快eq_refJOIN 时被驱动表通过主键或唯一索引等值匹配常见但性能也很稳ref非唯一索引等值匹配可能有少量回表range索引范围扫描BETWEEN、IN、 等操作index扫描整个索引树比全表好一点但依然代价不小ALL全表扫描绝对需要警惕。我见过最容易被忽略的是ref和range之间的差距。同样是订单表按user_id查询如果user_id是普通索引2000 万行表里有 5000 行属于同一个用户typeref但rows5000回表 5000 次依然不慢但如果这个user_id区分度特别差比如 90% 记录都属于大客户rows可能到 20 万type再好看也没用。所以rows比type更有参考价值。4.2 key_len 的精细计算联合索引命中了几列key_len表示索引中使用到的字节数通过它可以看出联合索引到底有哪几列生效。以(category_id INT, price DECIMAL(10,2), status TINYINT)联合索引为例假设字段都允许为 NULLcategory_idINT4 字节 1 字节 NULL 标志 5priceDECIMAL(10,2)4 字节 1 字节 5statusTINYINT1 字节 1 字节 2。如果查询是等值category_id 3 AND status 1但没有price条件那么key_len只会显示 7 字节约 category 5 status 2说明price这一列在索引中没有被利用中间断了一层。这个数字变化在日常排查里特别有用能一眼看出联合索引列序是否设计合理。4.3 Extra 列的关键信号Using index覆盖索引生效不回表Using index conditionICP 生效部分条件下推到索引层Using where存储引擎返回后在服务层又做了过滤通常意味着索引条件不完整Using filesort需要额外排序一般是排序字段和索引顺序不一致Using temporary使用临时表常见于 GROUP BY 和 DISTINCT 场景。Using filesort是最常见的性能杀手。一旦出现优先去看看排序字段能不能被现成联合索引覆盖而不是先去调sort_buffer_size。我之前某导购系统的商品推荐接口就是ORDER BY score DESC, id DESC导致 800ms 的排序耗时改成联合索引(category_id, score DESC, id DESC)后排序耗时直接归零。5. 索引数量不是越多越好从维护成本到选择性的全局设计很多团队里有一种“堆索引病”哪个查询慢了就给那张表加索引半年下来单表二三十个索引写入越来越慢磁盘占用越来越高。索引本质上是拿空间换时间必须在全局维度做取舍。5.1 冗余索引的识别与清理冗余索引分两类一是完全重复的索引比如(a, b)和(a)后者其实被前者覆盖二是高度重叠的索引比如(a, b)和(a, c)如果两者同时存在(a)前缀被重复存储。用sys.schema_unused_indexes视图可以找出长时间未使用的索引再结合隐藏索引做下线验证。有一次我在某订单系统里清理出 6 个冗余索引表大小直接降了 12%写入耗时平均改善 8%。索引不是收藏品长期不用的索引就是纯负债。5.2 字段选择性的正确理解索引的区分度Cardinality决定索引的“性价比”。区分度计算方式是COUNT(DISTINCT col) / COUNT(*)越高越好。比如性别列区分度约 50%几乎不值得建索引订单状态列区分度可能在 90% 以上但要结合业务分布看如果 99% 的订单是“已完成”状态查询“已完成”订单时索引选择性依然很差。真正有代表性的案例是某系统里order_status有 10 万条“已完成”、200 条“处理中”查询处理中订单时走索引收益巨大查询已完成订单时优化器会直接选择全表扫描。所以判断一个字段要不要建索引不要只看区分度数值要结合最常见的查询值分布来看。5.3 前缀索引与字符串压缩对于超长字符串字段如URL、身份证号、邮箱地址全列索引会占用大量空间。MySQL 支持前缀索引只取前 N 个字符建立索引CREATE INDEX idx_email_prefix ON user (email(12));但前缀索引有个坑它无法用于ORDER BY和覆盖索引场景因为索引里存的是截断字符串。选择 N 的原则是SELECT COUNT(DISTINCT LEFT(email, N))尽量接近COUNT(DISTINCT email)的 90% 以上同时 N 尽量小。实际操作里我见过直接在完整字段上建索引导致索引体积膨胀一倍以上的但改为前缀索引后效果立竿见影。5.4 写多读少的表要克制索引不是免费的每次 INSERT、UPDATE、DELETE 都要同步维护索引树索引数量越多写入链路越长。对于日志表、流水表这类写多读少的冷数据表我通常只保留主键和必要的分区键索引其他索引一律不上对于热点查询的表反而要大胆做覆盖索引宁可多占点空间也要把查询耗时压下来。总体上单表单次写入维护的索引数量建议控制在 5 个以内超过这个线就要逐个审计索引的必要性。6. 写在最后我实际排查索引问题时的一些体会从最初的“看 key 有没有值”到后来习惯性看rows、key_len、Extra再到动手重建联合索引、引入函数索引这个过程里我最大的一个体会是慢 SQL 往往不是缺少索引而是索引设计与查询模式错配。每一次优化都应该先确认业务的高频查询路径再反向设计索引而不是遇到慢查询就堆一个新索引。另外一个很实用的习惯是每次建立新索引前先对比这个索引对当前查询的收益和对写入的影响。索引下线和上线一样需要回归验证尤其是大表上的ALTER TABLE ADD INDEX建议用在线 DDL 工具如pt-online-schema-change操作避免阻塞业务读写。最后说一个 SQL 写法的小技巧在有联合索引的场景里把等值条件、范围条件、排序字段依次排好顺序查询时按索引顺序去组织 WHERE 子句虽然现代优化器已经做了大量重写工作但这种写法能最大程度减少优化器“猜错”的概率。项目里每一次索引优化后记得把EXPLAIN结果和优化前后的执行耗时记录归档下次再遇到类似的慢查询就能直接翻出历史方案做对比少走很多弯路。