1. 开局先聊聊我对MySQL索引的实际感受做后端开发这些年我几乎每天都会和MySQL打交道。不管是刚入行时写的第一个商品查询接口还是后来负责的千万级订单表索引这个东西始终绕不开。很多人问我说索引到底该怎么理解我觉得可以用一个特别生活化的比喻一本几百页的技术书你查某个概念时是愿意一页页翻还是先翻到书末尾的“索引页”根据页码直接跳到对应内容明显是后者。MySQL里的索引干的就是这件“按图索骥”的事。但索引的理解绝不只是“查得快”这么简单。如果你只停留在“给查询慢的字段加个索引”这种层面后面会遇到很多麻烦索引加了但查询还是慢、写操作被拖垮、磁盘空间膨胀、数据量一大就卡死。我自己就在生产环境里踩过好几次坑有的坑修起来非常痛苦。这篇就把我对MySQL索引的理解系统性地拆一遍从底层的B树数据结构到不同类型索引的使用场景再到索引失效和性能调优尽量用大白话讲清楚并用真实场景告诉你每一步该怎么处理。这篇文章适合正在学习数据库原理的开发者也适合工作两三年但没系统整理过索引知识的后端工程师如果你正在准备数据库相关的面试里面也会有不少可以直接拿来用的理解角度。2. 索引底层的核心数据结构为什么偏偏是B树很多人一说索引就会想到B树但你要问他为什么MySQL的InnoDB引擎选B树而不选别的他可能就支支吾吾了。这一节把底层逻辑彻底讲透理解了这部分后面所有关于索引的“玄学”都会变得非常自然。2.1 从“数据在磁盘上怎么读”说起要理解数据结构的选型首先得知道一个基本事实数据是存在磁盘上的磁盘读数据不是按“字节”来而是按“页”来。MySQL默认一页是16KB意思是哪怕你只查一条记录InnoDB也会把包含这条记录的整页数据从磁盘加载到内存。磁盘IO是个极度耗时的事相比内存操作可能要慢几个数量级。所以数据库设计的第一原则就是尽量减少磁盘IO的次数。顺着这个思路索引这个数据结构要回答的核心问题就是给定一个查找条件我最多读几个磁盘页就能找到目标数据如果数据结构是链表查找一个元素最坏情况要遍历全表N条数据就是N次磁盘IO这显然不行。如果是普通二叉树在数据分布不均匀时可能退化成链表也不行。如果是平衡二叉树AVL树虽然树高控制得不错但每个节点只能存一个关键字节点多了树照样很深每次下降一层都是一次磁盘IO。这里你可以把“树的高度”直接理解为“磁盘IO次数”高度越低IO越少查询越快。2.2 B树到底赢在哪B树的出现先把“一个节点只存一个关键字”这个问题解决了。B树是一种多路平衡查找树每个节点可以存多个关键字和多个子节点指针树高大幅下降。比如一个高度为3的B树就能存下几百万条数据这意味着最多3次磁盘IO就能定位到目标页这已经非常优秀了。那InnoDB为什么不用B树而用B树区别主要在两点。第一B树的所有数据都存放在叶子节点非叶子节点只存索引键值不存数据本身。这样每个非叶子节点能容纳更多关键字树就变得更矮更宽。InnoDB一页16KB非叶子节点里每条索引记录可能就占几十字节一个节点存几百上千个关键字很轻松树高基本保持在2到3层。对于千万级数据的表从根节点到叶子节点通常只需要2到3次磁盘IO这就是B树在查询效率上的硬实力。第二B树的叶子节点之间通过双向指针串成了一个有序链表天然支持范围查询。比如你要查“价格在100到200之间的商品”B树找到100的位置之后顺着叶子节点的链表向右遍历就能快速取出所有满足条件的记录。而B树的叶子节点之间没有这种指针连接范围查询只能中序遍历效率差很多。除此之外B树所有数据都在叶子节点上查询任何一条数据的路径长度几乎相同查询性能非常稳定。这个特性对数据库很重要因为SQL执行的耗时如果忽高忽低系统的整体延迟就会很难控制。2.3 哈希索引为什么只能做“配角”InnoDB索引默认使用B树但底层还有一个叫做自适应哈希索引Adaptive Hash Index的机制用于加速某些特定等值查询。注意它不能被用户主动指定只能由InnoDB根据运行情况自动构建。哈希索引的优势是等值查询能达到O(1)的查找速度这一点B树比不了。但它的缺点非常致命不支持范围查询、不支持部分匹配、数据无序。比如WHERE age 30这种SQL哈希索引完全帮不上忙。所以MySQL里的哈希索引更像一个补充角色绝大多数场景还是B树扛大梁。如果你用的是Memory引擎可以手动创建哈希索引但一般业务场景下不建议把这类表当成核心存储数据落到内存里重启就没了风险太大。3. 索引的几种常见类型别再傻傻分不清理解了B树再来看索引类型就会清晰很多。MySQL里有主键索引、唯一索引、普通索引、全文索引还有空间索引。实际业务里前三种用得最多全文索引在某些特定搜索场景下会用到空间索引则是GIS系统的专属。3.1 主键索引、唯一索引、普通索引的区别先说主键索引。在InnoDB里主键索引又叫聚簇索引Clustered Index它是表的“数据组织方式”而不仅仅是一个索引。表里的数据行本身就是按照主键的B树顺序存放在叶子节点上的叶子节点存的是整行数据。你可以把它理解成一本按页码排好序的书正文内容就是数据。唯一索引和主键索引很像都能保证字段值的唯一性但两者有重要区别一张表只能有一个主键索引但可以有很多个唯一索引主键不允许为NULL唯一索引允许一个NULL值在MySQL的某些隔离级别里多个NULL不冲突。普通索引就纯粹是“加速查询”的作用没有唯一性约束。你可以在任何字段上建普通索引甚至在一个字段上同时建普通索引和唯一索引不过现实中没人这么干纯属浪费空间。全文索引是另一种思路。它不是为了处理WHERE name abc这种等值或范围查询而是为了支持“在一大段文本里搜索关键词”这种全文检索需求。5.7版本之后InnoDB原生支持全文索引底层用的是倒排索引结构虽然功能不如Elasticsearch强大但处理简单的文章搜索够用了。3.2 聚簇索引与非聚簇索引二级索引的本质区别在InnoDB中除主键索引外的所有索引都叫二级索引Secondary Index也有人叫非聚簇索引。二级索引的B树叶子节点不存整行数据而是存“索引列的值 主键值”。这样设计的核心目的是节省空间避免每个索引都复制一份完整数据。但这也引出了一个非常重要的操作回表Bookmark Lookup。当你通过二级索引查数据时过程是先扫描二级索引的B树找到对应的主键值然后拿着主键值再去主键索引的B树里查一次才能拿到完整行数据。这就是回表。举个例子表user上有索引idx_name执行SELECT * FROM user WHERE name 张三时MySQL先通过idx_name找到张三对应的主键id然后再用这个id去主键索引中查询最终返回整行数据。整个过程涉及两次B树检索如果命中的行数很多回表成本会非常高。为了减少回表就出现了覆盖索引Covering Index的概念。如果SQL里查询的列恰好都在某个二级索引里MySQL就不需要回表了直接从二级索引的叶子节点拿到所有需要的数据。比如你有索引idx_name执行SELECT name, id FROM user WHERE name 张三二级索引里刚好有name和id就不需要回表了。这就是为什么很多优化建议里会说“不要用SELECT *尽量只查需要的列”因为你查的列越少覆盖索引被命中的概率就越大。3.3 复合索引的最左前缀匹配复合索引是指同时包含多个字段的索引比如idx_user_status(status, create_time)。它的B树先按第一列排序第一列相同的再按第二列排序以此类推。最左前缀匹配规则是复合索引的灵魂查询条件必须从索引的最左列开始连续匹配索引才会生效。比如上面那个复合索引WHERE status 1 AND create_time 2024-01-01可以用到索引WHERE create_time 2024-01-01则用不到因为跳过了最左列status。这个规则有两条扩展理解第一查询条件里的列顺序不重要MySQL优化器会帮你调整第二当你只匹配了一部分前缀列时后续列虽然不能继续用于查找定位但可以用在“索引排序”上避免额外的文件排序。比如WHERE status 1 ORDER BY create_timestatus用于定位create_time这个有序字段直接就能用于排序效果非常好。在设计复合索引时有一个经验法则把区分度高的字段放前面把常用于范围查询的字段放后面但这也不是绝对的还要结合实际查询频率来确定。我更倾向于根据业务里最频繁出现的SQL来反推索引设计而不是一上来就按“理论最优”建索引。4. 创建索引的实操套路与SQL细节知道了原理和类型接下来看如何动手。这一节会列出常见SQL语法、设计原则以及我实际建索引时的一些习惯。4.1 建索引的基本SQL语法在MySQL中你可以在建表时定义索引也可以后期通过ALTER TABLE或CREATE INDEX语句添加。-- 建表时创建索引 CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name), KEY idx_status_time (status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 后期添加索引 ALTER TABLE user ADD INDEX idx_name (name); ALTER TABLE user ADD UNIQUE INDEX uk_email (email);-- 删除索引 DROP INDEX idx_name ON user; ALTER TABLE user DROP INDEX uk_email;这里有几个细节值得注意。第一索引名在一个表里必须唯一虽然MySQL允许两个不同索引包含相同字段但这往往是冗余索引的根源。第二ALTER TABLE ADD INDEX在执行时会锁表数据量大时一定要小心建议使用在线DDL或者分批次处理否则线上业务会被阻塞。第三字段类型尽量选短的比如能用BIGINT就别用VARCHAR(255)因为索引比较的是字节数字段越短索引页能装下的键值越多树高越低。4.2 索引设计的一个核心原则区分度区分度这个概念简单说就是这个字段有多少种不同的值。比如性别字段只有“男/女/未知”三种值区分度极低翻遍索引页可能还要回表查大量数据建索引意义不大。相反用户邮箱、手机号这类字段几乎每条记录都不同区分度极高非常值得建索引。但在真实业务里你往往不能只靠区分度决定。比如订单表的“状态”字段区分度极低但业务里高频查询就是“查所有待支付订单”这时候仍然可以建索引。原因在于待支付订单在整体数据中的占比可能比较小MySQL优化器会评估是否走索引如果判断走索引需要访问的页面太多它会选择全表扫描。所以你看区分度不能单独决定索引的价值还要看“你过滤后的数据量在总数据量中的占比”。4.3 Explain执行计划怎么读创建索引之后怎么确认它生效了最直接的办法是用EXPLAIN查看执行计划。EXPLAIN SELECT * FROM user WHERE name 张三;输出结果里你需要重点关注的列包括列名含义需要警惕的地方type访问类型从好到差依次是system, const, eq_ref, ref, range, index, ALLkey实际使用的索引为NULL说明没走索引rows预估扫描行数和实际量级差距过大说明统计信息过期Extra额外信息出现Using filesort或Using temporary时通常要优化如果type是ALL说明全表扫描大表下极其危险。如果Extra里出现Using filesort说明排序没有用到索引MySQL额外做了文件排序数据量大时性能急剧下降。如果出现Using index恭喜这是覆盖索引性能最好。提示EXPLAIN在8.0版本中还可以用EXPLAIN ANALYZE来获取真实执行时间和更多运行时信息排障时比普通EXPLAIN更直观。4.4 索引下推一个容易被忽略的优化索引下推Index Condition PushdownICP是MySQL 5.6引入的一项优化。它的核心思想是在二级索引的B树扫描过程中直接把部分WHERE条件的判断下推到存储引擎层完成原本需要回表后再判断的条件现在在索引层就被过滤掉了。举个例子有复合索引idx_status_time(status, create_time)执行SELECT * FROM user WHERE status 1 AND create_time 2024-01-01。如果没有ICPInnoDB先根据status找到一批主键然后回表取出整行数据再去判断create_time条件。有了ICPcreate_time的判断会在索引扫描时提前过滤减少回表的次数。这个优化对减少随机IO非常有效。日常使用中你不需要手动开启ICP默认就是开启状态但理解它有助于你去分析那些“看起来没有走全索引但性能还不错”的执行计划。5. 索引失效的经典场景与排查技巧理论上理解了、索引也建了为什么有些SQL还是慢如蜗牛这一节是很多开发者的痛点索引失效。我整理了自己踩过和帮别人排查过的几类高频问题。5.1 那些让索引悄悄失效的写法第一类在索引列上做函数运算或计算。这是最常见的错误。比如WHERE YEAR(create_time) 2024虽然create_time上有索引但MySQL无法直接利用B树的有序性按“年份”定位只能把每条记录的create_time都取出来计算一遍再过滤索引自然失效。正确的写法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01。同类问题还包括WHERE id 1 100、WHERE name 张三 || name 李四这种写法都在某种程度上破坏了索引列本身的语义优化器没法正常使用索引。第二类隐式类型转换。如果字段是VARCHAR类型但查询条件里传了整型MySQL会把字段隐式转换为数字再比较导致索引失效。比如WHERE phone 13800138000如果phone是varchar类型这个查询会因为类型转换而无法走索引。解决方法是写SQL时保持字段类型和查询值类型一致WHERE phone 13800138000。这一点在你对接外部接口、拼接动态SQL时要格外小心。第三类对索引列使用LIKE通配符开头。LIKE %关键字%这种写法没法利用索引的有序性因为模糊匹配的位置在开头B树无法快速定位。但LIKE 关键字%是可以走索引的因为前缀确定的情况下B树能按范围去扫描。所以业务代码里如果必须用模糊查询要么把通配符放到末尾要么考虑用全文索引或外部搜索引擎。第四类使用OR连接条件时其中一个条件没索引。WHERE name 张三 OR status 1如果status没有索引优化器可能直接选择全表扫描因为需要把两个条件的结果做并集而status部分只能全表扫描导致整体走不了索引。长时间实践下来我发现很多团队把这类问题归为“优化器突发奇想”其实根因还是SQL写得不够严谨。改写方案是把OR拆成两个查询union all或者给条件里的每个字段都建上合适的索引。5.2 用Explain定位“慢”的源头遇到慢查询我一般按下面几步排查整个过程熟练之后一分钟内就能定位方向。第一步找到慢SQL。开启慢查询日志SET GLOBAL slow_query_log ON;并把long_query_time设置成一个你认为合理的阈值比如1秒。在生产环境我一般先看1秒以上SQL再逐步收窄。第二步对慢SQL执行EXPLAIN看有没有走索引。如果type是ALL优先确认是不是漏建索引。如果索引建了但仍全表扫描再分析是不是出现了上面提到的失效写法。第三步观察rows预估扫描行数和实际表行数的比例。如果优化器预估严重偏离实际可能是统计信息过期执行ANALYZE TABLE更新一下统计信息。注意rows只是预估值并不精确但它能反映大致量级。第四步检查Extra里的Using filesort和Using temporary。排序和去重往往是查询慢的另一大原因解决办法是让ORDER BY或GROUP BY的字段和索引顺序对齐。5.3 优化器“不用索引”的几种正当理由有一种情况让人很困惑索引明明存在条件也没做什么出格的事但EXPLAIN出来就是全表扫描。这时候不一定是你写错了而是优化器做了“理性判断”。当二级索引过滤出的行数占比很高时优化器认为回表成本太高干脆全表扫描顺序读盘更快。比如性别字段只有两个值查询男女各占一半哪怕建了索引优化器也会放弃它。这就是为什么我前面提到区分度太低的字段建索引意义不大。碰到这类问题解决思路有几个一是增加更多过滤条件让二级索引过滤后的结果集变小二是调整SQL逻辑避免大范围数据查询。5.4 排序、分组和连接时的索引利用索引不仅能加速WHERE过滤还能加速排序和分组。MySQL如果发现ORDER BY的字段与某个索引顺序一致就会直接利用索引的有序性返回结果不做filesort。同样GROUP BY也会先按索引顺序扫描减少临时表。但要注意ORDER BY多个字段时排序方向要一致才能利用索引。比如索引idx_status_time(status, create_time)ORDER BY status ASC, create_time DESC和索引顺序不一致优化器就很难直接利用可能产生filesort。你可以选择调整SQL排序方向或者调整索引定义如果业务真的需要双向排序MySQL 8.0支持索引列定义排序方向可以在建索引时指定。ALTER TABLE user ADD INDEX idx_status_time (status ASC, create_time DESC);这一功能在8.0版本已经支持低版本则无法直接在索引定义里指定方向。6. 索引带来的额外成本和运维事项索引不是免费的午餐。很多人忽略了一个基本事实索引虽然加速了读操作但拖慢了写操作。因为每次INSERT、UPDATE、DELETE不仅要改数据文件还要同步维护索引B树的结构。索引越多写放大越严重。6.1 为什么索引不是多多益善在一个高频写入的表上无节制地建索引会让写入性能急剧下降。你写一条记录可能要更新五六个索引每个索引对应一棵B树B树的节点一旦必要时还要做页分裂、合并代价不比读数据小。我见过一个崩溃案例一张日志表建了八个索引单条插入从几毫秒变成几十毫秒并发一上来直接打满数据库连接。所以我的原则是能用复合索引解决的就不要建多个单列索引真正高频的查询才需要覆盖索引低频统计查询可以走全表扫描或者汇总表大字段上的索引要谨慎比如TEXT列如果想加索引得指定前缀长度例如KEYidx_content(content(100))但这个场景如果能用ES或专门搜索引擎就不要硬杠MySQL。6.2 怎么发现和清理冗余索引冗余索引的表现形式很多最常见的是A、B两个单列索引和(A, B)复合索引同时存在。由于最左前缀匹配idx_a在查询条件只用到A时和idx_a_b是重复的这时候idx_a就是冗余索引。MySQL 8.0之前想看冗余索引需要依赖sys.schema_redundant_indexes视图这个视图在MySQL 5.7和8.0都有提供里面会直接给出重复的索引建议。SELECT * FROM sys.schema_redundant_indexes;清理冗余索引前一定要结合业务SQL做一次全量分析确认没有查询在依赖那个“冗余”索引。稳妥的做法是先用EXPLAIN验证移除后执行计划不变再在低峰期删除。删索引的过程同样会锁表所以也要在维护窗口执行或者使用支持在线DDL的工具。6.3 在线DDL与pt-oscMySQL的ALTER TABLE在5.6之前是极度危险的操作加了索引之后表会被锁住线上写入直接停止。5.6开始引入在线DDL一部分操作可以在不阻塞DML的情况下进行但内部还是要经历“构建临时表、拷贝数据、切换”的过程。对于超大表在线DDL仍然会产生大量IO和主从延迟。如果表已经很大比如几千万行甚至上亿行我会优先使用pt-online-schema-changept-osc这类工具。它的原理是通过触发器把增量数据同步到新表分批拷贝旧数据最后原子切换表名对业务的侵入时间大幅降低。不过使用pt-osc也有前提目标表必须有主键或唯一键且不支持外键约束使用前要评估清楚。7. MySQL索引面试高频题与避坑经验聊完实操和运维再来看看面试题。这部分不是让你背答案而是帮助你建立一套统一的索引知识体系不管面试官从哪个角度切入你都能用同一套逻辑去应对。7.1 常见问题怎么答问为什么InnoDB表必须建主键答因为InnoDB的数据行本身就是按聚簇索引组织的如果没有显式主键InnoDB会选择一个非空唯一索引作为主键如果也没有会生成一个隐藏的6字节ROWID作为主键。但隐藏主键对业务不可见会导致二级索引回表时无法利用业务主键同时每次插入都是随机顺序写入页分裂概率更大。所以最好指定一个递增的业务主键。问为什么主键推荐用自增整数而不是UUID答主键是聚簇索引的排序键如果用UUID做主键新插入的数据的主键值是无序的B树需要频繁在中间位置插入新记录导致大量页分裂和页重排产生碎片。而自增整数主键保证新纪录按顺序追加到B树末尾写入性能大为提升。如果你有分布式场景需要全局唯一ID可以用雪花算法生成有序的整数ID而不是直接用无规律字符串。问覆盖索引一定比回表快吗答大多数情况下是的因为覆盖索引避免了回表产生的随机IO。但要注意如果覆盖索引的字段过多索引本身的体积也会变大扫描阶段需要读取的页更多有可能出现“虽然不用回表但索引扫描本身就很慢”的极端场景。覆盖索引不是万能药尽量保证覆盖查询需要的列即可。问索引能提升UPDATE、DELETE性能吗答能前提是WHERE条件里的字段有索引。UPDATE和DELETE也是先定位记录再操作定位过程等价于SELECT所以走索引能大幅减少扫描行数。但索引数量本身会拖累UPDATE的写放大这个要区分清楚。7.2 从实际业务提炼的几条避坑经验第一条经验先统计SQL再设计索引。不要一建表就把每个字段都索引一遍。最好的做法是收集业务中高频SQL把它们的WHERE、ORDER BY、GROUP BY字段提取出来然后设计出一组复合索引来覆盖尽可能多的SQL。一个复合索引能覆盖三条SQL往往比三个单列索引更优雅。第二条经验定期检查慢查询日志。慢查询不是一次治理完就结束业务在不断变化数据量在增长原先几分钟的SQL慢慢会变成几分钟的噩梦。我的习惯是每周拉一次慢查询列表重点看那些执行次数高、平均耗时长的SQL分析是否需要加索引或改写SQL。第三条经验警惕“写多读少”的表的索引膨胀。日志、流水、埋点这类表历史数据很少被查询但表上如果误建了大量索引写入速度会被拖累。可以考虑按时间分表或分区老分区直接清理或归档从根上控制表的总行数。7.3 到底要不要用索引提示FORCE INDEX当优化器的选择不符合预期时有人会在SQL里强行写FORCE INDEX来指定索引。我的建议是能用统计信息、SQL改写解决的问题就不要滥用FORCE INDEX。因为一旦你写了强制索引后续数据分布变化、统计信息更新后这个“强扭的瓜”可能越来越不甜而且代码里的强制索引很难被后来人注意到属于隐形的技术债。我见过一个案例某团队为了绕过优化器的误判在一条核心SQL上加了FORCE INDEX结果半年后数据量翻倍那个索引的区分度大幅下降查询性能暴跌而因为强制索引的存在优化器想换路线都换不了。最终只能靠改写SQL结构才彻底解决。所以不到万不得已尽量让优化器做决策你只需要提供足够准确的统计信息和表结构。8. 一点收尾的折腾心得最后再分享一个我自己在排查慢SQL时的小习惯。拿到一个慢查询我从来不会只盯着EXPLAIN结果看而是先看表里数据总量和字段的区分度再结合SQL里的过滤条件估算最终结果集。先做到心中有数再去判断执行计划是否合理。这个方法看着笨但在真实业务里非常管用能帮你区分“优化器有问题”和“这个SQL本来就该慢”两种情况。还有一点索引相关的知识光靠背概念很容易忘最好的学习方式就是把公司里真实的慢查询拿来复盘。哪怕是一条很简单的查询只要你能说清楚为什么慢、索引该建在哪几个字段上、还能不能进一步改成覆盖索引这套思路熟练之后遇到更复杂的性能问题你也不会慌。MySQL索引这块内容说深很深说浅也很浅。我个人理解它本质上就是一套“空间换时间”的取舍逻辑。你只要把B树的结构、聚簇索引和二级索引的关系、最左前缀匹配这三个支点掌握住再结合日常的EXPLAIN实践去验证基本就能覆盖90%的工作场景。剩下的那些极端情况真遇到了再查再试也来得及。 MySQL索引底层原理与性能优化：从B+树到索引失效的全面解析
1. 开局先聊聊我对MySQL索引的实际感受做后端开发这些年我几乎每天都会和MySQL打交道。不管是刚入行时写的第一个商品查询接口还是后来负责的千万级订单表索引这个东西始终绕不开。很多人问我说索引到底该怎么理解我觉得可以用一个特别生活化的比喻一本几百页的技术书你查某个概念时是愿意一页页翻还是先翻到书末尾的“索引页”根据页码直接跳到对应内容明显是后者。MySQL里的索引干的就是这件“按图索骥”的事。但索引的理解绝不只是“查得快”这么简单。如果你只停留在“给查询慢的字段加个索引”这种层面后面会遇到很多麻烦索引加了但查询还是慢、写操作被拖垮、磁盘空间膨胀、数据量一大就卡死。我自己就在生产环境里踩过好几次坑有的坑修起来非常痛苦。这篇就把我对MySQL索引的理解系统性地拆一遍从底层的B树数据结构到不同类型索引的使用场景再到索引失效和性能调优尽量用大白话讲清楚并用真实场景告诉你每一步该怎么处理。这篇文章适合正在学习数据库原理的开发者也适合工作两三年但没系统整理过索引知识的后端工程师如果你正在准备数据库相关的面试里面也会有不少可以直接拿来用的理解角度。2. 索引底层的核心数据结构为什么偏偏是B树很多人一说索引就会想到B树但你要问他为什么MySQL的InnoDB引擎选B树而不选别的他可能就支支吾吾了。这一节把底层逻辑彻底讲透理解了这部分后面所有关于索引的“玄学”都会变得非常自然。2.1 从“数据在磁盘上怎么读”说起要理解数据结构的选型首先得知道一个基本事实数据是存在磁盘上的磁盘读数据不是按“字节”来而是按“页”来。MySQL默认一页是16KB意思是哪怕你只查一条记录InnoDB也会把包含这条记录的整页数据从磁盘加载到内存。磁盘IO是个极度耗时的事相比内存操作可能要慢几个数量级。所以数据库设计的第一原则就是尽量减少磁盘IO的次数。顺着这个思路索引这个数据结构要回答的核心问题就是给定一个查找条件我最多读几个磁盘页就能找到目标数据如果数据结构是链表查找一个元素最坏情况要遍历全表N条数据就是N次磁盘IO这显然不行。如果是普通二叉树在数据分布不均匀时可能退化成链表也不行。如果是平衡二叉树AVL树虽然树高控制得不错但每个节点只能存一个关键字节点多了树照样很深每次下降一层都是一次磁盘IO。这里你可以把“树的高度”直接理解为“磁盘IO次数”高度越低IO越少查询越快。2.2 B树到底赢在哪B树的出现先把“一个节点只存一个关键字”这个问题解决了。B树是一种多路平衡查找树每个节点可以存多个关键字和多个子节点指针树高大幅下降。比如一个高度为3的B树就能存下几百万条数据这意味着最多3次磁盘IO就能定位到目标页这已经非常优秀了。那InnoDB为什么不用B树而用B树区别主要在两点。第一B树的所有数据都存放在叶子节点非叶子节点只存索引键值不存数据本身。这样每个非叶子节点能容纳更多关键字树就变得更矮更宽。InnoDB一页16KB非叶子节点里每条索引记录可能就占几十字节一个节点存几百上千个关键字很轻松树高基本保持在2到3层。对于千万级数据的表从根节点到叶子节点通常只需要2到3次磁盘IO这就是B树在查询效率上的硬实力。第二B树的叶子节点之间通过双向指针串成了一个有序链表天然支持范围查询。比如你要查“价格在100到200之间的商品”B树找到100的位置之后顺着叶子节点的链表向右遍历就能快速取出所有满足条件的记录。而B树的叶子节点之间没有这种指针连接范围查询只能中序遍历效率差很多。除此之外B树所有数据都在叶子节点上查询任何一条数据的路径长度几乎相同查询性能非常稳定。这个特性对数据库很重要因为SQL执行的耗时如果忽高忽低系统的整体延迟就会很难控制。2.3 哈希索引为什么只能做“配角”InnoDB索引默认使用B树但底层还有一个叫做自适应哈希索引Adaptive Hash Index的机制用于加速某些特定等值查询。注意它不能被用户主动指定只能由InnoDB根据运行情况自动构建。哈希索引的优势是等值查询能达到O(1)的查找速度这一点B树比不了。但它的缺点非常致命不支持范围查询、不支持部分匹配、数据无序。比如WHERE age 30这种SQL哈希索引完全帮不上忙。所以MySQL里的哈希索引更像一个补充角色绝大多数场景还是B树扛大梁。如果你用的是Memory引擎可以手动创建哈希索引但一般业务场景下不建议把这类表当成核心存储数据落到内存里重启就没了风险太大。3. 索引的几种常见类型别再傻傻分不清理解了B树再来看索引类型就会清晰很多。MySQL里有主键索引、唯一索引、普通索引、全文索引还有空间索引。实际业务里前三种用得最多全文索引在某些特定搜索场景下会用到空间索引则是GIS系统的专属。3.1 主键索引、唯一索引、普通索引的区别先说主键索引。在InnoDB里主键索引又叫聚簇索引Clustered Index它是表的“数据组织方式”而不仅仅是一个索引。表里的数据行本身就是按照主键的B树顺序存放在叶子节点上的叶子节点存的是整行数据。你可以把它理解成一本按页码排好序的书正文内容就是数据。唯一索引和主键索引很像都能保证字段值的唯一性但两者有重要区别一张表只能有一个主键索引但可以有很多个唯一索引主键不允许为NULL唯一索引允许一个NULL值在MySQL的某些隔离级别里多个NULL不冲突。普通索引就纯粹是“加速查询”的作用没有唯一性约束。你可以在任何字段上建普通索引甚至在一个字段上同时建普通索引和唯一索引不过现实中没人这么干纯属浪费空间。全文索引是另一种思路。它不是为了处理WHERE name abc这种等值或范围查询而是为了支持“在一大段文本里搜索关键词”这种全文检索需求。5.7版本之后InnoDB原生支持全文索引底层用的是倒排索引结构虽然功能不如Elasticsearch强大但处理简单的文章搜索够用了。3.2 聚簇索引与非聚簇索引二级索引的本质区别在InnoDB中除主键索引外的所有索引都叫二级索引Secondary Index也有人叫非聚簇索引。二级索引的B树叶子节点不存整行数据而是存“索引列的值 主键值”。这样设计的核心目的是节省空间避免每个索引都复制一份完整数据。但这也引出了一个非常重要的操作回表Bookmark Lookup。当你通过二级索引查数据时过程是先扫描二级索引的B树找到对应的主键值然后拿着主键值再去主键索引的B树里查一次才能拿到完整行数据。这就是回表。举个例子表user上有索引idx_name执行SELECT * FROM user WHERE name 张三时MySQL先通过idx_name找到张三对应的主键id然后再用这个id去主键索引中查询最终返回整行数据。整个过程涉及两次B树检索如果命中的行数很多回表成本会非常高。为了减少回表就出现了覆盖索引Covering Index的概念。如果SQL里查询的列恰好都在某个二级索引里MySQL就不需要回表了直接从二级索引的叶子节点拿到所有需要的数据。比如你有索引idx_name执行SELECT name, id FROM user WHERE name 张三二级索引里刚好有name和id就不需要回表了。这就是为什么很多优化建议里会说“不要用SELECT *尽量只查需要的列”因为你查的列越少覆盖索引被命中的概率就越大。3.3 复合索引的最左前缀匹配复合索引是指同时包含多个字段的索引比如idx_user_status(status, create_time)。它的B树先按第一列排序第一列相同的再按第二列排序以此类推。最左前缀匹配规则是复合索引的灵魂查询条件必须从索引的最左列开始连续匹配索引才会生效。比如上面那个复合索引WHERE status 1 AND create_time 2024-01-01可以用到索引WHERE create_time 2024-01-01则用不到因为跳过了最左列status。这个规则有两条扩展理解第一查询条件里的列顺序不重要MySQL优化器会帮你调整第二当你只匹配了一部分前缀列时后续列虽然不能继续用于查找定位但可以用在“索引排序”上避免额外的文件排序。比如WHERE status 1 ORDER BY create_timestatus用于定位create_time这个有序字段直接就能用于排序效果非常好。在设计复合索引时有一个经验法则把区分度高的字段放前面把常用于范围查询的字段放后面但这也不是绝对的还要结合实际查询频率来确定。我更倾向于根据业务里最频繁出现的SQL来反推索引设计而不是一上来就按“理论最优”建索引。4. 创建索引的实操套路与SQL细节知道了原理和类型接下来看如何动手。这一节会列出常见SQL语法、设计原则以及我实际建索引时的一些习惯。4.1 建索引的基本SQL语法在MySQL中你可以在建表时定义索引也可以后期通过ALTER TABLE或CREATE INDEX语句添加。-- 建表时创建索引 CREATE TABLE user ( id int NOT NULL AUTO_INCREMENT, name varchar(64) NOT NULL, status tinyint NOT NULL DEFAULT 0, create_time datetime DEFAULT NULL, PRIMARY KEY (id), KEY idx_name (name), KEY idx_status_time (status, create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;-- 后期添加索引 ALTER TABLE user ADD INDEX idx_name (name); ALTER TABLE user ADD UNIQUE INDEX uk_email (email);-- 删除索引 DROP INDEX idx_name ON user; ALTER TABLE user DROP INDEX uk_email;这里有几个细节值得注意。第一索引名在一个表里必须唯一虽然MySQL允许两个不同索引包含相同字段但这往往是冗余索引的根源。第二ALTER TABLE ADD INDEX在执行时会锁表数据量大时一定要小心建议使用在线DDL或者分批次处理否则线上业务会被阻塞。第三字段类型尽量选短的比如能用BIGINT就别用VARCHAR(255)因为索引比较的是字节数字段越短索引页能装下的键值越多树高越低。4.2 索引设计的一个核心原则区分度区分度这个概念简单说就是这个字段有多少种不同的值。比如性别字段只有“男/女/未知”三种值区分度极低翻遍索引页可能还要回表查大量数据建索引意义不大。相反用户邮箱、手机号这类字段几乎每条记录都不同区分度极高非常值得建索引。但在真实业务里你往往不能只靠区分度决定。比如订单表的“状态”字段区分度极低但业务里高频查询就是“查所有待支付订单”这时候仍然可以建索引。原因在于待支付订单在整体数据中的占比可能比较小MySQL优化器会评估是否走索引如果判断走索引需要访问的页面太多它会选择全表扫描。所以你看区分度不能单独决定索引的价值还要看“你过滤后的数据量在总数据量中的占比”。4.3 Explain执行计划怎么读创建索引之后怎么确认它生效了最直接的办法是用EXPLAIN查看执行计划。EXPLAIN SELECT * FROM user WHERE name 张三;输出结果里你需要重点关注的列包括列名含义需要警惕的地方type访问类型从好到差依次是system, const, eq_ref, ref, range, index, ALLkey实际使用的索引为NULL说明没走索引rows预估扫描行数和实际量级差距过大说明统计信息过期Extra额外信息出现Using filesort或Using temporary时通常要优化如果type是ALL说明全表扫描大表下极其危险。如果Extra里出现Using filesort说明排序没有用到索引MySQL额外做了文件排序数据量大时性能急剧下降。如果出现Using index恭喜这是覆盖索引性能最好。提示EXPLAIN在8.0版本中还可以用EXPLAIN ANALYZE来获取真实执行时间和更多运行时信息排障时比普通EXPLAIN更直观。4.4 索引下推一个容易被忽略的优化索引下推Index Condition PushdownICP是MySQL 5.6引入的一项优化。它的核心思想是在二级索引的B树扫描过程中直接把部分WHERE条件的判断下推到存储引擎层完成原本需要回表后再判断的条件现在在索引层就被过滤掉了。举个例子有复合索引idx_status_time(status, create_time)执行SELECT * FROM user WHERE status 1 AND create_time 2024-01-01。如果没有ICPInnoDB先根据status找到一批主键然后回表取出整行数据再去判断create_time条件。有了ICPcreate_time的判断会在索引扫描时提前过滤减少回表的次数。这个优化对减少随机IO非常有效。日常使用中你不需要手动开启ICP默认就是开启状态但理解它有助于你去分析那些“看起来没有走全索引但性能还不错”的执行计划。5. 索引失效的经典场景与排查技巧理论上理解了、索引也建了为什么有些SQL还是慢如蜗牛这一节是很多开发者的痛点索引失效。我整理了自己踩过和帮别人排查过的几类高频问题。5.1 那些让索引悄悄失效的写法第一类在索引列上做函数运算或计算。这是最常见的错误。比如WHERE YEAR(create_time) 2024虽然create_time上有索引但MySQL无法直接利用B树的有序性按“年份”定位只能把每条记录的create_time都取出来计算一遍再过滤索引自然失效。正确的写法是改成范围条件WHERE create_time 2024-01-01 AND create_time 2025-01-01。同类问题还包括WHERE id 1 100、WHERE name 张三 || name 李四这种写法都在某种程度上破坏了索引列本身的语义优化器没法正常使用索引。第二类隐式类型转换。如果字段是VARCHAR类型但查询条件里传了整型MySQL会把字段隐式转换为数字再比较导致索引失效。比如WHERE phone 13800138000如果phone是varchar类型这个查询会因为类型转换而无法走索引。解决方法是写SQL时保持字段类型和查询值类型一致WHERE phone 13800138000。这一点在你对接外部接口、拼接动态SQL时要格外小心。第三类对索引列使用LIKE通配符开头。LIKE %关键字%这种写法没法利用索引的有序性因为模糊匹配的位置在开头B树无法快速定位。但LIKE 关键字%是可以走索引的因为前缀确定的情况下B树能按范围去扫描。所以业务代码里如果必须用模糊查询要么把通配符放到末尾要么考虑用全文索引或外部搜索引擎。第四类使用OR连接条件时其中一个条件没索引。WHERE name 张三 OR status 1如果status没有索引优化器可能直接选择全表扫描因为需要把两个条件的结果做并集而status部分只能全表扫描导致整体走不了索引。长时间实践下来我发现很多团队把这类问题归为“优化器突发奇想”其实根因还是SQL写得不够严谨。改写方案是把OR拆成两个查询union all或者给条件里的每个字段都建上合适的索引。5.2 用Explain定位“慢”的源头遇到慢查询我一般按下面几步排查整个过程熟练之后一分钟内就能定位方向。第一步找到慢SQL。开启慢查询日志SET GLOBAL slow_query_log ON;并把long_query_time设置成一个你认为合理的阈值比如1秒。在生产环境我一般先看1秒以上SQL再逐步收窄。第二步对慢SQL执行EXPLAIN看有没有走索引。如果type是ALL优先确认是不是漏建索引。如果索引建了但仍全表扫描再分析是不是出现了上面提到的失效写法。第三步观察rows预估扫描行数和实际表行数的比例。如果优化器预估严重偏离实际可能是统计信息过期执行ANALYZE TABLE更新一下统计信息。注意rows只是预估值并不精确但它能反映大致量级。第四步检查Extra里的Using filesort和Using temporary。排序和去重往往是查询慢的另一大原因解决办法是让ORDER BY或GROUP BY的字段和索引顺序对齐。5.3 优化器“不用索引”的几种正当理由有一种情况让人很困惑索引明明存在条件也没做什么出格的事但EXPLAIN出来就是全表扫描。这时候不一定是你写错了而是优化器做了“理性判断”。当二级索引过滤出的行数占比很高时优化器认为回表成本太高干脆全表扫描顺序读盘更快。比如性别字段只有两个值查询男女各占一半哪怕建了索引优化器也会放弃它。这就是为什么我前面提到区分度太低的字段建索引意义不大。碰到这类问题解决思路有几个一是增加更多过滤条件让二级索引过滤后的结果集变小二是调整SQL逻辑避免大范围数据查询。5.4 排序、分组和连接时的索引利用索引不仅能加速WHERE过滤还能加速排序和分组。MySQL如果发现ORDER BY的字段与某个索引顺序一致就会直接利用索引的有序性返回结果不做filesort。同样GROUP BY也会先按索引顺序扫描减少临时表。但要注意ORDER BY多个字段时排序方向要一致才能利用索引。比如索引idx_status_time(status, create_time)ORDER BY status ASC, create_time DESC和索引顺序不一致优化器就很难直接利用可能产生filesort。你可以选择调整SQL排序方向或者调整索引定义如果业务真的需要双向排序MySQL 8.0支持索引列定义排序方向可以在建索引时指定。ALTER TABLE user ADD INDEX idx_status_time (status ASC, create_time DESC);这一功能在8.0版本已经支持低版本则无法直接在索引定义里指定方向。6. 索引带来的额外成本和运维事项索引不是免费的午餐。很多人忽略了一个基本事实索引虽然加速了读操作但拖慢了写操作。因为每次INSERT、UPDATE、DELETE不仅要改数据文件还要同步维护索引B树的结构。索引越多写放大越严重。6.1 为什么索引不是多多益善在一个高频写入的表上无节制地建索引会让写入性能急剧下降。你写一条记录可能要更新五六个索引每个索引对应一棵B树B树的节点一旦必要时还要做页分裂、合并代价不比读数据小。我见过一个崩溃案例一张日志表建了八个索引单条插入从几毫秒变成几十毫秒并发一上来直接打满数据库连接。所以我的原则是能用复合索引解决的就不要建多个单列索引真正高频的查询才需要覆盖索引低频统计查询可以走全表扫描或者汇总表大字段上的索引要谨慎比如TEXT列如果想加索引得指定前缀长度例如KEYidx_content(content(100))但这个场景如果能用ES或专门搜索引擎就不要硬杠MySQL。6.2 怎么发现和清理冗余索引冗余索引的表现形式很多最常见的是A、B两个单列索引和(A, B)复合索引同时存在。由于最左前缀匹配idx_a在查询条件只用到A时和idx_a_b是重复的这时候idx_a就是冗余索引。MySQL 8.0之前想看冗余索引需要依赖sys.schema_redundant_indexes视图这个视图在MySQL 5.7和8.0都有提供里面会直接给出重复的索引建议。SELECT * FROM sys.schema_redundant_indexes;清理冗余索引前一定要结合业务SQL做一次全量分析确认没有查询在依赖那个“冗余”索引。稳妥的做法是先用EXPLAIN验证移除后执行计划不变再在低峰期删除。删索引的过程同样会锁表所以也要在维护窗口执行或者使用支持在线DDL的工具。6.3 在线DDL与pt-oscMySQL的ALTER TABLE在5.6之前是极度危险的操作加了索引之后表会被锁住线上写入直接停止。5.6开始引入在线DDL一部分操作可以在不阻塞DML的情况下进行但内部还是要经历“构建临时表、拷贝数据、切换”的过程。对于超大表在线DDL仍然会产生大量IO和主从延迟。如果表已经很大比如几千万行甚至上亿行我会优先使用pt-online-schema-changept-osc这类工具。它的原理是通过触发器把增量数据同步到新表分批拷贝旧数据最后原子切换表名对业务的侵入时间大幅降低。不过使用pt-osc也有前提目标表必须有主键或唯一键且不支持外键约束使用前要评估清楚。7. MySQL索引面试高频题与避坑经验聊完实操和运维再来看看面试题。这部分不是让你背答案而是帮助你建立一套统一的索引知识体系不管面试官从哪个角度切入你都能用同一套逻辑去应对。7.1 常见问题怎么答问为什么InnoDB表必须建主键答因为InnoDB的数据行本身就是按聚簇索引组织的如果没有显式主键InnoDB会选择一个非空唯一索引作为主键如果也没有会生成一个隐藏的6字节ROWID作为主键。但隐藏主键对业务不可见会导致二级索引回表时无法利用业务主键同时每次插入都是随机顺序写入页分裂概率更大。所以最好指定一个递增的业务主键。问为什么主键推荐用自增整数而不是UUID答主键是聚簇索引的排序键如果用UUID做主键新插入的数据的主键值是无序的B树需要频繁在中间位置插入新记录导致大量页分裂和页重排产生碎片。而自增整数主键保证新纪录按顺序追加到B树末尾写入性能大为提升。如果你有分布式场景需要全局唯一ID可以用雪花算法生成有序的整数ID而不是直接用无规律字符串。问覆盖索引一定比回表快吗答大多数情况下是的因为覆盖索引避免了回表产生的随机IO。但要注意如果覆盖索引的字段过多索引本身的体积也会变大扫描阶段需要读取的页更多有可能出现“虽然不用回表但索引扫描本身就很慢”的极端场景。覆盖索引不是万能药尽量保证覆盖查询需要的列即可。问索引能提升UPDATE、DELETE性能吗答能前提是WHERE条件里的字段有索引。UPDATE和DELETE也是先定位记录再操作定位过程等价于SELECT所以走索引能大幅减少扫描行数。但索引数量本身会拖累UPDATE的写放大这个要区分清楚。7.2 从实际业务提炼的几条避坑经验第一条经验先统计SQL再设计索引。不要一建表就把每个字段都索引一遍。最好的做法是收集业务中高频SQL把它们的WHERE、ORDER BY、GROUP BY字段提取出来然后设计出一组复合索引来覆盖尽可能多的SQL。一个复合索引能覆盖三条SQL往往比三个单列索引更优雅。第二条经验定期检查慢查询日志。慢查询不是一次治理完就结束业务在不断变化数据量在增长原先几分钟的SQL慢慢会变成几分钟的噩梦。我的习惯是每周拉一次慢查询列表重点看那些执行次数高、平均耗时长的SQL分析是否需要加索引或改写SQL。第三条经验警惕“写多读少”的表的索引膨胀。日志、流水、埋点这类表历史数据很少被查询但表上如果误建了大量索引写入速度会被拖累。可以考虑按时间分表或分区老分区直接清理或归档从根上控制表的总行数。7.3 到底要不要用索引提示FORCE INDEX当优化器的选择不符合预期时有人会在SQL里强行写FORCE INDEX来指定索引。我的建议是能用统计信息、SQL改写解决的问题就不要滥用FORCE INDEX。因为一旦你写了强制索引后续数据分布变化、统计信息更新后这个“强扭的瓜”可能越来越不甜而且代码里的强制索引很难被后来人注意到属于隐形的技术债。我见过一个案例某团队为了绕过优化器的误判在一条核心SQL上加了FORCE INDEX结果半年后数据量翻倍那个索引的区分度大幅下降查询性能暴跌而因为强制索引的存在优化器想换路线都换不了。最终只能靠改写SQL结构才彻底解决。所以不到万不得已尽量让优化器做决策你只需要提供足够准确的统计信息和表结构。8. 一点收尾的折腾心得最后再分享一个我自己在排查慢SQL时的小习惯。拿到一个慢查询我从来不会只盯着EXPLAIN结果看而是先看表里数据总量和字段的区分度再结合SQL里的过滤条件估算最终结果集。先做到心中有数再去判断执行计划是否合理。这个方法看着笨但在真实业务里非常管用能帮你区分“优化器有问题”和“这个SQL本来就该慢”两种情况。还有一点索引相关的知识光靠背概念很容易忘最好的学习方式就是把公司里真实的慢查询拿来复盘。哪怕是一条很简单的查询只要你能说清楚为什么慢、索引该建在哪几个字段上、还能不能进一步改成覆盖索引这套思路熟练之后遇到更复杂的性能问题你也不会慌。MySQL索引这块内容说深很深说浅也很浅。我个人理解它本质上就是一套“空间换时间”的取舍逻辑。你只要把B树的结构、聚簇索引和二级索引的关系、最左前缀匹配这三个支点掌握住再结合日常的EXPLAIN实践去验证基本就能覆盖90%的工作场景。剩下的那些极端情况真遇到了再查再试也来得及。