尧图网站建设 
在MySQL的日常使用里索引大概是最容易被低估的东西。我刚接触数据库时也以为建索引就是把几个字段加上直到线上一条本来毫秒级的查询突然变成几百毫秒甚至秒级才真正意识到索引设计不是简单的“加个B树索引”就能了事。这篇文章我会把索引从原理到优化的关键环节都串起来结合我实际排查慢SQL的经验说清楚什么时候该建索引、什么时候索引会失效、怎么用EXPLAIN判断问题以及深分页、排序、覆盖索引这些高频场景到底该怎么处理。无论你是刚入门的学生还是写了好几年业务代码的开发这篇文章都能给你一套可落地的排查思路。1. 索引不是万能的先搞清楚它到底解决了什么问题1.1 一条慢SQL引发的排查B树为什么能快有一次线上告警某个订单查询接口平均耗时从30ms涨到800ms查了下慢日志发现是这么一条语句SELECT id, order_no, user_id, amount FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;orders表当时数据量已经到2000万行而user_id这一列根本没有索引。在没有索引的情况下MySQL只能对整张表做全表扫描逐行比对user_id再把命中的结果放入临时文件排序最后才取20条返回。2000万行数据全部扫描一遍再排序时间自然就上去了。加了一个普通索引之后问题立刻缓解ALTER TABLE orders ADD INDEX idx_user_id (user_id);添加索引后查询先通过B树定位到user_id为12345的所有记录。B树的查找复杂度是O(log N)级别2000万行数据大概也就二十几次磁盘I/O就能定位到目标。如果索引叶子节点上还包含了create_time那排序也能省掉。这就是索引最核心的作用把全表遍历变成树上的快速定位顺便还能辅助排序和分组。1.2 索引选型背后的取舍索引不是建得越多越好。每个索引在写入数据时都要额外维护一棵B树插入、更新、删除的成本都会增加。而且索引还占用磁盘空间InnoDB里每一个二级索引都是一套完整的B树结构数据量大了以后空间开销非常明显。真正到生产环境我一般遵循几个原则区分度高、经常出现在WHERE条件里的列优先考虑索引出现在ORDER BY、GROUP BY、DISTINCT子句里的列也值得加索引因为B树本身有序可以直接利用这个有序性避免额外排序频繁更新的字段要谨慎加索引写多读少的表索引带来的查询提速可能远小于写入代价区分度极低的列比如性别、状态这类只有两三个枚举值的字段单独建索引几乎没有意义优化器可能直接放弃走索引所以索引设计本质上是个权衡问题。核心不是“建了多少个索引”而是“每个查询有没有合适的索引可用”。这个下一节具体展开。2. 索引设计的几个关键决策主键、联合索引与覆盖索引2.1 主键索引的正确打开方式InnoDB是聚簇索引组织表数据行本身存储在聚簇索引的叶子节点上聚簇索引的键就是主键。表没有显式主键时InnoDB会找第一个非空的唯一索引来当聚簇索引找不到就自动生成一个隐藏的rowid也就是内部6字节的自增列。所以建议每个InnoDB表都显式定义一个主键。主键的选择优先顺序依次是自增整型主键、业务天然唯一的整型字段、UUID不太推荐。如果用自增主键新插入的数据总是在B树的末尾追加维护成本很低。反过来如果用UUID这类随机字符串做聚簇索引每次插入都需要在B树中间某位置执行节点分裂页分裂带来额外的碎片和写放大时间长了表的性能和空间利用率都会明显下降。二级索引的叶子节点存储的并不是数据行本身而是主键值。这意味着通过二级索引查询时要先在二级索引的B树里找到主键再拿着主键回聚簇索引去查完整数据行。这个过程叫回表。回表本身有一次额外的磁盘I/O查询结果集很大时回表次数也会很多这时就要考虑覆盖索引。2.2 联合索引和失效场景联合索引是优化查询频率很高的复合条件。联合索引的排列原则是“最左前缀”MySQL会从联合索引的最左列开始匹配中间不能断。比如建了(a, b, c)联合索引查询条件里如果只有b而没有a这个索引就完全用不上。另一个容易被忽视的问题是范围查询会中断后续字段的索引匹配。WHERE a 1 AND b 100 AND c 5在联合索引(a, b, c)中a可以用来精确定位b用来范围扫描但c就不能继续利用索引了因为b的范围结果集里c是无序的。所以建联合索引时通常把等值条件的列放在前面范围条件的列放在后面。还有一个很典型的设计方式叫覆盖索引。覆盖索引是指查询需要的所有列都在索引里不需要回表。比如有一条高频查询SELECT user_id, status FROM orders WHERE user_id 123;如果只建idx_user_id(user_id)查询到user_id之后还要回表拿status。但建了idx_user_id_status(user_id, status)联合索引后索引叶子节点上已经有了status值优化器看到需要的列都在索引里就会直接走索引返回结果连回表都省了。对于大表和高频查询覆盖索引往往比单纯加一个单列索引收益大很多。3. 用EXPLAIN看懂MySQL的执行计划3.1 explain关键字段速查遇到慢SQL我第一步永远是执行EXPLAIN看执行计划里MySQL到底怎么跑这条语句的。这里分享一个我常用的字段速查表字段含义重点关注type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL出现ALL说明全表扫描基本可以判断索引失效或没建对possible_keys可能用到的索引列表看优化器考虑了哪些索引key实际选用的索引为空说明没走索引key_len使用索引字节数值越大一般说明用的索引字段越完整rows预计扫描的行数数值越小越好Extra额外信息出现filesort、temporary、Using index等关键字需要注意type字段是最直观的判断依据。至少要保证range级别最好能到ref或const。如果看到ALL扫描而且表本身很大那这条SQL基本就是需要优化的目标了。3.2 从type级别识别慢查询我实际排查过一条查询EXPLAIN结果里type是ALLrows预估60多万行。表其实只有几万行但由于WHERE条件里对索引列做了函数操作索引就失效了。SELECT * FROM users WHERE DATE(create_time) 2024-06-01;create_time字段本身有索引但DATE(create_time)套了函数之后索引列被改变优化器无法按原有顺序去B树查找只能放弃索引做全表扫描。改成范围查询之后SELECT * FROM users WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;这一步直接让type从ALL变成了rangerows从60万降到了几百。执行计划里的type变化是最直观的优化效果验证方式完全不需要去猜。再补充一个坑隐式类型转换也会导致索引失效。常见的情况是有一个varchar类型的字段phone查询时条件写成WHERE phone 13800138000MySQL会尝试把字符串转成数字去比较相当于在索引列上做了隐式操作索引照常失效。解决办法很粗暴查询参数跟字段类型保持一致或者用WHERE phone 13800138000。4. 常见慢SQL优化实战案例拆解4.1 深分页优化LIMIT 100000, 20为什么越翻越慢业务后台常见的分页接口数据量一大就会出现“翻到后面几页明显变卡”的现象SELECT id, user_id, amount, status FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 100000, 20;这条SQL的痛点是LIMIT 100000, 20需要先把前100000行取出来然后丢弃掉只返回最后20行。即使走了主键索引这100000次回表一次都跑不掉越到后面代价越大。一个比较常见的优化方案是延迟关联先利用覆盖索引快速查到目标主键ID再用主键ID去关联回原表拿整行数据。SELECT o.id, o.user_id, o.amount, o.status FROM ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 100000, 20 ) t JOIN orders o ON t.id o.id;子查询里只查id列可以在覆盖索引上完成排序和分页避免大量回表。外层再用JOIN把只需要的20行完整数据取回来。这个方案在分页深度比较大的场景里实测提速非常明显。如果业务场景允许还可以用游标分页代替偏移分页。也就是前端记录最后一条的id下一页查询带上WHERE id 上次最大id配合ORDER BY id LIMIT 20。这种方式没有offset的无效扫描数据量再大也能稳定在毫秒级。代价是用户不能直接跳到任意页码更适合滚动加载类的场景。4.2 排序导致的filesort为什么明明走了索引还是慢还有一类慢SQL走索引了但EXPLAIN的Extra里出现Using filesort排序没有完全依赖索引的有序性MySQL需要在内存或磁盘上自己做排序。数据量大时这个排序过程可能比查询本身还慢。之前优化过一个订单列表接口条件语句是SELECT order_no, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 50;初始索引只有idx_status(status)MySQL先通过status筛出数据再对create_time排序。因为id并非连续不能直接利用主键顺序于是触发了filesort。优化方案是建联合索引idx_status_create_time(status, create_time)让status等值过滤后create_time天然就是有序的排序直接省掉Extra里的filesort也消失了。这里有一个细节联合索引设计的“等值条件放前面排序字段放后面”原则在这个场景就体现出来了。如果你反过来把create_time放前面status放后面那WHERE status 1的时候就匹配不到这个联合索引索引直接废掉。4.3 前缀索引与索引下推对于超长文本字段比如URL、备注信息这类直接对整个字段建索引会导致索引体积膨胀而且索引树里的每一条都特别长。MySQL支持前缀索引比如ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));只索引字段前64个字符。好处是索引体积小、建树快缺点是有一定概率出现前缀相同导致额外回表判断。具体取多少长度可以通过统计区分度来决定SELECT COUNT(DISTINCT LEFT(url, 64)) / COUNT(*) AS selectivity FROM articles;区分度接近1说明64个字符基本能代表整列。再提一个很多人不知道的优化点索引下推英文是Index Condition Pushdown简称ICP。以前是WHERE条件里有索引覆盖不到的列时必须在回表之后再过滤。开启ICP后MySQL会在索引遍历过程中先把索引包含的列做一遍条件判断只有满足条件的才回表。这个特性在MySQL 5.6之后默认开启配合联合索引效果尤其明显。如果你的MySQL版本比较旧还是建议升级ICP对特定SQL的性能提升是实打实的。5. 索引维护与避坑回表、统计信息与索引失效的其他场景5.1 为什么建了索引还是很慢经常有这样的情况索引建了EXPLAIN也显示走了索引但查询还是慢。第一个怀疑方向是回表次数太多。比如一个查询命中了索引范围rows预估100万那就要回表拿100万次数据这种情况下即使走索引代价也很高。解决办法是把查询需要的列尽可能都放到二级索引里形成覆盖索引。如果无法覆盖就要考虑改变查询条件缩小命中范围比如在时间维度上加过滤条件。第二个要排查的是索引统计信息过期。优化器决定是否走索引时依赖表的统计信息来估算扫描行数。如果表数据变化剧烈而统计信息没更新优化器可能对一个明明有索引的查询选择全表扫描。通常跑一次ANALYZE TABLE让优化器重新统计即可。第三个容易被忽略的是表碎片。频繁的删除和更新会让InnoDB表产生大量碎片导致索引页利用率下降扫描相同行数的代价变大。OPTIMIZE TABLE可以重建表、整理碎片但这个操作会锁表生产环境要选在低峰期执行或者用gh-ost、pt-online-schema-change这类在线工具来操作。5.2 索引失效的其他隐藏场景除了前面提到的函数操作和隐式类型转换还有几个我实际踩过的坑使用不等于条件比如WHERE status ! 1多数情况下MySQL无法用索引快速定位因为B树本来就是按等值和范围来设计的使用LIKE %-关键字%这样以通配符开头的模糊查询最左前缀匹配原则会失效LIKE 关键字%这种以固定字符串开头的则可以用索引OR条件中的一个分支没有索引优化器可能放弃整个查询的索引联合索引但没遵循最左前缀法则比如索引是(a, b)WHERE只查b这些都是执行计划里type变成ALL或索引使用不充分的直接原因。我排查问题的习惯是拿到一条慢SQL第一步看表结构和已有索引第二步EXPLAIN看执行计划第三步根据执行计划反推是索引缺失、失效还是索引设计不合理然后针对性地改。6. 常见问题速查与日常工作建议汇总6.1 常见问题速查表现象可能原因解决方案EXPLAIN显示type为ALL没索引或索引失效检查WHERE条件列是否可建索引确认没有函数、隐式转换走了索引但Extra有filesort排序字段不在索引里建联合索引把排序字段放在等值字段之后翻页越深越慢LIMIT offset过大导致大量回表延迟关联或游标分页明明有索引还是全表扫描统计信息过期或命中行数太大ANALYZE TABLE检查区分度考虑覆盖索引联合索引某个字段没效果查询条件未遵循最左前缀法则调整查询条件或重新设计联合索引表数据量不大但查询很慢可能存在死锁或表锁SHOW ENGINE INNODB STATUS查看锁等待写入速度越来越慢索引过多或表碎片多评估无用索引定期整理碎片6.2 我日常维护索引的几点经验索引优化不是一次性工作而是伴随表结构和业务变化的持续过程。我一般在每个大版本上线前做一次索引评审把慢查询日志里的SQL拉出来逐一核对执行计划。用Percona Toolkit里的pt-query-digest分析慢日志也很方便它可以按执行次数和时间消耗排序帮我快速锁定最值得优化的SQL。有一个细节值得说新增索引尽量用在线DDL方式避免长时间锁表。MySQL 5.6之后InnoDB支持在线DDL执行ALTER TABLE ADD INDEX时通常会允许并发DML继续执行但具体还取决于算法和锁级别大批量数据操作前最好先在测试环境评估影响。删除无用索引也要注意。我见过一个表上有7个索引其中两个单列索引完全被新的联合索引覆盖属于冗余索引。这种冗余不仅浪费空间还白白增加每次写入的维护成本。通过sys.schema_unused_indexes视图可以查到哪些索引从未被使用过再结合慢日志确认就可以安全删除了。另外MySQL 8.0提供了不可见索引和函数索引两个实用特性。不可见索引可以让你在不删除索引的情况下测试“没有这个索引时优化器会怎么走”风险非常低。函数索引则直接解决对字段做函数操作导致索引失效的问题可以评估升级到8.0的价值。最后想说的是索引优化的核心从来不是背诵多少规则而是理解数据结构和执行计划。B树解决了有序存储与高效查询的矛盾理解了这一点很多索引设计原则都不用死记。真正遇到慢SQL的时候拿着EXPLAIN一步步看type、rows和Extra结合表的数据特征和业务场景自然就知道该怎么改。希望这篇内容能帮你少走一些弯路。 MySQL索引优化实战：从B+树原理到慢SQL排查与设计避坑
在MySQL的日常使用里索引大概是最容易被低估的东西。我刚接触数据库时也以为建索引就是把几个字段加上直到线上一条本来毫秒级的查询突然变成几百毫秒甚至秒级才真正意识到索引设计不是简单的“加个B树索引”就能了事。这篇文章我会把索引从原理到优化的关键环节都串起来结合我实际排查慢SQL的经验说清楚什么时候该建索引、什么时候索引会失效、怎么用EXPLAIN判断问题以及深分页、排序、覆盖索引这些高频场景到底该怎么处理。无论你是刚入门的学生还是写了好几年业务代码的开发这篇文章都能给你一套可落地的排查思路。1. 索引不是万能的先搞清楚它到底解决了什么问题1.1 一条慢SQL引发的排查B树为什么能快有一次线上告警某个订单查询接口平均耗时从30ms涨到800ms查了下慢日志发现是这么一条语句SELECT id, order_no, user_id, amount FROM orders WHERE user_id 12345 ORDER BY create_time DESC LIMIT 20;orders表当时数据量已经到2000万行而user_id这一列根本没有索引。在没有索引的情况下MySQL只能对整张表做全表扫描逐行比对user_id再把命中的结果放入临时文件排序最后才取20条返回。2000万行数据全部扫描一遍再排序时间自然就上去了。加了一个普通索引之后问题立刻缓解ALTER TABLE orders ADD INDEX idx_user_id (user_id);添加索引后查询先通过B树定位到user_id为12345的所有记录。B树的查找复杂度是O(log N)级别2000万行数据大概也就二十几次磁盘I/O就能定位到目标。如果索引叶子节点上还包含了create_time那排序也能省掉。这就是索引最核心的作用把全表遍历变成树上的快速定位顺便还能辅助排序和分组。1.2 索引选型背后的取舍索引不是建得越多越好。每个索引在写入数据时都要额外维护一棵B树插入、更新、删除的成本都会增加。而且索引还占用磁盘空间InnoDB里每一个二级索引都是一套完整的B树结构数据量大了以后空间开销非常明显。真正到生产环境我一般遵循几个原则区分度高、经常出现在WHERE条件里的列优先考虑索引出现在ORDER BY、GROUP BY、DISTINCT子句里的列也值得加索引因为B树本身有序可以直接利用这个有序性避免额外排序频繁更新的字段要谨慎加索引写多读少的表索引带来的查询提速可能远小于写入代价区分度极低的列比如性别、状态这类只有两三个枚举值的字段单独建索引几乎没有意义优化器可能直接放弃走索引所以索引设计本质上是个权衡问题。核心不是“建了多少个索引”而是“每个查询有没有合适的索引可用”。这个下一节具体展开。2. 索引设计的几个关键决策主键、联合索引与覆盖索引2.1 主键索引的正确打开方式InnoDB是聚簇索引组织表数据行本身存储在聚簇索引的叶子节点上聚簇索引的键就是主键。表没有显式主键时InnoDB会找第一个非空的唯一索引来当聚簇索引找不到就自动生成一个隐藏的rowid也就是内部6字节的自增列。所以建议每个InnoDB表都显式定义一个主键。主键的选择优先顺序依次是自增整型主键、业务天然唯一的整型字段、UUID不太推荐。如果用自增主键新插入的数据总是在B树的末尾追加维护成本很低。反过来如果用UUID这类随机字符串做聚簇索引每次插入都需要在B树中间某位置执行节点分裂页分裂带来额外的碎片和写放大时间长了表的性能和空间利用率都会明显下降。二级索引的叶子节点存储的并不是数据行本身而是主键值。这意味着通过二级索引查询时要先在二级索引的B树里找到主键再拿着主键回聚簇索引去查完整数据行。这个过程叫回表。回表本身有一次额外的磁盘I/O查询结果集很大时回表次数也会很多这时就要考虑覆盖索引。2.2 联合索引和失效场景联合索引是优化查询频率很高的复合条件。联合索引的排列原则是“最左前缀”MySQL会从联合索引的最左列开始匹配中间不能断。比如建了(a, b, c)联合索引查询条件里如果只有b而没有a这个索引就完全用不上。另一个容易被忽视的问题是范围查询会中断后续字段的索引匹配。WHERE a 1 AND b 100 AND c 5在联合索引(a, b, c)中a可以用来精确定位b用来范围扫描但c就不能继续利用索引了因为b的范围结果集里c是无序的。所以建联合索引时通常把等值条件的列放在前面范围条件的列放在后面。还有一个很典型的设计方式叫覆盖索引。覆盖索引是指查询需要的所有列都在索引里不需要回表。比如有一条高频查询SELECT user_id, status FROM orders WHERE user_id 123;如果只建idx_user_id(user_id)查询到user_id之后还要回表拿status。但建了idx_user_id_status(user_id, status)联合索引后索引叶子节点上已经有了status值优化器看到需要的列都在索引里就会直接走索引返回结果连回表都省了。对于大表和高频查询覆盖索引往往比单纯加一个单列索引收益大很多。3. 用EXPLAIN看懂MySQL的执行计划3.1 explain关键字段速查遇到慢SQL我第一步永远是执行EXPLAIN看执行计划里MySQL到底怎么跑这条语句的。这里分享一个我常用的字段速查表字段含义重点关注type访问类型从好到差依次是system、const、eq_ref、ref、range、index、ALL出现ALL说明全表扫描基本可以判断索引失效或没建对possible_keys可能用到的索引列表看优化器考虑了哪些索引key实际选用的索引为空说明没走索引key_len使用索引字节数值越大一般说明用的索引字段越完整rows预计扫描的行数数值越小越好Extra额外信息出现filesort、temporary、Using index等关键字需要注意type字段是最直观的判断依据。至少要保证range级别最好能到ref或const。如果看到ALL扫描而且表本身很大那这条SQL基本就是需要优化的目标了。3.2 从type级别识别慢查询我实际排查过一条查询EXPLAIN结果里type是ALLrows预估60多万行。表其实只有几万行但由于WHERE条件里对索引列做了函数操作索引就失效了。SELECT * FROM users WHERE DATE(create_time) 2024-06-01;create_time字段本身有索引但DATE(create_time)套了函数之后索引列被改变优化器无法按原有顺序去B树查找只能放弃索引做全表扫描。改成范围查询之后SELECT * FROM users WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;这一步直接让type从ALL变成了rangerows从60万降到了几百。执行计划里的type变化是最直观的优化效果验证方式完全不需要去猜。再补充一个坑隐式类型转换也会导致索引失效。常见的情况是有一个varchar类型的字段phone查询时条件写成WHERE phone 13800138000MySQL会尝试把字符串转成数字去比较相当于在索引列上做了隐式操作索引照常失效。解决办法很粗暴查询参数跟字段类型保持一致或者用WHERE phone 13800138000。4. 常见慢SQL优化实战案例拆解4.1 深分页优化LIMIT 100000, 20为什么越翻越慢业务后台常见的分页接口数据量一大就会出现“翻到后面几页明显变卡”的现象SELECT id, user_id, amount, status FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 100000, 20;这条SQL的痛点是LIMIT 100000, 20需要先把前100000行取出来然后丢弃掉只返回最后20行。即使走了主键索引这100000次回表一次都跑不掉越到后面代价越大。一个比较常见的优化方案是延迟关联先利用覆盖索引快速查到目标主键ID再用主键ID去关联回原表拿整行数据。SELECT o.id, o.user_id, o.amount, o.status FROM ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY id LIMIT 100000, 20 ) t JOIN orders o ON t.id o.id;子查询里只查id列可以在覆盖索引上完成排序和分页避免大量回表。外层再用JOIN把只需要的20行完整数据取回来。这个方案在分页深度比较大的场景里实测提速非常明显。如果业务场景允许还可以用游标分页代替偏移分页。也就是前端记录最后一条的id下一页查询带上WHERE id 上次最大id配合ORDER BY id LIMIT 20。这种方式没有offset的无效扫描数据量再大也能稳定在毫秒级。代价是用户不能直接跳到任意页码更适合滚动加载类的场景。4.2 排序导致的filesort为什么明明走了索引还是慢还有一类慢SQL走索引了但EXPLAIN的Extra里出现Using filesort排序没有完全依赖索引的有序性MySQL需要在内存或磁盘上自己做排序。数据量大时这个排序过程可能比查询本身还慢。之前优化过一个订单列表接口条件语句是SELECT order_no, amount FROM orders WHERE status 1 ORDER BY create_time DESC LIMIT 50;初始索引只有idx_status(status)MySQL先通过status筛出数据再对create_time排序。因为id并非连续不能直接利用主键顺序于是触发了filesort。优化方案是建联合索引idx_status_create_time(status, create_time)让status等值过滤后create_time天然就是有序的排序直接省掉Extra里的filesort也消失了。这里有一个细节联合索引设计的“等值条件放前面排序字段放后面”原则在这个场景就体现出来了。如果你反过来把create_time放前面status放后面那WHERE status 1的时候就匹配不到这个联合索引索引直接废掉。4.3 前缀索引与索引下推对于超长文本字段比如URL、备注信息这类直接对整个字段建索引会导致索引体积膨胀而且索引树里的每一条都特别长。MySQL支持前缀索引比如ALTER TABLE articles ADD INDEX idx_url_prefix (url(64));只索引字段前64个字符。好处是索引体积小、建树快缺点是有一定概率出现前缀相同导致额外回表判断。具体取多少长度可以通过统计区分度来决定SELECT COUNT(DISTINCT LEFT(url, 64)) / COUNT(*) AS selectivity FROM articles;区分度接近1说明64个字符基本能代表整列。再提一个很多人不知道的优化点索引下推英文是Index Condition Pushdown简称ICP。以前是WHERE条件里有索引覆盖不到的列时必须在回表之后再过滤。开启ICP后MySQL会在索引遍历过程中先把索引包含的列做一遍条件判断只有满足条件的才回表。这个特性在MySQL 5.6之后默认开启配合联合索引效果尤其明显。如果你的MySQL版本比较旧还是建议升级ICP对特定SQL的性能提升是实打实的。5. 索引维护与避坑回表、统计信息与索引失效的其他场景5.1 为什么建了索引还是很慢经常有这样的情况索引建了EXPLAIN也显示走了索引但查询还是慢。第一个怀疑方向是回表次数太多。比如一个查询命中了索引范围rows预估100万那就要回表拿100万次数据这种情况下即使走索引代价也很高。解决办法是把查询需要的列尽可能都放到二级索引里形成覆盖索引。如果无法覆盖就要考虑改变查询条件缩小命中范围比如在时间维度上加过滤条件。第二个要排查的是索引统计信息过期。优化器决定是否走索引时依赖表的统计信息来估算扫描行数。如果表数据变化剧烈而统计信息没更新优化器可能对一个明明有索引的查询选择全表扫描。通常跑一次ANALYZE TABLE让优化器重新统计即可。第三个容易被忽略的是表碎片。频繁的删除和更新会让InnoDB表产生大量碎片导致索引页利用率下降扫描相同行数的代价变大。OPTIMIZE TABLE可以重建表、整理碎片但这个操作会锁表生产环境要选在低峰期执行或者用gh-ost、pt-online-schema-change这类在线工具来操作。5.2 索引失效的其他隐藏场景除了前面提到的函数操作和隐式类型转换还有几个我实际踩过的坑使用不等于条件比如WHERE status ! 1多数情况下MySQL无法用索引快速定位因为B树本来就是按等值和范围来设计的使用LIKE %-关键字%这样以通配符开头的模糊查询最左前缀匹配原则会失效LIKE 关键字%这种以固定字符串开头的则可以用索引OR条件中的一个分支没有索引优化器可能放弃整个查询的索引联合索引但没遵循最左前缀法则比如索引是(a, b)WHERE只查b这些都是执行计划里type变成ALL或索引使用不充分的直接原因。我排查问题的习惯是拿到一条慢SQL第一步看表结构和已有索引第二步EXPLAIN看执行计划第三步根据执行计划反推是索引缺失、失效还是索引设计不合理然后针对性地改。6. 常见问题速查与日常工作建议汇总6.1 常见问题速查表现象可能原因解决方案EXPLAIN显示type为ALL没索引或索引失效检查WHERE条件列是否可建索引确认没有函数、隐式转换走了索引但Extra有filesort排序字段不在索引里建联合索引把排序字段放在等值字段之后翻页越深越慢LIMIT offset过大导致大量回表延迟关联或游标分页明明有索引还是全表扫描统计信息过期或命中行数太大ANALYZE TABLE检查区分度考虑覆盖索引联合索引某个字段没效果查询条件未遵循最左前缀法则调整查询条件或重新设计联合索引表数据量不大但查询很慢可能存在死锁或表锁SHOW ENGINE INNODB STATUS查看锁等待写入速度越来越慢索引过多或表碎片多评估无用索引定期整理碎片6.2 我日常维护索引的几点经验索引优化不是一次性工作而是伴随表结构和业务变化的持续过程。我一般在每个大版本上线前做一次索引评审把慢查询日志里的SQL拉出来逐一核对执行计划。用Percona Toolkit里的pt-query-digest分析慢日志也很方便它可以按执行次数和时间消耗排序帮我快速锁定最值得优化的SQL。有一个细节值得说新增索引尽量用在线DDL方式避免长时间锁表。MySQL 5.6之后InnoDB支持在线DDL执行ALTER TABLE ADD INDEX时通常会允许并发DML继续执行但具体还取决于算法和锁级别大批量数据操作前最好先在测试环境评估影响。删除无用索引也要注意。我见过一个表上有7个索引其中两个单列索引完全被新的联合索引覆盖属于冗余索引。这种冗余不仅浪费空间还白白增加每次写入的维护成本。通过sys.schema_unused_indexes视图可以查到哪些索引从未被使用过再结合慢日志确认就可以安全删除了。另外MySQL 8.0提供了不可见索引和函数索引两个实用特性。不可见索引可以让你在不删除索引的情况下测试“没有这个索引时优化器会怎么走”风险非常低。函数索引则直接解决对字段做函数操作导致索引失效的问题可以评估升级到8.0的价值。最后想说的是索引优化的核心从来不是背诵多少规则而是理解数据结构和执行计划。B树解决了有序存储与高效查询的矛盾理解了这一点很多索引设计原则都不用死记。真正遇到慢SQL的时候拿着EXPLAIN一步步看type、rows和Extra结合表的数据特征和业务场景自然就知道该怎么改。希望这篇内容能帮你少走一些弯路。