sql server 2019 修復(fù)函數(shù)內(nèi)聯(lián) bug，速度提高 1000 倍

作者 | Jonathan Allen 譯者 | 無明

與過去幾十年出現(xiàn)的大多數(shù)數(shù)據(jù)庫(kù)一樣，SQL Server 允許開發(fā)人員通過創(chuàng)建函數(shù)來擴(kuò)展數(shù)據(jù)庫(kù)。但在即將發(fā)布的 SQL Server 2019 之前，SQL Server 對(duì)標(biāo)量函數(shù)的支持存在很多問題。

在 SQL Server 中，返回單個(gè)值的函數(shù)被稱為“標(biāo)量 UDF”或“標(biāo)量用戶定義函數(shù)”。這些標(biāo)量 UDF 由數(shù)據(jù)庫(kù)自動(dòng)歸類為確定性或非確定性的。例如，非確定性函數(shù)用于讀取當(dāng)前時(shí)間或從表中獲取數(shù)據(jù)。確定性函數(shù)始終為給定的參數(shù)集返回相同的值。理論上，數(shù)據(jù)庫(kù)如果知道正在使用的是確定性 UDF，那么就可以針對(duì)這些函數(shù)進(jìn)行優(yōu)化。

遺憾的是，SQL Server 從未將標(biāo)量 UDF 支持完全集成到執(zhí)行計(jì)劃生成器中。因此，它經(jīng)常會(huì)做一些不必要的工作，例如會(huì)在每一行上執(zhí)行確定性函數(shù)，但其實(shí)如果只針對(duì)每個(gè)唯一值執(zhí)行一次函數(shù)會(huì)更快。

重復(fù)使用之前的值多少次以上才會(huì)帶來實(shí)際的好處？這個(gè)很難說。除非數(shù)據(jù)是預(yù)先排序的，或者它知道可能的輸入數(shù)量是有限的，否則緩存函數(shù)參數(shù)和結(jié)果的成本可能會(huì)超過收益。而這并不是標(biāo)量 UDF 存在的唯一問題。

SQL Server 標(biāo)量 UDF 的另一個(gè)問題是它們給并行化帶來了阻礙。跨多個(gè) CPU 分發(fā)復(fù)雜查詢的能力是 SQL Server 的主要賣點(diǎn)。（很多開源替代品幾乎沒有并行支持或者只能依賴分布式數(shù)據(jù)庫(kù)）。如果沒有并行化，就很難證明 SQL Server 對(duì)得起它的價(jià)格。

說到價(jià)格，我們根本無法估計(jì)一個(gè) SQL Server 標(biāo)量函數(shù)究竟有多貴。所有的標(biāo)量函數(shù)，不管是簡(jiǎn)單的還是復(fù)雜的，在執(zhí)行計(jì)劃中都會(huì)被賦予一個(gè)默認(rèn)的成本。

標(biāo)量 UDF 與調(diào)用它們的查詢之間是單獨(dú)進(jìn)行解釋的。根據(jù)微軟的說法，對(duì)于每一行數(shù)據(jù)，都涉及到查詢與函數(shù)之間的上下文切換。我們可以假設(shè)上下文切換的成本可能會(huì)超過函數(shù)本身的成本。

出于這些原因，很多開發(fā)人員和 DBA 建議不要在對(duì)性能要求較高的代碼中使用標(biāo)量函數(shù)，盡管將標(biāo)量函數(shù)的內(nèi)容粘貼到需要它的每個(gè)查詢、視圖和存儲(chǔ)過程中會(huì)導(dǎo)致大量代碼重復(fù)。于是，我們經(jīng)常會(huì)聽到諸如“除了視圖之外的代碼重用不適合數(shù)據(jù)庫(kù)”這樣的說法。

標(biāo)量函數(shù)的性能損失不容小覷，例如這個(gè)簡(jiǎn)單的函數(shù)：

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))RETURNS DECIMAL (12,2) ASBEGIN RETURN @price * (1 - @discount);END

微軟研究員 Karthik Ramachandra 表示，這個(gè)標(biāo)量函數(shù)可能會(huì)導(dǎo)致一個(gè)通常只需要 1.6 秒的查詢變成 29 分 11 秒那么久。雖然沒有語義差異，速度卻慢了 1000 倍。

解決方法是使用“內(nèi)聯(lián)表值函數(shù)”或“內(nèi)聯(lián) TVF”代替標(biāo)量函數(shù)。表值函數(shù)通常會(huì)返回一組行，但也可以將它們改寫成只返回一行。這個(gè)時(shí)候，可以使用 CROSS APPLY 運(yùn)算符模擬正常的標(biāo)量函數(shù)調(diào)用。 SQL Server 2019 中的函數(shù)內(nèi)聯(lián)

從 SQL Server 2019 開始，可以內(nèi)聯(lián)用使用 T-SQL 編寫的標(biāo)量函數(shù)。這意味著它們可以被嵌入到查詢中，并且不會(huì)有 UDF 那樣的開銷。在查看執(zhí)行計(jì)劃時(shí)，包含邏輯代碼的查詢與使用內(nèi)聯(lián)標(biāo)量函數(shù)的查詢之間是沒有區(qū)別的。

這個(gè)新功能不僅限于簡(jiǎn)單的表達(dá)式。一些多語句 UDF 也可以被內(nèi)聯(lián)，甚至可以內(nèi)聯(lián)涉及從表中讀取數(shù)據(jù)的非確定性 UDF。它還以推斷出是否需要添加 JOIN 或 GROUP BY 運(yùn)算符，以便將查詢中的表與函數(shù)中的表組合在一起。

當(dāng)然，并非所有函數(shù)都可以被內(nèi)聯(lián)。要進(jìn)行內(nèi)聯(lián)，UDF 需要滿足以下要求：

滿足以下所有條件的標(biāo)量 T-SQL UDF 可以被內(nèi)聯(lián)：

使用以下構(gòu)造編寫的 UDF：

DECLARE、SET：變量聲明和賦值。

SELECT：具有單 / 多變量賦值的 SQL 查詢。

IF/ELSE：具有任意級(jí)別的嵌套分支。

RETURN：?jiǎn)蝹€(gè)或多個(gè)返回語句。

UDF：嵌套 / 遞歸函數(shù)調(diào)用。

其他：關(guān)系操作，如 EXISTS、ISNULL。

不調(diào)用任何與時(shí)間相關(guān)的內(nèi)部函數(shù)（例如 GETDATE()）或具有副作用的函數(shù)（例如 NEWSEQUENTIALID()）的 UFD。

使用 EXECUTE AS CALLER 子句（如果未指定 EXECUTE AS 子句，則這個(gè)為默認(rèn)行為）的 UDF。

不引用表變量或表值參數(shù)的 UDF。

調(diào)用了標(biāo)量 UDF，但其 GROUP BY 子句中不引用標(biāo)量 UDF 調(diào)用的查詢。

不是原生編譯的 UDF。

不是被用在計(jì)算列或檢查約束定義中的 UDF。

不引用用戶定義類型的 UDF。

沒有添加任何簽名的 UDF。

不是用于分區(qū)的 UDF。

對(duì)于每個(gè) T-SQL 標(biāo)量 UDF，sys.sql_modules 視圖中都有對(duì)應(yīng)的 is_inlineable 屬性，這個(gè)屬性用于指示 UDF 是否可以內(nèi)聯(lián)。值為 1 表示它是可內(nèi)聯(lián)的，0 表示不可以內(nèi)聯(lián)。對(duì)于所有內(nèi)聯(lián) TVF，這個(gè)屬性的值均為 1。對(duì)于所有其他模塊，該值為 0。

可以通過將數(shù)據(jù)庫(kù)兼容級(jí)別設(shè)置為小于 150 或者將 TSQL_SCALAR_UDF_INLINING 的作用域配置設(shè)置為 OFF 來禁用 UDF 內(nèi)聯(lián)。

也可以通過 OPTION (USE HINT(‘DISABLE_TSQL_SCALAR_UDF_INLINING’)) 在給定查詢上禁用內(nèi)聯(lián)。

你也可以在聲明函數(shù)時(shí)使用 WITH INLINE = OFF 來表示永久禁用內(nèi)聯(lián)。 Froid 項(xiàng)目和未來的機(jī)會(huì)

如果沒有 Froid 研究項(xiàng)目，可能就無法將該功能添加到 SQL Server。Froid 項(xiàng)目被描述為：

用于優(yōu)化關(guān)系數(shù)據(jù)庫(kù)命令式程序的可擴(kuò)展框架。Froid 自動(dòng)將整個(gè)用戶定義函數(shù)（UDF）轉(zhuǎn)換為關(guān)系代數(shù)表達(dá)式，并將它們嵌入到 SQL 查詢中。這種形式可以進(jìn)行基于成本的優(yōu)化，并且可以生成有效的、面向集合的并行計(jì)劃，而不是 UDF 那種低效、迭代、串行的執(zhí)行過程。Froid 還為 UDF 帶來了很多編譯器優(yōu)化，而不需要進(jìn)行額外的實(shí)現(xiàn)。我們介紹了 Froid 的設(shè)計(jì)，并展示了我們的實(shí)驗(yàn)評(píng)估，它在實(shí)際工作負(fù)載上帶來了多達(dá)數(shù)個(gè)數(shù)量級(jí)的性能改進(jìn)。

目前只知道 Froid 框架支持 T-SQL，但論文中也提到了 C#、Java、Python 和 R 語言。由于 SQL Server 現(xiàn)在支持這四門語言中的三門，因此將函數(shù)內(nèi)聯(lián)擴(kuò)展到其他語言會(huì)帶來很大好處。 英文原文

https://www.infoq.com/news/2019/01/SQL-Server-Scalar-UDF-Inlining