我接手过不少线上数据库的烂摊子其中八成以上都和索引有关。要么是查询慢到被业务方追着问要么是索引建了一堆却根本不起作用还有的是因为加索引导致线上写入抖动。MySQL 索引这个东西光知道“创建索引能加速查询”远远不够真正要命的是一套完整的使用逻辑什么时候该建、按什么顺序建、哪些写法会让索引白建、以及索引背后到底消耗了什么。这篇文章我就围绕“MySQL 索引的使用”把这些问题一次讲透内容偏向实战落地适合刚接触索引的开发者也适合写过几年 SQL 但没系统梳理过索引逻辑的老手。先给你一个场景一张订单表跑到 500 万行一条 where 查询从接口进去要 3 秒才返回慢日志里全是它的影子。第一反应自然是加索引但索引加在哪个字段、组合索引怎么排、加了之后为什么 EXPLAIN 还是显示全表扫描这才是真正的分水岭。下面我从索引的底层逻辑讲起再拆解联合索引、失效场景、维护成本最后用一个完整的排查案例把这套思路串起来。1. 为什么一条简单的 where 能慢到怀疑人生先理解索引在解决什么问题1.1 没有索引时MySQL 是怎么找数据的你可以把 InnoDB 表想象成一本没有目录、但按章节顺序排好内容的书。查询的时候MySQL 只能从第一页翻到最后一页逐行比对字段值这个动作叫全表扫描typeALL。我见过很多新手以为“表不大扫就扫了”但实际上 InnoDB 的数据是按 16KB 的数据页Page组织的500 万行订单数据大概要占几千个数据页每个页里可能几十到几百行记录。全表扫描意味着要把这些页全部读进内存做匹配I/O 次数直接和表大小成正比。你可能会问不是有 Buffer Pool 吗读过的页会被缓存但慢查询的特征恰恰是数据量大、访问模式分散缓存命中率低最终还是落到磁盘读取。所以全表扫描的耗时不是线性上涨而是在某个数据量级上突然变得不可接受。这就是索引要解决的最根本的问题把“翻整本书”变成“查目录”把磁盘 I/O 从几千次降成几次。1.2 B 树索引用空间换时间的经典设计MySQL 最常用的索引结构是 B 树InnoDB 里实际上有两种聚簇索引clustered index表数据的物理存储顺序由主键决定叶子节点直接存整行数据。二级索引secondary index叶子节点存的是索引列的值 主键值查询时要根据主键再回聚簇索引拿整行数据这个过程叫回表。B 树的厉害之处在于它的层数很矮。一个三层 B 树就能管理几千万行数据也就是最多 3 次磁盘 I/O 就能定位到目标叶子节点。作为对比全表扫描可能把几千个页都读一遍。这中间的差距就是毫秒和秒级的差距。这里有个很容易被忽略的点二级索引本质上是个“索引列 - 主键”的映射表。如果你查询的字段本身不完整回表这一步是逃不掉的而回表又是一次随机 I/O。所以后面我们会聊覆盖索引就是为了把回表省掉。1.3 怎么用 EXPLAIN 验证索引是否真的生效纸上谈兵没用每一条 SQL 都应该用 EXPLAIN 验证执行计划。我平时最关注这几列type从好到坏大致是 system const eq_ref ref range index ALL。看到 ALL 基本就是全表扫描看到 index 也要警惕它可能是扫了整个索引树。key实际用到的索引名。如果你明明建了索引这里却是 NULL说明优化器没用。rows优化器估算的需要扫描的行数这个数字能直观反映索引的效果。Extra如果出现 Using filesort 或 Using temporary说明排序和分组没走索引通常意味着还有优化空间。提示EXPLAIN 是验证索引是否生效的最快手段后面所有案例我都会拿它说话。养成写完 SQL 就 EXPLAIN 的习惯会比你看十篇理论文章都管用。2. “where a and b”到底怎么建索引联合索引的设计思路2.1 最左前缀原则以及为什么顺序这么重要很多人在多个字段上各建一个单列索引比如给 a 建了 idx_a给 b 建了 idx_b然后以为 where a and b 能同时用上两个索引。实际 MySQL 大多数时候只会选其中一个另一个字段再回表过滤效率并不理想。正确做法是建联合索引 (a, b)。联合索引遵循最左前缀原则索引 (a, b, c) 能匹配 (a)、(a, b)、(a, b, c) 三种查询条件但查询条件是 b 单独或者 b and c 时这个索引就用不上。原因要从 B 树的排序规则理解联合索引的每个节点先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。既然最前面的列是排序的第一关键字跳过了它后面的顺序关系就失去了意义。这里的关键不是背原则而是把“最左前缀”四个字转化成设计直觉联合索引本质上定义了一个字段的优先级顺序你写查询条件和索引顺序一致时优化器才能一层层定位下去。2.2 从一条慢查询推导索引字段顺序我接手的绝大多数慢查询都是复合条件典型的像这样SELECT order_id, amount, created_at FROM orders WHERE user_id 10086 AND status PAID ORDER BY created_at DESC LIMIT 20;很多人第一反应是三个字段都建索引或者建 (user_id, status, created_at) 这种联合索引但根本说不清为什么。实际上判断顺序有两步第一步区分字段在查询里的角色。等值筛选条件user_id ?、status ?决定定位的精度排序字段ORDER BY created_at决定是否需要额外排序。通常把等值条件的字段放在联合索引前面把排序字段放在后面。所以这里最自然的选择是 (user_id, status, created_at) 或者 (status, user_id, created_at)第二步再决定这两个等值条件谁在前。第二步看字段选择性也就是这个字段能区分多少行。区分度可以这样估算SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity, COUNT(DISTINCT status) / COUNT(*) AS status_selectivity FROM orders;user_id 的选择性通常远高于 status因为 status 只有 PAID、UNPAID、REFUND 等少数几个值。选择性高的字段放前面可以更快地把数据量缩小到更小的范围。所以上面的查询我一般会建 (user_id, status, created_at)而不是 (status, user_id, created_at)。但这里有个微妙的权衡如果业务里 status 的单条件查询特别多且 status 本身过滤后的数据量足够小把 status 放前面也可以。没有银弹只有基于实际数据分布的顺序推导。2.3 覆盖索引带来的意外收益联合索引除了满足查询条件还有一个隐藏红利叫覆盖索引。如果查询要的字段都在索引树里InnoDB 就不用回表直接扫二级索引就拿到了全部数据。Extra 里会显示 Using index这是我很喜欢看到的字样。举个简单的例子SELECT user_id, created_at FROM orders WHERE status PAID AND created_at 2024-01-01;如果索引是 (status, created_at)那么这两个条件都能被利用而且查询所需的 user_id 和 created_at 都在索引里根本不需要回表查整行数据。在设计联合索引时把 SELECT 里高频出现的字段合理地加进索引尾部往往能让查询再快一个量级。注意覆盖索引不是把 SELECT 的字段一股脑全加进索引。索引列越多写入成本越高叶子节点越大一页能存的索引项越少。我个人的经验是只覆盖高频查询里不大的字段大字段比如长文本、BLOB千万别往索引里塞。3. 主键索引、唯一索引和排序几个容易搞混的边界3.1 主键索引为什么是聚簇索引主键怎么选InnoDB 表本身就是按主键索引组织的这就是聚簇索引。你没有显式定义主键InnoDB 也会找第一个非空的唯一索引或者偷偷生成一个 6 字节的隐藏主键。所以主键选择非常影响表结构的底层效率。日常开发里自增整数主键通常是最省心的选择插入的时候是追加写数据页按顺序增长不容易产生页分裂。反过来如果主键用 UUID 这种随机字符串每次插入都可能落在已有数据中间触发页分裂产生碎片写入性能就会明显变差。这里我把主键常见方案列一下主键方案写入顺序页分裂风险适用场景自增整数顺序追加很低常规业务表UUID/随机字符串随机插入较高分布式生成主键需改造为有序方案业务自然键如身份证取决于业务视分布而定有天然唯一标识且不频繁更新另外二级索引的叶子节点存的就是主键值。主键越长每个二级索引占的空间就越大。这也是为什么我一直不建议用超长字符串做主键的原因之一。3.2 唯一索引与普通索引的查询差异唯一索引UNIQUE和普通索引普通 KEY在查询上的核心区别是唯一索引能提前终止扫描。普通索引允许重复值找到第一条匹配记录后不能停还得继续查下一个索引项确认没有更多记录唯一索引因为保证唯一找到第一条就可以直接返回理论上走唯一索引的等值查询在极端情况下能省掉一部分扫描。不过日常中等值查询的差别通常不大真正要留意的是写入侧的差异。写入时唯一索引每插入一条记录都要做唯一性检查这会在索引层加锁还可能触发死锁检查所以唯一索引多的表写入吞吐会更低。除此之外唯一索引允许 NULL 值而且 NULL 不算重复这在业务上是个容易忽略的坑——你以为加了唯一索引就万事大吉结果插入了多行 NULL 也没报错。3.3 order by 能不能用上索引关键在“顺序一致”排序慢经常被甩锅给数据库其实很多时候是索引顺序和 ORDER BY 对不上。如果查询条件已经定位到一个小范围排序在这个范围内做 filesort内存或磁盘排序也能接受但如果排序的数据量很大filesort 就是性能瓶颈。想用索引避免排序核心是让 ORDER BY 的字段顺序和索引顺序保持一致。比如索引 (user_id, status, created_at)下面这条查询的排序就能直接走索引WHERE user_id 10086 AND status PAID ORDER BY created_at DESC;原因在于联合索引里同一个 user_id 和 status 下created_at 已经天然有序了优化器直接逆序扫描叶子节点即可。但如果 ORDER BY 的是 status 再按 created_at或者 WHERE 里跳过了中间的 status 直接按 created_at 排序索引顺序就对不上只能 filesort。这里有个容易踩的细节DESC 和 ASC 混用也可能导致无法走索引。早期 MySQL 对反向排序支持有限8.0 对降序索引做了改进但实际使用中尽量保持排序方向一致比较稳妥。如果你 EXPLAIN 里出现了 Using filesort先别急着加排序缓冲区回头检查索引顺序往往更有效。4. 索引失效的典型场景明明有索引为什么没生效4.1 对索引列做运算或函数调用优化器直接放弃最常见的失效写法是对索引列套函数。比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-15;即便你在 created_at 上建了索引DATE() 函数也让索引失去意义因为 B 树里存的是完整的日期时间值不是日期字符串优化器无法按树结构查找。正确写法是把函数去掉改成范围条件SELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;同理索引列上做算术运算也一样WHERE price * 0.9 100 会让 price 索引失效需要写成 WHERE price 100 / 0.9。原则就是让索引列保持原样出现在比较符的一侧所有运算尽量转移到常量一侧。4.2 隐式类型转换和前导模糊匹配隐式类型转换是特别隐蔽的杀手。MySQL 里如果一个字段是 VARCHAR但你传入的参数是个数字优化器会尝试把字段转成数字来比较。一旦字段上发生了类型转换索引就失效了。比如SELECT * FROM users WHERE phone 13800138000; -- phone 是 varchar这里传的是数字正确写法是传字符串WHERE phone 13800138000。至于为什么转换发生在字段侧而不是常量侧这其实由 MySQL 内部的类型优先级决定你只需要记住类型保持一致是最稳妥的。前导模糊匹配也是老生常谈LIKE %keyword% 没法走索引因为 B 树是按前缀排序的你不知道匹配从哪里开始。但 LIKE keyword% 是可以走索引的这一点在搜索场景里可以作为优化的思路——实在需要中间匹配可以考虑全文索引或者让应用层分词而不是盲目索引一个根本无法前缀匹配的列。4.3 OR、范围条件与最左前缀的边界OR 是个非常坑的用法。如果 OR 两边的字段都是索引列而且索引设计合理优化器可能选择 index_merge但如果其中一边不是索引列整个查询基本就退化成全表扫描了。比如SELECT * FROM orders WHERE user_id 10086 OR status PAID;即使 user_id 有索引status 也有索引优化器也要把两边的数据都取出来再合并一旦某个条件没法走索引整体就只能 ALL。更麻烦的是 OR 旁边的范围条件比如WHERE created_at 2024-01-01 AND status PAID联合索引 (created_at, status) 的情况下created_at 的范围条件一旦出现后面的 status 在索引定位中就不太好使了因为范围后面无法继续利用有序性定位。这也是为什么我前面强调范围字段尽量放到联合索引靠后的位置等值字段放前面。正确设计在这里应该是 (status, created_at)。注意索引失效的排查优先级顺序是先看 EXPLAIN 的 key再看 type再看 Extra。别凭感觉猜“应该是索引了”执行计划会告诉你真相。优化器虽然怂但它用自己的成本模型算过账。4.4 优化器的“理性”决定回表成本太高时宁可扫全表有些情况索引确实“能用”但优化器不选它因为走索引回表的成本可能比全表扫描更高。典型的就是选择性太差的字段比如 status 只有两个值 PAID / UNPAID一张表里一半都是 PAID。用 status 索引查找要回表几十万行还不如直接全表扫描再过滤。还有小表可能整张表就几百行一个数据页就装下了全表扫描一次 I/O 就搞定走索引反而要多读索引页再回表。这类情况 EXPLAIN 里通常能看到 rows 很小或者 key 为 NULL并不是索引坏了而是优化器认为不值得用。想确认优化器的真实判断可以打开 optimizer traceSET optimizer_traceenabledon; SELECT * FROM orders WHERE status PAID; SELECT * FROM information_schema.OPTIMIZER_TRACE;在这个输出里能看到行数估算和成本对比看完你会对 MySQL 的“理性”有很直观的认识。5. 索引不是免费的午餐表空间、碎片和冗余维护5.1 每一次写入都在维护每一棵索引树很多人在意查询速度却忘了索引是要付出写入代价的。INSERT 一行数据InnoDB 不只要写聚簇索引还要同步更新这张表上的所有二级索引。如果一张表上有 5 个索引那一次 INSERT 就要往 5 棵 B 树里插入对应条目UPDATE 更麻烦涉及索引列变更时还要删除旧条目、插入新条目。这在系统并发写入高的时候特别明显。我见过一个表原本 7 个索引业务方为了各种报表查询不断加索引结果平时查询是快了但每天的批量写入任务从 20 分钟涨到了 2 个小时。排查之后发现瓶颈就是索引维护。所以索引要“按需建”每多一个索引都要有明确的查询场景支撑。5.2 怎么识别和清理冗余索引冗余索引是最常见的管理问题。典型的冗余就是已有联合索引 (a, b, c)又单独建了 (a, b) 和 (a)理论上后面的两个索引能被前者覆盖或者两个联合索引前缀相同比如 (a, b) 和 (a, c)前面那列相同但后续列不同这种不算完全冗余但往往也能优化合并。我比较推荐的排查方式一是自己从业务 SQL 反推列出高频查询再看现有索引是否能被最左前缀覆盖二是借助 performance_schema 里的索引使用统计找出那些几乎没被用过的索引SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_STAR DESC;长时间 COUNT_STAR 接近 0 的索引基本可以列为删除候选。此外 pt-duplicate-key-checker 这类工具也会直接列出重复索引适合在表数量大的时候做整体检查。注意删除索引之前一定要确认它没有承担唯一性约束比如 UNIQUE 索引除了性能还负责保证数据唯一性不能因为“用得少”就删。删之前最好在测试环境压一遍主查询确认不影响执行计划。5.3 索引碎片和 OPTIMIZE TABLE 的正确打开方式索引用久了会产生碎片主要原因有两个随机插入导致的数据页分裂以及大量删除留下的空洞。碎片会让索引页的存储不再紧凑扫描时读到的页更多I/O 效率变差。这不是索引失效而是索引“变胖”了。重建索引的常用手段是 OPTIMIZE TABLE但它的代价是重建整张表锁表时间长对在线业务影响很大。MySQL 8.0 的在线 DDL 比 5.7 更成熟但 OPTIMIZE TABLE 依然要在低峰期执行而且要提前评估表大小。判断碎片程度可以用 information_schema 里的 Data_free 做个粗略估算SELECT table_name, data_length, data_free, data_free / (data_length data_free) AS fragmentation_ratio FROM information_schema.TABLES WHERE table_schema your_db;如果碎片率明显偏高又不想停业务也可以考虑用 ALTER TABLE ... FORCE 在在线 DDL 支持范围内重建。但我的建议始终是碎片只是性能因素中比较靠后的一环先把查询逻辑和执行计划优化好再考虑重建不要动不动就 OPTIMIZE。6. 一次线上慢查询的完整排查与索引优化实录6.1 现场慢日志里的一条 3 秒查询这是前一阵子一个订单系统的真实问题。表结构简化后大概长这样CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB;慢查询是SELECT id, user_id, amount, created_at FROM orders WHERE user_id 10086 AND status PAID ORDER BY created_at DESC LIMIT 20;EXPLAIN 的结果很典型typeALLkey 显示 NULLrows 估算约 480 万Extra 里还有 Using filesort。虽然表上有 idx_user_id但优化器发现需要先按 user_id 筛出几万条再按 status 过滤再对结果排序回表成本太高干脆走了全表扫描。6.2 推导索引并验证效果按前面讲的思路等值条件 user_id 和 status 放前面排序字段 created_at 放后面建联合索引ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);再次 EXPLAINtype 变成了 refkey 是 idx_user_status_createdrows 降到了几十行Extra 里 Using filesort 消失了只剩 limit 相关的信息。实际查询压测下来从 3 秒左右降到了 30 毫秒以内100 倍的提升这就是联合索引把定位、过滤、排序三个阶段合成了一个过程的结果。这里我多说一句如果业务里对 status 单独查询的场景也很多而且 status 过滤性差那么 (status, user_id, created_at) 可能是另一套选择。我在这次优化里选择 (user_id, status, created_at)是因为业务上 user_id 的单用户查询是绝对高频而且 user_id 的选择性远高于 status等值条件靠前收益最大。6.3 上线 DDL 需要注意的锁表时序给大表加索引本身也有风险。在 MySQL 5.7 里虽然很多 DDL 支持 ALGORITHMINPLACE但如果当前有长事务或者主从延迟执行 ALTER TABLE 依然可能造成阻塞。实际操作中我是这么做的先在测试环境用相同数据量验证执行计划和耗时。选择业务低峰期在从库上先执行 DDL确认不影响复制再切主库。主库执行时监控 threads_running 和锁等待如果发现问题马上终止。如果是特别大的表优先考虑 gh-ost 或 pt-online-schema-change 这类在线变更工具减少锁表窗口。这一套做完索引才算是安全落地。索引不是加上了就万事大吉后续还需要定期看执行计划、监控慢查询、清理冗余索引形成一套循环。我个人的习惯是每接到一个新报表查询先不看代码直接拿 SQL 做 EXPLAIN确认执行计划后再决定要不要动索引。索引是数据库性能优化的第一杠杆但用不好就是给自己埋雷。希望这篇文章能让你在下次面对慢查询时不只是会加索引而是知道为什么加、加在哪个列、加完怎么验证、后续怎么维护。这套思路远比记住几条索引语法值钱。
MySQL索引使用全解：从联合索引设计到慢查询优化实战
我接手过不少线上数据库的烂摊子其中八成以上都和索引有关。要么是查询慢到被业务方追着问要么是索引建了一堆却根本不起作用还有的是因为加索引导致线上写入抖动。MySQL 索引这个东西光知道“创建索引能加速查询”远远不够真正要命的是一套完整的使用逻辑什么时候该建、按什么顺序建、哪些写法会让索引白建、以及索引背后到底消耗了什么。这篇文章我就围绕“MySQL 索引的使用”把这些问题一次讲透内容偏向实战落地适合刚接触索引的开发者也适合写过几年 SQL 但没系统梳理过索引逻辑的老手。先给你一个场景一张订单表跑到 500 万行一条 where 查询从接口进去要 3 秒才返回慢日志里全是它的影子。第一反应自然是加索引但索引加在哪个字段、组合索引怎么排、加了之后为什么 EXPLAIN 还是显示全表扫描这才是真正的分水岭。下面我从索引的底层逻辑讲起再拆解联合索引、失效场景、维护成本最后用一个完整的排查案例把这套思路串起来。1. 为什么一条简单的 where 能慢到怀疑人生先理解索引在解决什么问题1.1 没有索引时MySQL 是怎么找数据的你可以把 InnoDB 表想象成一本没有目录、但按章节顺序排好内容的书。查询的时候MySQL 只能从第一页翻到最后一页逐行比对字段值这个动作叫全表扫描typeALL。我见过很多新手以为“表不大扫就扫了”但实际上 InnoDB 的数据是按 16KB 的数据页Page组织的500 万行订单数据大概要占几千个数据页每个页里可能几十到几百行记录。全表扫描意味着要把这些页全部读进内存做匹配I/O 次数直接和表大小成正比。你可能会问不是有 Buffer Pool 吗读过的页会被缓存但慢查询的特征恰恰是数据量大、访问模式分散缓存命中率低最终还是落到磁盘读取。所以全表扫描的耗时不是线性上涨而是在某个数据量级上突然变得不可接受。这就是索引要解决的最根本的问题把“翻整本书”变成“查目录”把磁盘 I/O 从几千次降成几次。1.2 B 树索引用空间换时间的经典设计MySQL 最常用的索引结构是 B 树InnoDB 里实际上有两种聚簇索引clustered index表数据的物理存储顺序由主键决定叶子节点直接存整行数据。二级索引secondary index叶子节点存的是索引列的值 主键值查询时要根据主键再回聚簇索引拿整行数据这个过程叫回表。B 树的厉害之处在于它的层数很矮。一个三层 B 树就能管理几千万行数据也就是最多 3 次磁盘 I/O 就能定位到目标叶子节点。作为对比全表扫描可能把几千个页都读一遍。这中间的差距就是毫秒和秒级的差距。这里有个很容易被忽略的点二级索引本质上是个“索引列 - 主键”的映射表。如果你查询的字段本身不完整回表这一步是逃不掉的而回表又是一次随机 I/O。所以后面我们会聊覆盖索引就是为了把回表省掉。1.3 怎么用 EXPLAIN 验证索引是否真的生效纸上谈兵没用每一条 SQL 都应该用 EXPLAIN 验证执行计划。我平时最关注这几列type从好到坏大致是 system const eq_ref ref range index ALL。看到 ALL 基本就是全表扫描看到 index 也要警惕它可能是扫了整个索引树。key实际用到的索引名。如果你明明建了索引这里却是 NULL说明优化器没用。rows优化器估算的需要扫描的行数这个数字能直观反映索引的效果。Extra如果出现 Using filesort 或 Using temporary说明排序和分组没走索引通常意味着还有优化空间。提示EXPLAIN 是验证索引是否生效的最快手段后面所有案例我都会拿它说话。养成写完 SQL 就 EXPLAIN 的习惯会比你看十篇理论文章都管用。2. “where a and b”到底怎么建索引联合索引的设计思路2.1 最左前缀原则以及为什么顺序这么重要很多人在多个字段上各建一个单列索引比如给 a 建了 idx_a给 b 建了 idx_b然后以为 where a and b 能同时用上两个索引。实际 MySQL 大多数时候只会选其中一个另一个字段再回表过滤效率并不理想。正确做法是建联合索引 (a, b)。联合索引遵循最左前缀原则索引 (a, b, c) 能匹配 (a)、(a, b)、(a, b, c) 三种查询条件但查询条件是 b 单独或者 b and c 时这个索引就用不上。原因要从 B 树的排序规则理解联合索引的每个节点先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。既然最前面的列是排序的第一关键字跳过了它后面的顺序关系就失去了意义。这里的关键不是背原则而是把“最左前缀”四个字转化成设计直觉联合索引本质上定义了一个字段的优先级顺序你写查询条件和索引顺序一致时优化器才能一层层定位下去。2.2 从一条慢查询推导索引字段顺序我接手的绝大多数慢查询都是复合条件典型的像这样SELECT order_id, amount, created_at FROM orders WHERE user_id 10086 AND status PAID ORDER BY created_at DESC LIMIT 20;很多人第一反应是三个字段都建索引或者建 (user_id, status, created_at) 这种联合索引但根本说不清为什么。实际上判断顺序有两步第一步区分字段在查询里的角色。等值筛选条件user_id ?、status ?决定定位的精度排序字段ORDER BY created_at决定是否需要额外排序。通常把等值条件的字段放在联合索引前面把排序字段放在后面。所以这里最自然的选择是 (user_id, status, created_at) 或者 (status, user_id, created_at)第二步再决定这两个等值条件谁在前。第二步看字段选择性也就是这个字段能区分多少行。区分度可以这样估算SELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_selectivity, COUNT(DISTINCT status) / COUNT(*) AS status_selectivity FROM orders;user_id 的选择性通常远高于 status因为 status 只有 PAID、UNPAID、REFUND 等少数几个值。选择性高的字段放前面可以更快地把数据量缩小到更小的范围。所以上面的查询我一般会建 (user_id, status, created_at)而不是 (status, user_id, created_at)。但这里有个微妙的权衡如果业务里 status 的单条件查询特别多且 status 本身过滤后的数据量足够小把 status 放前面也可以。没有银弹只有基于实际数据分布的顺序推导。2.3 覆盖索引带来的意外收益联合索引除了满足查询条件还有一个隐藏红利叫覆盖索引。如果查询要的字段都在索引树里InnoDB 就不用回表直接扫二级索引就拿到了全部数据。Extra 里会显示 Using index这是我很喜欢看到的字样。举个简单的例子SELECT user_id, created_at FROM orders WHERE status PAID AND created_at 2024-01-01;如果索引是 (status, created_at)那么这两个条件都能被利用而且查询所需的 user_id 和 created_at 都在索引里根本不需要回表查整行数据。在设计联合索引时把 SELECT 里高频出现的字段合理地加进索引尾部往往能让查询再快一个量级。注意覆盖索引不是把 SELECT 的字段一股脑全加进索引。索引列越多写入成本越高叶子节点越大一页能存的索引项越少。我个人的经验是只覆盖高频查询里不大的字段大字段比如长文本、BLOB千万别往索引里塞。3. 主键索引、唯一索引和排序几个容易搞混的边界3.1 主键索引为什么是聚簇索引主键怎么选InnoDB 表本身就是按主键索引组织的这就是聚簇索引。你没有显式定义主键InnoDB 也会找第一个非空的唯一索引或者偷偷生成一个 6 字节的隐藏主键。所以主键选择非常影响表结构的底层效率。日常开发里自增整数主键通常是最省心的选择插入的时候是追加写数据页按顺序增长不容易产生页分裂。反过来如果主键用 UUID 这种随机字符串每次插入都可能落在已有数据中间触发页分裂产生碎片写入性能就会明显变差。这里我把主键常见方案列一下主键方案写入顺序页分裂风险适用场景自增整数顺序追加很低常规业务表UUID/随机字符串随机插入较高分布式生成主键需改造为有序方案业务自然键如身份证取决于业务视分布而定有天然唯一标识且不频繁更新另外二级索引的叶子节点存的就是主键值。主键越长每个二级索引占的空间就越大。这也是为什么我一直不建议用超长字符串做主键的原因之一。3.2 唯一索引与普通索引的查询差异唯一索引UNIQUE和普通索引普通 KEY在查询上的核心区别是唯一索引能提前终止扫描。普通索引允许重复值找到第一条匹配记录后不能停还得继续查下一个索引项确认没有更多记录唯一索引因为保证唯一找到第一条就可以直接返回理论上走唯一索引的等值查询在极端情况下能省掉一部分扫描。不过日常中等值查询的差别通常不大真正要留意的是写入侧的差异。写入时唯一索引每插入一条记录都要做唯一性检查这会在索引层加锁还可能触发死锁检查所以唯一索引多的表写入吞吐会更低。除此之外唯一索引允许 NULL 值而且 NULL 不算重复这在业务上是个容易忽略的坑——你以为加了唯一索引就万事大吉结果插入了多行 NULL 也没报错。3.3 order by 能不能用上索引关键在“顺序一致”排序慢经常被甩锅给数据库其实很多时候是索引顺序和 ORDER BY 对不上。如果查询条件已经定位到一个小范围排序在这个范围内做 filesort内存或磁盘排序也能接受但如果排序的数据量很大filesort 就是性能瓶颈。想用索引避免排序核心是让 ORDER BY 的字段顺序和索引顺序保持一致。比如索引 (user_id, status, created_at)下面这条查询的排序就能直接走索引WHERE user_id 10086 AND status PAID ORDER BY created_at DESC;原因在于联合索引里同一个 user_id 和 status 下created_at 已经天然有序了优化器直接逆序扫描叶子节点即可。但如果 ORDER BY 的是 status 再按 created_at或者 WHERE 里跳过了中间的 status 直接按 created_at 排序索引顺序就对不上只能 filesort。这里有个容易踩的细节DESC 和 ASC 混用也可能导致无法走索引。早期 MySQL 对反向排序支持有限8.0 对降序索引做了改进但实际使用中尽量保持排序方向一致比较稳妥。如果你 EXPLAIN 里出现了 Using filesort先别急着加排序缓冲区回头检查索引顺序往往更有效。4. 索引失效的典型场景明明有索引为什么没生效4.1 对索引列做运算或函数调用优化器直接放弃最常见的失效写法是对索引列套函数。比如SELECT * FROM orders WHERE DATE(created_at) 2024-01-15;即便你在 created_at 上建了索引DATE() 函数也让索引失去意义因为 B 树里存的是完整的日期时间值不是日期字符串优化器无法按树结构查找。正确写法是把函数去掉改成范围条件SELECT * FROM orders WHERE created_at 2024-01-15 00:00:00 AND created_at 2024-01-16 00:00:00;同理索引列上做算术运算也一样WHERE price * 0.9 100 会让 price 索引失效需要写成 WHERE price 100 / 0.9。原则就是让索引列保持原样出现在比较符的一侧所有运算尽量转移到常量一侧。4.2 隐式类型转换和前导模糊匹配隐式类型转换是特别隐蔽的杀手。MySQL 里如果一个字段是 VARCHAR但你传入的参数是个数字优化器会尝试把字段转成数字来比较。一旦字段上发生了类型转换索引就失效了。比如SELECT * FROM users WHERE phone 13800138000; -- phone 是 varchar这里传的是数字正确写法是传字符串WHERE phone 13800138000。至于为什么转换发生在字段侧而不是常量侧这其实由 MySQL 内部的类型优先级决定你只需要记住类型保持一致是最稳妥的。前导模糊匹配也是老生常谈LIKE %keyword% 没法走索引因为 B 树是按前缀排序的你不知道匹配从哪里开始。但 LIKE keyword% 是可以走索引的这一点在搜索场景里可以作为优化的思路——实在需要中间匹配可以考虑全文索引或者让应用层分词而不是盲目索引一个根本无法前缀匹配的列。4.3 OR、范围条件与最左前缀的边界OR 是个非常坑的用法。如果 OR 两边的字段都是索引列而且索引设计合理优化器可能选择 index_merge但如果其中一边不是索引列整个查询基本就退化成全表扫描了。比如SELECT * FROM orders WHERE user_id 10086 OR status PAID;即使 user_id 有索引status 也有索引优化器也要把两边的数据都取出来再合并一旦某个条件没法走索引整体就只能 ALL。更麻烦的是 OR 旁边的范围条件比如WHERE created_at 2024-01-01 AND status PAID联合索引 (created_at, status) 的情况下created_at 的范围条件一旦出现后面的 status 在索引定位中就不太好使了因为范围后面无法继续利用有序性定位。这也是为什么我前面强调范围字段尽量放到联合索引靠后的位置等值字段放前面。正确设计在这里应该是 (status, created_at)。注意索引失效的排查优先级顺序是先看 EXPLAIN 的 key再看 type再看 Extra。别凭感觉猜“应该是索引了”执行计划会告诉你真相。优化器虽然怂但它用自己的成本模型算过账。4.4 优化器的“理性”决定回表成本太高时宁可扫全表有些情况索引确实“能用”但优化器不选它因为走索引回表的成本可能比全表扫描更高。典型的就是选择性太差的字段比如 status 只有两个值 PAID / UNPAID一张表里一半都是 PAID。用 status 索引查找要回表几十万行还不如直接全表扫描再过滤。还有小表可能整张表就几百行一个数据页就装下了全表扫描一次 I/O 就搞定走索引反而要多读索引页再回表。这类情况 EXPLAIN 里通常能看到 rows 很小或者 key 为 NULL并不是索引坏了而是优化器认为不值得用。想确认优化器的真实判断可以打开 optimizer traceSET optimizer_traceenabledon; SELECT * FROM orders WHERE status PAID; SELECT * FROM information_schema.OPTIMIZER_TRACE;在这个输出里能看到行数估算和成本对比看完你会对 MySQL 的“理性”有很直观的认识。5. 索引不是免费的午餐表空间、碎片和冗余维护5.1 每一次写入都在维护每一棵索引树很多人在意查询速度却忘了索引是要付出写入代价的。INSERT 一行数据InnoDB 不只要写聚簇索引还要同步更新这张表上的所有二级索引。如果一张表上有 5 个索引那一次 INSERT 就要往 5 棵 B 树里插入对应条目UPDATE 更麻烦涉及索引列变更时还要删除旧条目、插入新条目。这在系统并发写入高的时候特别明显。我见过一个表原本 7 个索引业务方为了各种报表查询不断加索引结果平时查询是快了但每天的批量写入任务从 20 分钟涨到了 2 个小时。排查之后发现瓶颈就是索引维护。所以索引要“按需建”每多一个索引都要有明确的查询场景支撑。5.2 怎么识别和清理冗余索引冗余索引是最常见的管理问题。典型的冗余就是已有联合索引 (a, b, c)又单独建了 (a, b) 和 (a)理论上后面的两个索引能被前者覆盖或者两个联合索引前缀相同比如 (a, b) 和 (a, c)前面那列相同但后续列不同这种不算完全冗余但往往也能优化合并。我比较推荐的排查方式一是自己从业务 SQL 反推列出高频查询再看现有索引是否能被最左前缀覆盖二是借助 performance_schema 里的索引使用统计找出那些几乎没被用过的索引SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_STAR, COUNT_READ, COUNT_WRITE FROM performance_schema.table_io_waits_summary_by_index_usage WHERE OBJECT_SCHEMA your_db ORDER BY COUNT_STAR DESC;长时间 COUNT_STAR 接近 0 的索引基本可以列为删除候选。此外 pt-duplicate-key-checker 这类工具也会直接列出重复索引适合在表数量大的时候做整体检查。注意删除索引之前一定要确认它没有承担唯一性约束比如 UNIQUE 索引除了性能还负责保证数据唯一性不能因为“用得少”就删。删之前最好在测试环境压一遍主查询确认不影响执行计划。5.3 索引碎片和 OPTIMIZE TABLE 的正确打开方式索引用久了会产生碎片主要原因有两个随机插入导致的数据页分裂以及大量删除留下的空洞。碎片会让索引页的存储不再紧凑扫描时读到的页更多I/O 效率变差。这不是索引失效而是索引“变胖”了。重建索引的常用手段是 OPTIMIZE TABLE但它的代价是重建整张表锁表时间长对在线业务影响很大。MySQL 8.0 的在线 DDL 比 5.7 更成熟但 OPTIMIZE TABLE 依然要在低峰期执行而且要提前评估表大小。判断碎片程度可以用 information_schema 里的 Data_free 做个粗略估算SELECT table_name, data_length, data_free, data_free / (data_length data_free) AS fragmentation_ratio FROM information_schema.TABLES WHERE table_schema your_db;如果碎片率明显偏高又不想停业务也可以考虑用 ALTER TABLE ... FORCE 在在线 DDL 支持范围内重建。但我的建议始终是碎片只是性能因素中比较靠后的一环先把查询逻辑和执行计划优化好再考虑重建不要动不动就 OPTIMIZE。6. 一次线上慢查询的完整排查与索引优化实录6.1 现场慢日志里的一条 3 秒查询这是前一阵子一个订单系统的真实问题。表结构简化后大概长这样CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, amount DECIMAL(10,2) NOT NULL, created_at DATETIME NOT NULL, KEY idx_user_id (user_id) ) ENGINEInnoDB;慢查询是SELECT id, user_id, amount, created_at FROM orders WHERE user_id 10086 AND status PAID ORDER BY created_at DESC LIMIT 20;EXPLAIN 的结果很典型typeALLkey 显示 NULLrows 估算约 480 万Extra 里还有 Using filesort。虽然表上有 idx_user_id但优化器发现需要先按 user_id 筛出几万条再按 status 过滤再对结果排序回表成本太高干脆走了全表扫描。6.2 推导索引并验证效果按前面讲的思路等值条件 user_id 和 status 放前面排序字段 created_at 放后面建联合索引ALTER TABLE orders ADD INDEX idx_user_status_created (user_id, status, created_at);再次 EXPLAINtype 变成了 refkey 是 idx_user_status_createdrows 降到了几十行Extra 里 Using filesort 消失了只剩 limit 相关的信息。实际查询压测下来从 3 秒左右降到了 30 毫秒以内100 倍的提升这就是联合索引把定位、过滤、排序三个阶段合成了一个过程的结果。这里我多说一句如果业务里对 status 单独查询的场景也很多而且 status 过滤性差那么 (status, user_id, created_at) 可能是另一套选择。我在这次优化里选择 (user_id, status, created_at)是因为业务上 user_id 的单用户查询是绝对高频而且 user_id 的选择性远高于 status等值条件靠前收益最大。6.3 上线 DDL 需要注意的锁表时序给大表加索引本身也有风险。在 MySQL 5.7 里虽然很多 DDL 支持 ALGORITHMINPLACE但如果当前有长事务或者主从延迟执行 ALTER TABLE 依然可能造成阻塞。实际操作中我是这么做的先在测试环境用相同数据量验证执行计划和耗时。选择业务低峰期在从库上先执行 DDL确认不影响复制再切主库。主库执行时监控 threads_running 和锁等待如果发现问题马上终止。如果是特别大的表优先考虑 gh-ost 或 pt-online-schema-change 这类在线变更工具减少锁表窗口。这一套做完索引才算是安全落地。索引不是加上了就万事大吉后续还需要定期看执行计划、监控慢查询、清理冗余索引形成一套循环。我个人的习惯是每接到一个新报表查询先不看代码直接拿 SQL 做 EXPLAIN确认执行计划后再决定要不要动索引。索引是数据库性能优化的第一杠杆但用不好就是给自己埋雷。希望这篇文章能让你在下次面对慢查询时不只是会加索引而是知道为什么加、加在哪个列、加完怎么验证、后续怎么维护。这套思路远比记住几条索引语法值钱。