超大规模数据集的索引与键优化方案咨询

问题背景 #

我有两张互相关联的表，每张表约含2亿条记录，表结构定义如下：

CREATE TABLE [dbo].[AS_tblTBCDEF](
    [CDEF_SOC_NUM] [numeric](5, 0) NULL,
    [CDEF_EFF_DATE] [date] NULL,
    [CDEF_TYP_BUS] [nvarchar](1) NULL,
    [CDEF_CLASS_NUM] [smallint] NULL,
    [CDEF_GROUP] [smallint] NULL,
    [CDEF_COV_EXP_TYP] [nvarchar](1) NULL,
    [CDEF_SCHEDULE] [nvarchar](9) NULL,
    [CDEF_LIMIT] [numeric](9, 2) NULL,
    [CDEF_LIMIT_PCTILE] [nvarchar](2) NULL,
    [CDEF_WHY_NOT_COV] [smallint] NULL,
    [CDEF_PROVIEW_GRP] [smallint] NULL,
    [CDEF_BAS_ADJ_IND] [nvarchar](1) NULL,
    [CDEF_BAS_ADJ_AMT] [numeric](9, 2) NULL,
    [CDEF_DEF_TYPE] [nvarchar](1) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[AS_tblTBCDEFD](
    [CDEF_DESC_SOC_NUM] [numeric](5, 0) NULL,
    [CDEF_DESC_EFF_DATE] [date] NULL,
    [CDEF_DESC_TYP_BUS] [nvarchar](1) NULL,
    [CDEF_DESC_CLASS] [smallint] NULL,
    [CDEF_DESC_GROUP] [smallint] NULL,
    [CDEF_DESC_TEXT] [nvarchar](77) NULL
) ON [PRIMARY]
GO

两表的连接逻辑如下：

FROM [dbo].[AS_tblTBCDEF] GC_TBCDEF
    LEFT JOIN [dbo].[AS_tblTBCDEFD] GC_TBCDEFD 
        ON (GC_TBCDEF.CDEF_GROUP = GC_TBCDEFD.CDEF_DESC_GROUP) 
        AND (GC_TBCDEF.CDEF_CLASS_NUM = GC_TBCDEFD.CDEF_DESC_CLASS) 
        AND (GC_TBCDEF.CDEF_TYP_BUS = GC_TBCDEFD.CDEF_DESC_TYP_BUS) 
        AND (GC_TBCDEF.CDEF_EFF_DATE = GC_TBCDEFD.CDEF_DESC_EFF_DATE) 
        AND (GC_TBCDEF.CDEF_SOC_NUM = GC_TBCDEFD.CDEF_DESC_SOC_NUM)

这两张表由其他部门的ETL脚本每月重建一次，目前执行上述连接查询时只会超时，无法返回结果。我已尝试添加以下单字段索引，但优化效果不明显，现寻求进一步优化方案：

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_Soc_Num')
CREATE INDEX idx_Soc_Num
ON [dbo].[AS_tblTBCDEF] (CDEF_SOC_NUM);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_Class_Num')
CREATE INDEX idx_Class_Num
ON [dbo].[AS_tblTBCDEF] (CDEF_CLASS_NUM);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_Eff_Date')
CREATE INDEX idx_Eff_Date
ON [dbo].[AS_tblTBCDEF] (CDEF_EFF_DATE);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_Typ_Bus')
CREATE INDEX idx_Typ_Bus
ON [dbo].[AS_tblTBCDEF] (CDEF_TYP_BUS);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_Group')
CREATE INDEX idx_Group
ON [dbo].[AS_tblTBCDEF] (CDEF_GROUP);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_Soc_Num')
CREATE INDEX idx_Soc_Num
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_SOC_NUM);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_Class_Num')
CREATE INDEX idx_Class_Num
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_CLASS);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_Eff_Date')
CREATE INDEX idx_Eff_Date
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_EFF_DATE);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_Typ_Bus')
CREATE INDEX idx_Typ_Bus
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_TYP_BUS);

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_Group')
CREATE INDEX idx_Group
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_GROUP);

优化建议 #

1. 创建匹配连接条件的复合覆盖索引 #

单字段索引无法高效支持多字段连接逻辑，需创建包含所有连接条件的复合索引，同时将选择性高的字段（如CDEF_SOC_NUM、CDEF_EFF_DATE）放在索引前列，再通过INCLUDE子句包含查询需要返回的所有字段，避免回表查询：

主表（AS_tblTBCDEF）索引

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEF]') AND NAME ='idx_TBCDEF_JoinKey')
CREATE NONCLUSTERED INDEX idx_TBCDEF_JoinKey
ON [dbo].[AS_tblTBCDEF] (CDEF_SOC_NUM, CDEF_EFF_DATE, CDEF_TYP_BUS, CDEF_CLASS_NUM, CDEF_GROUP)
INCLUDE (CDEF_COV_EXP_TYP, CDEF_SCHEDULE, CDEF_LIMIT, CDEF_LIMIT_PCTILE, CDEF_WHY_NOT_COV, CDEF_PROVIEW_GRP, CDEF_BAS_ADJ_IND, CDEF_BAS_ADJ_AMT, CDEF_DEF_TYPE);

从表（AS_tblTBCDEFD）索引

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = object_id('[dbo].[AS_tblTBCDEFD]') AND NAME ='idx_TBCDEFD_JoinKey')
CREATE NONCLUSTERED INDEX idx_TBCDEFD_JoinKey
ON [dbo].[AS_tblTBCDEFD] (CDEF_DESC_SOC_NUM, CDEF_DESC_EFF_DATE, CDEF_DESC_TYP_BUS, CDEF_DESC_CLASS, CDEF_DESC_GROUP)
INCLUDE (CDEF_DESC_TEXT);

2. 考虑设置主键或唯一约束 #

如果连接字段的组合能唯一标识单条记录（需业务层面确认无重复），可将其设为聚集主键，聚集索引的查询效率远高于非聚集索引：

-- 主表主键示例
ALTER TABLE [dbo].[AS_tblTBCDEF] 
ADD CONSTRAINT PK_AS_tblTBCDEF PRIMARY KEY CLUSTERED (CDEF_SOC_NUM, CDEF_EFF_DATE, CDEF_TYP_BUS, CDEF_CLASS_NUM, CDEF_GROUP);

-- 从表主键示例
ALTER TABLE [dbo].[AS_tblTBCDEFD] 
ADD CONSTRAINT PK_AS_tblTBCDEFD PRIMARY KEY CLUSTERED (CDEF_DESC_SOC_NUM, CDEF_DESC_EFF_DATE, CDEF_DESC_TYP_BUS, CDEF_DESC_CLASS, CDEF_DESC_GROUP);

若组合存在重复，可改用唯一非聚集索引替代主键。

3. 强制更新统计信息 #

ETL每月重建表后，需及时更新全量统计信息，确保查询优化器能生成最优执行计划：

UPDATE STATISTICS [dbo].[AS_tblTBCDEF] WITH FULLSCAN;
UPDATE STATISTICS [dbo].[AS_tblTBCDEFD] WITH FULLSCAN;

4. 实施表分区（可选） #

针对2亿级大表，可按时间字段（CDEF_EFF_DATE）进行分区，减少查询时扫描的数据范围：

-- 创建按年度分区的函数和方案（可根据业务调整分区粒度）
CREATE PARTITION FUNCTION PF_TBCDEF_Date (date)
AS RANGE RIGHT FOR VALUES ('2023-01-01', '2024-01-01', '2025-01-01');

CREATE PARTITION SCHEME PS_TBCDEF_Date
AS PARTITION PF_TBCDEF_Date
ALL TO ([PRIMARY]);

-- 重建主表使用分区方案
CREATE TABLE [dbo].[AS_tblTBCDEF](
    -- 字段定义与原表一致
) ON PS_TBCDEF_Date (CDEF_EFF_DATE);

5. 删除冗余单字段索引 #

之前创建的单字段索引在复合索引生效后会冗余，增加索引维护成本，可清理：

DROP INDEX IF EXISTS idx_Soc_Num ON [dbo].[AS_tblTBCDEF];
DROP INDEX IF EXISTS idx_Class_Num ON [dbo].[AS_tblTBCDEF];
DROP INDEX IF EXISTS idx_Eff_Date ON [dbo].[AS_tblTBCDEF];
DROP INDEX IF EXISTS idx_Typ_Bus ON [dbo].[AS_tblTBCDEF];
DROP INDEX IF EXISTS idx_Group ON [dbo].[AS_tblTBCDEF];

DROP INDEX IF EXISTS idx_Soc_Num ON [dbo].[AS_tblTBCDEFD];
DROP INDEX IF EXISTS idx_Class_Num ON [dbo].[AS_tblTBCDEFD];
DROP INDEX IF EXISTS idx_Eff_Date ON [dbo].[AS_tblTBCDEFD];
DROP INDEX IF EXISTS idx_Typ_Bus ON [dbo].[AS_tblTBCDEFD];
DROP INDEX IF EXISTS idx_Group ON [dbo].[AS_tblTBCDEFD];

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