1. 先搞清楚索引到底在解决什么“钱”的问题先说个我早年踩过的坑。有张业务表跑了一年多数据量到600万行左右线上开始出现接口超时报警。排查下来是一条按create_time和user_id过滤的查询全表扫描要2到3秒。当时第一反应是“加个索引不就行了”结果加了以后还是慢后来才发现建索引的思路完全错了。从那之后我开始认真研究索引的底层逻辑越往后越觉得不懂索引原理的人写SQL是“能跑就行”懂索引原理的人写SQL是“在设计数据访问路径”。索引的本质非常简单用额外的存储空间换取查询时磁盘IO次数和CPU计算量的减少。MySQL的数据最终落在磁盘上磁盘随机读的速度比内存慢几个数量级——内存纳秒级磁盘毫秒级看似只差一个数量级实际上差了上万倍。一条查询如果全表扫意味着要把磁盘上每一页数据都读进内存再逐行判断这个成本在数据量小的时候无所谓数据量大了就是灾难。索引做的事情就是建立一个“目录”让MySQL不必从头翻书而是按目录直接定位到目标数据所在的页。类比一下一本书500页你要找“索引失效”这个词没有目录你就得从第1页翻到第500页。有目录你翻到目录页找到“索引失效”在第310页直接翻过去就行了。MySQL的InnoDB引擎里这个“目录”就是一棵B树。搞清楚了这个核心逻辑你就能理解接下来所有关于索引的规则——为什么主键要短为什么联合索引有最左前缀为什么LIKE %xx%不走索引。所有规则都不是死记硬背而是从B树的结构里推出来的。这篇文章我尽量把原理、规则、实战从头到尾讲透适合刚接触MySQL的新人建立完整认知也适合写了两三年SQL但一直没系统梳理过索引的人查漏补缺。2. 一张图理解B树为什么索引结构选了它2.1 B树的结构本质InnoDB的索引结构是B树这是一个多路平衡搜索树。理解它先抓住三个关键点第一数据只存在叶子节点。非叶子节点只存索引键值和指向子节点的指针不存真实数据。这意味着同样大小的一个磁盘页InnoDB默认16KB非叶子节点能放下更多的键值树的高度就能控制得更低。一个3层的B树大概能存上千万条记录也就是说一次查询最多做3次磁盘IO就能定位到数据页这个成本非常低。第二叶子节点之间用双向链表相连。这个设计直接服务于范围查询。你查WHERE id BETWEEN 100 AND 200MySQL先在树里找到id100所在的叶子节点然后顺着链表往后扫就行不需要回头重新从根节点走。第三所有叶子节点在同一层。这个特性决定了B树是天然平衡的任何一条数据的查询路径长度都一样不会出现某条数据查得快、另一条查得慢的极端情况。对比一下二叉树如果数据插入顺序不理想二叉树可能会退化成链表B树通过节点的分裂合并机制避免了这个问题。2.2 为什么不用Hash索引为什么不用跳表很多人有疑问Hash索引查找是O(1)复杂度按理说比B树的O(logN)快为什么InnoDB默认不用Hash因为Hash索引有三个硬伤无法支持范围查询。Hash表只能做等值匹配、、BETWEEN这类条件在Hash表上等于全表扫。无法利用索引排序。Hash表的存储顺序跟键值逻辑顺序无关ORDER BY没法走索引。Hash冲突的处理有额外开销。冲突多的时候性能会明显劣化。跳表Skip List在Redis里用得多它其实能支持范围查询但MySQL没有选择它作为InnoDB的索引结构。核心原因是B树对磁盘IO更友好B树一个节点对应一个磁盘页树的层高决定了IO次数跳表是链表结构节点在磁盘上的物理分布可能很分散遍历时Cache命中率不如B树。而且B树因为节点内部键值有序配合顺序IO的预读特性扫描效率更高。简单说MySQL面对的是磁盘Redis面对的是内存面向的介质不同选择的结构就不同。2.3 聚簇索引与非聚簇索引的根本差异InnoDB的每张表都有一个聚簇索引这个索引的叶子节点直接存整行数据。表数据本身就是按主键顺序组织的所以InnoDB表也叫索引组织表。你建了主键主键索引就是聚簇索引你没建主键InnoDB会找一个无重复的列做隐式主键实在找不到生成一个隐藏的ROWID做主键。二级索引也叫非聚簇索引、辅助索引的叶子节点存的是索引键值 主键值不存整行数据。查询时如果索引键不能满足全部的字段需求MySQL拿到主键之后还要再回聚簇索引查一次这个过程就叫回表。这两者的区别直接引出一个设计原则主键不要用太长的字段。因为二级索引的叶子节点都带着主键值主键越大每个二级索引占用的空间就越大缓存能放下的索引页就越少IO次数就越多。用自增BIGINT做主键是大部分业务场景下最稳妥的选择。3. 六种索引类型逐个拆解别再傻傻分不清3.1 普通索引与唯一索引差的不只是约束普通索引INDEX是最基础的索引类型没有任何限制只是为了加速查询。唯一索引UNIQUE INDEX在普通索引的基础上加了约束索引列的值不能重复这是它最核心的价值。但从查询性能角度两者在B树上的读取方式几乎一样真正差别在于写操作。普通索引插入数据时只需要考虑B树的节点分裂合并唯一索引还要额外检查唯一性约束这个检查不是简单的“值有没有”而是要判断有没有冲突。这里有个容易被人忽略的点通常建议在唯一性有业务诉求的列上建唯一索引。如果你在业务上已经能保证列值不重复比如用户手机号那就直接建唯一索引不要建普通索引省一次隐含的重复检查逻辑。反过来如果业务上允许重复那就别为了“将来可能唯一”去建唯一索引插入性能会被拖累。3.2 主键索引每张表的定海神针主键索引是特殊的唯一索引区别在于它不仅约束唯一性还必须是非空的。更关键的是在InnoDB里它是聚簇索引直接决定表数据的物理组织方式。建主键时优先考虑这几个原则使用自增BIGINT避免使用UUID这类随机字符串。UUID不仅长而且随机插入时B树节点分裂频繁页碎片多整体写入性能明显不如自增。自增值顺序递增新数据永远插在树的最右边页分裂次数最少。主键越短越好。前面说过二级索引的叶子节点都存主键值主键长度直接影响所有二级索引的体积。能用单列主键就别用联合主键。联合主键会让二级索引变大还会让外键关联变得更复杂。3.3 联合索引最被低估的一类索引联合索引也叫多列索引是在多个列上建立的索引。它遵循最左前缀原则MySQL会从联合索引的最左列开始逐列匹配查询条件。比如建了(a, b, c)联合索引那么这个索引能加速的查询组合是aa, ba, b, c无法直接加速b、c、b, c开头的查询。这里有个常见的认知误区很多人以为联合索引要把查询里所有的WHERE列都放进去其实是先看查询条件里有哪些列再按“可选择性最高、最常作为过滤条件、出现在ORDER BY或GROUP BY中”的顺序来安排索引列的顺序。具体的选列策略我在第5部分展开。提示联合索引的列顺序消耗的是从左到右的匹配能力排最前面的列决定索引的起点。设计时优先考虑等值查询的列再考虑范围查询的列最后才考虑排序的列。3.4 全文索引中文搜索的正确打开方式全文索引FULLTEXT用在LIKE %keyword%这种模糊匹配场景。LIKE %xxx%在普通索引上没法利用B树的有序性只能全表扫而全文索引可以基于分词来匹配。但要注意MySQL的全文索引分词对中文支持不算好中文没有天然的空格分词默认分词器对中文的切分效果很一般。如果你的业务是中文全文搜索建议用专业的搜索引擎比如ElasticsearchMySQL自带的全文索引更适合英文场景或者数据量不大、搜索需求非常简单的场景。InnoDB从MySQL 5.6开始支持全文索引创建方式ALTER TABLE article ADD FULLTEXT INDEX ft_content (title, body);3.5 前缀索引处理超长字符串字段的妥协方案遇到VARCHAR(255)以上、甚至TEXT类型的字段直接建全列索引会导致索引体积巨大而且B树节点能容纳的键值数量大幅下降。折中方案是取字段的前N个字符建索引即前缀索引。ALTER TABLE user ADD INDEX idx_email (email(20));选N的方式很讲究目标是用尽量短的前缀区分尽量多的行。通常做法是SELECT COUNT(DISTINCT LEFT(email, 10)) AS c10, COUNT(DISTINCT LEFT(email, 15)) AS c15, COUNT(DISTINCT LEFT(email, 20)) AS c20, COUNT(*) AS total FROM user;选一个前缀长度让COUNT(DISTINCT LEFT(col, N))接近COUNT(*)又不至于太长。举个例子如果c15已经有95%以上的区分度那email(15)就够了不必用20。前缀索引有个限制无法用于ORDER BY和GROUP BY也不能用于覆盖索引。这些场景下需要考虑放弃前缀索引或者用生成列配合索引解决。3.6 降序索引解决ORDER BY DESC的排序痛点MySQL 8.0之前索引里的键值只能按升序排列。ORDER BY col DESC的时候MySQL要么反向扫描索引要么做文件排序效率都不理想。8.0开始支持降序索引可以按实际查询的排序方向建索引。ALTER TABLE orders ADD INDEX idx_user_time (user_id ASC, create_time DESC);这个语法在联合索引里特别有用user_id等值匹配走升序create_time排序走降序完美匹配查询的需求。4. 索引在磁盘上的物理组织索引表空间与页的分配4.1 MySQL的索引到底存放在哪里索引不是内存里的概念它和表数据一样要落盘。InnoDB的表由表结构定义文件.frm或字典信息和表空间文件组成。共享表空间模式下所有表的数据和索引都放在ibdata1文件里独立表空间模式下每张表对应一个.ibd文件数据和索引都存在这个文件里。检查当前模式的命令SHOW VARIABLES LIKE innodb_file_per_table;开启后一个orders.ibd文件里既包含聚簇索引的B树也包含所有二级索引的B树。通过ibd2sdi工具可以查看表空间里的索引结构信息。4.2 页的结构与索引树的生长索引树的最小存储单位是页Page默认16KB。每次从磁盘读数据最少读一页。页内部是一个有序的键值数组页与页之间通过指针串联。当不断插入数据一个页满了之后InnoDB会触发页分裂把一半数据移到新页。这个操作会让树的层级增加层级增加意味着查询时要多一次磁盘IO。所以对写入频繁的表索引不是越多越好每个索引都意味着每次插入都要更新对应的B树页分裂的开销是实打实的。4.3 索引碎片的产生与重建频繁的增删改会让索引页产生碎片物理上连续的页逻辑上可能已经有很多空位。碎片率高了之后扫描索引需要读更多的页性能就下来了。判断碎片率的办法SELECT table_name, index_name, stat_value * innodb_page_size / 1024 / 1024 AS index_size_mb FROM mysql.innodb_index_stats WHERE table_name orders;消除碎片的方式通常是重建表或重建索引ALTER TABLE orders ENGINE InnoDB;这个操作会把表重建一遍重新压缩索引。注意它会锁表在线执行建议用pt-online-schema-change这类工具滚动执行避免业务停摆。5. 联合索引设计实战从WHERE a AND b说起5.1 “该不该建联合索引”的决策链路热搜里有个高频问题WHERE a AND b的情况应该怎么建索引答案取决于a、b各自的选择性和查询特征。先给一个通用的决策流程确认a、b的等值条件是否高频且稳定出现。如果每次查询模式都是WHERE a ? AND b ?建联合索引(a, b)。如果a的区分度高比如能过滤掉90%以上的数据b的区分度低单独建a的索引就够了b的过滤可以在回表之后做。如果a和b单独过滤性都不行但组合起来很好那必须建联合索引。不要分别建(a)和(b)两个独立索引然后指望MySQL用索引合并把两个索引一起用。虽然MySQL有index_merge优化但它不是万能的多数情况下效率远不如一个联合索引。用具体数据说话。假设有一张orders表user_id有1万个不同值status只有3个值。那么WHERE user_id 100 AND status 1这种查询应该建(user_id, status)。因为user_id能过滤掉99%的数据status的区分度太低单独靠status建立索引只会把这三条数据从一个巨大集合里捞出来代价比直接查还高。5.2 联合索引列顺序选择性优先还是条件优先两个派别一直有争议。我的实践经验是分情况如果两个列都是等值查询区分度高的列放前面。区分度越高B树越能快速缩小范围。如果一个是等值、一个是范围查询等值列放前面。因为等值匹配可以直接定位到树的某个分支范围查询只能利用索引的一部分顺序。如果查询里有ORDER BY列排序列尽量放在联合索引的末尾让排序直接走索引顺序避免filesort。举个例子一条高频SQLSELECT id, amount FROM orders WHERE user_id ? AND create_time ? ORDER BY create_time DESC;适合建(user_id, create_time)user_id做等值过滤create_time做范围过滤和排序。这里把create_time放第二位不但能让范围查询走索引还能顺便完成排序一举两得。5.3 联合索引的隐式“影子索引”效应一个很容易被忽略的好处是联合索引(a, b, c)相当于同时拥有了(a)、(a, b)和(a, b, c)三个索引的能力所以理论上可以用一个联合索引覆盖三个查询模式。建索引时先盘点业务里的高频查询尽量用一到两个联合索引覆盖多数场景而不是每个查询单独建一个索引。但也要注意联合索引(a, b, c)不能覆盖(a, c)的等值查询优化——因为中间少了bc无法直接利用索引继续匹配MySQL只能根据a定位后在拿到的多条记录里过滤c这一步叫“索引跳跃扫描”MySQL 8.0的优化器能做一些优化但能力有限不要过度依赖。关键点是你能真实验证c是否走索引EXPLAIN会告诉你真相。6. 索引失效的十大场景每一个都是线上事故的种子6.1 失效场景逐项拆解这一部分值得反复对照自查。很多看起来该走索引的SQL实际执行时就是不走原因往往在这十个里面。第一对索引列使用函数。WHERE DATE(create_time) 2024-01-01MySQL无法直接利用create_time上的索引顺序因为索引用的是原始值不是函数处理后的值。正确写法是范围条件WHERE create_time 2024-01-01 AND create_time 2024-01-02第二隐式类型转换。索引列是字符串类型查询条件用数字WHERE phone 13800000000MySQL会把字符串列转成数字再比较索引就废了。解决办法是条件里加引号WHERE phone 13800000000。第三前导模糊查询。LIKE %abc无法利用B树的有序性因为B树是按前缀有序的不知道目标值从哪个前缀开始。LIKE abc%是可以走索引的。需要做后缀模糊匹配的场景建议考虑全文索引或者Elasticsearch。第四联合索引未满足最左前缀。建了(a, b)索引查询只用了b作为条件索引无法生效。这是最普遍也最容易被忽略的失效场景。第五索引列参与运算。WHERE age 1 30这不是把age做等值匹配而是对索引列做了加减运算索引失效。应改写成WHERE age 29。第六OR条件包含非索引列。WHERE a 1 OR b 2如果b没有索引MySQL只能全表扫。改成UNION ALL或者给b建索引。第七NOT IN、NOT EXISTS、操作。负向查询一般不走索引因为B树的有序性对“不等于”没有帮助。第八IS NULL与IS NOT NULL。这个比较特殊IS NULL在部分版本和场景下能走索引但要看优化器的具体选择不要想当然。如果你频繁按空值过滤可以考虑在列上建一个“哨兵值”替代NULL比如用空字符串代替NULL。第九数据倾斜。当某列90%都是同一个值时优化器可能直接放弃索引因为全表扫比走索引回表的成本更低。这不是索引“失效”而是优化器做了正确的成本决策。遇到这种场景需要重新设计查询条件或者考虑分区表。第十优化器统计信息不准确。表数据更新频繁但如果未及时更新统计信息优化器可能误判成本选择全表扫。执行ANALYZE TABLE orders更新统计信息很多时候就能恢复走索引。6.2 用EXPLAIN验证别靠猜看执行计划想验证一条SQL到底有没有走索引用EXPLAIN看执行计划。EXPLAIN SELECT id, amount FROM orders WHERE user_id 100 AND create_time 2024-01-01;关键看这几列typeconstrefrangeindexALL。出现ALL基本就是全表扫。possible_keysMySQL认为可用的索引列表。key实际选择的索引。如果这里是NULL说明没走索引。rows预估扫描的行数越小越好。Extra出现Using filesort说明排序没有用上索引出现Using temporary说明查询用了临时表出现Using index说明是覆盖索引无需回表这个是加分项。排查失效问题时一条SQL从typeALL到typerange的改变往往就是改写条件写法带来的接着再验证一次才算确认。7. 索引设计规范与运维建索引不是建完就完事7.1 从“三星索引”倒推设计标准数据库圈子里有一个“三星索引”的评价体系我认为它对实战很有指导意义第一星索引能覆盖查询涉及的列即索引包含查询的所有字段避免回表。第二星索引的键值顺序与查询的排序需求一致避免排序操作。第三星索引的键值顺序能高效定位查询条件的等值匹配列即把等值过滤列放在索引最前面。按照这个标准检查自己建的索引会很快发现很多设计问题。比如一个查询SELECT user_id, amount FROM orders WHERE status paid ORDER BY create_time DESC;如果要达到“三星”联合索引应该是(status, create_time, amount)——status负责第三星的等值定位create_time负责第二星的排序amount负责第一星的覆盖。注意amount不是过滤条件但放在索引里可以让查询完全不用回表。7.2 索引管理的常用运维操作创建索引-- 普通索引 CREATE INDEX idx_user_id ON orders(user_id); -- 唯一索引 CREATE UNIQUE INDEX uk_order_no ON orders(order_no); -- 联合索引 CREATE INDEX idx_user_status ON orders(user_id, status); -- 前缀索引 ALTER TABLE user ADD INDEX idx_email (email(20));删除索引DROP INDEX idx_user_id ON orders;查看索引SHOW INDEX FROM orders;重建索引合并碎片ALTER TABLE orders ENGINE InnoDB;注意直接执行ALTER TABLE ... ENGINEInnoDB会锁表。线上大表建议用pt-online-schema-change或gh-ost否则业务写入会被长时间阻塞。7.3 大表加索引的正确姿势线上表数据量过千万之后直接在原表ALTER TABLE ADD INDEX会带来严重的锁表风险。我通常的做法是创建一张结构相同的新表在新表上加好索引。使用工具同步增量数据把原表数据拷贝到新表。完成数据校验后在低峰期切换表名。如果觉得这套流程太重MySQL 8.0支持的ALGORITHMINPLACE, LOCKNONE配置可以在线加索引ALTER TABLE orders ADD INDEX idx_user_id (user_id), ALGORITHMINPLACE, LOCKNONE;但这个语法能否完全在线也取决于表的大小和当前负载执行前建议先在测试环境压一下。7.4 排查慢查询和冗余索引有监控条件的团队直接捞slow_query_log里的慢SQL逐个分析执行计划。没有监控工具的环境手动开慢查询日志SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;定位到慢SQL后除了关注索引缺失还要关注冗余索引。比如已经建了(a, b)联合索引又单独建了(a)索引后者就是冗余的白白增加写入开销。用sys.schema_unused_indexes视图可以查哪些索引长时间没被使用SELECT * FROM sys.schema_unused_indexes;该删的冗余索引尽早删索引不是越多越好每多一个索引INSERT/UPDATE/DELETE的性能都会下降一点。8. 回表、覆盖索引与索引下推三个高频深度概念8.1 回表是性能杀手覆盖索引是解药回表我前面提到过二级索引拿到主键后再回聚簇索引查数据。回表一次就是一次随机IO数据量大时性能消耗很可观。覆盖索引指的是查询需要的所有列都能从二级索引的叶子节点直接拿到不需要回表。前面那个三星索引例子里的amount列就是为了实现覆盖。覆盖索引是日常优化中性价比最高的手段它不额外占用多少空间但能把查询从“索引扫描回表”变成“纯索引扫描”。判断方法看EXPLAIN的Extra列出现Using index就是覆盖。8.2 索引下推MySQL 5.6之后的白送优化索引下推Index Condition PushdownICP很多人不了解但它一直都在帮你省时间。它的原理是在没有ICP之前InnoDB根据索引定位到记录后需要回表取出整行再在Server层判断其他索引列的条件有了ICP存储引擎层就能在索引遍历过程中直接判断联合索引后面的列条件过滤掉不满足条件的记录减少回表次数。举例建了(user_id, status)索引查询SELECT * FROM orders WHERE user_id 100 AND status paid;有ICP参与时InnoDB在索引扫描过程中直接用status条件过滤掉大量不匹配的记录只有真正满足条件的记录才回表。这个优化是默认开启的你不需要额外配置但要知道它的存在——以此可以理解为什么联合索引后面的列即使不满足最左前缀也能在索引层参与过滤。8.3 主键索引和唯一索引的查询差异回到热搜里的那个问题主键索引和唯一索引在查询上有什么不同最核心的差异就是聚簇。主键索引是聚簇索引叶子节点直接是整行数据所以SELECT * FROM orders WHERE id 1只需要一次B树搜索就能拿到全部字段。唯一索引是二级索引叶子节点只存唯一键值和主键值SELECT * FROM orders WHERE order_no xxx要先搜唯一索引拿主键再回表拿整行。当查询条件正好是主键时主键索引天然就是覆盖索引而唯一索引只有在查询列恰好等于索引键主键的子集时才可能覆盖。9. 结合热搜高频问题的场景化解答9.1WHERE a AND b到底怎么建索引这是搜索量非常高的问题。我的标准建议是如果a、b均为等值条件且区分度都不错建(a, b)或(b, a)把区分度高的放前面。如果a是等值、b是范围建(a, b)等值列放前面。如果a、b都要模糊查询基本都可以考虑放弃索引优化改用搜索引擎或接受全表扫。如果a、b分别属于两张表的关联字段还需要结合JOIN的顺序和驱动表来分析这个就更复杂了。9.2 排序字段怎么建索引ORDER BY create_time DESC单独出现时建create_time的降序索引ALTER TABLE orders ADD INDEX idx_create_time (create_time DESC);如果排序同时伴随WHERE user_id ?建联合索引(user_id, create_time)让user_id先定位再按create_time排序。这里要注意联合索引的列顺序ORDER BY的列放最后一个才能真正利用索引避免filesort。9.3 数据量大且需要模糊搜索的表怎么办普通索引无法支持LIKE %xxx%我的实践建议是小表百万行以内直接用LIKE %keyword%全表扫也能接受。大表考虑MySQL全文索引但中文分词效果有限。更大的表、更复杂的搜索需求上Elasticsearch或其他搜索引擎。数据库里保留全量数据搜索服务承担检索MySQL负责按主键批量拉取详情。这套模式我在实际项目中用过效果稳定。搜索引擎召回的主键列表通常几千到几万个分批WHERE id IN (...)去MySQL查性能完全可控。9.4 索引表空间膨胀怎么处理数据量增长后二级索引占用的空间可能比表本身还大。处理思路是先排查哪些索引未被使用删除冗余索引再针对碎片率高的表做重建最后考虑归档历史数据从源头控制数据规模。索引表空间本身不可怕怕的是无效索引和碎片堆积。10. 最后再分享一点体悟索引这个主题从入门到精通是一个不断推翻自己认知的过程。最早我以为索引就是加个字段那么简单后来发现联合索引的列顺序才是真正考验功力的地方再后来理解了B树和页分裂才发现为什么大表加索引那么疼为什么UUID主键在写入高峰期会拖垮数据库。我建议每个写MySQL的人都建一张100万行以上的表自己手动执行几次EXPLAIN改改条件、换换索引顺序观察rows和Extra的变化。纸上谈兵和亲手验证是两码事很多规则只有自己撞过一次坑才能真正内化成肌肉记忆。如果你正被某个具体SQL的性能问题卡住建议先从EXPLAIN看起按本文第6部分的失效场景逐一比对八成以上问题都不是玄学而是索引设计本身出了岔子。索引不是银弹但它确实是MySQL性能优化里投入产出比最高的环节值得花时间把它彻底搞透。