淺談MySQL Index

January 30, 2021

資料庫是可以讓我們儲存資料和查詢資料的地方

假設我們想要儲存所有學生的基本資料 我們可以創建一個table

CREATE TABLE students (
  student_id bigint NOT NULL AUTO_INCREMENT,
  ssn varchar(9) DEFAULT NULL,
  first_name varchar(255) DEFAULT NULL,
  last_name varchar(255) DEFAULT NULL,
  age int DEFAULT NULL,
  start_date DATETIME DEFAULT NULL,
  PRIMARY KEY (student_id)
)

當然不能忘記要加一個student_id 不然要是有兩個人同名 我們就不知道誰是誰

這個student id同時也是primary key 不允許重複

那你說為什麼不用ssn當key就好呢 因為不是所有學生都有ssn(比如交換學生或是留學生) 所以ssn有個DEFAULT NULL

面試官來了

嗯這個table看起來不錯 那面試官問你你應該在哪個column建立索引呢

要是你回答 ssn 或是 first_name 或是last_name 或是 age 你就等著像Citron一樣被擊飛到外太空

答案應該是 取決於使用者的使用情況 觀察哪種queries最頻繁最吃資源 再考慮值不值得創建索引

MySQL 存儲原理

說破哪值幾文錢 大部分的MySQL實現都是使用Key-Value存儲每筆數據 而這個key-value本身則是用BTree存

所以我們剛剛創建的那個students表 實際上每加一筆資料 我們加的是一筆key-value

(student_id) : (ssn, first_name, last_name, age)

就是因為這樣 所以當有人問說 誒誒我要知道student_id=5的名字 資料庫才能在很快的時間回傳給你

理解了原理之後 假設我們發現 資料庫的使用者很常在WHERE裡面利用ssn搜尋 比如

 
SELECT first_name
FROM students
WHERE ssn = '123456789' 

 
SELECT age
FROM students
WHERE ssn = '987654321' 

不難理解 每次執行這種查詢 都要整個table掃描去找ssn

解法就是 我們可以在ssn上加個索引

  
CREATE UNIQUE INDEX ssn on students (ssn);

這樣的話 系統就會再創建一個Key-Value存儲

(ssn) : (student_id)

這樣我們就可以用很快的時間找到student_id 再用很快的時間從這個student_id去找到age或是first_name

當然 壞處就是要多花一點空間去存第二個Key-Value數據 還有每次有新學生加入的時候 有兩個Key-Value數據要改

來聊聊細節

我們已經不是學生了 上面那些只能用來騙騙面試官而已 我來問問你 創建index的時候 UNIQUE是什麼意思?

這個意思就是 我知道這個column不會有重複 我才可以用UNIQUE INDEX

所以如果今天我們想對first_name創建索引 你必須拿掉UNIQUE

CREATE INDEX first_name on students (first_name);

那內部的key-value就不能這麼存

(first_name) : (student_id)

因為有很多人會有一樣的first_name 所以上面那樣存沒法用 而是變成這麼存

(first_name, student_id) : ()

注意 這裡student_id會照順序存 比如說

(‘Johnson’, 2) : ()

(‘Johnson’, 5) : ()

(‘Johnson’, 8) : ()

我等於是在存儲的時候就幫你排序好了 減少每次查詢的時候需要花在排序的時間 所以下面這個查詢

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson';

就等於是這樣

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson' order by student_id;

創建索引除了讓我們查詢更快 也讓資料變得有序

我們雖然讓資料有序 但這個有序卻是照著student_id排的序 如果我們想要

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson' order by last_name;

那就沒救了 那就只能找到所有first_name是Johnson的student_id之後 全部拿出來再排序

所以要是我們發現這種查詢很常發生

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson' 
ORDER BY last_name LIMIT 3;

哇賽 每次都要把所有的Johnson的資料拿出來 假設有1000個Johnson 然後每次只顯示前三個 那實在太沒效率了

我們需要再創建一個索引

CREATE INDEX first_last on students (first_name, last_name);

系統就會再創建一個Key-Value存儲

(first_name, last_name, student_id) : ()

這樣就容易了 因為對於同一個first_name來說 last_name是排序過的 就很好處理類似以下的查詢

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson' 
ORDER BY last_name LIMIT 1;

SELECT last_name, ssn 
FROM students 
WHERE first_name = 'Johnson' AND last_name = 'Chiang' 
ORDER BY student_id 
LIMIT 1;

但注意 無法search by last_name only

不夠過癮是吧 那我們再說一點

1.固定的值比範圍查詢好

比如說如果有school_location這個column 而且我們有對這個column索引的話

這個寫法比較有效(如果你知道所有可能的school位置)

SELECT * 
FROM students 
WHERE school_location IN ('NYC', 'BOS', 'LA') and first_name = 'Johnson'

這個寫法比較沒效率

SELECT * 
FROM students 
WHERE school_location <> 'SF' and first_name = 'Johnson'

同理 如果status只能是1,2,3,4 那

WHERE status IN (1,3,4)

WHERE status <> 2

2.輸出column的順序最好跟索引一樣

假設我們有對(first_name, last_name)做索引

SELECT * 
FROM students 
WHERE first_name IN ('Johnson', 'Mary') 
ORDER BY first_name, last_name

SELECT * 
FROM students 
WHERE first_name IN ('Johnson', 'Mary') 
ORDER BY last_name

3.如果要對有索引的column做範圍查詢可以 但麻煩直接下範圍查詢 不要再加上其他操作

假設我們有對start_date下索引

WHERE start_date BETWEEN '2021-01-01' AND '2021-01-31'

WHERE YEAR(start_date) = 2021 AND MONTH(start_date) = 1

有效不少 後者根本無法有效的利用BTree結構

4.直接對有索引的column下比較值

WHERE start_date > NOW() - ONE_YEAR_INTERVAL

WHERE start_date + ONE_YEAR_INTERVAL > NOW()

今天先講到這吧 大家有興趣的話再多講一些

總之要創建什麼index完全取決於使用者的常用查詢

更多更詳細的INDEX使用法 可以參考MySQL Doc