如何提高 SQL 查询性能 在何处子句搜索串联值

Question

我有一个查询在运行时运行得非常好(快)，但是一旦我尝试按串联值进行搜索，性能就会下降。

我有哪些选择可以保持高性能？我知道显而易见的解决方案是不按串联字符串进行搜索，但在某些情况下，我忍不住这样做。我该如何处理这些情况。

示例 1：快速运行

Select * From
(
With Exmp1 AS 
(
Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
FROM Table1 b 
Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
)
Select USERNAME, RCD
From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 
)
Where USERNAME = 'test1'

示例 2：运行缓慢

Select * From
(
With Exmp1 AS 
(
Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
FROM Table1 b 
Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
)
Select USERNAME || '@domain.com', RCD
From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 
)
Where USERNAME = '[e-mail]'

如果您想知道为什么我将整个查询包装在选择中，则使用此查询的应用程序期望查询以"选择"开头。我已经尝试在没有"with"子句的情况下重写它，但我得到了相同的结果。

Select USERNAME || '@domain.com', RCD
From MainTable MT Inner Join 
(Select ID, RCD 
From Table1 a 
Where EFFDT = (Select Max(b.EFFDT) FROM Table1 b Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
) E1 ON MT.ID = E1.ID 
Where USERNAME|| '@domain.com' = '[e-mail]'

============编辑以获取更多信息：

表 1 有四列：ID、RCD、EFFDT、STATUS。重要的是，我只能获取最新的记录 (EFFDT) 和状态值为"A"的记录。

主表有两列：ID、用户名。我无法更改此表或创建其他表，数据库视图。我必须使用目前存在的这些数据，并且仅以SQL查询的形式进行交互。

对于给定 [e-mail]，所需的结果是 [e-mail] 和 RCD。

请注意，这只是一个具体的例子，感谢有关该主题的一般帮助。

Answer 1

连接字符串时，会阻止 SQL 优化器在MainTable (USERNAME)上使用现有索引。这迫使发动机遵循不同的[较慢]路径;可能是堆 [表] 扫描。就这么简单。

如果您真的需要提供完整的电子邮件地址，我会在最后一步而不是之前计算串联，基本上回到您的第一个选项。例如：

Select USERNAME || '@domain.com', RCD From
(
With Exmp1 AS 
(
Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
FROM Table1 b 
Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
)
Select USERNAME, RCD
From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 
)
Where USERNAME = 'test1'

编辑：

更进一步，您可以改写整个查询，并在简化查询后找出哪些优化很容易看到：

• 问题是列MT.USERNAME可能比a.STATUS更具选择性，因此您应该先按它进行过滤。
• 然后，为了使相关的子查询快速，您可能希望在其上使用"覆盖索引"，因此我建议添加如下所示的ix2。

例如：

Select
MT.USERNAME || '@domain.com', a.RCD 
From MainTable MT
join Table1 a on a.ID = MT.ID
where MT.USERNAME = 'test1'
and a.status = 'A'
and a.EFFDT = (
Select Max(b.EFFDT) FROM Table1 b Where a.ID = b.ID and a.RCD = b.RCD
)

现在，为了使此查询真正快速，您需要以下索引。看来你已经有了第一个：

create index ix1 on MainTable (USERNAME); -- You already have this one
create index ix2 on Table1 (ID, RCD, EFFDT);

第二次编辑：如果您确实想使用完整的用户名进行搜索，则可以在表达式上添加索引。以"示例 2"为例，更改WHERE条件，如下所示：

Select * From
(
With Exmp1 AS 
(
Select ID, RCD From Table1 a where EFFDT = (Select Max(b.EFFDT) 
FROM Table1 b 
Where a.ID = b.ID and a.RCD = b.RCD) and status = 'A'
)
Select USERNAME || '@domain.com', RCD
From MainTable MT Inner Join Exmp1 E1 ON MT.ID = E1.ID 
)
Where USERNAME || '@domain.com' = '[e-mail]' -- changed here

然后添加以下索引：

create index ix3 on MainTable (USERNAME || '@domain.com');

这应该使查询速度更快，因为筛选前缀将与索引完全匹配。