T1表?10000000萬條數據,(插入時間36分鐘，count(*)查詢19秒，空間占用670M左右)sql

1.真正充分的利用索引

好比like?'張%'?就是符合SARG(符合掃描參數)標準

而like?'%張'?就不符合該標準數據庫

通配符%在字符串首字符的使用會致使索引沒法使用，雖然實際應用中很難避免這樣用，但仍是應該對這種現象有所了解，至少知道此種用法性能是很低下的。函數

**********************************************sqlserver

2.“非”操做符不知足SARG形式，使得索引沒法使用

不知足SARG形式的語句最典型的狀況就是包括非操做符的語句，如：NOT、!=、<>、!、NOT?EXISTS、NOT?IN、NOT?LIKE等。

若是使用not?或者?<>,最好轉換成別的方法，好比例子以下：性能

T1表?10000000萬條數據,構建以下:(插入時間36分鐘，count(*)查詢19秒，空間占用670M左右)測試

DECLARE?@i?INT

SET?@i?=?1

WHILE?@i<1000000

BEGIN

INSERT?INTO?t1?VALUES?('zhang'+CONVERT(char(50),?@i),'3.2',77);

SET?@i?+?1;

END優化

三種查詢方式：編碼

SELECT?*?FROM?t1?WHERE?id?<>300000

SELECT?*?FROM?t1?WHERE?id?NOT?IN?(300000)

SELECT?*?FROM?t1?WHERE?id?>299999?AND?id?

在執行計劃中能夠明顯看出，使用最后一種方式而不是前面兩種方式進行查詢。

網上是這么說的，但本身作的試驗100W條數據，開銷計劃是同樣的。server

*********************************************

3.?函數運算不知足SARG形式，使得索引沒法使用

例：下列SQL條件語句中的列都建有恰當的索引，但執行速度卻很是慢：

select?*?from?record?where?substring(card_no,1,4)=′5378′(13秒)

select?*?from?record?where?amount/30

select?*?from?record?where?convert(char(10),date,112)=′19991201′(10秒)

分析：

where子句中對列的任何操做結果都是在SQL運行時逐列計算獲得的，所以它不得不進行全表掃描，而沒有使用該列上面的索引；若是這些結果在查詢編譯時就能獲得，那么就能夠被SQL優化器優化，使用索引，避免表搜索，所以將SQL重寫成下面這樣：

select?*?from?record?where?card_no?like?′5378%′(

select?*?from?record?where?amount?

select?*?from?record?where?date=?′1999/12/01′?(

你會發現SQL明顯快不少

待測試.......

**********************************************

4.盡可能不要對創建了索引的字段，做任何的直接處理

select?*?from?employs?where?first_name?+?last_name?='beill?cliton';

沒法使用索引，改成：

select?*?from?employee?where

first_name?=?substr('beill?cliton',1,instr('beill?cliton','?')-1)

and

last_name?=?substr('beill?cliton',instr('beill?cliton','?')+1)

則可使用索引

***********************************************

5.不一樣類型的索引效能是不同的，應盡量先使用效能高的

好比：數字類型的索引查找效率高于字符串類型，定長字符串char，nchar的索引效率高于變長字符串varchar,nvarchar的索引。

應該將

where?username='張三'?and?age>20

改進為

where?age>20?and?username='張三'

注意：此處，SQL的查詢分析優化功能能夠作到自動重排條件順序，但仍是建議預先手工排列好。

**************************************************

6.某些狀況下IN?的做用與OR?至關?，且都不能充分利用索引

例：表stuff有200000行，id_no上有非群集索引，請看下面這個SQL：

select?count(*)?from?stuff?where?id_no?in(′0′,′1′)?(23秒)

我 們指望它會根據每一個or子句分別查找，再將結果相加，這樣能夠利用id_no上的索引；但實際上,它卻采用了"OR策略"，即先取出知足每一個or子句的 行，存入臨時數據庫的工做表中，再創建惟一索引以去掉重復行，最后從這個臨時表中計算結果。所以，實際過程沒有利用id_no?上索引，而且完成時間還要 受tempdb數據庫性能的影響。

實踐證實，表的行數越多，工做表的性能就越差，當stuff有620000行時，執行時間會很是長！若是肯定不一樣的條件不會產生大量重復值，還不如將or子句分開：

select?count(*)?from?stuff?where?id_no=′0′

select?count(*)?from?stuff?where?id_no=′1′

獲得兩個結果，再用union做一次加法合算。由于每句都使用了索引，執行時間會比較短，

select?count(*)?from?stuff?where?id_no=′0′

union

select?count(*)?from?stuff?where?id_no=′1′

從實踐效果來看，使用union在一般狀況下比用or的效率要高的多，而exist關鍵字和in關鍵字在用法上相似，性能上也相似，都會產生全表掃描，效率比較低下，根據未經驗證的說法，exist可能比in要快些。

***************************************************

7.使用變通的方法提升查詢效率

like關鍵字支持通配符匹配，但這種匹配特別耗時。例 如：select?*?from?customer?where?zipcode?like?“21_?_?_”，即便在zipcode字段上已創建了索 引，在這種狀況下也可能仍是采用全表掃描方式。若是把語句改 為：select?*?from?customer?where?zipcode?>“21000”，在執行查詢時就會利用索引，大大提升速度。但 這種變通是有限制的，不該引發業務意義上的損失，對于郵政編碼而 言，zipcode?like?“21_?_?_”?和?zipcode?>“21000”?意義是徹底一致的。

*********************************************************人各有志,但富貴在天，人生容許彷徨,但不容許蹉跎.

8.order?by按匯集索引列排序效率最高

排序是較耗時的操做，應盡可能簡化或避免對大型表進行排序，如縮小排序的列的范圍，只在有索引的列上排序等等。

咱們來看：(gid是主鍵，fariqi是聚合索引列)

select?top?10000?gid,fariqi,reader,title?from?tgongwen

用時：196?毫秒。?掃描計數?1，邏輯讀?289?次，物理讀?1?次，預讀?1527?次。

select?top?10000?gid,fariqi,reader,title?from?tgongwen?order?by?gid?asc

用時：4720毫秒。?掃描計數?1，邏輯讀?41956?次，物理讀?0?次，預讀?1287?次。

select?top?10000?gid,fariqi,reader,title?from?tgongwen?order?by?gid?desc

用時：4736毫秒。?掃描計數?1，邏輯讀?55350?次，物理讀?10?次，預讀?775?次。

select?top?10000?gid,fariqi,reader,title?from?tgongwen?order?by?fariqi?asc

用時：173毫秒。?掃描計數?1，邏輯讀?290?次，物理讀?0?次，預讀?0?次。

select?top?10000?gid,fariqi,reader,title?from?tgongwen?order?by?fariqi?desc

用時：156毫秒。?掃描計數?1，邏輯讀?289?次，物理讀?0?次，預讀?0?次。

同時，按照某個字段進行排序的時候，不管是正序仍是倒序，速度是基本至關的。

********************************************************

9.關于節省數據查詢系統開銷方面的措施

(1)使用TOP盡可能減小取出的數據量

(2)字段提取要按照“需多少、提多少”的原則，避免“select?*”

字段大小越大，數目越多，select所耗費的資源就越多，好比取int類型的字段就會比取char的快不少。咱們每少提取一個字段，數據的提取速度就會有相應的提高。提高的幅度根據舍棄的字段的大小來判斷

(3)count(*)?與?count(字段)?方法比較

用 count(*)和用?count(主鍵)的速度是至關的，而count(*)卻比其余任何除主鍵之外的字段匯總速度要快，并且字段越長，匯總速度就越 慢。若是用?count(*)，?SQL?SERVER會自動查找最小字段來匯總。固然，若是您直接寫count(主鍵)將會來的更直接些

(4)有嵌套查詢時，盡量在內層過濾掉數據

若是一個列同時在主查詢和where子句中出現，極可能當主查詢中的列值改變以后，子查詢必須從新查詢一次。并且查詢嵌套層次越多，效率越低，所以應當盡可能避免子查詢。若是子查詢不可避免，那么要在子查詢中過濾掉盡量多的行

(5)多表關聯查詢時，需注意表順序，并盡量早的過濾掉數據

在使用Join進行多表關聯查詢時候，應該使用系統開銷最小的方案。鏈接條件要充份考慮帶有索引的表、行數多的表，并注意優化表順序；說的簡單一點，就是盡量早的將以后要作關聯的數據量降下來。

通常狀況下，sqlserver?會對表的鏈接做出自動優化。例如：

select?name,no?from?A

join?B?on?A.?id=B.id

join?C?on?C.id=A.id

where?name='wang'

盡 管A表在From中先列出，而后才是B,最后才是C。但sql?server可能會首先使用c表。它的選擇原則是相對于該查詢限制為單行或少數幾行，就可 以減小在其余表中查找的總數據量。絕大多數狀況下，sql?server?會做出最優的選擇，但若是你發覺某個復雜的聯結查詢速度比預計的要慢，就可使 用SET?FORCEPLAN語句強制sql?server按照表出現順序使用表。如上例加 上：SET?FORCEPLAN?ON…….SET?FORCEPLAN?OFF?表的執行順序將會按照你所寫的順序執行。在查詢分析器中查看2種執行效 率，從而選擇表的鏈接順序。SET?FORCEPLAN的缺點是只能在存儲過程當中使用

原文：http://hi.baidu.com/mayw1985/item/2092f0427fcdf5e6dc0f6cab

總結

以上是生活随笔為你收集整理的08r2系统服务器开索引,SQLSERVER2008R2正确使用索引的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。