你在 Table 建立的 Index 真的有效嗎？用範例帶你理解 Coverage Index 的意義

我們會在 Table 中建立 Index 以增加搜尋的效率，但建立 Index 的同時也會影響到資料 insert、update、delete 的效率；所以我們在建立 Index 時需要抱持謹慎的態度，這篇文章將帶大家了解 Coverage Index (覆蓋索引)的意義。

SETP 1：建立測試用的 tmp_user Table

1. id 為 primary key
2. name 為 index

SETP 2：下 SQL 指令了解是否使用 Index

觀察最後「Extra」欄位的資訊

➤ SQL A：

select id from user_table where name= '寶寶不說'

➤ SQL B：

select password from user_table where name= '寶寶不說'

SETP 3：分析 SQL 指令邏輯

➤ SQL A 分析：

select id from user_table where name= '寶寶不說'

因為 name 有建立 index，所以在查詢到 name後可以直接與 id 對應返回結果；因為 name 已經覆蓋了查詢資料的需求，所以稱為 「Coverage Index」

➤ SQL B 分析：

select password from user_table where name= '寶寶不說'

透過 name找到「寶寶不說」對應的 id，接著透過 id這個 primary key 在 Table 重新輪詢才能取得這一列的完整資料。

SETP 4：建立 Coverage Index 優化 SQL B

➤ 先將資料庫 drop 掉

drop table tmp_user;

➤ 重新建立測試資料以及「Coverage Index」

為了避免用 primary key 再次從 Table 查詢的耗能，我們可以把 select 與 where 用到的欄位(name、password)做聯合索引，這樣就能夠直接使用索引查詢而不需用 primary key 重新比對，這就是「Coverage Index」的應用。

➤ 再次執行 SQL B：

explain select password from tmp_user where name = '寶寶不說';

你會發現本次的查詢就有使用到「Index」嚕～

總結

1. Index 需要根據實際情境建立
2. 如果查詢會用到多個欄位，建立 Coverage Index 來優化查詢效率
3. 如果資料高達百萬筆，你的 Index 是否有效是非常重要的事情

▶︎ 如果這篇文章有幫助到你
1. 可以點擊下方「Follow」來追蹤我～
2. 可以對文章拍手讓我知道 👏🏻
你們的追蹤與鼓勵是我繼續寫作的動力 🙏🏼
▶︎ 如果你對工程師的職涯感到迷茫
1. 也許我在iT邦幫忙發表的系列文可以給你不一樣的觀點 💡
2. 也歡迎您到書局選購支持，透過豐富的案例來重新檢視自己的職涯