索引这事儿真不是建得越多越好。我见过太多开发同学一口气把 WHERE 条件里的字段全建上索引结果查询没快多少写入倒是慢得感人磁盘也涨了一大截。也有同学遇到慢查询就甩锅给数据库殊不知 EXPLAIN 一看索引压根没走对。这篇文章不打算念手册而是从索引为什么能快什么时候会失效实际该怎么设计这几个角度把我这些年调优 MySQL 攒下来的经验一次性倒出来。1. 索引的本质为什么它能让查询快几个数量级很多教材一上来就画 B 树看得人头大。要理解索引先抛开树结构想一想 MySQL 最原始的活儿是什么它把数据一行一行存放在磁盘上你要找一条满足条件的记录最笨的办法就是把整张表从头到尾扫一遍。比如一张 2000 万行的订单表按user_id10086去查没索引就得读 2000 万行数据做比对一次全表扫描的 I/O 能到几百毫秒甚至秒级。索引的本质是用空间换时间搞出一份精简的目录。这份目录只存少量关键列比如 user_id和一个指向实际数据行的指针在 InnoDB 里是主键值按大小排好序。排序的意义在于查找一条数据从逐个比对变成折半查找复杂度从 O(n) 直接干到 O(log n)。2000 万行数据log₂(2000万) 大约是 24 次比较配合 B 树的磁盘读取特性查询时间往往能从秒级降到毫秒级。那为什么 B 树这么受 InnoDB 偏爱关键在矮胖二字。B 树的每个节点可以存很多个索引键一棵 3 层到 4 层的树就能放下千万级数据。查询时最多读三四次磁盘就能定位到叶子节点的记录省下了天文数字般的 I/O 开销。而且叶子节点之间通过双向指针串成了有序链表这让范围查询比如WHERE create_time BETWEEN ...和排序遍历变得极其廉价——找到下限后顺着链表往后挪就行不用反复从根节点重新开始。索引字面意义上是个目录但在 InnoDB 里有个容易忽略的细节目录和数据行其实是可以分开存的。索引分两大类一类是聚簇索引clustered index就是数据表本身按主键排序组织索引的叶子节点存的是完整的一行数据另一类是二级索引secondary index叶子节点存的是主键值拿到主键后再回聚簇索引里去取整行。这就引出一个非常重要的概念——回表。举个例子表上有索引idx_user_id执行SELECT * FROM orders WHERE user_id 10086。MySQL 先在二级索引里找到所有 user_id 等于 10086 的主键值再拿着这批主键去聚簇索引里把整行数据捞出来。如果命中了 1000 行就要回表 1000 次。回表本身不慢但每回一次都要走一次 B 树查找批量回表的成本就上去了。理解了回表你才能理解为什么覆盖索引后面会专门讲能带来那么夸张的性能提升——数据全在索引里压根不用回表。还有一个实际点必须提索引不是你建了就一定生效的。MySQL 的优化器会综合判断表的数据量分布、索引的区分度、当前要查的代价决定到底走哪个索引还是干脆全表扫。有时候你觉得天经地义的索引优化器甩都不甩你。所以真正要学的不是怎么建索引而是怎么配合优化器让索引真正被用上。2. WHERE 条件 a AND b 该怎么建索引联合索引设计的最左前缀原则热搜里排名靠前的问题是mysql where条件a and b应该怎么建索引——这几乎是索引面试里最经典的一道题。正确做法是建联合索引(a, b)而不是分开建(a)和(b)两个单列索引。为什么因为 MySQL 一条 SQL 通常只能选一个索引来用你建了两个单列索引优化器挑一个走得快的另一个就凉在那儿白占空间。而联合索引把两个条件合并在一棵 B 树里a 和 b 一起参与检索效果是 112。但联合索引有个核心约束最左前缀原则。索引(a, b)的排序逻辑是先按 a 排a 相同的再按 b 排。这样一来它能直接支持WHERE a ?和WHERE a ? AND b ?但不支持单独WHERE b ?——因为 b 是第二顺序键在不知道 a 的前提下b 在整棵树里是乱序的无法直接定位。这不是 MySQL 的 bug而是 B 树排序结构的必然结果。最左前缀原则听起来简单实操里翻车的场景我倒可以举几个。第一个翻车场景是顺序搞反。有个真实案例是订单表上建了(status, create_time)的联合索引日常查询条件是WHERE status 1 AND create_time 2024-01-01效果还行。但后来加了个需求要按create_time做全局范围查询开发同学天真地以为已经有 create_time 在索引里了就能覆盖到结果 EXPLAIN 一看全表扫描。原因就是单独WHERE create_time ?无法走(status, create_time)索引——status 没给值最左前缀被拆断了。第二个翻车场景是范围条件放错位置。索引(a, b)里 a 用了范围查询、、BETWEEN那 b 还能参与排序定位吗答案是不能完全参与。MySQL 定位到 a 的范围区间后对这个区间内的所有 b 值只能做一遍线性检查无法利用 b 的有序性进一步跳过。我把一条 SQL 写出来你就明白了SELECT * FROM t WHERE a 100 AND b 5;如果索引是(a, b)MySQL 先按 a100 把区间切出来b5 的条件只能在区间内过滤。你可以把 b 的条件提到前面或者考虑把索引改成(b, a)——让 b 用等值定位a 做范围过滤这就能最大化利用两个条件。再说第三个场景等值优先原则。联合索引列的顺序设计有一条经验法则——区分度高的放前面等值条件放前面范围条件放后面。比如要查WHERE user_id ? AND status ? AND create_time ?索引建(user_id, status, create_time)。user_id 和 status 都是等值条件谁在前看区分度。user_id 区分度通常远大于 statusstatus 可能就两三个取值所以把 user_id 放最前面这样树的第一层就能切掉绝大多数数据status 接着等值匹配create_time 放最后做范围过滤。如果反过来把 status 放最前就算它命中率只有 50%也要在这个半区的数据里逐个比对 user_id白白多走一层。这里有一个仿真实战的做法值得分享。建索引前先跑一遍真实业务的所有核心查询把查询条件里的字段全部抄下来统计每个字段出现的频率和过滤比例区分度。过滤比最高的等值字段放第一位范围字段永远垫底。这样设计出来的联合索引通常能满足 80% 以上核心查询的需求剩下的再用单列索引或冗余索引补充。我自己维护过一个千万级会员表核心索引就是按这个流程定的后来半年没动过慢查询日志几乎清空。3. 索引失效排查为什么明明有索引EXPLAIN 却显示全表扫描网上关于索引失效的知识零零散散什么千万不要在索引列上做函数运算OR 会失效LIKE 通配符会失效但很少有人把背后的原理讲透。失效的本质只有一条你破坏了索引列值的可比较性或者你让优化器认为走索引的代价比全表扫更大。想通这一条就能自己推演各种场景不用死记硬背。3.1 索引列用了函数或表达式袋子里怎么找物品第一个高频失效场景在索引列上做了函数操作或运算。比如WHERE DATE(create_time) 2024-01-01或WHERE price * 1.1 100。索引树里存的是原始的 create_time 和 price 值MySQL 没办法直接拿函数处理后的结果去树里匹配。它只能把整个索引列的值全部提取出来逐个套上函数或表达式算一遍再跟条件比对。这一过程本质就是全量扫描索引的排序结构完全派不上用场。这种场景在日志表、流水表里特别常见。我维护过一个支付流水表台账 1 亿行运营同学每天看报表喜欢写WHERE DATE(create_time) CURDATE()。第一次见到 EXPLAIN 里 typeALL 我都懵了后来才反应过来是函数包了列。解决办法也很简单把条件改成范围查询WHERE create_time 2024-01-01 00:00:00 AND create_time 2024-01-02 00:00:00范围查询能直接走索引的 B 树搜索效率立竿见影。如果确实要按日期做复杂处理在 MySQL 8.0 里可以尝试函数索引CREATE INDEX idx_date ON t ((DATE(create_time)))但这算高级玩法了普通业务尽量先把条件改造成等值或范围。3.2 隐式类型转换字符串字段被喂了数字第二个容易踩坑的是隐式类型转换。字段是 VARCHAR查询条件却传了数字或者字段是 INT查询条件传了字符串。MySQL 会自动做类型转换但转换的方向很有讲究它通常会把字符串转成数字来比较。如果索引列本身是 VARCHAR条件值是 10086数字MySQL 会把索引列的所有字符串值都转成数字再比较等于给索引列套了个隐式函数索引自然失效。我在一次生产事故里就经历过这个用户表phone字段是 VARCHAR(20)SQL 里忘了加引号写成WHERE phone 13812345678。这条 SQL 在几百万行数据上硬生生扫了 1.8 秒接口超时报警。排查时一眼就看到了类型转换给 phone 补上引号后查询耗时降到 3 毫秒。那件事之后我定了个纪律代码评审里凡是看到索引列和条件值类型不一致的一律打回。这个习惯救了我后来很多次。3.3 LIKE 前置通配符和 OR 条件优化器的无奈选择LIKE 模糊查询分两种。WHERE name LIKE 张%可以走索引因为 B 树有序前缀匹配能直接定位到张开头的区间。但WHERE name LIKE %三%就惨了——以通配符开头字符串前缀不确定MySQL 没法用树结构定位只能把全表或全部索引扫一遍逐个匹配。业务上如果确实需要中后位置模糊搜索又对性能有硬性要求就该上全文索引或 ES 之类的搜索引擎这个后面有空单独写一篇。OR 条件失效也是老生常谈。WHERE a 1 OR b 2如果 a、b 分别在两个索引上MySQL 7.0 版本之后虽然能用 index merge合并两个索引的扫描结果但再早一些的版本会直接放弃索引全表扫描即使是支持 index merge 的版本当 OR 涉及三个以上条件或其中一个是非索引列时性能依然堪忧。最稳妥的做法是把 SQL 改写成UNION ALLSELECT * FROM t WHERE a 1 UNION ALL SELECT * FROM t WHERE b 2两个查询各自走各自的索引然后合并结果。改完之后 EXPLAIN 的 type 会从 ALL 变成 ref 或 range通常能快一个数量级。3.4 优化器觉得全表扫更划算索引基数Cardinality的陷阱最后一种失效最有欺骗性——它连 EXPLAIN 都不报错就是慢而且看起来索引明明存在。原因在于优化器判断这个索引区分度太低比如性别、状态这类字段总共就两种取值走索引回表反而比直接全表扫描更贵。你想想走二级索引要先读索引页找出所有符合条件的主键再回表读数据页如果索引命中了一半以上的行回表的随机 I/O 远高于顺序读全表优化器算这笔账算得门儿清。我见过一个管理员把表上的每个字段都建了索引其中就包括 status 这种只有 0/1 两种取值的字段。结果查询WHERE status 1时优化器直接选全表扫描这个索引压根没被用上还要承受每次写入时的索引维护开销。判断一个索引是否值得留可以用这条 SQL 看基数SHOW INDEX FROM table_name;主要看 Cardinality 列它表示索引列的不重复值数量。如果某列总行数是 2000 万Cardinality 只有 2这种索引建的毫无意义建议删掉。索引的设计思路应该是宁缺毋滥与其堆一堆没用的单列索引不如把每条 SQL 跑一遍 EXPLAIN确认哪个索引真的被用了、用得好再决定要不要留。4. 主键索引、唯一索引、普通索引别再傻傻分不清它们的工作机制完全不同热搜词里主键索引和唯一索引的区别这题几乎是面试必问。很多同学背了主键索引不能为 NULL唯一索引可以这种书皮答案但真正影响线上性能的差异远不止这一点。4.1 聚簇索引与二级索引的底层分歧主键在 InnoDB 里承载的角色太重了它不光是一个索引更是整张表数据的物理存储顺序。InnoDB 规定表必须有一个聚簇索引你建主键它就拿主键当聚簇索引你不建主键它会找第一个非空唯一索引当聚簇索引两个都没有它会生成一个隐藏的 6 字节 RowID 当聚簇索引。所以主键索引的叶子节点就是整行数据没有回表的说法这是它跟普通二级索引最根本的区别。唯一索引和普通索引在叶子节点上其实结构一模一样——都是二级索引除非它碰巧被选为了聚簇索引。它们的区别在于约束逻辑唯一索引禁止出现重复值允许且仅允许一个 NULL普通索引允许完全相同的值。但这带来一个性能上的差异值得展开讲。4.2 唯一索引与普通索引的写入性能差异Change Buffer 的机制MySQL 在更新索引时有个叫 Change Buffer 的机制。当你要修改的索引页不在内存缓冲池里时InnoDB 不会立刻去磁盘读页更新而是先把修改操作缓存在 Change Buffer 里等下次这个页被读进内存了或者系统空闲了再把这些操作合并merge进去。这个机制对普通索引的随机写入性能帮助巨大——避免了大量的磁盘随机 I/O。但唯一索引享受不到这个福利。因为唯一性约束要求在插入前就得查明这个页面里到底有没有重复值你不能只记一个待定的修改必须立刻把目标索引页读进内存确认不重复再插入。所以说唯一索引的写操作是有额外 I/O 代价的。在高并发批量写入场景下我一再提醒团队如果业务上并不强制要求全局唯一就别为了严谨加唯一索引改成普通索引加应用层判断。一条性别混排不改系统写入吞吐就能差出 15%~30%。4.3 主键到底该怎么选自增、UUID 还是业务主键这个话题在分布式微服务时代吵得更凶了。核心要考虑的是写入是否有序。自增主键的写入是永远往 B 树最右侧追加新页填满就接着开新页几乎不会引发索引页分裂顺序写性能最好。而 UUID 主键是随机分布的每次插入都可能落在树的中间某个位置导致索引页分裂、重排随机 I/O 多索引膨胀也快。大厂面对海量写入时确实会用分布式 ID雪花 ID 或其他变种。雪花 ID 虽然带时间戳大体有序但仍可能出现乱序。如果业务能接受我会更推荐自增主键 业务唯一键建唯一索引的组合存储层面获得写入性能最优业务层面靠唯一索引保证约束。但要注意自增主键由单点生成分库分表后要改用分段发号或雪花方案具体就看你的架构能力了。4.4 覆盖索引为什么 SELECT 的字段也影响性能讲完主键和唯一索引必须顺带提覆盖索引因为它直接利用了二级索引里存主键这个特性。如果一条查询需要返回的列都包含在某个二级索引里MySQL 直接遍历索引就能拿到所有数据完全不用回表。典型写法-- 索引 (user_id, status) 覆盖了 user_id、status 两列 SELECT user_id, status FROM orders WHERE user_id 10086;这种 SQL 的 EXPLAIN 里 Extra 列会显示Using index表示走的是覆盖索引省掉了回表的那几百上千次随机 I/O。这在统计类、报表类查询里非常常用。所以顺手推荐一个设计技巧核心查询里 SELECT 的列越长越应该考虑让它顺手被索引覆盖能省掉一个巨大的性能瓶颈。5. 索引调优的实操方法论从 EXPLAIN 出发建立自己的排查链路前面讲了这么多理论和机制最后落回实操。作为 DBA 或后端开发你可能已经有一定的索引基础但遇到一条慢 SQL 时怎么把问题追到根上我给出一套我自己用的排查链路按这个顺序走大多数索引问题都能半小时内定位。5.1 EXPLAIN 关键字段速读手册EXPLAIN 输出里列很多但我通常只盯着三个最重要的字段看type、key、rows。type 是访问类型性能从好到差大致是system const eq_ref ref range index ALL。见到 ALL 就要警惕全表扫描。key 是实际用到的索引名。有时候你会发现明明该走 idx_a结果跑了别的索引那就说明优化器的选择可能不是最优。rows 是优化器预估的需要读取行数与实际值差距过大说明统计信息太久没更新了或者 SQL 写法诱导了错误评估。举个例子一条查询执行计划长这样EXPLAIN SELECT * FROM orders WHERE user_id 10086 AND status 1;结果显示 typerefkeyidx_user_statusrows2000。typeref 代表走的是普通等值匹配的非唯一索引key 正确预估行数很少那这条 SQL 的索引利用情况就是健康的。如果看到 typeALLkeyNULLrows20000000就要立刻回头查是不是索引列被函数包装了、类型不匹配或条件顺序问题。5.2 覆盖真实业务的索引设计流程我有一套屡试不爽的流程分享给团队后大家反馈都很好核心就四步采集核心 SQL打开慢查询日志slow_query_logON跑一两天把所有执行超过 100ms 的 SQL 拉出来按执行次数排序。提取条件映射表把每条 SQL 的 WHERE 条件、ORDER BY、GROUP BY 字段全部提取出来记在表格里标注每个字段是等值、范围、排序还是分组。按频率和区分度设计联合索引高频等值字段放最前范围字段放中间或最后排序字段如果走索引不会干扰前面的等值匹配就一并放进索引。逐条验证重新执行 EXPLAIN确认 type 达到 ref 或 range 级别rows 显著下降再上线观察慢查询日志的变化。这套流程要求团队在每次上线前做 SQL Review思想上从写完能跑就行切换到写完必须看执行计划。我自己统计过强制走这套流程之后新上线业务的慢查询数量基本能下降 80% 以上。5.3 索引数量与冗余控制别把表建成了索引自助餐索引不是越多越好这句话听着像废话但执行起来很多人忍不住。每多一个索引INSERT/UPDATE/DELETE 都要额外维护一棵 B 树页分裂、日志写入统统翻倍。我经手过一个商城订单表上线两年积累了 17 个索引写入性能下降了 40% 以上后来清理成 5 个核心联合索引写入性能几乎翻倍查询也没受什么影响。清理冗余索引也有一些成熟的原则。联合索引前缀完全重复的算冗余比如有了(a, b, c)单独的(a)就是冗余的因为联合索引的前缀已经覆盖了 a 单列的查询。还有长期没有被优化器选中的索引观察 performance_schema 的索引使用统计该删就删。另外同一列上同时存在单列索引和联合索引如果单列索引命中率不高也可以砍掉。5.4 两个典型误区的纠正最后说两个容易被带偏的说法。一是索引能够极大提升查询速度所以查询慢就加索引。这是把索引当万金油了。如果一条 SQL 已经是全表扫可它本来就得返回 50% 以上的行比如导出全量报表那加索引也救不了它因为走索引回表比直接全表扫还慢。这种场景要么改业务逻辑分页、增量、列裁剪要么上真正的分析型基础设施如 ClickHouse 或 ES而不是在 MySQL 上硬拗。二是覆盖索引是万能的SELECT 全列都塞进索引里。确实有这种极端例子把一行的所有列都建进联合索引做成所谓的索引即数据但这会让索引体积膨胀到接近表本身写入成本高到不可接受。覆盖索引的价值在于覆盖高频查询的那几个关键列而不是贪心覆盖所有。我在实际工作中给报表查询建过宽覆盖索引效果很好但同时我也会在代码里规定报表类查询必须走专用的宽索引不能全表 SELECT 所有列去碰运气。结尾最后再分享一个小技巧排查索引问题这么多年我个人体会最深的一点是永远不要凭感觉判断索引生效了没有一切以 EXPLAIN 为准。而且 EXPLAIN 里的 ANALYZE 选项MySQL 8.0.18 支持能直接给出真实的执行耗时和实际读取行数比预估行数靠谱得多。遇到索引该走却没走的情况先跑ANALYZE TABLE更新一下统计信息很多问题其实是因为统计信息太陈旧优化器做了错误判断。还有个小技巧在开发环境建索引时我习惯把区分度预估也纳入 SQL Review用一条简单的SELECT COUNT(DISTINCT col) / COUNT(*) FROM t算出来如果比值低于 0.1这个索引我基本就不会建了。这些细节看起来琐碎但堆在一起就是线上数据库稳定运行和三天两头出事故之间的差距。