隨著“金盾工程”建設(shè)的逐步深入和公安信息化的高速發(fā)展，公安計(jì)算機(jī)應(yīng)用系統(tǒng)被廣泛應(yīng)用在各警種、各部門。與此同時(shí)，應(yīng)用系統(tǒng)體系的核心、系統(tǒng)數(shù)據(jù)的存放地――數(shù)據(jù)庫也隨著實(shí)際應(yīng)用而急劇膨脹，一些大規(guī)模的系統(tǒng)，如人口系統(tǒng)的數(shù)據(jù)甚至超過了1000萬條，可謂海量。那么，如何實(shí)現(xiàn)快速地從這些超大容量的數(shù)據(jù)庫中提取數(shù)據(jù)（查詢）、分析、統(tǒng)計(jì)以及提取數(shù)據(jù)后進(jìn)行數(shù)據(jù)分頁已成為各地系統(tǒng)管理員和數(shù)據(jù)庫管理員亟待解決的難題。
在以下的文章中，我將以“辦公自動(dòng)化”系統(tǒng)為例，探討如何在有著1000萬條數(shù)據(jù)的MS SQL SERVER數(shù)據(jù)庫中實(shí)現(xiàn)快速的數(shù)據(jù)提取和數(shù)據(jù)分頁。以下代碼說明了我們實(shí)例中數(shù)據(jù)庫的“紅頭文件”一表的部分?jǐn)?shù)據(jù)結(jié)構(gòu)：
CREATE TABLE [dbo].[TGongwen] (    --TGongwen是紅頭文件表名
   [Gid] [int] IDENTITY (1, 1) NOT NULL ,
--本表的id號(hào)，也是主鍵
   [title] [varchar] (80) COLLATE Chinese_PRC_CI_AS NULL ,  
--紅頭文件的標(biāo)題
   [fariqi] [datetime] NULL ,
--發(fā)布日期
   [neibuYonghu] [varchar] (70) COLLATE Chinese_PRC_CI_AS NULL ,
--發(fā)布用戶
   [reader] [varchar] (900) COLLATE Chinese_PRC_CI_AS NULL ,
--需要瀏覽的用戶。每個(gè)用戶中間用分隔符“,”分開
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
下面，我們來往數(shù)據(jù)庫中添加1000萬條數(shù)據(jù)：
declare @i int
set @i=1
while @i<=250000
begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-2-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊(duì),特勤支隊(duì),交巡警支隊(duì),經(jīng)偵支隊(duì),戶政科,治安支隊(duì),外事科','這是最先的25萬條記錄')
    set @i=@i+1
end
GO
declare @i int
set @i=1
while @i<=250000
begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-9-16','辦公室','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊(duì),特勤支隊(duì),交巡警支隊(duì),經(jīng)偵支隊(duì),戶政科,外事科','這是中間的25萬條記錄')
    set @i=@i+1
end
GO
declare @h int
set @h=1
while @h<=100
begin
declare @i int
set @i=2002
while @i<=2003
begin
declare @j int
        set @j=0
        while @j<50
            begin
declare @k int
            set @k=0
            while @k<50
            begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values(cast(@i as varchar(4))+'-8-15 3:'+cast(@j as varchar(2))+':'+cast(@j as varchar(2)),'通信科','辦公室,通信科,王局長,劉局長,張局長,admin,刑偵支隊(duì),特勤支隊(duì),交巡警支隊(duì),經(jīng)偵支隊(duì),戶政科,外事科','這是最后的50萬條記錄')
            set @k=@k+1
            end
set @j=@j+1
        end
set @i=@i+1
end
set @h=@h+1
end
GO
declare @i int
set @i=1
while @i<=9000000
begin
    insert into Tgongwen(fariqi,neibuyonghu,reader,title) values('2004-5-5','通信科','通信科,辦公室,王局長,劉局長,張局長,admin,刑偵支隊(duì),特勤支隊(duì),交巡警支隊(duì),經(jīng)偵支隊(duì),戶政科,治安支隊(duì),外事科','這是最后添加的900萬條記錄')
    set @i=@i+1000000
end
GO
通過以上語句，我們創(chuàng)建了25萬條由通信科于2004年2月5日發(fā)布的記錄，25萬條由辦公室于2004年9月6日發(fā)布的記錄，2002年和2003年各100個(gè)2500條相同日期、不同分秒的由通信科發(fā)布的記錄（共50萬條），還有由通信科于2004年5月5日發(fā)布的900萬條記錄，合計(jì)1000萬條。
一、因情制宜，建立“適當(dāng)”的索引
建立“適當(dāng)”的索引是實(shí)現(xiàn)查詢優(yōu)化的首要前提。
索引（index）是除表之外另一重要的、用戶定義的存儲(chǔ)在物理介質(zhì)上的數(shù)據(jù)結(jié)構(gòu)。當(dāng)根據(jù)索引碼的值搜索數(shù)據(jù)時(shí)，索引提供了對(duì)數(shù)據(jù)的快速訪問。事實(shí)上，沒有索引,數(shù)據(jù)庫也能根據(jù)SELECT語句成功地檢索到結(jié)果，但隨著表變得越來越大，使用“適當(dāng)”的索引的效果就越來越明顯。注意，在這句話中，我們用了“適當(dāng)”這個(gè)詞，這是因?yàn)?，如果使用索引時(shí)不認(rèn)真考慮其實(shí)現(xiàn)過程，索引既可以提高也會(huì)破壞數(shù)據(jù)庫的工作性能。
（一）深入淺出理解索引結(jié)構(gòu)
實(shí)際上，您可以把索引理解為一種特殊的目錄。微軟的SQL SERVER提供了兩種索引：聚集索引（clustered index，也稱聚類索引、簇集索引）和非聚集索引（nonclustered index，也稱非聚類索引、非簇集索引）。下面，我們舉例來說明一下聚集索引和非聚集索引的區(qū)別：
其實(shí)，我們的漢語字典的正文本身就是一個(gè)聚集索引。比如，我們要查“安”字，就會(huì)很自然地翻開字典的前幾頁，因?yàn)椤鞍病钡钠匆羰恰癮n”，而按照拼音排序漢字的字典是以英文字母“a”開頭并以“z”結(jié)尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”開頭的部分仍然找不到這個(gè)字，那么就說明您的字典中沒有這個(gè)字；同樣的，如果查“張”字，那您也會(huì)將您的字典翻到最后部分，因?yàn)椤皬垺钡钠匆羰恰皕hang”。也就是說，字典的正文部分本身就是一個(gè)目錄，您不需要再去查其他目錄來找到您需要找的內(nèi)容。
我們把這種正文內(nèi)容本身就是一種按照一定規(guī)則排列的目錄稱為“聚集索引”。
如果您認(rèn)識(shí)某個(gè)字，您可以快速地從自動(dòng)中查到這個(gè)字。但您也可能會(huì)遇到您不認(rèn)識(shí)的字，不知道它的發(fā)音，這時(shí)候，您就不能按照剛才的方法找到您要查的字，而需要去根據(jù)“偏旁部首”查到您要找的字，然后根據(jù)這個(gè)字后的頁碼直接翻到某頁來找到您要找的字。但您結(jié)合“部首目錄”和“檢字表”而查到的字的排序并不是真正的正文的排序方法，比如您查“張”字，我們可以看到在查部首之后的檢字表中“張”的頁碼是672頁，檢字表中“張”的上面是“馳”字，但頁碼卻是63頁，“張”的下面是“弩”字，頁面是390頁。很顯然，這些字并不是真正的分別位于“張”字的上下方，現(xiàn)在您看到的連續(xù)的“馳、張、弩”三字實(shí)際上就是他們在非聚集索引中的排序，是字典正文中的字在非聚集索引中的映射。我們可以通過這種方式來找到您所需要的字，但它需要兩個(gè)過程，先找到目錄中的結(jié)果，然后再翻到您所需要的頁碼。
我們把這種目錄純粹是目錄，正文純粹是正文的排序方式稱為“非聚集索引”。
通過以上例子，我們可以理解到什么是“聚集索引”和“非聚集索引”。
進(jìn)一步引申一下，我們可以很容易的理解：每個(gè)表只能有一個(gè)聚集索引，因?yàn)槟夸浿荒馨凑找环N方法進(jìn)行排序。
（二）何時(shí)使用聚集索引或非聚集索引
下面的表總結(jié)了何時(shí)使用聚集索引或非聚集索引（很重要）。
動(dòng)作描述
 使用聚集索引
 使用非聚集索引
列經(jīng)常被分組排序
 應(yīng)
 應(yīng)
返回某范圍內(nèi)的數(shù)據(jù)
 應(yīng)
 不應(yīng)
一個(gè)或極少不同值
 不應(yīng)
 不應(yīng)
小數(shù)目的不同值
 應(yīng)
 不應(yīng)
大數(shù)目的不同值
 不應(yīng)
 應(yīng)
頻繁更新的列
 不應(yīng)
 應(yīng)

最新評(píng)論