基于不同值数量的索引列最优排序：哪种查询更高效？

问题背景 #

表结构 #

create table t (
    index_column_a integer not null,
    index_column_b integer not null,
    value_column_a integer not null,
    value_column_b character varying(256) not null,
    ...
);

数据特征 #

• 表内总记录数：100,000,000条
• index_column_a仅包含10个不同取值
• index_column_b包含10,000,000个不同取值
• index_column_a与index_column_b的组合具有唯一性

查询需求 #

每次查询固定指定一个index_column_a的具体值，同时搭配index_column_b的范围条件，示例SQL如下：

select index_column_a, index_column_b, value_column_b, ...
from   t
where  index_column_a = 5
and    index_column_b between 3200000 and 3210000;

索引选型对比 #

需要对比的两种唯一索引：

1. 索引A：

create unique index t_idx on t(index_column_a, index_column_b);

2. 索引B：

create unique index t_idx on t(index_column_b, index_column_a);

结论：索引A的查询效率远高于索引B #

核心原因：复合索引的「最左前缀匹配」规则 #

复合索引的排序和过滤逻辑是从左到右生效的，只有最左侧的列能被优先用于精准匹配或范围扫描：

• 对于索引A：
  首先通过index_column_a = 5精准匹配，直接定位到该列值对应的所有记录（约10,000,000条，占总数据的1/10）；之后在这个子集里，因为索引已经对index_column_b做了排序，数据库可以直接利用有序性快速定位between范围的目标记录，全程不需要扫描无关数据，效率极高。
• 对于索引B：
  查询条件里的精准匹配列index_column_a在索引的非最左位置，无法触发最左前缀匹配优化。数据库只能先扫描所有index_column_b处于指定范围的记录，再逐一校验index_column_a = 5的条件——这个范围里会包含所有index_column_a的10种取值，相当于扫描了大量无关数据，查询速度会慢很多。

虽然两个索引都能满足index_column_a+index_column_b的唯一性约束，但索引A完全贴合查询模式，能最大化利用索引的有序性和过滤能力。

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