优化 CLUSTERED INDEX 以与 JOIN 一起使用

Question

表 optin_channel_1（每个“通道”都有一个专用表）

CREATE TABLE [dbo].[optin_channel_1](
    [key_id] [bigint] NOT NULL,
    [valid_to] [datetime] NOT NULL,
    [valid_from] [datetime] NOT NULL,
    [key_type_id] [int] NOT NULL,
    [optin_flag] [tinyint] NOT NULL,
    [source_proc_id] [int] NOT NULL,
    [date_inserted] [datetime] NOT NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [ix_id] ON [dbo].[optin_channel_1] 
(
    [key_type_id] ASC,
    [key_id] ASC,
    [valid_to] ASC,
    [valid_from] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

表 profile_conns

CREATE TABLE [dbo].[profile_conns](
    [profile_key_id] [bigint] NOT NULL,
    [valid_to] [datetime] NOT NULL,
    [valid_from] [datetime] NOT NULL,
    [conn_key_id] [bigint] NOT NULL,
    [conn_key_type_id] [int] NOT NULL,
    [conn_type_id] [int] NOT NULL,
    [source_proc_id] [int] NOT NULL,
    [date_inserted] [datetime] NOT NULL
) ON [PRIMARY]

CREATE CLUSTERED INDEX [ix_id] ON [dbo].[profile_conns] 
(
    [profile_key_id] ASC,
    [conn_key_type_id] ASC,
    [conn_key_id] ASC,
    [valid_to] ASC,
    [valid_from] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

表 lu_channel_conns

CREATE TABLE [dbo].[lu_channel_conns](
    [channel_id] [int] NOT NULL,
    [conn_type_id] [int] NOT NULL,
 CONSTRAINT [PK_lu_channel_conns] PRIMARY KEY CLUSTERED 
(
    [channel_id] ASC,
    [conn_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

表 lu_conn_type

CREATE TABLE [dbo].[lu_conn_type](
    [conn_type_id] [int] NOT NULL,
    [default_key_type_id] [int] NOT NULL,
    [master_key_type_id] [int] NOT NULL,
    [date_inserted] [datetime] NOT NULL,
 CONSTRAINT [PK_lu_conns] PRIMARY KEY CLUSTERED 
(
    [conn_type_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

视图 v_source_proc_id_by_group_id

SELECT DISTINCT x.source_proc_id, x.source_proc_group_id
FROM lu_source_proc x INNER JOIN lu_source_proc_group y ON x.source_proc_group_id = y.group_id

将执行一个动态 SQL 语句：

SET @sql_str='SELECT @ret=MAX(o.optin_flag)
    FROM optin_channel_'+CAST(@channel_id AS NVARCHAR(100))+' o
    INNER HASH JOIN dbo.v_source_proc_id_by_group_id y ON o.source_proc_id=y.source_proc_id AND y.source_proc_group_id=@source_proc_group_id
    INNER HASH JOIN profile_conns z ON z.profile_key_id=cast(@profile_key_id AS NVARCHAR(100)) AND z.conn_key_type_id=o.key_type_id AND z.conn_key_id=o.[key_id] AND z.valid_to=''01.01.3000''
    INNER HASH JOIN lu_channel_conns x ON x.channel_id=@channel_id AND z.conn_type_id=x.conn_type_id
    INNER HASH JOIN lu_conn_type ct ON ct.conn_type_id=x.conn_type_id AND ct.default_key_type_id=o.key_type_id'
SET @param='@channel_id INT, @profile_key_id INT, @source_proc_group_id INT, @ret NVARCHAR(400) OUTPUT'
EXEC sp_executesql @sql_str,@param,@channel_id,@profile_key_id,@source_proc_group_id,@ret OUTPUT

即，这给出：

SELECT @ret=MAX(o.optin_flag) AS optin_flag
FROM optin_channel_1 o
INNER HASH JOIN dbo.v_source_proc_id_by_group_id y 
    ON o.source_proc_id=y.source_proc_id 
    AND y.source_proc_group_id=5
INNER HASH JOIN profile_conns z 
    ON z.profile_key_id=1 
    AND z.conn_key_type_id=o.key_type_id 
    AND z.conn_key_id=o.[key_id] 
    AND z.valid_to='01.01.3000'
INNER HASH JOIN lu_channel_conns x 
    ON x.channel_id=1 
    AND z.conn_type_id=x.conn_type_id
INNER HASH JOIN lu_conn_type ct 
    ON ct.conn_type_id=x.conn_type_id 
    AND ct.default_key_type_id=o.key_type_id

这些表用于 optin 数据库。 optin_flag 可以是 0 或 1。通过最后一条语句，我想从给定 channel_id= 的 optin_channel_1 获取 1 作为 optin_flag 1 对于具有 profile_key_id=1 的用户，当 optin 由属于 source_proc_group_id=5 的进程插入数据库时​​。我希望这足以理解正在发生的事情。

这是使用CLUSTERED INDEX的最佳方式吗？或者最好从 profile_conns 上的索引中删除 profile_key_id 并将 z.profile_key_id=1 放在 WHERE 中条款？

可能有更好的方法来优化此选择（不可能更改数据库模式，只能更改索引和修改语句）。

Answer 1

如果不知道表的大小以及表中存储的数据类型，就很难衡量。

假设 optin_channel_1 有很多数据并且 profile_cons 有很多数据，我会尝试以下操作：

• optin_channel_1(key_id) 或 key_type_id 上的聚集索引，具体取决于哪个字段具有最不同的值。 （因为你没有覆盖索引）
• profile_conns（cons_key_id）或cons_key_type_id上​​的聚集索引取决于你在optin_channel_1
• 等中选择的内容...

基本上，如果你的表profile_conns表没有太多数据，我会放置聚集索引在最分散的“过滤器”字段上（我怀疑 profile_key_id ）。如果表有大量数据，我会进行哈希/合并连接，并将聚集索引与 optin_channel_1 表的聚集索引进行匹配。

我还会这样重写查询：

SELECT @ret = MAX(o.optin_flag) AS optin_flag
  FROM optin_channel_1 o
  JOIN dbo.v_source_proc_id_by_group_id y 
    ON o.source_proc_id = y.source_proc_id  
  JOIN profile_conns z 
    ON z.conn_key_type_id = o.key_type_id 
   AND z.conn_key_id = o.[key_id] 
  JOIN lu_channel_conns x 
    ON z.conn_type_id = x.conn_type_id
  JOIN lu_conn_type ct 
    ON ct.conn_type_id = x.conn_type_id 
   AND ct.default_key_type_id=o.key_type_id 
 WHERE y.source_proc_group_id = 5
   AND z.profile_key_id = 1 
   AND x.channel_id = 1 
   AND z.valid_to = '01.01.3000'

查询会这样更改，因为：

• 将过滤条件放在 where 子句中会向您显示哪些相关字段旨在进行哈希/合并连接 放置
• 连接提示很少是一个好主意。很难击败查询调控器来确定最佳查询计划。糟糕的计划通常表明您的索引/统计数据存在问题。

综上所述：

• 小表连接到大表==>进行嵌套循环 &将聚集索引集中在小表中的“过滤器”字段上大表中的连接字段。
• 大表连接到大表 =>进行哈希/合并连接，并将聚集索引放在两侧的匹配字段上，
• 多字段索引通常只有在“覆盖”时才是一个好主意，这意味着您查询的所有字段都包含在索引中。 （或包含在 include() 子句中）