做SQL优化这些年我最大的体会就是索引这东西谁都会建但真正能用好的人不多。很多人一遇到慢查询第一反应是加个索引结果加了之后发现查询还是慢甚至更慢了去面试的时候被问为什么InnoDB要用B树、联合索引最左前缀到底是怎么回事又讲不清楚。这篇文章我就把自己在实际工作中对 MySQL 索引的理解、踩过的坑、以及性能调优的经验完整梳理一遍不绕弯子直接把最关键的原理和实操讲透。这篇文章适合三类人刚入行想系统搞懂索引原理的新人写完 SQL 总被领导说慢的开发者以及准备 MySQL 面试、想真正搞懂索引底层而不是背八股的同学。看完之后你至少能解决三个问题索引为什么能加速查询、索引在什么场景下会失效、以及面对一条慢 SQL 时如何设计出合理的索引。1. 索引的本质为什么一棵B树能让查询快几个数量级1.1 索引到底解决了什么问题先想一个朴素的问题如果没有索引MySQL 执行一条WHERE查询是怎么做的答案是全表扫描——从表的第一行开始一行一行地比对字段值直到找到所有满足条件的记录。假设表里有 1000 万行数据平均要找 500 万行才能命中这个成本是线性增长的数据量越大越慢。索引的作用就是把这种线性查找变成树形查找。这就好比查字典没有索引等于从第一页翻到最后一页有索引等于先查拼音或者偏旁部首定位到大概位置再翻过去精确定位。对于 B树这种数据结构1000 万条记录只需要大概 3 层就能定位到具体的数据页每次查找的磁盘 IO 次数就是树的层数几乎是个常数。1.2 为什么偏偏是B树哈希、二叉树、红黑树不行很多人背过结论MySQL 用 B树但对为什么不用其他结构一头雾水。这里我把几个候选结构的对比讲清楚理解了这一点面试问原理你就能自己推出来。哈希索引按哈希值直接定位单条等值查询确实快O(1) 的复杂度。但哈希表的致命缺陷是无法支持范围查询WHERE age 20 AND age 30这种 SQL 直接歇菜。而且哈希值是无序的排序也没法走索引。所以哈希索引只适合 Memory 引擎和 InnoDB 的自适应哈希索引这种特定场景不能作为通用存储结构。二叉树 / 红黑树虽然能支持范围查询但树的高度太高。二叉树在最坏情况下退化成链表红黑树虽然是平衡的但一个 1000 万节点的红黑树高度约是 23 层。这里有个关键点InnoDB 是磁盘存储引擎树的每一层对应一次磁盘 IO树越高查询次数越多。23 次磁盘 IO 和 3 次磁盘 IO 的差距在机械硬盘时代是数量级的差别。B树 B 树把二叉树变成了多叉树每个节点可以存多个键值树的高度大大降低这是进步。但 B 树的每个节点都存数据导致单个节点存不了多少索引键而且 B 树的叶子节点之间没有指针连接做范围查询时还得回到父节点去寻找相邻的叶子节点效率没有 B树高。最后看 B树的设计简直是为磁盘存储量身定做的非叶子节点只存索引键不存数据所以一页默认 16KB能放下非常多的键树的高度控制得很低。一个 3 层的 B树能存上千万条记录。所有数据都存在叶子节点上并且叶子节点之间用双向链表串起来范围查询和排序走链表的顺序访问效率极高。每一次节点访问就是一次磁盘 IO而节点的大小正好对应一页的 16KB和磁盘 IO 的最小单位对齐。这个为什么用 B树是索引问题里最值得掰开揉碎讲的因为它决定了你对后续所有索引行为的理解深度。1.3 InnoDB和MyISAM的索引存储差异同样是索引不同存储引擎在实现上有本质区别。MyISAM 的索引和数据是分开存的索引文件.MYI里叶子节点存的是指向数据行的物理地址这叫做非聚簇索引。InnoDB 的聚簇索引主键索引的叶子节点直接保存整行的数据数据和索引是一体的。这个差异直接导致了一个重要结论InnoDB 表必须有主键。没有显式主键InnoDB 也会找一个非空的唯一键再找不到就隐式生成一个 6 字节的 rowid 当作主键。这也是为什么业界一直强调InnoDB 表要建自增主键——因为聚簇索引的叶子节点按主键值有序排列自增可以保证插入走末尾减少页分裂和碎片。2. 索引的分类主键索引、二级索引、复合索引到底怎么区分2.1 按功能划分的四大索引类型先看最实用的分类维度——按照索引的功能和约束来分这也是建表时经常碰到的。索引类型特点典型应用场景主键索引PRIMARY KEY唯一且非空一个表最多一个每张表的核心标识字段如用户表 id唯一索引UNIQUE索引列的值必须唯一允许 NULL手机号、身份证号、订单号等业务唯一字段普通索引INDEX没有任何唯一性约束只是加速查询频繁出现在 WHERE、JOIN、ORDER BY 中的字段全文索引FULLTEXT用于全文检索对文本内容分词匹配文章内容、商品描述等大文本字段的模糊搜索主键索引和唯一索引的区别经常有人搞混主键索引本质上是非空的唯一索引而且一个表只能有一个主键但可以有多个唯一索引。唯一索引允许 NULL但需要注意MySQL 中唯一索引对 NULL 做了特殊处理——多个 NULL 值可以共存因为 NULL 不等于任何值包括它自己。2.2 聚簇索引与非聚簇索引回表问题的根源前面提到了聚簇索引和非聚簇索引这里把概念补完整。聚簇索引clustered index在 InnoDB 里特指主键索引叶子节点存的是完整的数据行。因为数据行和索引存在一起所以表的数据物理顺序是按主键排序的。非聚簇索引secondary index也叫二级索引叶子节点不存完整数据只存索引列的值 主键值。这里有个非常重要的推论通过二级索引查数据需要先查二级索引找到主键值再用主键值去聚簇索引里查找整行数据。这一步就叫回表。举个例子。表结构是(id PRIMARY KEY, name, age)你在name字段上建了普通索引。执行SELECT * FROM t WHERE name张三时MySQL 的完整动作是在 name 索引树中搜索找到 张三 对应的主键值 id拿着 id 去主键索引聚簇索引中搜索找到完整的数据行返回。这就多了一次 B树搜索。如果查询只涉及name和id两个字段就可以直接利用索引里存的值返回不用回表这就是后面要说的覆盖索引优化。2.3 联合索引与最左前缀原则高频面试点联合索引也叫复合索引是指在一个索引中包含多个列比如INDEX idx_name_age (name, age)。为什么需要联合索引因为一个查询如果同时用两个条件单独建两个单列索引的效果很不理想。MySQL 8.0 之前虽然有 index merge 优化但更多时候是让优化器从多个单列索引里挑一个用另一列回表过滤而联合索引一棵树同时照顾了两个条件效率完全不同。联合索引的核心规则是最左前缀原则idx_name_age在查找时会先按 name 字段排序name 相同的再按 age 排序。所以WHERE name 张三—— 走索引因为 name 是最左列WHERE name 张三 AND age 20—— 走索引两个条件都用上了WHERE age 20——无法走索引因为跳过了最左列 name。最左前缀原则对于一个从业者来说不仅是理论更是设计的直接依据联合索引的字段顺序决定了这个索引能不能被你的 SQL 用上。如果你把age放前面、name放后面那对WHERE name ?这条高频 SQL 来说索引就白建了。那排序到底该遵从什么顺序两个最实用的参考规则一是把等值查询的字段放在前面二是按照区分度从高到低排。等等这里有冲突怎么办比如 name 区分度高、age 区分度低但查询时 name 是等值条件、age 是范围条件这种情况下建议优先把等值条件字段放前面。这个规则后面在索引设计经验里细化。3. 索引操作全解创建、查看、删除与实操避坑3.1 创建索引的五种正确姿势实际建索引的 SQL 写法有好几种我按常用程度列一下并说清各自的适用场景。-- 方式1建表时直接定义索引适合新表 CREATE TABLE user ( id BIGINT PRIMARY KEY AUTO_INCREMENT, phone VARCHAR(20), name VARCHAR(50), age INT, KEY idx_phone (phone), UNIQUE KEY uk_phone (phone), INDEX idx_name_age (name, age) ); -- 方式2ALTER TABLE 添加索引适合已有表加索引 ALTER TABLE user ADD INDEX idx_age (age); ALTER TABLE user ADD UNIQUE INDEX uk_phone (phone); -- 方式3CREATE INDEX 独立建索引最直观推荐日常使用 CREATE INDEX idx_name ON user (name); CREATE UNIQUE INDEX uk_phone ON user (phone); CREATE INDEX idx_name_age ON user (name, age); -- 方式4前缀索引针对长字符串字段 -- 只对字符串的前 N 个字符建索引大幅减少索引体积 CREATE INDEX idx_content_prefix ON article (content(20)); -- 方式5降序索引MySQL 8.0 支持应对倒序排序场景 CREATE INDEX idx_age_desc ON user (age DESC);这里我特别想提醒一个实操上的点在大表上建索引要格外小心。MySQL 8.0 支持了在线 DDLALGORITHMINPLACE建索引期间不会阻塞正常读写但也不是完全没有代价。选择业务低峰期操作、观察主从延迟、建完立即用 EXPLAIN 验证是否命中这三步缺一不可。我之前遇到过建一个大索引导致主从延迟到分钟级的案例后来都是先在从库上建好、再切换主从安全很多。3.2 复合索引的创建原则一个索引顶三个索引为什么说复合索引设计得好一个索引可以顶三个因为最左前缀原则的存在INDEX(a, b, c)能同时覆盖(a)、(a,b)、(a,b,c)三种查询条件组合。这意味着在设计联合索引时能用一个复合索引 合适字段顺序解决的问题就绝不为每个单列单独建索引否则既浪费空间又拖慢增删改的速度。我总结一个复合索引字段顺序的决策流程你可以直接照着做先看等值查询所有条件字段按区分度从高到低排列再看范围查询范围字段、、BETWEEN、LIKE放在等值字段之后因为范围条件之后的其他列无法用于定位只能做过滤最后看排序字段如果 SQL 里要 ORDER BY 某个字段而这个字段不在查询条件里可以把它加在索引末尾让索引直接提供排序结果避免 filesort。这个决策流程里排最后一条的话ORDER BY 的使用要具体情况具体分析通常是在它的出现恰好落在最左前缀的末尾时能达到覆盖排序的效果。我自己建索引的习惯是先模拟跑一遍 EXPLAIN看 key、rows、Extra 三列再决定字段要不要调整。EXPLAIN 不会骗人它是索引设计里最重要的反馈工具。3.3 查看和删除索引别建了一堆没用的-- 查看表上的所有索引 SHOW INDEX FROM user; -- 查看建表语句同时也包含索引信息 SHOW CREATE TABLE user; -- 删除索引 DROP INDEX idx_name ON user; -- 或者等价 ALTER TABLE user DROP INDEX idx_name;SHOW INDEX FROM user的结果里Cardinality 这一列比较重要它表示索引中不同值的基数估算。Cardinality 除以表的行数可以粗略地判断索引的区分度。如果区分度太低比如性别字段这个索引基本没什么用查询优化器大概率不会选它。如果你发现一个有索引的字段执行计划里还是 full table scan先看这个索引的 Cardinality很可能是统计信息过期或者本身区分度太差。另外分享一个我常用的检查脚本把数据库中所有索引查出来看看有没有重复索引。重复索引的症状是idx_name和idx_name_age都存在其中 idx_name 就是冗余的因为 idx_name_age 的最左前缀已经覆盖了 name 单列查询。这种重复索引除了浪费空间还会让 INSERT、UPDATE 的维护成本翻倍清理掉对性能有明显正面效果。4. 索引失效的十大场景每一个都是真实踩过的坑4.1 索引失效场景全清单这里把我在实际排查 SQL 慢查询时最常见的索引失效原因系统整理一遍。每一条都是真金白银的教训建议收藏。序号失效场景错误示例正确写法1联合索引违反最左前缀WHERE age 20而索引是 (name, age)优先保证最左列 name 在条件中2对索引列使用函数或计算WHERE LEFT(name, 1) 张改写为WHERE name LIKE 张%3隐式类型转换索引列是字符串条件传了数字保持类型一致或显式 CAST4LIKE 以通配符开头WHERE name LIKE %张%能换成张%就换否则考虑全文索引5OR 条件中有非索引列WHERE age 20 OR status 1用 UNION 拆成两个查询6范围查询右边列失效索引 (age, name) 且WHERE age 18 AND name 张把等值列放前面范围列放后面7NOT IN / NOT EXISTSWHERE status NOT IN (1,2)改写为 LEFT JOIN 或 EXISTS 的肯定形式8IS NULL / IS NOT NULL索引列IS NOT NULL造成全扫给字段设默认值避免 NULL 判断9排序与索引顺序不一致ORDER BY age DESC 而索引是 (age ASC)建降序索引或让排序方向一致10优化器判定全表扫更便宜返回行数超过表行数的 20% 左右优化 SQL 逻辑缩小结果集4.2 逐个拆解失效原因与规避方案函数和计算导致的失效是初学者最容易踩的坑。WHERE DATE_FORMAT(create_time, %Y-%m-%d) 2024-01-01看着没毛病但函数包裹了索引列之后B树里存的是原始值根本无法按函数处理后的结果做二分查找索引自然就废了。正确写法是把范围条件转换到列本身WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00。MySQL 8.0 虽然引入了函数索引CREATE INDEX idx ON t ((DATE(create_time)))但那是建在函数表达式上不是对已有列搞函数转换。隐式类型转换是我见过线上事故率最高的坑。表里 phone 列是 VARCHAR但代码里传参写成了数字WHERE phone 13800138000MySQL 会把字符串列转成数字去比较索引直接失效。排查这种问题有个小技巧执行EXPLAIN SELECT * FROM user WHERE phone 13800138000看 type 列如果是 ALL 或 rows 特别大优先怀疑类型不一致。把 SQL 改成WHERE phone 13800138000索引立刻就能用上。OR 条件失效很多人不理解觉得两边都有索引为什么不用。MySQL 的处理逻辑是OR 要返回结果集的并集如果其中一个条件没法走索引就必须全表扫才能得到完整结果。所以要么保证 OR 两侧都有索引且能命中要么改写为 UNION-- 改写前status 无索引导致整个条件全表扫 SELECT * FROM t WHERE name 张 OR status 1; -- 改写后两个查询各走各的索引 SELECT * FROM t WHERE name 张 UNION SELECT * FROM t WHERE status 1;范围查询右侧列失效的逻辑稍微绕一点。联合索引(age, name)SQL 是WHERE age 18 AND name 张三。因为 age 是范围条件B树定位到 age 18 这个区间后在这个区间内 name 是无序的无法用二分继续定位只能逐条过滤。所以范围字段之后的列在索引里有等于无这就是最左前缀 范围列终结原则。4.3 优化器的心思为什么有时候有索引却不用最后一个场景最气人明明字段有索引EXPLAIN 一看还是全表扫。但这其实是优化器的成本决策不一定是索引坏了。比如你要查WHERE sex 男如果表里 90% 都是男性走索引找完 90% 的行再回表读取成本反而比全表扫高。优化器一估算直接全表扫完事。这种情况怎么破有几个思路一是查一下SHOW INDEX里的 Cardinality 是不是统计信息过期执行ANALYZE TABLE t刷新统计二是看 SQL 里是否真的能缩小结果集比如加时间范围三是用FORCE INDEX强制走索引试试但这个要谨慎只在充分验证后使用。大多数时候问题不在索引而在 SQL 本身需要优化。5. 索引优化实战覆盖索引、索引下推与MRR的底层提速逻辑5.1 覆盖索引一条SQL免掉回表查询速度直接起飞覆盖索引的定义很简单查询的列和 WHERE 条件都能在二级索引里找到不需要回表。因为二级索引的叶子节点存了索引列和主键值只要查询涉及这些列返回结果可以直接从索引里取。举个例子。表t(id, name, age)有索引idx_name (name)。执行SELECT id, name FROM t WHERE name 张三;这个查询需要的就是 name索引列本身和 id叶子节点存了主键值所以执行计划里 Extra 会显示Using index表示不需要回表代价非常小。但如果SELECT * FROM t WHERE name 张三要取 age 字段索引树里没有就必须回表。实际优化时我经常用覆盖索引来降维打击慢查询把高频查询涉及的字段塞进联合索引里让整个查询全程走索引不回表。比如一个订单表高频查询是按用户查订单号和金额那就建(user_id, order_no, amount)联合索引查询直接全部覆盖线上性能提升非常明显。5.2 索引下推ICP减少回表次数的隐形优化索引下推Index Condition Pushdown很多人不理解其实一句话就能讲明白本来要回表之后才能用 WHERE 条件过滤的行现在在索引遍历的时候就直接过滤掉了。看例子联合索引(name, age)SQL 是SELECT * FROM t WHERE name LIKE 张% AND age 20。MySQL 5.6 之前没有 ICP执行流程是先在索引里找到所有 name 以 张 开头的记录的主键然后逐个回表回表之后再用 age 20 过滤。如果 name 前缀匹配了 1 万行就要回表 1 万次再过滤。有 ICP 之后在遍历二级索引时发现索引里同时有 age 字段直接判断 age 是否等于 20不满足的就跳过只有满足的才回表。这样回表次数从 1 万次降到几十次。对用户来说ICP 是 MySQL 默认开启的优化不需要手动干预。但你知道了这个原理就能理解为什么联合索引把过滤字段加全、比建完索引再拿所有行去回表过滤要高效得多。5.3 MRR优化把随机IO变成顺序IOMRRMulti-Range Read的核心思想是回表的时候不要一条主键一次随机 IO而是把一批主键排序后顺序去聚簇索引里读数据。磁盘顺序读比随机读快几个数量级所以 MRR 对大量回表的场景优化效果很可观。MRR 由 MySQL 自动判断是否启用受mrron和mrr_cost_basedon控制。当二级索引返回的主键值本身比较分散时MRR 收益很大如果二级索引扫描本身就是有序的MRR 反而会带来额外排序开销优化器会根据成本决定是否启用。理解 MRR 的价值在于它和覆盖索引是两套互补的思路覆盖索引是干脆避免回表MRR 是让回表变快。5.4 索引设计黄金法则来自真实案例的总结写到最后把我在多个线上项目里总结出的一套索引设计规则整理出来。这不是面试八股是能直接落地的经验。单表索引数量控制在 5 个以内不含主键太多会导致写放大严重区分度低于 20% 的字段不要单独建索引性别、状态这种只能作为联合索引的一部分长字符串用前缀索引但要测出合适的前缀长度比如区分度最接近完整列的长度频繁更新的字段慎建索引每一次 UPDATE 都要同步维护索引树无法避免 IS NULL 判断时给字段设置默认值让索引列非 NULL化索引设计必须结合真实 SQL凭空设计索引不如不设计用慢查询日志 EXPLAIN 驱动索引调整每建一个索引都要问自己这个索引能不能覆盖/消除现有的某个索引避免冗余。6. 高频踩坑实录与索引面试题速查6.1 我亲身经历过的三个索引事故分享三个真实踩坑经历。第一个是把索引建错了方向一个统计报表 SQL 按时间倒序查询索引是升序的结果每次查询都触发 filesort几百万行数据排序直接卡死。后来在 MySQL 8.0 里改成降序索引秒级出结果。当时没意识到 ORDER BY DESC 和 ASC 对索引的利用完全不一样这是最常见的性能陷阱之一。第二个是联合索引字段顺序拍脑袋定的上线后发现最核心的查询走了全表扫。原因很简单SQL 里用的是 B 字段等值 A 字段范围而我们把 A 放前面、B 放后面范围列左侧的字段没法用。最后调整索引顺序为(B, A)问题立刻消失。字段顺序不是写的时候顺手排的而是要对着真实 SQL 的需求排。第三个是全表数据量只有几万行但 WHERE 字段建了索引却始终不走。后来发现是表的字符集是 utf8mb4代码里传参连接字符串时被转成了 latin1导致隐式类型转换 字符集不一致双重失效。排查了很久才发现。连接参数和表字符集不一致也会导致索引失效这个点特别隐蔽分享给各位避坑。6.2 面试官最爱问的索引问题附回答要点把面试里最高频的索引问题整理成速查表供快速回顾。问题回答要点为什么 InnoDB 用 B树不用哈希/红黑树磁盘 IO 次数树高、范围查询、页大小对齐聚簇索引和二级索引的区别叶子节点是否存整行数据二级索引要回表什么是最左前缀原则联合索引按定义顺序匹配跳过最左列会失效覆盖索引怎么避免回表查询列和条件都包含在索引里Extra 显示 Using index什么是回表和索引下推回表是二次查找ICP 是索引层提前过滤减少回表索引怎么设计才算合理区分度 等值在前 范围在后 避免冗余大表加索引要注意什么在线 DDL、低峰期、从库先行、验证执行计划关于索引失效场景再补充两个容易被忽略的一是ORDER BY字符串字段时如果没有与索引列顺序一致会产生 filesort二是JOIN时连接字段两边字符集不一致也会让连接条件里的索引失效。面试时能答出这两个细节通常能让面试官觉得你是真在实战中调过优的。最后再分享一个小技巧遇到一条慢 SQL我从来不先猜原因第一件事就是EXPLAIN SELECT ...先看 type 是否到ref或range再盯rows估算扫描行数和Extra里有没有Using filesort或Using temporary。这三板斧能定位掉 80% 的索引问题。剩下的 20%多半是统计信息过期、数据分布剧烈变化或者并发锁竞争需要结合SHOW PROFILE和实际业务数据继续分析。索引是 MySQL 性能优化的核心杠杆掌握好它你写出的 SQL 会进入另一个层次。