MySQL索引优化实战：从B+树原理到创建删除的完整指南

1. 为什么索引值得认真对待先说个我自己的感受。刚入行那两年我把索引当成“数据库的加分项”表建完、数据能查出来就完事了索引随手建一两个。直到有一次线上订单表数据量飙到千万级一条 where 条件带上create_time的查询要跑 4 秒多接口超时告警一个接一个我才不得不坐下来认认真真研究索引的底层逻辑。那次教训之后我把索引的创建、删除、优化当成 MySQL 的必修课每个生产环境变更都变得格外小心。索引说白了就是 MySQL 为了加速数据检索而维护的一套额外数据结构。没有索引的时候WHERE 条件筛选等于“全表扫描”每一行都要过一遍有了索引之后MySQL 可以像查字典一样先到索引结构里定位再回表取数据。数据量小的时候这个差距不明显一旦超过百万级差距就是几十倍甚至上百倍。索引不只是所谓的“性能优化手段”它直接决定了你的业务能不能跑得动。这篇文章我不会只停留在“CREATE INDEX 怎么写”这个层面而是把索引用起来之后的整个逻辑链条捋清楚为什么普通索引能快、聚簇索引和二级索引有什么区别、创建索引时要避开哪些会让你追悔莫及的坑、删除索引时又有什么看不见的副作用。无论你是刚学会建表的初级开发者还是正在维护一个慢查询频发的业务系统这篇文章的实操内容和经验总结都应该能派上用场。2. 索引的底层逻辑只有理解了数据结构才知道该怎么建2.1 B树为什么能支撑千万级数据的快速检索要搞懂索引的创建和删除先得知道索引底层长什么样。MySQL InnoDB 引擎的索引结构是 B 树。B 树是一种多路平衡查找树它不同于常见的二叉树每个节点可以存储多个键值而且叶子节点之间通过链表相连。用一个生活化的类比来说明全表扫描就像在一本没有目录的书里找某个关键词你必须一页页翻过去而 B 树索引相当于这本书最后附了一个多层次的目录先找到章再定位到节然后顺着页码直接翻到目标页。B 树的高度一般只有 3 到 4 层也就是说即使表里有上千万条记录定位一条数据也只需要几次磁盘 IO 就能完成而全表扫描要读上千个数据页。B 树相比其他数据结构有几个关键优势第一非叶子节点只存储索引键不存储数据这意味着一个数据页能装下的索引项非常多树的高度得以降低IO 次数随之减少第二叶子节点有序排列且有链表相连这让范围查询比如 BETWEEN、大于小于变得极其高效找到起点之后顺着叶子链表一路往后读就行第三所有数据都在叶子节点查询性能非常稳定不会像某些数据结构那样出现大幅度的性能抖动。哈希索引虽然能在等值查询上做到 O(1) 复杂度但它对范围查询无能为力这也是 InnoDB 默认使用 B 树而不是哈希表的核心原因。2.2 聚簇索引、二级索引和回表三个绕不开的概念InnoDB 的表其实就是一棵 B 树数据是“物理地”存储在聚簇索引的叶子节点上的。聚簇索引通常就是主键索引表里每一行完整的数据行都挂在主键 B 树的叶子节点上。因为数据行只能有一份物理存储所以一个表只能有一个聚簇索引。二级索引也就是我们平时手动创建的那些普通索引则完全不同。它的叶子节点存储的是索引列的值加上主键值。这句话非常关键——二级索引的叶子节点并不包含完整数据行只包含“索引字段”和“主键”这两个东西。当你通过二级索引查询数据时MySQL 会先到二级索引的 B 树里找到符合条件的记录得到主键值然后再拿着主键值到聚簇索引里查完整数据行这个步骤就叫回表。举个具体例子。假设订单表orders上有主键id还有一个普通索引idx_user_id在user_id列上。执行SELECT * FROM orders WHERE user_id 123时MySQL 会先去idx_user_id这棵 B 树里找到user_id123的记录拿到对应的主键 id再回到主键索引树里读取整行数据。这个过程中的两次查找就是回表的由来。如果查询语句只需要user_id和id两个字段也就是索引里本身已经有的列MySQL 就不需要回表这种情况叫覆盖索引查询效率会高很多。搞清楚这个逻辑你才能理解创建索引时为什么通常建议把查询频率最高的列作为索引前缀为什么不要动不动就SELECT *也才能理解为什么“索引不是越多越好”——因为每一棵二级索引 B 树都要占用磁盘空间而且每次 INSERT、UPDATE、DELETE 操作都要同步维护这些索引树索引数量越多写入成本就越高。2.3 创建索引之前先想清楚这个索引要服务什么样的查询很多人在建索引的时候属于“想起来就建”没有任何规划。我见过最极端的例子一张表上建了十多个单列索引结果慢查询一个没解决写入性能反而下降得很明显。索引不是为了“有”而建的它服务的对象是具体的 SQL 查询模式。在动手创建之前建议你先理清几个问题你的 WHERE 条件常用哪些列哪些列是等值过滤哪些列是范围过滤排序用的什么字段表连接用的关联键是什么有没有高频的 GROUP BY 或者 DISTINCT 操作比如WHERE user_id ? AND status ?这两列的等值组合出现得非常多建一个(user_id, status)的联合索引要比分别在两个列上建两个单列索引更高效再比如WHERE user_id ? ORDER BY create_time DESC如果只对user_id建索引排序必然会用到文件排序filesort效率远低于在一个(user_id, create_time)联合索引里直接按照索引顺序输出结果。索引本质上是“以空间换时间”的典型取舍。你要为读性能换取额外的磁盘开销和写入开销所以在创建索引之前最好先用慢查询日志或者 performance_schema 把线上真实的高频 SQL 捞出来看看它们到底缺什么索引而不是凭感觉拍脑袋。这是我在实践中学到的第一条法则不要为了建索引而建索引要让索引去服务真实的查询模式。3. 创建索引的完整实操指南3.1 三种创建索引的方式和适用场景MySQL 里创建索引的方式并不只有一种你可以根据场景灵活选择。最常用的有三类第一种是在建表时直接指定索引。比如CREATE TABLE orders ( id bigint(20) NOT NULL AUTO_INCREMENT, order_no varchar(64) NOT NULL COMMENT 订单号, user_id bigint(20) NOT NULL COMMENT 用户ID, status tinyint(4) NOT NULL DEFAULT 0 COMMENT 状态, create_time datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_id (user_id), KEY idx_create_time (create_time) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4;建表时就规划好索引的好处是结构一目了然适合新表上线。但问题是如果你后来才发现索引设计不合理要用 ALTER TABLE 去改表大的时候会涉及 DDL 锁表问题操作窗口比较紧张。第二种方式是用CREATE INDEX语句在已有表上添加索引。这是日常维护中最常用的方式CREATE INDEX idx_user_status ON orders(user_id, status);CREATE INDEX的优点是语法清晰专门就是干“加索引”这件事的而且支持一次创建一个索引不会影响表上已有的其他结构。注意CREATE INDEX不能用来创建主键索引主键索引只能通过ALTER TABLE ADD PRIMARY KEY或其他方式定义普通索引、唯一索引、联合索引、前缀索引都能用CREATE INDEX创建。第三种方式是用ALTER TABLE添加索引ALTER TABLE orders ADD KEY idx_user_status (user_id, status);ALTER TABLE的适用范围最广不但可以加索引还可以改表结构、加字段、改字段类型等。如果你修改表结构的同时要加索引一条 ALTER 语句就能合并搞定。从功能角度讲CREATE INDEX和ALTER TABLE ADD INDEX是等价的你可以按习惯选择。3.2 索引类型选择普通索引、唯一索引、联合索引、前缀索引到底怎么选新手最容易犯的一个错误就是把所有字段都建成普通索引从不考虑区分度和约束。实际上 MySQL 索引类型分得比较细每种类型背后的设计意图完全不同选错了要么性能达不到预期要么引入不必要的开销。普通索引KEY 或 INDEX是最基础的索引它的唯一任务就是加速查询。像订单表的status、user_id这类需要频繁过滤但允许重复的列适合建普通索引。唯一索引UNIQUE KEY则在普通索引的基础上增加了一层唯一性约束这不仅是性能优化工具更是数据完整性保障。比如订单号order_no业务上本来就不允许重复直接建一个唯一索引既能让查询走索引又能从数据库层面挡住重复插入。需要提醒的是唯一索引的写操作开销比普通索引略高因为每次插入都要额外检查是否冲突所以在没有唯一性需求的列上不要顺手建唯一索引。联合索引多列索引是性能优化里最值得深挖的内容。它的核心支持“最左前缀原则”也就是查询条件如果包含联合索引的最左列或最左连续的多列就能用上这个索引。比如有一个联合索引(user_id, status, create_time)那么WHERE user_id ?、WHERE user_id ? AND status ?、WHERE user_id ? AND status ? AND create_time BETWEEN ? AND ?都能命中索引但如果查询条件跳过了user_id只在status或create_time上过滤这个联合索引就帮不上忙。所以设计联合索引的时候必须把区分度高的列放在最前面其次考虑等值查询列、范围查询列的顺序同时尽量让一个索引覆盖多条高频 SQL 的查询条件这就是所谓的“一索引多用”。前缀索引是处理长字符串列比如 VARCHAR(255) 的文本字段的常用手段。对整列建索引会占用大量空间而且索引树的层级因为键值变长可能更高检索效率下降。你可以只对列的前 N 个字符建索引比如CREATE INDEX idx_phone_prefix ON users(phone(6))。但前缀索引有个明显的局限它无法用于覆盖索引优化因为索引里只保存了前缀字符查询依然要回表取完整值。另外前缀长度的选择直接影响区分度太短会导致你只拿到一大把相同前缀的索引项过滤效果变差太长又失去压缩意义。我的做法是先跑一条 SQL 统计不同前缀长度下的区分度保留在可接受范围内最短的前缀长度。3.3 实操案例订单表索引怎么建才合理讲完理论给一个完整的实操案例。假设我在维护一个电商订单系统订单表是orders核心字段包括 id、order_no、user_id、status、create_time、total_amount。业务上高频查询有用户查询自己的订单列表WHERE user_id ? ORDER BY create_time DESC、后台按状态筛选订单WHERE status ? AND create_time BETWEEN ? AND ?、根据订单号查询详情WHERE order_no ?。按照查询频率和区分度来设计ALTER TABLE orders ADD UNIQUE KEY uk_order_no (order_no), ADD KEY idx_user_create (user_id, create_time), ADD KEY idx_status_create (status, create_time);uk_order_no服务订单号查询同时保证唯一idx_user_create用户维度查询订单列表时索引天然有序避免 filesortidx_status_create支持后台的状态加时间范围筛选注意状态列的区分度不高但配合时间范围列之后过滤效果还是可以接受的。这个设计只用了三个索引就覆盖了绝大部分高频查询场景也不至于让写入压力过大。这里补充一个区分度判断的 SQLSELECT COUNT(DISTINCT user_id) / COUNT(*) AS user_cardinality, COUNT(DISTINCT status) / COUNT(*) AS status_cardinality FROM orders;区分度越接近 1索引过滤效果越好。如果某个列的区分度低于 0.1比如状态列只有几个枚举值单列索引几乎没什么价值一般要和其它列组合使用。3.4 创建索引时的三个实操要点第一个要点是尽量在业务低峰期在线添加索引或者使用在线 DDL 工具。MySQL 5.6 之后 InnoDB 支持了ALGORITHMINPLACE的在线 DDL很多索引添加操作可以在线完成不再完全锁表但不同版本的实现细节有差别5.7 和 8.0 的体验也不完全一样。生产环境已经跑到千万级的表我建议先用ALTER TABLE ... ADD INDEX ... , ALGORITHMINPLACE, LOCKNONE确认支持情况如果不支持就换用 gh-ost 或者 pt-online-schema-change 这类工具避免长时间阻塞业务写操作。第二个要点是时刻关注执行计划。索引创建完成后用EXPLAIN验证 SQL 是否真正用到了索引同时观察 type 字段常见有ref、range、index、ALL、key 字段、rows 字段。我之前遇到过一次很典型的翻车索引建完了EXPLAIN 显示 type 还是ALL全表扫描。排查后发现是查询条件里对索引列做了隐式类型转换比如字段类型是 varchar查询参数却传了数字MySQL 会自动把索引列转成数字去比较索引自然失效。第三个要点是关注索引大小和存储成本。一个索引建下去占用的磁盘空间可不是一个小数。在数据量几十 GB 的库里一个每列都是大 varchar 的联合索引轻松吞掉好几个 GB。单独的索引占用空间可以这样查SELECT database_name, table_name, index_name, stat_value * innodb_page_size AS index_size_bytes FROM mysql.innodb_index_stats WHERE database_name your_db AND table_name orders;如果索引占用的空间和收益不成比例那就得重新评估这个索引到底有没有存在的必要。4. 删除索引比创建更需要谨慎4.1 两种删除方式的适用边界删除索引有两种标准写法效果基本一致。方式一使用DROP INDEXDROP INDEX idx_user_create ON orders;方式二使用ALTER TABLE ... DROP INDEXALTER TABLE orders DROP INDEX idx_user_create;两种方式没有本质差别DROP INDEX语法上更加直观ALTER TABLE则能在同时修改表结构时一并处理。我个人的习惯是只删索引时用DROP INDEX删索引加改字段混在一起时用ALTER TABLE这样目的更明确别人看你的变更脚本时也更容易一眼读懂。有一个细节特别提醒主键索引不能直接通过这两条语句删除。你要删掉主键得先把主键约束处理掉或者重建表。比如原来主键设计不合理需要更换步骤是先把原主键 DROP 掉再添加新主键但这个过程中涉及唯一约束、外键依赖等一系列问题务必先梳理清楚依赖关系再操作。4.2 什么时候你才需要删除索引删除索引比创建索引更考验经验。我发现真正需要删除索引的场景往往伴随着业务变化或历史包袱。第一种场景是冗余索引清理。这是最常见的删除需求。由于早期迭代时每个人各建各的索引同一个列可能同时存在于多个单列索引和联合索引中。比如表上已经有联合索引(user_id, status)又单独在status上建了一个索引idx_status。从查询效率上讲idx_status对于WHERE status ?的过滤依然有效但联合索引已经覆盖了大部分状态查询场景单独的状态索引就是冗余的。冗余索引占空间、拖慢写入白白消耗资源。第二种场景是索引设计不合理区分度过低。我曾经见过一张日志表在level列上建了普通索引而 level 一共只有 INFO、WARN、ERROR 三个值查询时往往命中了大量数据还需要回表实际效率甚至不如全表扫描。这种索引删掉不会带来任何负面影响反而省出一块磁盘空间。第三种场景是查询模式发生根本变化。比如某个列表查询原来按字段 A 频繁过滤后来业务重构改成按字段 B 过滤字段 A 的索引就变成了摆设。这种索引因为长期不再被使用完全可以删掉。你可以通过performance_schema.table_io_waits_summary_by_index_usage这类视图统计索引的使用次数一个索引如果长时间没有任何访问就要考虑它是不是已经失去了存在价值。4.3 删除索引的完整操作流程生产环境删除索引我一般遵循这样的步骤。第一步先在测试环境确认删除这个索引对相关 SQL 的执行计划没有颠覆性影响。第二步在预发布环境通过EXPLAIN和真实业务流量观察一段时间尤其关注慢查询有没有增多。第三步选业务低峰期执行删除语句删除索引本身的操作通常比创建快但还是建议在变更窗口内完成。第四步删除后再次收集执行计划确认全表扫描或文件排序没有大面积出现。完整流程可以用下面的表单总结阶段动作关键检查点前置分析查看索引使用统计、确认冗余关系该索引近期是否有读请求预发验证在测试库执行 DROP跑相关 SQL 对比 EXPLAINsql 是否出现 typeALL 或 filesort变更执行低峰期执行删除语句观察数据库线程和锁状态事后观察持续收集慢查询日志和 performance_schema 数据是否出现新的慢查询告警还有一类需要特别小心的场景索引被外键约束引用。如果某个列上有外键MySQL 通常会自动为它创建索引你手动删除这个索引可能会直接报错或者导致外键约束失效。操作之前一定要用下面的语句检查一下约束情况SELECT table_name, column_name, constraint_name, referenced_table_name FROM information_schema.key_column_usage WHERE referenced_table_name IS NOT NULL AND table_name orders;4.4 删除索引时容易被忽略的锁问题删除索引并不像很多人想象的那样是“瞬时完成、影响为零”的操作。在 InnoDB 里DROP INDEX 会触发表的重建或者索引元数据的修改具体行为取决于索引类型和 MySQL 版本。对于大表来说删除索引同样会占用一定的 IO 资源和系统资源在业务高峰期执行照样可能引起性能抖动。另外说一下隐藏索引的概念——MySQL 8.0 支持隐藏索引invisible index。这是一个非常实用的过渡方案你可以先把索引设置为不可见ALTER TABLE orders ALTER INDEX idx_user_create INVISIBLE而不是直接删除。优化器会忽略隐藏索引查询不再走它但索引结构依然保留万一发现删了之后业务查询变慢一条语句就能立刻恢复可见。等观察期结束确认索引确实没用再真正 DROP 掉。这个方式比直接删除稳妥得多我在生产环境里遇到拿不准的索引都先用隐藏索引试探底。5. 索引实战高频问题与避坑经验5.1 索引失效场景速查表索引建得好不好不只看建了多少还得看查询有没有真正命中。我整理了实践中最高频的索引失效场景这张表可以直接在你排查性能问题时拿来对照。问题场景原因解决思路对索引列使用函数LOWER(column) 或 DATE(column) 导致优化器无法利用索引改写为范围条件或给计算列建表达式索引隐式类型转换varchar 列与数值比较统一参数类型左模糊匹配LIKE %keyword% 无法用索引改为前缀匹配或使用全文索引OR 条件跨列WHERE a 1 OR b 2 时优化器可能放弃索引用 UNION 拆分或建关联索引联合索引不符合最左前缀跳跃前导列调整查询字段或重新设计索引顺序索引列参与运算WHERE num * 2 10 等改写到独立列比较拿隐式类型转换举例我当时排查一个线上慢查询SELECT * FROM users WHERE mobile 13800138000mobile 是 varchar(11)传参是数字 13800138000。EXPLAIN 发现 type 为 ALL索引完全没走。后来我把参数统一成字符串13800138000查询直接走了索引执行时间从几百毫秒降到几毫秒。这类问题校验成本极低却是生产环境里最常见的性能杀手之一。5.2 二级索引更新时的锁顺序问题索引不只是查询优化工具它还会影响 InnoDB 的加锁行为而这方面的坑往往比较隐蔽。在一次排查死锁问题的时候我遇到过一个非常典型的场景业务上有一个更新操作UPDATE orders SET status ? WHERE order_no ?因为 order_no 上有二级索引InnoDB 会先锁二级索引项再回表去锁聚簇索引对应的主键行与此同时另一个事务通过主键 id 更新同一行顺序则可能相反——先锁聚簇索引行再锁二级索引项。两个事务各自持有了一把锁又都在等对方释放另一把锁形成交叉等待最终造成死锁。这种现象的根本原因在于InnoDB 对索引的锁操作是逐条执行的而不是一次性把所有锁都拿完。它的锁顺序依赖索引的扫描顺序当多个事务通过不同的索引路径访问同一行数据时加锁顺序就可能不一致。处理思路一般是尽量让高频更新操作使用同一条索引路径比如都通过主键更新减少跨索引更新的概率或者使用SELECT ... FOR UPDATE提前按统一顺序加锁给并发事务建立稳定的锁获取次序。建议把这个场景写进你的代码审查清单里凡是涉及“通过二级索引更新数据”的写操作都要考虑锁顺序带来的死锁风险。5.3 主键索引设计的常见误区主键索引的叶子节点就是整行数据这个特殊性决定了主键设计的容错率极低。最常见的误区有两类一类是使用业务字段作主键比如用身份证号或订单号另一类是用随机的 UUID 作主键。业务字段作主键的问题在于主键必须唯一且稳定业务字段一旦发生变更代价极高而且业务字段往往不是递增的Insert 时数据页的分裂现象会更频繁影响写入效率。UUID 作主键同样糟糕——它不保证顺序性插入时索引页需要频繁分裂与重排数据存储碎片化明显B 树的性能优势被削弱得很厉害。更推荐的做法是使用自增整数或者雪花算法生成的分布式有序 ID 作为主键。自增主键每一次插入都是追加写顺序性和写入性能都最好如果分布式的场景不允许依赖数据库自增雪花 ID 这种趋势递增的方案也过得去。强调一个设计原则主键越短越好。主键会出现在每一个二级索引的叶子节点上主键越长二级索引的体积就越大占用的缓冲池内存也越多磁盘 IO 负担随之上升。这也是为什么在很多表设计中BIGINT AUTO_INCREMENT比 VARCHAR 类型主键更受青睐的原因。5.4 索引表空间回收与表重建创建索引会占用表空间删除索引后空间能不能立刻还给操作系统答案是不能立刻。InnoDB 的表空间文件比如orders.ibd在删除了索引之后会留下碎片空间文件大小不会自动缩减。如果你特别在意磁盘占用需要执行表重建操作来整理和回收空间。重建表最简单的方式是ALTER TABLE orders ENGINEInnoDB;这条语句的作用是让 InnoDB 重建整张表在重建过程中重新组织数据和索引清掉碎片空间。要注意这个过程在 MySQL 5.7 之前会锁表即使在 5.7 和 8.0 支持了在线 DDL执行时依然有额外 IO 和主从延迟的风险。还有一个备选方案是OPTIMIZE TABLE orders它的效果实际上也是重建表。对于超大表这个操作的耗时可能非常长务必避开业务高峰期并且提前在磁盘空间上留足余量。另一种做法是直接重建索引通过删掉再重新创建的方式整理索引页的碎片ALTER TABLE orders DROP INDEX idx_user_create, ADD INDEX idx_user_create (user_id, create_time);这条语句的代价是重建索引期间有 IO 压力但相比整表重建要轻量不少。实测下来如果只是想消除索引空间的碎片这种“删除重建”的做法已经足够没必要整表重建。5.5 用隐藏索引做删除前的“灰度验证”前面提到隐藏索引这里补充一个具体的操作示例因为它真的能帮你避免很多事故。假设我怀疑idx_status_create已经基本没有查询在用了但后台系统偶尔可能有一次性报表任务还在跑我不确定删除它会不会导致这批任务变慢。这时候我会这样做-- 第一步让优化器忽略这个索引 ALTER TABLE orders ALTER INDEX idx_status_create INVISIBLE; -- 第二步观察慢查询日志确认这段时间有没有因为索引隐藏而变慢的 SQL -- 第三步确认安全之后彻底删除索引 DROP INDEX idx_status_create ON orders;整个流程下来如果中间哪一步发现问题一条ALTER TABLE orders ALTER INDEX idx_status_create VISIBLE就能立刻把索引救回来比删除之后又后悔要安全太多了。唯一要注意的是INVISIBLE这种索引状态在 MySQL 8.0 才支持5.7 及之前的版本没有这个能力有条件的话建议尽早把生产环境升到 8.0。6. 关于索引维护最后想说的几点经验从建索引到删索引整个循环其实就是一个“理解业务查询模式”的过程。索引不是堆得越多越好也不是建了就一劳永逸——它是一个需要持续维护的数据库对象。表结构会变业务查询会变数据量会变索引的方案也必须跟着演进。我个人在实操中最受益的几个习惯再单独强调一遍第一每次 DDL 变更之前一定先看EXPLAIN执行计划跑不出预期就坚决不动手第二建立一套索引使用情况巡检机制定期看performance_schema里各个索引的访问统计把长期闲置的索引找出来第三删除不确定是否还有用的索引时优先用隐藏索引做验证而不是一刀切第四所有涉及索引的变更脚本都要走版本管理上线时和代码发版一样谨慎操作时间窗口避开业务高峰。我之前踩过不少坑最深刻的一条是索引优化不能只靠文档和经验一定要基于你线上的真实数据和真实查询。同样一张表同样的索引设计在不同业务量、不同数据分布下的表现可能天差地别。先收集数据、再分析查询、最后才动手变更这个顺序永远不要颠倒。如果你正在处理某张具体表的索引问题建议从慢查询日志开始把消耗最高那几条 SQL 拿出来逐个用EXPLAIN分析你会发现真正需要创建的索引其实没有几个真正该删除的索引反而经常被忽略。