PostgreSQL与SQLAlchemy中含日期字段的多列复合索引最优设计咨询

我用SQLAlchemy定义了一张PostgreSQL表，现在要设计索引，纠结怎么排列internal_date和email_id的顺序才能让查询效率最高，或者是否需要用复合索引。

我的需求是按folder_id查询并获取最新的100封邮件。按folder_id、internal_date、email_id的顺序创建复合索引看起来最合理，但不确定效果是否理想，还是应该给每个字段单独建索引？

数据规模：数千个folder_id，数百万个email_id（一个文件夹包含多封邮件）。

class EmailMessages(db.Model):
    __tablename__ = "email_message_mapped_model"

    folder_id = db.Column(
        sa.Integer,
        db.ForeignKey("folders.folder_id", ondelete="CASCADE"),
        primary_key=True,
    )

    email_id = db.Column(
        sa.Integer,
        db.ForeignKey("emails.email_id", ondelete="CASCADE"),
        primary_key=True,
    )

    internal_date = db.Column(db.DateTime, default=datetime.utcnow)

方案一 #

__table_args__ = (
        db.Index(
            'ix_index1',
            folder_id, email_id, internal_date
        ),
    )

方案二 #

__table_args__ = (
        db.Index(
            'ix_index2',
            folder_id, internal_date, email_id
        ),
    )

方案三 #

__table_args__ = (
        db.Index(
            'ix_index3a',
            folder_id,
        ),
        db.Index(
            'ix_index3b',
            internal_date,
        ),
        db.Index(
            'ix_index3c',
            email_id,
        ),

    )

直接排除方案三 #

给三个字段单独建索引完全不适合你的查询需求。当你按folder_id过滤后需要按internal_date排序取top100时，PostgreSQL最多只能用到folder_id的单字段索引，取出该文件夹下的所有邮件后，还需要在内存或磁盘里做排序操作——如果某个文件夹下有几万甚至几十万封邮件，这个排序的性能开销会非常大，完全达不到高效查询的目的。

方案一 vs 方案二：方案二是最优选择 #

你的核心查询逻辑是WHERE folder_id = ? ORDER BY internal_date DESC LIMIT 100，复合索引的顺序直接决定了查询效率：

• 方案二（folder_id, internal_date, email_id）：这个索引的结构是先按folder_id分组，同一folder_id下按internal_date有序排列（PostgreSQL支持反向扫描升序索引实现降序查询，也可以在创建索引时显式指定internal_date.desc()），最后用email_id作为唯一标识（避免同一文件夹下出现相同internal_date的邮件时索引冲突）。查询时，数据库可以直接定位到目标folder_id的索引段，然后按顺序取前100条记录，不需要额外排序，性能拉满。
• 方案一（folder_id, email_id, internal_date）：这个索引是按folder_id分组后按email_id排序，internal_date的顺序是无序的。查询时，数据库只能先通过folder_id取出该文件夹下的所有邮件，再对这些邮件的internal_date进行排序，才能取到最新的100条——和方案三的问题一样，数据量大时排序成本极高。

另外补充：你的表主键是(folder_id, email_id)，PostgreSQL会自动为这个主键创建唯一索引，但这个索引的排序逻辑是folder_id + email_id，完全无法支持按internal_date排序的查询需求，所以必须单独创建方案二的复合索引。

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