SQL SERVER 索引中聚集索引分析和Transact-SQL語句優(yōu)化

第 2 頁 優(yōu)化 Transact-SQL 語句經常使用的語句
二 .優(yōu)化 Transact-SQL 語句經常使用的語句
  
 1.SET STATISTICS IO {ON| OFF} /*Transact-SQL 語句生成的磁盤活動量的信息*/
  
 2.SET SHOWPLAN_ALL ON {ON| OFF} /*返回有關語句執(zhí)行情況的詳細信息，并估計語句對資源的需求*/
 
 3.SET STATISTICS TIME {ON| OFF} /*顯示分析、編譯和執(zhí)行各語句所需的毫秒數*/
 
  4.使用T-SQL語句創(chuàng)建索引的語法：
    CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] 
    INDEX   index_name
    ON table_name (column_name)
    [WITH FILLFACTOR=x]
    UNIQUE表示唯一索引，可選CLUSTERED、NONCLUSTERED表示聚集索引還是非聚集索引， 可選 FILLFACTOR表示填充因子，指定一個0到100之間的值，該值指示索引頁填滿的空間所占的百分比
 
 
 SET STATISTICS IO 輸出信息如圖
 
三 創(chuàng)建數據測試下上面學到的理論知識
 
--創(chuàng)建表
CREATE TABLE employee
(
 emp_username varchar (20),
 emp_register DATETIME
)
  
--插入測試數據
DECLARE @startid INT
DECLARE @endid INT
SELECT @startid= 1,@endid = 100
WHILE @startid <=@endid
BEGIN
 INSERT INTO employee (
    emp_username,
    emp_register
 ) VALUES (
    /* emp_username - varchar (20) */ '劉'+CAST(@startid AS NVARCHAR(20)),
    /* emp_register - DATETIME */ GETDATE() )
 SELECT @startid =@startid +1;
END
 
-- 查詢employee的執(zhí)行計劃 和 io  信息
SET STATISTICS IO ON 
SELECT * FROM employee WHERE emp_username = '劉'

 
查看消息輸出的 IO 信息
表'employee'。(1)1掃描計數1，(2)邏輯讀取1 次，(3)物理讀取0 次，(4)預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
輸出的信息和上面的圖片講解的是對應的
1.      執(zhí)行的掃描次數 。
2.      從磁盤讀取的頁數。
3.      為進行查詢而放入緩存的頁數。
4. 預讀
T_SQL transaction 語句有很多種的寫法，但是決定那條語句是最優(yōu)的是根據(logical reads) 邏輯讀取來判斷。
 
 
添加聚集索引 查詢邏輯讀取是否會變少
CREATE CLUSTERED INDEX Idx_emp_username ON employee (emp_username);
 
--然后再執(zhí)行查詢 
SET STATISTICS IO ON
SELECT * FROM employee WHERE emp_username = '劉'

 
查看消息輸出的 IO 信息
表'employee'。掃描計數1，邏輯讀取2 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。 
Q 這次邏輯讀取是2次為什么呢 ？
A.難道查詢比表掃描還要慢，答案是對的，數據量小的時候，聚集索引的優(yōu)勢體現不出來。
Q 為什么是2次邏輯讀取
A 現在查詢的時候如聚集索引圖，先查詢索引頁 ，查找到對應的鍵值后，掃描數據頁，如果有包含索引，直接在索引頁就可以提取到需要的數據。
 
 
上面說了小數據量的時候聚集索引體現不出效果，下面我們繼續(xù)填充數據測試 。
 
 
填充測試數據到1000
 
表掃描
消息：
表'employee'。掃描計數1，邏輯讀取36 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
 
聚集索引掃描
消息：
表'employee'。掃描計數1，邏輯讀取2 次，物理讀取0 次，預讀0 次，lob 邏輯讀取0 次，lob 物理讀取0 次，lob 預讀0 次。
 
這個時候聚集索引的優(yōu)勢就先顯示出來了 O(∩_∩)O
 
 
 
下面在來講講transaction sql 語句 ，大家在網上看到的一些人說  In like left  不使用索引 ，我們動手來測試下看他們說的對不對 ？