多列索引列选择咨询：如何为指定查询设计高效索引

用户数据表 #

问题描述 #

我有上述表格，希望创建索引以加快查询速度。所有查询大致如下：

查询1：

Select *
from users
where Type = 'SomeType'
  and First = 'SomeName1'
Order by CreatedAt DESC;

查询2：

Select *
from users
where Type = 'SomeType'
  and Middle = 'SomeName2'
Order by CreatedAt DESC;

查询3：

Select *
from users
where Type = 'SomeType'
  and Last = 'SomeName3'
Order by CreatedAt DESC;

我该如何对列建立索引以提升查询效率？CreatedAt应作为索引的首列吗？

我目前的想法是：

CREATE INDEX idx_users on users(CreatedAt, Type, First, Middle, Last)

其中CreatedAt和Type会被所有查询使用，而First、Middle、Last则根据查询不同而变化。

索引优化方案 #

你的当前索引设计存在明显问题，CreatedAt绝对不能放在索引首列，核心原因如下：
你的所有查询逻辑都是先通过Type+First/Middle/Last过滤数据，再按CreatedAt排序。如果把CreatedAt放在索引首列，数据库无法利用索引快速定位符合Type和姓名条件的行——因为索引是按时间排序的，相同类型和姓名的数据会分散在索引的各个位置，只能做全索引扫描，效率极低。

针对你的三个查询，最优策略是创建三个独立的复合索引：

1. 适配Type + First的查询：

CREATE INDEX idx_users_type_first_created ON users(Type, First, CreatedAt DESC);

2. 适配Type + Middle的查询：

CREATE INDEX idx_users_type_middle_created ON users(Type, Middle, CreatedAt DESC);

3. 适配Type + Last的查询：

CREATE INDEX idx_users_type_last_created ON users(Type, Last, CreatedAt DESC);

设计逻辑说明

• 复合索引遵循最左前缀匹配原则，把所有查询都必用的过滤条件Type放在首列，再加上每个查询独有的姓名列，最后是排序用的CreatedAt（直接指定降序，避免数据库额外执行排序操作）。
• 这样每个查询都能完全利用对应索引：先快速定位Type匹配的行，再筛选出姓名匹配的行，最后因为索引已经按CreatedAt降序排列，直接返回结果即可，全程无需额外排序或全表扫描，效率达到最优。

当前方案的问题解析

你设计的(CreatedAt, Type, First, Middle, Last)索引对现有查询几乎无效：

• 索引首列是CreatedAt，但你的查询没有指定CreatedAt的范围条件，数据库无法通过这个索引快速定位数据，只能做全索引扫描，效率和无索引相差无几。
• 即使后续列包含Type和姓名，也无法发挥最左前缀的优势——因为首列的过滤条件缺失，索引的结构无法支撑快速筛选目标数据。

内容的提问来源于stack exchange，提问作者Alan Chen