先去对比一下实际执行计划再说话MySQL 索引优化这事网上教程一抓一大把但多数人看完还是只会背“最左前缀”“不要用函数”这种口诀。真正在线上业务里踩过坑的人都知道索引能不能生效、该不该建、建几列每一步都需要结合数据和执行计划来判断。今天我就围绕 MySQL 索引优化把从原理到实操、从设计到排查这一整套东西串起来讲一遍希望对正在折腾索引的朋友有点实际帮助。这篇文章适合三类人一类是刚接触 MySQL 索引、想系统搞懂原理的新手另一类是写过简单 SQL 但经常被慢查询折磨、想搞清楚索引为什么失效的开发还有一类是要接手线上数据库、需要做索引审查和维护的 DBA。内容不会太理论重点放在“怎么设计索引、怎么验证效果、怎么排查问题”上。1. 索引提速的核心原理从 B 树存储说起1.1 为什么 MySQL 偏偏选了 B 树索引的本质是一种数据结构目的就是让数据库不需要扫描全表就能快速定位数据。MySQL 默认存储引擎 InnoDB 使用的是 B 树这一点几乎所有教程都会提但很少有人讲清楚它相对于其他结构的优势。B 树有几个关键特征所有数据都存放在叶子节点非叶子节点只存储索引键值和指针因此单个节点能容纳更多键值树的高度被压得很低。InnoDB 的默认页大小是 16KB三层 B 树大概能存储千万级数据行这意味着一次普通查询走主键索引只需要经历三次磁盘 I/O。叶子节点之间通过双向指针串联形成了有序的双向链表对于范围查询、排序操作非常友好。比如你要查询某个时间段内的订单命中索引后直接顺着叶子节点链往下读就行不需要反复回溯树结构。数据是按索引列排序存储的所以走索引可以避免额外的排序操作这就是为什么有些查询建立索引后能从“Using filesort”变成“Using index”的原因。哈希索引虽然单点查询更快但它不支持范围查询也不支持排序所以 InnoDB 虽然有自适应哈希索引功能但默认的索引结构依然是 B 树。还有一种常见疑问是为什么不直接用红黑树或二叉查找树这两种树的查找时间复杂度是 O(logN)看起来也不差但问题是树的高度会随数据量线性增长。对于磁盘存储来说每次树层级的下降都可能触发一次磁盘 I/O而内存随机访问的速度远比磁盘快所以 B 树通过扁平化结构把树的层级压到极低本质上是在和磁盘 I/O 做斗争。理解了这个逻辑你就能明白为什么说“索引不是越多越好而是越有用越好”——每一条索引都是一棵独立的 B 树而树是有物理存储成本的。1.2 聚簇索引与二级索引数据到底存哪里InnoDB 里主键索引又叫聚簇索引它的叶子节点直接存放整行数据。也就是说你建表时指定的主键本身就会生成一棵 B 树而表里的数据就按这棵树的叶子节点顺序物理存放。这就是为什么 InnoDB 表必须要有主键的原因——如果没有显式主键InnoDB 会选择一个唯一的非空索引作为聚簇索引实在没有就隐式生成一个 rowid。二级索引也就是普通索引则完全不同它的叶子节点存放的是索引列的值加上主键值。注意到没有它不存整行数据。所以当你通过普通索引查询数据时要走两步从二级索引的 B 树中找到匹配的主键值。再通过主键值到聚簇索引的 B 树中回表查询完整行数据。这个“回表”是索引优化中一个非常重要但又容易忽略的细节。回表的次数越多性能损耗越大。如果一次查询命中了 1000 条记录每条都要回表那实际上就是 1000 次随机主键查询比全表扫描好不到哪去。这也是为什么“覆盖索引”这个概念这么重要它指的就是二级索引里已经包含了你需要的所有字段压根不需要回表。理解了聚簇索引和二级索引的差别很多索引设计问题就迎刃而解了。比如二级索引里放的是主键值所以主键字段长度越短二级索引的体积就越小占用的磁盘空间和内存也就越少。这就是为什么推荐用自增整数做主键而不是用 UUID 字符串——长字符串主键不仅让聚簇索引体积膨胀还会让所有二级索引跟着膨胀。从数据结构的角度看索引优化本质上就是在控制访问路径的长度和存储体积返回的每一行数据都对应一次磁盘访问而这些访问路径是由数据结构和索引结构共同决定的。2. 高效索引的设计思路先考虑业务查询模式很多人在建索引时习惯对着表结构按字段逐个添加以为这样就能覆盖所有查询。这种思路的问题在于索引不是字段的堆砌而是为查询设计的路径。真正高效的索引设计必须从让 SQL 语句的 WHERE 条件、排序、分组和 Join 字段驱动设计。2.1 查询需求反推索引字段先收集慢查询我在拿到一个新项目或者接手一张新表时第一件事不是急着写 CREATE INDEX而是先做三件事收集线上慢查询日志找出最频繁出现的几条慢 SQL。查看业务方最常用的列表查询和详情查询确认 WHERE 子句的过滤条件。分析表的数据分布看有哪些字段是高区分度、哪些是低区分度哪些字段会被用于 ORDER BY 和 GROUP BY。为什么这样做因为索引优化的目标是让高频查询的代价降到最低。如果业务上根本没人在意某条查询它再慢也没有太大风险但如果一张表的详情查询每次都要扫全表那这就是必须优化的重点对象。所以索引设计一定是围绕需求驱动的而不是单纯围绕字段类型。以电商订单表为例最频繁的查询往往是“查某个买家最近的订单列表”这个查询的过滤条件通常是user_id和create_time排序通常是create_time DESC。那么一条复合索引(user_id, create_time)就是值得优先考虑的方案。而如果你只对user_id建单列索引查询已经能定位到买家的所有订单但还要额外做一次排序效率和覆盖面都不如复合索引。2.2 组合索引的列顺序不是随便排列组合索引的列顺序是整个索引设计里最核心的决策之一也是最容易出错的地方。MySQL 的组合索引遵循最左前缀原则具体来说假设你创建了一个组合索引(a, b, c)那么实际生效的查询条件有aa, ba, b, c也就是说查询条件里没有包含最左边的列 a 时这个索引基本不会被使用除了 8.0 的索引跳跃扫描能做少量优化。真正难的是既然组合索引只能匹配最左前缀那当多个查询条件并存时到底哪个字段放最左边我自己的经验是按照这样几条原则来排区分度最高的列放在最前面因为索引的目的是最快地缩小范围。经常被用于范围查询的列放在最后面比如create_time ?。经常被用于排序的列尽量按排序方向排列进索引这能消除文件排序。拿实际例子说订单查询有user_id ?和create_time排序那么(user_id, create_time)是合理的但如果是查询“某个时间范围内所有订单”没有用户ID这个等值条件那同样这条索引就用不上反而需要单独建(create_time)索引。所以索引设计必须跟随实际的查询条件而不是看哪个字段“看起来重要”。这里我特别强调一个观点区分度高的列放前面的原则并不绝对适合所有场景。如果某个区分度高的列是范围查询比如status字段区分度很低但经常用于 IN 查询那把它放在最前面反而会让索引扫描成本变大。最怕的是设计索引时只看字段名不去看 WHERE 条件里到底是等值还是范围这个坑我踩过不止一次。2.3 前缀索引与函数索引解决字段长度和表达式问题假如表中有一个字段存的是长字符串比如用户邮箱email字段值基本唯一但整个字段长度可能接近100个字符。此时如果直接对整列建索引索引体积会很大产生额外磁盘占用和写入开销。更好的方案是只取字符串的前几个字符比如前8个字符来建前缀索引这样能大幅缩小索引体积同时保持足够的选择性。创建语法很简单ALTER TABLE user ADD INDEX idx_email_prefix (email(8));但这里有个前提你必须验证前缀长度是否足够区分。推荐这么测试SELECT COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12 FROM user;通常当选择性比例超过 0.9 时前缀长度就基本可用了。取太短会导致大量冲突取太长则失去前缀索引的压缩优势。前缀索引也有个麻烦它无法用于覆盖索引因为索引中只存了一部分字段值查询返回完整值时还是得回表。MySQL 8.0 还引入了函数索引的功能语法上是在索引定义里直接使用表达式ALTER TABLE user ADD INDEX idx_email_domain ( (SUBSTRING_INDEX(email, , -1)) );这解决了以前“WHERE SUBSTRING_INDEX(email, , -1) example.com”这类查询无法走索引的问题。但在老版本里没有这个功能只能通过生成列或者改写查询逻辑来绕过这也是我在历史项目中经常碰到的一个实用性痛点。2.4 覆盖索引带来的额外收益前面提过覆盖索引的二级索引里包含了查询需要的所有列因此不需要回表。不光如此由于二级索引通常比聚簇索引体积小覆盖索引扫描的叶子节点也更少整体 I/O 成本明显低。尤其在统计类查询中效果显著比如SELECT COUNT(*), status FROM orders GROUP BY status;如果能建立一个(status)索引这个查询可以只扫索引而不是全表几乎任何时候都比全表扫描快一个量级。对于高并发、查询频率极高的场景建立覆盖索引是比盲目加内存缓存更直接的优化手段。我之前在某个业务里做过一次优化原本一个统计接口经常触发慢查询最后仅仅加了一条覆盖索引耗时就从 800 毫秒降到 80 毫秒这就是回表被完全消除的威力。3. 实操一条索引从创建到验证的全流程3.1 建索引的常见语句及注意事项MySQL 里常见的索引类型有普通索引、唯一索引、全文索引、组合索引和前缀索引。日常优化用得最多的是普通索引和组合索引下面这些语句都是实际开发里高频出现的形式-- 创建普通索引 CREATE INDEX idx_user_id ON orders (user_id); -- 创建唯一索引常用于保证业务字段唯一性 CREATE UNIQUE INDEX idx_order_no ON orders (order_no); -- 创建组合索引 CREATE INDEX idx_user_time ON orders (user_id, create_time); -- ALTER TABLE 方式添加索引 ALTER TABLE orders ADD INDEX idx_status (status); -- 删除索引 DROP INDEX idx_status ON orders;这里有几个细节要特别提醒索引命名要统一规范比如idx_字段名、uniq_字段名方便后续排查和维护。生产环境索引非常多时命名混乱会浪费大量维护时间。不要在频繁更新的列上建过多索引每次 UPDATE / INSERT 都会同步维护索引索引数量越多写入链路就越重。这就像每加一个索引就是为这本书多追加一套目录写新内容时目录同步更新也得很及时。大表建索引要选业务低峰期操作因为 InnoDB 在建立索引时会对表加共享锁数据量一大容易阻塞业务。常规做法是使用在线 DDLOnline DDL特性MySQL 5.6 之后已经支持但依然不建议在高峰期操作。3.2 EXPLAIN 不只看 type还要看这几列建完索引一定要用EXPLAIN验证索引是否真的生效。不少初学者看到EXPLAIN结果里有index关键字就以为万事大吉这是最大的误区。一条 SQL 能不能高效执行重点要看下面这几列列名含义排查重点type访问类型如果出现 all说明全表扫描需要重点优化key实际使用的索引为 NULL 说明没用索引需要检查 WHERE 条件rows预估扫描行数这个值越大说明过滤性越差需要调整索引Extra附加信息出现 Using filesort / Using temporary 需要优化排序或分组我通常最关注type和Extra这两列。type 从好到坏大致是system表只有一行基本不出现。const主键或唯一索引等值查询只需扫描一行。eq_ref唯一索引扫描多表 JOIN 时很常见的优秀级别。ref普通索引等值查询。range索引范围扫描比如BETWEEN、、等操作。index全索引扫描即遍历整棵索引树通常只比全表扫描好一点。ALL全表扫描最差情况。Extra 里如果出现Using filesort说明 MySQL 需要自己排序而不是顺着索引读出来的天然顺序这个代价往往很高。如果出现Using temporary说明查询使用了临时表常见于 GROUP BY、去重或某些子查询场景这两个词一出现基本就意味着这条 SQL 还有优化空间。实际执行 EXPLAIN 的操作如下EXPLAIN SELECT id, user_id, amount FROM orders WHERE user_id 1024 ORDER BY create_time DESC;假设建了(user_id, create_time)的组合索引type 应该是 refExtra 里不会出现Using filesort因为组合索引的第二列天生就是按 create_time 排好的。如果只建了(user_id)单列索引那么 type 虽然还是 ref但 Extra 里会出现Using filesort这说明排序没有走索引需要额外排序。3.3 覆盖索引和索引下推的实际效果对比MySQL 5.6 引入了一项优化叫“索引条件下推ICP”它允许 MySQL 在存储引擎层直接过滤掉不符合二级索引条件的更多记录减少回表次数。举个例子假设表 people 上有索引(zipcode, lastname)执行这样一条查询SELECT * FROM people WHERE zipcode 10001 AND lastname LIKE %Zhang%;在 ICP 出现之前存储引擎只能根据 zipcode 找到所有匹配记录然后在服务器层对 lastname 做 LIKE 过滤回表次数很多。启用 ICP 之后存储引擎直接利用 lastname 的条件在索引内部过滤只有完全符合的记录才回表显著降低 I/O 量和回表次数。像这样的细节如果只看 type 列根本意识不到需要结合Extra里的Using index condition来确认。这也是我建议所有做索引优化的人一定要养成看完整 EXPLAIN 结果习惯的原因。本来一个 SQL 就不该只看执行结果正不正确还要看得快不快看它走的路径是否是最短的一条。另外关于覆盖索引实际做验证的时候可以用这样的小技巧把查询字段改成索引字段看 EXPLAIN 里是否出现Using index。如果出现说明索引已经覆盖了查询需要的所有列EXPLAIN SELECT user_id, create_time FROM orders WHERE user_id 1024;这种情况下无需回表性能自然最好。4. 索引优化中常见的坑与排查心得4.1 隐式类型转换让索引失效这是线上最典型、出现频率最高的问题之一。比如 phone 字段是VARCHAR类型查询时写了WHERE phone 13800000000数字是 INT 类型MySQL 在比较时会尝试把字符串字段转换为数字一旦对字段本身做了函数式的隐式转换索引就无法使用。实际排查时用EXPLAIN一看type 从 ref 变成了 ALL小表还好大表直接刷慢查询。解决办法有两个层面第一是 SQL 层面把参数值写成字符串形式如WHERE phone 13800000000第二是表结构层面如果这个字段实质就是数字干脆从一开始就建成BIGINT类型这也是字段设计时该考虑清楚的一个点。4.2 函数表达式包裹字段导致索引失效同样常见的还有在 WHERE 子句里对索引字段做函数计算SELECT * FROM orders WHERE DATE(create_time) 2024-01-15;这里对create_time做DATE()函数处理MySQL 无法直接利用索引。正确做法是把查询条件改写为时间范围SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;这种改写不仅能用上索引语义上也更精准。还有一个常见场景是LEFT(name, 3) 张这种函数写法本质上也是牺牲了索引的使用。如果业务上频繁需要这种查询在 8.0 里可以考虑函数索引在低版本里就得考虑生成列或者干脆接受全表扫。4.3 LIKE 查询如何使用索引LIKE 查询的索引使用规则很多人记不清我直接说结论LIKE abc%可以用索引因为前缀是确定的。LIKE %abc%基本不能用索引除非有全文索引或倒排索引来解决。LIKE %abc也不能正常走索引。实际上在 MySQL 8.0 之前的版本里%abc%这种模糊搜索想走普通 B 树索引几乎不可能只能用全文索引或外部搜索引擎。MySQL 8.0 之后引入了全文索引的 ngram 解析器对中文模糊查询有一定改善但也不是万能药。如果你真的频繁要做包含式模糊匹配我更建议考虑引入搜索组件或独立搜索引擎而不是指望普通索引优化出奇迹。4.4 索引冗余与维护成本线上库的索引数量往往比想象中多而冗余索引是慢查询以外又一个容易忽视的问题。比如你已经有一个组合索引(a, b)再单独建一个(a)单列索引两者就存在明显冗余。原因是(a, b)已经完全覆盖了以a为前缀的查询路径单列索引(a)几乎派不上用场。在 MySQL 5.7 及更早版本里没有直接识别冗余索引的视图我一般用系统库的统计信息来辅助判断SELECT table_name, index_name, column_name, seq_in_index FROM information_schema.statistics WHERE table_schema your_db ORDER BY table_name, index_name, seq_in_index;8.0 之后的版本还提供视图sys.schema_redundant_indexes可以直接查看冗余索引。维护索引数量和业务查询频率之间需要平衡我个人的建议是黄金法则是每张表索引数量控制在 5 个以内如果超过这个数一定要逐一审视是否真的被高频查询使用。索引归一化和清理是一个长期持续的工作。4.5 排序带来的隐藏性能风险有时候明明查询过滤行的条件很简单但执行计划里偏偏出现 Using filesort导致查询变得很慢。原因是 ORDER BY 字段没有和 WHERE 条件共同组成合适的复合索引。比如这个查询SELECT user_id, amount FROM orders WHERE status PAID ORDER BY create_time DESC;如果只有单列索引(status)那么 MySQL 得先查出来所有 statusPAID 的记录再对这些记录按 create_time 做外部排序。一张千万级的大表即使过滤后剩 10 万行排序也会非常吃力。最直接的优化方式就是建组合索引(status, create_time)这样索引本身就是按 status 过滤后里面的叶子节点已经按照 create_time 排好序完全不需要额外排序。相同的逻辑放到 GROUP BY 也一样(status, create_time)可以让分组和排序一起受益。很多时候一条复合索引能同时解决过滤、排序、分组三件事远比你单独为每一个字段各建一个索引高效得多。这一点我强烈建议在索引设计阶段就列出一个业务查询清单把高频 SQL 的 WHERE 条件和 ORDER BY 字段全部统计出来然后统一计算覆盖度。5. 线上索引优化的完整流程与自我复盘从发现慢查询到完成索引优化我自己有一套固定的流程走了一遍又一遍踩过的坑也积累了不少现在分享出来大家可以参考收集慢查询开启慢查询日志设置long_query_time 1定期分析慢查询记录。定位高频慢 SQL把慢日志按查询次数排序优先处理频率最高、单次耗时最长的 SQL。查看表结构SHOW CREATE TABLE table_name把现有索引摸清楚避免建立重复索引。核对索引使用情况基于performance_schema或sys.schema_unused_indexes来查看哪些索引从未被使用。设计索引方案根据 SQL 的 WHERE、ORDER BY、GROUP BY 字段设计组合索引并考虑列顺序。EXPLAIN 验证执行 EXPLAIN 对比优化前后的 type、rows、Extra。观察线上效果上线后监控慢查询数量是否下降锁等待和 I/O 压力是否改善。整个过程最重要的是不要凭感觉给业务加索引一定要通过真实数据和执行计划来验证。我见过太多人上来就抄网上现成的索引优化矩阵结果某个字段在业务表里区分度很低建了索引反而让写入变慢。索引优化永远是个“具体问题具体分析”的活。最后再分享一个我自己经常用的小经验优化一个慢查询不要只看单条 SQL 的 EXPLAIN还要看它的实际执行频率。一个每天执行几百万次的百万行表查询即使每次省下 10 毫秒整体收益也远大于一个每天只执行几次、但单次优化掉 1 秒的查询。索引设计最终服务的是整体业务链路而高效索引的本质是用合适的数据结构去匹配真实的数据访问模式。设计索引时多想一步未来整个业务的数据访问就平稳一分。 MySQL索引优化实战：从B+树原理到EXPLAIN排查指南
先去对比一下实际执行计划再说话MySQL 索引优化这事网上教程一抓一大把但多数人看完还是只会背“最左前缀”“不要用函数”这种口诀。真正在线上业务里踩过坑的人都知道索引能不能生效、该不该建、建几列每一步都需要结合数据和执行计划来判断。今天我就围绕 MySQL 索引优化把从原理到实操、从设计到排查这一整套东西串起来讲一遍希望对正在折腾索引的朋友有点实际帮助。这篇文章适合三类人一类是刚接触 MySQL 索引、想系统搞懂原理的新手另一类是写过简单 SQL 但经常被慢查询折磨、想搞清楚索引为什么失效的开发还有一类是要接手线上数据库、需要做索引审查和维护的 DBA。内容不会太理论重点放在“怎么设计索引、怎么验证效果、怎么排查问题”上。1. 索引提速的核心原理从 B 树存储说起1.1 为什么 MySQL 偏偏选了 B 树索引的本质是一种数据结构目的就是让数据库不需要扫描全表就能快速定位数据。MySQL 默认存储引擎 InnoDB 使用的是 B 树这一点几乎所有教程都会提但很少有人讲清楚它相对于其他结构的优势。B 树有几个关键特征所有数据都存放在叶子节点非叶子节点只存储索引键值和指针因此单个节点能容纳更多键值树的高度被压得很低。InnoDB 的默认页大小是 16KB三层 B 树大概能存储千万级数据行这意味着一次普通查询走主键索引只需要经历三次磁盘 I/O。叶子节点之间通过双向指针串联形成了有序的双向链表对于范围查询、排序操作非常友好。比如你要查询某个时间段内的订单命中索引后直接顺着叶子节点链往下读就行不需要反复回溯树结构。数据是按索引列排序存储的所以走索引可以避免额外的排序操作这就是为什么有些查询建立索引后能从“Using filesort”变成“Using index”的原因。哈希索引虽然单点查询更快但它不支持范围查询也不支持排序所以 InnoDB 虽然有自适应哈希索引功能但默认的索引结构依然是 B 树。还有一种常见疑问是为什么不直接用红黑树或二叉查找树这两种树的查找时间复杂度是 O(logN)看起来也不差但问题是树的高度会随数据量线性增长。对于磁盘存储来说每次树层级的下降都可能触发一次磁盘 I/O而内存随机访问的速度远比磁盘快所以 B 树通过扁平化结构把树的层级压到极低本质上是在和磁盘 I/O 做斗争。理解了这个逻辑你就能明白为什么说“索引不是越多越好而是越有用越好”——每一条索引都是一棵独立的 B 树而树是有物理存储成本的。1.2 聚簇索引与二级索引数据到底存哪里InnoDB 里主键索引又叫聚簇索引它的叶子节点直接存放整行数据。也就是说你建表时指定的主键本身就会生成一棵 B 树而表里的数据就按这棵树的叶子节点顺序物理存放。这就是为什么 InnoDB 表必须要有主键的原因——如果没有显式主键InnoDB 会选择一个唯一的非空索引作为聚簇索引实在没有就隐式生成一个 rowid。二级索引也就是普通索引则完全不同它的叶子节点存放的是索引列的值加上主键值。注意到没有它不存整行数据。所以当你通过普通索引查询数据时要走两步从二级索引的 B 树中找到匹配的主键值。再通过主键值到聚簇索引的 B 树中回表查询完整行数据。这个“回表”是索引优化中一个非常重要但又容易忽略的细节。回表的次数越多性能损耗越大。如果一次查询命中了 1000 条记录每条都要回表那实际上就是 1000 次随机主键查询比全表扫描好不到哪去。这也是为什么“覆盖索引”这个概念这么重要它指的就是二级索引里已经包含了你需要的所有字段压根不需要回表。理解了聚簇索引和二级索引的差别很多索引设计问题就迎刃而解了。比如二级索引里放的是主键值所以主键字段长度越短二级索引的体积就越小占用的磁盘空间和内存也就越少。这就是为什么推荐用自增整数做主键而不是用 UUID 字符串——长字符串主键不仅让聚簇索引体积膨胀还会让所有二级索引跟着膨胀。从数据结构的角度看索引优化本质上就是在控制访问路径的长度和存储体积返回的每一行数据都对应一次磁盘访问而这些访问路径是由数据结构和索引结构共同决定的。2. 高效索引的设计思路先考虑业务查询模式很多人在建索引时习惯对着表结构按字段逐个添加以为这样就能覆盖所有查询。这种思路的问题在于索引不是字段的堆砌而是为查询设计的路径。真正高效的索引设计必须从让 SQL 语句的 WHERE 条件、排序、分组和 Join 字段驱动设计。2.1 查询需求反推索引字段先收集慢查询我在拿到一个新项目或者接手一张新表时第一件事不是急着写 CREATE INDEX而是先做三件事收集线上慢查询日志找出最频繁出现的几条慢 SQL。查看业务方最常用的列表查询和详情查询确认 WHERE 子句的过滤条件。分析表的数据分布看有哪些字段是高区分度、哪些是低区分度哪些字段会被用于 ORDER BY 和 GROUP BY。为什么这样做因为索引优化的目标是让高频查询的代价降到最低。如果业务上根本没人在意某条查询它再慢也没有太大风险但如果一张表的详情查询每次都要扫全表那这就是必须优化的重点对象。所以索引设计一定是围绕需求驱动的而不是单纯围绕字段类型。以电商订单表为例最频繁的查询往往是“查某个买家最近的订单列表”这个查询的过滤条件通常是user_id和create_time排序通常是create_time DESC。那么一条复合索引(user_id, create_time)就是值得优先考虑的方案。而如果你只对user_id建单列索引查询已经能定位到买家的所有订单但还要额外做一次排序效率和覆盖面都不如复合索引。2.2 组合索引的列顺序不是随便排列组合索引的列顺序是整个索引设计里最核心的决策之一也是最容易出错的地方。MySQL 的组合索引遵循最左前缀原则具体来说假设你创建了一个组合索引(a, b, c)那么实际生效的查询条件有aa, ba, b, c也就是说查询条件里没有包含最左边的列 a 时这个索引基本不会被使用除了 8.0 的索引跳跃扫描能做少量优化。真正难的是既然组合索引只能匹配最左前缀那当多个查询条件并存时到底哪个字段放最左边我自己的经验是按照这样几条原则来排区分度最高的列放在最前面因为索引的目的是最快地缩小范围。经常被用于范围查询的列放在最后面比如create_time ?。经常被用于排序的列尽量按排序方向排列进索引这能消除文件排序。拿实际例子说订单查询有user_id ?和create_time排序那么(user_id, create_time)是合理的但如果是查询“某个时间范围内所有订单”没有用户ID这个等值条件那同样这条索引就用不上反而需要单独建(create_time)索引。所以索引设计必须跟随实际的查询条件而不是看哪个字段“看起来重要”。这里我特别强调一个观点区分度高的列放前面的原则并不绝对适合所有场景。如果某个区分度高的列是范围查询比如status字段区分度很低但经常用于 IN 查询那把它放在最前面反而会让索引扫描成本变大。最怕的是设计索引时只看字段名不去看 WHERE 条件里到底是等值还是范围这个坑我踩过不止一次。2.3 前缀索引与函数索引解决字段长度和表达式问题假如表中有一个字段存的是长字符串比如用户邮箱email字段值基本唯一但整个字段长度可能接近100个字符。此时如果直接对整列建索引索引体积会很大产生额外磁盘占用和写入开销。更好的方案是只取字符串的前几个字符比如前8个字符来建前缀索引这样能大幅缩小索引体积同时保持足够的选择性。创建语法很简单ALTER TABLE user ADD INDEX idx_email_prefix (email(8));但这里有个前提你必须验证前缀长度是否足够区分。推荐这么测试SELECT COUNT(DISTINCT LEFT(email, 4)) / COUNT(*) AS sel4, COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS sel8, COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS sel12 FROM user;通常当选择性比例超过 0.9 时前缀长度就基本可用了。取太短会导致大量冲突取太长则失去前缀索引的压缩优势。前缀索引也有个麻烦它无法用于覆盖索引因为索引中只存了一部分字段值查询返回完整值时还是得回表。MySQL 8.0 还引入了函数索引的功能语法上是在索引定义里直接使用表达式ALTER TABLE user ADD INDEX idx_email_domain ( (SUBSTRING_INDEX(email, , -1)) );这解决了以前“WHERE SUBSTRING_INDEX(email, , -1) example.com”这类查询无法走索引的问题。但在老版本里没有这个功能只能通过生成列或者改写查询逻辑来绕过这也是我在历史项目中经常碰到的一个实用性痛点。2.4 覆盖索引带来的额外收益前面提过覆盖索引的二级索引里包含了查询需要的所有列因此不需要回表。不光如此由于二级索引通常比聚簇索引体积小覆盖索引扫描的叶子节点也更少整体 I/O 成本明显低。尤其在统计类查询中效果显著比如SELECT COUNT(*), status FROM orders GROUP BY status;如果能建立一个(status)索引这个查询可以只扫索引而不是全表几乎任何时候都比全表扫描快一个量级。对于高并发、查询频率极高的场景建立覆盖索引是比盲目加内存缓存更直接的优化手段。我之前在某个业务里做过一次优化原本一个统计接口经常触发慢查询最后仅仅加了一条覆盖索引耗时就从 800 毫秒降到 80 毫秒这就是回表被完全消除的威力。3. 实操一条索引从创建到验证的全流程3.1 建索引的常见语句及注意事项MySQL 里常见的索引类型有普通索引、唯一索引、全文索引、组合索引和前缀索引。日常优化用得最多的是普通索引和组合索引下面这些语句都是实际开发里高频出现的形式-- 创建普通索引 CREATE INDEX idx_user_id ON orders (user_id); -- 创建唯一索引常用于保证业务字段唯一性 CREATE UNIQUE INDEX idx_order_no ON orders (order_no); -- 创建组合索引 CREATE INDEX idx_user_time ON orders (user_id, create_time); -- ALTER TABLE 方式添加索引 ALTER TABLE orders ADD INDEX idx_status (status); -- 删除索引 DROP INDEX idx_status ON orders;这里有几个细节要特别提醒索引命名要统一规范比如idx_字段名、uniq_字段名方便后续排查和维护。生产环境索引非常多时命名混乱会浪费大量维护时间。不要在频繁更新的列上建过多索引每次 UPDATE / INSERT 都会同步维护索引索引数量越多写入链路就越重。这就像每加一个索引就是为这本书多追加一套目录写新内容时目录同步更新也得很及时。大表建索引要选业务低峰期操作因为 InnoDB 在建立索引时会对表加共享锁数据量一大容易阻塞业务。常规做法是使用在线 DDLOnline DDL特性MySQL 5.6 之后已经支持但依然不建议在高峰期操作。3.2 EXPLAIN 不只看 type还要看这几列建完索引一定要用EXPLAIN验证索引是否真的生效。不少初学者看到EXPLAIN结果里有index关键字就以为万事大吉这是最大的误区。一条 SQL 能不能高效执行重点要看下面这几列列名含义排查重点type访问类型如果出现 all说明全表扫描需要重点优化key实际使用的索引为 NULL 说明没用索引需要检查 WHERE 条件rows预估扫描行数这个值越大说明过滤性越差需要调整索引Extra附加信息出现 Using filesort / Using temporary 需要优化排序或分组我通常最关注type和Extra这两列。type 从好到坏大致是system表只有一行基本不出现。const主键或唯一索引等值查询只需扫描一行。eq_ref唯一索引扫描多表 JOIN 时很常见的优秀级别。ref普通索引等值查询。range索引范围扫描比如BETWEEN、、等操作。index全索引扫描即遍历整棵索引树通常只比全表扫描好一点。ALL全表扫描最差情况。Extra 里如果出现Using filesort说明 MySQL 需要自己排序而不是顺着索引读出来的天然顺序这个代价往往很高。如果出现Using temporary说明查询使用了临时表常见于 GROUP BY、去重或某些子查询场景这两个词一出现基本就意味着这条 SQL 还有优化空间。实际执行 EXPLAIN 的操作如下EXPLAIN SELECT id, user_id, amount FROM orders WHERE user_id 1024 ORDER BY create_time DESC;假设建了(user_id, create_time)的组合索引type 应该是 refExtra 里不会出现Using filesort因为组合索引的第二列天生就是按 create_time 排好的。如果只建了(user_id)单列索引那么 type 虽然还是 ref但 Extra 里会出现Using filesort这说明排序没有走索引需要额外排序。3.3 覆盖索引和索引下推的实际效果对比MySQL 5.6 引入了一项优化叫“索引条件下推ICP”它允许 MySQL 在存储引擎层直接过滤掉不符合二级索引条件的更多记录减少回表次数。举个例子假设表 people 上有索引(zipcode, lastname)执行这样一条查询SELECT * FROM people WHERE zipcode 10001 AND lastname LIKE %Zhang%;在 ICP 出现之前存储引擎只能根据 zipcode 找到所有匹配记录然后在服务器层对 lastname 做 LIKE 过滤回表次数很多。启用 ICP 之后存储引擎直接利用 lastname 的条件在索引内部过滤只有完全符合的记录才回表显著降低 I/O 量和回表次数。像这样的细节如果只看 type 列根本意识不到需要结合Extra里的Using index condition来确认。这也是我建议所有做索引优化的人一定要养成看完整 EXPLAIN 结果习惯的原因。本来一个 SQL 就不该只看执行结果正不正确还要看得快不快看它走的路径是否是最短的一条。另外关于覆盖索引实际做验证的时候可以用这样的小技巧把查询字段改成索引字段看 EXPLAIN 里是否出现Using index。如果出现说明索引已经覆盖了查询需要的所有列EXPLAIN SELECT user_id, create_time FROM orders WHERE user_id 1024;这种情况下无需回表性能自然最好。4. 索引优化中常见的坑与排查心得4.1 隐式类型转换让索引失效这是线上最典型、出现频率最高的问题之一。比如 phone 字段是VARCHAR类型查询时写了WHERE phone 13800000000数字是 INT 类型MySQL 在比较时会尝试把字符串字段转换为数字一旦对字段本身做了函数式的隐式转换索引就无法使用。实际排查时用EXPLAIN一看type 从 ref 变成了 ALL小表还好大表直接刷慢查询。解决办法有两个层面第一是 SQL 层面把参数值写成字符串形式如WHERE phone 13800000000第二是表结构层面如果这个字段实质就是数字干脆从一开始就建成BIGINT类型这也是字段设计时该考虑清楚的一个点。4.2 函数表达式包裹字段导致索引失效同样常见的还有在 WHERE 子句里对索引字段做函数计算SELECT * FROM orders WHERE DATE(create_time) 2024-01-15;这里对create_time做DATE()函数处理MySQL 无法直接利用索引。正确做法是把查询条件改写为时间范围SELECT * FROM orders WHERE create_time 2024-01-15 00:00:00 AND create_time 2024-01-16 00:00:00;这种改写不仅能用上索引语义上也更精准。还有一个常见场景是LEFT(name, 3) 张这种函数写法本质上也是牺牲了索引的使用。如果业务上频繁需要这种查询在 8.0 里可以考虑函数索引在低版本里就得考虑生成列或者干脆接受全表扫。4.3 LIKE 查询如何使用索引LIKE 查询的索引使用规则很多人记不清我直接说结论LIKE abc%可以用索引因为前缀是确定的。LIKE %abc%基本不能用索引除非有全文索引或倒排索引来解决。LIKE %abc也不能正常走索引。实际上在 MySQL 8.0 之前的版本里%abc%这种模糊搜索想走普通 B 树索引几乎不可能只能用全文索引或外部搜索引擎。MySQL 8.0 之后引入了全文索引的 ngram 解析器对中文模糊查询有一定改善但也不是万能药。如果你真的频繁要做包含式模糊匹配我更建议考虑引入搜索组件或独立搜索引擎而不是指望普通索引优化出奇迹。4.4 索引冗余与维护成本线上库的索引数量往往比想象中多而冗余索引是慢查询以外又一个容易忽视的问题。比如你已经有一个组合索引(a, b)再单独建一个(a)单列索引两者就存在明显冗余。原因是(a, b)已经完全覆盖了以a为前缀的查询路径单列索引(a)几乎派不上用场。在 MySQL 5.7 及更早版本里没有直接识别冗余索引的视图我一般用系统库的统计信息来辅助判断SELECT table_name, index_name, column_name, seq_in_index FROM information_schema.statistics WHERE table_schema your_db ORDER BY table_name, index_name, seq_in_index;8.0 之后的版本还提供视图sys.schema_redundant_indexes可以直接查看冗余索引。维护索引数量和业务查询频率之间需要平衡我个人的建议是黄金法则是每张表索引数量控制在 5 个以内如果超过这个数一定要逐一审视是否真的被高频查询使用。索引归一化和清理是一个长期持续的工作。4.5 排序带来的隐藏性能风险有时候明明查询过滤行的条件很简单但执行计划里偏偏出现 Using filesort导致查询变得很慢。原因是 ORDER BY 字段没有和 WHERE 条件共同组成合适的复合索引。比如这个查询SELECT user_id, amount FROM orders WHERE status PAID ORDER BY create_time DESC;如果只有单列索引(status)那么 MySQL 得先查出来所有 statusPAID 的记录再对这些记录按 create_time 做外部排序。一张千万级的大表即使过滤后剩 10 万行排序也会非常吃力。最直接的优化方式就是建组合索引(status, create_time)这样索引本身就是按 status 过滤后里面的叶子节点已经按照 create_time 排好序完全不需要额外排序。相同的逻辑放到 GROUP BY 也一样(status, create_time)可以让分组和排序一起受益。很多时候一条复合索引能同时解决过滤、排序、分组三件事远比你单独为每一个字段各建一个索引高效得多。这一点我强烈建议在索引设计阶段就列出一个业务查询清单把高频 SQL 的 WHERE 条件和 ORDER BY 字段全部统计出来然后统一计算覆盖度。5. 线上索引优化的完整流程与自我复盘从发现慢查询到完成索引优化我自己有一套固定的流程走了一遍又一遍踩过的坑也积累了不少现在分享出来大家可以参考收集慢查询开启慢查询日志设置long_query_time 1定期分析慢查询记录。定位高频慢 SQL把慢日志按查询次数排序优先处理频率最高、单次耗时最长的 SQL。查看表结构SHOW CREATE TABLE table_name把现有索引摸清楚避免建立重复索引。核对索引使用情况基于performance_schema或sys.schema_unused_indexes来查看哪些索引从未被使用。设计索引方案根据 SQL 的 WHERE、ORDER BY、GROUP BY 字段设计组合索引并考虑列顺序。EXPLAIN 验证执行 EXPLAIN 对比优化前后的 type、rows、Extra。观察线上效果上线后监控慢查询数量是否下降锁等待和 I/O 压力是否改善。整个过程最重要的是不要凭感觉给业务加索引一定要通过真实数据和执行计划来验证。我见过太多人上来就抄网上现成的索引优化矩阵结果某个字段在业务表里区分度很低建了索引反而让写入变慢。索引优化永远是个“具体问题具体分析”的活。最后再分享一个我自己经常用的小经验优化一个慢查询不要只看单条 SQL 的 EXPLAIN还要看它的实际执行频率。一个每天执行几百万次的百万行表查询即使每次省下 10 毫秒整体收益也远大于一个每天只执行几次、但单次优化掉 1 秒的查询。索引设计最终服务的是整体业务链路而高效索引的本质是用合适的数据结构去匹配真实的数据访问模式。设计索引时多想一步未来整个业务的数据访问就平稳一分。