MySQL 单点详解（14）

/* MD / 富文本中的 .toc（含博客园搬家等嵌套结构）；.toc-box 在侧栏，不受影响 */
#content_views .toc,
/* 编辑器常在目录前后插入空 p（:empty 仍占 20px），一并去掉避免顶空隙 */
#content_views.markdown_views > p:empty:has(+ .toc),
#content_views.markdown_views > .toc + p:empty,
/* 富文本旧版目录标记 */
#content_views.htmledit_views #main-toc,
#content_views.htmledit_views #hr-toc,
#content_views.htmledit_views p[id*=\”-toc\”] {
display: none !important;
}
/* 目录去掉后，紧跟的首个标题不再多出一块上边距 */
#content_views.markdown_views > .toc + h1,
#content_views.markdown_views > .toc + h2,
#content_views.markdown_views > .toc + h3,
#content_views.markdown_views > .toc + h4,
#content_views.markdown_views > .toc + p:empty + h1,
#content_views.markdown_views > .toc + p:empty + h2,
#content_views.markdown_views > .toc + p:empty + h3,
#content_views.markdown_views > .toc + p:empty + h4 {
margin-top: 0 !important;
}

MySQL 单点详解（14）：给定一条 SQL，联合索引的列该怎么排

面试官：这条 SQL 该建什么索引？联合索引的三列按什么顺序排？

面试官：where 里有范围，order by 又要排序，索引怎么建？

建索引只有三步：先看 where 怎么筛，再看 order by 能不能借上，最后看 select 能不能覆盖。

一、先拆这条 SQL

表① 四个子句，各管一件事

四个子句里，第一个决定起点，后两个决定能不能省掉额外动作。

一条 SQL 的索引，基本就是这四件事的折中。

二、列顺序的四条规则

表② 排序不是凭感觉

先说为什么范围列要放后面。如果反过来建成 (age, city)，age 是范围，范围之后 city 就定位不了，只能当过滤条件用。

同样两列，顺序一换，定位能力少一半。

区分度等于不重复的值除以总行数，越接近 1 越好。

手机号、订单号接近 1，性别只有两个值，接近 0。

这也是为什么性别、状态这类列不该单独建索引：筛完还剩一半，不如直接扫。

三、范围列和排序列冲突时

看一条具体的。

select id, name from user where city = ? and age > 20 order by create_time desc limit 20

city 是等值，age 是范围，create_time 要排序。三个列不可能都受益。

表③ 两种建法，各让一步

选哪个，取决于数据分布和 limit 的大小。

如果 city 等于北京、age 大于 20 的行本来就只有一百来条，第一种更好：筛完排一百行，代价很小。

如果这类行有几十万，第一种就要排几十万行；第二种反而更快，因为它按时间倒序扫，凑够二十条就停，不用排完。

这就是 limit 小的场景里，第二种常常胜出的原因：它可以提前终止，而排序必须处理完所有候选行。

实际做法是先 explain 两种，看 rows 和 Extra 再定，不要凭规则猜。

建索引的最后一步永远是看执行计划，不是看规则。规则只能缩小范围，不能替做决定。

四、什么时候不该建

四种情况，建了也是负担。

第一种，区分度太低。性别、是否删除、状态只有几个值，筛完剩一大半，索引的定位作用用不上。

第二种，表很小。几百行的表，全表扫比走索引再回表还快。

第三种，写多读少。每次插入更新都要维护索引，多一个索引就多一次维护。

第四种，已经有前缀覆盖。建了 (a, b, c)，就不必再单独建 (a)，它已经覆盖了 (a) 和 (a, b)。

一般单表索引控制在五个以内，超过就要审视一下。

每个索引都要占空间，一千万行的表，一个索引可能就是几百兆。写越频繁，这笔开销越明显。

五、面试官会往下问的三层

第一层：区分度怎么算？

用不重复的值个数除以总行数。也可以直接查一下某列有多少个不同的值，跟总行数比。

注意区分度是跟着数据走的。一个字段现在区分度高，业务变了可能就不高了，索引要跟着复查。

第二层：字符串太长怎么建索引？

用前缀索引，只取前若干位。比如邮箱取前二十位。

长度的定法：逐步加长，直到不同前缀的个数接近整列的个数，再长就没意义了。

代价是前缀索引不能用于覆盖索引，也不能用于排序。

第三层：order by 用了索引，是不是一定不排序了？

不一定。要满足两个条件：排序的列在索引里，且顺序一致。

如果 SQL 里是 order by a desc, b asc，两个方向不一致，索引就用不上，还是会额外排序。

收尾

这期压成一句：等值定起点，范围断后路，排序看能不能顺路，覆盖看能不能不回表，冲突时用 explain 定，不用规则定。