1. 一次线上慢查询把我逼到重新理解索引先说个真实经历。去年年底我们有个订单列表接口每天晚上8点到10点准时变慢前端转圈转得用户直接在群里开喷。看了下慢查询日志罪魁祸首就是一条按用户ID和创建时间范围分页的SQL单次查询跑了1.8秒。表里数据量其实不大也就三百多万行但就是慢。当时我第一反应是加索引结果加完一测效果有但没达到预期。后来用EXPLAIN一看发现MySQL明明走了索引却还是回了几十万行表。那一刻我才意识到很多人对索引的理解停留在建了索引查询就快但真正决定查询快慢的是索引结构怎么组织数据、查询怎么利用索引、以及索引在什么情况下会失效。这篇文章我不想写成那种堆概念的教学文档。我打算从实际场景出发把InnoDB的索引原理、复合索引的最左前缀法则、索引失效的常见场景、ORDER BY排序优化以及一套能落地的索引设计流程全部串起来讲清楚。适合正在做MySQL性能调优的开发者也适合那些准备面试前想系统补一遍索引知识的同学。看完之后你至少能回答三个问题索引为什么快、什么情况下索引会白建、遇到慢查询该怎么设计索引。2. 索引的本质空间换时间但换得很有技术含量2.1 没有索引时数据库到底在做全表扫描很多人以为全表扫描就是把表从头到尾读一遍其实在实际存储中InnoDB是按页组织数据的每页默认16KB数据行按主键顺序存在页里页与页之间通过双向链表连接。全表扫描的本质是顺着这个链表把所有页读进内存逐行判断条件是否满足。300万行数据按每页大概存100行来算就是3万个页每个页16KB总共约480MB。这480MB要被完整读一遍才能找到目标行。如果这些页不在缓冲池里就得走磁盘IO。哪怕按顺序预读的优化做得再好这个IO量也摆在那里。这就是为什么很多慢查询在大数据量下像老牛拉车——不是SQL写得有问题而是它天生要面对这么大体量的数据搬运。索引的价值就是通过一个远小于原表的额外数据结构帮你把需要读取的数据范围大幅缩小。2.2 索引怎么做到目录式定位你可以把索引想象成一本书的目录。没有目录查找一个关键词就得从第一页翻到最后一页有目录先看章节页码定位到对应区域再细翻那几页就够了。MySQL里的索引主流是B树结构。B树的内部节点只存索引键和子节点指针数据全在叶子节点叶子节点之间用指针串成有序链表。这样一来范围查询只需要找到起点然后沿着叶子链表顺序往后扫就行不用每次都从根节点重新走。以InnoDB为例一张表就是一个聚簇索引数据行本身就是索引的叶子节点。假设一行数据约200字节一页16KB能放80行300万行就需要3.75万个叶子页。但如果你在某个字段上建了二级索引索引项只包含索引列和主键值一个索引项可能才20字节左右一页能放下800个索引项同样是300万行二级索引的叶子页只需要3750个左右。查询时先在二级索引上找到主键再回表查数据需要读的页比全表扫描少一个数量级。这个对比就是索引最朴素的价值用更少的数据读取量换取更快的定位速度。2.3 索引也不是白嫖的写入代价和存储成本但索引不是免费的午餐。每建一个索引就意味着每次INSERT、UPDATE、DELETE都要额外维护这个索引结构——B树的节点分裂、合并、页写入都是开销。索引太多写入性能就会被拖累。同时索引也占磁盘空间。我自己的经验是一个写多读少的业务表索引数量严格控制在5个以内读多写少的表可以适当多一些但也不是越多越好因为查询优化器在多个索引可选时评估成本也需要时间偶尔还会选错。索引设计的本质就是在查询加速和写入维护之间找平衡。注意MySQL里最忌讳的一件事是给表的每个字段都建索引。索引不是收藏品每一个索引都是在用真金白银的写入开销换查询速度。3. InnoDB的B树聚簇索引与非聚簇索引的分工3.1 为什么偏偏是B树而不是哈希表或红黑树聊索引原理绕不开数据结构选型。哈希表做等值查询确实快O(1)的复杂度但一碰到范围查询就抓瞎因为哈希把物理分布完全打乱无法保证有序。红黑树是平衡二叉树查找效率O(logN)但在数据量大的时候树高太高每次查找都要走很多层节点而且不擅长范围扫描——中序遍历才能拿到有序结果效率差。B树之所以被MySQL选中核心在于两点第一节点可以存储多个键值树的高度被压得极低。InnoDB一页16KB假设一个索引键占8字节内部节点一页能存约1000个键三层B树就能存10亿行数据。这意味着从根到叶子最多走三次磁盘IO效率极其稳定。第二叶子节点构成了一个有序的双向链表范围查询只需要定位起点然后线性往后读天然适配SQL里的范围条件、排序操作。3.2 聚簇索引主键决定数据住在哪里InnoDB里表数据本身就是索引这个索引就是聚簇索引通常建立在主键上。聚簇索引的叶子节点存的是整行数据也就是说数据行的物理顺序由主键顺序决定。这个设计带来的直接影响是按主键等值或范围查询非常快因为数据一次到位不需要回表。但也带来一个问题——如果主键是随机字符串比如UUID新插入的数据会随机落在B树的任意位置导致频繁的页分裂和物理重排不仅写入慢还会产生大量碎片。我自己带过的项目里凡是主键用UUID的表插入性能在大数据量下都明显劣于自增ID。这绝对不是玄学而是结构所决定的。3.3 二级索引每次查询最多两次B树搜索的秘密非主键索引统称二级索引也叫辅助索引。它的叶子节点存的是索引列的值加上主键值不包含完整行数据。当你用二级索引查询时InnoDB先走一遍二级索引的B树找到匹配项拿到主键再用主键去聚簇索引里走一遍找到完整数据行。这个过程叫回表。一次完整查询最多两次B树搜索这个设计是故意的二级索引独立于聚簇索引存在不需要因为数据行的移动而重建全部索引只需要更新主键位置的索引项。回表到底是不是性能杀手取决于二级索引筛选出的数据量占总行数的比例。如果查询命中了1%的行回表1%是划算的如果命中了30%的行回表30%就非常亏优化器在这种情况下可能会放弃索引直接全表扫描。这里引出一个经典优化策略覆盖索引。如果查询的字段全部都在二级索引里已经存在MySQL就不需要回表直接在索引页里取数据返回。这就是为什么select *经常比select 指定列慢——因为select *几乎必然触发回表。3.4 主键设计失误的代价一个实际对比我对比过两张结构相同、数据量相同的业务表一张主键用自增ID一张主键用UUID。插入100万条数据自增ID的表耗时约为UUID表的三分之一。原因很简单自增ID是顺序插入新数据永远追加在B树最右侧页分裂概率极低UUID完全乱序每次插入都可能触发页分裂叶子页不断拆分、重写磁盘写的量翻了好几倍。如果你无法避免使用UUID也有缓解方案把UUID转成有序的二进制格式比如UUID_TO_BIN函数或者用雪花ID这类趋势递增的分布式ID方案把随机性降到最低。4. 复合索引的最左前缀法则组合索引的正确打开方式4.1 复合索引是排序后的列表不是多份索引复合索引联合索引不是给每个字段各建一个索引而是把多个字段组合成一个索引项先按第一个字段排序第一个字段相同再按第二个字段排序依此类推。打个比方复合索引就像一本先按姓氏拼音排、再按名字拼音排的通讯录。想查张伟你可以先定位到Z再在上面定位到W但如果你只知道伟这个名没有姓氏这本通讯录对你基本没用因为你不知道去哪一段找。这个特性决定了复合索引的查询能力是逐步衰减的MySQL里管这个叫最左前缀原则。4.2 最左前缀到底怎么理解连续性和范围终结最左前缀有两个关键点。第一查询条件必须包含复合索引最左边的字段才能走索引。第二在遇到范围条件、、BETWEEN、LIKE xx%之前等值匹配的字段可以完整利用索引一旦某个字段用了范围查询索引就只能定位到这个范围后续字段就无法走索引了。举个例子索引(a, b, c)WHERE a 1 AND b 2 AND c 3全部命中索引最优WHERE a 1 AND b 2命中索引的前两列WHERE b 2 AND c 3a不在条件里索引用不上全表扫描WHERE a 1 AND c 3a用索引定位后c无法走索引需要在a的结果集里做过滤WHERE a 1 AND b 2a用了范围b失去索引定位能力只能在a范围内逐个判断第二个场景值得多说一句。很多人以为只要条件里有a索引就能完全用上实际上范围条件会截断索引的连续性。这就是为什么复合索引的字段顺序设计必须考虑实际查询用到的等值和范围条件而不仅仅是建了就行。4.3 复合索引字段顺序怎么排等值优先、区分度高的放前面基于上述原理复合索引字段顺序的设计原则可以归纳为四条经常作为等值条件的字段放最前面比如订单表里的user_id。区分度高的字段优先于区分度低的字段。比如性别这种只有两个值的字段放最前面几乎没用因为即使靠它定位了剩下的候选集仍然巨大。经常作为范围条件的字段放到后面尽量减少范围条件对后面字段的截断伤害。尽量让常用查询能用同一个索引覆盖减少冗余索引。举个实操例子。订单表订单查询最常用的是user_id status create_time这个组合其中user_id是等值status是等值create_time是范围。那么复合索引应该设计成(user_id, status, create_time)而不是(create_time, user_id, status)。因为user_id等值定位最精确create_time作为范围放最后即使截断了前面两个等值已经过滤掉了绝大部分数据。4.4 一个建了索引却不走的案例复盘我之前排查过一个线上问题。表结构里有索引(a, b, c)SQL是WHERE a 1 AND c 3EXPLAIN显示possible_keys里有这个索引但实际只用了索引的一部分rows扫描数还是很高。原因就是上面说的c无法利用索引定位MySQL只能在a的结果集里面逐条过滤c。这个教训是索引设计不是写出来就完事必须用EXPLAIN验证每个查询是否真正用上了索引的全部潜力。很多开发者在建索引时只考虑字段齐全没有考虑顺序合理导致索引效果大打折扣这是最常见的误区之一。5. 索引失效大排查明明有索引却走全表扫描多半栽在这些细节里5.1 索引失效的常见场景清单我在评审代码时检查索引失效已经成了固定动作。下面这个表是我实际排查中总结的高频场景可以直接收藏当checklist场景示例原因对索引列使用函数WHERE DATE(create_time) 2024-01-01函数破坏了索引有序性隐式类型转换WHERE phone 13812345678phone是varchar类型转换使索引失效左边模糊匹配WHERE name LIKE %张B树按前缀排序无法定位OR连接非索引列WHERE a 1 OR b 2仅a有索引无法同时用两个索引合并联合索引未用最左字段WHERE b 2索引(a,b)最左前缀原则索引列参与运算WHERE a 1 100运算破坏索引列值NOT IN / NOT EXISTS 数据占比大WHERE status NOT IN (1,2)优化器评估后放弃索引数据分布本身不优性别字段建索引查男区分度低优化器认为全表扫更快5.2 函数操作和隐式类型转换最隐蔽的两个坑函数坑之所以隐蔽是因为它在小数据量下表现不明显表一大就原形毕露。解决办法是尽量把函数从索引列转移到常量列把WHERE DATE(create_time) 2024-01-01写成WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00既走索引语义也更严谨。隐式类型转换更阴险。比如phone字段是varchar你写的参数是数字MySQL会把字符串列转成数字再比较相当于给列套了一个隐式CAST索引就废了。判断方法很简单EXPLAIN看type是不是ALL同时注意参数和字段类型的匹配。规范做法是应用层保证参数类型和字段类型一致必要时强制加引号。5.3 LIKE、OR、NOT IN这些关键字到底怎么用才靠谱LIKE慢查询里最常见的写法是LIKE %关键字%这种模糊搜索在MySQL里天生和B树合不来因为B树只能按前缀匹配。如果你业务确实需要全文模糊搜索应该考虑全文索引或专门的搜索引擎而不是硬扛。如果只是后缀模糊可以考虑把字段值反转后建索引配合LIKE 关键字%这个技巧我在实际项目里用过效果立竿见影。OR的问题在于如果两边条件里有一个字段没索引整体就无法走索引。解决办法有两个一是把OR拆成两个查询用UNION ALL合并二是用UNION把有索引的查询单独拎出来。不过5.7之后优化器也能用索引合并具体情况还是得EXPLAIN验证。NOT IN和NOT EXISTS在大数据量下经常被优化器放弃索引因为要扫描的数据太多还不如全表扫。能用LEFT JOIN...IS NULL替代NOT EXISTS的场景尽量用JOIN方案优化空间更大。5.4 用EXPLAIN验证每一步type是首要观察指标排查索引失效EXPLAIN是第一步也是最重要的一步。重点看type列从好到差排列system系统表极少见const主键或唯一索引等值查询只匹配一行eq_ref被驱动表通过唯一索引等值匹配JOIN场景最优ref非唯一索引等值匹配range索引范围扫描index全索引扫描跟全表扫差不多只是扫的是索引ALL全表扫描最差如果type是ALL说明这个查询基本没有合理利用索引。再看key列确认实际使用了哪个索引看key_len判断索引具体用到了几个字段看rows评估扫描行数。EXPLAIN看多了你对索引的直觉会非常准。提示导出慢查询日志把慢SQL统一跑一遍EXPLAIN你会发现80%的问题都集中在上面这些场景里。排查索引失效不是靠猜而是靠EXPLAIN逐条验证。6. ORDER BY与索引filesort的代价和排序优化6.1 filesort什么时候发生代价有多大MySQL的排序有两种方式利用索引有序性直接返回或者生成结果集后额外排序filesort。第二种方式尤其需要注意因为它可能使用临时文件排序过程还会占用临时表空间数据量大时磁盘IO和内存开销都非常可观。INDEX排序和FILESORT的分界线在于ORDER BY的字段是否正好是已用索引的一部分且顺序和排序方向与索引一致。举例索引(a, b, c)ORDER BY a, b, c能走索引ORDER BY b, c不能走缺少aORDER BY a, b, c DESC如果索引是默认ASC也不能直接倒着用除非MySQL 8.0支持倒序索引。6.2 一个典型场景分页深翻页的排序地狱最经典的排序问题出现在分页上。比如ORDER BY create_time LIMIT 100000, 20MySQL并不是只取最后20条而是把前10万条全部找出来排序再丢弃前10万条最后返回20条。越是往后翻页扫描和排序的数据量越大性能断崖式下跌。我之前优化过一个报表接口翻到第100页时查询耗时超过5秒。排查发现就卡在这个深分页排序上。解决方案是改成基于游标的分页记录上一页最后一条的create_time查询条件加上WHERE create_time :last_create_time然后ORDER BY create_time LIMIT 20。这样每次查询只扫描目标范围的索引不再处理前面的大量数据。改造之后无论翻多少页查询时间都稳定在几十毫秒以内。这个方案在业务上需要前端配合改一下交互方式换来的性能收益非常大。6.3 排序场景的实战改造方向如果你的业务确实需要ORDER BY我给几个可以直接上手的建议让排序字段尽量包含在已使用的索引中避免filesort。排序字段优先放索引末尾且与WHERE条件的范围字段保持兼容。如果复合索引包含范围条件排序字段可能会被截断此时考虑单独设计排序专用索引比如(user_id, create_time)。避免SELECT * ORDER BY大字段的组合排序结果集越小越好尽量只select必要字段减少临时表的数据量。深分页场景坚决不用LIMIT偏移写法用游标或基于排序键的条件分页。MySQL 8.0之后可以建倒序索引对DESC排序很友好升级到8.0后可以考虑。7. 索引设计实战从订单表反推一套能落地的索引方案7.1 先收集高频查询再设计索引我见过太多人一上来就给表建一堆索引好像集邮一样。我自己设计索引的第一步永远是翻应用日志和慢查询日志把真实高频SQL列出来。然后按频率和耗时排优先级优先处理高频且耗时的查询。以订单表为例假设高频查询有三类按user_id查订单列表按create_time倒序分页按order_no精确查单条订单按user_id status查待付款/已付款订单这三类查询对应的索引设计是索引A(user_id, create_time DESC)。用户查看自己的订单是典型的分页排序场景索引B(order_no)唯一定位因为order_no是唯一流水号索引C(user_id, status)。覆盖按用户和状态筛选的需求这三条索引加起来基本能覆盖绝大多数订单查询场景。多余索引坚决不建后面再加。7.2 主键选择自增ID还是业务唯一键订单表的主键选择我一般建议自增ID作为代理主键业务上的唯一标识order_no单独建唯一索引。好处有两个自增ID维持聚簇索引的写入顺序性减少页分裂订单号即使业务规则变化也不影响表结构的物理布局。有些系统喜欢直接用订单号做主键看起来省了一条索引但全局唯一订单号往往不是顺序递增的写入时容易触发页分裂。而且订单号一般很长二级索引的叶子节点都要冗余一份主键值索引空间成倍增加。综合下来弊大于利。7.3 冗余索引、重复索引和不可见索引冗余索引是索引设计里最浪费钱的东西。比如你已经有索引(a, b)再建索引(a)就是冗余的因为(a, b)已经能覆盖(a)的查询。平时我检查表结构时会用information_schema.statistics查询所有索引把前缀相同的索引列出来逐个对比冗余的及时删掉。MySQL 8.0支持不可见索引用INVISIBLE关键字这是一个特别好的灰度工具。当你怀疑某个索引没有用但不敢直接删的时候可以先设为不可见观察一段时间确认没有业务依赖再物理删除。这个方法比直接删索引安全得多推荐给你。7.4 索引设计的原则沉淀最后把我这些年做索引设计的原则沉淀成几条虽然不是标准答案但踩过很多坑后回头看每一条都有血的教训在后面索引列尽量选区分度高的字段区分度低于20%的字段单独建索引基本没用。字符串字段过长的用前缀索引比如前10个字符减少索引体积但要验证前缀区分度是否够。频繁更新的字段不要放索引首位更新索引列的成本很高。联合索引的字段数量控制在3个以内超过3个的索引维护成本会明显增加。每张表的索引数量控制在5个以内必要时通过冗余字段反范式设计减少索引需求。8. 一个真实的索引优化案例复盘从1.8秒到30毫秒8.1 优化前的完整情况回到文章开头那个订单列表接口。原始SQL大概长这样SELECT * FROM orders WHERE user_id 12345 AND create_time BETWEEN 2024-11-01 00:00:00 AND 2024-11-30 23:59:59 ORDER BY create_time DESC LIMIT 20;表里有300万行数据索引情况是主键id、单列索引user_id。EXPLAIN的结果是type为refkey为idx_user_idrows显示有20多万行。问题就看出来了MySQL先通过user_id定位到该用户全部订单20多万行然后对这批数据做create_time排序最后才取20条。虽然用了索引但排序是filesort代价巨大。8.2 优化过程调整索引字段顺序分析之后发现user_id等值过滤后数据量仍然太大create_time排序不走索引而且BETWEEN范围条件本身对排序还有影响。我的优化方案是重新设计复合索引ALTER TABLE orders DROP INDEX idx_user_id; ALTER TABLE orders ADD INDEX idx_user_create_time (user_id, create_time DESC);把user_id放在第一位等值匹配可以直接定位到该用户create_time放在第二位并且利用MySQL 8.0的倒序索引特性让排序方向正好匹配ORDER BY DESC。排序直接走索引filesort消失排序阶段的数据量也大幅减少。改完之后EXPLAIN显示type为refkey为idx_user_create_timekey_len明显变长type从ALL变成range或refExtra列对应的Using filesort消失。接口耗时从1.8秒降到30到40毫秒效果立竿见影。8.3 优化验证和效果数据优化完成后我做了三件验证工作压测验证用同样的参数跑前后对比P99耗时从1.8秒降到150毫秒以内P50降到30到40毫秒。慢查询观察持续一周观察慢查询日志该SQL再也没出现在慢查询Top列表里。写入性能回检确认新索引没有明显拖慢INSERT和UPDATE。表是读多写少的报表类业务写入频率低索引维护成本可接受。这个案例很好地印证了文章的所有观点索引不是建了就完事字段顺序、索引结构、查询写法三者必须匹配。优化SQL或索引之前先EXPLAIN看清楚当前执行计划再对症下药。8.4 复盘哪些做法可以平移到其他项目这套排查方法可以平移到任何MySQL项目慢SQL出现先EXPLAIN看type、key、rows、Extra四个关键列。Extra列出现Using filesort或Using temporary优先考虑调整索引。索引字段顺序根据查询的等值、范围、排序条件排列。每次索引变更都做前后对比验证而不是改完就丢。9. 最后说一句心里话MySQL索引这个东西表面上是几个数据结构和几条优化规则但实际上特别考验对真实业务的理解。我从最开始背八股文式地知道最左前缀、回表、覆盖索引这些名词到后来能从一个慢查询倒推出一套索引设计中间靠的不是看更多文章而是反反复复被线上故障教会做人。如果你现在刚接触索引看完这篇文章先别急着背结论。拿一张真实业务表导出慢查询日志跑EXPLAIN亲手调一次索引体感会完全不一样。如果你已经有几年经验那我更希望你能从为什么的层面重新审视每一条所谓的最佳实践——比如主键为什么推荐自增范围查询为什么截断索引filesort为什么慢。把这些底层逻辑想透了以后遇到任何性能问题你都有一根清晰的思考主线。最后再分享一个排查时特别好用的小技巧把每条慢SQL的EXPLAIN结果保存下来在索引调整前后对比着看。不要只看耗时变没变要看执行计划的形状变没变。这个对比习惯能帮你在复杂的索引场景里快速建立自己的排查直觉。