索引这东西只要是跟 MySQL 打过交道的人就不可能绕得开。不管你是刚入行的后端开发还是已经带团队的技术负责人只要 SQL 慢了第一个被拉出来问罪的十有八九就是索引。但说实话我见过太多人把索引当成“加了就快”的万金油结果加了一堆索引写入变慢、磁盘膨胀查询也没快多少。这篇文章我就把 MySQL 索引从头到尾拆一遍从底层数据结构到实际建索引的姿势再到最常见的索引失效场景和调优手段全部用我实际踩过的坑和验证过的结论来讲希望能帮你把索引这块彻底吃透。这篇文章适合谁看刚学 MySQL 的新人可以把它当索引的入门地图知道索引是什么、为什么能加速、日常该怎么建写过几年 SQL 但没系统梳理过索引原理的人可以重点看 BTree 这部分和失效场景很多“为什么这样写就慢”的疑惑会在这里找到答案至于准备面试的文末也有不少可以直接拿来答的干货点。1. 索引到底是什么先搞懂它为什么能加速1.1 从全表扫描说起没有索引时 MySQL 在干什么很多人用索引用了很久但真要问一句“索引为什么快”反而说不清楚。我先从最朴素的角度讲起。假设你有一张用户表里面有 100 万行数据你要执行SELECT * FROM user WHERE age 25在没有索引的情况下MySQL 只能从第一行数据开始一行一行往下扫把每一行的 age 字段都取出来跟 25 比较一次直到把整张表扫完。这个操作在 MySQL 里有个专门的术语叫全表扫描full table scan。全表扫描的耗时跟表的数据量成正比100 万行可能只要几百毫秒但到了 5000 万行、上亿行的时候一次全表扫描可能就是几十秒甚至几分钟。用户那边的表现就是接口超时、页面转圈DBA 那边的表现就是慢查询日志刷屏。那索引做了什么呢它相当于给数据建了一个“目录”。就像你查一本厚字典不会从第一页开始翻到最后一页而是先看目录找到对应的页码直接翻过去。索引就是数据库里的目录它把某个字段的值和对应的数据行位置记录下来查询的时候直接走目录定位而不是从头翻到尾。这个类比基本准确但真实的索引要比字典目录复杂得多因为数据库要面对的不仅仅是等值查询还有范围查询、排序、分组、多条件组合查询等等。所以 MySQL 最终选择了 BTree 这种数据结构来承载索引而不是简单的目录结构。1.2 为什么偏偏是 BTree聊聊底层数据结构选型索引的底层数据结构在 MySQL 里默认是 BTree。面试的时候这是高频考点很多人能背出“BTree 矮胖、叶子节点存储数据、非叶子节点只存索引键”这种结论但背结论没意义你得知道它到底解决了什么问题。先拿几种常见数据结构的优劣势对比一下数据结构查询效率范围查询插入删除磁盘IO哈希表O(1)极高不支持优秀极少二叉树O(log n)支持一般树高不可控红黑树O(log n)支持优秀树高仍偏高B-TreeO(log n)支持优秀矮胖IO少BTreeO(log n)支持且极快优秀比B-Tree更优数据库的数据最终是存在磁盘上的磁盘 IO 的速度比内存慢好几个数量级所以索引设计的第一原则就是尽量减少磁盘 IO 次数。而一次磁盘 IO 读取的数据量是固定的通常以“页”为单位MySQL 默认一页 16KB如果树的高度越高查询一个数据要经历的磁盘 IO 就越多。BTree 通过“一个节点存多个键值”的设计把树的高度压得很低一般 3 到 4 层就能存储上千万条数据的索引。这里有一个非常关键的细节就是MySQL 的 InnoDB 引擎里索引和数据是存储在同一个文件中的具体说是存储在表空间文件里。BTree 的每个节点对应一个数据页非叶子节点只存储索引键和指向子节点的指针真正的数据行或者说主键值行数据都存在叶子节点上。这样的设计配合上叶子节点之间通过指针形成的有序链表范围查询就变得极其高效——找到范围的起点之后顺着叶子链表一路往后扫就行不用像 B-Tree 那样在树的不同层级之间反复跳跃。1.3 聚簇索引与二级索引为什么“回表”这个事这么重要InnoDB 里有两类索引一类是聚簇索引clustered index一类是二级索引secondary index。这个区别直接影响查询性能值得多花点时间讲清楚。聚簇索引就是 InnoDB 存储数据的方式本身。在 InnoDB 里表的数据行实际上就存储在聚簇索引的叶子节点上一个表有且只有一个聚簇索引。如果你建表时定义了主键那主键就是聚簇索引如果你没定义主键InnoDB 会选择一个非空的唯一索引作为聚簇索引如果连唯一索引都没有InnoDB 会隐式创建一个 6 字节的 rowid 作为聚簇索引。所以你看主键索引其实比较特殊它直接把整行数据都“挂在”叶子节点上了。二级索引则不同它的叶子节点不存完整行数据只存索引键和对应的主键值。你用二级索引查询时过程是这样的先在 BTree 里找到满足条件的叶子节点拿到主键值然后再用主键值到聚簇索引的 BTree 里再查一次才能拿到完整的数据行。后面的这一次按主键查找的过程就叫回表bookmark lookup。回表是额外的一次磁盘 IO 操作所以性能会打折扣。理解了这一点你也就明白了覆盖索引为什么那么香——如果查询需要的所有字段都已经在二级索引的叶子节点里了MySQL 就不需要回表直接返回索引里的数据就行。比如你的联合索引建的是(age, name)查询条件是WHERE age 25且只需要输出name字段那这个查询完全不需要回表因为age和name两个字段都在索引里存着。2. 索引设计与建索引的正确姿势2.1 主键索引的选择自增主键真的无脑选吗先记住一个结论InnoDB 表里绝大部分情况下都建议使用自增整数主键。原因跟 InnoDB 的数据页存储机制强相关。聚簇索引的数据行按主键值的顺序物理排序存放如果主键是自增的新插入的行总是在 BTree 的“最后面”插入操作只需要追加数据页而且避免频繁的页分裂。而如果主键是 UUID 之类的随机值新插入的行会随机分布在索引树的各个位置导致频繁的页分裂、页重排还会让数据页产生大量碎片插入性能显著下降。我之前接手过一个项目早期表结构用的是 UUID 字符串做主键数据量到千万级之后插入性能从每秒 2000 条掉到不到 500 条主库的 IO 直接被打满。后来改成自增主键 UUID 列加唯一索引的方式插入性能才恢复正常。其实业务根本不需要主键对外暴露UUID 加个唯一索引保证业务唯一性就足够了主键完全可以用自增。当然也有例外情况比如分库分表场景下生成全局唯一 ID 做主键这时候通常会选择雪花算法生成的分布式 ID它虽然是数字但不是连续的插入性能比自增差一些但至少比 UUID 好得多。另一个例外是某些关联查询特别频繁、且主键本身就是业务天然标识的场景比如订单号但这种场景下建议仔细评估数据量再来决定。2.2 普通索引、唯一索引、前缀索引怎么选很多人在建索引的时候把INDEX和UNIQUE INDEX混着用其实这两个的适用场景完全不同。唯一索引的核心价值是约束不是性能。它的语义是“这个字段的值不能重复”数据库层面帮你保证这一点。需要注意的是唯一索引的查询性能确实会比普通索引好一点点因为查到第一条满足条件的记录后就知道不可能有第二条了可以直接停下来但这个性能差异微乎其微不要指望靠它来优化查询。普通索引就是纯粹的加速工具不承担任何约束职责。它的适用面最广查询条件里的字段如果区分度不错都可以考虑加普通索引。前缀索引是处理长字符串字段的利器。比如你要给一个存储 URL 或者长字符串的字段建索引整字段索引会占用大量空间而且由于字段太长索引树的分支因子下降树会变高反而影响性能。这时候可以只取字段的前 N 个字符建索引这就是前缀索引。前缀索引有个要注意的点长度 N 的选择会影响索引区分度而区分度直接决定索引效率。我一般用这个 SQL 来测试SELECT COUNT(DISTINCT LEFT(column_name, N)) / COUNT(*) FROM table_name;这个比例越接近 1说明区分度越好。通常我会取 80% 到 90% 区分度对应的 N 值然后在性能和空间之间做一个平衡。比如一个字段全字段索引可能需要 200 字节但前缀 20 个字符就能达到 85% 的区分度那果断用前缀索引索引体积直接缩到十分之一性能反而更好。2.3 联合索引的列顺序最左前缀原则到底是什么联合索引也叫复合索引是实际工作中用得最多的索引类型同时也是最容易用错的一种。联合索引的底层结构依然是 BTree只是索引键从单个字段变成了多个字段。假设你建立了联合索引(a, b, c)这个索引在 BTree 里排序的规则是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。正因为排序规则是这样的所以这个联合索引能高效支持的查询组合是(a)、(a, b)、(a, b, c)也就是查询条件里必须包含联合索引的最左前缀列否则索引无法生效。这就是最左前缀原则。举个例子索引(a, b, c)下面几种查询的情况查询条件是否走索引原因WHERE a 1走包含最左列 aWHERE a 1 AND b 2走包含 a、bWHERE a 1 AND c 3走但只用到 ac 不连续等于只用了一部分WHERE b 2不走不包含最左列 aWHERE b 2 AND c 3不走不包含最左列 a所以设计联合索引的时候列的顺序是排在第一位的。我总结的排列原则是先看等值查询的列把等值查询的列放在前面如果有多个等值查询的列把区分度高的放在前面如果有范围查询的列把范围查询的列放在最后面。原因是等值查询的列可以完全命中索引而范围查询的列一旦出现后面的列就无法继续走索引的排序特性了。2.4 覆盖索引的威力让查询不回表前面讲过回表会造成额外的 IO那有没有办法让某个查询“不回表”有这就是覆盖索引。覆盖索引不是一种独立的索引类型而是一种查询优化策略。当你的SELECT查询涉及的列全部包含在某个索引的键值中时InnoDB 只需要扫描索引树拿到索引键值直接返回完全不需要再回到聚簇索引去查完整行数据。我优化过一条跑了 3 秒的慢查询表里有两个字段需要关联查询原来的语句是SELECT order_id, user_id, amount FROM orders WHERE user_id 12345;表上有user_id的单列索引所以 MySQL 需要通过索引定位到主键再回表拿order_id和amount。数据量一大回表的开销就很明显。后来我把单列索引改成了联合索引(user_id, order_id, amount)这个查询就完全走索引了耗时直接降到了 50 毫秒以内。所以写查询语句之前你要学会先问自己一个问题这条 SQL 需要的字段能不能全部塞进一个索引里如果可以那就设计成覆盖索引这是性价比最高的 SQL 优化手段之一。3. 索引失效场景全梳理为什么索引没生效3.1 最常见的隐式类型转换陷阱我敢说只要是稍微有点规模的业务线上绝对出现过因为隐式类型转换导致索引失效的慢查询。这里有个最经典的翻车现场SELECT * FROM user WHERE phone 13800138000;假设phone字段在表里是varchar(20)类型但你在查询条件里传了一个整数。MySQL 看到字符串字段跟数字比较会把字符串字段隐式转换为数字再比较转换之后就相当于对索引列应用了一个函数索引自然就失效了。解决办法有两个一是写 SQL 的时候老老实实加引号写成phone 13800138000二是直接把列类型改成跟业务语义匹配的类型。这里我的建议是只要某个字段是字符串类型且需要建索引就必须在代码规范里强制要求参数类型对齐靠人肉记忆很容易出问题但你可以通过代码评审工具或者 ORM 层做校验拦截。再补充一个容易忽略的WHERE date_field 2024-01-01这种写法如果date_field是datetime类型而传进来的参数是日期字符串MySQL 其实可以做类型转换并正确利用索引因为转换发生在参数上而不是发生在索引列上。但WHERE DATE(date_field) 2024-01-01这样直接在索引列上套函数索引就失效了。3.2 对索引列使用函数或计算SQL 写法大忌对索引列使用函数索引失效这个很多人都知道但实际项目中同样问题不停在犯。比较典型的几类写法-- 对索引列使用函数 SELECT * FROM user WHERE DATE(create_time) 2024-06-01; -- 对索引列进行计算 SELECT * FROM user WHERE salary * 12 500000; -- 隐式类型转换 SELECT * FROM user WHERE phone 13800138000;DATE(create_time)这种写法优化器不会考虑把函数反过来推导到索引上它会老老实实地全表扫描然后逐行计算。优化方式是把条件改写成范围查询SELECT * FROM user WHERE create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00;这样不仅能用上索引而且语义也更准确——原写法其实每一天内的时间都会被过滤掉除非你踩过这个坑不然不一定注意到这个 bug。salary * 12 500000这种对索引列做运算的改动方式也是一样的思路把表达式移到等号右侧WHERE salary 500000 / 12。3.3 这些情况索引也会悄悄失效除了上面两种还有几类场景也很常见但不一定所有人都能第一时间反应出来。LIKE 模糊查询左匹配。WHERE name LIKE %张这种左侧带通配符的写法索引会失效因为 BTree 按前缀排序的特性决定了它只能从左侧开始匹配。但WHERE name LIKE 张%这种右侧通配符的写法是可以走索引的。这背后还是最左前缀原则在起作用。OR 连接条件不全是索引列。WHERE age 25 OR name 张三如果只有age有索引而name没有优化器没法只走age索引再合并结果因为它还得去全表扫name的条件所以整体上它很可能选择全表扫描。解决办法是给name也加上索引或者改写为UNION ALL两个子查询。条件里出现 IS NULL 与 IS NOT NULL。在 MySQL 的优化器里IS NULL在某些条件下可以用到索引但IS NOT NULL大概率不会走索引因为非空记录占比太高的情况下优化器算完账发现全表扫描比走索引还划算就弃用索引了。这是一个“成本决策”问题理解这一点很重要索引是否生效本质上是优化器基于统计信息做的成本估算不是死规则。NOT IN、!、 操作。这类不等于操作同样很容易导致索引失效原因也是优化器判断需要扫描的记录过多走索引反而不划算。如果业务确实需要大量“排除”类查询可以考虑改写成LEFT JOIN或者用NOT EXISTS改写某些场景下效果会更好。4. 实操索引管理命令与 EXPLAIN 执行计划解读4.1 索引的创建与删除语法其实没你想的那么简单创建索引的标准语法是-- 普通索引 CREATE INDEX idx_user_name ON user(name); -- 唯一索引 CREATE UNIQUE INDEX uk_user_phone ON user(phone); -- 联合索引 CREATE INDEX idx_user_age_name ON user(age, name); -- 前缀索引 CREATE INDEX idx_user_url_prefix ON user(url(20));在已有表上创建索引时有一个性能大坑要特别注意在数据量很大的表上直接CREATE INDEX会锁表。具体来说MySQL 8.0 之前的版本CREATE INDEX会阻塞该表上的写操作8.0 之后支持了在线 DDL但不同的索引创建方式对并发的影响依然存在。我的经验是线上大表加索引一定不要在业务高峰期直接执行可以在低峰期用pt-online-schema-change这类工具来做它会通过触发器同步增量数据尽量减少锁表时间。另外建表的时候直接在CREATE TABLE语句里定义索引也是一种常见做法CREATE TABLE user ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(50) NOT NULL, phone VARCHAR(20) NOT NULL, age INT, PRIMARY KEY (id), UNIQUE KEY uk_phone (phone), KEY idx_name (name), KEY idx_age_name (age, name) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;删除索引的语法ALTER TABLE user DROP INDEX idx_name;我要特别提醒一句索引不是越多越好。每多一个索引就多一棵 BTree插入、更新、删除数据时都要同步维护这棵树写入性能损失是实实在在的。而且索引还占磁盘空间。我见过一张 5000 万行的业务表被人从上游导数据的时候顺手加了 8 个索引结果写入性能惨不忍睹后来砍到 3 个关键索引才恢复正常。4.2 用 EXPLAIN 看懂 MySQL 到底怎么执行 SQL索引建得对不对不能靠猜得靠EXPLAIN来看执行计划。这是 MySQL 性能调优里最重要的一步也是每个后端开发必须熟练掌握的技能。执行一条 SQL 前面加EXPLAIN关键字MySQL 会输出一张表里面最关键的几列我逐个讲type 列表示访问类型从好到差依次是systemconsteq_refrefrangeindexALL。ALL就是全表扫描这是最差的情况index是扫描整棵索引树虽然也扫全量但比表小range是范围扫描已经算不错ref/eq_ref/const都是走索引点查性能很好。key 列实际用到的索引名。如果这一列是NULL说明这条 SQL 没用到任何索引。rows 列优化器估算的需要扫描的行数这个数字越小越好。Extra 列这列信息量很大常见的值有Using index用了覆盖索引不需要回表这是最理想的情况。Using where在索引基础上还需要回表过滤通常意味着部分条件没有完全命中索引。Using index condition索引条件下推MySQL 把部分 WHERE 条件判断下推到索引层减少了回表行数。Using temporary使用了临时表通常出现在GROUP BY或DISTINCT场景下说明没有用联合索引优化掉排序分组。Using filesort文件排序说明排序没有用到索引需要在内存或磁盘上额外排序。这是个重要信号如果出现这个优先检查能不能用索引顺序替代排序。4.3 一次慢查询的完整调优案例我拿一个实际优化过的案例来讲这样过程更完整。场景电商订单表orders数据量大约 2000 万行。业务方反馈一个统计接口特别慢查询大约需要 6 秒。慢 SQL 大概是这样的SELECT user_id, COUNT(*) FROM orders WHERE status 1 AND create_time 2024-01-01 AND create_time 2024-04-01 GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 20;先用EXPLAIN看执行计划结果是这样的idtypekeyrowsExtra1ALLNULL5123680Using where; Using temporary; Using filesort出现了ALL全表扫描且没有用到任何索引还有临时表和文件排序三个问题叠加。这个查询要是能快就怪了。分析一下条件status、create_time都是过滤条件user_id是分组列。我最后建了一个联合索引ALTER TABLE orders ADD INDEX idx_status_time_user (status, create_time, user_id);列顺序的依据是status是等值条件放在最前面create_time是范围条件放在第二位user_id是分组列且需要返回所以放在联合索引里实现覆盖索引让GROUP BY可以直接利用索引顺序而不产生临时表。生产环境执行 DDL 必须谨慎2000 万行的表不能直接ADD INDEX我用了pt-online-schema-change在低峰期执行pt-online-schema-change --alter ADD INDEX idx_status_time_user (status, create_time, user_id) --no-drop-old-table Dshop,torders执行完再跑EXPLAINidtypekeyrowsExtra1refidx_status_time_user5832Using where; Using index直接变成了Using index扫描行数从 512 万降到 5 千多实际查询耗时从 6 秒多降到了 80 毫秒。这个案例很典型优化思路完全建立在理解联合索引和最左前缀原则的基础上。5. 常见问题与深度思考5.1 关于索引失效的排查与验证方法很多开发遇到 SQL 慢第一反应是“我加了索引呀怎么还慢”。这时候不要猜按下面这个流程排查第一步用EXPLAIN看执行计划确认key列到底有没有用上索引。这一步能排除一半的“我以为”。第二步如果key是NULL把 WHERE 条件里的每一列单独拿出来分析是不是对索引列用了函数是不是发生了隐式类型转换是不是 LIKE 左匹配是不是 OR 连接了非索引列按照第 3 部分列的场景逐项对照。第三步如果key列显示用上了索引但性能还是慢那就看rows列和Extra列。rows特别大说明索引的区分度不够好或者扫描范围本身太大Extra里有Using filesort说明排序没用上索引有Using temporary说明分组可能触发了临时表。还有一个我常用的验证手段强制走索引对比SELECT * FROM user FORCE INDEX (idx_name) WHERE name 张三;如果强制走索引比优化器自动选择的方案慢那说明当前索引可能确实不适合这条查询需要重新设计索引而不是继续调 SQL。如果强制走索引反而更快那可能是统计信息过期导致优化器误判可以执行ANALYZE TABLE user;更新统计信息后再看。5.2 索引与排序、分组的关系很多人建索引只想着 WHERE 条件但排序和分组其实也能用索引来优化。BTree 本身就是按顺序存储的所以如果ORDER BY的字段顺序跟索引顺序一致MySQL 直接扫描索引就能拿到有序结果不需要额外的排序操作也就没有了Using filesort。举个例子索引是(age, create_time)那么-- 能利用索引顺序 SELECT * FROM user WHERE age 30 ORDER BY create_time; -- 没法利用索引顺序会产生 filesort SELECT * FROM user WHERE age 30 ORDER BY name;第一个查询里age等值条件定位后create_time天然有序第二个查询的name不在索引里必须在拿到数据后重新排序。但要注意顺序的反向ORDER BY create_time DESC其实也可以走索引因为 BTree 支持反向扫描只是性能略逊于正序。如果你对排序性能要求极高可以考虑把索引列定义为DESC索引MySQL 8.0 支持降序索引。至于GROUP BY它的底层逻辑是先排序再分组或者用哈希分组所以GROUP BY能利用的索引规则跟ORDER BY基本一致。上面案例里我把user_id放进联合索引GROUP BY直接扫索引就完成了分组连临时表都省了。5.3 关于索引代价和反模式的一些补充最后再说说“不要干什么”。不要给低区分度字段建索引。比如性别字段只有男女两种值选择性太低建了索引也过滤不掉多少行优化器大概率也不愿意用纯属白占空间。不要给大文本字段直接建索引。TEXT、BLOB这类大字段除非是InnoDB为它们生成前缀索引实际就是走了前缀索引的路子否则不适合直接建索引原因是索引体积太大而且区分度也不一定好。不要搞“索引套索引”的过度优化。我刚工作那会儿拿到一条慢查询就加索引一个月加了十几个后来被 DBA 找去谈话一张热表写入延迟翻了三倍。索引每多一个写入时的维护成本就多一份。对于读多写少的报表库多几个索引问题不大但对于高并发写入的核心业务表一个索引的成本必须反复权衡。不要在生产库随意执行 DDL 加索引。数据量大就上pt-online-schema-change或者利用 MySQL 8.0 的在线 DDL 语法ALGORITHMINPLACE, LOCKNONE尽量降低对线上业务的影响。5.4 从索引失效聊到 SQL 性能优化的一些体会说句实在话索引这件事难的不是原理而是每一天都对自己写的 SQL 保持警觉。我见过太多“这次先跑通性能以后再说”的代码结果就是上了生产、数据量上来之后慢查询一个接一个爆出来。SQL 和索引设计本应该是在写代码阶段就考虑好的事而不是等接口超时了再做抢救式优化。我自己写 SQL 的习惯是三步走写完之后先跑一遍EXPLAIN确认没有全表扫描再盯着rows估算值和Extra里有没有Using filesort或Using temporary这两个危险信号最后把高频查询涉及的条件列和返回列整理一下回头检查联合索引设计能不能覆盖这些查询。这套流程看起来不起眼但真的能帮你避开 80% 以上的性能坑。数据库表的索引设计不是一锤子买卖业务在变查询模式在变索引设计也要跟着演进。定期翻一翻慢查询日志用EXPLAIN重新审视那些高频 SQL看看哪些索引被用得少、哪些查询有更好的索引优化空间这是每一个跟数据库打交道的人都值得养成的习惯。