在今天的文章里我想談下基數(shù)計(jì)算里的一個(gè)特定問(wèn)題：在查詢謂語(yǔ)里相關(guān)列的基數(shù)計(jì)算。首先我們看下在SQL Server 7.0起的操作方法，最后我們?cè)敿?xì)看下SQL Server 2014里，在查詢期間，處理相關(guān)列基數(shù)計(jì)算的全新實(shí)現(xiàn)方式。

在我們進(jìn)入問(wèn)題細(xì)節(jié)前，我們必須要澄清什么是相關(guān)列。當(dāng)我們看SQL Server使用的查詢優(yōu)化器時(shí)，查詢優(yōu)化器是基于4個(gè)核心假設(shè)：

• 獨(dú)立性（Independence）
• 一致性（Uniformity）
• 密封度（Containment）
• 夾雜物（Inclusion）

我不想細(xì)談每個(gè)假設(shè)，因?yàn)樗鼈冊(cè)谄渌陌灼锝忉尩梅浅Ｇ宄Ｔ谖恼履┪驳男〗Y(jié)部分你會(huì)找到白皮書的鏈接。今天我們要聚焦的是第1個(gè)假設(shè)——獨(dú)立性（Independence）。獨(dú)立性意味這在查詢謂語(yǔ)（WHERE子句）里用到的列是獨(dú)立的，當(dāng)各自查詢時(shí)，會(huì)返回不同的記錄。它們彼此間互不影響。遺憾的是這個(gè)假設(shè)并不都正確。我們來(lái)看一個(gè)具體的例子，這里違反了假設(shè)。假設(shè)下列2個(gè)查詢：

1 SELECT * FROM Sales.SalesOrderHeader2 WHERE SalesOrderID > 74000 AND SalesOrderID < 750003 GO4 5 SELECT * FROM Sales.SalesOrderHeader6 WHERE OrderDate >= '20080626' AND OrderDate <= '20080724'7 GO

第1個(gè)查詢返回999條記錄，第2個(gè)查詢返回1125條記錄。但符合這2個(gè)查詢條件的記錄是912條，這就是說(shuō)這2列之間是有關(guān)聯(lián)的。但是查詢優(yōu)化器并沒(méi)有意識(shí)到這點(diǎn)。

現(xiàn)在我們來(lái)看下SQL Server 7.0-2012是如何處理這個(gè)相關(guān)列的。在具體的執(zhí)行計(jì)劃里，第1個(gè)查詢里，查詢優(yōu)化器估計(jì)行數(shù)是999.936，第2個(gè)查詢里，查詢優(yōu)化器估計(jì)行數(shù)是1125。

用那些信息就與基數(shù)計(jì)算我們的表（這里表總記錄數(shù)是31465），我們可以計(jì)算出查詢謂語(yǔ)所謂的選擇度（Selectivity）。選擇度是0到1的數(shù)字。選擇度越小，從查詢返回的記錄越少（0表示0%的記錄返回，1表示100%的記錄返回）。我們可以通過(guò)估計(jì)行數(shù)除以表存儲(chǔ)的總記錄數(shù)來(lái)計(jì)算查詢謂語(yǔ)的選擇度。因此第1個(gè)查詢謂語(yǔ)的選擇度是0.03177931034482758620689655172414（999/31465），第2個(gè)查詢謂語(yǔ)的選擇度是0.03575401239472429683775623708883（1125/31465）?，F(xiàn)在我們來(lái)看下如果我們使用AND運(yùn)算符組合2個(gè)查詢謂語(yǔ)會(huì)發(fā)生什么：

1 SELECT * FROM Sales.SalesOrderHeader2 WHERE SalesOrderID > 74000 AND SalesOrderID < 750003 AND OrderDate >= '20080626' AND OrderDate <= '20080724'4 GO

當(dāng)你查看執(zhí)行計(jì)劃時(shí)，查詢優(yōu)化器在聚集索引查找（聚集的）（Clustered Index Seek (Clustered) ）運(yùn)算符上的估計(jì)行數(shù)是35.7517。

實(shí)際執(zhí)行返回是如剛才提到的912行。這個(gè)差異太大了。查詢優(yōu)化器只是把2個(gè)查詢謂語(yǔ)的選擇度值相乘得出最后的估計(jì)行數(shù)。SQL Server假設(shè)2個(gè)查詢謂語(yǔ)返回不同的行——假設(shè)這2列是彼此獨(dú)立的：

0.03177931034482758620689655172414 * 0.03575401239472429683775623708883 * 31465 = 35.7517241379310344827586206896586

當(dāng)然，事實(shí)是完全不一樣的，因?yàn)樵?個(gè)查詢謂語(yǔ)間有巨大的關(guān)聯(lián)。因此你會(huì)看到估計(jì)行數(shù)和行數(shù)之間有絕大的差異。在查詢里使用更多的AND組合各個(gè)查詢謂語(yǔ)，差異就會(huì)更大。當(dāng)最后估計(jì)將至1行時(shí)，查詢優(yōu)化器總會(huì)估計(jì)至少1行——從不估計(jì)0行。

你可能已經(jīng)聽(tīng)說(shuō)了，SQL Server 2014包含了一個(gè)新的基數(shù)計(jì)算。一旦你的數(shù)據(jù)庫(kù)是在120的兼容模式，新的基數(shù)計(jì)算就會(huì)用到。注意，當(dāng)你從老版本的SQL Server還原或附加數(shù)據(jù)庫(kù)時(shí)——這里的兼容性會(huì)變成老的！如果你想步改變兼容模式使用新的基數(shù)計(jì)算，你也可以使用新的2312跟蹤標(biāo)記。現(xiàn)在讓我們對(duì)查詢啟用2312跟蹤標(biāo)記來(lái)讓剛才的2個(gè)查詢謂語(yǔ)使用新的基數(shù)計(jì)算。

1 SELECT * FROM Sales.SalesOrderHeader2 WHERE SalesOrderID > 74000 AND SalesOrderID < 750003 AND OrderDate >= '20080626' AND OrderDate <= '20080724'4 OPTION (RECOMPILE, QUERYTRACEON  2312)5 GO

當(dāng)你查看執(zhí)行計(jì)劃時(shí)，你會(huì)看到基數(shù)計(jì)算已經(jīng)變了。

現(xiàn)在新的基數(shù)計(jì)算估計(jì)行數(shù)是188898.比剛才的老的基數(shù)計(jì)算的35.75行大很多。但到查詢實(shí)際返回的912行還是有個(gè)大的缺口。不過(guò)現(xiàn)在新的基數(shù)估計(jì)用的是什么公式呢？新的基數(shù)計(jì)算使用所謂的指數(shù)退避算法（Exponential Back-off algorithm）。查詢優(yōu)化器取走這4個(gè)查詢謂語(yǔ)，根據(jù)它們的選擇度排序。所有的選擇度再次相互相乘，但這里不同的是每個(gè)子過(guò)程值通過(guò)更大的平方根來(lái)軟化。我們來(lái)看下公式來(lái)來(lái)理解這個(gè)行為：

c0 * (c1 ^ 1/2) * (c2 ^1/4) * (c3 ^ 1/8)

我們來(lái)看下具體的例子，通過(guò)下列計(jì)算就可以獲得最終的基數(shù)：

0.03177931034482758620689655172414 * SQRT(0.03575401239472429683775623708883) * 31465 = 189.075212620762

比起188.898的估計(jì)行數(shù)我們的計(jì)算還有小差異，因?yàn)樵赟QL Server提供給執(zhí)行計(jì)劃里的估計(jì)行數(shù)是999.936行。使用指數(shù)退避算法，查詢優(yōu)化器可以確保做出更好的估計(jì)收緊估計(jì)行數(shù)和實(shí)際行數(shù)的洞，如果接受的查詢參數(shù)之間有關(guān)聯(lián)的話。

在這篇文章里我們談了關(guān)系數(shù)據(jù)庫(kù)里基數(shù)計(jì)算期間的特定問(wèn)題：作為查詢謂語(yǔ)使用的關(guān)聯(lián)列如何使用基數(shù)估計(jì)。在SQL Server 2014之前，查詢優(yōu)化器使用不同選擇值相乘，非常平穩(wěn)的方法。這會(huì)導(dǎo)致巨大的低估，如果執(zhí)行計(jì)劃里前一個(gè)運(yùn)算符（例如Sort或Hash運(yùn)算符）基于這些估計(jì)，它會(huì)引起麻煩。

SQL Server 2014新的基數(shù)計(jì)算對(duì)此特定問(wèn)題使用增強(qiáng)的機(jī)制：在基數(shù)計(jì)算期間使用指數(shù)退避公式，生成更好的估計(jì)。但和運(yùn)行時(shí)的實(shí)際行數(shù)還是有差異。如果你想了解更多SQL Server 2014新的基數(shù)計(jì)算，我建議看下Joe Sack寫的白皮書“使用SQL Server 2014參數(shù)計(jì)算優(yōu)化你的查詢計(jì)劃”。

感謝關(guān)注！

-----------------------------------------------------------------------

原文鏈接：https://www.sqlpassion.at/archive/2014/04/22/cardinality-estimation-for-correlated-columns/

Translated by:WoodyTu