代码之家 › 专栏 › 技术社区 › Thomas

生成列与触发器列的性能优化

generated-columns db2

Thomas · 技术社区 · 3 年前

我使用的一个表有一个char(19)列,让我们称之为P。由于某些情况,我需要检查是否在P中(在P中字符串的末尾)找到了一个10个字符的变量。因此,我正在做: 其中P类似于CONCAT(“%”,variableName)。

相应地,表现也很糟糕。现在一个可能的解决方案是引入一个自动更新/计算的列,让我们用char(10)调用它P10。然后我可以使用类似variableName的P10,这更好。(尤其是在索引方面)。

现在的问题是,什么是性能方面更好的方法(我没有找到任何关于这方面的信息)? -Alter TABLE MyTable添加列P10 char(10)GENERATED ALWAYS AS substr(P,9,10) -或者用来自P的子字符串填充P10的触发事件

0 回复 | 直到 3 年前

The Impaler 3 年前

如果您确信列 P 总是完整的19个字符长,搜索值总是10个字符长。您可以对搜索表达式进行索引并使用它进行搜索(键入时与索引完全一样)。例如:

create table t (p varchar(19));

insert into t (p) values ('1234567890123456789');
insert into t (p) values ('1234567890555555555');

create index ix1 on t (substr(p, 10, 10));

select * from t where substr(p, 10, 10) = '0123456789';

请参阅上的运行示例 db<>fiddle 。

请注意 substr(p, 10, 10) 以与SELECT中的字母完全相同的方式键入。

或者,如果您想搜索字符串的最后一个字符,一般的解决方案是对反向值进行索引,然后使用反向模式进行搜索。通过这种方式 % 将位于搜索模式的末尾,引擎将自然使用索引。

Fred Sobotka 3 年前

GENERATED ALWAYS列只是一种方法

添加和索引派生/生成的 CHAR(10) 柱 P10 在桌子上 MyTable 不是加速搜索的最后十个字符的查询的唯一方法 CHAR(19) 柱 P ,但如果你最终选择了这种方法, 定义列 P10 像 GENERATED ALWAYS 提供了一些优于填充的优势 P10 通过触发器 除了相对简单并且由DBMS更严格地维护之外, 始终生成 列还提供了重要的上下文,即使语句没有引用生成的列,但以正确的方式引用了基列,查询优化器也可以利用这些上下文。

在问题中描述的场景中,添加列 P10 CHAR(10) GENERATED ALWAYS AS (SUBSTR(P, 10, 10)) 然后 CREATE INDEX ixmytblp10 ON MyTable (P10) 应至少受益于以下声明:

SELECT ... FROM MyTable ... WHERE P10 = '0516273849'; 
SELECT ... FROM MyTable ... WHERE SUBSTR(P, 10, 10) = '0246813579';

第一条语句引用 P10 直接受益,并将如预期的那样受益于该指数。第二条语句根本没有提到索引列,但查询优化器应该注意到该语句上有一个表达式 P 与 始终生成 柱 P10 然后利用上的索引 P10 。

查询解释实用程序是调优SQL时必备的工具

查询解释实用程序,例如 db2expln 或 db2exfmt 将显示给定SQL语句的详细访问计划,并显示该语句是否会使用您创建的索引。例如,explain实用程序不太可能为 ...WHERE MyTable.P LIKE '%' || some10charactervalue 因为 % LIKE模式开头的通配符实际上是 以结尾 通常扫描整个索引(如果不是整个表的话)的搜索。

可变长度注意事项

如果有任何机会 P 包含少于19个字符,您可能需要考虑替换 SUBSTR(P, 10, 10) 使用更灵活的表达式,例如 RIGHT(RTRIM(P), 10) 在 始终生成 列定义和SQL查询中,这样他们就可以更可靠地从列中获取最后十个非空白字符 P 不管它包含什么。

基于表达式的索引是另一种方法

这个问题的另一个答案正确地指出,如果您在列上创建基于表达式的索引,则不需要生成列 P 并确保您的应用程序在查询中使用该表达式。如果的每个非NULL值 P 正好包含19个字符,那么 CREATE INDEX ixmytblp10 ON MyTable (SUBSTR(P, 10, 10)) 应该有效,否则使用更灵活的方法 CREATE INDEX ixmytblp10 ON MyTable (RIGHT(RTRIM(P), 10))