解析SQL中樹形分層數(shù)據(jù)的查詢優(yōu)化

在數(shù)據(jù)查詢中，從2008開始SQL Server提供了一個(gè)新的數(shù)據(jù)類型hierarchyid，專門用來操作層次型數(shù)據(jù)結(jié)構(gòu)。

hierarchyid 類型對(duì)層次結(jié)構(gòu)樹中有關(guān)單個(gè)節(jié)點(diǎn)的信息進(jìn)行邏輯編碼的方法是：對(duì)從樹的根目錄到該節(jié)點(diǎn)的路徑進(jìn)行編碼。

這種路徑在邏輯上表示為一個(gè)在根之后被訪問的所有子級(jí)的節(jié)點(diǎn)標(biāo)簽序列。 表示形式以一條斜杠開頭，只訪問根的路徑由單條斜杠表示。 對(duì)于根以下的各級(jí)，各標(biāo)簽編碼為由點(diǎn)分隔的整數(shù)序列。 子級(jí)之間的比較就是按字典順序比較由點(diǎn)分隔的整數(shù)序列。 每個(gè)級(jí)別后面緊跟著一個(gè)斜杠。 因此斜杠將父級(jí)與其子級(jí)分隔開。 例如，以下是長(zhǎng)度分別為 1 級(jí)、2 級(jí)、2 級(jí)、3 級(jí)和 3 級(jí)的有效 hierarchyid 路徑：

? /

? /1/

? /0.3.-7/

? /1/3/

? /0.1/0.2/

在沒有hierarchyid的日子里，我們通過CTE的方式來查詢父以及全部的下級(jí)，但是，數(shù)據(jù)量多的情況下，CTE的方式將會(huì)變的很慢，后來，我們通過構(gòu)造PATH的方式來加快速度。那么，有了hierarchyid類型后，自然得使用hierarchyid了。

現(xiàn)在，通過一個(gè)實(shí)際的例子來看看hierarchyid的威力。

一：CTE方式

WITH CTEGetChild AS  
(  
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4' --and [State]=0 and AuditState=2
    UNION ALL  
     (
        SELECT A.* FROM EL_Organization.Organization AS A
        INNER JOIN CTEGetChild AS B ON a.PARENTID=B.ID  --and A.[State]=0 and A.AuditState=2
     )  
)

查詢出來4489行，需要25S。

看來CTE方式已經(jīng)到了不能容忍的地步，那么，現(xiàn)在，我們就用它來進(jìn)行優(yōu)化。

二：hierarchyid

首先，我們得新建該字段，然后為其賦值，

create function f_cidname(@id varchar(50)) returns varchar(max) as 
begin 
declare @pids nvarchar(max); 
declare @pNames nvarchar(max); 
set @pids=''; 
set @pNames=''; 
with cte as 
( select id,parentid,name from EL_Organization.Organization where id =@id--'00037fdf184e48d084b87c3499e3c0e5'
union all 
select b.id,b.parentid,b.name from cte A ,EL_Organization.Organization B where a.parentid = b.id 
)
select @pids=convert(varchar(32),Convert(int, Convert(varbinary(max), id)))  + '/'+ @pids from cte 
return [email protected]
end 
go

接著，我們需要Update全表：

UPDATE EL_Organization.Organization SET PIDS=dbo.f_cidname(id)

注意，id是guid的32位字符串，而hierarchyid字段不支持那么大的Path內(nèi)路徑，于是我們將GUID轉(zhuǎn)為了整型：convert(varchar(32),Convert(int, Convert(varbinary(max), id)))

2.1 TIP

Exception message: DataReader.GetFieldType(4) returned null. Exception data: System.Collections.ListDictionaryInternal

注意，極有可能我們把字段更新上去后，我們的程序卻出錯(cuò)了，如上。這個(gè)時(shí)候，我們需要把

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

這個(gè)DLL打包到我們的應(yīng)用程序中去。原因不解釋了。

看看效果吧，修改過后的代碼為：

DECLARE @tmpIds hierarchyid
SELECT @tmpIds=Pids FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4';
WITH CTEGetChild AS  (
    SELECT * FROM EL_Organization.Organization WHERE ID='ecc43c7159924dca91e2916368f923f4'
    UNION ALL(
    SELECT * FROM EL_Organization.Organization WHERE Pids.IsDescendantOf(@tmpIds)=1 
    )
)
SELECT * FROM CTEGetChild

現(xiàn)在，我們的時(shí)間到了1S內(nèi)。

2.2 一切為了不動(dòng)應(yīng)用層代碼

現(xiàn)在，既然，增加了一個(gè)字段，我們就要維護(hù)這個(gè)字段，如：本條記錄在應(yīng)用程序中被移動(dòng)到了別的父級(jí)下，就需要更新這個(gè)字段。為了不動(dòng)上層代碼，唯一能做的就是創(chuàng)建觸發(fā)器，即：原有的ParentId變動(dòng)的時(shí)候，就需要更新這個(gè)PIds字段，于是，我們創(chuàng)建觸發(fā)器如下：

create trigger UpdateOrgPIds
on EL_Organization.Organization
after update
as
if update ([ParentId])
begin
     declare @tmpId varchar(36)
     select @tmpId=id from inserted 
     update EL_Organization.Organization set pids=dbo.f_cidname(@tmpId)
end 
go
-- drop  trigger EL_Organization.UpdateOrgPIds

以上就是解析SQL中樹形分層數(shù)據(jù)的查詢優(yōu)化的詳細(xì)內(nèi)容，更多請(qǐng)關(guān)注php中文網(wǎng)其它相關(guān)文章！