尧图网页设计 
1. 索引到底是什么值得你花五分钟彻底搞明白问十个开发“你知道索引吗”十个都会点头。但真到了线上慢查询报警看EXPLAIN看出typeALL的时候有一半人第一反应是“这SQL是不是写错了”而不是“这索引是不是没建对”。做 MySQL 优化做得久了会发现绝大多数索引问题根本不是不会建而是不懂索引为什么快、快在哪里、什么时候会失效。如果你正被“为什么我明明加了索引还是慢”、“where条件 a and b 到底怎么建索引”这类问题困扰这篇文章大概就是给你准备的。我常打一个比方索引就是数据库的目录。没有目录你要在一本几千页的书里找一个词只能从头翻到尾这叫全表扫描。有了目录先查页码再直接翻到那一页这叫索引查找。但注意目录本身也是要有额外纸张来印的这就是索引占空间每次改书内容目录也得跟着改这就是写放大。索引不是免费午餐设计得好是加速器设计得烂是累赘。从根上讲索引问题本质是三个问题结构问题、顺序问题、代价问题。结构决定查询多快顺序决定单个查询还是多个查询受益代价决定这个索引值不值得留。下面我从这三条线展开把索引的设计原则讲透。2. 一张图看懂 InnoDB 索引结构为什么是 B 树而不是别的2.1 聚簇索引与二级索引先分清主次InnoDB 的表主键索引本身就是表数据这叫聚簇索引。叶子节点存的是整行数据二级索引叶子节点存的则是“索引字段值 主键值”。所以走二级索引查数据要先在二级索引的 B 树里找到主键值再回聚簇索引里取整行这个过程叫回表。这个结构带来的连锁反应你一定要记住表必须要有主键。没有主键InnoDB 会找第一个不重复的列当主键找不到就偷偷生成一个隐藏主键。所以设计表结构时主动定主键比让存储引擎替你决定要主动得多你也能控制聚簇索引的组织方式。主键不要用随机值或长字符串。因为聚簇索引是按主键顺序物理排列页的插入的随机主键会导致页分裂、数据移动写性能血崩。二级索引不是越大越好。你给一个长文本字段建索引二级索引里也得存一份完整文本又大又慢这时候就该考虑前缀索引。2.2 B 树快在哪计算给你看B 树的优势一句话讲完矮。树的高度低意味着查询时读的磁盘页少。InnoDB 默认页大小是 16KB你可以算一下非叶子节点里存的是“索引键值 子节点指针”。假设索引字段是bigint8 字节指针在 InnoDB 里约 6 字节一行约 14 字节。一个 16KB 页大约能放16 * 1024 / 14 ≈ 1170个键值对。叶子节点里一条数据假设 1KB一页能放约 16 行。高度为 3 的 B 树能存1170 * 1170 * 16 ≈ 2190万行数据。也就是说两三千万行的表走主键索引查询最多读 3 个磁盘页。而全表扫描可能得读几万甚至几十万个页。这就是索引吊打全表扫描的最底层原因。理解了这一点后面所有设计原则都能推出来。3. 索引设计原则什么时候该建、什么时候别碰3.1 可量化的判断标准区分度建索引之前先问一个问题这个字段能不能把行数快速过滤到很小用区分度衡量也就是COUNT(DISTINCT 字段) / COUNT(*)。区分度越接近 1索引越有价值。我一般设一条线区分度低于 20% 的字段单独建索引通常没什么用。比如性别字段只有男女两类区分度可能 0.001 都不到。在“男”这个条件下还是有一半行要扫MySQL 优化器算一下账就不乐意走这个索引了。但注意区分度低的字段不意味着完全不能进索引它可以作为复合索引的一部分后面我会讲这种情况。3.2 该建索引的场景高频查询的 WHERE 条件字段这是最典型的需求点。一个查询每天跑几万次哪怕每次快 10ms累计收益都非常客观。ORDER BY 字段B 树天生有序。让排序字段走索引就能避免Using filesort直接在索引顺序上取数据。GROUP BY、DISTINCT 字段有序结构天然能加速分组去重。JOIN 的关联字段让关联查找走索引避免嵌套循环里的全表扫描。覆盖索引需求如果索引字段本身包含了查询要的所有列连回表都省了这个收益翻倍。3.3 不该建的场景频繁更新的字段索引列每次更新都要同步改索引树写放大非常明显。如果一个字段被高频 UPDATE但你很少拿它做查询条件那就别建。数据量小的表一张表就几百行全表扫描也就扫几个页优化器大概率不走索引。建了占空间、拖慢写入纯亏。冗余索引已有复合索引(a, b)的情况下再单独建(a)就是冗余。因为复合索引的最左前缀已经能覆盖单列 a 的查询。冗余索引浪费空间还拖慢写入。大文本字段TEXT和超长VARCHAR直接建索引索引树会非常臃肿。要么用前缀索引要么干脆交给全文索引或搜索引擎去管。3.4 字符串字段的前缀索引技巧超长字符串字段不能整列建索引可以只取前面一部分字符来建索引这样索引体积小区分度损失可控。比如邮箱字段email varchar(255)可以只对前 10 个字符建索引。判断到底取多长就是逐步增加前缀长度看区分度直到接近整列的区分度。不过要注意前缀索引有两个代价一是无法用作覆盖索引二是无法支持 ORDER BY。因为索引只存了一部分字符排序时拿不到完整值。所以前缀索引只适合“查得快”这一目标。4. 复合索引与最左前缀原则WHERE 条件 A AND B 究竟怎么建这是实践中最容易踩坑的地方网上搜“mysql where条件a and b 应该怎么建索引”的热度一直很高基本是所有 MySQL 开发者的共同痛点。4.1 先理解最左前缀原则的底层逻辑复合索引(a, b, c)实际是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。这就像电话簿按“姓、名、开头字母”排列一样能直接查(姓张)能查(姓张 且 名三)也能查(姓张 且 名三 且 开头字母A)但你要是只知道“名三”那就没法用这个电话簿快速定位了因为整个目录根本就不是按名排的。所以最左前缀原则本质上不是什么玄学而是“复合索引整体有序”这一物理事实的直接推论。MySQL 8.0 引入了索引跳跃扫描部分场景下跳过最左列也能走索引但那是优化器的特殊处理有诸多限制如前面的列要枚举值有限别把它当成常规手段去依赖。4.2 字段顺序怎么排四步决策法当你要为WHERE a ? AND b ?建复合索引时按这个顺序做决策第一步先放等值查询字段。等值条件、IN可以精确定位对索引顺序不敏感先放哪都无所谓但放在前面能更快缩小区间。第二步把区分度高的字段放前面。这样做能让 B 树更早地剪掉大量分支减少后续比较次数。比如用户ID和状态字段做复合索引用户ID放前面因为用户ID能秒级过滤到几十行状态字段如果放前面可能需要比对几万行。第三步范围查询字段放后面。这是关键原则。WHERE a ? AND b ?的场景b 如果放在 a 后面a 先定位到相等的行b 的范围在有序结构里是一个连续区间依然高效。但如果把 b 放前面b ?扫到的行就太多了后面的 a 等值条件只能在扫出来的大集合里过滤收益大打折扣。第四步优先服务排序和分组。如果查询里有ORDER BY b此时把 b 放进索引比如(a, b)就能让排序走索引避免Using filesort。排序字段应该放在等值字段后面并且排序方向要一致要么都 ASC要么都 DESC否则优化器没法直接用索引排序又得 filesort。4.3 经典 SQLWHERE a ? AND b ?建索引实操假设有一张订单表高频查询是SELECT order_no, amount, status FROM orders WHERE user_id 123 AND status PAID ORDER BY create_time DESC LIMIT 10;这个查询涉及三个点WHERE 等值user_id、status、排序create_time、覆盖列amount。最合理的索引路径是主键order_id或id聚簇索引天然存在。辅助索引一(user_id, status, create_time) DESC。注意我把 create_time 排到第三位因为前两个等值条件把范围缩到极小再用 create_time 直接满足 ORDER BY无需 filesort。要彻底避免回表还得让查询的列都在索引里。把 amount 加进去变成(user_id, status, create_time, amount)这就是覆盖索引select 的所有字段直接从索引里返回不碰数据行。这段 SQL 单独的status查询就不要指望这个索引了因为 user_id 在最左单独查 status 用不上。如果 status 查询也很高频那就单独建(status, create_time)。4.4 读懂 EXPLAIN别被“走了索引”骗了有索引不代表用得好。EXPLAIN SELECT ...的输出里重点看这些列列名含义关注点type访问方式从好到差const eq_ref ref range index ALL。见到 ALL 或 index 就要警惕key实际用的索引如果是 NULL说明索引根本没被用上key_len索引使用长度值越大说明用到的索引列越多。比如复合索引(a,b,c)key_len 只有 a 的长度说明只用了 arows预估扫描行数这个数字越小越好是判断索引有效性的直观参考Extra附加信息出现Using filesort说明排序没用索引出现Using temporary说明用了临时表要重点优化实际排查时我有个习惯不只看key列还要看key_len。因为它能告诉你复合索引到底用到了第几列。比如你建了(a, b, c)三列复合索引但 explain 显示 key_len 只有 8 字节a 是 bigint 的话那说明 b 和 c 根本没帮上忙SQL 写法可能有问题。4.5 覆盖索引与索引下推两个隐形加速器覆盖索引是指查询的所有列都包含在索引里连回表都省了。比如SELECT user_id, status FROM orders WHERE status PAID如果复合索引是(status, user_id)那这条 SQL 直接在索引树上就能拿到全部数据。这时候Extra列会显示Using index这个信息光看 key 是看不出来的。索引下推是 MySQL 5.6 引入的优化。没有索引下推时WHERE a ? AND b LIKE xxx%这种查询MySQL 需要在服务层对回表后的每行做 LIKE 过滤。有了索引下推存储引擎层在索引树上就先按 LIKE 条件过滤一批再回表回表次数减少很多。Extra 列会出现Using index condition。这个特性默认开启大部分时候不用你管但理解它能帮你解释“为什么执行计划看起来没全用索引但还是很快”这类现象。5. 一个真实场景的索引设计与优化复盘5.1 业务背景与初始问题假设有一套电商订单系统核心表结构简化后是这样CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id int NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;上线一段时间后运营反馈后台订单列表页越翻越慢特别是按用户查历史订单时经常超过 3 秒。看慢查询日志典型的两条-- 查询用户某个状态下的订单按时间倒序 SELECT * FROM orders WHERE user_id 1001 AND status 2 ORDER BY create_time DESC LIMIT 10; -- 运营按状态和时间段拉数据 SELECT order_no, user_id, amount, status FROM orders WHERE status 2 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY create_time;5.2 初始设计凭感觉建索引一开始开发同学随手建了三个单列索引idx_user_id、idx_status、idx_create_time。这种“给每个字段都建一个索引”的做法很常见但对复合查询来说基本没用。WHERE user_id ? AND status ?只能从三个索引里挑一个最优的用剩下的条件还是得回表后逐行过滤。那两条慢查询冷启动跑下来都超过 2 秒EXPLAIN的结果第一条keyidx_user_idrows2401ExtraUsing filesort第二条keyidx_statusrows8500ExtraUsing filesort。问题一目了然回表行数多排序也没走上索引。5.3 优化调整按查询模式重排复合索引优化思路是覆盖高频查询把单列索引改成复合索引-- 服务第一条 SQL ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- 服务第二条 SQL同时做成覆盖索引 ALTER TABLE orders ADD INDEX idx_status_time_cover (status, create_time, order_no, user_id, amount);第一条复合索引(user_id, status, create_time)。等值查 user_id 和 status 后create_time 直接在索引里有续排序不再 filesort。如果不加amount到索引里select 的 * 需要回表但因为 user_id 已经过滤到几十行回表代价很小没必要强行覆盖所有列。第二条索引(status, create_time, order_no, user_id, amount)。status 区分度不高单独走会扫很多行但二级索引里回表会额外读主键树之前是灾难性的。现在把 select 需要的列都塞进索引Extra直接变成Using index全索引覆盖无回表。优化后的EXPLAIN第一条typeref, keyidx_user_status_time, rows≈12, Extra无 filesort 第二条typerange, keyidx_status_time_cover, rows≈30, ExtraUsing index两条慢查询从 2 秒分别降到 15ms 和 20ms 以内。这个案例很典型不是索引的“数量”不够而是索引的“形状”不对。单列索引堆再多也救不了复合条件查询。5.4 深分页优化再快也得防 LIMIT 深翻订单列表页还有一个隐藏问题翻到第 100 页时LIMIT 990, 10意味着 MySQL 必须先扫 990 行再丢掉这 990 行的回表开销是白付的。推荐的做法是“延迟关联”——先只查主键再和原表做 JOINSELECT * FROM orders JOIN ( SELECT id FROM orders WHERE user_id 1001 AND status 2 ORDER BY create_time DESC LIMIT 990, 10 ) t ON orders.id t.id;子查询里因为索引已经覆盖了user_id, status, create_time和主键 id扫描 1000 行也只碰二级索引不回表拿到 10 个主键后再去聚簇索引取整行回表次数从 1000 降到了 10。这个技巧在数据量大、翻页深的场景下几乎是必备手段。6. 索引失效场景与高频面试题实录6.1 索引失效的五个高频场景逐个拆原因1. 对索引列做了函数或计算。WHERE YEAR(create_time) 2024这种写法索引树里存的是原始值不是年份MySQL 不能拿函数结果去走树查找。8.0 版本对部分函数引入了函数索引但老写法依然默认失效。正确做法是改成create_time 2024-01-01 AND create_time 2025-01-01。2. 隐式类型转换。索引列是字符串查的时候却传了数字。MySQL 会把字符串列转数字去做比较导致索引列上隐式发生函数操作优化器放弃索引。检查方法很简单看EXPLAIN里key_len和type发现异常就查 SQL 里的字段类型是否和参数类型一致。3. LIKE 通配符开头。LIKE %abc没法用 B 树的顺序查找因为要匹配的字符不固定LIKE abc%可以用。真要查后缀匹配别指望普通索引考虑用全文索引或者存一份反转后的字段。4. 联合索引不满足最左前缀。复合索引(a, b)直接WHERE b ?必然失效这在前文已经讲过。注意还有一个隐蔽场景WHERE a 100 AND b 5。a 用了范围查询b 的判断就只能在 a 筛选出的区间里逐个过滤索引帮不上 b。5. 负向查询。WHERE status 2、WHERE status NOT IN (1, 2)这类条件通常扫出来的行数占比很大优化器会直接选择全表扫描。负向条件的优化思路是用正向条件改写或者反过来建一个“记录异常状态”的索引。要特别强调的是“索引失效”不是绝对的。MySQL 优化器会根据行数估算、索引基数、回表代价做综合判断。有时你看到typerange却不扫全表说明优化器认为走索引更划算。判断标准永远以 EXPLAIN 的 rows 和 Extra 为准别凭经验拍脑袋。6.2 面试高频问题整理从原理到场景以下是这两年我在面试和带新人时经常被问到的 MySQL 索引题目我按难度递进整理了一份问题答题要点为什么选择 B 树不用 B 树或哈希B 树数据都在叶子节点且形成有序链表范围查询和排序友好B 树的非叶子节点也存数据树更高IO 更多哈希适合单点等值查询不支持范围聚簇索引和二级索引的区别聚簇索引叶子节点存整行数据一个表只有一个二级索引叶子节点存索引列主键查询要回表什么是覆盖索引查询所需列全部包含在索引里无需回表Extra 显示 Using index什么是索引下推存储引擎层在索引遍历过程中对索引包含的字段先做过滤减少回表次数为什么主键建议用自增整数聚簇索引按主键顺序物理存放自增主键插入走顺序追加避免页分裂和随机 IO复合索引(a, b, c)WHERE b ? AND c ?能走索引吗不能违反最左前缀原则除非 MySQL 8.0 的跳跃扫描特定场景如果一张表查询很慢你会怎么排查先看慢查询日志定位 SQL看执行计划确认是否全表扫描、是否回表过多确认过滤行数与区分度再决定加索引还是改 SQL6.3 一个容易被忽略的细节排序方向要统一复合索引里字段的排序方向如果和ORDER BY不一致MySQL 就没法直接利用索引排序。比如(a ASC, b DESC)这个索引无法直接服务ORDER BY a ASC, b ASC因为 b 的排列方向刚好相反。8.0 支持降序索引CREATE INDEX ... ON t (a ASC, b DESC)能更灵活地匹配排序需求。老版本遇到这种场景只能 filesort设计时就要提前核对高频排序的方向。7. 我在实际项目里反复用的一小套索引设计心法最后再分享一点个人经验。做了几年 MySQL 维护我总结出一套极简心法虽然谈不上高深但每次拿来做初版设计都很稳先写 SQL再定索引。不是先建一堆索引再想让 SQL 怎么走。把业务里的慢查询和高频查询列出来按访问频率排优先级设计索引只服务 Top N 的 SQL。复合索引优先单列索引最后补。大多数业务查询都有多个条件单列索引往往不够用。每建一个复合索引都要想清楚它最左前缀覆盖了哪些查询。能覆盖就别回表。在满足查询的条件下把 select 的列尽量塞进索引。但这有个前提索引不是越宽越好。索引列的字节数太大树会变宽变胖插入和查询效率都会下降。一般两到三个业务字段加主键就够了。定期给索引“减肥”。用sys.schema_unused_indexes或performance_schema查一下长期无人使用的索引果断 DROP。索引占了写入成本不用的索引就是在给每次 INSERT 和 UPDATE 添堵。监控慢查询别只看平均值。线上经常出现“平均查询时间挺正常但偶尔抖动到好几秒”的情况。这是索引基数统计过期或缓存淘汰导致的。这时可以用ANALYZE TABLE重新统计或者考虑调整 optimizer 的索引选择逻辑。索引设计从来不是“加了索引就完事”而是一个持续跟业务查询模式磨合的过程。你建的每一个索引都是在对 MySQL 说未来的查询大概率会这么走。判断准了数据库就快判断不准索引就是纯负担。希望这篇文章能帮你在建每一个索引前多想一层“为什么”。 MySQL索引设计全解析：从B+树到复合索引，终结慢查询
1. 索引到底是什么值得你花五分钟彻底搞明白问十个开发“你知道索引吗”十个都会点头。但真到了线上慢查询报警看EXPLAIN看出typeALL的时候有一半人第一反应是“这SQL是不是写错了”而不是“这索引是不是没建对”。做 MySQL 优化做得久了会发现绝大多数索引问题根本不是不会建而是不懂索引为什么快、快在哪里、什么时候会失效。如果你正被“为什么我明明加了索引还是慢”、“where条件 a and b 到底怎么建索引”这类问题困扰这篇文章大概就是给你准备的。我常打一个比方索引就是数据库的目录。没有目录你要在一本几千页的书里找一个词只能从头翻到尾这叫全表扫描。有了目录先查页码再直接翻到那一页这叫索引查找。但注意目录本身也是要有额外纸张来印的这就是索引占空间每次改书内容目录也得跟着改这就是写放大。索引不是免费午餐设计得好是加速器设计得烂是累赘。从根上讲索引问题本质是三个问题结构问题、顺序问题、代价问题。结构决定查询多快顺序决定单个查询还是多个查询受益代价决定这个索引值不值得留。下面我从这三条线展开把索引的设计原则讲透。2. 一张图看懂 InnoDB 索引结构为什么是 B 树而不是别的2.1 聚簇索引与二级索引先分清主次InnoDB 的表主键索引本身就是表数据这叫聚簇索引。叶子节点存的是整行数据二级索引叶子节点存的则是“索引字段值 主键值”。所以走二级索引查数据要先在二级索引的 B 树里找到主键值再回聚簇索引里取整行这个过程叫回表。这个结构带来的连锁反应你一定要记住表必须要有主键。没有主键InnoDB 会找第一个不重复的列当主键找不到就偷偷生成一个隐藏主键。所以设计表结构时主动定主键比让存储引擎替你决定要主动得多你也能控制聚簇索引的组织方式。主键不要用随机值或长字符串。因为聚簇索引是按主键顺序物理排列页的插入的随机主键会导致页分裂、数据移动写性能血崩。二级索引不是越大越好。你给一个长文本字段建索引二级索引里也得存一份完整文本又大又慢这时候就该考虑前缀索引。2.2 B 树快在哪计算给你看B 树的优势一句话讲完矮。树的高度低意味着查询时读的磁盘页少。InnoDB 默认页大小是 16KB你可以算一下非叶子节点里存的是“索引键值 子节点指针”。假设索引字段是bigint8 字节指针在 InnoDB 里约 6 字节一行约 14 字节。一个 16KB 页大约能放16 * 1024 / 14 ≈ 1170个键值对。叶子节点里一条数据假设 1KB一页能放约 16 行。高度为 3 的 B 树能存1170 * 1170 * 16 ≈ 2190万行数据。也就是说两三千万行的表走主键索引查询最多读 3 个磁盘页。而全表扫描可能得读几万甚至几十万个页。这就是索引吊打全表扫描的最底层原因。理解了这一点后面所有设计原则都能推出来。3. 索引设计原则什么时候该建、什么时候别碰3.1 可量化的判断标准区分度建索引之前先问一个问题这个字段能不能把行数快速过滤到很小用区分度衡量也就是COUNT(DISTINCT 字段) / COUNT(*)。区分度越接近 1索引越有价值。我一般设一条线区分度低于 20% 的字段单独建索引通常没什么用。比如性别字段只有男女两类区分度可能 0.001 都不到。在“男”这个条件下还是有一半行要扫MySQL 优化器算一下账就不乐意走这个索引了。但注意区分度低的字段不意味着完全不能进索引它可以作为复合索引的一部分后面我会讲这种情况。3.2 该建索引的场景高频查询的 WHERE 条件字段这是最典型的需求点。一个查询每天跑几万次哪怕每次快 10ms累计收益都非常客观。ORDER BY 字段B 树天生有序。让排序字段走索引就能避免Using filesort直接在索引顺序上取数据。GROUP BY、DISTINCT 字段有序结构天然能加速分组去重。JOIN 的关联字段让关联查找走索引避免嵌套循环里的全表扫描。覆盖索引需求如果索引字段本身包含了查询要的所有列连回表都省了这个收益翻倍。3.3 不该建的场景频繁更新的字段索引列每次更新都要同步改索引树写放大非常明显。如果一个字段被高频 UPDATE但你很少拿它做查询条件那就别建。数据量小的表一张表就几百行全表扫描也就扫几个页优化器大概率不走索引。建了占空间、拖慢写入纯亏。冗余索引已有复合索引(a, b)的情况下再单独建(a)就是冗余。因为复合索引的最左前缀已经能覆盖单列 a 的查询。冗余索引浪费空间还拖慢写入。大文本字段TEXT和超长VARCHAR直接建索引索引树会非常臃肿。要么用前缀索引要么干脆交给全文索引或搜索引擎去管。3.4 字符串字段的前缀索引技巧超长字符串字段不能整列建索引可以只取前面一部分字符来建索引这样索引体积小区分度损失可控。比如邮箱字段email varchar(255)可以只对前 10 个字符建索引。判断到底取多长就是逐步增加前缀长度看区分度直到接近整列的区分度。不过要注意前缀索引有两个代价一是无法用作覆盖索引二是无法支持 ORDER BY。因为索引只存了一部分字符排序时拿不到完整值。所以前缀索引只适合“查得快”这一目标。4. 复合索引与最左前缀原则WHERE 条件 A AND B 究竟怎么建这是实践中最容易踩坑的地方网上搜“mysql where条件a and b 应该怎么建索引”的热度一直很高基本是所有 MySQL 开发者的共同痛点。4.1 先理解最左前缀原则的底层逻辑复合索引(a, b, c)实际是先按 a 排序a 相同再按 b 排序b 相同再按 c 排序。这就像电话簿按“姓、名、开头字母”排列一样能直接查(姓张)能查(姓张 且 名三)也能查(姓张 且 名三 且 开头字母A)但你要是只知道“名三”那就没法用这个电话簿快速定位了因为整个目录根本就不是按名排的。所以最左前缀原则本质上不是什么玄学而是“复合索引整体有序”这一物理事实的直接推论。MySQL 8.0 引入了索引跳跃扫描部分场景下跳过最左列也能走索引但那是优化器的特殊处理有诸多限制如前面的列要枚举值有限别把它当成常规手段去依赖。4.2 字段顺序怎么排四步决策法当你要为WHERE a ? AND b ?建复合索引时按这个顺序做决策第一步先放等值查询字段。等值条件、IN可以精确定位对索引顺序不敏感先放哪都无所谓但放在前面能更快缩小区间。第二步把区分度高的字段放前面。这样做能让 B 树更早地剪掉大量分支减少后续比较次数。比如用户ID和状态字段做复合索引用户ID放前面因为用户ID能秒级过滤到几十行状态字段如果放前面可能需要比对几万行。第三步范围查询字段放后面。这是关键原则。WHERE a ? AND b ?的场景b 如果放在 a 后面a 先定位到相等的行b 的范围在有序结构里是一个连续区间依然高效。但如果把 b 放前面b ?扫到的行就太多了后面的 a 等值条件只能在扫出来的大集合里过滤收益大打折扣。第四步优先服务排序和分组。如果查询里有ORDER BY b此时把 b 放进索引比如(a, b)就能让排序走索引避免Using filesort。排序字段应该放在等值字段后面并且排序方向要一致要么都 ASC要么都 DESC否则优化器没法直接用索引排序又得 filesort。4.3 经典 SQLWHERE a ? AND b ?建索引实操假设有一张订单表高频查询是SELECT order_no, amount, status FROM orders WHERE user_id 123 AND status PAID ORDER BY create_time DESC LIMIT 10;这个查询涉及三个点WHERE 等值user_id、status、排序create_time、覆盖列amount。最合理的索引路径是主键order_id或id聚簇索引天然存在。辅助索引一(user_id, status, create_time) DESC。注意我把 create_time 排到第三位因为前两个等值条件把范围缩到极小再用 create_time 直接满足 ORDER BY无需 filesort。要彻底避免回表还得让查询的列都在索引里。把 amount 加进去变成(user_id, status, create_time, amount)这就是覆盖索引select 的所有字段直接从索引里返回不碰数据行。这段 SQL 单独的status查询就不要指望这个索引了因为 user_id 在最左单独查 status 用不上。如果 status 查询也很高频那就单独建(status, create_time)。4.4 读懂 EXPLAIN别被“走了索引”骗了有索引不代表用得好。EXPLAIN SELECT ...的输出里重点看这些列列名含义关注点type访问方式从好到差const eq_ref ref range index ALL。见到 ALL 或 index 就要警惕key实际用的索引如果是 NULL说明索引根本没被用上key_len索引使用长度值越大说明用到的索引列越多。比如复合索引(a,b,c)key_len 只有 a 的长度说明只用了 arows预估扫描行数这个数字越小越好是判断索引有效性的直观参考Extra附加信息出现Using filesort说明排序没用索引出现Using temporary说明用了临时表要重点优化实际排查时我有个习惯不只看key列还要看key_len。因为它能告诉你复合索引到底用到了第几列。比如你建了(a, b, c)三列复合索引但 explain 显示 key_len 只有 8 字节a 是 bigint 的话那说明 b 和 c 根本没帮上忙SQL 写法可能有问题。4.5 覆盖索引与索引下推两个隐形加速器覆盖索引是指查询的所有列都包含在索引里连回表都省了。比如SELECT user_id, status FROM orders WHERE status PAID如果复合索引是(status, user_id)那这条 SQL 直接在索引树上就能拿到全部数据。这时候Extra列会显示Using index这个信息光看 key 是看不出来的。索引下推是 MySQL 5.6 引入的优化。没有索引下推时WHERE a ? AND b LIKE xxx%这种查询MySQL 需要在服务层对回表后的每行做 LIKE 过滤。有了索引下推存储引擎层在索引树上就先按 LIKE 条件过滤一批再回表回表次数减少很多。Extra 列会出现Using index condition。这个特性默认开启大部分时候不用你管但理解它能帮你解释“为什么执行计划看起来没全用索引但还是很快”这类现象。5. 一个真实场景的索引设计与优化复盘5.1 业务背景与初始问题假设有一套电商订单系统核心表结构简化后是这样CREATE TABLE orders ( id bigint NOT NULL AUTO_INCREMENT, order_no varchar(32) NOT NULL, user_id int NOT NULL, status tinyint NOT NULL DEFAULT 0, amount decimal(10,2) NOT NULL, create_time datetime NOT NULL, PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no) ) ENGINEInnoDB;上线一段时间后运营反馈后台订单列表页越翻越慢特别是按用户查历史订单时经常超过 3 秒。看慢查询日志典型的两条-- 查询用户某个状态下的订单按时间倒序 SELECT * FROM orders WHERE user_id 1001 AND status 2 ORDER BY create_time DESC LIMIT 10; -- 运营按状态和时间段拉数据 SELECT order_no, user_id, amount, status FROM orders WHERE status 2 AND create_time BETWEEN 2024-01-01 AND 2024-01-31 ORDER BY create_time;5.2 初始设计凭感觉建索引一开始开发同学随手建了三个单列索引idx_user_id、idx_status、idx_create_time。这种“给每个字段都建一个索引”的做法很常见但对复合查询来说基本没用。WHERE user_id ? AND status ?只能从三个索引里挑一个最优的用剩下的条件还是得回表后逐行过滤。那两条慢查询冷启动跑下来都超过 2 秒EXPLAIN的结果第一条keyidx_user_idrows2401ExtraUsing filesort第二条keyidx_statusrows8500ExtraUsing filesort。问题一目了然回表行数多排序也没走上索引。5.3 优化调整按查询模式重排复合索引优化思路是覆盖高频查询把单列索引改成复合索引-- 服务第一条 SQL ALTER TABLE orders ADD INDEX idx_user_status_time (user_id, status, create_time); -- 服务第二条 SQL同时做成覆盖索引 ALTER TABLE orders ADD INDEX idx_status_time_cover (status, create_time, order_no, user_id, amount);第一条复合索引(user_id, status, create_time)。等值查 user_id 和 status 后create_time 直接在索引里有续排序不再 filesort。如果不加amount到索引里select 的 * 需要回表但因为 user_id 已经过滤到几十行回表代价很小没必要强行覆盖所有列。第二条索引(status, create_time, order_no, user_id, amount)。status 区分度不高单独走会扫很多行但二级索引里回表会额外读主键树之前是灾难性的。现在把 select 需要的列都塞进索引Extra直接变成Using index全索引覆盖无回表。优化后的EXPLAIN第一条typeref, keyidx_user_status_time, rows≈12, Extra无 filesort 第二条typerange, keyidx_status_time_cover, rows≈30, ExtraUsing index两条慢查询从 2 秒分别降到 15ms 和 20ms 以内。这个案例很典型不是索引的“数量”不够而是索引的“形状”不对。单列索引堆再多也救不了复合条件查询。5.4 深分页优化再快也得防 LIMIT 深翻订单列表页还有一个隐藏问题翻到第 100 页时LIMIT 990, 10意味着 MySQL 必须先扫 990 行再丢掉这 990 行的回表开销是白付的。推荐的做法是“延迟关联”——先只查主键再和原表做 JOINSELECT * FROM orders JOIN ( SELECT id FROM orders WHERE user_id 1001 AND status 2 ORDER BY create_time DESC LIMIT 990, 10 ) t ON orders.id t.id;子查询里因为索引已经覆盖了user_id, status, create_time和主键 id扫描 1000 行也只碰二级索引不回表拿到 10 个主键后再去聚簇索引取整行回表次数从 1000 降到了 10。这个技巧在数据量大、翻页深的场景下几乎是必备手段。6. 索引失效场景与高频面试题实录6.1 索引失效的五个高频场景逐个拆原因1. 对索引列做了函数或计算。WHERE YEAR(create_time) 2024这种写法索引树里存的是原始值不是年份MySQL 不能拿函数结果去走树查找。8.0 版本对部分函数引入了函数索引但老写法依然默认失效。正确做法是改成create_time 2024-01-01 AND create_time 2025-01-01。2. 隐式类型转换。索引列是字符串查的时候却传了数字。MySQL 会把字符串列转数字去做比较导致索引列上隐式发生函数操作优化器放弃索引。检查方法很简单看EXPLAIN里key_len和type发现异常就查 SQL 里的字段类型是否和参数类型一致。3. LIKE 通配符开头。LIKE %abc没法用 B 树的顺序查找因为要匹配的字符不固定LIKE abc%可以用。真要查后缀匹配别指望普通索引考虑用全文索引或者存一份反转后的字段。4. 联合索引不满足最左前缀。复合索引(a, b)直接WHERE b ?必然失效这在前文已经讲过。注意还有一个隐蔽场景WHERE a 100 AND b 5。a 用了范围查询b 的判断就只能在 a 筛选出的区间里逐个过滤索引帮不上 b。5. 负向查询。WHERE status 2、WHERE status NOT IN (1, 2)这类条件通常扫出来的行数占比很大优化器会直接选择全表扫描。负向条件的优化思路是用正向条件改写或者反过来建一个“记录异常状态”的索引。要特别强调的是“索引失效”不是绝对的。MySQL 优化器会根据行数估算、索引基数、回表代价做综合判断。有时你看到typerange却不扫全表说明优化器认为走索引更划算。判断标准永远以 EXPLAIN 的 rows 和 Extra 为准别凭经验拍脑袋。6.2 面试高频问题整理从原理到场景以下是这两年我在面试和带新人时经常被问到的 MySQL 索引题目我按难度递进整理了一份问题答题要点为什么选择 B 树不用 B 树或哈希B 树数据都在叶子节点且形成有序链表范围查询和排序友好B 树的非叶子节点也存数据树更高IO 更多哈希适合单点等值查询不支持范围聚簇索引和二级索引的区别聚簇索引叶子节点存整行数据一个表只有一个二级索引叶子节点存索引列主键查询要回表什么是覆盖索引查询所需列全部包含在索引里无需回表Extra 显示 Using index什么是索引下推存储引擎层在索引遍历过程中对索引包含的字段先做过滤减少回表次数为什么主键建议用自增整数聚簇索引按主键顺序物理存放自增主键插入走顺序追加避免页分裂和随机 IO复合索引(a, b, c)WHERE b ? AND c ?能走索引吗不能违反最左前缀原则除非 MySQL 8.0 的跳跃扫描特定场景如果一张表查询很慢你会怎么排查先看慢查询日志定位 SQL看执行计划确认是否全表扫描、是否回表过多确认过滤行数与区分度再决定加索引还是改 SQL6.3 一个容易被忽略的细节排序方向要统一复合索引里字段的排序方向如果和ORDER BY不一致MySQL 就没法直接利用索引排序。比如(a ASC, b DESC)这个索引无法直接服务ORDER BY a ASC, b ASC因为 b 的排列方向刚好相反。8.0 支持降序索引CREATE INDEX ... ON t (a ASC, b DESC)能更灵活地匹配排序需求。老版本遇到这种场景只能 filesort设计时就要提前核对高频排序的方向。7. 我在实际项目里反复用的一小套索引设计心法最后再分享一点个人经验。做了几年 MySQL 维护我总结出一套极简心法虽然谈不上高深但每次拿来做初版设计都很稳先写 SQL再定索引。不是先建一堆索引再想让 SQL 怎么走。把业务里的慢查询和高频查询列出来按访问频率排优先级设计索引只服务 Top N 的 SQL。复合索引优先单列索引最后补。大多数业务查询都有多个条件单列索引往往不够用。每建一个复合索引都要想清楚它最左前缀覆盖了哪些查询。能覆盖就别回表。在满足查询的条件下把 select 的列尽量塞进索引。但这有个前提索引不是越宽越好。索引列的字节数太大树会变宽变胖插入和查询效率都会下降。一般两到三个业务字段加主键就够了。定期给索引“减肥”。用sys.schema_unused_indexes或performance_schema查一下长期无人使用的索引果断 DROP。索引占了写入成本不用的索引就是在给每次 INSERT 和 UPDATE 添堵。监控慢查询别只看平均值。线上经常出现“平均查询时间挺正常但偶尔抖动到好几秒”的情况。这是索引基数统计过期或缓存淘汰导致的。这时可以用ANALYZE TABLE重新统计或者考虑调整 optimizer 的索引选择逻辑。索引设计从来不是“加了索引就完事”而是一个持续跟业务查询模式磨合的过程。你建的每一个索引都是在对 MySQL 说未来的查询大概率会这么走。判断准了数据库就快判断不准索引就是纯负担。希望这篇文章能帮你在建每一个索引前多想一层“为什么”。