1. 先搞懂索引的本质为什么加了索引查询就快1.1 索引是数据目录不是玄学对 MySQL 稍有了解的读者应该都有这个经验一张表数据量上去之后查询慢得让人抓狂加个索引之后速度立刻起飞。但很多人对索引的理解停留在查询快这三个字上真要问为什么快、什么时候该加、什么时候加了反而坏事就答不上来了。索引在最底层就是一个数据结构MySQL 用它来加速数据的检索。你可以把它想象成一本厚书的目录——没有目录时你要一页一页翻全表扫描有了目录你直接翻到对应章节就行。MySQL 默认存储引擎 InnoDB 里索引采用的是 B 树结构这是数据库领域经过几十年验证的最优解之一。为什么不用二叉树、红黑树或者哈希表二叉树在数据量增大时会退化成链表红黑树虽然能自平衡但树高依然随数据量增长千万级数据下磁盘 I/O 次数会高到不可接受。哈希表查单条数据确实快但你没法做范围查询也没法做排序。B 树的优势在于所有数据都存在叶子节点并且叶子节点之间用指针串联既支持等值查询也天然支持范围查询和排序树高通常只有 3 到 4 层意味着最多几次磁盘 I/O 就能定位到目标数据。1.2 InnoDB 的聚簇索引结构这里有一个必须搞清楚的概念InnoDB 的表本身就是一棵 B 树。表数据文件按照主键构建了一棵聚簇索引树叶子节点直接存放整行数据。也就是说InnoDB 表必须有主键如果你没有显式指定MySQL 会隐式生成一个 6 字节的 rowid 作为主键。这个设计带来的结果很关键通过主键查询是最快的路径因为直接走聚簇索引就能拿到整行数据不需要额外的回表操作。而二级索引非聚簇索引的叶子节点存放的是主键值不是数据行本身所以通过二级索引查询时通常会先找到主键再回到聚簇索引树里取完整数据这个过程叫回表。理解这个结构之后很多问题就迎刃而解了。比如为什么主键不建议用 UUID 这种随机字符串因为聚簇索引树的叶子节点是按主键顺序排列的随机主键会导致频繁的页分裂和碎片写入性能会明显下降。而自增主键是顺序写入新数据总是追加到末尾性能最稳定。我处理过不少线上表主键从自增改成 UUID 之后写入耗时直接翻倍这就是聚簇索引结构带来的物理代价。2. 索引的类型与适用场景2.1 按功能划分的四种基本索引MySQL 里常见的索引类型按功能可以分成四种主键索引、唯一索引、普通索引、全文索引。它们的核心区别在于约束能力和适用场景。主键索引就是聚簇索引一张表只能有一个不允许为空也不能重复。唯一索引保证列值的唯一性但允许有一个 NULL 值这个细节在面试里经常被问到。普通索引没有唯一性约束纯粹是为了加速查询。全文索引主要用于大文本字段的模糊搜索比如文章内容的关键词匹配在 InnoDB 里 5.6 版本之后才开始支持。实际业务中唯一索引和普通索引的选择需要仔细权衡。写入端如果业务本身就保证数据唯一比如订单号那直接建唯一索引就行省去应用层的重复检查。但如果唯一性只是业务上偶尔需要不必为这个场景牺牲写入性能因为唯一索引每次插入都要额外做一次唯一性校验写入路径比普通索引更重。2.2 联合索引与最左前缀原则联合索引也叫复合索引是指在一张表的多个列上共同创建的索引。它的底层结构依然是 B 树但排序规则是先按第一个列排序再按第二个列排序以此类推。这就引出了最左前缀原则查询条件里必须包含联合索引的最左列索引才能被使用。举个例子假设表上有联合索引(a, b, c)那么以下查询条件走索引a 1、a 1 AND b 2、a 1 AND b 2 AND c 3。但b 2或c 3单独作为条件时索引无法生效。这条规则是 MySQL 索引面试题里出镜率最高的知识点也是实际开发中最容易踩的坑。最左前缀原则看似简单实际设计联合索引时却非常考验功力。我见过太多团队在(user_id, create_time)和(create_time, user_id)之间反复纠结其实判断标准很简单先想清楚业务里最频繁的查询条件是什么把区分度最高的列放在最左边同时考虑范围查询的列尽量往后放因为范围查询之后的列无法继续利用索引进行精确定位。2.3 覆盖索引与回表的取舍覆盖索引是一个很容易被忽视但收益巨大的优化手段。所谓覆盖是指查询所需的全部列都能从索引本身获取不需要回表。这要求索引列覆盖了 SELECT 的字段列表和 WHERE 条件中的所有列。我举个例子你就明白了。表user有字段id、name、age、email二级索引(name)的叶子节点存的是主键 id。执行SELECT id FROM user WHERE name 张三时从二级索引里直接就能拿到 id不用回表这个查询就是覆盖索引。但如果执行SELECT age FROM user WHERE name 张三二级索引里没有 age就必须回表查聚簇索引性能就多了一次 I/O。所以设计索引时不能光看 WHERE 条件还要看 SELECT 的字段。如果高频查询只用到几个固定字段可以考虑把这些字段加到索引里这就是索引列冗余的设计思路。但要注意索引不是越多越好每个索引都会增加写入开销和存储空间覆盖索引的设计要针对真正的热查询不要为了优化而优化。3. 索引创建与实战设计3.1 创建索引的完整 SQL 语法MySQL 创建索引主要有三种方式语法上略有差异但最终效果是一样的。-- 方式一建表时直接定义索引 CREATE TABLE user ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(64) NOT NULL, age INT NOT NULL, email VARCHAR(128) DEFAULT NULL, PRIMARY KEY (id), UNIQUE KEY uk_email (email), KEY idx_name_age (name, age) ) ENGINEInnoDB DEFAULT CHARSETutf8mb4; -- 方式二直接在已存在的表上创建索引 ALTER TABLE user ADD INDEX idx_name_age (name, age); -- 方式三使用 CREATE INDEX 语法 CREATE INDEX idx_name_age ON user (name, age);这三种方式我用得最多的是第二种因为线上的表结构变更通常不会重建整张表ALTER TABLE 语法直观而且可以和其它表结构变更语句放在一个变更脚本里执行。第三种CREATE INDEX在语义上和 ALTER 等价但有些自动化 DDL 平台对它的兼容性不如 ALTER所以团队协作时我会统一约定用 ALTER 方式。删除索引的语法是DROP INDEX idx_name ON user或者ALTER TABLE user DROP INDEX idx_name。这里要提醒一句线上删除索引前一定要先确认这个索引没有别的事务正在使用尤其在高并发环境下删索引是元数据操作可能引起短时间的锁等待。稳妥做法是在低峰期操作先观察一段时间业务日志确认没有依赖再删。3.2 联合索引字段顺序怎么排很多开发者在设计联合索引时会直接把 SQL 里 WHERE 条件出现的字段顺序抄一遍这是最常见的错误。字段顺序的排列要综合考虑三个因素等值条件优先、区分度大的列优先、范围条件放最后。假设业务里有这么一条高频 SQLSELECT * FROM order_info WHERE status 1 AND user_id 12345 ORDER BY create_time DESC;我见过很多同学一上来就建了一个(status, user_id, create_time)的三列索引理由是 SQL 里条件就是按这个顺序写的。但 status 字段的取值可能只有 0 和 1 两种区分度极低把它放在最左边会导致整个索引的选择性变差。更合理的方案是(user_id, status, create_time)。这样定位到某个用户后status 再过滤create_time 还能用来排序避免额外 filesort。这个顺序的调整对查询性能的影响有时候是数量级的差距。还有一个经验能用一个联合索引覆盖多个查询场景时就不要为每个场景单独建索引。比如(user_id, create_time)这个索引既能支持user_id 1 AND create_time 2024-01-01的范围查询也能直接支持user_id 1 ORDER BY create_time的排序场景一个索引解决两个问题。3.3 索引下推MySQL 5.6 之后的隐形优化索引下推Index Condition PushdownICP是 MySQL 5.6 引入的特性它允许 MySQL 在索引遍历过程中直接对索引包含的字段进行 WHERE 条件的过滤减少回表次数。举个例子联合索引(name, age)执行SELECT * FROM user WHERE name 张三 AND age 20。在没有 ICP 的旧版本里MySQL 先通过 name 定位到所有张三的主键回表拿到完整行再判断 age 是否大于 20。开启 ICP 之后age 判断在索引遍历阶段就完成了只有满足条件的记录才回表。这个优化对开发者是透明的不需要改任何代码。如果你想确认自己数据库是否开启了 ICP可以在 EXPLAIN 的 Extra 列里看到Using index condition字样。了解这个机制的意义在于联合索引设计时把范围条件的列放在最后配合 ICP 可以获得更好的过滤效果这进一步印证了前面说的字段顺序原则。4. 索引失效的坑这些场景明明有索引却不走4.1 函数操作与隐式类型转换索引失效的场景我列过一张清单每次做 SQL 评审都会拿出来对照一遍。其中出现频率最高的就是查询条件对索引列做了函数操作。-- 索引失效对 create_time 使用函数 SELECT * FROM order_info WHERE DATE(create_time) 2024-06-01; -- 索引生效使用范围查询替代函数 SELECT * FROM order_info WHERE create_time 2024-06-01 AND create_time 2024-06-02;第一个查询里MySQL 无法直接使用 create_time 上的索引因为索引树里存的是原始时间戳而查询条件是函数处理后的结果索引的有序性被破坏了。正确做法是改成范围查询也就是第二个写法。这个改写方案是我在大量 SQL 优化里最常用的招数效果立竿见影。隐式类型转换同样危险。比如手机号字段在表里是varchar类型查询时写成WHERE phone 13800138000MySQL 会把字符串列隐式转成数字比较你的索引可能直接失效。这个问题在代码 Review 阶段很难发现因为数据量小的时候全表扫描也能跑得很快数据量一大就爆发了。4.2 最左前缀违反与范围查询的截断效应最左前缀违反导致的索引失效属于设计层面的问题会话中说b 2单独查不走索引本质上是因为 B 树的排序规则决定了它无法跳过最左列直接定位。但很多人不知道的是范围查询对联合索引的影响也很大。假设联合索引是(a, b, c)查询条件a 10 AND b 5因为 a 是范围条件b 只能做到索引内过滤但索引原本可以精确命中 a 和 b 的路径已经断了这就是范围查询的截断效应。我处理过一个真实案例一张订单表有(status, pay_time)联合索引线上有个查询条件是status 1 AND pay_time 2024-01-01 AND type 2type 字段没有索引导致每次查询都要从大量满足前面条件的记录里再过滤一遍。后来我把索引改成(status, type, pay_time)把等值条件的 type 放中间范围条件的 pay_time 放最后查询耗时从 800ms 降到了 30ms。4.3 优化器为什么不选索引还有一个让人头疼的场景索引明明存在查询条件也完全匹配但 EXPLAIN 显示走了全表扫描。这不是索引失效而是优化器认为走索引不如全表扫描快。最常见的触发条件是查询返回的行数占表总行数比例过高。比如表里 1000 万条数据一个查询要返回其中 30% 的行这时候全表扫描可能只需要顺序读而走索引还要大量回表随机 I/O 的开销更大。MySQL 优化器有成本模型会根据表的统计信息估算代价当它认为全表扫描代价更小时就会放弃索引。这种情况下你很难通过改 SQL 来强制走索引除非用FORCE INDEX提示但我不建议这么做因为统计信息变化后FORCE INDEX 可能让计划更差。更合理的做法是重新审视业务需求看看能不能缩小查询范围或者优化表本身的统计信息。另外LIKE %keyword%这种前置通配符的模糊查询也是永远走不了索引的需要配合全文索引或者外部搜索引擎解决。5. 用 EXPLAIN 定位索引问题5.1 EXPLAIN 关键字段解读排查索引问题第一工具永远是 EXPLAIN。它不会真正执行 SQL而是基于优化器的执行计划告诉你这条 SQL 会怎么跑。我平时看 EXPLAIN重点看四个字段type、key、rows、Extra。type 字段表示访问类型从好到差大致是systemconsteq_refrefrangeindexALL。看到 ALL 就说明是全表扫描需要警惕。看到 index 也不是好现象它代表全索引扫描虽然没用全表但同样遍历了整棵索引树。key 字段显示实际使用的索引名可以为 NULL为 NULL 表示没用索引。rows 是优化器估算的扫描行数这个值越小越好。Extra 字段里如果出现Using filesort或Using temporary都要重点排查前者说明排序没用上索引后者说明查询涉及临时表。我一般会用EXPLAIN ANALYZE查看实际执行耗时和行数5.7 之后的版本还支持EXPLAIN FORMATJSON看更详细的成本分析。不过对大多数日常问题标准 EXPLAIN 的四个字段已经够用了。5.2 一个真实排查案例有一次线上用户反馈一个列表接口突然变慢原来 100ms 以内后来涨到了 2 秒以上。我先抓了慢查询日志看到这样一条 SQLSELECT order_no, amount, create_time FROM order_info WHERE seller_id 10086 ORDER BY create_time DESC LIMIT 20;表上有seller_id单列索引查询条件也命中了但 EXPLAIN 结果显示 Extra 列有Using filesortrows 估算有 40 多万行。问题出在 ORDER BY create_timeMySQL 先用 seller_id 索引找到 40 多万行主键回表拿到 create_time再在内存里排序取前 20 条。排序消耗成了主要瓶颈。修改方案是把索引改成(seller_id, create_time)这样索引本身就是按 seller_id 分组、组内 create_time 有序排序操作直接省掉。改完之后接口耗时降到 30ms 左右。这个案例给我的启示是联合索引的设计不能只看 WHEREORDER BY 和 GROUP BY 字段同样要纳入考虑范围。5.3 索引维护与碎片处理索引不是建好就一劳永逸的。随着业务数据的频繁增删改索引树会产生碎片页的利用率下降查询性能会逐渐变差。InnoDB 里OPTIMIZE TABLE可以用来重建表和索引但这个操作会锁表线上大表直接执行风险很高我一般不建议在业务高峰期跑。更稳妥的做法是使用一种在线 DDL 工具或者把索引维护放到低峰期进行。MySQL 5.6 之后支持ALGORITHMINPLACE, LOCKNONE的在线 DDL7.0 之后的版本甚至可以快速加索引。日常维护节奏上我习惯每季度检查一次大表-- 查看表大小和索引碎片情况 SELECT TABLE_NAME, ROUND(DATA_LENGTH/1024/1024, 2) AS data_mb, ROUND(INDEX_LENGTH/1024/1024, 2) AS index_mb FROM information_schema.TABLES WHERE TABLE_SCHEMA your_db ORDER BY data_mb DESC;另外要注意ANALYZE TABLE也很重要。它会重新统计索引的基数分布帮助优化器做出更好的计划。有时候查询计划莫名其妙变差先别急着改 SQL执行一次 ANALYZE 可能就解决了。6. 面试高频问题与避坑清单6.1 面试官常问的索引问题MySQL 索引是面试中的必考题我整理了几道出现频率极高的问题答案都用上面讲到的原理来组织。第一道为什么 MySQL 用 B 树而不是 B 树核心差异是 B 树的数据全部存在叶子节点且叶子节点之间有序连接。这带来两个优势范围查询可以直接沿链表遍历不需要中序遍历树结构非叶子节点可以存放更多键值树更矮磁盘 I/O 更少。第二道什么是回表什么时候需要回表二级索引找到主键后再通过主键去聚簇索引取整行数据的过程就是回表。当查询的列不在二级索引中时就需要回表解决办法是设计覆盖索引。第三道唯一索引和普通索引如何选择如果业务需要唯一性约束直接建唯一索引如果只是查询加速就建普通索引。唯一索引在插入时需要额外检查唯一性写入稍微慢一点但这是特性的代价不算问题。第四道索引失效的场景有哪些我前面让你记的那些坑都可以拿来作答函数操作、隐式类型转换、最左前缀违反、范围查询截断、LIKE 前置通配符每一条最好都能说出具体的 SQL 例子。6.2 我总结的索引设计检查清单最后把我这些年积累的索引设计检查项整理成一张清单每条都是踩过坑换来的。每张表必须有显式主键优先自增整型业务上有自然唯一键也可以作为主键但不要用 UUID。联合索引字段顺序等值条件放前范围条件放后区分度高的列优先。高频查询尽量设计覆盖索引避免不必要的回表。控制单表索引数量一般不超过 5 个索引不是越多越好写入频繁的小表尤其要克制。字符串字段要关注字符集utf8mb4下每个字符占 4 字节索引长度容易超限必要时用前缀索引。大文本字段不要直接建索引考虑前缀索引或者独立表存储。定期检查慢查询日志用 EXPLAIN 逐个分析不要等问题暴露了才去处理。大表 DDL 操作要评估锁和 I/O 影响尽量使用在线 DDL在低峰期执行。6.3 最后分享一个小技巧在使用SHOW INDEX FROM table_name查看索引信息时会有一个Cardinality字段它表示索引列的基数估算值也就是这一列有多少个不同的值。Cardinality 相对表行数越接近 1说明列区分度越高适合做索引前缀列如果 Cardinality 特别小比如性别这种只有两三个取值的列建索引的意义就很有限即使建了优化器也可能不走。很多人在设计索引时只看业务字段是否常用忽略了区分度这个维度结果建了索引却长期闲置。我的习惯是设计索引前先用一条 SQL 验证一下候选字段的区分度SELECT COUNT(DISTINCT column_name) / COUNT(*) AS selectivity FROM table_name;这个比值大于 20% 的字段才值得考虑放在联合索引左侧。行数特别大的表一次 COUNT 可能比较耗时可以抽样统计结果也足够指导设计了。索引这块知识看起来基础但越深入越发现每个细节都和底层数据结构挂钩。理解 B 树的组织方式再看最左前缀、回表、索引失效这些问题就不会觉得是死记硬背的规则而是顺理成章的结论。我个人的体会是把原理吃透再结合实际 EXPLAIN 分析索引设计和 SQL 优化就不会再靠猜了。
