1. 索引到底做了什么为什么慢查询没它不行先聊个真实场景。上周我帮同事排查一个线上接口页面开了要三秒才返回定位到一条SQLSELECT * FROM orders WHERE user_no U20241001001 ORDER BY create_time DESC;orders表才几十万行不算大可这条SQL跑了将近两秒。翻一下表结构user_no这列上压根就没索引。加了个普通索引之后同样的SQL降到20毫秒以内。就这一行CREATE INDEX把一个“用户查询超时”的问题解决了成本低得让人怀疑是不是弄错了。这也是我写这篇东西的初衷MySQL里创建索引语法简单到三分钟能学会但真正难的是判断“在哪里建、建什么类型的索引、为什么建完能快这么多”。很多人建索引全凭感觉看着哪个字段频繁出现在WHERE里就加一个结果慢SQL反而变成了更快但依然慢的SQL甚至把写入拖垮。如果你正准备系统地把索引搞明白或者已经在为慢查询头疼这篇内容应该能帮你少走不少弯路。我在生产环境里见过太多关于索引的误解比如“索引越多越好”、“把查询字段都塞进索引里就行”、“加了索引查询就一定走”。这些坑我在下面会一个一个讲清楚包括为什么B树能大幅减少磁盘寻道为什么组合索引要讲究顺序以及建索引时MySQL会怎么锁表。1.1 全表扫描与索引查找的差别先做一个最直白的类比。你去图书馆找一本叫《MySQL实战》的书如果图书馆没有目录你只能从第一排书架开始一本一本看封面直到找到为止。几十万本书要翻多久换成线上的数据库这叫全表扫描MySQL把整个表的每一行都读出来逐行判断user_no是否匹配。数据跑在磁盘上哪怕用了预读一次全表扫描也要消耗大量IO数据量越大越吃亏。有了索引之后等于图书馆东边入口就有个目录柜按“作者”、“书名”分类检索一次直接告诉你在哪一排哪一格。MySQL里的普通索引逻辑上就是一张排好序的查找表索引里面存的是字段值和对应数据行的物理位置或者说主键值。查询时先在索引这棵树上快速找到符合条件的值再根据指针去数据页里取整行。这个过程叫回表比全表扫描快一两个数量级。具体到MySQL默认的InnoDB存储引擎它的主键索引和二级索引都采用B树结构。主键索引的叶子节点直接保存整行数据二级索引的叶子节点保存的是主键值。所以如果你的条件字段恰好是主键一次索引查找就能拿到全部数据如果是普通字段上的索引则要先在二级索引树里找到主键再回主键索引树取完整行这就是“回表”的来源。为什么选择B树而不是二分查找或者哈希因为MySQL的数据量通常远大于内存索引要落盘。B树是多叉平衡树树的高度很矮一般几千万行的数据高度也只有三四层。查询一次只需要沿着根节点往下走几十次磁盘IO实际受缓冲池影响相比全表扫描动辄上千次的IO差距就这么拉开的。1.2 索引的数据结构与检索成本普通索引、唯一索引、组合索引在InnoDB里底层都是B树。唯一索引和普通索引的区别主要在叶子节点存储时是否允许重复值以及插入时是否会先做唯一性检查。组合索引则是一棵按照多个字段顺序排序的B树先按第一个字段排第一个字段相同再按第二个字段排依次类推。这里有一个核心的成本概念索引不是零代价的。每建一个索引就是多了一棵额外的B树。每次INSERT、UPDATE、DELETE时MySQL除了维护主键索引树还要同步维护所有二级索引树。插入一行数据如果表上有5个二级索引那就要额外更新5棵树。更新涉及页分裂、页合并的时候代价更高。另外索引还要占用磁盘空间。有些大表上建了好几个组合索引每个索引几GB磁盘和内存缓冲池都受影响。缓冲池里放的是热门的索引页和数据页索引占得多了数据页就可能被挤出去反而容易造成更多磁盘IO。所以创建索引的第一原则是为真实查询创建索引不为“可能以后用得上”创建索引。宁缺毋滥。1.3 哪些场景真正需要索引列个我常用的判断清单满足任何一条都值得考虑建索引WHERE条件里的过滤字段尤其区分度高的字段比如user_no、order_id而不是status这种只有几个离散值的。JOIN关联字段也就是连接条件里的外键列。ORDER BY排序字段特别是需要和WHERE条件组合成复合索引的排序字段。频繁用于GROUP BY或DISTINCT的字段索引可以帮助做分组和去重。具备唯一约束的字段直接创建唯一索引。反过来这类场景基本不需要建索引表数据量很小比如几百行全表扫描都不到0.1ms建索引纯属浪费。频繁更新的字段比如某个“最后修改时间”每行每秒都可能变化。区分度低的字段比如gender只有“男”“女”在1000万行里过滤一半数据索引帮助不大优化器可能放弃。在WHERE条件里基本不会单独出现的字段建了也是闲置索引。2. 创建索引的标准语法和基础选择MySQL创建索引的语法本身不难三句话能说完但选型才是关键。先把语法和常见的操作方式列出来。2.1 三种创建方式直接建、改表建、建表时建第一种在已有表上直接创建索引CREATE INDEX idx_user_no ON orders (user_no);第二种通过修改表结构来加索引功能和上面等价ALTER TABLE orders ADD INDEX idx_user_no (user_no);这两种方式在MySQL 8.0里对于InnoDB表都支持在线DDLALGORITHMINPLACE不会锁住整张表的读写。但是早期的MySQL 5.5、5.6版本里ALTER TABLE加索引是有可能锁表的生产环境要特别小心。后面我专门讲锁表的问题。第三种在建表时直接定义索引CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_no VARCHAR(32) NOT NULL, order_amount DECIMAL(12,2) NOT NULL, create_time DATETIME NOT NULL, KEY idx_user_no (user_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;如果后续要加唯一索引可以用CREATE UNIQUE INDEX uk_user_order ON orders (user_no, order_no);或者ALTER TABLE orders ADD UNIQUE INDEX uk_user_order (user_no, order_no);需要注意的是唯一索引名称在表内必须唯一MySQL不区分大小写。另外创建索引时字段长度如果过长InnoDB对单列索引长度有限制默认最大767字节如果启用innodb_large_prefix且DYNAMIC行格式上限是3072字节。所以遇到很长的字符串列别直接整列加索引用前缀索引。2.2 组合索引和最左前缀规则组合索引恐怕是索引设计里最容易翻车的地方。比如CREATE INDEX idx_user_time ON orders(user_no, create_time)这个索引能用于查询条件包含user_no、或者同时包含user_no和create_time的情况。但如果查询条件里只写了create_time而没写user_no这个索引大概率用不上。这就是最左前缀规则MySQL使用组合索引时必须从最左边的字段开始匹配不能跳过。就好比你要在电话簿里找人电话簿先按姓氏拼音排再按名字拼音排。你只知道名字“小明”想通过电话簿快速找到他这是不行的因为电话簿不是按名字排的。你得先知道姓。实际操作中我会把所有可能组成的等值查询条件列出来然后把经常一起出现在WHERE、ORDER BY里的字段放在前面把区分度高的字段放前面把范围查询字段放后面。比如WHERE user_no ? AND create_time BETWEEN ? AND ?组合索引要建在(user_no, create_time)上这样user_no命中等值条件create_time完成范围扫描效率最高。如果你建反了把create_time放前面user_no等值条件就无法直接利用索引定位到小范围性能会差很多。还有一个容易忽略的点组合索引右边的字段如果在查询中用作范围比较、、BETWEEN那它右边的字段就无法走索引用于等值过滤只能做回表后的过滤。比如索引(a,b,c)查询WHERE a1 AND b2 AND c3c这个字段就利用不上索引因为b是范围条件索引只能定位到a1且b2这一段c没法在这棵树上再做精确匹配了。这是很多慢查询的隐藏原因。2.3 普通索引、唯一索引、前缀索引怎么选先看唯一索引。如果你的业务字段本身需要保证唯一性比如用户手机号、订单号就建唯一索引。MySQL会在插入和更新时做唯一性校验能防止重复数据同时它也能用于等值查询速度不比普通索引慢。再看普通索引。如果只是加速查询不需要约束唯一性就建普通索引。很多人纠结“普通索引和唯一索引查询性能谁更快”纯从查询看几乎没差别。InnoDB在普通索引上查找到第一条匹配记录后因为可能存在重复值还要沿指针往后扫到不匹配为止唯一索引查找到第一条就直接返回。但这个后扫的代价通常很小因为重复值不会太多。而写入时唯一索引需要额外的唯一性检查会多一次索引树的随机读所以写入性能上普通索引更友好。接着是前缀索引。针对长字符串字段比如TEXT、VARCHAR(200)以上的URL、文章标题如果只用于等值或前缀模糊查询可以只对字段的前N个字符建索引能大幅减少索引体积。例如CREATE INDEX idx_url_prefix ON articles (url(100));但要注意几个坑前缀索引不能用于排序和覆盖索引因为索引里没有完整的字段值。我一般在URL、文件名这类字段上使用取多少字符要看实际数据的分布。有个经验做法取不同前缀长度对比区分度直到和完整列区分度接近为止。SELECT COUNT(DISTINCT LEFT(url, 100)) / COUNT(*) AS ratio FROM articles;当ratio接近1或者接近全列去重后的比率就说明前缀足够长了。3. 实战给订单表设计一个能落地的索引方案光讲语法容易飘拿一张真实业务表走完整流程更有参考价值。下面这个例子我故意保留了一些常见的“坏味道”方便对比。3.1 场景一个被慢查询缠身的订单表假设有一张订单表线上数据量已经到500万行CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_no VARCHAR(32) NOT NULL COMMENT 用户编号, order_no VARCHAR(32) NOT NULL COMMENT 订单编号, order_status TINYINT NOT NULL DEFAULT 0 COMMENT 0待支付 1已支付 2已发货 3已完成 4已取消, pay_amount DECIMAL(12,2) NOT NULL, create_time DATETIME NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;运营经常要查“某个用户最近3个月内的已支付订单按创建时间倒序”SQL长这样SELECT order_no, pay_amount, create_time FROM orders WHERE user_no U90001 AND order_status 1 AND create_time 2024-10-01 00:00:00 ORDER BY create_time DESC LIMIT 20;在没有合适索引的情况下这条SQL的执行计划大概率是全表扫描或者只用了uk_order_no但没查order_no用不上MySQL只能一行行找出符合条件的记录文件排序后再取前20条。慢是必然的。3.2 用EXPLAIN定位索引设计的痛点建索引之前先让MySQL告诉我们它打算怎么跑这条SQL。执行EXPLAIN SELECT order_no, pay_amount, create_time FROM orders WHERE user_no U90001 AND order_status 1 AND create_time 2024-10-01 00:00:00 ORDER BY create_time DESC LIMIT 20;重点关注几个字段type如果是ALL就是全表扫描key是NULL表示没用到索引rows是预计扫描行数Extra里出现Using filesort表示要额外的排序操作。在没有合适索引时你会看到typeALLrows可能有5000000Extra里有Using where和Using filesort。这个结果基本可以判定为索引设计异常。下面我们来做索引设计。第一步列出所有参与WHERE、ORDER BY、SELECT的字段user_no等值条件、order_status等值条件、create_time范围条件排序字段、order_noSELECT字段、pay_amountSELECT字段。第二步确定组合索引的字段顺序。等值条件字段优先放在前面即user_no、order_status。但注意这里有个细节order_status区分度非常低只有0~4五个值而user_no区分度高。如果建(user_no, order_status, create_time)那么会先按user_no过滤出该用户的所有订单再在索引内进一步按order_status过滤最后用create_time做范围匹配。由于InnoDB索引叶子节点按整棵索引的顺序排列create_time放在最后还能让排序字段天然有序避免额外的filesort。是不是要把order_status放进去这个要看查询频率。如果这个条件是必填的那放进去没有坏处如果你怀疑它可能被去掉那也可以建一个去掉order_status的索引(user_no, create_time)查询同样能走索引但order_status的过滤就只能回表后再做了。权衡之下因为业务上该查询order_status是必选条件我选择把order_status也放进索引让过滤更前置。第三步考虑覆盖索引。这条SQL查询的字段是order_no, pay_amount, create_time其中order_no、pay_amount、create_time都在索引里user_no和order_status也在索引条件里。如果SELECT的字段全部包含在一个组合索引里MySQL就可以不回表直接从索引里取数据达到覆盖索引的效果。这里有一个潜在的坑表里order_no虽然有唯一索引但那是独立的uk_order_no和我们的组合索引无关。要让查询覆盖我们需要把order_no、pay_amount都加进组合索引。所以最优索引可能是ALTER TABLE orders ADD INDEX idx_user_status_time (user_no, order_status, create_time, order_no, pay_amount);等等这样索引列数有点多是不是过度设计了别急着下结论。如果这张表只服务这一个高频查询这种覆盖索引效果很好查询只需要读索引页不用回数据页。但弊端是索引树变大写入成本增加。所以需要结合整体业务来权衡。真实情况里我会先建(user_no, create_time)解决主要慢查询观察写入压力再考虑是否需要完全覆盖。为了示例我们先用一个平衡方案ALTER TABLE orders ADD INDEX idx_user_status_time (user_no, order_status, create_time);这样查询能用到user_no等值、order_status等值、create_time范围同时ORDER BY create_time也能用到索引顺序几乎不用filesort。order_no和pay_amount需要回表取但回表行数很少用户订单量有限可接受。3.3 验证索引效果和优化前后对比重新执行EXPLAINEXPLAIN SELECT ...你会看到typeref等值引用keyidx_user_status_timerows可能从几百万降到几十几百Extra里不再出现Using filesort可能显示Using index condition表示使用了索引下推MySQL 5.6。测试真实耗时对比状态扫描行数耗时无索引约500万1.8s有组合索引约5320.02s这个结果足以说明问题。如果你建完索引发现MySQL还是没走最常见的原因是统计信息没更新或者优化器觉得回表成本更高。可以用ANALYZE TABLE orders;刷新统计信息或者用FORCE INDEX先强制测试一下。4. 创建索引时那些容易踩的坑这一节是血泪经验汇总几乎每一个我都线上亲自踩过。很多问题不是索引没建对而是建完之后压根不生效或者引发新的故障。4.1 索引列上的隐式转换和函数操作最常见的失效场景就是对索引列使用函数或运算。比如建立idx_user_no后查询写成SELECT * FROM orders WHERE LEFT(user_no, 5) U9000;或者SELECT * FROM orders WHERE DATE(create_time) 2024-10-01;这种情况下即便有索引MySQL也没法对函数加工后的结果直接走B树范围查找只能全表扫描。优化器通常不会把函数拆回去。解决办法是把函数挪到等号另一边让索引列保持裸列SELECT * FROM orders WHERE create_time 2024-10-01 00:00:00 AND create_time 2024-10-02 00:00:00;另一个隐蔽的坑是字段类型不一致导致的隐式类型转换。例如user_no是VARCHAR字段但SQL里传的参数是数字SELECT * FROM orders WHERE user_no 90001;MySQL会把字符串列转成数字去比较效率变差索引也可能失效。解决方式很简单确保参数类型和字段类型一致或者强制在SQL里写字符串。这一点在Java的MyBatis里特别容易发生尤其当代码把数字和字符串混用时。4.2 用了索引但还慢的几种情况建了索引SQL还是会慢常见有以下几种第一范围条件后面再跟索引列覆盖不到。前面提到过组合索引(a,b,c)如果b用了范围c就无法走精确索引。优化器只能把c当作过滤条件回表后处理。这属于索引设计顺序问题不是失效但容易让人误以为是索引没用上。第二排序字段方向与索引顺序相反。比如索引是(create_time ASC)查询ORDER BY create_time DESCInnoDB其实可以反向扫描大多数情况下没问题。但如果是ORDER BY create_time ASC, id DESC这种混合方向且和索引顺序不一致就可能需要filesort。第三LIKE以通配符开头。WHERE name LIKE %张%无法利用索引但如果是以固定前缀开头LIKE 张%则可以走范围索引。实际业务里的模糊搜索如果前面不带%的走索引是没问题的但中间模糊基本躲不过全表扫描这时就得上全文索引或者搜索中间件了。第四数据分布让优化器放弃索引。比如order_status只有0~4五个值优化器算出来“走索引需要回表很多行不如直接全表扫描”时会选择不走索引哪怕你的索引建了。这种情况不是索引建错了而是字段本身区分度低。解决办法是用覆盖索引把需要回表的字段都放进索引里或者考虑业务上是否能把低区分度字段和其他高区分度字段组合在一起。4.3 在线DDL与锁表问题在MySQL 5.6之前InnoDB的ALTER TABLE ADD INDEX通常会先创建一张新表拷贝全部数据期间原表写的操作会被阻塞。MySQL 5.6以后引入在线DDL默认情况下ALGORITHMINPLACE, LOCKNONE允许DML并发执行。但要注意不是所有DDL都支持在线执行。具体到加索引操作MySQL 8.0中通过ALTER TABLE ... ADD INDEX通常是INPLACE不会阻塞DML。但有两个前提一是要确认版本和参数二是要注意操作时的主从延迟。即使不会锁表在超大表上加索引依然是个耗时操作因为需要重建整棵索引树。建议在业务低峰期执行或者在从库上先建好再切换。查询当前DDL状态可以用SHOW PROCESSLIST;看到State为alter table表示还在执行中。如果看到Waiting for table metadata lock说明有长事务持有表的元数据锁DDL卡住了。这时需要找到阻塞会话和业务方协商处理必要时KILL掉长事务。生产中我还遇到过一种情况MySQL 5.7里用了旧语法ALTER TABLE ... ADD INDEX由于表上有大事务DDL卡了几个小时最后导致主从延迟。后来我们统一改成用pt-online-schema-change这类工具来做大表结构变更允许分批拷贝数据能有效避免锁和主从延迟。如果你维护的表超过千万行建议研究一下这个方向。5. 进阶索引不是建完就万事大吉索引设计是一个持续的、动态的过程。表结构在变数据量在变业务查询模式也在变所以维护索引是日常工作的一部分。5.1 覆盖索引和索引下推的利用覆盖索引的价值在于减少回表。比如前面常见查询里如果SELECT字段都能从索引里取到那么查询过程中只需要扫描二级索引树完全不需要访问聚簇索引磁盘IO少一大截性能提升非常明显。怎么判断是不是覆盖索引看EXPLAIN的Extra列如果显示Using index注意不是Using index condition说明查询所需字段都在索引里不需要回表。而Using index condition是索引下推意思是存储引擎层先用索引过滤部分条件然后把含其他条件的行返回给Server层再做过滤虽然也用了索引但可能要回表。实际设计中我会为高频查询特意建一个“宽”索引把SELECT的字段都包含进去。但绝不会无脑把所有常用字段都塞进一个索引那样索引页变大缓冲池命中率下降写入成本剧增。通常做法是针对最高频的3~5个查询语句分别设计2~3个字段的组合索引再兼顾覆盖。如果一张表超过5个二级索引就要开始警惕了。5.2 索引数量和写性能的平衡量化一下假设一张表每秒钟有200次INSERT20次UPDATE。每多一个二级索引每次写入都要额外更新一棵B树。如果这棵树的节点不在缓冲池里还需要额外的磁盘读取。所以索引数量从3个涨到6个写入延时的上升不是线性的可能是成倍增长。我的个人经验是对于OLTP表单表二级索引控制在5个以内组合索引控制在3个字段以内。在读多写少的报表类表索引可以多一点。针对一个表如果它只有一份数据却要支持几十种查询姿态那就需要靠组合索引的复用性而不是每个查询都建一个索引。组合索引(a,b,c)可以支持a、a,b、a,b,c三种查询条件这就是复用。还有一种情况两个索引前缀完全一样比如已经存在idx_a_b(a,b)又建了idx_a(a)后者就是冗余可以直接删掉。结合SHOW INDEX FROM table;查看索引列表整理索引字段顺序能找出大量这种冗余。5.3 定期排查未使用和冗余索引MySQL 5.7有sys.schema_unused_indexes视图可以查看哪些索引在实例启动后从未被使用SELECT * FROM sys.schema_unused_indexes;这个功能很实用能帮你快速找到“僵尸索引”。删除之前最好先确认没有业务方手动指定FORCE INDEX依赖这个索引然后低峰期删除。删除索引的语法是DROP INDEX idx_user_status_time ON orders;另外可以用sys.schema_redundant_indexes查看冗余索引这个视图会列出和现有索引重复的索引定义。我把这个当成每月例行巡检的一部分。比如查询SELECT * FROM sys.schema_redundant_indexes \G输出里会明确指出哪个索引是冗余的、被哪个索引覆盖照着清理就行。做完清理之后别忘了重新分析表ANALYZE TABLE orders;让统计信息反映最新的索引情况避免优化器因为旧统计信息做出错误选择。6. 最后分享一点个人体会做了这么多年后端开发和数据库优化我最大的感受是MySQL创建索引不是“写一行SQL”的事而是一整套基于业务查询模式的思考过程。每次拿到慢SQL我不会急着去加索引而是先花几分钟把这条SQL涉及的查询条件、排序规则、字段区分度列出来用EXPLAIN看执行计划再决定索引怎么建。这套流程多跑几次就形成肌肉记忆了。另外建索引一定要有敬畏心。在几千万行的大表上一条ALTER TABLE ADD INDEX可能让主库IO飙升也可能引来主从延迟。我的习惯是先在小表或从库上演练确认执行时间再挑业务低峰期操作。对于超大表直接用在线变更工具宁可多等一会儿也不要堵住线上业务。索引也有生命周期。新功能上线后查询变多了旧索引可能就不适用了业务下线了索引却还挂在表上。所以巡检索引不只是做加法也要做减法。删掉那些从未用过的索引不仅降低写入压力还能让优化器少一些无谓的考虑。如果你现在有一张表线上查询很慢想尝试建索引我建议你按照这个顺序走一遍先确认慢SQL的执行计划再列出所有WHERE、JOIN、ORDER BY字段设计组合索引时把等值条件放前面、范围条件放中间、排序字段利用索引顺序最后验证EXPLAIN里是否出现了Using filesort或typeALL。这套方法可能不高级但足够稳。等你自己跑通一次看到那些从秒级变成毫秒级的查询大概就明白为什么索引这么值得研究了。