您當(dāng)前位置：首頁 > 數(shù)據(jù)庫 > Sqlserver > 使用SQL Server2005擴(kuò)展函數(shù)進(jìn)行性能優(yōu)化

使用SQL Server2005擴(kuò)展函數(shù)進(jìn)行性能優(yōu)化

來源：程序員人生發(fā)布時(shí)間：2014-05-13 04:13:40 閱讀次數(shù)：4089次

SQL Server2005擴(kuò)展函數(shù)已經(jīng)不是一件什么新鮮的事了，但是我看網(wǎng)上的大部分都是說聚合函數(shù)，例子也比較淺，那么這里就講講我運(yùn)用擴(kuò)展函數(shù)來優(yōu)化數(shù)據(jù)庫性能的例子，希望和大家一起分享這個(gè)經(jīng)驗(yàn)。如果你還不知道什么是SQLCLR，那么你可以參考：SQL Server擴(kuò)展函數(shù)的基本概念。

需求說明

大家在使用SQL Server開發(fā)的時(shí)候一定會(huì)遇到這樣的需求，那就是通過Table_Name1表的兩個(gè)字段Column1、Column2來查詢在Table_Name2表中符合這兩個(gè)條件的記錄，并返回Table_Name2中的字段Column3，面對這樣的需求，你也許會(huì)說使用表連接就可以了，對的，沒錯(cuò)，我也是這樣想的，但是有的時(shí)候往往要面對不同的突發(fā)情況，那就是并不是一定會(huì)Column1與Column2是全匹配的查詢，可能中間還需要一些邏輯的處理，比如字符串的截取后再匹配等等。

這個(gè)時(shí)候我們通常會(huì)在SQL Server中寫一個(gè)函數(shù)，這個(gè)函數(shù)接收兩個(gè)參數(shù)：Column1、Column2，函數(shù)體里面做一些邏輯處理，在通過處理好的參數(shù)去查詢Table_Name2表，并返回相應(yīng)的值。很好，那下面我們來計(jì)算下圖中數(shù)據(jù)的查詢情況。假設(shè)表1的數(shù)據(jù)有50W，表2的數(shù)據(jù)有4W，在表2沒有索引的條件下，查詢的復(fù)雜度就有50W*4W了，兩個(gè)表都需要做全表掃描，表2的全表掃描就會(huì)達(dá)到50W次。

（圖1：需求說明）

優(yōu)化1：這一個(gè)優(yōu)化，每個(gè)開發(fā)人員都知道，那就是對表2的兩個(gè)查詢字段分別建立索引。這樣的優(yōu)化和之前相比，性能將會(huì)提高N個(gè)等級。

優(yōu)化2：這第二個(gè)優(yōu)化方法是使用SQL Server的復(fù)合索引，在表2上創(chuàng)建一個(gè)復(fù)合索引，這個(gè)符合索引包括需要查詢的兩個(gè)字段，其實(shí)就是把兩個(gè)字段的內(nèi)容生成一個(gè)索引，其中索引包含了兩個(gè)索引的排序。

優(yōu)化3：這第三個(gè)優(yōu)化方法是使用SQL Server2005之后版本才有的索引-包含性索引（Include），就是在優(yōu)化2的基礎(chǔ)上，把需要返回的字段也一起放入到索引中，這樣的查詢就只需要查詢索引就夠了，不需要再讀取數(shù)據(jù)頁了，減少磁盤的IO消耗。不過這個(gè)方法也不是萬能，因?yàn)橛袝r(shí)可能返回的字段會(huì)比較多，有時(shí)幾個(gè)字段加起來的長度有可能超出了900個(gè)字符（索引大小范圍），如果想了解可以進(jìn)入：SQL Server 索引中include的魅力（具有包含性列的索引）

優(yōu)化4：在不考慮一些分區(qū)、分表、分到不同的磁盤等優(yōu)化方式的情況下，我們是否還能進(jìn)一步優(yōu)化我們的查詢呢？這就是這篇文章想要告訴你的，因?yàn)槲覀兊幕卮鹗牵河械摹Ｄ蔷褪峭ㄟ^SQLCLR的UDT，把表2的數(shù)據(jù)一次性加載到內(nèi)存，那么在進(jìn)行表1查詢的時(shí)候，我們不需要通過B+樹來查詢數(shù)據(jù)了，直接到內(nèi)存中查詢，這樣之所以快是因?yàn)椴僮鲀?nèi)存要比操作磁盤要快得多。這其中會(huì)有些局限性和缺點(diǎn)，具體見下面的缺點(diǎn)描述。

設(shè)計(jì)思路

去數(shù)據(jù)庫中把表2讀取出來，并放到private static readonly IDictionary<string, string> resultCollectionDic的靜態(tài)變量中。在數(shù)據(jù)庫服務(wù)啟動(dòng)的時(shí)候是會(huì)初始化SQLCLR函數(shù)的，所以在啟數(shù)據(jù)庫服務(wù)的時(shí)候，也一起把表2的數(shù)據(jù)保存到了內(nèi)存當(dāng)中了。
上面的查詢中包括了兩個(gè)字段Column1、Column2和一個(gè)返回字段Column3，那么我們?nèi)绾伟堰@些數(shù)據(jù)保存到IDictionary字典當(dāng)中呢？我的做法就是把Column1、Column2的中間加一個(gè)字符“+”，把這個(gè)字符串作為Key值，把Column3這個(gè)返回值做為Value，這樣就解決了多個(gè)And的查詢的問題。這個(gè)會(huì)有些局限性，具體可以見下面的缺點(diǎn)描述。
在函數(shù)FunctionImsi2HLR2中傳進(jìn)的兩個(gè)字符后，就要進(jìn)行上面的拼湊方式來拼湊Key值，再到IDictionary中查詢。

測試結(jié)果

測試數(shù)據(jù)：表2有4.6732萬條記錄，表1有54.2524萬條記錄。

經(jīng)過測試：

優(yōu)化1方法（單獨(dú)索引）的時(shí)間是106秒
優(yōu)化3方法（包含性索引）的時(shí)間是45秒
優(yōu)化4方法（擴(kuò)展函數(shù)）的時(shí)間是33秒

代碼

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
//經(jīng)過測試發(fā)現(xiàn)：使用Hashtable和SortedList沒有使用IDictionary的性能好.
//IDictionary<string, string>中使用string比SqlString的性能要高.
private static readonly IDictionary<string, string> resultCollectionDic = new Dictionary<string, string>();

static UserDefinedFunctions()
{
GetTableFromDB(resultCollectionDic);
}

/// <summary>
/// 從數(shù)據(jù)庫中獲取某個(gè)表的數(shù)據(jù).
/// </summary>
/// <param name="resultCollection"></param>
private static void GetTableFromDB(IDictionary<string, string> resultCollectionDic)
{
using (SqlConnection connection = new SqlConnection("context connection=true"))
{
connection.Open();

using (SqlCommand selectMGT = new SqlCommand("SELECT NS,NP,HLR FROM dbo.zh_mgt ORDER BY NS,NP", connection))
{
using (SqlDataReader zhmgtReader = selectMGT.ExecuteReader())
{
while (zhmgtReader.Read())
{
string NS = zhmgtReader["NS"].ToString();
string NP = zhmgtReader["NP"].ToString();
string HLR = zhmgtReader["HLR"].ToString();
string key = NS + "+" + NP;
if (!resultCollectionDic.ContainsKey(key))
{
resultCollectionDic.Add(key, HLR);
}
}
}
}

connection.Close();
}
}

/// <summary>
/// 暴露給SQL Server調(diào)用的函數(shù).
/// </summary>
/// <param name="NS">參數(shù)1</param>
/// <param name="NP">參數(shù)2</param>
/// <returns></returns>
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlString FunctionImsi2HLR2(string NS, int NP)
{
string result = null;//這里設(shè)置為null是為了在方法IMSI2HLR2中判斷繼續(xù)循環(huán).
string key = NS + "+" + NP.ToString();//使用特殊符號+連接兩個(gè)列作為key值.
if (resultCollectionDic.ContainsKey(key))
result = resultCollectionDic[key].ToString();
return new SqlString(result);
}
};

調(diào)用方式對比

--1：這個(gè)是在NP和NS字段中分別建立索引
SELECT @rc=HLR FROM zh_mgt WHERE NP=7 and NS=@mgt

--2：這個(gè)是在NP、NS、HLR字段中建立了一個(gè)包含性索引(Include)
SELECT @rc=HLR FROM zh_mgt WHERE NS=@mgt and NP=7

--3：這是使用SQLCLR擴(kuò)展函數(shù)的調(diào)用方法
SELECT @rc= dbo.FunctionImsi2HLR2(@mgt,7)

優(yōu)點(diǎn)

性能上的比較（這里的>是表示時(shí)間的長短，時(shí)間越小，性能越優(yōu)）：每個(gè)列有單獨(dú)的索引>使用Include的包含索引>擴(kuò)展函數(shù)
把表里面的記錄放到內(nèi)存上，直接去內(nèi)存上查詢，不需要使用到B+樹來查詢數(shù)據(jù)。當(dāng)你的內(nèi)存足夠大或者空閑，并且使用到這個(gè)表的次數(shù)很多，而且更新不頻繁，那就可以考慮這樣的優(yōu)化方案。
如果需要面對一些比較復(fù)雜的邏輯處理，也許SQL是沒有辦法做到，即使做到了，那么SQL代碼的閱讀和維護(hù)會(huì)比較困難，其實(shí)這個(gè)既是優(yōu)點(diǎn)又是缺點(diǎn)，下面的缺點(diǎn)中有提到。
封裝代碼，加強(qiáng)代碼安全。

缺點(diǎn)

有一定的局限性，當(dāng)有多個(gè)AND條件一起查詢或者幾個(gè)鍵通過上面的方法加起來的字符串不唯一，那么就沒有辦法像上面IDictionary<string, string>的方法來使用key了，但是也不是沒有辦法的，其實(shí)辦法就是IList，把唯一的值作為key，再構(gòu)造一個(gè)實(shí)體作為key的value。
如果表更新了，需要重新注冊函數(shù)，因?yàn)槌绦蛞呀?jīng)把整個(gè)表加載到內(nèi)存了；如果不重新注冊函數(shù)，那么就需要數(shù)據(jù)庫重啟服務(wù)了，因?yàn)槟莻€(gè)程序集是在服務(wù)啟動(dòng)的時(shí)候就初始化了。
針對上面第二個(gè)缺點(diǎn)，也是有辦法解決的，那就是在表中做一個(gè)觸發(fā)器，當(dāng)有Insert、Update、Delete等操作就調(diào)用一個(gè)重新注冊的存儲(chǔ)過程就可以了。
如果里面的邏輯處理比較復(fù)雜，那么更新邏輯所帶來的部署、維護(hù)成本比較大，因?yàn)槿绻菍懗珊瘮?shù)或者是建立包含性索引可能會(huì)更好維護(hù)。

疑問

在SQL Server中，對一個(gè)包含性索引的疑問：比如有一個(gè)int類型的字段和一個(gè)nvarchar的字段，int字段的重復(fù)率比較大，而nvarchar的重復(fù)率比較少，我之前是根據(jù)重復(fù)率來確認(rèn)誰放前面的，但是int與nvarchar的匹配效率是不一樣的，int只要匹配一次，而nvarchar需要匹配跟字符串長度一樣多的次數(shù)，那么應(yīng)該如何把誰放到前面呢？
數(shù)據(jù)庫中可以把90%的查詢都?xì)w結(jié)為1：完全匹配，2：前綴匹配。對應(yīng)解決方案是：1：可采用bloom-filter擴(kuò)展函數(shù)進(jìn)行高速匹配，2：可采用改進(jìn)的哈夫曼樹。如何做這方面的方案呢？

總結(jié)

雖然這樣的方式比較難在現(xiàn)實(shí)的運(yùn)用中被使用，因?yàn)橛泻芏嗑窒扌院腿秉c(diǎn)，但是我寫這篇文章的初衷就是想讓大家知道在特殊的情況下，還有這樣一種優(yōu)化的方法可以使用。

作者：聽風(fēng)吹雨

出處：http://gaizai.cnblogs.com/

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機(jī)掃描二維碼進(jìn)行捐贈(zèng)
程序員人生