sql - 使用子字符串与使用通配符的 SQL 比较的性能

Question

我正在处理 2 个表之间的连接条件，其中要匹配的列之一是值的串联。我需要将 tableA 中的 columnA 连接到 tableB 中 columnB 的前 2 个字符。

我开发了 2 种不同的语句来处理这个问题，并尝试分析每种方法的性能。

方法一:

ON tB.columnB   like  tA.columnA || '%'

方法二:

ON substr(tB.columnB,1,2) = tA.columnA

与方法 2 相比，使用方法 1 的查询执行计划的步骤要少得多，但是，方法 2 的执行速度似乎要快得多。此外，执行计划显示了方法 2 的推荐索引，可以提高其性能。

我在 IBM iSeries 上运行它，但对一般意义上的答案感兴趣，以了解有关 sql 查询优化的更多信息。

方法 2 执行得更快是否有意义？

这个问题很相似，但似乎没有人对这些方法的性能差异提供任何具体的答案: T-SQL speed comparison between LEFT() vs. LIKE operator .

PS:需要这种类型连接的表设计不是我目前可以更改的。我意识到最好将包含不同类型数据的字段分开。

Answer 1

我在 IBM Data Studio 的 SQL Advisor 中对 DB2 LUW 10.1 数据库中的一个表运行了以下命令:

SELECT *
FROM PDM.DB30
WHERE DB30_SYSTEM_ID = 'XXX'
    AND DB30_VERSION_ID = 'YYY'
    AND SUBSTR(DB30_REL_TABLE_NM, 1, 4) = 'ZZZZ'

和

SELECT * 
FROM PDM.DB30 
WHERE DB30_SYSTEM_ID = 'XXX' 
    AND DB30_VERSION_ID = 'YYY' 
    AND DB30_REL_TABLE_NM LIKE 'ZZZZ%' 

它们都有完全相同的访问路径，使用相同的索引、相同的估计 IO 成本和相同的估计基数，唯一的区别是 LIKE 的估计总 CPU 成本是 178,343.75，而 SUBSTR 是 197,518.48(大约 10% 的差异)。

不过，两者的累计总成本相同，因此根据顾问的说法，这种差异可以忽略不计。