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

2022 年 5 月 15 日
單元６索引 1,364 次瀏覽

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