6-3:SQL指令與索引的關係

1. 避免在使用LIKE運算子比對萬用字元(%)開頭的字串

使用LIKE與萬用字元(%)時,需注意萬用字元的位置,例如:'林%'可以使用索引來執行查詢;但若是萬用字元在常數的開頭,則不會使用索引來執行查詢,只能使用資料表掃描來執行查詢。

使用索引

select * from Emp where address like '中壢%';

不使用索引

select * from Emp where address like '%中壢';

2. 避免對索引值的欄位進行運算或使用函數

在where條件中將欄位資料運算後才做比對搜尋,雖然此欄位已經建立索引,但是卻無法使用有效率的資料搜尋,而是對資料欄使用循序計算再搜尋,例如用以下之SQL指令查詢年薪超過50萬的員工姓名:

select ename from Emp where salary*12 >= 500000;

應將SQL查詢指令改寫為:

select ename from Emp where salary >= 500000/12;

3. 在Where的OR中搜尋的欄位皆需建索引,例如下列之SQL指令,員工編號 eid 和 身分證號欄位ssid 都必須建立索引。

select * from Emp where eid = 'e001' or ssid = 'A323456789';

4. 在Where的AND中搜尋的欄位不必皆建立索引,例如下列之SQL指令,員工編號 eid 或身份證號ssid只需其中一個欄位建立索引,且索引欄位放前面。

select * from Emp where eid = 'e001' and ssid = 'A323456789';

在Where的AND中採用欄位的順序應如下:

  1. 具唯一性的資料(Unique):學號、訂單編號, …
  2. 可能重複但機率不高:身分證號、統一編號、姓名、生日、電話, …
  3. 一定重複但可把範圍縮小:郵遞區號(地區)、年齡、成績、學歷,…
  4. Boolean(True/False)或只有少數個值:性別、婚姻 、有無貸款…

5. 複合索引的各欄位無法分開使用,仍須視需求對個別欄位建立索引。

例如員工編號欄位eid與姓名欄位ename已經建立為複合索引,但是某些查詢中where的條件只需搜尋姓名欄位ename,則姓名欄位ename仍需獨立建成索引。

6. 除了查詢是否常用到,資料表大小等因素外,還要考量欄位本身的性質。

  1. 具唯一性的資料一定要建Unique索引,例如學號、訂單編號。
  2. 會重複但重複機率不高,仍應該要建索引,例如:
    身分證號、統一編號、姓名、生日、電話。
  3. 一定會重複但能把範圍縮小。

7. 依照查詢的需要決定是否要建索引,能篩選到越小塊時,越應建索引,也越有效益。

  1. 郵遞區號(地區):全國有319鄉鎮,可篩選成1/319
  2. 年齡:0~150,大約可篩選成1/70
  3. 縣市:全國有26縣市,可篩選成1/26
  4. 成績等第:若為A~E,約可篩選成1/5
  5. 學歷:小學~博士,約可篩選成1/5

Boolean(True/False)或資料重複性高,不建議建索引,費了功夫作排序,在查詢時只能把資料過濾一半不划算,通常會和其他索引欄位併用,只作為輔助篩選之用,例如:

  1. 性別: 男 / 女 / (其他)
  2. 婚姻狀況: 已婚 / 未婚 / (離婚/再婚)
  3. 有無貸款: 有 / 無

一般不建索引之欄位,例如:

  1. 大資料區塊(Memo/Text/Image/LOB)
  2. Memo/Text:產品描述/文章
  3. Image:產品照片/學生照片/聲紋
  4. LOB: Oracle 的 CLOB, BLOB