前两天有个后端同事跑过来找我说他们订单查询接口突然慢得离谱本来十几毫秒的请求变成了30多秒才返回。我把慢查询日志捞出来一看单表已经攒了2000多万行查询条件里user_id、status、create_time全都有可这表上就孤零零一条主键索引。当时我就知道这不是SQL写法的问题是索引设计压根没跟上业务增长速度。今天就把 MySQL 索引优化这件事从底层原理到实战落地完整捋一遍。这篇文章我会从B树索引到底怎么工作讲起再到联合索引字段顺序怎么排、如何用 EXPLAIN 验证索引到底走没走、索引失效的隐藏场景最后分享一些只有踩过坑才知道的经验。适合刚接触索引的新手也适合正在排查慢查询、设计新表索引的开发同学。索引优化不是玄学它有一套可推理可验证的方法看完这篇文章你可以直接拿这套方法去处理手上的慢SQL。1. 建立索引之前先把这几个底层问题想清楚1.1 B树凭什么能扛住千万级数据MySQL 里的 InnoDB 引擎用的索引底层结构是 B 树本质是一棵多路平衡查找树。你不需要把它的旋转逻辑背下来只需要记住三个和实际查询直接相关的特点叶子节点才真正存数据非叶子节点只存键值和指针叶子节点之间用链表串起来天然支持范围扫描树的高度决定了查询要走几次磁盘IO。为什么它能在千万级数据下保持极快的查询速度关键在于页的大小。InnoDB 默认一页是 16KB非叶子节点里每条记录大概就是某个索引值和指向下一个节点的指针假设占 14 字节那么一页大约能放 1170 个条目。往下一层如果叶子节点里紧凑地存几百条行数据三层树结构大概就能覆盖千万级的数据量。换句话说哪怕表里有两千万行走主键查询也只需要大约三次磁盘IO这比几千次全表扫描判断一个等值条件要经济太多。这个树越高IO越多的特性也引出了索引设计的一个重要原则索引字段尽量够短够小。比如能用int做主键就不要用varchar(32)的 UUID因为非叶子节点一个条目占用越小一页能容纳的指针就越多树就越矮查询就越快。很多人刚开始建表不重视这个等到数据量上来发现更新和查询都变慢才意识到这个选择埋了雷。1.2 聚集索引、二级索引和回表的关系InnoDB 里每张表都有一个聚集索引通常就是主键索引它的叶子节点直接保存整行数据。除了聚集索引之外的索引都叫二级索引也有的叫辅助索引。二级索引的叶子节点里保存的是索引列的值加上主键值。这里就引出了回表这个概念当你通过二级索引查询需要的字段在二级索引的叶子节点里找不到时就必须拿着主键回到聚集索引里再查一次整行数据。一次回表就意味着一次随机IO数据量小的时候感受不明显千万级数据下回表次数一多性能就会直线下降。举个例子如果一张用户表上有索引idx_user_name(user_name)执行SELECT user_name, phone FROM user WHERE user_name 张三MySQL 会先通过二级索引找到记录的主键和user_name但phone字段不在这棵索引上于是需要拿主键回表取phone。如果执行的是SELECT user_name FROM user WHERE user_name 张三二级索引里已经有了user_name无需回表这就是覆盖索引带来的收益。理解回表之后你就会明白为什么不少 SQL 优化的第一选择不是疯狂加索引而是把查询需要的字段塞进一个联合索引里制造覆盖索引。1.3 索引不是免费的无脑建索引必然翻车很多开发同学有个朴素的想法查询慢了加索引还有问题再加索引反正是空间换时间。但索引本质上是牺牲写入性能换取查询性能空间和时间都不是免费的。索引至少有三个代价。第一是存储空间的代价一个复合索引可能占用比表数据本身还要大的空间。第二是写入维护的代价每次 insert、update、delete 都不仅要更新表数据还要同步更新所有相关索引索引多了写入自然变慢。第三是优化器选择代价一张表十几个索引MySQL 优化器在计算执行计划时也会因为候选索引过多而选错有时候甚至宁可全表扫也不选你的索引因为它根据统计信息估算出来的代价更小。我在实际工作中见过最夸张的一张订单表建立了21个索引业务查询慢不说单表的写入也严重下降后来做了一次索引瘦身只留下了覆盖核心高频查询的5个索引写入立刻提升30%。所以记住这个原则索引不在多而在精准。一个设计良好的联合索引能顶得上五六个零散的单列索引。2. 索引设计与创建联合索引是重头戏2.1 从一条真实业务查询反推索引方案只看理论容易飘我用一个实际订单查询来拆解。假设有下面这样一张表CREATE TABLE order_info ( id bigint NOT NULL AUTO_INCREMENT COMMENT 主键, order_no varchar(32) NOT NULL COMMENT 订单号, user_id bigint NOT NULL COMMENT 下单用户ID, product_id bigint NOT NULL DEFAULT 0 COMMENT 商品ID, status tinyint NOT NULL DEFAULT 0 COMMENT 订单状态 0待支付 1已支付 2已发货 3已完成 4取消, amount decimal(10,2) NOT NULL DEFAULT 0 COMMENT 订单金额, create_time datetime NOT NULL COMMENT 下单时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT订单信息表;业务方最常执行的查询是查某用户在某个状态下的最近订单列表SELECT order_no, amount, status, create_time FROM order_info WHERE user_id 888888 AND status 1 ORDER BY create_time DESC LIMIT 10;这时你该怎么设计索引一个常见的错误做法是给user_id和status分别建单列索引然后赌 MySQL 会自己合并两个索引。暂且不说 index_merge 的触发条件和效率即使走了两个索引最终还得回表取order_no和amount性能远不如一个联合索引来得干脆。正确的思路是这个查询的等值条件有user_id和status排序条件有create_time需要返回order_no、amount等字段。我会把等值条件字段放在联合索引前面把排序字段放在等值条件后面如果空间允许把需要返回的字段追加到最后一并形成覆盖索引。ALTER TABLE order_info ADD INDEX idx_user_status_time (user_id, status, create_time);执行完这个索引之后MySQL 可以直接在二级索引上定位到user_id 888888 AND status 1的区间然后沿着叶子节点的链表按create_time倒序读取前10条记录。这时候虽然order_no和amount不在索引上每条还是要回表但只回表10次完全可接受。如果追求极致可以再加一段覆盖索引ALTER TABLE order_info ADD INDEX idx_user_status_time_cover (user_id, status, create_time, order_no, amount);建这个覆盖索引的理由很简单高频查询只要这5个字段二级索引叶子节点已经有全部内容EXPLAIN 里会看到Using index连回表都省了。但注意覆盖索引会让索引宽度变大写入和存储成本上升适合明确的高频请求不适合把整张大表的所有字段都塞进去。2.2 联合索引字段顺序的三个排列原则联合索引字段顺序是新手最容易纠结的问题它的核心依据是最左前缀原则。MySQL 只能从联合索引的最左列开始连续匹配字段。索引(a, b, c)上能用到(a)、(a, b)、(a, b, c)的查询组合但where b 1 and c 2这种跳过第一列的情况很难有效使用索引定位。在这个前提下字段顺序的排列原则可以总结为三条。第一条等值条件放前面范围条件放最后。where user_id 888888 and status 1都是等值不需要特殊处理。如果查询里有create_time 2024-01-01这种范围条件把create_time放在最后会比较安全因为范围类型一旦出现它后面的字段就无法继续精确定位只能做过滤。你总不希望一个范围字段把后面几个等值字段的路都堵死。第二条把区分度更高的字段尽量前置。区分度指的是某个字段的不同值占比可以用SELECT COUNT(DISTINCT user_id) / COUNT(*) FROM order_info这种SQL量出来。一般来说区分度高、能更快速缩小扫描范围的字段放在前面收益更大。但这条不是绝对真理如果某个区分度不高的字段是业务高频等值条件而区分度高的字段很少出现在查询里那还是以这张表真实查询模式为准。第三条把排序和分组字段纳入索引末尾。如果查询里有ORDER BY create_time DESC或者GROUP BY这类需求把字段放进联合索引可以避免 MySQL 做额外的 filesort 排序。但有一个前置条件排序字段必须和前面能命中的索引列组成一个最左前缀否则排序优化依然无法触发。你完全没必要背死规则多看几次 EXPLAIN 的Using filesort就知道该怎么调整顺序。2.3 联合索引的隐藏收益覆盖高频查询用覆盖索引优化查询是成本最低、收益最直观的手段。很多人的思维还停留在建好索引就等于走索引却忽略了二级索引走完之后的回表代价。假设有个查询是统计用户的订单总金额你写SELECT user_id, SUM(amount) FROM order_info WHERE user_id 888888 GROUP BY user_id;如果只有idx_user_status_time这个索引那么即便能快速定位到目标用户的记录依然要回表读取amount字段才能完成求和。如果有一个(user_id, amount)覆盖索引二级索引叶子节点直接携带所有需要数据整个查询不需要回表耗时能够明显下降。这里要特别说明覆盖索引和普通联合索引的差异在 EXPLAIN 的 Extra 列里一眼就能看出来。如果出现Using index说明这条查询已经通过索引直接覆盖到了所有需要的数据没有回表。如果只是Using index condition说明走索引的同时还带了条件下推但仍然可能需要回表。如果出现Using where往往意味着索引用了一部分剩下字段到Server层再做过滤效率就有折扣。2.4 创建和删除索引的几个实操要点创建索引最直接的SQL是CREATE INDEX idx_user_status_time ON order_info (user_id, status, create_time);和ALTER TABLE添加索引在效果上基本等价。如果要删除一个索引ALTER TABLE order_info DROP INDEX idx_user_status_time;这段操作看起来简单但有几个实际要点值得提醒。第一尽量在一张表批量调整索引时集中操作避免反复重建表。第二大表在线加索引会产生额外压力8.0 对不少索引操作默认支持在线DDL但仍建议放在业务低峰执行。第三如果是一张上千万行的表直接ALTER TABLE加索引可能会触发长时间锁和主从延迟最好用pt-online-schema-change这类工具分批操作或者干脆在业务低峰执行。注意删除索引前一定要通过information_schema.statistics或工具确认是否存在冗余依赖。举个例子如果表上已有联合索引(a, b, c)那么独立的(a)索引基本都是冗余的它可以删但(b)和(c)不一定冗余因为联合索引无法命中以b或c开头的查询。删除前一定要对照业务查询做判断。3. 实操从定位慢SQL到索引落地全流程3.1 第一步把慢查询日志打开做索引优化最蠢的方法是拿着一张表在本地脑补查询最靠谱的方法是直接看线上真实慢SQL。MySQL 慢查询日志就是定位问题的第一步。在配置文件中加上[mysqld] slow_query_log 1 slow_query_log_file /var/log/mysql/mysql-slow.log long_query_time 1 log_queries_not_using_indexes 1long_query_time表示超过多少秒的记录会进慢日志线上环境一般从1秒起步数据量大的系统可以先从2秒或者5秒开始排查避免日志量爆炸。log_queries_not_using_indexes表示没走索引的查询也记下来这个参数对发现漏建索引特别有用但它也会把大量短小但无索引的查询塞进日志正式开启后要定期清理。如果不想改配置文件重启也可以动态开启SET GLOBAL slow_query_log ON; SET GLOBAL long_query_time 1;但要清楚这种方式只对当前实例生效重启后失效适合诊断问题临时开长期使用还是建议写进配置文件。开启之后用mysqldumpslow -s at -t 10 /var/log/mysql/mysql-slow.log可以快速把耗时最长的 Top N SQL 拉出来这就是你要重点优化的对象。3.2 第二步用EXPLAIN看执行计划拿到慢SQL之后别急着加索引先看它的执行计划。在 SQL 前加EXPLAINEXPLAIN SELECT order_no, amount, status, create_time FROM order_info WHERE user_id 888888 AND status 1 ORDER BY create_time DESC LIMIT 10;重点看几个字段type表示扫描类型从优到差大致是system const eq_ref ref range index ALL。如果看到ALL说明是全表扫描这就是慢SQL的根源。key表示最终选中的索引如果后面括号里为空说明没走索引。rows是优化器估算的需要扫描行数这个值越大通常越慢。Extra里如果出现Using filesort或Using temporary说明 SQL 在索引层面没有解决排序或分组需要额外消耗内存和CPU。继续拿前面那个订单SQL举例如果执行计划里显示type: ALL、rows: 20000000、Extra: Using filesort那就很明确了全表扫了两千万行然后还要在内存里做排序怎么可能快。建立idx_user_status_time之后执行计划大概率会变成type: refkey: idx_user_status_timerows降到几十或者几百Using filesort消失因为排序已经可以直接从索引里按顺序读了。3.3 第三步分阶段验证优化效果索引优化最忌讳一把梭直接在生产环境乱改。我的标准流程是这样先把慢日志里的SQL统一收集起来按出现频率和平均耗时排序然后在测试环境用同样规模的数据恢复现场逐条执行 EXPLAIN设计好索引后再在测试环境执行一遍 SQL对比扫描行数和响应时间最后才在低峰期到生产环境加索引。我经常看到有人加完索引之后只看一眼执行计划里key有值就宣布优化完成。这种判断其实不够稳妥更可靠的验证方式是执行真实SQL两次第二次命中buffer pool缓存之后再看耗时。第一次执行时数据页要从磁盘加载时间偏高是正常的第二次才接近线上稳定状态。如果第二次仍然有较大提升才说明索引真正解决了问题。3.4 大表加索引的在线操作经验如果索引已经设计好了但表特别大比如千万级甚至上亿直接执行一条ALTER TABLE是很危险的动作。MySQL 8.0 的很多在线DDL操作默认不会锁全表但加索引过程中仍然会占用大量IO和日志可能拖垮主库导致从库延迟飙升。实际项目中我遇到过两次教训一次是凌晨对大表加索引还是把主库CPU吃到了90%以上业务出现明显抖动。从那之后凡是对超过千万级的表加索引我基本都用pt-online-schema-change来处理它通过创建临时表、拷贝数据、替换表结构的方式完成DDL虽然耗时更长但对线上影响小很多。如果公司有数据库平台支持原生在线加索引也可以借平台的低峰窗口操作。核心原则是索引加得再正确也不能在高峰期用粗糙的执行方式毁掉线上稳定性。4. 索引失效场景与排序分页的坑4.1 五个让索引失效的隐形杀手索引设计出来了不代表它一定会被用上以下几个场景是我在踩坑记录里反复出现的高频索引失效原因。第一对索引列做了函数或运算。比如WHERE DATE(create_time) 2024-01-01或者WHERE amount 1 100。MySQL 对普通二级索引无法直接对这个字段做函数计算所以这个条件基本不会走索引。解决办法是改写条件把函数移到右侧比如create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。在 MySQL 8.0.13 之后也可以建函数索引但能改写还是优先改写。第二隐式类型转换。比如某张表的user_id是varchar类型你写WHERE user_id 123MySQL 会把字符串和数字对比时的字段类型都转成数字这一转就可能让索引失效。判断方法很粗暴看执行计划的key_len和type或者直接把条件和表结构对照一下确保关联字段和比较字段的类型完全一致。第三违反最左前缀。联合索引(a, b, c)上直接查b或者c开头大概率走不了完整索引。这要求设计联合索引时要想清楚业务查询模式而不是把高频字段随便丢在一起。第四使用了左模糊。WHERE order_no LIKE %888%无法用普通索引因为模糊匹配必须知道起始位置。解决办法可以是改为前缀匹配LIKE 888%或者引入全文索引、ES 等检索方案。第五OR条件横跨无索引字段。WHERE user_id 1 OR status 1如果其中status没有索引优化器往往选择全表扫描。可以用UNION改写或者把两列放进同一个联合索引让优化器有足够选择的空间。注意这不是绝对必然执行计划会基于代价选择但从经验看OR两边字段如果没有合理索引支撑性能翻车概率极高。4.2 排序优化与深分页问题日常优化中经常被忽略的还有排序。如果你查询里有ORDER BY但该字段不在索引里MySQL 会走 filesort先查出一批数据放到内存或者临时文件中排序再取前N条。少量数据没什么感觉百万级数据时这步排序可能比整个查询还耗时。要利用索引消除排序推荐的做法是让ORDER BY字段排在联合索引里等值条件之后。还是订单表那个例子索引(user_id, status, create_time)能完美支撑WHERE user_id 888888 AND status 1 ORDER BY create_time DESC因为前面两个等值条件定位区间后叶子节点本身已经按create_time有序排列MySQL 倒过来读就行。另一个高频坑是深分页。LIMIT 100000, 10看起来是只要10条但 MySQL 实际要把前面10万条都扫描出来再丢掉。在联合索引支持排序时也一样要遍历。更优的方案是用条件分页或游标分页把上一页的create_time作为下一个查询条件SELECT order_no, amount, create_time FROM order_info WHERE user_id 888888 AND status 1 AND create_time 2024-01-15 12:00:00 ORDER BY create_time DESC LIMIT 10;这样每次查询都从指定游标开始索引直接跳过了10万行扫描响应时间非常稳定不会随页数增长而恶化。4.3 索引碎片、统计信息与更新维护索引在频繁增删改之后会产生碎片。比如大量随机删除造成数据页空洞或者页分裂导致索引顺序不合理都会让同样的查询付出更多IO。对已经确认碎片比较严重的表可以执行OPTIMIZE TABLE order_info。但这个方法会重建表线上执行时要谨慎最好在低峰进行。另外一个容易忽略的问题是统计信息不准确导致优化器不走索引。MySQL 优化器选择执行计划依赖统计信息如果某些索引的基数统计明显失真它可能宁可全表扫也不走索引。遇到这种情况可以先执行ANALYZE TABLE order_info;更新统计信息后再看执行计划。这招看起来简单却是我处理索引明明存在但优化器就是不选它问题时最常用的第一步很多时候一条 ANALYZE 就能解决。注意不要动不动就用FORCE INDEX强制指定索引。FORCE INDEX是在紧急情况下让查询先恢复可用但业务数据是动态的索引选择的最优解也会变。长期通过强制索引压住优化器是在给未来埋债正确做法还是把索引和统计信息本身维护好。5. 常见问题与排查技巧实录5.1 加了索引还是慢的三种典型原因很多人最困惑的一句话是我明明加了索引为什么慢SQL还是慢我梳理几个常见原因。第一种索引走是走了但回表次数太多。比如用辅助索引定位到了几万条记录每条都需要回表性能反而比不上索引覆盖。解决方法就是把查询需要的字段补进联合索引让查询变成覆盖索引。第二种SQL写法里有隐式转换或函数操作索引建好了但执行计划根本没考虑使用它。排查这类问题特别简单直接看 EXPLAIN 的key字段如果没有你刚建的索引就要回头检查SQL写法。第三种优化器根据统计信息认为走索引还不如全表扫。这通常出现在字段区分度很低比如status里90%都是同一种状态或者统计信息过期。前者要重新审视索引设计后者可以先做 ANALYZE再确认是否真的需要这个索引。5.2 索引相关问题速查表现象可能原因排查与解决思路执行计划 typeALLkey为空没建索引、索引失效、SQL写法问题用EXPLAIN对照条件字段检查函数、隐式转换、最左前缀Extra显示Using filesort排序字段不在索引中或顺序不匹配把排序字段加入联合索引末尾确认排序方向和索引方向一致索引存在但没被选择统计信息不准、字段区分度低先ANALYZE TABLE再评估字段区分度必要时精简冗余索引查询走了索引还是慢回表次数太多、扫描区间过大用覆盖索引消除回表或优化查询条件缩小扫描范围写入性能明显下降索引过多、索引字段过长用未使用索引检查工具清理冗余压缩索引字段JOIN查询很慢关联字段无索引或字符集不一致确认被驱动表关联字段有索引并检查字符集和排序规则统一5.3 几条写在最后的个人经验伴随这些案例我最后想分享几个自己长期坚持的做法。首先我建索引前一定会先看慢查询日志业务驱动大于原则驱动不要凭空造索引。其次我会定期用系统表检查未使用索引比如sys.schema_unused_indexes把这些吃空间不干活的索引清掉给表做减负。最后索引设计要留一点可扩展意识一张表的核心高频查询就那么几条你只需要为它们设计一个真正好用的联合索引就够。再补充一个小技巧联合索引字段顺序优化时别只看区分度要把等值条件、范围条件、排序需求放在同一张纸上一起推导。很多时候我都是把业务SQL整理成清单逐条做标记哪个字段出现在哪个位置一目了然。等值字段排前面范围字段靠后排序字段尽量跟在等值和范围的后面需要覆盖查询就把低频返回字段追加到索引尾端。这套方法简单但在我接触过的绝大多数数据库项目里都有效。最后说一句索引优化是一个持续迭代的过程不要指望一次改造一劳永逸。新版本MySQL会带来新能力比如8.0的不可见索引、函数索引、降序索引都可以成为你手里的工具但前提是你先掌握最基础的设计逻辑。把核心原理吃透遇到再复杂的慢查询你也能顺着执行计划一层层找到答案。