上周帮某开发者排查一个报表接口的慢查询筛选三个月的数据要跑八秒多前端一直在转圈。看了一圈SQL无非就是几条 WHERE 条件加上 ORDER BY表里也就几十万行问题几乎一眼就能锁定——索引没建到位。当时补上一个复合索引查询时间直接掉到几十毫秒整个接口体感完全两样。这种“一条索引救一条SQL”的例子在绝大多数业务系统里每天都在发生。但我在日常帮人看库的过程中发现很多同学对 MySQL 添加索引这件事的理解停留在“会写 CREATE INDEX 就行”的层面实际工作中经常会遇到索引加了但没走、重复建了一堆冗余索引、大表加索引锁断了业务甚至分不清唯一索引和普通索引的区别。这篇主要把 MySQL 添加索引这件事拆开讲清楚尤其是 5 种不同的添加方式各自的适用场景、执行细节和容易踩的坑。内容按实操向展开适合刚接触索引的初学者也适合已经有工作经验的开发或 DBA 拿来对照自查。1. 一条慢SQL引发的思考索引本质上是给数据“排好队”1.1 那次排查索引缺失的典型症状先说回那个订单查询的场景。原始 SQL 大致长这样SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;表里数据量接近六十万行但查询要走全表扫描。执行计划里的 type 是 ALL扫描行数是 578324Extra 列里还有 Using filesort。全表扫一遍加外排八秒多并不意外。加索引之后就两句话的事ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);再看执行计划type 从 ALL 变成了 ref扫描行数从 57 万掉到 120 多行Using filesort 也消失了查询耗时降到 40ms 左右。这个例子其实特别典型它同时踩了三个常见问题WHERE 条件没有可用索引、排序字段没有涵盖在索引里、数据量大到全表扫描的代价无法接受。后面我会把每个点拆开细讲。1.2 索引为什么能快B树与“顺序查找”的逻辑理解 MySQL 的 InnoDB 索引最核心的是要理解 B 树。我打个比方假设你用一本没有书签、没有目录的小说找一句话唯一的办法就是从第一页开始翻这叫全表扫描。如果目录就是 B 树树的高度一般只有三四层你顺着目录查过去经过的节点数量极少很快就能定位到对应页码这就是索引查找。B 树和普通二叉树的区别在于它每个节点可以存很多键值叶子节点存真正的数据而且叶子节点之间通过指针连成了有序链表。这意味着它既能做精确查询也能做高效的范围查询。比如上面那个 SQL 里的create_time 2024-01-01B 树从第一满足条件的位置开始顺着链表往后扫就行了不用再回根节点重新查。InnoDB 的主键索引本身就是一棵聚簇索引树叶子节点直接存整行数据。而二级索引树的叶子节点存的是索引列的值和对应的主键值。所以走二级索引查询时如果 select 的列不能全部在二级索引里找到就需要用主键值回聚簇索引取整行数据这个动作叫回表。能用“覆盖索引”避免回表是后期性能优化里性价比极高的一招后面会专门提到。1.3 索引不是越多越好索引也有维护成本很多同学把索引当“加速器”不加白不加一张表恨不得建十几二十个索引。这个思路问题很大。索引在带来查询加速的同时会让写入变慢。每插入一行、每更新一个索引列InnoDB 都要同步维护对应的 B 树。索引越多写入路径越长。比如你给一张高频 INSERT 的表加了八个索引那么每次插入不仅写聚簇索引还要写八个二级索引磁盘 IO 和锁竞争都会放大。对更新频繁的字段来说索引列的改动还会触发节点分裂和页合并极端情况下会造成碎片。所以合理的原则是先有真实慢查询再建索引每个索引都要有明确的服务对象。后面我讲 5 种添加方式时也会把这个“按需创建”的思路贯穿进去。2. 加索引之前先把这些索引类型的关系搞明白2.1 按数据结构分普通索引、唯一索引、全文索引从功能上分最基础的是普通索引它只负责加速查找不限制数值重复。最常用的一种默认写的INDEX关键字就是它。唯一索引多了一个约束索引列的值不能重复像用户身份证号、手机号这类业务上天然唯一的字段特别适合建唯一索引。唯一索引除了加速查找还能在数据库层做幂等保护防住应用层并发下重复插入。注意唯一索引允许 NULL而且 NULL 值可以重复出现不要被“唯一”两个字骗了。全文索引针对的是大文本字段的模糊检索比如文章正文。InnoDB 在 5.6 之后支持全文索引但它的底层机制和 B 树完全不同走的是倒排索引。日常业务里如果只是LIKE %关键词%全文索引不是简单的加速器针对这种需求要么上专门的检索方案要么接受全表扫描的结果别指望普通 B 树索引能解决。2.2 按字段组织分单列索引、复合索引、前缀索引按字段个数分单列索引最简单只对一个字段加索引。复合索引是多个字段的组合比如(user_id, status, create_time)这类索引在业务查询里最有价值但也是最容易被用错的核心规则是“最左前缀原则”查询条件必须从复合索引最左边的列开始依次匹配。比如索引是(a, b, c)可以走a 1、a1 AND b2、a1 AND b2 AND c3但查询条件是b 2或c 3时这个索引完全帮不上忙。前缀索引是专门给长字符串准备的。比如存 URL、邮箱、摘要这类字段整个字段建索引会占大量空间而且判断重复率时太长没有意义。MySQL 允许只取字段前 N 个字符建索引像(email(20))。但前缀索引有个限制不能用于覆盖索引扫描因为索引里存的是截断后的值不是完整值回表基本躲不掉。而且前缀长度要经过区分度测试选一个既能筛掉大部分数据、长度又不至于过长的值。2.3 主键索引与回表理解聚簇索引和二级索引这一段可以说是理解整个 InnoDB 索引体系的枢纽。InnoDB 表本身就是按主键聚簇的 B 树。如果你建表时定义了主键那这张表的数据就是按主键排布在聚簇索引的叶子节点上如果没有主键InnoDB 会找一个非空唯一索引代替再没有就生成一个隐藏的 rowid 做主键。你加的每一个二级索引叶子节点存的是“索引列的值 主键值”。查询时如果先走二级索引而 select 列表里有索引列之外的数据就必须拿主键值回到聚簇索引找整行数据这就是回表。回表本身不慢走主键也是索引查找问题在于数据量大时会有大量随机 IO。如果一个查询的所有字段都被二级索引包含就不需要回表这叫覆盖索引。比如查询只涉及user_id和status复合索引(user_id, status)就能直接覆盖Extra 列显示 Using index。设计复合索引时优先考虑覆盖你要查的列往往比单纯匹配 WHERE 条件效果更明显这一点很多开发同学会忽略。2.4 索引类型选择速查表索引类型作用典型场景注意事项普通索引加速查询各种 WHERE、ORDER BY、JOIN 条件任何字段都可以建看区分度唯一索引加速查询 防重复手机号、邮箱、业务单号NULL 可重复复合索引多条件组合查询多字段 WHERE / 排序遵循最左前缀原则前缀索引长字符串加速URL、文件名、摘要需测区分度不可用于覆盖扫描全文索引文本模糊检索文档搜索、文章标题倒排索引不支持中文分词时需额外插件覆盖索引避免回表查询列包含在索引里Extra 为 Using index3. 方式一CREATE INDEX——最标准也最不容易出错的写法3.1 基础语法与三个常用变体第一种添加方式是CREATE INDEX也是 SQL 标准里语义最直观的写法。基础语法很简单CREATE INDEX idx_user_id ON t_order (user_id);如果希望索引列的值不重复用UNIQUE关键字CREATE UNIQUE INDEX uk_user_mobile ON t_user (mobile);多个字段组合在一起就是复合索引CREATE INDEX idx_user_status_time ON t_order (user_id, status, create_time);这三个变体覆盖了日常绝大多数场景。我习惯用的命名规则是idx_表名_字段名或uk_表名_字段名比如idx_order_user、uk_user_mobile。命名这一细节大部分情况下不直接影响性能但在排查问题时一眼能看出索引落在哪张表的哪个字段上能省很多时间。3.2 CREATE INDEX 的注意事项使用CREATE INDEX时有几点需要注意。第一CREATE INDEX一次只能创建一个索引没办法在一条语句里同时加一个普通索引和一个唯一索引它只能创建单个索引单列或复合。不过它是标准的独立语句不需要先写ALTER TABLE也适合在迁移脚本里单独维护一个索引变更。第二创建复合索引时字段顺序很重要。MySQL 选择索引时最关心最左前缀所以你应该把等值条件下区分度高的字段放在左边把范围条件的字段放在后面避免让有范围条件的字段挡住后面字段的索引使用。比如WHERE user_id ? AND status ? AND create_time ?理想的顺序是(user_id, status, create_time)而不是把create_time放前面。第三单独创建索引时不会去判断表上是否已经有等价索引如果已经有idx_user_time(user_id, create_time)你又建一个idx_user_id(user_id)后者基本就是纯冗余。建议执行任何新增索引前先查一下现有索引SHOW INDEX FROM t_order;3.3 实操案例给一个订单表加上复合索引结合前面那个慢查询案例完整的做法应该是CREATE INDEX idx_user_status_time ON t_order (user_id, status, create_time);然后验证EXPLAIN SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;执行计划里能看到 key 变成了idx_user_status_timetype 为 refExtra 里的 Using filesort 没了。因为复合索引的前缀顺序恰好和 ORDER BY 的顺序一致排序可以直接利用索引有序性完成不需要单独 filesort。这也是我在 1.1 里强调“排序字段尽量并进索引”的意义所在。4. 方式二ALTER TABLE ADD INDEX——线上变更的主力方案4.1 语法与内核差异ALTER TABLE 和 CREATE INDEX 的关系第二种添加方式是用ALTER TABLE给表加索引这是我在生产环境里最常用的操作方式。语法是ALTER TABLE t_order ADD INDEX idx_status (status);加唯一索引ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);和CREATE INDEX相比ALTER TABLE的威力在于可以一条语句同时干多件事。比如既加字段、又加索引、还改字段类型一条 SQLALTER TABLE t_order ADD COLUMN remark VARCHAR(200) DEFAULT NULL AFTER status, ADD INDEX idx_user_id (user_id), CHANGE COLUMN status status TINYINT NOT NULL DEFAULT 0;这在脚本迁移里非常有用。而CREATE INDEX只能单独加索引不能和别的表结构变更混在一起。从原理上看CREATE INDEX本质上也是通过改动表结构完成的MySQL 内部会把它转换成和ALTER TABLE ... ADD INDEX类似的操作。两者对线上业务的影响取决于执行时使用的算法。4.2 在线DDL与大表加索引的坑这是我在实际运维里踩过最深的一个坑也是区分“会用”和“会用好”的分水岭。很多资料会说InnoDB 从 5.6 开始支持在线 DDL加索引不会锁表。这个说法容易误导人。准确一点讲像是ADD INDEX这类操作在 MySQL 5.6 之后确实会采用 INPLACE 算法相比 5.5 时代完全 COPY 表的老办法已经有本质改善。但在执行期间DDL 语句必须持有表的元数据锁并且准备阶段和结束阶段都需要短暂的排他锁。如果你的表上有长时间未提交的事务DDL 会被卡在元数据锁等待上即便是 INPLACE也会因为大量写入造成比较大的 IO 压力。我在大表上加索引至少遵守以下几条纪律通过information_schema.innodb_trx检查是否有长事务等事务窗口干净再执行。给 DDL 设置锁等待超时避免无限期被阻塞SET SESSION lock_wait_timeout 30;大表变更尽量放在低峰期并准备回滚预案。表特别大、变更特别频繁的优先考虑第三方在线工具比如 pt-osc、gh-ost它们的原理是新建结构相同的临时表再通过触发器或 binlog 增量同步数据把变更过程拆成可控的小事务。这个后面会在第 7 节展开。4.3 实际订单表添加索引的正确打开方式回到前面的订单表如果表里数据量大且线上有持续写入我推荐像下面这样分步操作先在测试环境模拟-- 检查表大小 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb FROM information_schema.tables WHERE table_schema your_db AND table_name t_order;再评估执行时间。如果表在几百万行以内低峰期直接ALTER TABLE通常可行。执行前一定要先确认没有长事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx;确认没有活跃事务后再执行变更并观察执行耗时和监控。这一步最关键的是“先看事务再动手”比任何索引技巧都重要。别在小表上练完就觉得万事大吉大表的 DDL 是一头完全不同的怪兽。5. 方式三建表时直接定义索引——源头上的“一次到位”5.1 CREATE TABLE 语法里的索引定义第三种方式是在CREATE TABLE建表时直接定义索引这也是唯一在“表还不存在”时规划索引的方式适合新项目、新模块的初始设计。语法示例CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_no VARCHAR(32) NOT NULL COMMENT 用户编号, mobile VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, name VARCHAR(50) NOT NULL COMMENT 姓名, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_no (user_no), UNIQUE KEY uk_mobile (mobile), INDEX idx_status_create_time (status, create_time), INDEX idx_email_prefix (email(32)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里有个容易混淆的点KEY和INDEX在 MySQL 里是同义关键词写KEY还是INDEX效果一样没有区别。而PRIMARY KEY和UNIQUE KEY除了加速查找还承担约束职责。很多同学以为建表时唯一索引和普通索引效果一样这个理解是有偏差的后面细说。5.2 物理设计阶段的索引规划思路建表阶段设计索引核心不是“把能想到的字段都塞进去”而是“根据未来的查询路径倒推”。我的做法是在表结构评审时先把这张表可能出现的各类查询场景列出来。比如用户表最高频的查询可能是“根据手机号登录”“根据用户编号查资料”“按创建时间和状态筛选一批用户”。那么手机号、用户编号的等值查询就应该有唯一索引或普通索引而(status, create_time)这种组合则服务后台列表页的时间范围查询。要克制住“越多越好”的冲动。我在第 1.3 节说过索引有维护成本而建表阶段的索引规划直接决定了未来一段时间的性能基线。建议新表上线后配合慢查询日志跑一到两周再根据真实 SQL 做增量调整。没有必要在一开始就把索引建得满满当当。5.3 唯一约束和普通索引的区别用代码来说明最直观。假如某张表要求业务单号不能重复你在建表时写了UNIQUE KEY uk_order_no (order_no)那么当第二条 SQL 插入重复的 order_no 时数据库直接报错ERROR 1062 (23000): Duplicate entry ORD20240101001 for key t_order.uk_order_no如果建的只是普通索引数据库不会拦截重复值重复数据只能靠应用层自己保证。在高并发场景下应用层的“先查再插入”大概率会出问题因为两个请求同时查到「不存在」然后同时插入等建立唯一约束已经来不及了。所以凡是业务上必须唯一的字段都应该直接上唯一索引这是一种成本极低、收益极高的防重复手段。另外提醒一下主键本身就是唯一索引不要再为主键字段单独建一个唯一索引或普通索引那是纯冗余操作——聚簇索引已经覆盖了它。6. 方式四可视化工具添加索引——快速验证的首选6.1 常用工具的索引管理入口第四种方式是借助可视化数据库客户端比如 Navicat、DBeaver 这类工具在图形界面上点选添加索引。对不熟悉命令行的同学来说这是一种入门成本最低的方式。以 Navicat 为例右键选择表进入“设计表”切到“索引”面板点“添加索引”填写索引名勾选字段选择索引类型普通、唯一、全文保存即可。MySQL Workbench 也有类似入口步骤基本一致。部分工具还会帮你可视化判断索引类型和字段顺序对新手比较友好。如果是 DBeaver双击表名弹出表编辑器在其中找到“索引/键”或“属性”页签就能维护索引。6.2 工具背后的SQL让GUI干的是安全活用 GUI 点保存后台生成的 SQL 无外乎就是我前面说的ALTER TABLE或CREATE INDEX。工具不过是把 SQL 的输入过程图形化了。这里我要特别提醒一个坑有些 GUI 工具对表结构的“图形化修改”并不是只执行你选择的那条变更它会读出整张表结构生成一段重建表的 DDL然后在“设计表”的弹窗里一次性提交。比如你只是想加一个索引但它可能把整张表删掉再重建取决于工具版本和连接设置。在线上大表上用这种方式操作非常危险一旦工具生成了DROP TABLECREATE TABLE的流程数据安全完全不可控。所以我的建议是生产环境用 GUI 前先查一下工具生成的 SQL能看到它到底提交了什么语句。在一些数据库管理平台上可以开启“执行前显示 SQL”的选项。你肉眼确认过是单纯的ALTER TABLE ... ADD INDEX再执行。6.3 可视化操作的边界哪些场景别用工具对比前几种方式GUI 最大的问题是操作可追溯性差。手敲 SQL 可以写进版本管理、走变更审批、留存审计记录GUI 点几下往往只有数据库日志里有一串操作记录出了问题很难复盘。我的经验是GUI 适合在开发库、测试库做快速验证比如临时验证一个索引对某个 SQL 有没有效果。生产环境的索引变更即使是小表也建议写成脚本走流程。不要图省事直接在线上用 GUI 改表这是一个资深同学和新手在操作习惯上最明显的差别。7. 方式五动态SQL与脚本批量添加——多环境一致性维护的工程化手段7.1 先查后建基于 information_schema 的幂等脚本第五种方式更偏工程化用动态 SQL 或脚本批量添加索引。典型场景是你要维护多套环境开发、测试、预发、生产的数据库结构一致性或者要在一批表上做同类型规范调整。如果靠人工每张表敲一遍ALTER TABLE效率低、容易漏而且重复执行会报索引已存在的错误。解决办法是先查询信息模式表判断索引是否存在存在就跳过不存在就执行创建。MySQL 的information_schema.statistics表记录了所有索引信息可以这样检查SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema DATABASE() AND table_name t_order AND index_name idx_user_status_time;如果计数为 0就执行建索引否则跳过。把这一步放进脚本里整个添加索引的流程就变成幂等的可以反复执行而不会报错。7.2 一个可复用的存储过程/批处理示例下面给一个实用的示例。以存储过程的形式给目标表的指定字段添加索引并做存在性判断DELIMITER // CREATE PROCEDURE add_index_if_not_exists( IN p_table VARCHAR(64), IN p_index VARCHAR(64), IN p_columns VARCHAR(255) ) BEGIN DECLARE idx_count INT DEFAULT 0; SELECT COUNT(*) INTO idx_count FROM information_schema.statistics WHERE table_schema DATABASE() AND table_name p_table AND index_name p_index; IF idx_count 0 THEN SET ddl_sql CONCAT( ALTER TABLE , p_table, ADD INDEX , p_index, (, p_columns, ); ); PREPARE stmt FROM ddl_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT CONCAT(index , p_index, added) AS message; ELSE SELECT CONCAT(index , p_index, already exists) AS message; END IF; END // DELIMITER ;调用方式CALL add_index_if_not_exists(t_order, idx_user_status_time, user_id, status, create_time);执行两次第二次会直接提示already exists不会报错。这个模式特别适合发布流程里的结构迁移脚本也适合把索引变更纳进标准 SQL 文件统一走变更审批。如果你更习惯用 shell 脚本也可以循环读表名拼出ALTER TABLE语句批量执行但建议每次先打印 SQL再逐条执行避免一把梭把线上 IO 打满。7.3 第三方在线工具与灰度发布经验大表场景下第七节提到的 pt-osc、gh-ost 这类第三方工具其实是“动态批量加索引”思路的更大型实现。它们不直接在原表上执行 DDL而是通过触发器或 binlog 同步把新索引逐步应用到一个影子表上等数据同步完成后再切换表名。这样做的好处是变更期间原表基本不受影响可以持续提供读写服务。在使用这类工具时需要有完善的流程先备份元数据、设定好 chunk 大小和并发线程数、确认 binlog 开启、跑完检查数据一致最后再切换。不熟悉底层机制的人贸然使用反而可能把简单问题复杂化所以中小表完全没必要上这类工具直接走普通 DDL 就好。工具是给大表场景准备的这是一个“按需选择”的原则。8. 索引加上去了为什么查询还是慢验证与避坑8.1 用 EXPLAIN 验证索引是否生效前面所有方式讲完最后一个绕不开的问题是索引到底加对了没有最直接的办法是看执行计划。EXPLAIN SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;重点看这几列列名说明期望值type访问类型至少要达到 ref 或 rangeALL 说明全表扫描key实际使用的索引显示你预期命中的索引名rows预估扫描行数越小越好和表总行数差距越大说明索引越有效Extra额外信息出现 Using filesort 说明排序没走索引Using index 则表示覆盖索引有一个很容易误判的地方你以为 MySQL 会用某个索引结果它选了另一条。优化器会综合统计信息、区分度、回表代价来决定。如果统计信息不准或者是ANALYZE TABLE没更新它可能宁可全表扫。这时候先ANALYZE TABLE t_order;刷新统计信息再重新看执行计划很多时候问题就消失了。8.2 最常见的六种索引失效场景我在实际排查里反复见到的索引失效场景基本可以归纳成下面六类第一对索引列使用函数或计算。比如WHERE DATE(create_time) 2024-01-01索引列套了函数B 树的有序性被破坏。正确写法是范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02第二隐式类型转换。字符串字段和数字比较比如mobile是 VARCHAR条件写成WHERE mobile 13812345678MySQL 会把字段转成数字再比较索引失效。最好保持字段类型和值的类型完全一致。第三前导模糊查询。LIKE %keywords%无法利用索引有序性因为不确定匹配的起点。LIKE keywords%则可以走索引范围扫描。第四复合索引不满足最左前缀。我在第 2.2 节讲过这里再强调一遍INDEX idx_abc(a, b, c)不会因为WHERE b 1 AND c 2起效。碰到这种 SQL要么调整索引顺序要么补充新的索引。第五OR 条件。WHERE user_id 123 OR status 1这类语句即使两边各自有索引MySQL 也可能需要做索引合并甚至全表扫描。通常能拆成两个查询再合并或者用 UNION ALL效果更可控。第六优化器认为全表更快。表很小、索引区分度差比如一个字段只有两种值优化器扫描一遍的开销比走索引加回表还小它会主动放弃索引。这不算配置错误但你需要知道“加索引”不等于“强制走索引”。真要强制可以用FORCE INDEX但这属于特例手段不建议常规使用。8.3 冗余索引与未使用索引定期体检最后分享一个习惯每隔一段时间我会把所有表的索引拉出来体检一遍。先看冗余索引。比如表上有idx_user_id(user_id)和idx_user_time(user_id, create_time)前者的最左列已经被后者覆盖前者大概率可以删除。用SHOW INDEX FROM t_order;拉出来过一遍特别留意那些前缀重复的索引。再看未使用索引。MySQL 的performance_schema有表级和索引级的统计信息配合sys.schema_unused_indexes可以直观查到哪些索引从未被使用过SELECT * FROM sys.schema_unused_indexes;查出来的候选索引再结合真实业务场景确认才删不要因为线上长期没用到就直接清理。删除索引前我会在备份环境跑几遍代表性 SQL确认执行计划不受影响。索引不是越多越好也不是建完就一劳永逸。把它看成和数据一样需要持续维护的对象定期检查、合理规划、按真实查询路径补充才是保障 MySQL 性能长期稳定的关键。实际操作中我最常重复的建议就一条每次加索引前都问自己一句“它的存在能让哪条慢 SQL 变快”回答不上来就先别加。 
长河君 · 2026/10/14 22:02:56 · 阅读 1.2k MySQL添加索引的5种方式：从慢查询到执行计划，彻底搞定索引优化
上周帮某开发者排查一个报表接口的慢查询筛选三个月的数据要跑八秒多前端一直在转圈。看了一圈SQL无非就是几条 WHERE 条件加上 ORDER BY表里也就几十万行问题几乎一眼就能锁定——索引没建到位。当时补上一个复合索引查询时间直接掉到几十毫秒整个接口体感完全两样。这种“一条索引救一条SQL”的例子在绝大多数业务系统里每天都在发生。但我在日常帮人看库的过程中发现很多同学对 MySQL 添加索引这件事的理解停留在“会写 CREATE INDEX 就行”的层面实际工作中经常会遇到索引加了但没走、重复建了一堆冗余索引、大表加索引锁断了业务甚至分不清唯一索引和普通索引的区别。这篇主要把 MySQL 添加索引这件事拆开讲清楚尤其是 5 种不同的添加方式各自的适用场景、执行细节和容易踩的坑。内容按实操向展开适合刚接触索引的初学者也适合已经有工作经验的开发或 DBA 拿来对照自查。1. 一条慢SQL引发的思考索引本质上是给数据“排好队”1.1 那次排查索引缺失的典型症状先说回那个订单查询的场景。原始 SQL 大致长这样SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;表里数据量接近六十万行但查询要走全表扫描。执行计划里的 type 是 ALL扫描行数是 578324Extra 列里还有 Using filesort。全表扫一遍加外排八秒多并不意外。加索引之后就两句话的事ALTER TABLE t_order ADD INDEX idx_user_status_time (user_id, status, create_time);再看执行计划type 从 ALL 变成了 ref扫描行数从 57 万掉到 120 多行Using filesort 也消失了查询耗时降到 40ms 左右。这个例子其实特别典型它同时踩了三个常见问题WHERE 条件没有可用索引、排序字段没有涵盖在索引里、数据量大到全表扫描的代价无法接受。后面我会把每个点拆开细讲。1.2 索引为什么能快B树与“顺序查找”的逻辑理解 MySQL 的 InnoDB 索引最核心的是要理解 B 树。我打个比方假设你用一本没有书签、没有目录的小说找一句话唯一的办法就是从第一页开始翻这叫全表扫描。如果目录就是 B 树树的高度一般只有三四层你顺着目录查过去经过的节点数量极少很快就能定位到对应页码这就是索引查找。B 树和普通二叉树的区别在于它每个节点可以存很多键值叶子节点存真正的数据而且叶子节点之间通过指针连成了有序链表。这意味着它既能做精确查询也能做高效的范围查询。比如上面那个 SQL 里的create_time 2024-01-01B 树从第一满足条件的位置开始顺着链表往后扫就行了不用再回根节点重新查。InnoDB 的主键索引本身就是一棵聚簇索引树叶子节点直接存整行数据。而二级索引树的叶子节点存的是索引列的值和对应的主键值。所以走二级索引查询时如果 select 的列不能全部在二级索引里找到就需要用主键值回聚簇索引取整行数据这个动作叫回表。能用“覆盖索引”避免回表是后期性能优化里性价比极高的一招后面会专门提到。1.3 索引不是越多越好索引也有维护成本很多同学把索引当“加速器”不加白不加一张表恨不得建十几二十个索引。这个思路问题很大。索引在带来查询加速的同时会让写入变慢。每插入一行、每更新一个索引列InnoDB 都要同步维护对应的 B 树。索引越多写入路径越长。比如你给一张高频 INSERT 的表加了八个索引那么每次插入不仅写聚簇索引还要写八个二级索引磁盘 IO 和锁竞争都会放大。对更新频繁的字段来说索引列的改动还会触发节点分裂和页合并极端情况下会造成碎片。所以合理的原则是先有真实慢查询再建索引每个索引都要有明确的服务对象。后面我讲 5 种添加方式时也会把这个“按需创建”的思路贯穿进去。2. 加索引之前先把这些索引类型的关系搞明白2.1 按数据结构分普通索引、唯一索引、全文索引从功能上分最基础的是普通索引它只负责加速查找不限制数值重复。最常用的一种默认写的INDEX关键字就是它。唯一索引多了一个约束索引列的值不能重复像用户身份证号、手机号这类业务上天然唯一的字段特别适合建唯一索引。唯一索引除了加速查找还能在数据库层做幂等保护防住应用层并发下重复插入。注意唯一索引允许 NULL而且 NULL 值可以重复出现不要被“唯一”两个字骗了。全文索引针对的是大文本字段的模糊检索比如文章正文。InnoDB 在 5.6 之后支持全文索引但它的底层机制和 B 树完全不同走的是倒排索引。日常业务里如果只是LIKE %关键词%全文索引不是简单的加速器针对这种需求要么上专门的检索方案要么接受全表扫描的结果别指望普通 B 树索引能解决。2.2 按字段组织分单列索引、复合索引、前缀索引按字段个数分单列索引最简单只对一个字段加索引。复合索引是多个字段的组合比如(user_id, status, create_time)这类索引在业务查询里最有价值但也是最容易被用错的核心规则是“最左前缀原则”查询条件必须从复合索引最左边的列开始依次匹配。比如索引是(a, b, c)可以走a 1、a1 AND b2、a1 AND b2 AND c3但查询条件是b 2或c 3时这个索引完全帮不上忙。前缀索引是专门给长字符串准备的。比如存 URL、邮箱、摘要这类字段整个字段建索引会占大量空间而且判断重复率时太长没有意义。MySQL 允许只取字段前 N 个字符建索引像(email(20))。但前缀索引有个限制不能用于覆盖索引扫描因为索引里存的是截断后的值不是完整值回表基本躲不掉。而且前缀长度要经过区分度测试选一个既能筛掉大部分数据、长度又不至于过长的值。2.3 主键索引与回表理解聚簇索引和二级索引这一段可以说是理解整个 InnoDB 索引体系的枢纽。InnoDB 表本身就是按主键聚簇的 B 树。如果你建表时定义了主键那这张表的数据就是按主键排布在聚簇索引的叶子节点上如果没有主键InnoDB 会找一个非空唯一索引代替再没有就生成一个隐藏的 rowid 做主键。你加的每一个二级索引叶子节点存的是“索引列的值 主键值”。查询时如果先走二级索引而 select 列表里有索引列之外的数据就必须拿主键值回到聚簇索引找整行数据这就是回表。回表本身不慢走主键也是索引查找问题在于数据量大时会有大量随机 IO。如果一个查询的所有字段都被二级索引包含就不需要回表这叫覆盖索引。比如查询只涉及user_id和status复合索引(user_id, status)就能直接覆盖Extra 列显示 Using index。设计复合索引时优先考虑覆盖你要查的列往往比单纯匹配 WHERE 条件效果更明显这一点很多开发同学会忽略。2.4 索引类型选择速查表索引类型作用典型场景注意事项普通索引加速查询各种 WHERE、ORDER BY、JOIN 条件任何字段都可以建看区分度唯一索引加速查询 防重复手机号、邮箱、业务单号NULL 可重复复合索引多条件组合查询多字段 WHERE / 排序遵循最左前缀原则前缀索引长字符串加速URL、文件名、摘要需测区分度不可用于覆盖扫描全文索引文本模糊检索文档搜索、文章标题倒排索引不支持中文分词时需额外插件覆盖索引避免回表查询列包含在索引里Extra 为 Using index3. 方式一CREATE INDEX——最标准也最不容易出错的写法3.1 基础语法与三个常用变体第一种添加方式是CREATE INDEX也是 SQL 标准里语义最直观的写法。基础语法很简单CREATE INDEX idx_user_id ON t_order (user_id);如果希望索引列的值不重复用UNIQUE关键字CREATE UNIQUE INDEX uk_user_mobile ON t_user (mobile);多个字段组合在一起就是复合索引CREATE INDEX idx_user_status_time ON t_order (user_id, status, create_time);这三个变体覆盖了日常绝大多数场景。我习惯用的命名规则是idx_表名_字段名或uk_表名_字段名比如idx_order_user、uk_user_mobile。命名这一细节大部分情况下不直接影响性能但在排查问题时一眼能看出索引落在哪张表的哪个字段上能省很多时间。3.2 CREATE INDEX 的注意事项使用CREATE INDEX时有几点需要注意。第一CREATE INDEX一次只能创建一个索引没办法在一条语句里同时加一个普通索引和一个唯一索引它只能创建单个索引单列或复合。不过它是标准的独立语句不需要先写ALTER TABLE也适合在迁移脚本里单独维护一个索引变更。第二创建复合索引时字段顺序很重要。MySQL 选择索引时最关心最左前缀所以你应该把等值条件下区分度高的字段放在左边把范围条件的字段放在后面避免让有范围条件的字段挡住后面字段的索引使用。比如WHERE user_id ? AND status ? AND create_time ?理想的顺序是(user_id, status, create_time)而不是把create_time放前面。第三单独创建索引时不会去判断表上是否已经有等价索引如果已经有idx_user_time(user_id, create_time)你又建一个idx_user_id(user_id)后者基本就是纯冗余。建议执行任何新增索引前先查一下现有索引SHOW INDEX FROM t_order;3.3 实操案例给一个订单表加上复合索引结合前面那个慢查询案例完整的做法应该是CREATE INDEX idx_user_status_time ON t_order (user_id, status, create_time);然后验证EXPLAIN SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;执行计划里能看到 key 变成了idx_user_status_timetype 为 refExtra 里的 Using filesort 没了。因为复合索引的前缀顺序恰好和 ORDER BY 的顺序一致排序可以直接利用索引有序性完成不需要单独 filesort。这也是我在 1.1 里强调“排序字段尽量并进索引”的意义所在。4. 方式二ALTER TABLE ADD INDEX——线上变更的主力方案4.1 语法与内核差异ALTER TABLE 和 CREATE INDEX 的关系第二种添加方式是用ALTER TABLE给表加索引这是我在生产环境里最常用的操作方式。语法是ALTER TABLE t_order ADD INDEX idx_status (status);加唯一索引ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);和CREATE INDEX相比ALTER TABLE的威力在于可以一条语句同时干多件事。比如既加字段、又加索引、还改字段类型一条 SQLALTER TABLE t_order ADD COLUMN remark VARCHAR(200) DEFAULT NULL AFTER status, ADD INDEX idx_user_id (user_id), CHANGE COLUMN status status TINYINT NOT NULL DEFAULT 0;这在脚本迁移里非常有用。而CREATE INDEX只能单独加索引不能和别的表结构变更混在一起。从原理上看CREATE INDEX本质上也是通过改动表结构完成的MySQL 内部会把它转换成和ALTER TABLE ... ADD INDEX类似的操作。两者对线上业务的影响取决于执行时使用的算法。4.2 在线DDL与大表加索引的坑这是我在实际运维里踩过最深的一个坑也是区分“会用”和“会用好”的分水岭。很多资料会说InnoDB 从 5.6 开始支持在线 DDL加索引不会锁表。这个说法容易误导人。准确一点讲像是ADD INDEX这类操作在 MySQL 5.6 之后确实会采用 INPLACE 算法相比 5.5 时代完全 COPY 表的老办法已经有本质改善。但在执行期间DDL 语句必须持有表的元数据锁并且准备阶段和结束阶段都需要短暂的排他锁。如果你的表上有长时间未提交的事务DDL 会被卡在元数据锁等待上即便是 INPLACE也会因为大量写入造成比较大的 IO 压力。我在大表上加索引至少遵守以下几条纪律通过information_schema.innodb_trx检查是否有长事务等事务窗口干净再执行。给 DDL 设置锁等待超时避免无限期被阻塞SET SESSION lock_wait_timeout 30;大表变更尽量放在低峰期并准备回滚预案。表特别大、变更特别频繁的优先考虑第三方在线工具比如 pt-osc、gh-ost它们的原理是新建结构相同的临时表再通过触发器或 binlog 增量同步数据把变更过程拆成可控的小事务。这个后面会在第 7 节展开。4.3 实际订单表添加索引的正确打开方式回到前面的订单表如果表里数据量大且线上有持续写入我推荐像下面这样分步操作先在测试环境模拟-- 检查表大小 SELECT table_name, table_rows, ROUND(data_length / 1024 / 1024, 2) AS data_mb FROM information_schema.tables WHERE table_schema your_db AND table_name t_order;再评估执行时间。如果表在几百万行以内低峰期直接ALTER TABLE通常可行。执行前一定要先确认没有长事务SELECT trx_id, trx_state, trx_started, trx_mysql_thread_id FROM information_schema.innodb_trx;确认没有活跃事务后再执行变更并观察执行耗时和监控。这一步最关键的是“先看事务再动手”比任何索引技巧都重要。别在小表上练完就觉得万事大吉大表的 DDL 是一头完全不同的怪兽。5. 方式三建表时直接定义索引——源头上的“一次到位”5.1 CREATE TABLE 语法里的索引定义第三种方式是在CREATE TABLE建表时直接定义索引这也是唯一在“表还不存在”时规划索引的方式适合新项目、新模块的初始设计。语法示例CREATE TABLE t_user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_no VARCHAR(32) NOT NULL COMMENT 用户编号, mobile VARCHAR(20) NOT NULL COMMENT 手机号, email VARCHAR(128) DEFAULT NULL COMMENT 邮箱, name VARCHAR(50) NOT NULL COMMENT 姓名, status TINYINT NOT NULL DEFAULT 0, create_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uk_user_no (user_no), UNIQUE KEY uk_mobile (mobile), INDEX idx_status_create_time (status, create_time), INDEX idx_email_prefix (email(32)) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4 COMMENT用户表;这里有个容易混淆的点KEY和INDEX在 MySQL 里是同义关键词写KEY还是INDEX效果一样没有区别。而PRIMARY KEY和UNIQUE KEY除了加速查找还承担约束职责。很多同学以为建表时唯一索引和普通索引效果一样这个理解是有偏差的后面细说。5.2 物理设计阶段的索引规划思路建表阶段设计索引核心不是“把能想到的字段都塞进去”而是“根据未来的查询路径倒推”。我的做法是在表结构评审时先把这张表可能出现的各类查询场景列出来。比如用户表最高频的查询可能是“根据手机号登录”“根据用户编号查资料”“按创建时间和状态筛选一批用户”。那么手机号、用户编号的等值查询就应该有唯一索引或普通索引而(status, create_time)这种组合则服务后台列表页的时间范围查询。要克制住“越多越好”的冲动。我在第 1.3 节说过索引有维护成本而建表阶段的索引规划直接决定了未来一段时间的性能基线。建议新表上线后配合慢查询日志跑一到两周再根据真实 SQL 做增量调整。没有必要在一开始就把索引建得满满当当。5.3 唯一约束和普通索引的区别用代码来说明最直观。假如某张表要求业务单号不能重复你在建表时写了UNIQUE KEY uk_order_no (order_no)那么当第二条 SQL 插入重复的 order_no 时数据库直接报错ERROR 1062 (23000): Duplicate entry ORD20240101001 for key t_order.uk_order_no如果建的只是普通索引数据库不会拦截重复值重复数据只能靠应用层自己保证。在高并发场景下应用层的“先查再插入”大概率会出问题因为两个请求同时查到「不存在」然后同时插入等建立唯一约束已经来不及了。所以凡是业务上必须唯一的字段都应该直接上唯一索引这是一种成本极低、收益极高的防重复手段。另外提醒一下主键本身就是唯一索引不要再为主键字段单独建一个唯一索引或普通索引那是纯冗余操作——聚簇索引已经覆盖了它。6. 方式四可视化工具添加索引——快速验证的首选6.1 常用工具的索引管理入口第四种方式是借助可视化数据库客户端比如 Navicat、DBeaver 这类工具在图形界面上点选添加索引。对不熟悉命令行的同学来说这是一种入门成本最低的方式。以 Navicat 为例右键选择表进入“设计表”切到“索引”面板点“添加索引”填写索引名勾选字段选择索引类型普通、唯一、全文保存即可。MySQL Workbench 也有类似入口步骤基本一致。部分工具还会帮你可视化判断索引类型和字段顺序对新手比较友好。如果是 DBeaver双击表名弹出表编辑器在其中找到“索引/键”或“属性”页签就能维护索引。6.2 工具背后的SQL让GUI干的是安全活用 GUI 点保存后台生成的 SQL 无外乎就是我前面说的ALTER TABLE或CREATE INDEX。工具不过是把 SQL 的输入过程图形化了。这里我要特别提醒一个坑有些 GUI 工具对表结构的“图形化修改”并不是只执行你选择的那条变更它会读出整张表结构生成一段重建表的 DDL然后在“设计表”的弹窗里一次性提交。比如你只是想加一个索引但它可能把整张表删掉再重建取决于工具版本和连接设置。在线上大表上用这种方式操作非常危险一旦工具生成了DROP TABLECREATE TABLE的流程数据安全完全不可控。所以我的建议是生产环境用 GUI 前先查一下工具生成的 SQL能看到它到底提交了什么语句。在一些数据库管理平台上可以开启“执行前显示 SQL”的选项。你肉眼确认过是单纯的ALTER TABLE ... ADD INDEX再执行。6.3 可视化操作的边界哪些场景别用工具对比前几种方式GUI 最大的问题是操作可追溯性差。手敲 SQL 可以写进版本管理、走变更审批、留存审计记录GUI 点几下往往只有数据库日志里有一串操作记录出了问题很难复盘。我的经验是GUI 适合在开发库、测试库做快速验证比如临时验证一个索引对某个 SQL 有没有效果。生产环境的索引变更即使是小表也建议写成脚本走流程。不要图省事直接在线上用 GUI 改表这是一个资深同学和新手在操作习惯上最明显的差别。7. 方式五动态SQL与脚本批量添加——多环境一致性维护的工程化手段7.1 先查后建基于 information_schema 的幂等脚本第五种方式更偏工程化用动态 SQL 或脚本批量添加索引。典型场景是你要维护多套环境开发、测试、预发、生产的数据库结构一致性或者要在一批表上做同类型规范调整。如果靠人工每张表敲一遍ALTER TABLE效率低、容易漏而且重复执行会报索引已存在的错误。解决办法是先查询信息模式表判断索引是否存在存在就跳过不存在就执行创建。MySQL 的information_schema.statistics表记录了所有索引信息可以这样检查SELECT COUNT(*) FROM information_schema.statistics WHERE table_schema DATABASE() AND table_name t_order AND index_name idx_user_status_time;如果计数为 0就执行建索引否则跳过。把这一步放进脚本里整个添加索引的流程就变成幂等的可以反复执行而不会报错。7.2 一个可复用的存储过程/批处理示例下面给一个实用的示例。以存储过程的形式给目标表的指定字段添加索引并做存在性判断DELIMITER // CREATE PROCEDURE add_index_if_not_exists( IN p_table VARCHAR(64), IN p_index VARCHAR(64), IN p_columns VARCHAR(255) ) BEGIN DECLARE idx_count INT DEFAULT 0; SELECT COUNT(*) INTO idx_count FROM information_schema.statistics WHERE table_schema DATABASE() AND table_name p_table AND index_name p_index; IF idx_count 0 THEN SET ddl_sql CONCAT( ALTER TABLE , p_table, ADD INDEX , p_index, (, p_columns, ); ); PREPARE stmt FROM ddl_sql; EXECUTE stmt; DEALLOCATE PREPARE stmt; SELECT CONCAT(index , p_index, added) AS message; ELSE SELECT CONCAT(index , p_index, already exists) AS message; END IF; END // DELIMITER ;调用方式CALL add_index_if_not_exists(t_order, idx_user_status_time, user_id, status, create_time);执行两次第二次会直接提示already exists不会报错。这个模式特别适合发布流程里的结构迁移脚本也适合把索引变更纳进标准 SQL 文件统一走变更审批。如果你更习惯用 shell 脚本也可以循环读表名拼出ALTER TABLE语句批量执行但建议每次先打印 SQL再逐条执行避免一把梭把线上 IO 打满。7.3 第三方在线工具与灰度发布经验大表场景下第七节提到的 pt-osc、gh-ost 这类第三方工具其实是“动态批量加索引”思路的更大型实现。它们不直接在原表上执行 DDL而是通过触发器或 binlog 同步把新索引逐步应用到一个影子表上等数据同步完成后再切换表名。这样做的好处是变更期间原表基本不受影响可以持续提供读写服务。在使用这类工具时需要有完善的流程先备份元数据、设定好 chunk 大小和并发线程数、确认 binlog 开启、跑完检查数据一致最后再切换。不熟悉底层机制的人贸然使用反而可能把简单问题复杂化所以中小表完全没必要上这类工具直接走普通 DDL 就好。工具是给大表场景准备的这是一个“按需选择”的原则。8. 索引加上去了为什么查询还是慢验证与避坑8.1 用 EXPLAIN 验证索引是否生效前面所有方式讲完最后一个绕不开的问题是索引到底加对了没有最直接的办法是看执行计划。EXPLAIN SELECT id, order_no, user_id, amount, status FROM t_order WHERE user_id 12345 AND status 1 AND create_time 2024-01-01 ORDER BY create_time DESC LIMIT 20;重点看这几列列名说明期望值type访问类型至少要达到 ref 或 rangeALL 说明全表扫描key实际使用的索引显示你预期命中的索引名rows预估扫描行数越小越好和表总行数差距越大说明索引越有效Extra额外信息出现 Using filesort 说明排序没走索引Using index 则表示覆盖索引有一个很容易误判的地方你以为 MySQL 会用某个索引结果它选了另一条。优化器会综合统计信息、区分度、回表代价来决定。如果统计信息不准或者是ANALYZE TABLE没更新它可能宁可全表扫。这时候先ANALYZE TABLE t_order;刷新统计信息再重新看执行计划很多时候问题就消失了。8.2 最常见的六种索引失效场景我在实际排查里反复见到的索引失效场景基本可以归纳成下面六类第一对索引列使用函数或计算。比如WHERE DATE(create_time) 2024-01-01索引列套了函数B 树的有序性被破坏。正确写法是范围查询WHERE create_time 2024-01-01 AND create_time 2024-01-02第二隐式类型转换。字符串字段和数字比较比如mobile是 VARCHAR条件写成WHERE mobile 13812345678MySQL 会把字段转成数字再比较索引失效。最好保持字段类型和值的类型完全一致。第三前导模糊查询。LIKE %keywords%无法利用索引有序性因为不确定匹配的起点。LIKE keywords%则可以走索引范围扫描。第四复合索引不满足最左前缀。我在第 2.2 节讲过这里再强调一遍INDEX idx_abc(a, b, c)不会因为WHERE b 1 AND c 2起效。碰到这种 SQL要么调整索引顺序要么补充新的索引。第五OR 条件。WHERE user_id 123 OR status 1这类语句即使两边各自有索引MySQL 也可能需要做索引合并甚至全表扫描。通常能拆成两个查询再合并或者用 UNION ALL效果更可控。第六优化器认为全表更快。表很小、索引区分度差比如一个字段只有两种值优化器扫描一遍的开销比走索引加回表还小它会主动放弃索引。这不算配置错误但你需要知道“加索引”不等于“强制走索引”。真要强制可以用FORCE INDEX但这属于特例手段不建议常规使用。8.3 冗余索引与未使用索引定期体检最后分享一个习惯每隔一段时间我会把所有表的索引拉出来体检一遍。先看冗余索引。比如表上有idx_user_id(user_id)和idx_user_time(user_id, create_time)前者的最左列已经被后者覆盖前者大概率可以删除。用SHOW INDEX FROM t_order;拉出来过一遍特别留意那些前缀重复的索引。再看未使用索引。MySQL 的performance_schema有表级和索引级的统计信息配合sys.schema_unused_indexes可以直观查到哪些索引从未被使用过SELECT * FROM sys.schema_unused_indexes;查出来的候选索引再结合真实业务场景确认才删不要因为线上长期没用到就直接清理。删除索引前我会在备份环境跑几遍代表性 SQL确认执行计划不受影响。索引不是越多越好也不是建完就一劳永逸。把它看成和数据一样需要持续维护的对象定期检查、合理规划、按真实查询路径补充才是保障 MySQL 性能长期稳定的关键。实际操作中我最常重复的建议就一条每次加索引前都问自己一句“它的存在能让哪条慢 SQL 变快”回答不上来就先别加。