MySQL 索引与执行计划下面按“索引原理 → 索引设计 → EXPLAIN 执行计划 → 实战分析”讲默认以MySQL 5.7 InnoDB为主。一、MySQL 索引是什么索引可以理解为数据库为数据额外建立的一套快速查找结构。没有索引时SELECT*FROMuserWHEREname张三;数据库可能需要从第一行扫描到最后一行称为全表扫描。有索引时CREATEINDEXidx_nameONuser(name);MySQL 可以先从索引中定位张三对应的记录再读取完整数据。索引能够加快查询但会带来额外成本占用磁盘空间插入、更新、删除时需要维护索引索引过多会降低写入性能优化器可能选择错误索引因此索引不是越多越好。二、InnoDB 为什么使用 B 树InnoDB 普通索引默认使用BTree。B 树大致具有以下特点非叶子节点只存储索引键和指针真正的数据都存储在叶子节点叶子节点之间通过双向链表连接树的高度通常很低一般只有 34 层例如[20 | 40] / | \ [1~19] [20~39] [40~]查询WHEREid35只需要从根节点逐层定位不需要扫描全部数据。B 树特别适合WHEREid100WHEREid100WHEREidBETWEEN100AND200ORDERBYid因为叶子节点本身是有序的。三、聚簇索引与二级索引1. 聚簇索引InnoDB 表的数据实际存储在主键索引的叶子节点中。例如CREATETABLEuser(idBIGINTPRIMARYKEY,nameVARCHAR(50),ageINT);主键索引结构大致是id - 完整行数据例如1 - {id1, name张三, age20} 2 - {id2, name李四, age25}因此SELECT*FROMuserWHEREid1;通过主键索引定位后能够直接拿到完整数据。这就是聚簇索引。一个 InnoDB 表只能有一个聚簇索引因为数据只能按一种方式组织。2. 二级索引普通索引、唯一索引都属于二级索引。CREATEINDEXidx_nameONuser(name);二级索引叶子节点保存的是name - 主键值例如张三 - 1 李四 - 2执行SELECT*FROMuserWHEREname张三;执行过程在idx_name中找到张三得到主键id 1再通过主键索引找到完整数据第二次通过主键查完整记录的过程叫作回表四、覆盖索引如果查询的字段全部存在于索引中就不需要回表。假设有索引CREATEINDEXidx_name_ageONuser(name,age);执行SELECTname,ageFROMuserWHEREname张三;索引中已经有name age 主键因此不需要再通过主键读取整行数据。这叫作覆盖索引在执行计划中通常会看到Using index覆盖索引通常能显著减少磁盘读取。但是SELECT*FROMuserWHEREname张三;因为索引中没有其他所有字段所以一般仍然需要回表。五、联合索引与最左前缀原则假设创建联合索引CREATEINDEXidx_a_b_cONtest(a,b,c);索引的排序方式可以理解为先按 a 排序 a 相同时再按 b 排序 a、b 相同时再按 c 排序因此以下查询通常可以使用索引WHEREa1WHEREa1ANDb2WHEREa1ANDb2ANDc3WHEREa1ANDc3最后一个查询WHEREa1ANDc3一般只能有效使用a不能直接利用c定位。以下查询无法满足最左前缀WHEREb2WHEREc3WHEREb2ANDc3因为跳过了联合索引最左边的a。联合索引不是必须按照 SQL 条件顺序写索引(a,b,c)下面两条 SQL 通常等价WHEREa1ANDb2WHEREb2ANDa1MySQL 优化器会重新分析条件顺序。最左前缀是指联合索引的字段定义顺序而不是 WHERE 条件的书写顺序。六、范围条件对联合索引的影响索引CREATEINDEXidx_a_b_cONtest(a,b,c);查询WHEREa1ANDb10ANDc3通常a用于索引定位b用于范围扫描c很难继续用于缩小索引扫描范围原因是进入b 10后后面的c已经不是连续有序的。可以简单记为联合索引中遇到范围查询后后续字段通常无法继续用于索引范围定位。常见范围条件BETWEENLIKEabc%不过后续字段仍可能通过索引条件下推 ICP进行过滤不代表完全没有作用。七、常见索引类型1. 主键索引PRIMARYKEY(id)特点唯一不允许 NULLInnoDB 聚簇索引一张表只能有一个2. 唯一索引CREATEUNIQUEINDEXuk_phoneONuser(phone);特点保证字段值唯一通常允许多个 NULL具体表现与数据库实现相关可以提高唯一值查询效率3. 普通索引CREATEINDEXidx_nameONuser(name);只用于提高查询效率不保证唯一性。4. 联合索引CREATEINDEXidx_status_create_timeONorders(status,create_time);适合多个字段组合查询。5. 前缀索引对于很长的字符串可以只索引前几个字符CREATEINDEXidx_urlONrequest_log(url(50));优点减少索引大小提高索引页容纳的数据量缺点区分度可能下降通常不能完整支持覆盖索引需要选择合理的前缀长度6. 全文索引FULLTEXTINDEXidx_content(content)用于文本检索但中文分词需要额外关注分词器和版本支持。八、什么字段适合建索引通常适合索引的字段WHERE 中频繁查询的字段JOIN 关联字段ORDER BY 字段GROUP BY 字段区分度高的字段唯一约束字段经常组合查询的字段例如SELECT*FROMordersWHEREuser_id?ANDstatus?ORDERBYcreate_timeDESC;可以考虑CREATEINDEXidx_user_status_timeONorders(user_id,status,create_time);九、什么字段不一定适合建索引1. 区分度很低的字段例如gender is_deletedstatus假设is_deleted只有0 1单独建立CREATEINDEXidx_deletedONuser(is_deleted);查询WHEREis_deleted0如果 99% 的数据都是0使用索引需要大量回表可能还不如全表扫描。但是低区分度字段可以作为联合索引的一部分INDEXidx_tenant_deleted_time(tenant_id,is_deleted,create_time)不能简单认为“低区分度字段绝对不能建索引”。2. 很少用于查询的字段维护成本大于查询收益没有必要创建索引。3. 频繁更新的大字段索引越多更新成本越高。十、索引下推 ICP假设索引CREATEINDEXidx_name_ageONuser(name,age);执行SELECT*FROMuserWHEREnameLIKE张%ANDage20;没有 ICP 时根据name找到所有姓张的主键全部回表再判断age 20有 ICP 时根据name扫描索引直接在索引层判断age 20只对符合条件的数据回表执行计划中通常出现Using index condition这不等于覆盖索引。区别Using index表示覆盖索引通常不需要回表。Using index condition表示使用索引条件下推但可能仍然需要回表。十一、索引失效的常见场景严格来说不一定是完全“失效”而是优化器没有使用索引或者只使用了部分索引。1. 对索引字段使用函数WHEREDATE(create_time)2026-07-21可能无法正常使用create_time索引范围查询。建议改成WHEREcreate_time2026-07-21 00:00:00ANDcreate_time2026-07-22 00:00:002. 对索引字段进行计算WHEREamount1100建议改成WHEREamount993. 隐式类型转换字段是字符串phoneVARCHAR(20)错误写法WHEREphone13800138000更合适WHEREphone13800138000当字符串列和数字比较时MySQL 可能对列进行类型转换导致索引无法高效使用。4. LIKE 以%开头可以使用索引WHEREnameLIKE张%通常不能有效使用普通 B 树索引WHEREnameLIKE%张WHEREnameLIKE%张%因为不知道从索引的哪个位置开始扫描。5. 联合索引跳过最左字段索引(a,b,c)查询WHEREb2通常无法有效利用该联合索引。6. 使用!或WHEREstatus!1不是说一定不使用索引而是因为可能匹配大量数据优化器经常认为全表扫描成本更低。7.IS NULL是否使用索引WHEREdeleted_atISNULLMySQL 可以使用索引。但如果大量记录都是 NULL优化器也可能选择全表扫描。8. OR 条件WHEREname张三ORage20如果name和age都有索引MySQL 可能使用index_merge。如果其中一个字段没有索引可能退化为全表扫描。有时可以改为SELECT...FROMuserWHEREname张三UNIONALLSELECT...FROMuserWHEREage20ANDname张三;但是否优化需要结合执行计划和数据量判断不能机械改写。十二、执行计划 EXPLAIN基本用法EXPLAINSELECT*FROMuserWHEREname张三;常见结果id select_type table type possible_keys key key_len ref rows filtered Extra重点关注type key key_len rows filtered Extra十三、EXPLAIN 各字段说明1. id表示查询块的执行顺序。SELECT*FROMuserWHEREidIN(SELECTuser_idFROMorders);可能产生多个id。通常id越大越先执行id相同从上往下执行不过复杂 SQL 经过优化器重写后不能只机械看 id。2. select_type常见值值含义SIMPLE简单查询没有子查询和 UNIONPRIMARY最外层查询SUBQUERY子查询DERIVEDFROM 中的派生表UNIONUNION 后面的查询UNION RESULTUNION 结果DEPENDENT SUBQUERY依赖外层结果的子查询DEPENDENT SUBQUERY往往需要重点关注因为它可能对外层每行都执行一次。3. table当前访问的表。如果出现derived2表示访问的是id 2产生的派生表。4. typetype表示访问方式是执行计划最重要的字段之一。一般性能从好到差system const eq_ref ref range index ALLsystem表中只有一行数据极少见。const通过主键或唯一索引定位一条数据SELECT*FROMuserWHEREid1;eq_ref关联查询中被驱动表通过主键或唯一索引每次最多匹配一行。SELECT*FROMorders oJOINuseruONo.user_idu.id;如果u.id是主键访问u时可能是eq_ref。ref使用普通索引等值查询可能返回多行SELECT*FROMuserWHEREname张三;range索引范围扫描WHEREid100WHEREcreate_timeBETWEEN...AND...WHEREidIN(1,2,3)index扫描整个索引。type index不代表性能很好它只是全索引扫描比ALL好一点是因为索引通常比整行数据更小。ALL全表扫描type ALL大表中一般需要重点检查。不过小表全表扫描是正常的不一定需要优化。十四、possible_keys 和 keypossible_keys优化器认为可能使用的索引。key最终实际选择的索引。例如possible_keys: idx_name, idx_name_age key: idx_name_age表示两个索引都有可能使用但最终选择了idx_name_age。如果possible_keys: idx_name key: NULL说明虽然存在候选索引但优化器认为全表扫描成本更低。十五、key_len 怎么看key_len表示 MySQL 实际使用的索引长度。它可以帮助判断联合索引用了几个字段。例如CREATEINDEXidx_a_b_cONtest(a,b,c);执行WHEREa1和WHEREa1ANDb2第二条 SQL 的key_len通常更长。注意key_len是最大可能长度会受到字段类型、字符集、NULL 属性影响不是实际读取的字节数例如VARCHAR(100)CHARACTERSETutf8mb4一个字符最多可能占 4 字节因此索引长度不能简单按 100 计算。十六、ref表示索引字段与什么值进行比较。常见值const 数据库.表.字段 func例如ref: const表示与常量进行比较。JOIN 中ref: db.orders.user_id表示当前表索引字段与另一张表的user_id比较。十七、rowsrows表示优化器预计需要扫描的行数。例如rows: 100000意味着优化器估计需要检查 10 万行。这不是实际扫描行数而是根据统计信息估算的。一般来说rows 越小越好但要结合最终返回行数看。如果返回 1 行却预计扫描 100 万行就需要重点优化。MySQL 5.7 的 EXPLAIN 主要给估算值。需要更准确数据时可以结合SHOWSTATUS;以及慢查询日志 performance_schemaMySQL 8.0 才提供更实用的EXPLAINANALYZE十八、filteredfiltered表示从存储引擎返回的数据中预计有多少比例能够通过剩余条件。例如rows 10000 filtered 10.00大致表示最终向下一阶段传递10000 × 10% 1000 行它也是估算值。十九、Extra 常见值1. Using where表示读取数据后还需要通过 WHERE 条件过滤。它非常常见不代表一定有问题。2. Using index表示使用覆盖索引不需要回表。通常是好现象。3. Using index condition表示使用索引条件下推 ICP。可能仍然需要回表。4. Using temporary表示需要使用临时表。常见于GROUPBYDISTINCTORDERBYUNION大数据量时需要重点关注。5. Using filesort表示无法直接利用索引顺序完成排序需要额外排序。名字叫filesort但不一定真的使用磁盘也可能在内存中完成。例如SELECT*FROMuserWHEREstatus1ORDERBYcreate_time;如果只有INDEXidx_status(status)可能出现Using filesort可以考虑INDEXidx_status_time(status,create_time)6. Using join buffer说明 JOIN 时无法有效使用索引MySQL 使用 Join Buffer。例如Using join buffer (Block Nested Loop)通常需要检查关联字段是否有索引。7. Impossible WHEREWHERE 条件不可能成立。例如WHEREid1ANDid2二十、实际示例建表CREATETABLEorders(idBIGINTNOTNULLAUTO_INCREMENT,user_idBIGINTNOTNULL,statusTINYINTNOTNULL,amountDECIMAL(18,2)NOTNULL,create_timeDATETIMENOTNULL,PRIMARYKEY(id),KEYidx_user_id(user_id),KEYidx_status_time(status,create_time))ENGINEInnoDB;查询SELECTid,user_id,status,create_timeFROMordersWHEREstatus1ANDcreate_time2026-07-01 00:00:00ANDcreate_time2026-08-01 00:00:00ORDERBYcreate_timeDESC;执行计划可能类似type: range possible_keys: idx_status_time key: idx_status_time rows: 10000 Extra: Using index condition说明使用了idx_status_timestatus 1是等值条件create_time是范围条件能够利用索引完成范围定位排序方向通常也可以从索引中反向扫描完成二十一、联合索引字段顺序怎么定联合索引字段顺序一般综合考虑等值条件优先范围条件放后面高频查询字段优先尽量兼顾排序尽量形成覆盖索引考虑字段区分度考虑多个 SQL 的复用性例如WHEREtenant_id?ANDstatus?ANDcreate_time?ORDERBYcreate_timeDESC通常可以建立INDEXidx_tenant_status_time(tenant_id,status,create_time)而不是INDEXidx_time_status_tenant(create_time,status,tenant_id)因为create_time是范围条件如果放在最前面后续字段很难继续参与索引范围定位。二十二、ORDER BY 如何使用索引索引INDEXidx_a_b_c(a,b,c)可以较好支持WHEREa1ORDERBYb,c也可以支持WHEREa1ORDERBYbDESC,cDESC但下面可能无法完整利用索引排序WHEREa1ORDERBYbASC,cDESCMySQL 5.7 对混合排序方向支持有限可能出现Using filesort下面也可能无法利用该索引排序ORDERBYc因为跳过了a、b。二十三、分页查询的索引问题普通分页SELECT*FROMordersORDERBYidLIMIT1000000,20;即使id有索引也需要先扫描前 1000020 条再丢掉前 1000000 条。可以改成游标式分页SELECT*FROMordersWHEREid1000000ORDERBYidLIMIT20;如果必须按偏移量分页可以先通过覆盖索引获取主键SELECTo.*FROMorders oJOIN(SELECTidFROMordersORDERBYidLIMIT1000000,20)tONo.idt.id;是否更快取决于表宽度和数据量需要用执行计划和压测验证。二十四、索引设计常见误区误区 1每个字段都建单列索引查询WHEREuser_id?ANDstatus?ANDcreate_time?分别建立INDEX(user_id)INDEX(status)INDEX(create_time)通常不如建立合适的联合索引INDEX(user_id,status,create_time)MySQL 虽然可能使用index_merge但多数场景下不如一个合理联合索引稳定。误区 2区分度最高的字段必须放最前面区分度只是因素之一还要考虑查询条件模式是否等值查询是否范围查询是否用于排序是否复用索引前缀不能只按区分度排序。误区 3执行计划使用了索引就一定快例如type index rows 5000000虽然使用了索引但实际扫描了整个索引仍然可能很慢。需要综合看type rows filtered Extra 回表次数 实际耗时误区 4Using filesort一定非常慢如果只需要排序几十行Using filesort可能没有明显影响。为了消除一次很小的排序而增加一个复杂索引可能得不偿失。二十五、分析慢 SQL 的推荐步骤第一步查看 SQL 返回量确认实际返回多少行扫描多少行是否查询了不必要字段是否使用了SELECT *第二步执行 EXPLAINEXPLAINSELECT...;重点检查type key key_len rows filtered Extra第三步检查表结构和索引SHOWCREATETABLEorders;或SHOWINDEXFROMorders;关注当前有哪些索引联合索引字段顺序是否存在重复索引字段类型是否一致第四步检查数据分布SELECTstatus,COUNT(*)FROMordersGROUPBYstatus;确认字段区分度和数据倾斜。例如status1 占 99% status2 占 1%同一条 SQL 查询不同 status优化器可能选择不同执行方式。第五步检查隐式转换和函数重点排查WHEREvarchar_col123WHEREDATE(create_time)...WHEREid1...WHEREnameLIKE%abc%第六步建立联合索引后重新分析ALTERTABLEordersADDINDEXidx_user_status_time(user_id,status,create_time);重新执行EXPLAINSELECT...;不要只看执行计划还要测试实际耗时。二十六、一个典型优化案例原 SQLSELECT*FROMordersWHEREuser_id1001ANDstatus1ANDDATE(create_time)2026-07-21ORDERBYcreate_timeDESC;已有索引INDEXidx_user_id(user_id)INDEXidx_status(status)INDEXidx_create_time(create_time)问题三个单列索引不能很好匹配组合条件DATE(create_time)对索引字段使用函数SELECT *可能产生大量回表排序可能出现Using filesort修改 SQLSELECTid,user_id,status,amount,create_timeFROMordersWHEREuser_id1001ANDstatus1ANDcreate_time2026-07-21 00:00:00ANDcreate_time2026-07-22 00:00:00ORDERBYcreate_timeDESC;增加索引ALTERTABLEordersADDINDEXidx_user_status_time(user_id,status,create_time);此时执行过程通常是通过user_id 1001定位索引范围继续通过status 1缩小范围通过create_time范围扫描利用索引顺序返回结果根据主键回表读取所需字段二十七、最终记忆版索引设计等值条件在前 范围条件在后 兼顾 ORDER BY 避免重复索引 尽量覆盖查询字段 不要盲目给低区分度字段建单列索引EXPLAIN 重点type访问方式 key实际索引 key_len用了联合索引的多少部分 rows预计扫描行数 filtered过滤比例 Extra是否回表、排序、临时表、ICP需要重点警惕type ALL rows 很大 Using filesort Using temporary Using join buffer DEPENDENT SUBQUERY key NULL 扫描行数远大于返回行数但不要机械判断最终还是要结合数据量 数据分布 返回行数 SQL 执行频率 写入压力 实际耗时