首先，根據情況，建立壹個“適當的”指數
建立壹個“合適的”索引是實現查詢優化的首要前提。
除了表，索引是存儲在物理介質上的另壹種重要的用戶定義的數據結構。根據索引代碼的值搜索數據時，索引提供了對數據的快速訪問。事實上，在沒有索引的情況下，數據庫可以根據SELECT語句成功檢索結果，但隨著表越來越大，使用壹個“合適的”索引的效果就越來越明顯。註意，在這句話中，我們使用了“適當”這個詞，因為如果在使用索引時不認真考慮實現過程，索引既可以提高也可以破壞數據庫的工作性能。
(壹)簡單理解指數結構
事實上，您可以將索引視為壹種特殊的目錄。微軟的SQL SERVER提供了兩種索引:聚集索引和非聚集索引。下面，我們來說明聚集索引和非聚集索引之間的區別:
其實我們的漢語詞典的正文本身就是壹個聚簇索引。比如我們要查“an”這個字，自然會翻開字典的前幾頁，因為“An”的拼音是“An”，按拼音排序漢字的字典是以英文字母“A”開頭，以“Z”結尾，所以“An”這個字自然排在字典的最前面。如果妳找遍了所有以“A”開頭的部分都找不到這個單詞，說明這個單詞不在妳的字典裏。同樣，如果妳查“張”這個字，妳也會翻到妳字典的最後壹部分，因為“張”的拼音是“張”。換句話說，字典的主體本身就是壹個目錄，妳不需要去查其他目錄來找到妳需要找的東西。
我們把這種文本內容本身按照壹定規則排列的目錄稱為“聚簇索引”。
如果妳知道壹個單詞，妳可以很快地從自動查詢。但是妳可能會遇到壹個妳不認識的單詞，妳不知道它的發音。這時候妳就找不到妳剛才要查的字了，妳需要根據偏旁查找妳要的字，然後直接翻到壹頁，根據字後的頁碼找到妳要的字。但是，妳結合部首目錄和字典找到的單詞排序，並不是真正的文本排序方法。比如妳查“張”這個字，我們看到部首查完之後的字典裏“張”這個字的頁碼是672，“池”這個字在字典裏“張”這個字的上面，但是頁碼是63，在它的下面。顯然，這些字並不是真的分別在“張”字的上面和下面。現在妳看到的連續詞“遲、張、弩”其實就是它們在非聚集索引中的排序，是字典體中的詞在非聚集索引中的映射。這樣我們就可以找到妳需要的單詞，但是需要兩個過程，先在目錄中找到結果，然後翻到妳需要的頁碼。
我們把這種目錄純為目錄，文本純為文本的排序方式稱為“非聚集索引”。
通過上面的例子，我們可以理解什麽是聚集索引和非聚集索引。
進壹步延伸，我們很容易理解，每個表只能有壹個聚集索引，因為目錄只能按壹種方式排序。
(B)何時使用聚集索引或非聚集索引
下表總結了何時使用聚集索引或非聚集索引(很重要)。
動作描述
使用聚集索引
使用非聚集索引
列通常按分組排序。
應該做
應該做
返回壹系列數據
應該做
但我總是不
壹個或幾個不同的值
但我總是不
但我總是不
十進制數的不同值
應該做
但我總是不
大量不同的價值觀
但我總是不
應該做
經常更新的列
但我總是不
應該做
外鍵列
應該做
應該做
主鍵列
應該做
應該做
頻繁修改索引列
但我總是不
應該做
實際上，我們可以通過前面的聚集索引和非聚集索引定義的例子來理解上表。例如，返回某個範圍內的壹項數據。例如，您的壹個表有壹個time列，您只需在該列中設置壹個聚集索引。這時，當妳查詢2004年6月65438+10月1到6月65438+10月1的所有數據時，這個速度會非常快，因為妳的字典的正文是按日期排序的，聚類索引只需要找到所有要檢索的數據。與非聚集索引不同，必須先找到目錄中每壹項數據對應的頁碼，然後根據頁碼找到具體內容。
(三)結合實際情況，談談指標使用的誤區。
理論的目的是應用。雖然我們剛剛列出了何時應該使用聚集索引或非聚集索引，但在實際操作中，上述規則很容易被忽略，或者無法根據實際情況進行全面分析。下面我們就根據實踐中遇到的實際問題來談談指數使用的誤區，讓大家掌握指數建立的方法。
1，主鍵是聚集索引。
作者認為這種想法是極其錯誤的，是對聚集索引的浪費。盡管默認情況下SQL SERVER在主鍵上建立了聚集索引。
通常我們會在每個表中創建壹個ID列來區分每壹條數據，並且這個ID列是自動遞增的，步長壹般為1。在我們的辦公自動化示例中，列Gid就是這種情況。此時，如果我們將此列設置為主鍵，SQL SERVER將默認此列為聚集索引。這樣做的好處是妳的數據在數據庫中可以按ID物理排序，但我覺得意義不大。
顯然，聚集索引的優勢是顯而易見的，每個表中只能有壹個聚集索引規則，這使得聚集索引更加珍貴。
從我們前面講的聚集索引的定義可以看出，使用聚集索引最大的好處就是可以根據查詢需求快速縮小查詢範圍，避免掃描整個表。在實際應用中，由於身份證號是自動生成的，我們並不知道每條記錄的身份證號，所以在實際操作中我們很難用身份證號進行查詢。這使得使用ID號的主鍵作為聚集索引是壹種資源浪費。其次，將ID號不同的字段作為聚集索引，不符合“存在大量不同值時不應建立聚集索引”的規則。當然，這種情況只會在用戶經常修改記錄的內容，尤其是索引項時產生負面作用，但不會影響查詢速度。
在辦公自動化系統中，無論是系統首頁顯示的需要用戶簽名的文檔，還是會議，還是用戶查詢的文檔，數據查詢都離不開字段“日期”和用戶自己的“用戶名”。
通常，辦公自動化的主頁會顯示每個用戶尚未簽收的文檔或會議。雖然我們的where語句只能限制當前用戶沒有簽收的情況，但是如果妳的系統建立時間很長，數據量很大，那麽每個用戶每次打開首頁都要掃描整個表是沒有意義的。大多數用戶在1個月前就已經瀏覽過文件了，這只能增加數據庫的成本。實際上，當用戶打開系統主頁時，數據庫只能查詢用戶近三個月未閱讀的文件，並通過“日期”字段限制表格掃描，提高查詢速度。如果妳的辦公自動化系統已經建立了2年，妳的主頁顯示速度理論上會快8倍甚至更多。
這裏之所以提到“理論上”這個詞，是因為如果妳的聚集索引還是盲目的建立在主鍵ID上，那麽妳的查詢速度就沒有那麽高，即使妳在字段“Date”上建立索引(非聚集索引)。我們來看看在654.38+00萬條數據(三個月25萬條數據)的情況下，各種查詢的速度表現:
(1)僅在主鍵上創建聚集索引，不劃分時間段:
從原文中選擇gid，fariqi，neibuyonghu，title
時間:128470毫秒(即128秒)
(2)在主鍵上建立聚集索引，在fariq上建立非聚集索引:
從原文中選擇gid，fariqi，neibuyonghu，title
其中fariqi & gtdateadd(day，-90，getdate())
時間:53，763毫秒(54秒)
(3)在日期列上建立聚集索引(fariqi):
從原文中選擇gid，fariqi，neibuyonghu，title
其中fariqi & gtdateadd(day，-90，getdate())
時間:2423毫秒(2秒)
雖然每個語句提取250，000條數據，但在各種情況下會有巨大的差異，尤其是當聚集索引基於日期列時。事實上，如果妳的數據庫真的有654.38+00萬的容量，在ID列上設置主鍵，就像上面654.38+0和2的情況壹樣，網頁上的性能是超時的，根本顯示不出來。這也是我放棄ID列作為聚集索引的最重要因素。
獲得上述速度的方法是在每個select語句前加上:declare @d datetime。
set @d=getdate()
在select語句後，添加:
select[語句執行時間(毫秒)]=datediff(毫秒，@d，getdate())
2.只要建立了索引，查詢速度就能顯著提高。
事實上，我們可以發現，在上面的例子中，第二條和第三條語句是完全壹樣的，建立索引的字段也是壹樣的；唯壹不同的是，前者在fariqi字段上建立非聚集索引，後者在該字段上建立聚集索引，但查詢速度差別很大。所以不是簡單的對任何字段建立索引來提高查詢速度。
從建表的語句中我們可以看到，這個有10萬數據的表的fariqi字段有5003條不同的記錄。在這壹領域建立壹個綜合指數是適當的。現實中我們每天發幾個文件，這些文件的日期都是壹樣的，完全符合“既不是絕大多數，也不是只有少數”的規律。從這個角度來看，我們建立壹個“合適的”聚集索引來提高查詢速度是非常重要的。
3.將所有需要提高查詢速度的字段添加到聚集索引中，以提高查詢速度。
如上所述，在進行數據查詢時，離不開字段的是“日期”和用戶自己的“用戶名”。由於這兩個字段非常重要，我們可以將它們結合起來構建壹個復合索引。
很多人認為只要在聚簇索引中添加任意壹個字段，就可以提高查詢速度，也有人疑惑:如果單獨查詢復合聚簇索引字段，查詢速度會變慢嗎？帶著這個問題，我們來看看下面的查詢速度(結果集全是25萬條數據):(日期列fariqi排在復合聚簇索引的最前面，用戶名neibuyonghu排在後面)。
(1)select gid，fariqi，neibuyonghu，title from Tgongwen where fari qi & gt；'2004-5-5'
查詢速度:2513毫秒
(2)從Tgongwen中選擇gid、fariqi、neibuyonghu、title，其中fariqi & gt“2004年5月5日”和內步永和=“辦公室”
查詢速度:2516毫秒
(3) Select GID，Fariqi，Neibu胡勇，Title From Tgongwen Where Neibu胡勇=“Office”
查詢速度:60280ms
從上面的實驗可以看出，如果只使用聚簇索引的初始列作為查詢條件，那麽查詢速度與復合聚簇索引所有列同時查詢的速度幾乎相同，甚至略快於所有復合索引列(在查詢結果集數量相同的情況下)；但是，如果只使用復合聚集索引的非起始列作為查詢條件，則該索引將不起任何作用。當然，語句1和2的查詢速度是壹樣的，因為查詢項數是壹樣的。如果使用復合索引的所有列，查詢結果很少，就會形成“索引覆蓋”，所以性能可以達到最優。同時，請記住，無論您是否經常使用聚合索引的其他列，前導列都必須是使用頻率最高的列。
(D)總結使用其他書籍中沒有的索引的經驗。
1.使用聚合索引比使用非聚合索引的主鍵更快。
下面是壹個示例語句:(全部提取25萬條數據)
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi = ' 2004-9-16 '
使用時間:3326毫秒
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where gid & lt=250000
使用時間:4470毫秒
這裏，使用聚集索引比使用非聚集索引的主鍵快將近1/4。
2.使用聚合索引比使用普通主鍵作為order by更快，尤其是在數據量較小的情況下。
根據法瑞奇的順序從Tgongwen中選擇gid、法瑞奇、內步永虎、讀者、標題
時間:12936
按照gid的順序從Tgongwen中選擇gid、fariqi、neibuyonghu、reader、title
時間:18843
這裏，使用聚集索引比使用壹般主鍵作為order by快3/10。事實上，如果數據量很小，使用聚集索引作為排序列要比使用非聚集索引快得多；但如果數據量很大，比如654.38+萬以上，兩者的速度差異就不明顯了。
3.使用聚集索引中的時間段，無論使用多少個聚集索引，搜索時間都將與整個數據表中數據的百分比成比例地減少。
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi & gt；'2004-1-1'
時間:6343毫秒(提取1百萬個片段)
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi & gt；'2004-6-6'
時間:3170毫秒(提取了50萬個)
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi = ' 2004-9-16 '
時間:3326毫秒(和上壹句的結果完全壹樣。如果集合的數量相同，則大於號和等號相同)
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi & gt；2004-1-1 '和fariqi & lt'2004-6-6'
時間:3280毫秒
4.日期列不會因為分和秒的輸入而降低查詢速度。
在下面的例子中，* * *有1萬條數據，2004年1之後有50萬條數據，但只有兩個不同的日期，日期精確到天；有50萬條數據和5000個不同的日期，日期精確到秒。
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi & gt；fariqi的2004-1-1 '訂單
時間:6390毫秒
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi & lt；fariqi的2004-1-1 '訂單
時間:6453毫秒
(5)其他註意事項
“水能載舟，亦能覆舟”，指數也是如此。索引有助於提高檢索性能，但過多或不適當的索引也會導致系統效率低下。因為每次用戶向表中添加索引時，數據庫都要做更多的工作。過多的索引甚至會導致索引碎片。
所以要建立壹個“合適”的索引系統，尤其是聚合索引的創建，力求完美，這樣妳的數據庫才能獲得高性能。
當然，在實踐中，作為壹個認真的數據庫管理員，您必須測試更多的方案，以找出哪壹個是最高效和有效的。
第二，改進SQL語句
很多人不知道SQL語句在SQL SERVER中是如何執行的，擔心自己的SQL語句被SQL SERVER誤解。例如:
select * from table1其中name = ' zhangsan ' and tID & gt10000
和實施:
select * from table1其中tID & gt10000，name= '張三'
有些人不知道上面兩條語句的執行效率是否相同，因為如果單純看語句的順序，這兩條語句確實是不壹樣的。如果tID是聚合索引，那麽最後壹句只能從表中10000之後的記錄中找到。前壹句應該先從全表中查找幾個name= '張三'，然後根據限制性條件tid >；10000來呈現查詢結果。
其實這樣的擔心是多余的。SQL SERVER中有壹個查詢分析優化器，可以計算where子句中的搜索條件，確定哪個索引可以減少表掃描的搜索空間，即可以實現自動優化。
雖然查詢優化器可以根據where子句自動優化查詢，但還是需要大家了解“查詢優化器”的工作原理。否則，有時候查詢優化器不會按照妳的本意進行快速查詢。
在查詢分析階段，查詢優化器查看查詢的每個階段，並決定限制需要掃描的數據量是否有用。如果壹個階段可以作為掃描參數(SARG)，則稱之為可優化的，使用該索引可以快速獲得所需的數據。
SARG的定義:用於限制搜索的操作，因為它通常指特定的匹配，在壹個值範圍內的匹配或兩個或多個條件的AND連接。其形式如下:
列名運算符；
或者
& lt常量或變量>；運算符列名
列名可以出現在運算符的壹側，而常量或變量出現在運算符的另壹側。比如:
Name= '張三'
價格> 5000
5000 & lt價格
Name= '張三'價格> 5000
如果壹個表達式不能滿足SARG的形式，那麽它就不能限制搜索的範圍，即SQL SERVER必須對每壹行判斷它是否滿足WHERE子句中的所有條件。因此，對於不符合SARG形式的表達式，索引是沒有用的。
在介紹完SARG之後，讓我們總結壹下使用SARG的經驗以及在實踐中從壹些材料中得出的不同結論:
1，Like語句是否屬於SARG取決於使用的通配符類型。
例如:同名的“張%”，屬於
還有:同名同姓的“%張”不屬於。
原因是字符串中的通配符%使得索引不可用。
2.否則將導致全表掃描。
Name= '張三'且價格> 5000符號SARG，且:Name= '張三'或價格>；5000不符合SARG。使用或將導致全表掃描。
3.由非運算符和函數引起的不符合SARG形式的語句。
不符合SARG形式的語句最典型的情況是包含非運算符的語句，例如:NOT，！= 、& lt& gt、!& lt、!& gt，不存在，不在，不喜歡等等。，除了功能。這裏有幾個不符合SARG形式的例子:
ABS(價格)< 5000
類似名稱的“% 3”
壹些表達，如:
其中價格* 2 & gt5000
SQL SERVER也會認為是SARG，SQL SERVER會把這個公式轉換成:
其中價格> 2500/2
但我們不建議這樣做，因為有時SQL SERVER不能保證這種轉換完全等價於原始表達式。
4、IN的作用相當於OR。
聲明:
select * from table 1 where tid in(2，3)
和
Select * from table1，其中tid=2或tid=3
是壹樣的，會引起全表掃描，如果tid上有索引，它的索引也會無效。
5.盡量不要用，越少越好
6.exists和in的執行效率是壹樣的。
大量數據表明，exists比in效率更高，應該盡可能使用not exists而不是not in。但其實我試了壹下，發現兩者之間的執行效率是壹樣的，不管前面有沒有not。因為它涉及子查詢，所以我們這次嘗試使用SQL SERVER附帶的pubs數據庫。在運行之前，我們可以打開SQL SERVER的統計I/O狀態。
(1)select title，price from title _ id where(select title _ id from sales where qty & gt；30)
這句話的執行結果是:
表格“銷售”。掃描計數是18，56個邏輯讀數，0個物理讀數和0個預讀數。
表格“標題”。掃描計數為1，邏輯讀兩次，物理讀零，預讀零。
(2)select title，price from titles where exists(select * from sales where sales . title _ id = titles . title _ id and qty & gt；30)
第二句話的執行結果是:
表格“銷售”。掃描計數是18，56個邏輯讀數，0個物理讀數和0個預讀數。
表格“標題”。掃描計數為1，邏輯讀兩次，物理讀零，預讀零。
從現在開始，我們可以看到使用exists的執行效率和使用in是壹樣的。
7.使用charindex()函數的執行效率與在它前面添加通配符%相同。
前面我們說過如果在LIKE前加通配符%會造成全表掃描，所以它的執行效率低。但據壹些資料顯示，用函數charindex()替換LIKE會大大提高速度。經過我的實驗，我發現這個解釋也是錯誤的:
Select GID，title，fariqi，reader from tgongwen where char index('刑偵支隊'，reader)& gt；0和fariqi & gt'2004-5-5'
時間:7秒，另外:掃描計數4，邏輯讀取7155次，物理讀取0次，預讀取0次。
Select GID，title，fariqi，reader from tgongwen其中reader like'%'+'刑偵支隊'+'% '和fariqi & gt'2004-5-5'
時間:7秒，另外:掃描計數4，邏輯讀取7155次，物理讀取0次，預讀取0次。
8.聯合並不絕對比或更有效。
我們已經提到，在where子句中使用or將導致整個表被掃描。總的來說，我看到的所有資料都推薦用union，而不是這裏的or。事實證明，這句話對他們大多數人都適用。
從Tgongwen中選擇gid、fariqi、neibuyonghu、reader、title，其中fariqi='2004-9-16 '或gid & gt9990000
時間:68秒。掃描計數1，邏輯讀取404008次，物理讀取283次，預讀取392163次。
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi = ' 2004-9-16 '
聯盟
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where gid & gt9990000
時間:9秒。掃描計數為8，邏輯讀取為67489次，物理讀取為216次，預讀取為7499次。
似乎在正常情況下，使用union比使用or要高效得多。
但是筆者經過實驗發現，如果or兩邊的查詢列相同，那麽使用union的執行速度要比使用or差很多，雖然union掃描的是這裏的索引，or掃描的是整個表。
從Tgongwen中選擇gid，fariqi，neibuyonghu，reader，title，其中fariqi='2004-9-16 '或fariqi='2004-2-5 '
時間:6423毫秒。掃描計數2，邏輯讀取14726次，物理讀取1次，預讀取7176次。
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi = ' 2004-9-16 '
聯盟
select gid，fariqi，neibuyonghu，reader，title from Tgongwen where fari qi = ' 2004-2-5 '
時間:11640ms。掃描計數為8，邏輯讀取14806次，物理讀取108次，預讀取1144次。
9.實地提取應遵循“需要多少，提取多少”的原則，避免“選擇*”
讓我們做壹個實驗:
按照gid desc的順序從tgongwen中選擇前10000 gid，fariqi，reader，title
時間:4673毫秒
按照gid desc的順序從tgongwen中選擇top 10000 gid，fariqi，title
時間:1376毫秒
按照gid desc的順序從tgongwen中選擇top 10000 gid，fariqi
時間:80毫秒
從這個角度來看，我們每少提取壹個字段，數據提取的速度就會相應提高。提升的速度取決於妳丟棄的字段的大小。
10和count(*)並不比count (field)慢。
有資料顯示，用*會統計所有列，明顯不如列出壹個世界效率高。這種說法其實是沒有根據的。讓我們看看:
從Tgongwen中選擇count(*)
時間:1500毫秒
從Tgongwen中選擇計數(gid)
時間:1483毫秒
從Tgongwen中選擇count(fariqi)
時間:3140毫秒
從Tgongwen中選擇計數(標題)
時間:52050毫秒
從上面可以看出，如果使用count(*)和count(主鍵)的速度相同，但是count(*)比除主鍵以外的任何字段都快，而且字段越長，匯總速度越慢。我覺得如果使用count(*)，SQL SERVER可能會自動找到最小的字段進行匯總。當然，如果直接寫count，會更直接。
11，order by是按聚集索引列進行最有效的排序。
讓我們看看:(gid是主鍵，fariqi是聚合索引列)
從tgongwen中選擇top 10000 gid，fariqi，reader，title
時間:196毫秒。掃描計數1，邏輯讀取289次，物理讀取1次，預讀取1527次。
從tgongwen中選擇前10000 gid，fariqi，reader，title按gid asc排序
時間:4720ms。掃描計數為1，邏輯讀取為41956次，物理讀取為0次，預讀取為1287次。
按照gid desc的順序從tgongwen中選擇前10000 gid，fariqi，reader，title
時間:4736毫秒。掃描計數1，邏輯讀取55350次，物理讀取10次，預讀取775次。
選擇前10000 gid，fariqi，reader，title from tgongwen order by fari qi ASC
時間:173毫秒。掃描計數1，邏輯讀取290次，物理讀取0次，預讀取0次。
從desc法裏奇的文章順序中選擇10000的gid，法裏奇，讀者，標題
時間:156毫秒。掃描計數1，邏輯讀取289次，物理讀取0次，預讀取0次。
從上面可以看出，非排序的速度和邏輯讀取次數與“按聚集索引列排序”的速度相當，但這些都比“按非聚集索引列排序”的查詢速度快得多。
同時，按某個字段排序時，無論是正序還是逆序，速度基本壹致。
12，高效頂
事實上，在查詢和提取超大數據集時，影響數據庫響應時間的最大因素不是數據搜索，而是物理I/O操作。比如:
選擇top 10 * from(
從tgongwen中選擇top 10000 gid，fariqi，title
其中內步永和=“辦公室”
由紀德·desc訂購)
按gid asc排序
理論上，這個語句的執行時間應該比子句的執行時間長，但事實正好相反。因為子句執行後返回10000條記錄，而整個語句只返回10條語句，所以影響數據庫響應時間最大的因素是物理I/O操作。這裏限制物理I/O操作的最有效方法之壹是使用TOP關鍵字。TOP關鍵字是SQL SERVER中由系統優化的壹個詞，用於提取前幾個或前幾個百分比數據。通過作者在實踐中的應用，發現TOP真的很好用，效率也很高。但是這個詞在另壹個大型數據庫ORACLE中沒有，不能說是壹個遺憾，雖然在ORACLE中可以通過其他方法(比如rownumber)解決。在以後關於“實現千萬級數據的分頁顯示和存儲過程”的討論中，我們會用到關鍵詞TOP。
到目前為止，我們已經討論了如何從大容量數據庫中快速找到您需要的數據。當然，我們介紹的這些方法都是“軟”方法。在實際操作中，我們不得不考慮各種“硬”因素，比如網絡性能、服務器性能、操作系統性能，甚至網卡和交換機。