6.3 SQL指令與索引的關係

  1. 避免在使用LIKE運算子比對萬用字元(%)開頭的字串
  2. 使用LIKE與萬用字元(%)時,需注意萬用字元的位置,例如:'林%'可以使用索引來執行查詢;但若是萬用字元在常數的開頭,則不會使用索引來執行查詢,只能使用資料表掃描來執行查詢。
    --使用索引
    select * from Emp where address like '中壢%'
    --不使用索引
    select * from Emp where address like '%中壢'
  3. 避免對索引值的欄位進行運算或使用函數
  4. 在where條件中將欄位資料運算後才做比對搜尋,雖然此欄位已經建立索引,但是卻無法使用有效率的資料搜尋,而是對資料欄使用循序計算再搜尋,例如用以下之SQL指令查詢年薪超過50萬的員工姓名:
    select ename
    from Emp
    where salary*12 >= 500000
    應將SQL查詢指令改寫為:
    select ename from Emp where salary >= 500000/12
  5. 在Where的OR中搜尋的欄位皆需建索引,例如下列之SQL指令,員工編號 eid 和 身分證號欄位ssid 都必須建立索引。
  6. select *
    from Emp
    where eid = 'e001' or ssid = 'A323456789'
  7. 在Where的AND中搜尋的欄位不必皆建立索引,例如下列之SQL指令,員工編號 eid 或身份證號ssid只需其中一個欄位建立索引,且索引欄位放前面。
  8. select *
    from Emp
    where eid = 'e001' and ssid = 'A323456789'
    在Where的AND中採用欄位的順序應如下:
    (1) 具唯一性的資料(Unique):學號、訂單編號, …
    (2) 可能重複但機率不高:身分證號、統一編號、姓名、生日、電話, …
    (3) 一定重複但可把範圍縮小:郵遞區號(地區)、年齡、成績、學歷,…
    (4) Boolean(True/False)或只有少數個值:性別、婚姻 、有無貸款…
  9. 複合索引的各欄位無法分開使用,仍須視需求對個別欄位建立索引。
  10. 例如員工編號欄位eid與姓名欄位ename已經建立為複合索引,但是某些查詢中where的條件只需搜尋姓名欄位ename,則姓名欄位ename仍需獨立建成索引。
  11. 除了查詢是否常用到,資料表大小等因素外,還要考量欄位本身的性質。
  12. (1) 具唯一性的資料一定要建Unique索引,例如學號、訂單編號。
    (2) 會重複但重複機率不高,仍應該要建索引,例如:
       身分證號、統一編號、姓名、生日、電話。
    (3) 一定會重複但能把範圍縮小
  13. 依照查詢的需要決定是否要建索引,能篩選到越小塊時,越應建索引,也越有效益。
  14. (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