亂馬客

前言

前陣子讀了「SQL Query Optimization FAQ Part 1 (The SQL Plan)」，在此整理一下，也分享給大家!

每個SQL Query都可細分成數個執行步驟，這些執行步驟叫作"operator"。

每個"operator"執行基本的操作，像是insertion, search, scan, updation, aggregation等等。

"operator"又可分成"logical"及"physical"兩種。相關資訊請參考:Logical and Physical Operators Reference。

SQL Query執行可分為以下3個步驟:

1.Parse:先檢查語法，再建立processor tree(定義logical steps)。

2.Optimize:使用"Optimizer"取得資料的統計資訊(如多少筆資料，有多少唯一的資料，需要多少resources, CPU & I/O等等)。"Optimizer"會依這些資訊建立很多的plan，然後選擇最好的plan。

3.Execute:最後就是依"Optimizer"送過來的plan去執行。

什麼是Table Scan?

在了解Table Scan之前我們先看一下如何顯示"執行計畫"!

知道了如何顯示"執行計畫"後，我們建立一個簡單的CUSTOMER TABLE(P_NO, USR_ID, CNAME)，不建立Primary Key及任何的Index。然後執行以下的SQL

SELECT * FROM CUSTOMER WHERE P_NO = '***00042'

它就會使用Table Scan去找到該筆資料。因為沒有建任何的Index，所以它會一筆筆去找，直到找到為此。

能加上Index增進效能嗎?

可以的! 我們為CUSTOMER加上"UNIQUE KEY INDEX" P_NO。

再RUN一次之前的SQL一次，可發現統計出來的Logical reads減少了!

 

What are physical and logical reads?

資料庫的主要工作是儲存及讀取資料，所以會讀取及寫入Disk。而這些從Disk讀取及寫入需要花較長的時間，所以SQL Server會配置一些Memory來做Cache，以加快讀寫的速度。

當資料從SQL Cache取出來叫logical read，從Disk讀取出來叫physical read。

Table scan doesn’t look to be efficient?

如果資料少的話，那使用table scan倒還好，但是如果資料很多的話，那table scan就會非常沒有效率。要使用seek比較好!

What is a seek scan?

Seek scan並不是一筆筆去找資料，而是使用B-Tree Logic去取得資料，以下是個簡單的圖說明index從1~50的範圍中是如何找到資料的! 

如果你想要找39的話，因為數值是39，大於30，所以會移到50 這個root node。再到下一層，比較是大於還是小於40，因為是小於40，所以就移到40的那個Non-Leaf nodes，然後往下找下去，所以一定比一筆筆找還快!

建立Clustered index

如果我們把P_NO的index改成Clustered index的話，執行計畫跟使用Non-Clustered index少了RID Lookup。如果能用到Clustered index的話，是效能會比較好哦!

 

結論

每個Table幾乎都會加上PK，但是在要隨時注意自已的Query是否有沒有用到適合的index，而且在where 儘量別使用function去join。我們在查效能問題幾乎會先看有沒有用到index，還是寫法有問題!

以下的SQL是在查相關的資料，覺得有幫助，也一起列出來。

--在sysindexes中indid=0表示該table沒有建任何index
--以下是找出沒有建INDEX的Table
SELECT USER_NAME(OBJECTPROPERTY(A.id, 'OwnerID' )) as OwnerName
, B.name 
, A.name as IndexName
, A.rowcnt 
FROM sysindexes A, sysobjects B
WHERE A.indid = 0 AND A.id = B.id
AND B.type  = N'U'
ORDER BY A.rowcnt desc

 

--以下是列出Table中的index的list，包含最後異動日期
SELECT USER_NAME( OBJECTPROPERTY( i.id, 'OwnerID' ) ) AS OwnerName,
     OBJECT_NAME( i.id ) AS TableName,
     i.name AS IndexName,
      CASE INDEXPROPERTY( i.id , i.name , 'IsClustered')
             WHEN 1 THEN 'YES'
             ELSE 'NO'
      END AS IsClustered,
      CASE INDEXPROPERTY( i.id , i.name , 'IsUnique'    )
            WHEN 1 THEN 'YES'
            ELSE 'NO'
      END AS IsUnique,
      STATS_DATE( i.id , i.indid ) AS LastUpdatedDate
      ,rowcnt 
  FROM sysindexes i 
WHERE  OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0 And
      1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics'   ) ,
          INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
          INDEXPROPERTY( i.id , i.name , 'IsHypothetical'   ) ) And
      i.indid BETWEEN 1 And 250
ORDER BY OwnerName, TableName, IndexName

參考資料

An Introduction to Clustered and Non-Clustered Index Data Structures

SQL Query Optimization FAQ Part 1 (The SQL Plan)

Querying the Sysindexes System Table

 測試資料:測試資料.rar

Hi, 

亂馬客Blog已移到了 「亂馬客​ : Re:從零開始的軟體開發生活」

請大家繼續支持 ^_^