上周线上有个订单查询接口突然慢得离谱一个简单的按用户查订单列表SQL 跑了快两秒。当时第一反应就是看EXPLAIN结果type是ALLrows直接飚到几十万典型的全表扫描。那台 MySQL 实例上的订单表已经过千万级加索引之前和之后完全是两种体验——这也是我想写这篇博文的直接原因。关于 MySQL 索引从原理到实操、从命中规则到失效场景、从设计规范到维护巡检我在这几年的数据库优化里踩了不少坑这次一次性整理出来。这篇内容不是帮你背概念而是告诉你真正到线上环境给 MySQL 数据库建立索引时每一步该怎么做、为什么要这么做、哪些做法看起来很合理其实是给自己埋雷。文章适合正在做后端开发、需要自己优化数据库性能的工程师也适合准备 MySQL 面试、想系统梳理索引知识的同学。我尽量把 B Tree 原理、索引分类、实际建索引 SQL、组合索引设计、失效场景排查这整条链路讲清楚让看完的人能直接上手。1. 索引到底在解决什么问题先了解InnoDB的存储模型1.1 为什么全表扫描会慢B Tree如何加速查找先不急着写CREATE INDEX你得知道 MySQL 无缘无故为什么要存一棵树。InnoDB存储引擎的数据是按页page组织的默认每页 16KB数据行存放在页里页与页之间形成双向链表同一个页内的行通过单向链表串联。当你执行不带索引的查询时存储引擎只能从第一个页开始把每一行依次读出来做匹配这就是全表扫描。千万级的数据哪怕只查一条也要把几万甚至几十万个页全部读一遍瓶颈完全卡在磁盘 IO 上。B Tree 的核心价值在于把“遍历”变成“查找”。它是一棵矮胖的多路平衡树根节点到叶子节点的高度通常只有 2 到 4 层。因为每一层节点保存多个键值和子节点指针一次查找最多只需要 3、4 次磁盘 IO就能定位到目标叶子页。从“读几十万个页”变成“读几个页”性能差距就是这样拉开的。叶子节点之间通过双向链表连接也让范围查询非常舒服——找到了起始位置之后顺着链表往后扫就行不需要回溯父节点。1.2 聚簇索引、二级索引与主键索引的关系InnoDB 里有个非常重要的设计表本身就是一棵以主键为排序键的 B Tree这叫聚簇索引。叶子节点直接保存整行数据所以通过主键查数据找到叶子节点就等于拿到了完整数据行。这也是为什么 InnoDB 表强烈建议显式定义主键如果没有主键它会选第一个非空的唯一索引作为聚簇索引再没有就隐式生成一个 6 字节的 rowid。没有主键的 InnoDB 表每次插入都可能引发页分裂性能隐患很大。二级索引也叫辅助索引或普通索引则不同它的叶子节点存的是索引列的值再加上对应主键值。查的时候先走二级索引树找到主键值再回聚簇索引树里查一次完整数据这个动作叫回表。举个例子你在email字段上建了普通索引执行SELECT * FROM user WHERE emailxxxMySQL 会先查二级索引拿到主键 id再用 id 回表取出完整行。这就是为什么有些查询看似走了索引还是有性能损耗——回表多了IO 次数自然上去了这也是后面讲覆盖索引能够大幅提速的根本原因。2. 为MySQL数据库建立索引从语法到实战决策2.1 创建索引的基本语法和使用场景MySQL 里建索引最常用的有三种途径建表时指定、CREATE INDEX、ALTER TABLE追加。拿一个实际订单表来举例CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, ... );如果要给订单号加唯一索引可以这样CREATE UNIQUE INDEX uk_order_no ON t_order(order_no);或者ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);CREATE INDEX和ALTER TABLE ADD INDEX在 MySQL 里效果是一样的唯一索引用UNIQUE关键字普通索引直接INDEX或KEY。建表时指定的话就在表定义的最后加上KEY idx_user_id (user_id)这样的语法。我平时更习惯用ALTER TABLE因为线上表结构变更一般通过工具执行ALTER TABLE语义更明确。要注意的是大表在线建索引不能直接ALTER TABLEMySQL 8.0 之前这会锁表生产环境用gh-ost或pt-online-schema-change这类在线改表方案这个后面细说。另一个实用场景是联合唯一索引。比如业务上要求同一个用户对同一个商品只能有一条评价记录假设表里已有user_id和product_id字段可以直接建联合唯一索引来兜底防重ALTER TABLE t_review ADD UNIQUE INDEX uk_user_product (user_id, product_id);这个索引既保证了唯一性又能加速“查某人对某商品的评价”这类高频查询一举两得。但要注意联合唯一索引的字段顺序会影响唯一性约束的判断范围顺序不同语义完全相同但查询利用的效率不同选择时优先让最常用于等值查询的字段放在最左边。2.2 索引类型怎么选普通、唯一、全文、前缀索引类型这件事选错了不是不能用而是会造成没必要的成本。普通索引INDEX只加速查询不约束数据唯一性UNIQUE索引额外多一层唯一性约束写入时会多做一次冲突检测所以如果没有唯一性要求不要随便加UNIQUE。全文索引FULLTEXT在 MySQL 里专门处理大文本的模糊匹配比如文章内容的词法搜索如果你只是对VARCHAR字段做LIKE abc%普通索引就能搞定不需要全文索引。LIKE %abc%即使有普通索引也走不了这属于索引失效范畴后面单独讲。VARCHAR字段长度很长的时候比如某个业务表里存了邮箱或 URL整个字段建索引会导致索引树变得很大占用空间多且单个索引条目太大一个页能存放的键值变少树的高度可能增加。这时候用前缀索引更划算——只取字段前 N 个字符做索引ALTER TABLE t_user ADD INDEX idx_email_prefix (email(20));到底取多长核心看选择性。选择性 去重后的前缀值数量 / 去重后的完整值数量越接近 1 越好。通常的做法是分别试5、10、15、20不同前缀长度的区分度选一个能让选择性超过 0.9 且长度尽量短的值。代价是前缀索引无法用于ORDER BY email或者覆盖索引扫描但如果只是做等值查询实际效果非常好。2.3 索引不是越多越好建索引前必须权衡的问题很多新手容易犯的一个错误是为了优化某个慢查询立刻加一个索引结果索引越加越多最后一张表二三十个索引。索引不是免费的午餐它在加速读取的同时牺牲的是写入性能和存储空间。每次INSERT、UPDATE、DELETEInnoDB 不仅要修改聚簇索引里的数据页还要同步维护每一条二级索引树索引越多写入放大越明显。在写入频繁的表上多加几个索引TPS 可能出现肉眼可见的下降。另一个容易被忽略的问题冗余索引和重复索引。比如你建了(user_id, status)联合索引又单独建了(user_id)索引后者就是冗余的——因为联合索引的最左前缀原则已经能覆盖user_id单独查询的场景。重复索引则更直接建了KEY idx_user_id (user_id)又建KEY idx_user_id_2 (user_id)完全一样的两棵树纯浪费。我建议每半年做一次索引梳理用sys.schema_unused_indexes视图查一下哪些索引从未被使用结合慢日志确认后删除。这个视图在 MySQL 5.7 以上就自带了非常方便SELECT * FROM sys.schema_unused_indexes;这里我要多说一句删索引前一定确认它没被使用别只看视图结果——视图统计的是服务启动以来的使用情况如果业务有周期性任务刚好在统计周期之外可能误判。稳妥做法是把慢日志里所有 SQL 拉出来手工检查一遍可能走这些索引的查询确认没有命中再动手。3. 组合索引与排序优化让一条索引服务多个查询3.1 最左前缀原则与实际字段编排方法组合索引是 MySQL 索引设计里最能体现功力的部分。一张表最多建那么几个索引怎么样让这几个索引覆盖尽可能多的高频查询场景靠的就是对组合索引字段顺序的理解。核心规则是最左前缀原则MySQL 只能从组合索引最左边的字段开始连续匹配跳到中间字段再查后面的字段就没法用索引了。比如创建了组合索引(a, b, c)那么查询条件是a 1能用到索引查询条件是a 1 AND b 2能用到索引查询条件是a 1 AND b 2 AND c 3完整用到索引查询条件是b 2 AND c 3用不到这个索引查询条件是a 1 AND c 3只能用到a列c列无法从索引中过滤。明白了这个规则编排组合索引字段顺序时应该遵循几条原则等值查询的字段放最前面经常用于范围查询、、BETWEEN的字段放在后面区分度高的字段尽量靠前。之所以区分度高的放前面是因为索引树在每层节点上能更早过滤掉更多数据减少向下检索的路径长度。但这不是绝对的如果某个低区分度字段是高频率的等值查询条件比如status1把 status 放前面反而能让查询直接定位到对应分支。所以最终要结合业务查询的实际频率而不是机械套公式。3.2 覆盖索引如何避免回表带来的性能损耗回表操作要额外读一次聚簇索引在数据量大或者二级索引本身不在内存里的时候等于多一次磁盘 IO。那有没有办法让查询需要的列全部存在于二级索引树中有这就是覆盖索引。MySQL 如果发现二级索引里已经有查询需要的全部列就会直接返回结果不再回表执行计划里Extra会显示Using index。举个实际例子订单列表页需要展示订单号、状态和创建时间查询条件是user_idSELECT order_no, status, created_at FROM t_order WHERE user_id 10086;如果只是建一个KEY idx_user_id (user_id)查出来的是user_id和主键 id要拿order_no、status、created_at还得回表。改成建一个覆盖索引直接把这三列都放进去ALTER TABLE t_order ADD INDEX idx_user_cover (user_id, order_no, status, created_at);这样查询所需的所有列都在二级索引树上一次索引扫描直接返回结果完全不需要回表。注意覆盖索引不是让你把所有字段都塞进索引那样索引体积会失控而是针对「高频查询、固定返回列」的场景做定向覆盖。像订单状态这种只是展示用的字段加进来没多大成本如果连order_detail这种大的TEXT字段也放索引里就得不偿失了。3.3 排序与索引的配合ORDER BY 走索引优化ORDER BY也是索引能明显起作用的地方。B Tree 本身就是按索引列排序的如果查询的排序顺序和索引顺序一致MySQL 可以直接利用索引的有序性返回结果不需要额外的filesort文件排序。文件排序很贵要先把结果集取出来放到内存或磁盘临时文件里排一遍数据量大的时候性能极差。比如订单列表经常要按创建时间倒序SELECT * FROM t_order WHERE user_id 10086 ORDER BY created_at DESC;这时如果索引是(user_id, created_at)先按user_id等值定位created_at天然有序倒序扫一遍就行Extra里会显示Using index condition且没有Using filesort。但如果索引是(created_at, user_id)虽然created_at有序可等值条件user_id在右边没办法先定位用户再按时间取排序就落回filesort了。MySQL 8.0 还引入了降序索引的支持可以显式指定索引列的排序方向ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at DESC);在 8.0 之前索引列默认都是升序存储反向扫描也能工作但效率略低。如果你确定某列的排序方向几乎总是倒序在 8.0 里建降序索引会更稳妥。不过说实话实际工程里大部分场景升序索引加反向扫描已经够用了降序索引更多用于混合排序一部分升序一部分降序这种特殊情况日常开发不必过度设计。4. 索引失效排查与EXPLAIN实战索引建了却不生效怎么办4.1 最常见的六种索引失效场景盘点索引建了不等于 SQL 一定会走。线上最常见的索引失效场景我总结了六种几乎每个项目都能碰上第一对索引列使用了函数或表达式计算。比如WHERE DATE(created_at) 2025-01-01这在created_at上做了函数运算MySQL 无法利用索引的有序性只能全表扫。正确写法是WHERE created_at 2025-01-01 AND created_at 2025-01-02才是范围查询。第二隐式类型转换。字段类型是VARCHAR查询条件却写成数字比如WHERE phone 13800138000phone 是VARCHARMySQL 会把phone转成数字再比较索引列上发生了隐式函数转换索引就废了。反过来如果字段是整型条件写字符串通常不会失效因为优化器会把字符串转成数字。但千万别依赖这个行为开发规范里就要求字段类型和参数类型严格一致。第三LIKE以通配符开头的模糊查询。WHERE name LIKE %张没法走索引因为 B Tree 定位的时候必须知道前缀。WHERE name LIKE 张%就能走。如果业务确实需要后缀模糊匹配考虑引入搜索引擎或者改用前缀索引思路重建字段不要在查询上硬扛。第四联合索引没有遵循最左前缀。前面已经详细讲过了这里不再展开。第五OR连接条件时有一个字段没有索引。WHERE user_id 10086 OR status 1如果status没有索引MySQL 可能选择全表扫描而不是分别走两个索引合并结果因为单独用索引再合并的成本往往比全表扫描更高。解决办法是给status也加索引或者优化器选择INDEX_MERGE。第六NOT IN、NOT LIKE和!这类否定操作大部分情况下也无法利用索引因为 B Tree 索引本质上是基于等值和范围有序匹配的否定操作很难直接定位区间。4.2 EXPLAIN输出如何判断SQL是否用到索引判断一条 SQL 有没有走索引不是靠猜也不是看执行时间而是直接看EXPLAIN输出。以最典型的字段来说type访问类型。从好到差依次是system const eq_ref ref range index ALL。看到ALL基本就是全表扫描了得警惕index是扫描了整棵索引树也不怎么样range开始就比较健康比如BETWEEN、、这类范围查询ref是等值查询走了普通二级索引const是主键或唯一索引等值查询性能最好。key实际使用的索引名称。如果为NULL就是没走任何索引。rows优化器预估需要扫描的行数。这个数字不精确但量级能说明问题几千和几十万完全是两个概念。Extra补充信息。看到Using filesort说明排序没走索引Using temporary说明用了临时表Using index是最理想的情况代表覆盖索引。举个例子EXPLAIN SELECT order_no, status FROM t_order WHERE user_id 10086 ORDER BY created_at DESC;如果执行结果的Extra里有Using filesort说明(user_id, created_at)这个组合索引没建或者字段顺序不对数据库只能用文件排序。这一条信息比任何性能调优文档都更能直接指导你该建什么索引。4.3 索引下推与优化器的那些细节MySQL 5.6 引入了索引下推Index Condition Pushdown简称 ICP这是个容易被忽视但很有用的优化。在没有 ICP 时如果组合索引是(user_id, status)查询条件是user_id 1000 AND status 1InnoDB 要先把所有user_id 1000的索引记录取出来回表再过滤status 1。有了 ICP存储引擎在读取二级索引时就同时判断status 1把不满足条件的记录直接过滤掉减少了回表次数。EXPLAIN的Extra里会显示Using index condition。另外一个细节是优化器对索引的选择并不总是最优的。MySQL 的优化器基于统计信息估算成本有时候明明有索引但它估算走全表扫描成本更低比如表很小、或者索引列区分度很低就会放弃索引。这时候先用ANALYZE TABLE t_order;更新统计信息再重新看执行计划。如果确实不该走索引也别强行FORCE INDEX那是最后的手段而且索引数据分布一变强制索引可能变成负优化。5. 常见问题与排查技巧实录把索引问题做进日常巡检5.1 索引碎片与维护为什么加了索引性能还是越来越差一个长期运行的线上表即使索引建得没问题性能也可能越来越差。这通常是索引碎片fragmentation和统计信息不准确导致的。InnoDB 的索引页在频繁删除、更新后可能出现大量碎片空间页利用率下降导致同样的数据量占用更多页扫描 IO 变高。可以通过information_schema查看索引的页数和碎片情况也可以用OPTIMIZE TABLE t_order;重建表并整理索引。不过OPTIMIZE TABLE在大表上会锁表不能直接在生产环境跑。我的经验是结合在线改表工具做或者选择低峰期操作。顺便说一个更省事的方案定期执行ANALYZE TABLE t_order;更新统计信息让优化器拿到更准确的基数估计。这个操作成本远低于OPTIMIZE日常巡检可以优先用好它。如果确认碎片严重再找窗口期做在线OPTIMIZE。5.2 针对慢SQL的排查流程与线上问题速查表在团队里我经常带新人做慢查询排查慢慢固定下来一套流程这里直接分享出来照着做基本能解决大部分问题在 RDS 或自建 MySQL 的慢查询日志里把慢 SQL 捞出来按执行次数和耗时排序。对目标 SQL 执行EXPLAIN先看type、key、rows、Extra判断是全表扫描、回表还是 filesort。对照索引设计原则检查是缺索引、索引字段顺序不对还是 SQL 写法导致索引失效。如果是索引失效优先改 SQL 写法——比如把函数运算改成范围条件、去掉隐式转换、调整联合索引的字段顺序。线上索引变更走在线 DDL 工具不要直接ALTER TABLE锁表。变更后回看执行计划和性能指标确认问题闭环。给你一个速查表对应常见问题直接查找方案场景现象处理方案无索引全表扫描typeALLrows巨大按查询条件建合适索引已有索引但未命中keyNULL先查索引失效原因再看统计信息是否需要更新排序慢ExtraUsing filesort调整索引顺序让排序字段跟查询走同一索引回表过多typeref但延迟高把查询返回列尽量收入覆盖索引联合索引未生效触发了最左前缀法则的例外重新设计组合索引字段顺序索引频繁更新UPDATE变慢精简索引数量删除冗余索引权衡读写比5.3 数据库同步与在线索引变更的影响如果业务用了主从复制或者接了数据库同步工具在线加索引的时候要格外小心。MySQL 的主从同步是逻辑复制主库执行 DDL 后从库也会执行同样的 DDL。MySQL 8.0 之前的 DDL 大部分需要获取元数据锁会阻塞该表的 DML 操作如果从库同步延迟本身偏高一个慢的ALTER TABLE可能让从库延迟进一步拉大进而影响线上读写分离的稳定性。我自己做线上索引变更的推荐姿势优先用gh-ost这类无锁在线变更工具它在主库上通过 binlog 同步的方式创建临时表逐步拷贝数据最后原子切换对线上影响小得多。如果没有这类工具至少在业务低峰期执行ALTER TABLE并把lock_wait_timeout设小一点避免长时间拿不到锁而阻塞。变更前最好在测试环境用同样量级的数据先跑一遍估算执行时间。记住索引是给查询加速的但加索引这个动作本身如果影响了可用性那得不偿失。MySQL 索引这件事看起来是几条 SQL 语法真正吃透之后会发现整个数据库优化的思路都清晰了。从 B Tree 的存储结构到聚簇索引和二级索引的配合再到组合索引如何覆盖高频查询每一步都需要结合你实际的表结构和业务查询来做决策。我在实操中最深的体会是没有任何一套索引设计规则能直接套用到所有项目核心方法就是多跑EXPLAIN、多观察真实查询、多做索引梳理慢慢你会形成一种直觉看到一条慢 SQL脑子里基本能判断出是缺索引、索引失效还是索引根本没设计好。最后再分享一个小习惯我每周五下午会花十分钟看一次慢日志和sys.schema_unused_indexes这个动作坚持了快两年线上因为索引导致的性能问题基本都在萌芽期就被干掉了。希望你也能养成这个习惯少踩几个我在生产环境里踩过的坑。 MySQL索引优化实战：B+Tree原理、组合索引设计与EXPLAIN排查
上周线上有个订单查询接口突然慢得离谱一个简单的按用户查订单列表SQL 跑了快两秒。当时第一反应就是看EXPLAIN结果type是ALLrows直接飚到几十万典型的全表扫描。那台 MySQL 实例上的订单表已经过千万级加索引之前和之后完全是两种体验——这也是我想写这篇博文的直接原因。关于 MySQL 索引从原理到实操、从命中规则到失效场景、从设计规范到维护巡检我在这几年的数据库优化里踩了不少坑这次一次性整理出来。这篇内容不是帮你背概念而是告诉你真正到线上环境给 MySQL 数据库建立索引时每一步该怎么做、为什么要这么做、哪些做法看起来很合理其实是给自己埋雷。文章适合正在做后端开发、需要自己优化数据库性能的工程师也适合准备 MySQL 面试、想系统梳理索引知识的同学。我尽量把 B Tree 原理、索引分类、实际建索引 SQL、组合索引设计、失效场景排查这整条链路讲清楚让看完的人能直接上手。1. 索引到底在解决什么问题先了解InnoDB的存储模型1.1 为什么全表扫描会慢B Tree如何加速查找先不急着写CREATE INDEX你得知道 MySQL 无缘无故为什么要存一棵树。InnoDB存储引擎的数据是按页page组织的默认每页 16KB数据行存放在页里页与页之间形成双向链表同一个页内的行通过单向链表串联。当你执行不带索引的查询时存储引擎只能从第一个页开始把每一行依次读出来做匹配这就是全表扫描。千万级的数据哪怕只查一条也要把几万甚至几十万个页全部读一遍瓶颈完全卡在磁盘 IO 上。B Tree 的核心价值在于把“遍历”变成“查找”。它是一棵矮胖的多路平衡树根节点到叶子节点的高度通常只有 2 到 4 层。因为每一层节点保存多个键值和子节点指针一次查找最多只需要 3、4 次磁盘 IO就能定位到目标叶子页。从“读几十万个页”变成“读几个页”性能差距就是这样拉开的。叶子节点之间通过双向链表连接也让范围查询非常舒服——找到了起始位置之后顺着链表往后扫就行不需要回溯父节点。1.2 聚簇索引、二级索引与主键索引的关系InnoDB 里有个非常重要的设计表本身就是一棵以主键为排序键的 B Tree这叫聚簇索引。叶子节点直接保存整行数据所以通过主键查数据找到叶子节点就等于拿到了完整数据行。这也是为什么 InnoDB 表强烈建议显式定义主键如果没有主键它会选第一个非空的唯一索引作为聚簇索引再没有就隐式生成一个 6 字节的 rowid。没有主键的 InnoDB 表每次插入都可能引发页分裂性能隐患很大。二级索引也叫辅助索引或普通索引则不同它的叶子节点存的是索引列的值再加上对应主键值。查的时候先走二级索引树找到主键值再回聚簇索引树里查一次完整数据这个动作叫回表。举个例子你在email字段上建了普通索引执行SELECT * FROM user WHERE emailxxxMySQL 会先查二级索引拿到主键 id再用 id 回表取出完整行。这就是为什么有些查询看似走了索引还是有性能损耗——回表多了IO 次数自然上去了这也是后面讲覆盖索引能够大幅提速的根本原因。2. 为MySQL数据库建立索引从语法到实战决策2.1 创建索引的基本语法和使用场景MySQL 里建索引最常用的有三种途径建表时指定、CREATE INDEX、ALTER TABLE追加。拿一个实际订单表来举例CREATE TABLE t_order ( id BIGINT PRIMARY KEY AUTO_INCREMENT, order_no VARCHAR(64) NOT NULL COMMENT 订单号, user_id BIGINT NOT NULL COMMENT 用户ID, status TINYINT NOT NULL DEFAULT 0 COMMENT 订单状态, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 创建时间, ... );如果要给订单号加唯一索引可以这样CREATE UNIQUE INDEX uk_order_no ON t_order(order_no);或者ALTER TABLE t_order ADD UNIQUE INDEX uk_order_no (order_no);CREATE INDEX和ALTER TABLE ADD INDEX在 MySQL 里效果是一样的唯一索引用UNIQUE关键字普通索引直接INDEX或KEY。建表时指定的话就在表定义的最后加上KEY idx_user_id (user_id)这样的语法。我平时更习惯用ALTER TABLE因为线上表结构变更一般通过工具执行ALTER TABLE语义更明确。要注意的是大表在线建索引不能直接ALTER TABLEMySQL 8.0 之前这会锁表生产环境用gh-ost或pt-online-schema-change这类在线改表方案这个后面细说。另一个实用场景是联合唯一索引。比如业务上要求同一个用户对同一个商品只能有一条评价记录假设表里已有user_id和product_id字段可以直接建联合唯一索引来兜底防重ALTER TABLE t_review ADD UNIQUE INDEX uk_user_product (user_id, product_id);这个索引既保证了唯一性又能加速“查某人对某商品的评价”这类高频查询一举两得。但要注意联合唯一索引的字段顺序会影响唯一性约束的判断范围顺序不同语义完全相同但查询利用的效率不同选择时优先让最常用于等值查询的字段放在最左边。2.2 索引类型怎么选普通、唯一、全文、前缀索引类型这件事选错了不是不能用而是会造成没必要的成本。普通索引INDEX只加速查询不约束数据唯一性UNIQUE索引额外多一层唯一性约束写入时会多做一次冲突检测所以如果没有唯一性要求不要随便加UNIQUE。全文索引FULLTEXT在 MySQL 里专门处理大文本的模糊匹配比如文章内容的词法搜索如果你只是对VARCHAR字段做LIKE abc%普通索引就能搞定不需要全文索引。LIKE %abc%即使有普通索引也走不了这属于索引失效范畴后面单独讲。VARCHAR字段长度很长的时候比如某个业务表里存了邮箱或 URL整个字段建索引会导致索引树变得很大占用空间多且单个索引条目太大一个页能存放的键值变少树的高度可能增加。这时候用前缀索引更划算——只取字段前 N 个字符做索引ALTER TABLE t_user ADD INDEX idx_email_prefix (email(20));到底取多长核心看选择性。选择性 去重后的前缀值数量 / 去重后的完整值数量越接近 1 越好。通常的做法是分别试5、10、15、20不同前缀长度的区分度选一个能让选择性超过 0.9 且长度尽量短的值。代价是前缀索引无法用于ORDER BY email或者覆盖索引扫描但如果只是做等值查询实际效果非常好。2.3 索引不是越多越好建索引前必须权衡的问题很多新手容易犯的一个错误是为了优化某个慢查询立刻加一个索引结果索引越加越多最后一张表二三十个索引。索引不是免费的午餐它在加速读取的同时牺牲的是写入性能和存储空间。每次INSERT、UPDATE、DELETEInnoDB 不仅要修改聚簇索引里的数据页还要同步维护每一条二级索引树索引越多写入放大越明显。在写入频繁的表上多加几个索引TPS 可能出现肉眼可见的下降。另一个容易被忽略的问题冗余索引和重复索引。比如你建了(user_id, status)联合索引又单独建了(user_id)索引后者就是冗余的——因为联合索引的最左前缀原则已经能覆盖user_id单独查询的场景。重复索引则更直接建了KEY idx_user_id (user_id)又建KEY idx_user_id_2 (user_id)完全一样的两棵树纯浪费。我建议每半年做一次索引梳理用sys.schema_unused_indexes视图查一下哪些索引从未被使用结合慢日志确认后删除。这个视图在 MySQL 5.7 以上就自带了非常方便SELECT * FROM sys.schema_unused_indexes;这里我要多说一句删索引前一定确认它没被使用别只看视图结果——视图统计的是服务启动以来的使用情况如果业务有周期性任务刚好在统计周期之外可能误判。稳妥做法是把慢日志里所有 SQL 拉出来手工检查一遍可能走这些索引的查询确认没有命中再动手。3. 组合索引与排序优化让一条索引服务多个查询3.1 最左前缀原则与实际字段编排方法组合索引是 MySQL 索引设计里最能体现功力的部分。一张表最多建那么几个索引怎么样让这几个索引覆盖尽可能多的高频查询场景靠的就是对组合索引字段顺序的理解。核心规则是最左前缀原则MySQL 只能从组合索引最左边的字段开始连续匹配跳到中间字段再查后面的字段就没法用索引了。比如创建了组合索引(a, b, c)那么查询条件是a 1能用到索引查询条件是a 1 AND b 2能用到索引查询条件是a 1 AND b 2 AND c 3完整用到索引查询条件是b 2 AND c 3用不到这个索引查询条件是a 1 AND c 3只能用到a列c列无法从索引中过滤。明白了这个规则编排组合索引字段顺序时应该遵循几条原则等值查询的字段放最前面经常用于范围查询、、BETWEEN的字段放在后面区分度高的字段尽量靠前。之所以区分度高的放前面是因为索引树在每层节点上能更早过滤掉更多数据减少向下检索的路径长度。但这不是绝对的如果某个低区分度字段是高频率的等值查询条件比如status1把 status 放前面反而能让查询直接定位到对应分支。所以最终要结合业务查询的实际频率而不是机械套公式。3.2 覆盖索引如何避免回表带来的性能损耗回表操作要额外读一次聚簇索引在数据量大或者二级索引本身不在内存里的时候等于多一次磁盘 IO。那有没有办法让查询需要的列全部存在于二级索引树中有这就是覆盖索引。MySQL 如果发现二级索引里已经有查询需要的全部列就会直接返回结果不再回表执行计划里Extra会显示Using index。举个实际例子订单列表页需要展示订单号、状态和创建时间查询条件是user_idSELECT order_no, status, created_at FROM t_order WHERE user_id 10086;如果只是建一个KEY idx_user_id (user_id)查出来的是user_id和主键 id要拿order_no、status、created_at还得回表。改成建一个覆盖索引直接把这三列都放进去ALTER TABLE t_order ADD INDEX idx_user_cover (user_id, order_no, status, created_at);这样查询所需的所有列都在二级索引树上一次索引扫描直接返回结果完全不需要回表。注意覆盖索引不是让你把所有字段都塞进索引那样索引体积会失控而是针对「高频查询、固定返回列」的场景做定向覆盖。像订单状态这种只是展示用的字段加进来没多大成本如果连order_detail这种大的TEXT字段也放索引里就得不偿失了。3.3 排序与索引的配合ORDER BY 走索引优化ORDER BY也是索引能明显起作用的地方。B Tree 本身就是按索引列排序的如果查询的排序顺序和索引顺序一致MySQL 可以直接利用索引的有序性返回结果不需要额外的filesort文件排序。文件排序很贵要先把结果集取出来放到内存或磁盘临时文件里排一遍数据量大的时候性能极差。比如订单列表经常要按创建时间倒序SELECT * FROM t_order WHERE user_id 10086 ORDER BY created_at DESC;这时如果索引是(user_id, created_at)先按user_id等值定位created_at天然有序倒序扫一遍就行Extra里会显示Using index condition且没有Using filesort。但如果索引是(created_at, user_id)虽然created_at有序可等值条件user_id在右边没办法先定位用户再按时间取排序就落回filesort了。MySQL 8.0 还引入了降序索引的支持可以显式指定索引列的排序方向ALTER TABLE t_order ADD INDEX idx_user_created (user_id, created_at DESC);在 8.0 之前索引列默认都是升序存储反向扫描也能工作但效率略低。如果你确定某列的排序方向几乎总是倒序在 8.0 里建降序索引会更稳妥。不过说实话实际工程里大部分场景升序索引加反向扫描已经够用了降序索引更多用于混合排序一部分升序一部分降序这种特殊情况日常开发不必过度设计。4. 索引失效排查与EXPLAIN实战索引建了却不生效怎么办4.1 最常见的六种索引失效场景盘点索引建了不等于 SQL 一定会走。线上最常见的索引失效场景我总结了六种几乎每个项目都能碰上第一对索引列使用了函数或表达式计算。比如WHERE DATE(created_at) 2025-01-01这在created_at上做了函数运算MySQL 无法利用索引的有序性只能全表扫。正确写法是WHERE created_at 2025-01-01 AND created_at 2025-01-02才是范围查询。第二隐式类型转换。字段类型是VARCHAR查询条件却写成数字比如WHERE phone 13800138000phone 是VARCHARMySQL 会把phone转成数字再比较索引列上发生了隐式函数转换索引就废了。反过来如果字段是整型条件写字符串通常不会失效因为优化器会把字符串转成数字。但千万别依赖这个行为开发规范里就要求字段类型和参数类型严格一致。第三LIKE以通配符开头的模糊查询。WHERE name LIKE %张没法走索引因为 B Tree 定位的时候必须知道前缀。WHERE name LIKE 张%就能走。如果业务确实需要后缀模糊匹配考虑引入搜索引擎或者改用前缀索引思路重建字段不要在查询上硬扛。第四联合索引没有遵循最左前缀。前面已经详细讲过了这里不再展开。第五OR连接条件时有一个字段没有索引。WHERE user_id 10086 OR status 1如果status没有索引MySQL 可能选择全表扫描而不是分别走两个索引合并结果因为单独用索引再合并的成本往往比全表扫描更高。解决办法是给status也加索引或者优化器选择INDEX_MERGE。第六NOT IN、NOT LIKE和!这类否定操作大部分情况下也无法利用索引因为 B Tree 索引本质上是基于等值和范围有序匹配的否定操作很难直接定位区间。4.2 EXPLAIN输出如何判断SQL是否用到索引判断一条 SQL 有没有走索引不是靠猜也不是看执行时间而是直接看EXPLAIN输出。以最典型的字段来说type访问类型。从好到差依次是system const eq_ref ref range index ALL。看到ALL基本就是全表扫描了得警惕index是扫描了整棵索引树也不怎么样range开始就比较健康比如BETWEEN、、这类范围查询ref是等值查询走了普通二级索引const是主键或唯一索引等值查询性能最好。key实际使用的索引名称。如果为NULL就是没走任何索引。rows优化器预估需要扫描的行数。这个数字不精确但量级能说明问题几千和几十万完全是两个概念。Extra补充信息。看到Using filesort说明排序没走索引Using temporary说明用了临时表Using index是最理想的情况代表覆盖索引。举个例子EXPLAIN SELECT order_no, status FROM t_order WHERE user_id 10086 ORDER BY created_at DESC;如果执行结果的Extra里有Using filesort说明(user_id, created_at)这个组合索引没建或者字段顺序不对数据库只能用文件排序。这一条信息比任何性能调优文档都更能直接指导你该建什么索引。4.3 索引下推与优化器的那些细节MySQL 5.6 引入了索引下推Index Condition Pushdown简称 ICP这是个容易被忽视但很有用的优化。在没有 ICP 时如果组合索引是(user_id, status)查询条件是user_id 1000 AND status 1InnoDB 要先把所有user_id 1000的索引记录取出来回表再过滤status 1。有了 ICP存储引擎在读取二级索引时就同时判断status 1把不满足条件的记录直接过滤掉减少了回表次数。EXPLAIN的Extra里会显示Using index condition。另外一个细节是优化器对索引的选择并不总是最优的。MySQL 的优化器基于统计信息估算成本有时候明明有索引但它估算走全表扫描成本更低比如表很小、或者索引列区分度很低就会放弃索引。这时候先用ANALYZE TABLE t_order;更新统计信息再重新看执行计划。如果确实不该走索引也别强行FORCE INDEX那是最后的手段而且索引数据分布一变强制索引可能变成负优化。5. 常见问题与排查技巧实录把索引问题做进日常巡检5.1 索引碎片与维护为什么加了索引性能还是越来越差一个长期运行的线上表即使索引建得没问题性能也可能越来越差。这通常是索引碎片fragmentation和统计信息不准确导致的。InnoDB 的索引页在频繁删除、更新后可能出现大量碎片空间页利用率下降导致同样的数据量占用更多页扫描 IO 变高。可以通过information_schema查看索引的页数和碎片情况也可以用OPTIMIZE TABLE t_order;重建表并整理索引。不过OPTIMIZE TABLE在大表上会锁表不能直接在生产环境跑。我的经验是结合在线改表工具做或者选择低峰期操作。顺便说一个更省事的方案定期执行ANALYZE TABLE t_order;更新统计信息让优化器拿到更准确的基数估计。这个操作成本远低于OPTIMIZE日常巡检可以优先用好它。如果确认碎片严重再找窗口期做在线OPTIMIZE。5.2 针对慢SQL的排查流程与线上问题速查表在团队里我经常带新人做慢查询排查慢慢固定下来一套流程这里直接分享出来照着做基本能解决大部分问题在 RDS 或自建 MySQL 的慢查询日志里把慢 SQL 捞出来按执行次数和耗时排序。对目标 SQL 执行EXPLAIN先看type、key、rows、Extra判断是全表扫描、回表还是 filesort。对照索引设计原则检查是缺索引、索引字段顺序不对还是 SQL 写法导致索引失效。如果是索引失效优先改 SQL 写法——比如把函数运算改成范围条件、去掉隐式转换、调整联合索引的字段顺序。线上索引变更走在线 DDL 工具不要直接ALTER TABLE锁表。变更后回看执行计划和性能指标确认问题闭环。给你一个速查表对应常见问题直接查找方案场景现象处理方案无索引全表扫描typeALLrows巨大按查询条件建合适索引已有索引但未命中keyNULL先查索引失效原因再看统计信息是否需要更新排序慢ExtraUsing filesort调整索引顺序让排序字段跟查询走同一索引回表过多typeref但延迟高把查询返回列尽量收入覆盖索引联合索引未生效触发了最左前缀法则的例外重新设计组合索引字段顺序索引频繁更新UPDATE变慢精简索引数量删除冗余索引权衡读写比5.3 数据库同步与在线索引变更的影响如果业务用了主从复制或者接了数据库同步工具在线加索引的时候要格外小心。MySQL 的主从同步是逻辑复制主库执行 DDL 后从库也会执行同样的 DDL。MySQL 8.0 之前的 DDL 大部分需要获取元数据锁会阻塞该表的 DML 操作如果从库同步延迟本身偏高一个慢的ALTER TABLE可能让从库延迟进一步拉大进而影响线上读写分离的稳定性。我自己做线上索引变更的推荐姿势优先用gh-ost这类无锁在线变更工具它在主库上通过 binlog 同步的方式创建临时表逐步拷贝数据最后原子切换对线上影响小得多。如果没有这类工具至少在业务低峰期执行ALTER TABLE并把lock_wait_timeout设小一点避免长时间拿不到锁而阻塞。变更前最好在测试环境用同样量级的数据先跑一遍估算执行时间。记住索引是给查询加速的但加索引这个动作本身如果影响了可用性那得不偿失。MySQL 索引这件事看起来是几条 SQL 语法真正吃透之后会发现整个数据库优化的思路都清晰了。从 B Tree 的存储结构到聚簇索引和二级索引的配合再到组合索引如何覆盖高频查询每一步都需要结合你实际的表结构和业务查询来做决策。我在实操中最深的体会是没有任何一套索引设计规则能直接套用到所有项目核心方法就是多跑EXPLAIN、多观察真实查询、多做索引梳理慢慢你会形成一种直觉看到一条慢 SQL脑子里基本能判断出是缺索引、索引失效还是索引根本没设计好。最后再分享一个小习惯我每周五下午会花十分钟看一次慢日志和sys.schema_unused_indexes这个动作坚持了快两年线上因为索引导致的性能问题基本都在萌芽期就被干掉了。希望你也能养成这个习惯少踩几个我在生产环境里踩过的坑。