MySQL索引优化实战：从加索引到EXPLAIN验证的完整指南

“这个表加个索引吧”——很多人对 MySQL 表添加索引这件事的认知可能就停留在这一句话上。作为一个经常跟慢查询打交道的人我想说加索引本身不难难的是知道为什么加、加在哪几列、用什么姿势加以及加完之后怎么验证它真的生效了。这篇文章不聊高深理论就把我这些年给线上 MySQL 表加索引的完整思路、实操 SQL、踩坑经历和验证方法一次说清楚希望能帮你少走弯路。1. 一条慢查询背后什么时候该给MySQL表加索引先还原一个我印象很深的场景。上半年接手一个电商后台系统订单表也就几百万行不算夸张但运营侧经常反馈“订单列表加载要好几秒”。一开始我以为又是服务器带宽或者应用代码的问题结果翻到数据库一看发现所有订单查询都在走全表扫描。几百万行数据每次查询都要把整张表从头到尾读一遍不快才怪。这就是典型的“没加索引”症状。MySQL 在 InnoDB 引擎下数据是按 B 树结构组织的主键索引天生存在但普通查询条件如果没有对应索引优化器只能选择全表扫描。全表扫描的本质是线性遍历数据量越大耗时越长而且这个慢是没有任何缓存技巧能彻底弥补的。1.1 全表扫描为什么慢两个经典查询对比拿一个最简单的例子说。假设有一张orders订单表结构大概是这样的CREATE TABLE orders ( id BIGINT PRIMARY KEY AUTO_INCREMENT, user_id INT NOT NULL, order_no VARCHAR(64) NOT NULL, status TINYINT NOT NULL, created_at DATETIME NOT NULL, KEY idx_created_at (created_at) );如果查询条件落在未建索引的字段上SELECT * FROM orders WHERE user_id 10001 ORDER BY created_at DESC LIMIT 20;在没有user_id索引的情况下这条 SQL 的执行计划里type就是ALL表示全表扫描。MySQL 会把几百万行数据一页页读进内存逐行比对user_id找到满足条件的行之后还要再做一次ORDER BY created_at的排序操作性能自然崩塌。而给user_id加上索引之后执行计划会变成ref或rangeInnoDB 通过 B 树直接定位到user_id10001的若干条索引记录再回表取完整行数据读取的数据页可能只有全表扫描的千分之一。这条查询从秒级降到毫秒级靠的就是索引把“查找”从遍历变成了跳跃。1.2 什么情况下才值得加索引但这里必须泼一盆冷水不是所有表都适合加索引也不是所有字段都适合建索引。我在团队里反复强调一个原则——索引是拿“写入开销 存储空间”换“查询速度”所以要分场景。适合加索引的情况很明确高频查询的 WHERE 条件字段例如订单表的user_id、order_noJOIN 语句的关联字段例如子表里的order_id需要排序或分组的字段比如ORDER BY created_at、GROUP BY status需要保证唯一性的字段比如order_no用于唯一索引。不适合加索引的情况也很典型表本身很小只有几千行甚至几百行全表扫描本来就快建索引反而增加维护成本写入非常频繁而查询极少的热点表每个索引都会拖慢 INSERT、UPDATE 的性能字段区分度极低比如只有 0 和 1 两个值的is_deleted字段单独建索引基本没用。我自己的实操习惯是先用慢查询日志或者performance_schema找 Top SQL再通过EXPLAIN确认是不是全表扫描最后才决定要不要加索引。效果导向而不是“看着某个字段顺眼就建一个”。2. 加索引的完整SQL操作三种方式与应用场景辨析确定了要给哪张表、哪个字段加索引之后接下来就是动手写 SQL。MySQL 提供的主流建索引方式有几种很多人一直只用其中一种其实它们各有侧重用好组合能省不少事。2.1 建索引的三种SQL姿势第一种是在创建表结构时直接定义索引CREATE TABLE users ( id BIGINT PRIMARY KEY, email VARCHAR(128) NOT NULL, nickname VARCHAR(32), UNIQUE KEY uk_email (email), KEY idx_nickname (nickname) );这种方式适合新表建表时就把常规查询路径规划好避免上线后再做 DDL。第二种是用 ALTER TABLE 语句添加索引ALTER TABLE users ADD INDEX idx_nickname (nickname); ALTER TABLE users ADD UNIQUE INDEX uk_email (email); ALTER TABLE users ADD INDEX idx_status_created (status, created_at);第三种是独立的 CREATE INDEX 语句CREATE INDEX idx_nickname ON users (nickname); CREATE UNIQUE INDEX uk_email ON users (email);从功能结果上看ALTER TABLE ... ADD INDEX和CREATE INDEX几乎等价都能创建索引。但ALTER TABLE有一个优势是可以在一条语句里同时做多个变更比如加索引的同时修改字段类型、加字段组合操作时更灵活。比如ALTER TABLE users ADD COLUMN mobile VARCHAR(20) DEFAULT NULL, ADD INDEX idx_email (email), ADD UNIQUE INDEX uk_mobile (mobile);这种一条语句搞定多个变更的方式在 MySQL 内部可以共用一次表重建或在线变更的调度比逐条执行更可控。2.2 ALTER TABLE与CREATE INDEX到底该用哪个要说选型逻辑我的建议很简单如果你的目的是纯粹加索引用CREATE INDEX语义更清晰如果你要顺带调整别的表结构用ALTER TABLE合并 DDL 操作如果你在建表初期规划索引直接在CREATE TABLE里指定。另外要注意一个细节索引名称不要乱起。业界约定俗成的命名规则是idx_字段名普通索引、uk_字段名唯一索引多个字段用下划线连接比如idx_user_status。命名清晰的好处有两个——排查问题时一看执行计划里用了哪个索引就明白意图维护旧索引时也知道该从哪儿查起。还有一个容易忽略的点InnoDB 表的主键天然是聚簇索引不需要为id额外再建索引。很多人会在主键字段上再建一个普通索引这叫“重复索引”纯粹浪费空间。2.3 大表加索引在线DDL参数别乱写这是最需要重点说的一节。给几百万、上千万行的大表加索引跟小表完全不是一回事。在 MySQL 5.6 之前加索引的 DDL 往往需要锁表业务写入会被阻塞严重的能把线上服务直接打断。MySQL 5.6 开始支持 Online DDL 之后可以在执行 DDL 的同时允许部分 DML 操作但前提是你得显式指定算法和锁策略ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHMINPLACE, LOCKNONE;这段 SQL 的含义很关键ALGORITHMINPLACE表示在 InnoDB 内部就地构建索引不拷贝整张表的数据LOCKNONE表示允许并发的读写不阻塞线上业务。但这里有几个坑想提醒你。第一即使用了LOCKNONE大表加索引期间依然会产生额外的 I/O 和资源消耗高峰期还是可能把数据库负载推高。第二有些操作并不支持INPLACE和LOCKNONE比如部分类型的字段变更如果你不加参数直接执行MySQL 可能悄悄用 COPY 方式重建整表导致长时间锁表。第三如果是特别大的表更稳妥的做法是用pt-online-schema-change这类工具先创建临时表、同步增量数据、最后原子切换表名把对线上影响降到最低。我自己实测下来千万级以下用 Online DDL 的LOCKNONE基本能接受但千万级以上我会在业务低峰期操作并且提前准备好回滚方案——加索引不像删除索引那么轻量出错时要能及时恢复。3. 索引类型选择不是所有索引都叫BTREE很多新手以为索引就是给字段“加个 KEY”其实 MySQL 里的索引有很多细分类型选错了不仅效果打折还可能出现“明明建了索引却不走”的尴尬。这个章节我们按两个维度拆开看数据结构维度、用途维度。3.1 按数据结构分BTREE、HASH、FULLTEXT先上表索引类型底层结构适合场景注意事项BTREEB 树绝大多数场景支持范围查询、排序、最左前缀InnoDB 默认也是平时最常用的HASH哈希表等值查询极快例如WHERE user_id ?不支持范围查询不支持排序场景很窄FULLTEXT倒排索引大文本字段的模糊搜索比如文章内容匹配中文分词要额外配置数据量杂时维护成本高SPATIALR 树地理坐标等空间数据用得很少一般不在业务表里直接建绝大多数项目里你只需要记住一句话InnoDB 表默认的 BTREE 索引覆盖 95% 以上的需求。HASH 索引本身在 InnoDB 中没有真正可用的“HASH 索引”类型只有 MEMORY 引擎和 NDB 引擎支持所以不要想着给 MySQL 普通表指定 HASH 索引来优化等值查询——InnoDB 是在内存中靠自适应哈希索引来加速 BTREE 查找这不是你 DDL 能直接控制的。FULLTEXT 索引也一样日常业务查询里用到全文检索的场景不多真遇到建议先考虑专业搜索服务而不是在 MySQL 里硬扛。我想说的是数据结构维度更像是一个背景知识真正日常操作时你常用的还是 BTREE。3.2 按用途分普通、唯一、联合、前缀接下来这几种才是每天打交道的。普通索引KEY / INDEX最基础的类型只加速查询不强制数据唯一。比如ALTER TABLE orders ADD INDEX idx_status (status);唯一索引UNIQUE KEY除了加速查询之外还强制该列或列组合的值全局唯一。它同时承担着“数据库约束”和“查询加速”两种职责。比如订单编号order_no就应该用唯一索引这样不仅查询快应用层也能依靠数据库硬性防重复。ALTER TABLE orders ADD UNIQUE INDEX uk_order_no (order_no);联合索引复合索引这是很多性能问题的关键所在。它可以同时对多个字段建立一棵 B 树比如idx_user_created (user_id, created_at)这样WHERE user_id ? ORDER BY created_at DESC这条查询就能只走一个索引完成不需要额外的排序步骤。前缀索引针对 VARCHAR、TEXT 等大字段只取前 N 个字符建索引大幅减少索引体积。比如邮箱字段前 20 个字符基本就能区分绝大部分数据ALTER TABLE users ADD INDEX idx_email_prefix (email(20));前缀索引的代价是查询时如果SELECT的字段不在索引覆盖范围内依然要回表而且前缀长度选得太短区分度会恶化。它比较适合“字段很长但业务上只做前缀匹配”的场景适用范围偏窄。3.3 聚簇索引和二级索引为什么会有“回表”这个知识点偏原理但太重要了我强烈建议每个写 SQL 的人都要理解。InnoDB 表的主键索引是聚簇索引叶子节点直接存整行数据而其他所有二级索引也就是咱们手动建的普通索引、联合索引的叶子节点存的是主键值和索引列的值。所以当你通过二级索引查到一条记录时如果SELECT需要的字段在二级索引里找不到MySQL 就必须拿着主键值再去聚簇索引里取一遍完整行数据这个动作就叫“回表”。回表本身不是问题小量回表性能完全可接受。可如果索引选的字段不对导致每一行都要回表大批量数据下性能就会显著下降。这也是为什么我要在下一章专门讲“覆盖索引”——把查询需要的字段全部放进索引里就能省掉回表这一步很多慢查询的优化秘密就在这里。4. 设计索引的关键判断区分度、最左前缀与覆盖索引如果说前面几节是“索引怎么加”这一节真正回答的是“索引怎么设计”。我一直跟团队成员说建索引不是技术活而是决策活。你多花的不是那几条 DDL 语句的时间而是想清楚业务查询模式的精力。4.1 区分度索引列选“值分布广”的索引的本质是缩小查找范围。如果一个字段在全部数据里只有两三个值比如status只有“未支付、已支付、已退款”三种单独建索引的价值就很低。因为每次查“已支付”可能命中几百万条记录MySQL 会认为没必要走索引直接全表扫描反而更快。怎么判断一个字段的区分度好不好看两个指标唯一值数量或其估算值 Cardinality总行数。比如一张 500 万行的用户表email字段的唯一值几乎等于 500 万区分度接近 1就是很好的索引列而gender字段可能就两三个唯一值区分度趋近于 0不适合单列索引。Cardinality可以通过SHOW INDEX FROM 表名直接看到后续我会详细展开。设计索引时尽量把区分度高的字段放在索引的前面这一步对查询优化影响极为明显。4.2 最左前缀原则联合索引的顺序怎么定联合索引之所以叫“联合”是因为它里面有多个字段但 MySQL 在使用时遵循一个铁律最左前缀原则。意思是从联合索引的第一个字段开始如果查询条件没有先命中前面的字段那么这个联合索引就没法被完整使用。举例来说一个联合索引定义为idx_user_status_created (user_id, status, created_at)它能被下面的查询使用WHERE user_id 10001; WHERE user_id 10001 AND status 1; WHERE user_id 10001 AND status 1 AND created_at 2024-01-01;下面的查询就不能完整使用这个索引只能部分匹配甚至完全不走索引WHERE status 1; -- 没用到 user_id从第二个字段开始索引失效 WHERE created_at 2024-01-01; -- 直接跳过前两个字段索引失效所以联合索引的字段顺序设计必须把“等值查询条件中最常出现的字段”放在最前面。这是一个在很多线上案例里都要反复斟酌的细节。推荐先分析业务 SQL 里 WHERE 条件的频率把等值条件放前面范围条件放后面。4.3 覆盖索引让查询不回表的实战技巧回到我前面说的“回表”概念。如果查询的所有列都包含在二级索引里那么 MySQL 只需要扫描索引本身就能拿到全部结果不需要再回表。这种情况下执行计划里的Extra列会显示Using index性能相当可观。举个例子。假设有联合索引idx_user_created (user_id, created_at)下面的查询就不需要回表SELECT user_id, created_at FROM orders WHERE user_id 10001;因为user_id和created_at两列都在索引里索引叶子节点已经存放了这两个列的值数据库扫描完索引直接返回。但如果你把SELECT *写进去索引覆盖不了order_no、status等所有字段那就必须回表取整行。在写线上 SQL 的时候我习惯先看SELECT字段列表,再反推索引设计如果高频 SELECT 的字段不超过三五个就可以考虑做一个联合索引把它们全部覆盖进去既能命中 WHERE 条件又能消除回表开销一举两得。4.4 一个完整的联合索引设计流程这里分享一个我在实际业务里常用到的五步设计流程收集一段时间内该表的高频查询 SQL按出现次数排序提取每条 SQL 的 WHERE 条件字段、JOIN 字段、ORDER BY/GROUP BY 字段看哪些条件字段的组合重复出现最多候选联合索引字段就选这一组按“等值字段优先、区分度高的放前、范围字段放后”的原则排顺序设计完用 EXPLAIN 验证判断key是否命中、Extra里有没有出现Using filesort。我自己给订单列表页优化的时候就是先发现高频 SQL 统一集中在user_id status created_at三个条件上于是建了idx_user_status_created联合索引查询耗时从 2.8 秒降到了 80 毫秒左右。索引设计不是靠感觉堆字段而是靠实际 SQL 模式反推。5. 加了索引还是慢常见的失效场景、冗余索引与锁表体验前面讲的是“怎么让索引生效”但现实残酷的是有不少人加了索引之后发现查询还是慢甚至比以前更差。这不是索引没用而是踩进了几个非常典型的坑里。这一节我把高频问题集中盘一遍。5.1 WHERE条件里那些“隐形杀手”最经典的索引失效场景是“对索引列使用函数”比如SELECT * FROM orders WHERE DATE(created_at) 2024-12-01;即使created_at上有索引这个查询也不会走索引因为 MySQL 必须对所有行的created_at先执行DATE()函数再比较索引的顺序信息已经被破坏了。正确的写法是SELECT * FROM orders WHERE created_at 2024-12-01 00:00:00 AND created_at 2024-12-02 00:00:00;同样容易踩坑的还有隐式类型转换。如果order_no是 VARCHAR 类型的字段查询里却写成SELECT * FROM orders WHERE order_no 20241201001;MySQL 会把字段值从字符串转到数字再比较索引同样失效。解决办法是保持类型一致把参数写成字符串形式SELECT * FROM orders WHERE order_no 20241201001;除此之外还有几个高频场景也值得注意LIKE %keyword这种以通配符开头的模糊匹配无法利用 BTREE 索引多个条件之间用OR连接时如果其中一个条件没有索引整个查询可能放弃索引NOT IN、在某些数据分布下也容易让优化器做出全表扫描的决定。这些都是我在排查慢查询时最先检查的方向。5.2 冗余索引和重复索引白占空间的常态另一个非常普遍的问题是索引冗余。我经常见到一张表上有idx_user_id (user_id)和idx_user_created (user_id, created_at)两个索引。从功能上看前者完全被后者覆盖了——因为联合索引最左边的字段就是user_id所以单独建user_id索引纯属多余只会增加写入开销和存储成本。怎么发现这类冗余索引可以通过information_schema.STATISTICS视图做一次手工核对或者直接查看所有索引的字段排列判断是否存在“已有索引 A 是索引 B 的左前缀”的情况。SELECT TABLE_NAME, INDEX_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS idx_columns FROM information_schema.STATISTICS WHERE TABLE_SCHEMA your_db GROUP BY TABLE_NAME, INDEX_NAME;我处理这类问题的原则是先确认业务查询没有单独用到被覆盖的小索引确认无影响后才删除。冗余索引不会让查询变快但一定会让 INSERT、UPDATE 变慢这一点在写入较多的表上尤其明显。5.3 给在线大表加索引锁表那点事热搜词里有个“mysql锁表”正好可以说清楚。很多人以为加索引就一条 DDL 的事但线上大表加索引时最怕的就是锁表。前面我提到 MySQL 5.6 之后的 Online DDL 支持LOCKNONE但版本差异和操作系统环境仍然有可能让你遇到意料之外的锁等待。我遇到过最典型的情况是某核心业务表有 800 万行数据团队直接在交易高峰期执行ALTER TABLE ADD INDEX没有指定ALGORITHM和LOCK参数结果整个表的写入全部卡住持续了近两分钟。事后看监控那段时间所有依赖这张表的接口都超时了。这是相当痛的教训。从那之后我把“大表 DDL 必须有锁策略、必须在低峰期执行、可以用 pt-online-schema-change 或 gh-ost 替代”写进了团队规范。如果有条件先把 DDL 语句在测试库用EXPLAIN和实际数据量模拟一遍确认执行时间可接受再上生产。5.4 字符集不一致导致JOIN索引失效还有一个很隐蔽的问题两张表 JOIN 时如果关联字段的字符集或者排序规则不一致MySQL 可能需要做隐式的字符集转换索引也会失效。比如一张表的member_id是utf8mb4_general_ci另一张表却是utf8mb4_unicode_ci那 JOIN 条件虽然逻辑上等价但底层执行时无法直接使用索引比较。排查方法就是看两张表相关字段的CHARACTER_SET_NAME和COLLATION_NAME。解决办法是统一字符集和排序规则最好在建表时就保持全库一致。这类问题的特点是表面上看 SQL 没问题、索引也存在但执行计划就是不走索引特别容易让人怀疑人生。6. 用EXPLAIN和系统视图确认索引是否真正生效索引加完之后不是结束而是验证的开始。我会在每一条新增索引之后用 EXPLAIN 跑一遍历史慢 SQL确认执行计划确实变了。这也是排查索引问题的核心手段必须熟练掌握。6.1 EXPLAIN关键列解读type、key、rows、Extra看一条典型语句EXPLAIN SELECT * FROM orders WHERE user_id 10001 ORDER BY created_at DESC LIMIT 20;输出里最关键的四列type访问类型。从差到好的顺序大致是ALL全表扫描、index全索引扫描、range范围扫描、ref非唯一等值匹配、eq_ref主键/唯一索引匹配、const主键等值匹配。看到ALL基本可以断定没走索引或索引设计失败。key实际使用的索引名。如果为NULL就说明这条 SQL 没用上任何索引。rows优化器预估需要扫描的行数。数字越小越好。Extra额外信息。Using index表示覆盖索引不需要回表Using filesort表示需要额外排序通常意味着 ORDER BY 字段没吃上索引Using temporary表示查询中用了临时表分组或去重场景常见。我自己判断索引是否有效的标准很简单type从ALL变成range或refrows下降 1 到 2 个数量级Extra里不再出现Using filesort这时基本可以认定这次优化是成功的。6.2 辅助确认工具SHOW INDEX与ANALYZE TABLE日常运维里还有两个命令我用得很多。第一个是SHOW INDEX FROMSHOW INDEX FROM orders;输出里有几列很关键。Non_unique表示是否唯一索引Seq_in_index表示字段在联合索引里的顺序Cardinality是索引区分度的估算值。如果把Cardinality除以表总行数得到的比例越高说明区分度越好索引价值越大。第二个是表和索引统计信息更新。有些时候表里的数据量发生了大幅变化而优化器依赖的统计信息还没有刷新就会出现“明明有索引却不走索引”的情况。这时执行一次ANALYZE TABLE orders;强制更新统计信息再跑一遍 EXPLAIN索引可能就正常命中。虽然这个操作在大多数版本里不会锁表但建议还是放在低峰期执行。6.3 加完索引之后的监测项最后说一点经验之谈索引上线后不要只看 EXPLAIN 就完事。我会额外关注几个持续指标原慢 SQL 的 P95/P99 耗时是否稳定下降数据库的响应延迟在业务高峰是否仍有抖动写入性能有没有明显恶化尤其是写入频繁的流水表索引占用的磁盘空间增长是否在可接受范围内。如果业务查询模式稳定一个设计得当的索引能用很长时间。但如果业务迭代让 SQL 模式变了那些曾经高效的索引也可能变成冗余这时候就要回到information_schema.STATISTICS和慢查询日志做新一轮评估。我自己实操下来MySQL 表添加索引这件事最核心的从来不是那条 DDL 怎么拼而是你能不能从业务 SQL 里提炼出真正被频繁使用的字段组合。这一个习惯坚持两三年你写的索引十有八九都是好用的。