做后端这几年MySQL索引估计是聊得最多、坑踩得最多的一个话题。我在接手一个订单系统时库里单表数据过亿几个核心查询动不动就超过两秒后来把索引梳理了一遍最快的查询降到了几十毫秒。这篇文章把我实际验证过的21个MySQL索引优化实战技巧整理出来从索引原理、设计原则、SQL写法、优化器调优到运维上线每个技巧都会讲清楚为什么这么做、在什么场景下用、有哪些注意点。很多性能问题乍一看是SQL写得差背后其实是索引没设计对。索引不是“建得越多越好”也不是“只要加了索引查询就一定快”。MySQL的索引是一套有代价的数据结构每一次写入都要维护它查询时还要看优化器愿不愿意用它。所以真正有效的优化是把原理、设计、写法、执行计划串起来看。下面的21个技巧我按实际排查问题的顺序排开读完可以直接对着自己的表做一轮健康检查。1. 先看懂索引的工作原理再谈优化很多人一上来就背“联合索引最左前缀”“不要用函数”这些口诀但索引优化如果脑子里没有正确的模型口诀很容易用错。MySQL InnoDB的默认索引结构是B树理解B树、回表、最左前缀这三个基础概念后面的技巧才有地方落脚。1.1 技巧1B树在数据库里到底存了什么InnoDB的索引结构不是二叉树也不是哈希表而是B树。B树的非叶子节点只保存索引键值和一个指向下一层的指针真正的数据地址或者主键值只存在叶子节点。更关键的是所有叶子节点通过链表串起来按索引键值顺序排列。这意味着通过索引不仅能快速定位一行还能按顺序扫描一段区间的数据。可以把它类比成图书馆的索引卡片柜。柜子里的卡片按书名拼音排序每张卡片不写书的全文只写书在哪个书架哪一层。你要找某一类书先顺着卡片抽屉精准翻到对应区域再根据卡片上的位置去书架上取书。数据库里“翻卡片”的过程就是索引查找“去书架上取书”的过程就是回表。MySQL选择B树而不是B树核心原因是InnoDB以页为最小存储单位一页通常是16KBB树非叶子节点不存数据所以单节能容纳更多索引键值树的高度就能保持很矮。三到四层的B树足以支撑千万级甚至上亿行的表而磁盘IO次数基本就对应树的层数。这也是为什么主键特别长的表二级索引会明显变大二级索引叶子节点存的是主键值主键越大每个叶子页能放下的索引条目就越少。1.2 技巧2回表为什么那么贵覆盖索引又是怎么省钱的InnoDB是聚集索引组织表数据本身按主键构建的B树存放。除了主键索引之外其他索引都叫二级索引二级索引的叶子节点不存整行数据只存索引列和主键值。所以通过二级索引查到主键之后还要拿着主键去主键索引的B树里再查一次才能拿到完整行这个二次查找就是回表。回表不是每次都很慢如果命中的行数很少比如只有几行性能完全可接受。但如果是几万行的范围查询每一行都要回表就相当于随机读几万次数据页慢是必然的。避免回表最直接的办法是覆盖索引把查询需要的字段全部放进索引里让索引“覆盖”查询列。举个实际例子SELECT id, name, status FROM user WHERE name 张三;如果只在name上建了普通索引idx_name那么查询先走idx_name找到主键id再回表读取name和status所在行的完整数据。如果你建的是联合索引idx_name_status(name, status)并且查询只拿name和status那么索引叶子节点里已经有这两个值和主键id不需要回表Extra列会显示Using index。我见过一个报表查询单次扫描几十万行因为查询列和过滤列刚好可以塞进同一个联合索引耗时直接从1.8秒降到0.3秒。覆盖索引看似简单却是收益最稳定、最值得优先实施的优化方案。1.3 技巧3联合索引最左前缀匹配到底匹配了什么联合索引(a, b, c)不是把三个列分别建了索引而是按a、b、c的顺序拼接成一条复合键共同排成一颗B树。只有当查询条件里出现了最左边的a列时优化器才能利用联合索引的排序结构如果条件只写了b和cB树就无法按顺序定位因为复合键的开头部分缺失了。具体匹配规则是这样的WHERE a 1 AND b 2 AND c 3可以完整走索引WHERE a 1 AND c 3只能用到a列因为缺少b后c不能继续精确匹配WHERE b 2 AND c 3大概率走不了这个索引除非依赖后面技巧里说的索引跳跃扫描而那是有限制的。最左前缀不只是指等值条件范围条件也影响后续列。WHERE a 1 AND b 10 AND c 3中a用来定位b用于范围扫描但c没有办法继续在索引内精确过滤只能拿到主键后再判断。设计联合索引时要把等值条件放前面、范围条件放后面顺序不对等于白白损失索引列的过滤能力。要确认匹配情况最直接的方法是用EXPLAIN看key_len长度越长说明能用到的索引列越多。2. 索引设计阶段的高性价比决策索引设计是在建表阶段就要做的决定很多表一旦上线再改索引成本成倍增加。这个章节讲的是如何判断一列该不该建索引、联合索引怎么排优先级、前缀索引怎么选长度以及主键设计对索引空间的连锁影响。2.1 技巧4用区分度优先原则选择索引列判断一个列适不适合做索引先看区分度列中不重复值的比例。计算公式是COUNT(DISTINCT col) / COUNT(*)区分度越接近1索引的过滤效果越好。比如用户表的id区分度是1gender可能只有0.5左右因为男女各占一半拿它做单列索引过滤后在索引扫描阶段仍然要访问一半的数据页。我习惯用这两条SQL快速评估候选列SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_ratio FROM order_log; SELECT COUNT(DISTINCT status) / COUNT(*) AS status_ratio FROM order_log;区分度低不代表一定不能建索引还要看实际查询中选择该值后能过滤掉多少数据。比如status 1占了全表的80%建索引的意义就很小因为优化器算完成本后大概率会选择全表扫描。但如果status只有少数值是“待处理”且这类数据不到5%即便区分度数字不高只要查询高频联合索引仍然很有价值。判断的唯一标准不是公式而是“索引能帮我们跳过多少无关数据”。2.2 技巧5低基数列不要单独建索引组合才是出路低基数列比如状态、类型、月份取值范围很小单独建索引经常被优化器无视。原因不复杂一次索引扫描加回表成本可能比全表扫描还高。这类列更适合放进联合索引作为等值过滤的前导列之一或者干脆不建靠查询改写来优化。我在某个物流项目里见过这样的案例运单表在status上建了单列索引业务上status 0代表待揽收只有1%的数据。理论上这个索引应该有效但优化器统计发现status只有5个值基数太低经常选择全表扫描加过滤因为索引返回的1%行在表中随机分布回表IO反而不如顺序扫描。后来把索引改成(status, create_time)联合索引因为待揽收运单需要按时间排序导出查询既能过滤状态又能排序执行计划才稳定走索引。低基数列正确的使用方式是“服务具体查询组合”而不是单独存在。建索引前先列出这张表最核心的三条查询让联合索引去满足它们。2.3 技巧6联合索引字段顺序的黄金法则联合索引字段顺序有两个核心原则等值条件优先于范围条件区分度高的列尽量放在前面。第一条保证过滤条件能用满索引第二条可以更快缩小扫描范围。先看一个反例索引(status, user_id)查询条件WHERE user_id 123 AND status 1两个都是等值条件但status区分度低放前面意味着索引先按status分成5个区段再在对应区段里找user_id如果改成(user_id, status)索引可以先精确锁定一个用户的所有订单再按status过滤扫描量完全不同。排序需求也要一起考虑。WHERE user_id 123 ORDER BY create_time DESC如果索引是(user_id, create_time)走索引就能直接按时间倒序取出数据Extra里不会出现Using filesort。如果字段顺序反了排序还得临时在内存或磁盘里做。我建议在设计联合索引时把高频率查询的所有WHERE等值列先列出来按区分度从高到低排再把需要排序的列追加到后面范围条件永远放在最后一个“真正参与匹配”的位置。多张查询如果无法共用同一个索引果断拆成两个索引别硬凑。2.4 技巧7前缀索引的合适长度应该这样确定长字符串列比如网址、邮箱、长备注整列建索引会占用大量索引空间写入时页分裂更频繁。常见的办法是前缀索引只取字符串前N个字节作为索引键。关键是N怎么定。太短区分度不够过滤效果差太长又失去了节省空间的意义。我一般会分段比较前缀区分度目标是前缀的COUNT(DISTINCT LEFT(col, N))接近整列的COUNT(DISTINCT col)比如90%以上。实际操作可以跑这样一组SQLSELECT COUNT(DISTINCT LEFT(url, 8)) AS len8, COUNT(DISTINCT LEFT(url, 16)) AS len16, COUNT(DISTINCT LEFT(url, 24)) AS len24, COUNT(DISTINCT url) AS full_cnt FROM url_table;选定长度后直接用ALTER TABLE url_table ADD KEY idx_url(url(24))创建。这个方法能让长文本列的索引体积大幅缩小但有两个代价前缀索引不能用于覆盖索引因为索引里没有完整列值查询其他列仍然要回表也不能直接用前缀索引处理ORDER BY。如果业务上需要精确匹配或排序优先考虑把长字符串做一次哈希存一个额外列再建索引。2.5 技巧8冗余索引比想象中更贵冗余索引指的是多个索引的前缀完全一样比如已经有(a, b)又建了(a)那么(a)就是冗余索引。很多人在开发时顺手给每个查询都加索引久而久之表上出现三四个都从user_id开头的索引写放大特别明显。InnoDB每一条记录的所有索引都要跟随插入、更新、删除同步维护索引越多更新语句就越慢还有额外的磁盘占用。我在一个订单流水表上清理索引时发现表上有(user_id)、(user_id, create_time)、(user_id, status)三个索引当时单日写入超过200万行插入性能一直不达标。把第一个冗余索引删掉后插入延迟下降了约20%查询完全不受影响。排查冗余索引可以查系统表但最简单的方法是人为梳理打开慢查询日志里的所有SQL把每一条SQL会用到的索引列画出来合并成最少的索引集合。原则是尽量让一个联合索引覆盖多个高频查询但也不要为了“覆盖”把所有列都塞进一个索引那样索引太大扫描效率反而差。2.6 技巧9主键设计会连锁影响每一个二级索引InnoDB的二级索引叶子节点存储的是主键值所以主键越短每个二级索引的叶子页能容纳的条目就越多整棵索引树就越矮IO越少。反过来如果主键是36字符的UUID所有二级索引都会跟着膨胀空间和性能都被拖累。更麻烦的是随机主键。UUID是无序生成的每次插入新记录B树都可能需要随机插入到任意页触发页分裂产生大量碎片。而自增整数主键按顺序插入数据基本追加在树的右侧页分裂少写入更平滑。我在一个千万级用户表上做过对比把业务自然键手机号从主键改成自增id主键和二级索引的总体积下降了40%左右导入速度提升明显。有人担心自增id会暴露数据量这是业务问题可以用雪花ID这类趋势递增的有序ID替代前提是生成算法保证单调性。最理想的主键是短、有序、稳定不变比如INT或BIGINT自增列。用业务字段做主键要非常谨慎任何业务规则的微调都可能导致主键变更进而引发所有二级索引的大规模更新。3. SQL写法中的索引利用艺术索引建好了SQL写法不对优化器也发挥不出来。这个章节是我踩坑最多的地方LIKE、OR、函数、隐式转换、排序、深分页每一个都曾让线上索引失效。把这些写法问题改掉往往比加索引更快见效。3.1 技巧10LIKE查询只有前缀匹配才容易走索引B树按索引键值有序排列所以LIKE abc%可以看成范围查询优化器能够从abc起始处连续扫描索引是有效的。但LIKE %abc或LIKE %abc%不行因为目标字符串可能出现在任意位置无法利用有序结构做范围定位大概率退化成全表扫描。一个常见的需求是搜索用户昵称中包含某个词比如LIKE %张%这种写法无论怎么建索引都基本用不上。我有两个替代方案如果前缀就是用户输入的真实开头尽量让用户在客户端补全比如用户名 LIKE 张%如果后缀匹配是硬需求可以考虑把字段反转后存储一份建一个反转列索引然后查LIKE 张%对应的反转值前缀。数据量非常大且全文检索需求强烈时建议直接引入全文索引或外部搜索组件不要让数据库硬抗模糊查询。执行计划里如果看到type index或者type ALL而Extra显示Using where很可能就是模糊匹配没有真正利用索引结构。需要明确的是覆盖索引对LIKE %xx%也有帮助因为虽然无法利用叶子节点的有序性做范围匹配但如果能从索引直接取得所有需要的列优化器可能选择扫描整个索引而不是全表数据页整个索引通常比整表小很多这算是一种降级优化。3.2 技巧11OR条件别硬拼成一条有时拆成UNION更快WHERE a 1 OR b 2这种写法优化器理论上可以把条件拆成两个索引扫描再做合并。MySQL确实提供了Index Merge优化但前提是a和b都有合适的单独索引。即便走了Index Merge两个索引扫描结果还需要做去重合并临时结构的开销仍然存在不如直接精简查询。我习惯把复杂OR拆分成两条独立查询再用UNION ALL合并让每条查询自己走最优索引。比如SELECT * FROM orders WHERE user_id 100 AND status 1 UNION ALL SELECT * FROM orders WHERE order_id 8888 AND status 1;这里user_id和order_id都有索引两条子查询各自走一个索引最终只是简单拼接。要注意少用UNION因为默认的UNION会做去重多一次排序开销业务上如果两个子查询结果不可能重叠就明确用UNION ALL。多数情况下OR改写并不复杂但改完一定要用EXPLAIN确认执行计划不要凭感觉。3.3 技巧12索引列上的函数操作和隐式转换是隐形杀手在索引列上加函数比如WHERE DATE(create_time) 2024-01-01会让优化器无法直接使用create_time索引因为B树里存储的是原始时间值不是函数处理后的结果。我们习惯写成范围查询来解决WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00这个写法既能用上索引也更容易让优化器精确计算扫描范围。隐式转换也是同类问题。比如表的phone列是varchar查询写成WHERE phone 13800001111数字会隐式转换为字符串但MySQL可能在列上应用转换规则导致索引失效。我排查慢SQL时看到WHERE order_no 202401010001这样的条件会先检查order_no字段类型。如果它是varchar一律改成字符串字面量WHERE order_no 202401010001。既消除转换也避免意外的比较结果。任何CAST(col AS ...)、DATE_FORMAT(col, ...)这类写在索引列上的操作都值得警惕。3.4 技巧13IN和EXISTS别只看经验用执行计划说话“小表驱动大表IN适合大表EXISTS适合小表”这种话在MySQL 8.0里已经越来越不灵了。优化器会尝试把子查询转换成半连接也会生成物化临时表但临时表能不能用上索引才是真正的关键。有一类慢查询长这样SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip_level 5);MySQL可能会把users查询结果物化成一张临时表然后和orders做关联但如果物化结果很大或者orders.user_id没有索引整个查询性能就会很差。更稳定的写法是用JOINSELECT o.* FROM orders o JOIN users u ON o.user_id u.id WHERE u.vip_level 5;重点不是纠结IN还是EXISTS而是保证关联字段两侧都有索引然后打开执行计划确认有没有出现大临时表、有没有全表扫描。另外IN列表不要写得过长几百上千个值会导致优化器构造大量OR条件索引范围扫描的优势会被削弱这种情况下拆成临时表和JOIN反而更快。3.5 技巧14让ORDER BY走索引避免Using filesort排序是非常容易被忽视的优化点。ORDER BY的字段只要和WHERE条件构成联合索引的最左前缀优化器就能直接利用索引的有序性取出数据不需要额外排序EXPLAIN里不会出现Using filesort。举例说明索引是(user_id, create_time)查询SELECT * FROM orders WHERE user_id 123 ORDER BY create_time DESCuser_id定位到具体用户create_time在索引内部已经有序倒序读取即可完全不需要filesort。但如果索引是(create_time, user_id)WHERE user_id 123没法在索引开头用上排序也只能临时做。另一个容易踩的坑是排序方向不一致ORDER BY a ASC, b DESCB树默认统一方向反向扫描一个字段也许可以但混合方向通常需要额外的排序操作。如果实在无法避免filesort也不要太紧张。排序数据量不大时内存排序很快只有当结果集特别大超出sort_buffer_size才会溢出到磁盘文件那才是性能雪崩。优化方向一是让结果集尽量小二是能用索引就用索引三是必要时调整排序缓冲区参数。3.6 技巧15深分页翻页慢试试游标分页和延迟关联分页越深越慢LIMIT 1000000, 20不是只取20条而是必须扫描前1000020条再丢掉前1000000条前期的扫描和回表都浪费了。我给业务方给出的优化方向有两个。第一个是游标分页也就是“下一页”式查询。假设按id排序SELECT * FROM orders WHERE id 1000000 ORDER BY id LIMIT 20;这样每次查询只从上一页最后一条id开始顺序扫描20条完全避开了深偏移量。前提是业务允许用“上一页/下一页”而非随意页码跳转。第二个是延迟关联SELECT o.* FROM orders o JOIN ( SELECT id FROM orders WHERE create_time 2024-01-01 ORDER BY create_time LIMIT 1000000, 20 ) tmp ON o.id tmp.id;子查询只扫二级索引拿到主键后再一次性回表20条而不是扫描百万行时每次都回表性能提升极其明显。凡是报表、列表页存在深分页我都优先推荐这两个方案。4. 优化器与索引选择的调优手段有时候SQL没问题、索引也建了查询还是慢问题可能出在优化器选错了索引。这个章节讲统计信息、索引提示和EXPLAIN分析是排查执行计划问题的必备技能。4.1 技巧16统计信息不准时执行计划会“眼瞎”MySQL优化器根据表的统计信息估算扫描行数、索引基数、IO成本再选择执行计划。如果统计信息表长时间没有更新或者大表数据剧烈波动优化器对行数的估算就可能严重失真明明有更好的索引它就是不用。我遇到过很典型的情况一张千万级流水表新加了一个日期联合索引等值查询返回的数据量实际上只有几千行但优化器统计认为该索引会扫描全表的一半数据最后选择了全表扫描加过滤。当时用ANALYZE TABLE刷新统计信息后执行计划立刻改走新索引查询从2秒降到80毫秒。InnoDB的统计信息来自随机采样是估算值不是精确值。对高频变更的大表可以设置innodb_stats_auto_recalc让它自动重新计算也可以在核心发布流程里手动跑一次ANALYZE。要注意ANALYZE会消耗资源业务高峰期执行会拖慢写入最好放在低峰期。如果统计信息频繁不准甚至要考虑是否有频繁的大规模插入删除这时候分区表或归档可能是更合适的方案。4.2 技巧17FORCE INDEX是排查工具不是长期依赖FORCE INDEX可以强迫优化器使用某个索引在排查问题时非常有用。比如我们怀疑优化器选错索引可以先强制指定目标索引确认性能变化再回头找优化器选择错误的原因。强制索引的写法可以是这样SELECT * FROM orders FORCE INDEX(idx_user_status) WHERE user_id 123 AND status 1;但我不建议把FORCE INDEX留在生产SQL里长期运行。原因在于索引和数据分布会变今天最适合的索引可能三个月后不再适合强制指定会阻碍优化器后续的调整。FORCE INDEX的真正价值是用它验证“索引好不好用”而不是替优化器做决定。如果发现强制索引确实更快更安全的手段是想办法让优化器也意识到这一点更新统计信息、改写SQL让过滤条件更准确、或者删除干扰优化器的低效索引。我通常把FORCE INDEX作为临时止血方案记录在案然后推动索引重构等重构完成再移除。4.3 技巧18EXPLAIN别只盯着key这些列才是关键日常排查慢查询EXPLAIN是第一个动作。很多人只看key有没有索引但key走了索引不代表执行计划好。真正需要重点看的是type、rows、filtered和Extra。type有几种值需要心里有数const是主键或唯一键等值查询最好ref是非唯一索引等值匹配也很好range是索引范围扫描可以接受index是扫描整个索引树通常比全表扫描好ALL是全表扫描必须警惕。rows表示优化器估算要扫描的行数这个数字要结合实际过滤效果看key用了索引但rows还是几百万说明索引选择还不够精准。Extra里最需要关注的是Using filesort、Using temporary和Using index。举一个我实际排查过的慢查询EXPLAIN SELECT order_id, amount FROM orders WHERE user_id 123 ORDER BY create_time DESC LIMIT 20;当时表上有索引(user_id)EXPLAIN结果type refkey idx_user_id但Extra里出现Using filesort原因就是create_time不在同一个索引里。最后把索引改成(user_id, create_time)Using filesort消失查询耗时下降了一个数量级。看执行计划要比对多个字段顺着rows和Extra往前反推缺失的索引结构才是EXPLAIN的正确用法。5. 运维阶段与大数据量场景的索引进阶到线上维护阶段加索引不再只是写一条DDL那么简单。大表索引变更会影响线上写入冷热数据的选择也会反过来影响索引设计。最后三个技巧我把上线运维和大数据量场景里最容易忽视的点补上。5.1 技巧19在线加索引要盯着磁盘、IO和主从延迟MySQL 8.0对于普通加索引操作默认支持INPLACE算法和LOCKNONE理论上不阻塞读写但“不阻塞”不等于“没有代价”。创建索引需要对表数据做全量扫描和排序再把新索引结构写盘这个过程中磁盘空间会临时增长IO也会明显抬高主从环境下容易产生复制延迟。我在大表上执行ALTER TABLE ... ADD INDEX时会先检查几个指标表大小、剩余磁盘空间是否至少有表体积的1倍以上、当前IO利用率、主从延迟情况。低峰期执行是底线如果表实在太大我建议用在线DDL工具来分批处理比如常见的开源工具pt-online-schema-change它通过触发器同步增量数据把DDL对在线业务的影响降到最低。更稳妥的做法是先创建一个小规模的影子表在影子表上加好索引验证查询性能再决定是否在正式表上执行。加完索引后还要重新检查执行计划统计信息可能需要刷新否则新索引可能不会被优化器选上。5.2 技巧20Index Merge和Skip Scan可以知道但别指望MySQL优化器有两种相对高级的执行计划Index Merge和Skip Scan。Index Merge指一条SQL的多个OR或AND条件分别扫描多个索引再把结果合并或取交集Skip Scan指联合索引缺少最左前缀时优化器通过枚举左边列的每个值来扫描后续列。这两种能力在特定场景下有效比如索引(a, b)查询条件只有WHERE b 1如果a的枚举值不多MySQL 8.0.13之后可能用Skip Scan走这个索引。但这类优化限制很多a必须是有少量不同值的列否则跳列扫描的代价比全表扫描还高。Index Merge则要求每个条件都有独立索引合并去重的开销也可能抵消它带来的收益。依赖这些高级特性很容易踩坑。数据分布稍微一变优化器就可能从Skip Scan回到全表扫描执行计划非常不稳定。我在设计索引时仍然严格按最左前缀来匹配查询Index Merge和Skip Scan只当作优化器给出的“额外惊喜”不做核心依赖。如果业务确实需要跳过最左列查询老老实实为高频条件再建一个合适的索引。5.3 技巧21冷热分离后热表索引越少越稳当一张表数据量持续增长到几亿行时靠连续加索引解决性能问题已经不现实。更彻底的做法是冷热分离把近期高频访问的数据留在热表历史数据迁到归档表。热表数据量控制在千万级以内索引反倒可以做得更精简。我参与过的一个订单查询系统最早把所有订单都放在一张大表里各种查询条件加起来建了十来个索引。后来引入归档策略三个月前的订单迁到另一张归档表热表只剩最近订单查询集中在user_id和create_time上。我把热表索引精简到两个联合索引写入性能、查询性能都恢复到了理想状态。归档表因为查询模式以统计报表为主索引设计也完全按归档查询重新调整。冷热分离后的重点是要保证应用层路由正确查询默认走热表只有时间超过一定范围才去查归档表。对数据的访问模式要定期统计每个月看一次慢查询日志如果某类查询不再是核心对应的索引就要果断砍掉。索引最理想的形态永远是“刚好满足现状需求”而不是无限堆叠。整理这21条技巧时我最想强调的还是那句话索引优化不是建得越多越好而是要把每一张表、每一条核心SQL的访问路径都搞清楚。我自己习惯每季度把慢查询日志拿出来过一遍用EXPLAIN逐个看执行计划有问题再改索引。这套流程坚持下来比临时抱佛脚有效得多。