根据查询设计索引

为常用筛选和排序建立索引,并判断它的维护成本

🎯 引言

学完这篇文章,你能为常用筛选和排序建立索引,并判断它的维护成本。


🔎 索引用来减少查找范围

建索引前后,查到的用户应该相同;索引要改善的是“怎么找到这些用户”。 例如按姓名找小明,没有姓名索引时可能要逐行检查姓名;有合适的索引后,数据库可以按姓名定位匹配记录,少检查不相关的数据。

本篇使用的 query_user 原始数据如下,建表与插入语句见 查询练习数据:

idusernameagecitycreated_at
1小明25北京2026-10-01 09:00:00
2小红30上海2026-10-02 09:00:00
3小刚22北京2026-10-03 09:00:00
4小林30上海2026-10-04 09:00:00
5小安NULL杭州2026-10-05 09:00:00

下面始终查询同一个用户,先观察没有姓名索引时的查找过程,再创建索引对比。五行数据很少,运行时间可能看不出差别,因此还要检查数据库采用的查找方式。


🔍 没有姓名索引时,要检查哪些记录

先不要创建姓名索引,查询小明:

SELECT id, username FROM query_user WHERE username = '小明';

查询结果:

idusername
1小明

此时表只有 id 主键索引。它方便按 id 找人,但我们提供的是姓名,数据库并不知道小明的 id 是多少。没有合适的姓名索引时,需要检查表中的记录,这叫全表扫描。

下面是逐行检查的过程示意,不表示数据库承诺固定的扫描顺序:

检查的记录姓名是否等于“小明”是否放入结果
1,小明是放入
2,小红否不放入
3,小刚否不放入
4,小林否不放入
5,小安否不放入

虽然只返回一行,但五行都需要检查。找到小明后也不能直接停,因为姓名没有唯一约束,后面仍可能有另一个小明。

先记录数据库的查询计划,也就是它准备采用的查找方式:

EXPLAIN SELECT id, username FROM query_user WHERE username = '小明';

EXPLAIN 返回的是计划信息,不是用户数据。没有姓名索引、采用全表扫描时,其中两个关键字段如下:

typekey
ALLNULL

ALL 表示全表扫描;key 为 NULL 表示没有选用索引。这时返回一行,不等于只检查了一行。


🛠 为常见姓名查询建索引

CREATE INDEX idx_query_username ON query_user (username);

idx_query_username 是索引名称。创建后,不需要在普通查询里手工指定这个名字:

SELECT id, username FROM query_user WHERE username = '小明';

查询结果:

idusername
1小明

结果仍然是小明。创建索引时,数据库会额外建立一套按姓名排序、能够按范围逐层查找的结构。查询为什么能少检查记录,下面单独拆开看。


🧱 索引为什么能跳过无关记录

索引会提前把值排好序,并记下各部分的范围和位置。查询时先看范围,就知道该去哪里找。

用年龄举例:我们要找 25 岁的用户。没有年龄索引时,需要逐个检查用户的年龄;有年龄索引时,查找过程可以这样理解:

先看索引中的范围目录
    ├─ 小于 25 岁         → 跳过
    ├─ 25 岁到不足 30 岁  → 进入这里,查找 25 岁的记录
    └─ 30 岁及以上        → 跳过

这是一张简化的查找示意图,只展示已填写年龄的部分,不代表五行练习数据真的被分成三块。

为什么能跳过? 因为索引已经按年龄排好:小于 25 的部分不可能有 25,30 及以上的部分也不可能有 25。数据库只需要进入中间部分,找出所有年龄为 25 的记录。

如果中间部分仍然很大,里面还可以有更细的范围目录,继续缩小查找范围。索引变快的原因,就是少检查了大量不符合条件的记录。

前面建的姓名索引同理:按姓名排序并记录范围,查“小明”时沿对应范围查找。目录由数据库自动建立和维护,你仍然写原来的 SELECT,不需要自己编写这套查找过程。


🔍 检查查询是否使用了姓名索引

再次检查同一条查询的计划:

EXPLAIN SELECT id, username FROM query_user WHERE username = '小明';

如果数据库选择按姓名索引查找,关键字段通常如下;这是对应查找方式的示意,不是保证每台机器都输出相同计划:

typekey
refidx_query_username

ref 表示按某个索引值查找匹配记录;key 显示使用了刚建的姓名索引。如果仍显示 ALL 和 NULL,说明这次没有选择它,不能声称已经减少了扫描。

对比项没有姓名索引,采用全表扫描使用姓名索引定位
返回的数据小明小明
查找过程逐行检查姓名,找出所有匹配者先定位索引中“小明”对应的记录
表中无关用户越来越多时需要检查的无关记录也增多通常可以避开大量无关记录

所以索引的用途是降低查找成本,而不是改变查询答案。 当数据少,或查询本来就要返回大部分记录时,建索引可能没有明显收益;还要承担索引占空间和维护写入的成本。

查看刚建立的索引

查看已有索引:

SHOW INDEX FROM query_user;

按本篇操作到这里,结果中与索引名称和字段有关的列如下,其他列省略:

Key_nameNon_uniqueSeq_in_indexColumn_name
PRIMARY01id
idx_query_username11username

Non_unique 为 0 表示不允许重复值,1 表示允许;Seq_in_index 是字段在该索引中的位置。

Key_name 中有主键 PRIMARY 和新索引。主键已经有索引,无需再给 id 建一个相同索引。


🧩 联合索引服务组合查询

接口经常“按城市筛选,再按年龄排序”时,可以建立:

CREATE INDEX idx_query_city_age ON query_user (city, age);

联合索引把多个字段按先后顺序一起组织,先按 city,再在同一城市内按 age。对应查询:

SELECT id, city, age FROM query_user
WHERE city = '北京' AND age >= 23 ORDER BY age;

查询结果:

idcityage
1北京25

返回小明的编号、城市和年龄。city 固定后,age 的范围和顺序就能在这一城市内部利用。

索引顺序不是随意的:按 city 查询、按 city 加 age 查询,都符合这个索引从左边开始的组织方式;只按 age 查询时,不能按同样方式直接定位城市范围。不要因此断言“绝不会使用任何索引”,实际执行方式仍要看计划。

WHERE 中条件书写的前后顺序不需要和索引列顺序一样,关键是哪些字段参与了筛选。


📄 覆盖索引避免读取不需要的完整记录

InnoDB 的普通索引除了索引列,还保存对应记录的主键。需要索引中没有的字段时,数据库会按主键再读取完整记录,这一步叫回表。

刚才的查询只读取 id、city、age,这些内容已包含在联合索引中。能从索引取得所需字段、不必再取完整记录的情况,叫覆盖索引。

如果还读取 username,而联合索引里没有它,就可能需要按主键取出姓名。先只查询真正需要的列,再评估是否值得扩展索引;不能为了覆盖所有查询把每个字段都塞进去。


🔤 前缀查询和包含查询不同

SELECT id, username FROM query_user WHERE username LIKE '小%';

查询结果:

idusername
1小明
2小红
3小刚
4小林
5小安

已知开头时,数据库有机会利用普通索引缩小范围。但本例每个人都姓“小”,筛选效果很弱,并不保证比扫描更划算。

两侧都有 % 的包含查询,没有确定的开头,通常不能像前缀查询一样定位一小段索引范围。它也可能扫描索引,不应简单描述成“完全不可能用索引”。


⚖️ 每个索引也需要维护

插入、删除或修改索引列时,数据库要同时维护相关索引,索引还占磁盘和缓存空间。因此优先为高频、耗时、能减少大量候选行的查询设计索引。

不要仅凭“字段值很少”就断定不值得建。某个状态只占少量记录且经常被查询时,索引仍可能有帮助;要结合实际数据分布。

确定某个索引不再需要后,才删除:

DROP INDEX idx_query_username ON query_user;

删除不会删除用户数据,只移除一种查找结构。后面的执行计划练习使用保留的 idx_query_city_age。


❓ 知识问答

Q1:唯一约束和普通索引有什么不同?

唯一约束还会拒绝重复值,并由唯一索引支持;普通索引只提供查找结构,不限制值重复。

Q2:为什么建索引后查询计划没有改变?

表很小、条件匹配大部分数据,或查询方式不适合这个索引时,数据库可能认为扫描更省成本。

Q3:联合索引越宽越好吗?

不是。更宽的索引占用更多空间,也增加写入成本,应围绕具体查询设计。


🧪 小练习

保留 city、age 联合索引,分别写出按城市、按城市和年龄、只按年龄的查询。下一篇用执行计划观察差异,不预先认定结果。

-- 请在这里编写 SQL 或记录验证结果