1. 先分清索引类型前得先想明白索引到底是什么很多人被“MySQL索引有哪几种类型”这个问题卡住不是因为没背过八股而是上来就背“B树索引、Hash索引、全文索引、空间索引……”背完还是不知道怎么用。我自己带项目的时候最怕听到同事说“这个表我加了索引啊怎么还慢”——那只说明他加的索引和查询根本不匹配。我一般建议先把索引想成书的目录没有目录你要从第一页翻到最后有目录直接翻到对应章节。但MySQL的目录比书的目录复杂得多因为它要考虑数据怎么存、怎么读磁盘最快、怎么在增删改的时候仍然保持有序。所有这些考虑最终都会落到索引的数据结构和类型分类上。先说一个最重要的结论MySQL 里默认且最常用的索引底层结构是 B 树不是二叉搜索树也不是哈希表。B 树的特点是把所有数据都放在叶子节点并且叶子节点之间用指针串联这样范围查询和排序就特别快。而哈希索引只擅长等值匹配区间查询直接废掉。理解了这一层再看“索引有哪几种”其实就是从不同维度切分同一个东西——有的按功能分有的按存储结构分有的按建索引的方式分。搞清楚这几个维度面试和实际调优都不会再慌。接下来我按“数据结构 → 功能类型 → 场景设计 → 优缺点与代价 → 失效排查”这个顺序展开全程结合实操和踩坑经历尽量让这篇文章能直接当参考手册用。2. 索引的数据结构决定了它的性格2.1 B 树索引MySQL 的默认答案B 树为什么能成为 InnoDB 的默认索引结构核心原因是它把“有序”和“高效”两个需求平衡得非常好。每一个节点可以存储多个键值树的高度很低——几百万行数据的表B 树往往只需要三层到四层。这意味着一次查询最多只需要做三四次磁盘IO就能定位到数据。对比平衡二叉树那种结构每一层只能存一个键数据一多树就深磁盘IO次数成倍上升。这么讲吧B树的高度大概相当于楼层高度你要找一个人如果住在只有三四层的楼里坐电梯很快就能到如果住在一百层的楼里光等电梯就急死人。InnoDB 把索引根节点常驻内存所以定位甚至可以更低到两次IO这种效率是二叉树在数据量大时完全做不到的。B 树的另一个关键设计是非叶子节点只存索引键不存数据叶子节点才存完整数据或主键值并且叶子节点之间用链表串起来。这个链表太重要了它让范围查询比如WHERE age BETWEEN 20 AND 30不用反复从根节点往下走而是先在树里找到起始位置然后沿着链表往后扫就行。索引排序输出也很顺手走一遍链表就是有序结果。构建索引时还有两个必须知道的机制页分裂与页合并。插入数据时如果页满了B 树会做页分裂把一部分数据挪到新页删除数据导致页太稀疏时会做页合并。这个操作代价不低所以“索引不是越多越好”——每多一个索引每次 INSERT 都可能要触动多棵 B 树的结构维护。2.2 Hash 索引等值查询的极速通道Hash 索引的结构就是哈希表对索引列计算 Hash 值然后直接定位到数据。它的优势极其明显等值查询时间复杂度是 O(1)比 B 树的 O(logN) 快。但它的短板也同样非常明显——Hash 索引不支持范围查询、不支持排序、不支持部分前缀匹配。你写了WHERE name LIKE 张%或者WHERE age 18Hash 索引直接全表扫描给你看。我在实际项目里很少主动用 Hash 索引不是它不好而是业务查询很少只有等值匹配这一种形态。InnoDB 引擎其实不允许你显式创建 Hash 索引它有一个自适应哈希索引特性InnoDB 会根据热点查询自动在 B 树上面构建哈希索引来加速等值查询——这个特性默认开启所以你有很大概率已经在享受它的好处只是感知不到。Memory 引擎才支持显式建 Hash 索引但 Memory 引擎本身不适合当业务主存储用的人极少。2.3 全文索引与倒排索引全文索引处理的是“文章内容里搜关键词”这种需求。MySQL 5.6 之前只有 MyISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了。很多人对这个索引不太熟其实它的底层结构是倒排索引——简单理解就是建立“关键词 → 包含该关键词的文档ID列表”的映射类似于书的末尾“术语索引”。你要搜“索引优化”全文索引直接返回所有包含这个词的文档而不是用 LIKE 去糊一屏幕扫描。“倒排索引”这个词在热词里出现的频率很高注意有些人会把它和 HBase / MapReduce 里的倒排索引技术混为一谈——原理一样都是“词到文档的映射”但 MySQL 的全文索引只在自己的全文检索语法里用。我自己一般建议数据量小用 LIKE 就行数据量大且要语义化搜索直接上 Elasticsearch不要硬靠 MySQL 全文索引因为它的分词能力、词权重调节、相关性打分都比专业检索引擎差太多了。2.4 存储引擎与索引类型其实是绑定的你的表用 InnoDB 还是 MyISAM直接决定你能用哪些索引类型。InnoDB 的表数据文件本身按主键聚簇存放数据行就是主键索引的叶子节点二级索引的叶子节点存的是主键值而不是行的物理地址。MyISAM 则是数据和索引分离索引叶子节点存的是行数据的物理地址。这俩机制看着差不多性能差异却非常明显——走二级索引查询时InnoDB 可能还要“回表”也就是先查索引拿到主键再按主键查一次数据MyISAM 则直接拿到地址去读行了。MySQL 8.0 里 MyISAM 虽然还能用但官方已经把它边缘化整个引擎生态基本围绕 InnoDB 转。所以下面我讲的所有场景和坑默认都基于 InnoDB如果你还在用 MyISAM 业务表真心建议尽早迁移。3. 功能维度上的索引类型六种索引一次说透搞清楚底层结构之后再来看“MySQL 索引有哪几种类型”这个问题就能答得比较系统了。按功能划分有六大类按存储方式划分有两大类。这两个维度分开记面试时就不容易乱。3.1 按功能划分的六种索引索引类型约束/特点创建示例典型用法主键索引每张表只能有一个非空且唯一InnoDB 中也是聚簇索引PRIMARY KEY (id)每行数据的唯一身份标识唯一索引列值不能重复但允许 NULL且 NULL 可以有多个UNIQUE KEY uk_email (email)手机号、身份证号、邮箱等业务唯一字段普通索引没有任何约束就是为了加速查询KEY idx_name (name)高频查询但不需要唯一的字段全文索引面向文本内容检索基于倒排索引FULLTEXT KEY ft_content (body)搜索文章正文、商品描述等空间索引面向地理坐标等空间数据类型SPATIAL KEY idx_pos (pos)GIS 应用基于 MySQL 8 的空间函数复合索引多个字段组成的索引KEY idx_uid_status (uid, status)多条件过滤、覆盖索引优化其中主键索引和唯一索引最容易被搞混。它们的本质区别是主键索引除了唯一约束外还承担了 InnoDB 存放数据行的“大纲”职责——整个表的物理存储顺序都按主键排列而唯一索引只是一张独立的查询加速目录并不影响数据行的物理排布。另一个容易忽略的点是复合索引联合索引。它不是你建两个单列索引就行的。KEY idx_uid_status (uid, status)建立了一套“先按 uid 排序uid 相同再按 status 排序”的目录它的优势在于可以同时服务WHERE uid? AND status?这种查询还可以通过“最左前缀原则”服务WHERE uid?这个单独的查询。但如果你只写WHERE status?这个复合索引大概率帮不上忙因为跳过最左列目录的排序规则就无从谈起。这个坑我在实际开发里见到过太多次了。3.2 按存储方式划分聚簇索引与非聚簇索引面试题常问“聚簇索引和非聚簇索引区别”你要是把这两个概念和“主键索引”“普通索引”放一起比较就说明还没吃透。聚簇索引叶子节点直接保存整行数据。InnoDB 的主键索引就是聚簇索引所以 InnoDB 表必须有主键没显式定义的话 MySQL 会默认生成一个隐藏列ROW_ID作为聚簇索引。非聚簇索引 / 二级索引叶子节点只保存索引键和主键值。普通索引、唯一索引、复合索引都是非聚簇索引。一次查询走了二级索引如果 SELECT 需要的字段不在索引列里就要拿主键值再去聚簇索引里查一遍整行数据这个过程就是“回表”。回表次数多了性能会明显下降所以优化方向通常是“覆盖索引”——让 SELECT 的字段全部包含在索引里这样扫描二级索引时就能直接拿到数据完全不用回表。之前有一个统计报表的接口原SQL 跑了 800 多毫秒我改成一个覆盖索引后直接压到 60 毫秒这个优化性价比特别高。3.3 复合索引设计中的最左前缀原则复合索引是最能体现优化功力的类型。先说结论使用复合索引时查询条件必须从复合索引的第一个字段开始连续匹配否则索引失效。但这只是最基础的理解深入一点你会发现它还支持两种“变通”在索引列上做范围查询、、BETWEEN范围列右边的索引列会失效。比如复合索引(a,b,c)查询是WHERE a1 AND b2 AND c3那么a能走索引b能走索引c的索引条件基本就用不上了。Rang 列之后的其他条件只能等回表后再做过滤。查询条件的顺序可以打乱比如WHERE b? AND a?MySQL 优化器会自动调整顺序不会影响索引命中这一点很多人不知道容易自己吓自己。实际设计复合索引时我常用的心法就一句话——“先等值后排序最后才范围”。把等值条件字段放前面需要排序的字段放中间范围条件放最后。这样做能最大程度发挥索引的有序性避免文件排序filesort。我见过很多慢查询就是排序字段没进索引导致 MySQL 先取出结果再在内存里做一堆排序运算。4. 索引的使用场景到底什么时候必须建、什么时候别碰4.1 必须建索引的高频场景主键字段物理结构上就是聚簇索引不用单独建也不用纠结每条表都得有。高频 WHERE 条件的等值字段比如订单表里的user_id、状态字段、分类字段只要查询频率高就值得建普通索引或复合索引。高频排序字段ORDER BY 的字段如果能走索引就避免了磁盘文件排序性能提升巨大。这里有一个容易被忽略的细节排序方向也影响索引命中的可能性。ORDER BY a ASC, b DESC这种混合方向在早期 MySQL 版本中很难走索引8.0 开始支持降序索引才勉强好一些。所以设计索引字段顺序时要把排序需求考虑进去。多表 JOIN 的连接字段关联查询时驱动表和被驱动表的连接列上最好都有索引否则 MySQL 每一步关联都要全表扫描那性能是灾难级的。唯一性约束字段手机号、邮箱、身份证号这类必须唯一的字段直接建唯一索引一箭双雕——既保证业务约束又加速查询。4.2 多条件组合查询该怎么建索引热词里有一条“mysql where 条件 a and b 应该怎么建索引”这是个特别经典的问题我单独展开讲。假设有表orders字段有user_id、status、created_at业务 SQL 是SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 10。方案一建两个单列索引idx_user_id和idx_status。执行时 MySQL 可能会选一个索引再过滤另一个条件另一个索引基本浪费了——MySQL 8.0 之前的版本里一次查询通常只能用一个索引用两个索引做 Intersection 的场景极少且难调优。方案二建复合索引(user_id, status)同时把排序字段created_at也放进去变成(user_id, status, created_at)。这样 WHERE 的两个等值条件都能命中索引ORDER BY 也能直接利用索引的有序性一次索引扫描全部搞定。这两个方案表面差别不大实际性能可能差一个数量级。所以遇到 “a and b” 的查询条件在一个复合索引里协作比各自建单列索引用处大得多。那为什么不能无脑把所有查询字段都塞进一个复合索引因为复合索引是有顺序的跟语文书的目录一样——先拼音再偏旁你不能跳过拼音直接查偏旁。比如你用(user_id, status)建了索引但实际查询是WHERE status1 AND created_at 2024-01-01索引最左列 user_id 没出现在条件里这种情况下索引基本不生效。所以复合索引字段顺序必须跟着“最常用的查询条件组合”走而不是所有字段乱加入。4.3 不建议建索引的典型场景低基数列字段可区分度很低比如性别、状态值就那么几类索引出来能筛掉的比例太小反而浪费空间和维护成本。频繁更新的列索引列更新时B 树要同步调整代价很高。如果业务上非更新不可至少别把索引建得太宽。超长文本列对很长的VARCHAR或TEXT建索引每一条索引记录都很占空间而且索引树变得又高又胖。通常做法是取前缀建索引比如KEY idx_content (content(20))用前 20 个字符做索引。数据量很小的表几千行的表全表扫描不过几毫秒加索引反而多余。一个几百行的字典表我绝不会加索引。5. 索引的优缺点和代价清楚收益更要看得见成本5.1 优点不只是查询变快这么简单加速查询是面向用户的收益面向系统内部的收益容易被忽略。InnoDB 在索引设计上可以让数据按索引顺序存储所以 ORDER BY 和 GROUP BY 很多时候不需要额外的排序过程。还有个收益是用索引做覆盖扫描时可以减少回表带来的随机IO随机IO换成顺序IO之后磁盘阵列的吞吐都会好看很多。另一个很少有人提的点索引还能帮助 InnoDB 做行级锁的精准定位。更新操作如果走索引InnoDB 需要锁定的行数很少锁冲突概率大幅降低。我优化过一个秒杀接口就是因为 UPDATE 语没走索引一行还好但流量一大把行都被锁住改了索引条件之后并发量直线上升——这个收益不在常规“索引加速查询”的讨论范围里但实战中价值巨大。5.2 缺点每一棵索引树都要用真金白银养索引最直接的代价是空间成本。一个字段 40 字节一个索引就要给每行多存 40 字节如果索引还包含主键值那成本再翻一倍。500 万行的表多一个索引可能多占几百 MB 磁盘空间几个索引下来 1~2 GB 很常见。云数据库磁盘不便宜这个钱是要算的。更大的代价是写入性能损耗。每次 INSERT、UPDATE、DELETE不只是操作数据行所有涉及的索引 B 树都要同步插入、删除、更新节点。索引多了写入抖动会非常明显。我做压测的时候一张表从两个索引加到六个索引同样一批 INSERT 操作吞吐量直接掉了近四成。如果业务再怎么优化索引数量都压不下去说明到了分库分表或者引入别的手段的时候了。所以选索引这事不是“越多越好”而是“每一棵索引都要有能说清楚的使用场景”。如果一个索引连续几周都没有被 EXPLAIN 命中过就应该列入清理清单。我在公司定期做索引治理清理掉冗余索引之后写入性能立竿见影改善也能省出一大块磁盘空间。5.3 回表成本和索引下推二级索引只存索引列和主键值所以查询结果里只要带有非索引列就必然发生一次回表。回表是随机IO因为索引结构顺序排列但数据行的物理存储顺序是按聚簇索引排列的两者并不总是一致。数据量大、索引选择性不高时回表次数飙升查询反而是灾难。MySQL 5.6 开始引入的“索引下推Index Condition PushdownICP”能缓解这个问题。它的原理很简单把 WHERE 条件里和索引相关的判断提前到索引扫描阶段执行减少回表次数。我有一个复合索引(city, age)查询是WHERE cityBeijing AND age20没有 ICP 时MySQL 可能先在索引里找到 cityBeijing 的所有记录再一条条回表看 age有 ICP 时age 判断直接在索引阶段就做了只有满足条件的才回表。所以 MySQL 版本越新复合索引能承担复杂条件的本事就越强这也是我建议所有人都尽快升到 5.7 或 8.0 的原因之一。6. 索引失效的常见原因和排查手册6.1 六种典型的索引失效场景这一节可能是整个项目里最实用的部分了。索引建了一大堆查询还是慢十有八九是下面这几种原因让索引失效了对索引列使用函数或运算WHERE YEAR(created_at)2024或WHERE price1100索引列被处理后B 树的有序性无从利用直接失效。正确做法是把条件改写为WHERE created_at 2024-01-01 AND created_at 2025-01-01。隐式类型转换索引列是字符串查询入参写的是数字MySQL 会自动把字段转成数字再比较函数作用到索引列上索引失效。WHERE phone 13800138000看似没问题但 phone 是 VARCHAR这里就把索引废了。正确做法是WHERE phone 13800138000。模糊查询前置通配符LIKE %abc没法利用索引因为 B 树只能按前缀匹配。LIKE abc%就能走索引。这种场景如果非要前缀模糊搜索可以考虑全文索引或者借助专门的搜索组件。OR 连接非索引列WHERE id1 OR name张三如果 name 没有索引整个查询很可能走全表扫描。优化方式是拆成两个查询用 UNION 合并或者给两边都建上覆盖索引。复合索引不满足最左前缀这个前面讲过了唯一例外是查询条件里有全部索引列且顺序打乱优化器会调整其他情况基本失效。索引列参与比较时左边是函数或者右边是表达式本质上和第一条一样不再赘述。6.2 用 EXPLAIN 快速定位索引是否起作用排查慢 SQL我最常用的工具就是EXPLAIN。它的作用相当于把 MySQL 优化器的“脑内决策单”给你看但它不会真的执行查询。核心字段就几个type从好到坏依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明全表扫描了肯定有问题。key实际命中的索引名。如果建了索引但这里显示NULL说明索引压根没被用上。rows估算需要扫描的行数。和表总行数对比一下如果rows几乎等于总数说明索引选择性太差。Extra看到Using filesort说明排序没走索引看到Using temporary说明用了临时表看到Using index说明覆盖索引命中这是大好消息。我排查问题时习惯用这样一套固定流程先看 SQL 有没有函数运算、类型转换这些显性问题没有的话再 EXPLAIN 看 type 和 key然后根据 rows 判断是否真的缩小了扫描范围。经验法则是type 只要不是 ALL通常还能救如果出现 ALL 加上 rows 很大那 SQL 基本要重构了。6.3 一个真实排查案例为什么加了索引还是不生效有一次线上表user_login_log数据量大约 800 万行同事反馈一个统计 SQL 要跑 3 秒多老是拖垮接口。原 SQL 大概是SELECT COUNT(*) FROM user_login_log WHERE login_date BETWEEN 2024-01-01 AND 2024-03-01 AND user_id 12345。表上已经有idx_login_date和idx_user_id两个索引看起来没什么问题。EXPLAIN 结果显示typeindex_merge索引合并——它同时用两个索引然后做交集理论上有收益但实际rows估算近百万因为 login_date 范围太大。真正的解法是调整业务统计逻辑优先用 user_id 缩小范围再在范围内查 login_date。我把 SQL 改成WHERE user_id 12345 AND login_date BETWEEN ...并建了复合索引(user_id, login_date)同样的查询直接压到 30 毫秒以内。这个案例的教训很有代表性加索引不能只看字段有没有要看你实际过滤时哪个字段的区分度更高。区分度高的字段放复合索引前面才是正确打开方式。7. 索引设计自查清单与个人经验总结做索引设计没有一劳永逸的方案每个项目都有自己独特的查询模式。根据自己的长期实践我总结了一套排查清单每次做索引评审时都会过一遍每个索引是否能直接对应到一个高频 SQL复合索引字段顺序是否按照等值优先、范围最后的原则设计是否满足了覆盖索引的要求能否把回表成本降到最低有没有对低基数列、超长文本列、频繁更新列建冗余索引定期用 EXPLAIN 抽检重点 SQL确认type没有退化到ALL对写入量大的表是否评估过索引数量带来的性能损耗这个工作做起来不复杂但对线上稳定性的贡献非常实在。最后分享一个我个人的小习惯每次建索引时顺手把这条索引的“出生原因”写在表结构的注释里比如“支持订单列表按用户和状态筛选”。半年后再看这些索引哪些留哪些删一眼就知道不用再对着 SQL 日志猜半天。这个习惯看起来不起眼但真帮我在索引治理上省了大量时间也推荐给你试试。
MySQL索引类型详解：B+树、复合索引与优化实战
1. 先分清索引类型前得先想明白索引到底是什么很多人被“MySQL索引有哪几种类型”这个问题卡住不是因为没背过八股而是上来就背“B树索引、Hash索引、全文索引、空间索引……”背完还是不知道怎么用。我自己带项目的时候最怕听到同事说“这个表我加了索引啊怎么还慢”——那只说明他加的索引和查询根本不匹配。我一般建议先把索引想成书的目录没有目录你要从第一页翻到最后有目录直接翻到对应章节。但MySQL的目录比书的目录复杂得多因为它要考虑数据怎么存、怎么读磁盘最快、怎么在增删改的时候仍然保持有序。所有这些考虑最终都会落到索引的数据结构和类型分类上。先说一个最重要的结论MySQL 里默认且最常用的索引底层结构是 B 树不是二叉搜索树也不是哈希表。B 树的特点是把所有数据都放在叶子节点并且叶子节点之间用指针串联这样范围查询和排序就特别快。而哈希索引只擅长等值匹配区间查询直接废掉。理解了这一层再看“索引有哪几种”其实就是从不同维度切分同一个东西——有的按功能分有的按存储结构分有的按建索引的方式分。搞清楚这几个维度面试和实际调优都不会再慌。接下来我按“数据结构 → 功能类型 → 场景设计 → 优缺点与代价 → 失效排查”这个顺序展开全程结合实操和踩坑经历尽量让这篇文章能直接当参考手册用。2. 索引的数据结构决定了它的性格2.1 B 树索引MySQL 的默认答案B 树为什么能成为 InnoDB 的默认索引结构核心原因是它把“有序”和“高效”两个需求平衡得非常好。每一个节点可以存储多个键值树的高度很低——几百万行数据的表B 树往往只需要三层到四层。这意味着一次查询最多只需要做三四次磁盘IO就能定位到数据。对比平衡二叉树那种结构每一层只能存一个键数据一多树就深磁盘IO次数成倍上升。这么讲吧B树的高度大概相当于楼层高度你要找一个人如果住在只有三四层的楼里坐电梯很快就能到如果住在一百层的楼里光等电梯就急死人。InnoDB 把索引根节点常驻内存所以定位甚至可以更低到两次IO这种效率是二叉树在数据量大时完全做不到的。B 树的另一个关键设计是非叶子节点只存索引键不存数据叶子节点才存完整数据或主键值并且叶子节点之间用链表串起来。这个链表太重要了它让范围查询比如WHERE age BETWEEN 20 AND 30不用反复从根节点往下走而是先在树里找到起始位置然后沿着链表往后扫就行。索引排序输出也很顺手走一遍链表就是有序结果。构建索引时还有两个必须知道的机制页分裂与页合并。插入数据时如果页满了B 树会做页分裂把一部分数据挪到新页删除数据导致页太稀疏时会做页合并。这个操作代价不低所以“索引不是越多越好”——每多一个索引每次 INSERT 都可能要触动多棵 B 树的结构维护。2.2 Hash 索引等值查询的极速通道Hash 索引的结构就是哈希表对索引列计算 Hash 值然后直接定位到数据。它的优势极其明显等值查询时间复杂度是 O(1)比 B 树的 O(logN) 快。但它的短板也同样非常明显——Hash 索引不支持范围查询、不支持排序、不支持部分前缀匹配。你写了WHERE name LIKE 张%或者WHERE age 18Hash 索引直接全表扫描给你看。我在实际项目里很少主动用 Hash 索引不是它不好而是业务查询很少只有等值匹配这一种形态。InnoDB 引擎其实不允许你显式创建 Hash 索引它有一个自适应哈希索引特性InnoDB 会根据热点查询自动在 B 树上面构建哈希索引来加速等值查询——这个特性默认开启所以你有很大概率已经在享受它的好处只是感知不到。Memory 引擎才支持显式建 Hash 索引但 Memory 引擎本身不适合当业务主存储用的人极少。2.3 全文索引与倒排索引全文索引处理的是“文章内容里搜关键词”这种需求。MySQL 5.6 之前只有 MyISAM 引擎支持全文索引5.6 之后 InnoDB 也支持了。很多人对这个索引不太熟其实它的底层结构是倒排索引——简单理解就是建立“关键词 → 包含该关键词的文档ID列表”的映射类似于书的末尾“术语索引”。你要搜“索引优化”全文索引直接返回所有包含这个词的文档而不是用 LIKE 去糊一屏幕扫描。“倒排索引”这个词在热词里出现的频率很高注意有些人会把它和 HBase / MapReduce 里的倒排索引技术混为一谈——原理一样都是“词到文档的映射”但 MySQL 的全文索引只在自己的全文检索语法里用。我自己一般建议数据量小用 LIKE 就行数据量大且要语义化搜索直接上 Elasticsearch不要硬靠 MySQL 全文索引因为它的分词能力、词权重调节、相关性打分都比专业检索引擎差太多了。2.4 存储引擎与索引类型其实是绑定的你的表用 InnoDB 还是 MyISAM直接决定你能用哪些索引类型。InnoDB 的表数据文件本身按主键聚簇存放数据行就是主键索引的叶子节点二级索引的叶子节点存的是主键值而不是行的物理地址。MyISAM 则是数据和索引分离索引叶子节点存的是行数据的物理地址。这俩机制看着差不多性能差异却非常明显——走二级索引查询时InnoDB 可能还要“回表”也就是先查索引拿到主键再按主键查一次数据MyISAM 则直接拿到地址去读行了。MySQL 8.0 里 MyISAM 虽然还能用但官方已经把它边缘化整个引擎生态基本围绕 InnoDB 转。所以下面我讲的所有场景和坑默认都基于 InnoDB如果你还在用 MyISAM 业务表真心建议尽早迁移。3. 功能维度上的索引类型六种索引一次说透搞清楚底层结构之后再来看“MySQL 索引有哪几种类型”这个问题就能答得比较系统了。按功能划分有六大类按存储方式划分有两大类。这两个维度分开记面试时就不容易乱。3.1 按功能划分的六种索引索引类型约束/特点创建示例典型用法主键索引每张表只能有一个非空且唯一InnoDB 中也是聚簇索引PRIMARY KEY (id)每行数据的唯一身份标识唯一索引列值不能重复但允许 NULL且 NULL 可以有多个UNIQUE KEY uk_email (email)手机号、身份证号、邮箱等业务唯一字段普通索引没有任何约束就是为了加速查询KEY idx_name (name)高频查询但不需要唯一的字段全文索引面向文本内容检索基于倒排索引FULLTEXT KEY ft_content (body)搜索文章正文、商品描述等空间索引面向地理坐标等空间数据类型SPATIAL KEY idx_pos (pos)GIS 应用基于 MySQL 8 的空间函数复合索引多个字段组成的索引KEY idx_uid_status (uid, status)多条件过滤、覆盖索引优化其中主键索引和唯一索引最容易被搞混。它们的本质区别是主键索引除了唯一约束外还承担了 InnoDB 存放数据行的“大纲”职责——整个表的物理存储顺序都按主键排列而唯一索引只是一张独立的查询加速目录并不影响数据行的物理排布。另一个容易忽略的点是复合索引联合索引。它不是你建两个单列索引就行的。KEY idx_uid_status (uid, status)建立了一套“先按 uid 排序uid 相同再按 status 排序”的目录它的优势在于可以同时服务WHERE uid? AND status?这种查询还可以通过“最左前缀原则”服务WHERE uid?这个单独的查询。但如果你只写WHERE status?这个复合索引大概率帮不上忙因为跳过最左列目录的排序规则就无从谈起。这个坑我在实际开发里见到过太多次了。3.2 按存储方式划分聚簇索引与非聚簇索引面试题常问“聚簇索引和非聚簇索引区别”你要是把这两个概念和“主键索引”“普通索引”放一起比较就说明还没吃透。聚簇索引叶子节点直接保存整行数据。InnoDB 的主键索引就是聚簇索引所以 InnoDB 表必须有主键没显式定义的话 MySQL 会默认生成一个隐藏列ROW_ID作为聚簇索引。非聚簇索引 / 二级索引叶子节点只保存索引键和主键值。普通索引、唯一索引、复合索引都是非聚簇索引。一次查询走了二级索引如果 SELECT 需要的字段不在索引列里就要拿主键值再去聚簇索引里查一遍整行数据这个过程就是“回表”。回表次数多了性能会明显下降所以优化方向通常是“覆盖索引”——让 SELECT 的字段全部包含在索引里这样扫描二级索引时就能直接拿到数据完全不用回表。之前有一个统计报表的接口原SQL 跑了 800 多毫秒我改成一个覆盖索引后直接压到 60 毫秒这个优化性价比特别高。3.3 复合索引设计中的最左前缀原则复合索引是最能体现优化功力的类型。先说结论使用复合索引时查询条件必须从复合索引的第一个字段开始连续匹配否则索引失效。但这只是最基础的理解深入一点你会发现它还支持两种“变通”在索引列上做范围查询、、BETWEEN范围列右边的索引列会失效。比如复合索引(a,b,c)查询是WHERE a1 AND b2 AND c3那么a能走索引b能走索引c的索引条件基本就用不上了。Rang 列之后的其他条件只能等回表后再做过滤。查询条件的顺序可以打乱比如WHERE b? AND a?MySQL 优化器会自动调整顺序不会影响索引命中这一点很多人不知道容易自己吓自己。实际设计复合索引时我常用的心法就一句话——“先等值后排序最后才范围”。把等值条件字段放前面需要排序的字段放中间范围条件放最后。这样做能最大程度发挥索引的有序性避免文件排序filesort。我见过很多慢查询就是排序字段没进索引导致 MySQL 先取出结果再在内存里做一堆排序运算。4. 索引的使用场景到底什么时候必须建、什么时候别碰4.1 必须建索引的高频场景主键字段物理结构上就是聚簇索引不用单独建也不用纠结每条表都得有。高频 WHERE 条件的等值字段比如订单表里的user_id、状态字段、分类字段只要查询频率高就值得建普通索引或复合索引。高频排序字段ORDER BY 的字段如果能走索引就避免了磁盘文件排序性能提升巨大。这里有一个容易被忽略的细节排序方向也影响索引命中的可能性。ORDER BY a ASC, b DESC这种混合方向在早期 MySQL 版本中很难走索引8.0 开始支持降序索引才勉强好一些。所以设计索引字段顺序时要把排序需求考虑进去。多表 JOIN 的连接字段关联查询时驱动表和被驱动表的连接列上最好都有索引否则 MySQL 每一步关联都要全表扫描那性能是灾难级的。唯一性约束字段手机号、邮箱、身份证号这类必须唯一的字段直接建唯一索引一箭双雕——既保证业务约束又加速查询。4.2 多条件组合查询该怎么建索引热词里有一条“mysql where 条件 a and b 应该怎么建索引”这是个特别经典的问题我单独展开讲。假设有表orders字段有user_id、status、created_at业务 SQL 是SELECT * FROM orders WHERE user_id 1001 AND status 1 ORDER BY created_at DESC LIMIT 10。方案一建两个单列索引idx_user_id和idx_status。执行时 MySQL 可能会选一个索引再过滤另一个条件另一个索引基本浪费了——MySQL 8.0 之前的版本里一次查询通常只能用一个索引用两个索引做 Intersection 的场景极少且难调优。方案二建复合索引(user_id, status)同时把排序字段created_at也放进去变成(user_id, status, created_at)。这样 WHERE 的两个等值条件都能命中索引ORDER BY 也能直接利用索引的有序性一次索引扫描全部搞定。这两个方案表面差别不大实际性能可能差一个数量级。所以遇到 “a and b” 的查询条件在一个复合索引里协作比各自建单列索引用处大得多。那为什么不能无脑把所有查询字段都塞进一个复合索引因为复合索引是有顺序的跟语文书的目录一样——先拼音再偏旁你不能跳过拼音直接查偏旁。比如你用(user_id, status)建了索引但实际查询是WHERE status1 AND created_at 2024-01-01索引最左列 user_id 没出现在条件里这种情况下索引基本不生效。所以复合索引字段顺序必须跟着“最常用的查询条件组合”走而不是所有字段乱加入。4.3 不建议建索引的典型场景低基数列字段可区分度很低比如性别、状态值就那么几类索引出来能筛掉的比例太小反而浪费空间和维护成本。频繁更新的列索引列更新时B 树要同步调整代价很高。如果业务上非更新不可至少别把索引建得太宽。超长文本列对很长的VARCHAR或TEXT建索引每一条索引记录都很占空间而且索引树变得又高又胖。通常做法是取前缀建索引比如KEY idx_content (content(20))用前 20 个字符做索引。数据量很小的表几千行的表全表扫描不过几毫秒加索引反而多余。一个几百行的字典表我绝不会加索引。5. 索引的优缺点和代价清楚收益更要看得见成本5.1 优点不只是查询变快这么简单加速查询是面向用户的收益面向系统内部的收益容易被忽略。InnoDB 在索引设计上可以让数据按索引顺序存储所以 ORDER BY 和 GROUP BY 很多时候不需要额外的排序过程。还有个收益是用索引做覆盖扫描时可以减少回表带来的随机IO随机IO换成顺序IO之后磁盘阵列的吞吐都会好看很多。另一个很少有人提的点索引还能帮助 InnoDB 做行级锁的精准定位。更新操作如果走索引InnoDB 需要锁定的行数很少锁冲突概率大幅降低。我优化过一个秒杀接口就是因为 UPDATE 语没走索引一行还好但流量一大把行都被锁住改了索引条件之后并发量直线上升——这个收益不在常规“索引加速查询”的讨论范围里但实战中价值巨大。5.2 缺点每一棵索引树都要用真金白银养索引最直接的代价是空间成本。一个字段 40 字节一个索引就要给每行多存 40 字节如果索引还包含主键值那成本再翻一倍。500 万行的表多一个索引可能多占几百 MB 磁盘空间几个索引下来 1~2 GB 很常见。云数据库磁盘不便宜这个钱是要算的。更大的代价是写入性能损耗。每次 INSERT、UPDATE、DELETE不只是操作数据行所有涉及的索引 B 树都要同步插入、删除、更新节点。索引多了写入抖动会非常明显。我做压测的时候一张表从两个索引加到六个索引同样一批 INSERT 操作吞吐量直接掉了近四成。如果业务再怎么优化索引数量都压不下去说明到了分库分表或者引入别的手段的时候了。所以选索引这事不是“越多越好”而是“每一棵索引都要有能说清楚的使用场景”。如果一个索引连续几周都没有被 EXPLAIN 命中过就应该列入清理清单。我在公司定期做索引治理清理掉冗余索引之后写入性能立竿见影改善也能省出一大块磁盘空间。5.3 回表成本和索引下推二级索引只存索引列和主键值所以查询结果里只要带有非索引列就必然发生一次回表。回表是随机IO因为索引结构顺序排列但数据行的物理存储顺序是按聚簇索引排列的两者并不总是一致。数据量大、索引选择性不高时回表次数飙升查询反而是灾难。MySQL 5.6 开始引入的“索引下推Index Condition PushdownICP”能缓解这个问题。它的原理很简单把 WHERE 条件里和索引相关的判断提前到索引扫描阶段执行减少回表次数。我有一个复合索引(city, age)查询是WHERE cityBeijing AND age20没有 ICP 时MySQL 可能先在索引里找到 cityBeijing 的所有记录再一条条回表看 age有 ICP 时age 判断直接在索引阶段就做了只有满足条件的才回表。所以 MySQL 版本越新复合索引能承担复杂条件的本事就越强这也是我建议所有人都尽快升到 5.7 或 8.0 的原因之一。6. 索引失效的常见原因和排查手册6.1 六种典型的索引失效场景这一节可能是整个项目里最实用的部分了。索引建了一大堆查询还是慢十有八九是下面这几种原因让索引失效了对索引列使用函数或运算WHERE YEAR(created_at)2024或WHERE price1100索引列被处理后B 树的有序性无从利用直接失效。正确做法是把条件改写为WHERE created_at 2024-01-01 AND created_at 2025-01-01。隐式类型转换索引列是字符串查询入参写的是数字MySQL 会自动把字段转成数字再比较函数作用到索引列上索引失效。WHERE phone 13800138000看似没问题但 phone 是 VARCHAR这里就把索引废了。正确做法是WHERE phone 13800138000。模糊查询前置通配符LIKE %abc没法利用索引因为 B 树只能按前缀匹配。LIKE abc%就能走索引。这种场景如果非要前缀模糊搜索可以考虑全文索引或者借助专门的搜索组件。OR 连接非索引列WHERE id1 OR name张三如果 name 没有索引整个查询很可能走全表扫描。优化方式是拆成两个查询用 UNION 合并或者给两边都建上覆盖索引。复合索引不满足最左前缀这个前面讲过了唯一例外是查询条件里有全部索引列且顺序打乱优化器会调整其他情况基本失效。索引列参与比较时左边是函数或者右边是表达式本质上和第一条一样不再赘述。6.2 用 EXPLAIN 快速定位索引是否起作用排查慢 SQL我最常用的工具就是EXPLAIN。它的作用相当于把 MySQL 优化器的“脑内决策单”给你看但它不会真的执行查询。核心字段就几个type从好到坏依次是system、const、eq_ref、ref、range、index、ALL。看到ALL就说明全表扫描了肯定有问题。key实际命中的索引名。如果建了索引但这里显示NULL说明索引压根没被用上。rows估算需要扫描的行数。和表总行数对比一下如果rows几乎等于总数说明索引选择性太差。Extra看到Using filesort说明排序没走索引看到Using temporary说明用了临时表看到Using index说明覆盖索引命中这是大好消息。我排查问题时习惯用这样一套固定流程先看 SQL 有没有函数运算、类型转换这些显性问题没有的话再 EXPLAIN 看 type 和 key然后根据 rows 判断是否真的缩小了扫描范围。经验法则是type 只要不是 ALL通常还能救如果出现 ALL 加上 rows 很大那 SQL 基本要重构了。6.3 一个真实排查案例为什么加了索引还是不生效有一次线上表user_login_log数据量大约 800 万行同事反馈一个统计 SQL 要跑 3 秒多老是拖垮接口。原 SQL 大概是SELECT COUNT(*) FROM user_login_log WHERE login_date BETWEEN 2024-01-01 AND 2024-03-01 AND user_id 12345。表上已经有idx_login_date和idx_user_id两个索引看起来没什么问题。EXPLAIN 结果显示typeindex_merge索引合并——它同时用两个索引然后做交集理论上有收益但实际rows估算近百万因为 login_date 范围太大。真正的解法是调整业务统计逻辑优先用 user_id 缩小范围再在范围内查 login_date。我把 SQL 改成WHERE user_id 12345 AND login_date BETWEEN ...并建了复合索引(user_id, login_date)同样的查询直接压到 30 毫秒以内。这个案例的教训很有代表性加索引不能只看字段有没有要看你实际过滤时哪个字段的区分度更高。区分度高的字段放复合索引前面才是正确打开方式。7. 索引设计自查清单与个人经验总结做索引设计没有一劳永逸的方案每个项目都有自己独特的查询模式。根据自己的长期实践我总结了一套排查清单每次做索引评审时都会过一遍每个索引是否能直接对应到一个高频 SQL复合索引字段顺序是否按照等值优先、范围最后的原则设计是否满足了覆盖索引的要求能否把回表成本降到最低有没有对低基数列、超长文本列、频繁更新列建冗余索引定期用 EXPLAIN 抽检重点 SQL确认type没有退化到ALL对写入量大的表是否评估过索引数量带来的性能损耗这个工作做起来不复杂但对线上稳定性的贡献非常实在。最后分享一个我个人的小习惯每次建索引时顺手把这条索引的“出生原因”写在表结构的注释里比如“支持订单列表按用户和状态筛选”。半年后再看这些索引哪些留哪些删一眼就知道不用再对着 SQL 日志猜半天。这个习惯看起来不起眼但真帮我在索引治理上省了大量时间也推荐给你试试。
