Jiang Ying-Fu的部落格

以下是各SQL語法中WHERE的語法查詢結果比較，單位是豪秒。

資料庫為MSSQL。

測試筆數為100萬筆。

各執行30次。

當ENAME為Index時如下：

(1) ENAME='0000299987     '

       1         0         0         0         0
       1         0         0         0         0
       0         0         0         0         0
       0         0         0         0         0
       0         0         0         0         0
       0         0         0         0         0

(2)ENAME LIKE '0000299987%'

       0         0         0         0         0
       0         0         0         0         0
       0         1         0         1         0
       0         0         0         0         0
       0         1         0         0         0
       0         0         0         0         0

(3) SUBSTRING(ENAME,5,6)='299987'

   281     296     273     285     271
   269     280     271     279     286
   284     304     284     301     278
   299     294     323     280     282
   264     277     273     272     283
   269     275     280     280     287

(4) CAST(ENAME AS INT)=299987

   409     393     395     392     388
   390     397     393     394     391
   388     393     391     394     394
   391     391     394     396     393
   418     400     394     392     394
   415     391     390     391     394

(5) ENAME LIKE '____299987%'

   948     941     942     945      951
   943     938     950     990   1,004
   945     937     947     952      946
   941     938     951     959      968
   948     992     944     945      944
   946     941     945     972      947

(6) ENAME LIKE '%299987%'

 2,541   2,543   2,562   2,524   2,516
 2,513   2,524   2,564   2,493   2,516
 2,523   2,557   2,496   2,557   2,525
 2,520   2,498   2,522   2,582   2,520
 2,505   2,517   2,496   2,597   2,510
 2,499   2,497   2,510   2,574   2,523

各Index查詢結果的最大值、最小值，以及平均值如下：

(1) Max:         1  Min:        0    Avg:       0.1
(2) Max:         1  Min:        0    Avg:       0.07  
(3) Max:     323  Min:     264    Avg:   282.7
(4) Max:     418  Min:     388    Avg:   394.9
(5) Max:   1004  Min:     937    Avg:   952.0
(6) Max:   2597  Min:   2493    Avg:  2527.5

當ENAME不為index時如下：

(1) ENAME='0000299987     '


   262     267     261     261     279
   293     306     262     263     263
   253     257     257     258     256
   258     259     266     256     260
   256     258     259     262     264
   257     256     261     255     256

(2) SUBSTRING(ENAME,5,6)='299987'

   283     268     267     266     268
   267     268     267     272     266
   267     267     270     266     268
   268     270     269     266     270
   267     268     268     275     269
   267     269     272     267     268

(3) CAST(ENAME AS INT)=299987

   277     245     240     243     241
   244     238     238     239     243
   238     238     239     246     241
   240     241     243     242     241
   240     242     239     239     239
   242     238     240     239     244

(4) ENAME LIKE '____299987%'


    990     963     974     986      971
    972     970     968     977      970
    969     975     970     974      969
    969     968     971     974   1,083
 1,018     984     984     985      978
    970     973     964     974      983

(5) ENAME LIKE '%299987%'


 2,570   2,518   2,518   2,579   2,529
 2,566   2,524   2,536   2,530   2,535
 2,520   2,534   2,544   2,554   2,539
 2,525   2,527   2,531   2,546   2,548
 2,547   2,549   2,554   2,567   2,534
 2,534   2,532   2,532   2,548   2,539

查詢非Index

查詢為Index

查詢為Index，各執行500次：

依據以上結果，大致定出幾個規則。

1.查詢條件為index時，其比對規則在文字char型態時，是使用由左至右。

2.若將%放至比對字串的最前方則會因為比對範圍擴大而導致index失效。

3.即使是使用_字元比對，雖然供比較慢，但因其定義範圍比%小，故其index還是存在。

4.使用substring會比較快的原因為，雖然有破壞結構，但其在實質上，並無完整的破壞，所以index還是存在，若將截取的字串再減少，或許有加快的可能，這個日後再另行測試。

5.使用轉型態後轉換，為半破壞結構，且在轉換時，需多花一道手續，故多花一些時間，但依規則而言，在轉換手續上，應該還有其限制和手續，故這個可能會因轉換的型態位元長度而有時間的長短，且因多一道Index轉換，所以在Index的狀態下，所需的時間為非Index時間的1.5倍上下。

其實Index設定是資料庫管理員和程式設計師需要共同合作的項目，若該欄位為Index，但所有查詢條件都是使用%，那可能只是一種空間和時間的浪費，浪費磁碟的空間，浪費SQL存取的時間。

雖然說現在硬碟很便宜，但在不適合的Index下，硬碟成長的太快，最後依舊會有不夠用的情況發生。

個人資料僅供參考，請勿轉貼。