1. 先搞清楚索引是把钥匙还是导航1.1 索引的本质从翻书找内容说起很多刚接触MySQL的朋友会把索引理解成一把万能钥匙认为只要有索引查询就一定会快。但实际踩过几次坑之后你会发现索引更像一本图书的目录导航它帮你快速定位到数据所在的物理位置而不是让你直接拿到数据本身。这个理解上的偏差直接决定了你后续优化思路是否正确。我们拿一张真实的业务表来举例。假设有一张订单表里面有两百万行数据你要查某个用户在最近一个月内的所有订单。没有索引的时候MySQL只能从头到尾把两百万行数据全部读一遍逐个判断用户ID和下单时间是否满足条件这就是全表扫描full table scan。全表扫描意味着两百万次判断磁盘IO的消耗是实打实的慢才是正常现象。有索引之后MySQL可以先通过索引这个目录找到满足条件的记录的物理地址然后再回表把主键拿回去查完整行数据取出完整记录。这个过程就像你在一本几百页的书里查找某个关键词有目录和没目录的体验完全是天壤之别。不过这里有一个很关键的点索引不是免费的午餐。每建立一个索引写入数据的时候就要额外维护一份索引结构插入、更新、删除操作的性能都会受影响。所以索引优化不是一个越多越好的问题而是一个怎么在查询性能和写入成本之间找平衡的问题。1.2 聚簇索引与非聚簇索引的存储差异MySQL默认的InnoDB存储引擎下索引的存储方式分为两大类聚簇索引clustered index和二级索引secondary index也叫辅助索引、非聚簇索引。聚簇索引在InnoDB里就是主键索引。它的特点是索引的叶子节点直接存储整行数据。InnoDB的表数据本身就是按照主键顺序组织的所以每张InnoDB表有且只能有一个聚簇索引。你建表时如果没有显式定义主键InnoDB会优先选一个非空的唯一列作为聚簇索引如果连唯一列都没有它会自动生成一个不可见的rowid来作为聚簇索引。这种隐藏主键表面上没影响但如果你后续经常用其他字段做查询就很容易出现回表次数过多的问题。二级索引的叶子节点存储的是索引列值加上主键值。查询时如果索引列已经覆盖了需要的字段就不用回表这叫做覆盖索引如果还需要其他字段就必须拿着主键回到聚簇索引里去取整行数据这个动作就是回表。回表的次数直接决定了查询速度所以优化索引时一个高频操作就是想办法把回表次数降下来甚至做到完全不回表。我自己在实际项目里见过一个典型的例子某系统加了索引之后查询反而更慢排查下来发现是走了二级索引后每条记录都要回表而普通列上建的索引选择性又不够高结果回表次数几乎等于全表行数。这种时候索引还不如不用优化器最终选择全表扫描反而是合理的。理解了存储差异你才能真正读懂后面要讲的联合索引设计、覆盖索引优化等等内容。索引不是简单的加个索引三个字而是要先想清楚底层数据结构是怎么工作、查询路径是怎么走的。2. 给一张具体的表设计索引时先回答三个问题2.1 你查得最多的到底是哪几条SQL这是我在做索引优化时问自己的第一句话。很多人的习惯是一上来就对着表结构想哪几个字段比较常用然后每个字段都建一个单列索引。这样做往往会让事情变得更糟索引数量膨胀写入变慢优化器在选择索引时也会纠结。正确顺序应该是先捞慢查询日志看看生产环境里真正拖后腿的SQL是哪些。每一类SQL都要拆开看它的WHERE条件、ORDER BY排序字段、GROUP BY分组字段、JOIN连接字段以及SELECT要返回哪些列。我举一个真实场景。一张订单表里有user_id、order_no、status、pay_time、amount这几个字段。业务上有两类高频查询一是查某个用户最近30天的订单列表二是根据订单号查订单详情。这两类查询需要的索引完全不同。第一类适合在user_id和pay_time上建联合索引因为查询条件是用户加时间范围第二类适合直接在order_no上建唯一索引因为订单号本身就足够区分每一条记录。这里面有一个反直觉的现象如果你给status这种字段单独建索引往往收益极低。因为status一般只有几个取值待支付、已支付、已取消等每个值对应的数据量占比都很高MySQL优化器一算就知道索引选择性太低走索引还不如直接全表扫描快。所以建索引之前先搞清楚业务查询模式比任何技巧都重要。2.2 区分度与选择性为什么性别列不适合建索引区分度cardinality这个指标值得认真理解。它表示索引列上不同值的个数。区分度越高索引的筛选能力越强。比如订单号每一条都是唯一的区分度很高性别只有两个值区分度极低。一个很直观的计算方式是列的选择性 列中不同值的数量 / 表的总行数选择性越接近1说明这个列越适合建索引选择性越接近0说明这个列区分度越低建索引的意义越小比如一张十万行的用户表城市列有300个不同值选择性就是300/1000000.003这个值只能算一般。但如果是身份证号列几乎每一行都不同选择性接近1就非常适合做唯一索引。不过区分度也不是唯一标准。如果某个查询条件里经常用到低区分度字段但你组织的联合索引把这个低区分度字段放在了最前面那索引的过滤效果就会被严重拉低。最典型的反面案例就是联合索引(sex, age, name)这种设计因为第一个字段就已经把可用的区分度浪费掉了。我之前接手过一个项目表里有个is_deleted字段只有0和1两个值结果开发同学在上面建了索引。每次查询都要带上is_deleted0这个条件MySQL也确实走了这个索引但扫描的行数依然接近全表。后来把is_deleted从索引里去掉让它作为普通过滤条件查询速度反而加快了。这就是索引不一定要包含所有查询字段的教训。2.3 字段长度与冗余前缀索引的取舍如果要在很长的字符串列上建索引比如一个很长的备注字段、URL字段直接对整个列建索引不仅浪费空间还会让索引的B树变得非常大。这时可以考虑前缀索引只取字符串的前N个字符作为索引值。前缀索引的核心思路是用更小的存储空间换取可接受的区分度。假设某字段有十万条数据你分别取前缀10、15、20个字符前缀区分度会逐渐上升。实际操作时可以用这样一句SQL来验证SELECT COUNT(DISTINCT LEFT(comment, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(comment, 15)) / COUNT(*) AS sel15, COUNT(DISTINCT LEFT(comment, 20)) / COUNT(*) AS sel20 FROM activity_log;当某个N对应的选择性和全列选择性的差距可以接受时就选那个N作为前缀长度。我一般会要求前缀索引的选择性尽量接近全列选择性的90%以上太低的话查询时会扫出太多脏数据。前缀索引也有代价它不能让这条查询走覆盖索引因为索引里存的是前缀而不是全值回表是免不了的。所以如果某条查询对性能要求极严且那个长字段又必须返回就需要权衡是建完整列索引消耗空间还是建前缀索引接受回表。没有绝对正确的答案只有适合当前业务场景的取舍。3. 联合索引的命中与失效边界3.1 最左前缀原则的完整演示联合索引是MySQL索引优化的核心武器。假设我们要对a、b、c三个字段建立联合索引(a, b, c这个索引实际上会按照先按a排序a相同再按b排序b相同再按c排序的方式组织。最左前缀原则意味着可以命中该索引的查询条件是WHERE a ?WHERE a ? AND b ?WHERE a ? AND b ? AND c ?而不太容易命中的情况是WHERE b ?跳过了aWHERE c ?跳过了a和bWHERE b ? AND c ?跳过了a我经常用字典的目录结构来解释这件事一本词典先按首字母排序再按第二个字母排序最后按第三个字母排序。你想查某个词必须从首字母开始翻直接找第二个字母是找不到页面的。联合索引也是同理优化器需要从联合索引的第一个字段开始匹配才能一步步利用索引的有序性。需要特别留意的范围查询。如果WHERE条件里出现了范围查询比如BETWEEN、、那么范围查询后面的字段就享受不到索引的排序优势了。如下面的SQLSELECT * FROM order_detail WHERE user_id 123 AND pay_time 2024-01-01 AND status 1;如果联合索引是(user_id, pay_time, status)那么status实际上用不到这个索引的排序过滤能力MySQL只能在pay_time的范围结果里再做一次status过滤。这是范围之后全失效原则。所以设计联合索引时要把等值查询的字段排在前面范围查询的字段排在后面。3.2 隐式类型转换、函数操作与字符集索引失效还有一个高频陷阱对索引列做了函数运算或隐式类型转换。最经典的案例就是字符串列和数字列比较。-- phone 字段是 varchar 类型但条件里直接传了数字 SELECT * FROM member WHERE phone 13800138000;这条SQL看起来很正常但MySQL会先把phone列转成数字再和13800138000比较结果就是索引列上发生了隐式转换索引失效变成全表扫描。这也是为什么我总是建议查询条件里的参数类型一定要和表结构里的字段类型保持一致。如果是接口传参要特别检查是不是有JS的数字类型把字符串自动转掉了、ORM框架有没有做类型映射这类问题。函数操作则是另一种常见失效场景SELECT * FROM member WHERE DATE(create_time) 2024-06-01;对create_time列用DATE()函数之后索引列已经被加工过B树里存储的原始值和查询条件无法直接比较所以索引废掉。解决办法有两个一是把条件改成create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00二是考虑建函数索引如果MySQL版本支持比如8.0里的功能索引。字符集不一致的问题也很隐蔽。两张表关联查询时一张表的字段是utf8mb4另一张表的字段是utf8MySQL在做比较时会对其中一个列做字符集隐式转换同样导致索引使用失败。早期的跨表JOIN性能问题很多就是出在字符集不统一上。3.3 排序与分组场景下的索引利用ORDER BY和GROUP BY操作是最容易被忽视的索引优化场景。很多人把索引只理解为WHERE过滤但其实索引本身是有序的如果ORDER BY的字段顺序和索引顺序一致MySQL就可以直接利用索引顺序输出结果省去文件排序filesort的开销。举个例子SELECT user_id, pay_time FROM pay_record WHERE user_id 10086 ORDER BY pay_time DESC;联合索引(user_id, pay_time)能同时完成过滤和排序。因为索引里user_id相同的情况下pay_time已经天然有序优化器直接反向扫描就可以拿到按时间倒序的数据完全不需要filesort。但如果你写成ORDER BY pay_time ASC, user_id DESC这种混合排序方向和字段顺序都和索引不一致排序优势就没法利用了。还有一个常见误区联合索引是(a, b, c)你在WHERE里只用了aORDER BY却用了b和c这时候索引能搞定过滤和排序但如果你ORDER BY了c和b顺序颠倒索引又会失效。排序方向不一致、字段顺序不一致、中间有范围条件都会让排序优化打折扣。GROUP BY本质上也是一种排序操作MySQL会对分组字段做排序再分组。如果分组字段能走联合索引效率会明显提升。尤其是配合聚合函数COUNT、SUM使用时通过覆盖索引能够大幅减少回表次数。4. 实战案例从500ms到8ms的优化全过程4.1 表结构与慢查询现场下面分享一个我实际处理过的案例完整演示一次索引优化的排查链路。业务背景是一个电商后台的订单列表页运营人员要按各种条件筛选订单。表结构简化后如下CREATE TABLE trade_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, shop_id bigint NOT NULL, status tinyint NOT NULL, order_amount decimal(10,2) NOT NULL, pay_time datetime DEFAULT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;表里大概有六百万行数据。运营后台的筛选条件常见的有按用户ID查、按店铺ID查、按订单状态查、按支付时间范围查并且要根据支付时间倒序排列。最初的慢SQL长这样SELECT order_no, user_id, status, order_amount FROM trade_order WHERE shop_id 1001 AND status 2 AND pay_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 23:59:59 ORDER BY pay_time DESC LIMIT 20;这条SQL在测试环境跑还不觉得有什么问题一到生产就原形毕露单次查询要500ms左右。用户一多数据库连接池直接被慢查询占满整个后台都卡。我做优化第一步不是直接建索引而是先看执行计划搞清楚MySQL到底是怎么执行这条SQL的。这里强调一下index优化一定要先explain再看执行计划再动索引。4.2 explain读片新手必须看懂的关键列MySQL在执行一条SQL之前优化器会生成一个执行计划explain命令可以把执行计划展示出来。很多人知道explain但不知道重点看哪些列。我自己最常看的五个关键列type访问类型从好到坏大致是system const eq_ref ref range index ALL。能看到ref或者range就已经不错了如果出现ALL就是全表扫描的警报。key实际选中的索引名称。rows预估扫描的行数越小越好。filtered表示返回的行数占扫描行数的百分比100%最好。Extra里面出现的Using filesortUsing temporary都是性能警示信号出现Using index condition说明走了索引下推出现Using index说明覆盖索引生效。我执行explain看到的结果大概是type: ALLkey: NULLrows: 6000000Extra: Using where; Using filesorttype是ALL等于全表扫描六百万行还要做文件排序不慢才有鬼。虽然status、shop_id、pay_time这些字段都各自有单列索引但面对三条等值/范围条件组合优化器判断走任何一个单列索引都扫不出足够精确的结果还要大量回表倒不如直接全表扫还省事。这里就能看出每个字段各建一个索引的坏处了单列索引之间是独立的没法相互配合做交集过滤。MySQL虽然用索引合并index merge这个机制但它的出场条件很苛刻效果也不稳定不能依赖它。4.3 对症下药覆盖索引与索引下推分析清楚之后我给这张表设计了一个联合索引ALTER TABLE trade_order ADD INDEX idx_shop_status_paytime (shop_id, status, pay_time);设计理由很简单shop_id等值、status等值、pay_time是范围查询。按照之前说的等值字段放前面范围字段放后面的原则顺序就是shop_id、status、pay_time。加上这个索引之后我再跑explaintype: rangekey: idx_shop_status_paytimerows: 3050Extra: Using index condition扫描行数从六百万降到了三千行左右查询耗时降到了20ms以下。但我还不满意因为还有个问题SELECT返回的列里有order_no、order_amount这两个字段不在索引里所以每次查到符合条件的记录后还需要拿着主键id回到主索引取完整行也就是回表。当结果集很大的时候回表次数依然不少。于是我又调整了索引把查询要返回的字段也包含进来ALTER TABLE trade_order ADD INDEX idx_shop_status_paytime_cover (shop_id, status, pay_time, order_no, order_amount);这一步的目的就是尽量做到覆盖索引。但这条SQL里还有ORDER BY pay_time DESC而索引里pay_time在中间位置所以排序时需要费点劲。我分析了一下其实因为筛选后的行数已经很少三千行filesort的代价已经可以接受了。最终这条慢查询的耗时稳定在8ms左右运营后台的页面从转圈圈变成了秒开。这个案例说明了一个道理索引设计不是一步到位的。有时候你的第一版索引已经把查询从全表扫描救到了范围扫描但距离最优还差一步。如果你连返回字段都装进索引里就能把回表也省掉。关于MySQL 5.6以后引入的索引下推Index Condition PushdownICP值得单独提一下。ICP允许MySQL在存储引擎层直接对索引中包含的字段做过滤减少回表次数。它的关键标志就是Extra列里的Using index condition。比如上面的联合索引里pay_time是范围条件status是等值条件在没有ICP的时候MySQL只把shop_id和status作为索引条件其他字段过滤要回表后才能做。有了ICPpay_time在存储引擎层就被过滤掉了回表次数进一步减少这也是为什么现在新版本MySQL里联合索引范围查询后的字段也不是完全没用。5. 索引建完不是结束还要体检与复盘5.1 慢日志与performance_schema的组合用法索引设计完并上线之后接下来要做的是验证效果和持续监控。我见过太多人建完索引就撒手不管结果过了几周索引基数漂移了、查询也变慢了完全不知道。第一步是开启慢查询日志。参数有两组long_query_time用来定义多慢算慢一般业务我建议先设成1秒slow_query_log用来开启日志记录。这种设置有两种做法一是改配置文件my.cnf永久生效二是用SET GLOBAL在线调整适合临时排查。开启了慢查询日志之后还要配合performance_schema来定位高频慢SQL。有一个思路如果慢日志里同一类SQL反复出现ORMs的模板SQL大概率是固定的只是参数值不同而已。你只需要把这些带参数的SQL统一归类统计各模板出现的次数、平均耗时、总耗时就能快速排查出最值得优化的SQL模板。我在项目里常用的一种做法是SELECT digest_text, COUNT(*) AS exec_count, ROUND(SUM(timer_wait)/1000000000, 2) AS total_ms, ROUND(AVG(timer_wait)/1000000000, 2) AS avg_ms FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME your_db GROUP BY digest_text ORDER BY total_ms DESC LIMIT 20;这条SQL会把数据库里所有聚合后的SQL按总耗时排序。你会发现有趣的现象有些SQL执行次数不高但单次特别慢有些SQL执行次数极高平均耗时几毫秒累加起来的总耗时却非常惊人。优化时不能只看单次速度而是要看总消耗。体检完要牢记一件事慢查询日志只是一个入口真正要判断这条SQL有没有利用好索引还是回到explain去验证。如果某个查询走了索引但rows还是太大可能需要重新审视索引的区分度和过滤条件。5.2 冗余索引清理与索引统计信息更新索引建多了之后最直接的问题是写入变慢和磁盘占用变大。更隐蔽的问题是冗余索引——看似多个索引实际职责重叠。比如已经有了联合索引(shop_id, status)再单独建一个shop_id的单列索引就是完全冗余的因为联合索引的最左前缀已经能覆盖shop_id条件。冗余索引的存在还会干扰优化器的选择让它计算代价时做出错误判断。清理冗余索引时先查一下当前有哪些索引然后逐条分析它们的覆盖关系。我一般用这条SQL看表上的所有索引SHOW INDEX FROM trade_order;把结果列出来之后按左前缀原则找重复凡是某个索引的前缀字段集合被另一个联合索引的前缀包含这个索引就属于冗余索引。当然也要看具体value的区分度如果某个单列索引有独特用途比如唯一约束那它有额外存在价值不能一刀切。另外一个容易被忽视的问题是索引统计信息过期。MySQL优化器判断走哪个索引靠的是表的统计信息cardinality。如果统计信息不准优化器可能做出错误的索引选择。常见做法是在数据量发生大幅变化后比如大批量导入数据执行ANALYZE TABLEANALYZE TABLE trade_order;优化器拿到最新的统计信息后索引选择的准确性会高很多。这也是数据导入后查询反而变慢的常见原因之一。这里还要补充一个我不太推荐但偶尔有用的手段force index。大多数时候我不会用它作为生产环境的长期方案因为它是硬编码级别的干预一旦数据分布变化强制指定的索引可能反而不是最优。我更愿意把它当排查工具用force index强制走某个索引和正常执行做对比判断优化器选错索引的原因到底出在统计信息还是索引结构上。6. 索引设计经验清单最后整理一份我在多个项目里沉淀下来的索引设计清单每条都来自真实踩坑。供你对照自查。先看业务SQL再设计索引。没有慢查询日志就先开慢查询日志拿数据说话不要靠感觉猜。联合索引的字段顺序等值条件在前范围条件在后排序字段根据实际需要放在合适位置。优先使用覆盖索引减少回表但不要为了覆盖而盲目把很多字段塞进索引索引宽度过大会导致B树层级变深IO次数反而增多。低区分度字段性别、状态码、is_deleted这类尽量避免作为索引的第一列如果业务必须带这种字段要考虑它后面是否跟着高区分度字段。字符串列太长时使用前缀索引但一定要统计SELECT DISTINCT LEFT的效果不能拍脑袋定前缀长度。避免在索引列上做函数运算和隐式类型转换这会让索引直接失效。参数类型和列类型保持一致是最基本的操作规范。联合索引范围查询后方的字段并不会完全失效在MySQL 5.6的索引下推机制下仍能部分过滤但排序优势会消失设计时仍要遵循等值在前、范围在后的大原则。频繁更新、删除的表上索引不宜过多索引维护的开销可能比查询节省的成本更大。如果某个索引只为了偶尔一次管理后台查询而建建议评估一下是否值得。定期用performance_schema聚合慢SQL定期更新统计信息定期清理冗余索引。索引优化不是一次性工作而是一个持续迭代的过程。建索引时优先考虑已有联合索引是否能覆盖你的新查询场景复用永远比新增更划算。根据我个人经验索引优化做到能解释清楚每一步为什么比直接给出三十条优化建议值钱得多。你面对一张新表时不要急着加索引先弄明白数据分布、业务查询模式、MySQL优化器的选择逻辑然后有针对性地建一两个高质量联合索引效果往往比一股脑加十个单列索引好得多。这个思路在你遇到下一个性能问题时会比任何现成工具都管用。 
长河君 · 2026/9/29 2:52:45 · 阅读 1.2k MySQL索引优化实战：从慢查询到覆盖索引的完整指南
1. 先搞清楚索引是把钥匙还是导航1.1 索引的本质从翻书找内容说起很多刚接触MySQL的朋友会把索引理解成一把万能钥匙认为只要有索引查询就一定会快。但实际踩过几次坑之后你会发现索引更像一本图书的目录导航它帮你快速定位到数据所在的物理位置而不是让你直接拿到数据本身。这个理解上的偏差直接决定了你后续优化思路是否正确。我们拿一张真实的业务表来举例。假设有一张订单表里面有两百万行数据你要查某个用户在最近一个月内的所有订单。没有索引的时候MySQL只能从头到尾把两百万行数据全部读一遍逐个判断用户ID和下单时间是否满足条件这就是全表扫描full table scan。全表扫描意味着两百万次判断磁盘IO的消耗是实打实的慢才是正常现象。有索引之后MySQL可以先通过索引这个目录找到满足条件的记录的物理地址然后再回表把主键拿回去查完整行数据取出完整记录。这个过程就像你在一本几百页的书里查找某个关键词有目录和没目录的体验完全是天壤之别。不过这里有一个很关键的点索引不是免费的午餐。每建立一个索引写入数据的时候就要额外维护一份索引结构插入、更新、删除操作的性能都会受影响。所以索引优化不是一个越多越好的问题而是一个怎么在查询性能和写入成本之间找平衡的问题。1.2 聚簇索引与非聚簇索引的存储差异MySQL默认的InnoDB存储引擎下索引的存储方式分为两大类聚簇索引clustered index和二级索引secondary index也叫辅助索引、非聚簇索引。聚簇索引在InnoDB里就是主键索引。它的特点是索引的叶子节点直接存储整行数据。InnoDB的表数据本身就是按照主键顺序组织的所以每张InnoDB表有且只能有一个聚簇索引。你建表时如果没有显式定义主键InnoDB会优先选一个非空的唯一列作为聚簇索引如果连唯一列都没有它会自动生成一个不可见的rowid来作为聚簇索引。这种隐藏主键表面上没影响但如果你后续经常用其他字段做查询就很容易出现回表次数过多的问题。二级索引的叶子节点存储的是索引列值加上主键值。查询时如果索引列已经覆盖了需要的字段就不用回表这叫做覆盖索引如果还需要其他字段就必须拿着主键回到聚簇索引里去取整行数据这个动作就是回表。回表的次数直接决定了查询速度所以优化索引时一个高频操作就是想办法把回表次数降下来甚至做到完全不回表。我自己在实际项目里见过一个典型的例子某系统加了索引之后查询反而更慢排查下来发现是走了二级索引后每条记录都要回表而普通列上建的索引选择性又不够高结果回表次数几乎等于全表行数。这种时候索引还不如不用优化器最终选择全表扫描反而是合理的。理解了存储差异你才能真正读懂后面要讲的联合索引设计、覆盖索引优化等等内容。索引不是简单的加个索引三个字而是要先想清楚底层数据结构是怎么工作、查询路径是怎么走的。2. 给一张具体的表设计索引时先回答三个问题2.1 你查得最多的到底是哪几条SQL这是我在做索引优化时问自己的第一句话。很多人的习惯是一上来就对着表结构想哪几个字段比较常用然后每个字段都建一个单列索引。这样做往往会让事情变得更糟索引数量膨胀写入变慢优化器在选择索引时也会纠结。正确顺序应该是先捞慢查询日志看看生产环境里真正拖后腿的SQL是哪些。每一类SQL都要拆开看它的WHERE条件、ORDER BY排序字段、GROUP BY分组字段、JOIN连接字段以及SELECT要返回哪些列。我举一个真实场景。一张订单表里有user_id、order_no、status、pay_time、amount这几个字段。业务上有两类高频查询一是查某个用户最近30天的订单列表二是根据订单号查订单详情。这两类查询需要的索引完全不同。第一类适合在user_id和pay_time上建联合索引因为查询条件是用户加时间范围第二类适合直接在order_no上建唯一索引因为订单号本身就足够区分每一条记录。这里面有一个反直觉的现象如果你给status这种字段单独建索引往往收益极低。因为status一般只有几个取值待支付、已支付、已取消等每个值对应的数据量占比都很高MySQL优化器一算就知道索引选择性太低走索引还不如直接全表扫描快。所以建索引之前先搞清楚业务查询模式比任何技巧都重要。2.2 区分度与选择性为什么性别列不适合建索引区分度cardinality这个指标值得认真理解。它表示索引列上不同值的个数。区分度越高索引的筛选能力越强。比如订单号每一条都是唯一的区分度很高性别只有两个值区分度极低。一个很直观的计算方式是列的选择性 列中不同值的数量 / 表的总行数选择性越接近1说明这个列越适合建索引选择性越接近0说明这个列区分度越低建索引的意义越小比如一张十万行的用户表城市列有300个不同值选择性就是300/1000000.003这个值只能算一般。但如果是身份证号列几乎每一行都不同选择性接近1就非常适合做唯一索引。不过区分度也不是唯一标准。如果某个查询条件里经常用到低区分度字段但你组织的联合索引把这个低区分度字段放在了最前面那索引的过滤效果就会被严重拉低。最典型的反面案例就是联合索引(sex, age, name)这种设计因为第一个字段就已经把可用的区分度浪费掉了。我之前接手过一个项目表里有个is_deleted字段只有0和1两个值结果开发同学在上面建了索引。每次查询都要带上is_deleted0这个条件MySQL也确实走了这个索引但扫描的行数依然接近全表。后来把is_deleted从索引里去掉让它作为普通过滤条件查询速度反而加快了。这就是索引不一定要包含所有查询字段的教训。2.3 字段长度与冗余前缀索引的取舍如果要在很长的字符串列上建索引比如一个很长的备注字段、URL字段直接对整个列建索引不仅浪费空间还会让索引的B树变得非常大。这时可以考虑前缀索引只取字符串的前N个字符作为索引值。前缀索引的核心思路是用更小的存储空间换取可接受的区分度。假设某字段有十万条数据你分别取前缀10、15、20个字符前缀区分度会逐渐上升。实际操作时可以用这样一句SQL来验证SELECT COUNT(DISTINCT LEFT(comment, 10)) / COUNT(*) AS sel10, COUNT(DISTINCT LEFT(comment, 15)) / COUNT(*) AS sel15, COUNT(DISTINCT LEFT(comment, 20)) / COUNT(*) AS sel20 FROM activity_log;当某个N对应的选择性和全列选择性的差距可以接受时就选那个N作为前缀长度。我一般会要求前缀索引的选择性尽量接近全列选择性的90%以上太低的话查询时会扫出太多脏数据。前缀索引也有代价它不能让这条查询走覆盖索引因为索引里存的是前缀而不是全值回表是免不了的。所以如果某条查询对性能要求极严且那个长字段又必须返回就需要权衡是建完整列索引消耗空间还是建前缀索引接受回表。没有绝对正确的答案只有适合当前业务场景的取舍。3. 联合索引的命中与失效边界3.1 最左前缀原则的完整演示联合索引是MySQL索引优化的核心武器。假设我们要对a、b、c三个字段建立联合索引(a, b, c这个索引实际上会按照先按a排序a相同再按b排序b相同再按c排序的方式组织。最左前缀原则意味着可以命中该索引的查询条件是WHERE a ?WHERE a ? AND b ?WHERE a ? AND b ? AND c ?而不太容易命中的情况是WHERE b ?跳过了aWHERE c ?跳过了a和bWHERE b ? AND c ?跳过了a我经常用字典的目录结构来解释这件事一本词典先按首字母排序再按第二个字母排序最后按第三个字母排序。你想查某个词必须从首字母开始翻直接找第二个字母是找不到页面的。联合索引也是同理优化器需要从联合索引的第一个字段开始匹配才能一步步利用索引的有序性。需要特别留意的范围查询。如果WHERE条件里出现了范围查询比如BETWEEN、、那么范围查询后面的字段就享受不到索引的排序优势了。如下面的SQLSELECT * FROM order_detail WHERE user_id 123 AND pay_time 2024-01-01 AND status 1;如果联合索引是(user_id, pay_time, status)那么status实际上用不到这个索引的排序过滤能力MySQL只能在pay_time的范围结果里再做一次status过滤。这是范围之后全失效原则。所以设计联合索引时要把等值查询的字段排在前面范围查询的字段排在后面。3.2 隐式类型转换、函数操作与字符集索引失效还有一个高频陷阱对索引列做了函数运算或隐式类型转换。最经典的案例就是字符串列和数字列比较。-- phone 字段是 varchar 类型但条件里直接传了数字 SELECT * FROM member WHERE phone 13800138000;这条SQL看起来很正常但MySQL会先把phone列转成数字再和13800138000比较结果就是索引列上发生了隐式转换索引失效变成全表扫描。这也是为什么我总是建议查询条件里的参数类型一定要和表结构里的字段类型保持一致。如果是接口传参要特别检查是不是有JS的数字类型把字符串自动转掉了、ORM框架有没有做类型映射这类问题。函数操作则是另一种常见失效场景SELECT * FROM member WHERE DATE(create_time) 2024-06-01;对create_time列用DATE()函数之后索引列已经被加工过B树里存储的原始值和查询条件无法直接比较所以索引废掉。解决办法有两个一是把条件改成create_time 2024-06-01 00:00:00 AND create_time 2024-06-02 00:00:00二是考虑建函数索引如果MySQL版本支持比如8.0里的功能索引。字符集不一致的问题也很隐蔽。两张表关联查询时一张表的字段是utf8mb4另一张表的字段是utf8MySQL在做比较时会对其中一个列做字符集隐式转换同样导致索引使用失败。早期的跨表JOIN性能问题很多就是出在字符集不统一上。3.3 排序与分组场景下的索引利用ORDER BY和GROUP BY操作是最容易被忽视的索引优化场景。很多人把索引只理解为WHERE过滤但其实索引本身是有序的如果ORDER BY的字段顺序和索引顺序一致MySQL就可以直接利用索引顺序输出结果省去文件排序filesort的开销。举个例子SELECT user_id, pay_time FROM pay_record WHERE user_id 10086 ORDER BY pay_time DESC;联合索引(user_id, pay_time)能同时完成过滤和排序。因为索引里user_id相同的情况下pay_time已经天然有序优化器直接反向扫描就可以拿到按时间倒序的数据完全不需要filesort。但如果你写成ORDER BY pay_time ASC, user_id DESC这种混合排序方向和字段顺序都和索引不一致排序优势就没法利用了。还有一个常见误区联合索引是(a, b, c)你在WHERE里只用了aORDER BY却用了b和c这时候索引能搞定过滤和排序但如果你ORDER BY了c和b顺序颠倒索引又会失效。排序方向不一致、字段顺序不一致、中间有范围条件都会让排序优化打折扣。GROUP BY本质上也是一种排序操作MySQL会对分组字段做排序再分组。如果分组字段能走联合索引效率会明显提升。尤其是配合聚合函数COUNT、SUM使用时通过覆盖索引能够大幅减少回表次数。4. 实战案例从500ms到8ms的优化全过程4.1 表结构与慢查询现场下面分享一个我实际处理过的案例完整演示一次索引优化的排查链路。业务背景是一个电商后台的订单列表页运营人员要按各种条件筛选订单。表结构简化后如下CREATE TABLE trade_order ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL, user_id bigint NOT NULL, shop_id bigint NOT NULL, status tinyint NOT NULL, order_amount decimal(10,2) NOT NULL, pay_time datetime DEFAULT NULL, create_time datetime NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;表里大概有六百万行数据。运营后台的筛选条件常见的有按用户ID查、按店铺ID查、按订单状态查、按支付时间范围查并且要根据支付时间倒序排列。最初的慢SQL长这样SELECT order_no, user_id, status, order_amount FROM trade_order WHERE shop_id 1001 AND status 2 AND pay_time BETWEEN 2024-03-01 00:00:00 AND 2024-03-31 23:59:59 ORDER BY pay_time DESC LIMIT 20;这条SQL在测试环境跑还不觉得有什么问题一到生产就原形毕露单次查询要500ms左右。用户一多数据库连接池直接被慢查询占满整个后台都卡。我做优化第一步不是直接建索引而是先看执行计划搞清楚MySQL到底是怎么执行这条SQL的。这里强调一下index优化一定要先explain再看执行计划再动索引。4.2 explain读片新手必须看懂的关键列MySQL在执行一条SQL之前优化器会生成一个执行计划explain命令可以把执行计划展示出来。很多人知道explain但不知道重点看哪些列。我自己最常看的五个关键列type访问类型从好到坏大致是system const eq_ref ref range index ALL。能看到ref或者range就已经不错了如果出现ALL就是全表扫描的警报。key实际选中的索引名称。rows预估扫描的行数越小越好。filtered表示返回的行数占扫描行数的百分比100%最好。Extra里面出现的Using filesortUsing temporary都是性能警示信号出现Using index condition说明走了索引下推出现Using index说明覆盖索引生效。我执行explain看到的结果大概是type: ALLkey: NULLrows: 6000000Extra: Using where; Using filesorttype是ALL等于全表扫描六百万行还要做文件排序不慢才有鬼。虽然status、shop_id、pay_time这些字段都各自有单列索引但面对三条等值/范围条件组合优化器判断走任何一个单列索引都扫不出足够精确的结果还要大量回表倒不如直接全表扫还省事。这里就能看出每个字段各建一个索引的坏处了单列索引之间是独立的没法相互配合做交集过滤。MySQL虽然用索引合并index merge这个机制但它的出场条件很苛刻效果也不稳定不能依赖它。4.3 对症下药覆盖索引与索引下推分析清楚之后我给这张表设计了一个联合索引ALTER TABLE trade_order ADD INDEX idx_shop_status_paytime (shop_id, status, pay_time);设计理由很简单shop_id等值、status等值、pay_time是范围查询。按照之前说的等值字段放前面范围字段放后面的原则顺序就是shop_id、status、pay_time。加上这个索引之后我再跑explaintype: rangekey: idx_shop_status_paytimerows: 3050Extra: Using index condition扫描行数从六百万降到了三千行左右查询耗时降到了20ms以下。但我还不满意因为还有个问题SELECT返回的列里有order_no、order_amount这两个字段不在索引里所以每次查到符合条件的记录后还需要拿着主键id回到主索引取完整行也就是回表。当结果集很大的时候回表次数依然不少。于是我又调整了索引把查询要返回的字段也包含进来ALTER TABLE trade_order ADD INDEX idx_shop_status_paytime_cover (shop_id, status, pay_time, order_no, order_amount);这一步的目的就是尽量做到覆盖索引。但这条SQL里还有ORDER BY pay_time DESC而索引里pay_time在中间位置所以排序时需要费点劲。我分析了一下其实因为筛选后的行数已经很少三千行filesort的代价已经可以接受了。最终这条慢查询的耗时稳定在8ms左右运营后台的页面从转圈圈变成了秒开。这个案例说明了一个道理索引设计不是一步到位的。有时候你的第一版索引已经把查询从全表扫描救到了范围扫描但距离最优还差一步。如果你连返回字段都装进索引里就能把回表也省掉。关于MySQL 5.6以后引入的索引下推Index Condition PushdownICP值得单独提一下。ICP允许MySQL在存储引擎层直接对索引中包含的字段做过滤减少回表次数。它的关键标志就是Extra列里的Using index condition。比如上面的联合索引里pay_time是范围条件status是等值条件在没有ICP的时候MySQL只把shop_id和status作为索引条件其他字段过滤要回表后才能做。有了ICPpay_time在存储引擎层就被过滤掉了回表次数进一步减少这也是为什么现在新版本MySQL里联合索引范围查询后的字段也不是完全没用。5. 索引建完不是结束还要体检与复盘5.1 慢日志与performance_schema的组合用法索引设计完并上线之后接下来要做的是验证效果和持续监控。我见过太多人建完索引就撒手不管结果过了几周索引基数漂移了、查询也变慢了完全不知道。第一步是开启慢查询日志。参数有两组long_query_time用来定义多慢算慢一般业务我建议先设成1秒slow_query_log用来开启日志记录。这种设置有两种做法一是改配置文件my.cnf永久生效二是用SET GLOBAL在线调整适合临时排查。开启了慢查询日志之后还要配合performance_schema来定位高频慢SQL。有一个思路如果慢日志里同一类SQL反复出现ORMs的模板SQL大概率是固定的只是参数值不同而已。你只需要把这些带参数的SQL统一归类统计各模板出现的次数、平均耗时、总耗时就能快速排查出最值得优化的SQL模板。我在项目里常用的一种做法是SELECT digest_text, COUNT(*) AS exec_count, ROUND(SUM(timer_wait)/1000000000, 2) AS total_ms, ROUND(AVG(timer_wait)/1000000000, 2) AS avg_ms FROM performance_schema.events_statements_summary_by_digest WHERE SCHEMA_NAME your_db GROUP BY digest_text ORDER BY total_ms DESC LIMIT 20;这条SQL会把数据库里所有聚合后的SQL按总耗时排序。你会发现有趣的现象有些SQL执行次数不高但单次特别慢有些SQL执行次数极高平均耗时几毫秒累加起来的总耗时却非常惊人。优化时不能只看单次速度而是要看总消耗。体检完要牢记一件事慢查询日志只是一个入口真正要判断这条SQL有没有利用好索引还是回到explain去验证。如果某个查询走了索引但rows还是太大可能需要重新审视索引的区分度和过滤条件。5.2 冗余索引清理与索引统计信息更新索引建多了之后最直接的问题是写入变慢和磁盘占用变大。更隐蔽的问题是冗余索引——看似多个索引实际职责重叠。比如已经有了联合索引(shop_id, status)再单独建一个shop_id的单列索引就是完全冗余的因为联合索引的最左前缀已经能覆盖shop_id条件。冗余索引的存在还会干扰优化器的选择让它计算代价时做出错误判断。清理冗余索引时先查一下当前有哪些索引然后逐条分析它们的覆盖关系。我一般用这条SQL看表上的所有索引SHOW INDEX FROM trade_order;把结果列出来之后按左前缀原则找重复凡是某个索引的前缀字段集合被另一个联合索引的前缀包含这个索引就属于冗余索引。当然也要看具体value的区分度如果某个单列索引有独特用途比如唯一约束那它有额外存在价值不能一刀切。另外一个容易被忽视的问题是索引统计信息过期。MySQL优化器判断走哪个索引靠的是表的统计信息cardinality。如果统计信息不准优化器可能做出错误的索引选择。常见做法是在数据量发生大幅变化后比如大批量导入数据执行ANALYZE TABLEANALYZE TABLE trade_order;优化器拿到最新的统计信息后索引选择的准确性会高很多。这也是数据导入后查询反而变慢的常见原因之一。这里还要补充一个我不太推荐但偶尔有用的手段force index。大多数时候我不会用它作为生产环境的长期方案因为它是硬编码级别的干预一旦数据分布变化强制指定的索引可能反而不是最优。我更愿意把它当排查工具用force index强制走某个索引和正常执行做对比判断优化器选错索引的原因到底出在统计信息还是索引结构上。6. 索引设计经验清单最后整理一份我在多个项目里沉淀下来的索引设计清单每条都来自真实踩坑。供你对照自查。先看业务SQL再设计索引。没有慢查询日志就先开慢查询日志拿数据说话不要靠感觉猜。联合索引的字段顺序等值条件在前范围条件在后排序字段根据实际需要放在合适位置。优先使用覆盖索引减少回表但不要为了覆盖而盲目把很多字段塞进索引索引宽度过大会导致B树层级变深IO次数反而增多。低区分度字段性别、状态码、is_deleted这类尽量避免作为索引的第一列如果业务必须带这种字段要考虑它后面是否跟着高区分度字段。字符串列太长时使用前缀索引但一定要统计SELECT DISTINCT LEFT的效果不能拍脑袋定前缀长度。避免在索引列上做函数运算和隐式类型转换这会让索引直接失效。参数类型和列类型保持一致是最基本的操作规范。联合索引范围查询后方的字段并不会完全失效在MySQL 5.6的索引下推机制下仍能部分过滤但排序优势会消失设计时仍要遵循等值在前、范围在后的大原则。频繁更新、删除的表上索引不宜过多索引维护的开销可能比查询节省的成本更大。如果某个索引只为了偶尔一次管理后台查询而建建议评估一下是否值得。定期用performance_schema聚合慢SQL定期更新统计信息定期清理冗余索引。索引优化不是一次性工作而是一个持续迭代的过程。建索引时优先考虑已有联合索引是否能覆盖你的新查询场景复用永远比新增更划算。根据我个人经验索引优化做到能解释清楚每一步为什么比直接给出三十条优化建议值钱得多。你面对一张新表时不要急着加索引先弄明白数据分布、业务查询模式、MySQL优化器的选择逻辑然后有针对性地建一两个高质量联合索引效果往往比一股脑加十个单列索引好得多。这个思路在你遇到下一个性能问题时会比任何现成工具都管用。