資料庫最佳化

查詢變慢的常見原因

表格只有幾十列時，怎麼查都很快；資料累積到幾十萬、幾百萬列，同一個查詢可能要好幾秒。常見的原因有：

這一課從資料庫的「設計」著手：索引、資料型別、表格結構與維護。下一課再從「查詢」著手。

索引像書後面的索引

一本 500 頁的書，想找「交易」出現在哪幾頁，有兩種方法：從第一頁翻到最後一頁，或翻到書後面的索引， 索引依筆畫或字母排好，很快就找到「交易：第 128、305 頁」，再直接翻過去。

資料庫的索引（index）是同一個想法：把某個欄位的值排好序另外存一份，每個值旁邊記著「這筆資料在哪裡」。 沒有索引時，WHERE name = 'Ben Smith' 只能逐列檢查整張表格，稱為全表掃描（full table scan）； 有了 name 的索引，MySQL 在排好序的索引裡很快找到 Ben Smith，再去取那一列。

MySQL 8 預設的儲存引擎 InnoDB，索引是 B-tree（精確地說是 B+tree）結構：

• 樹的每個節點存放一段排好序的值，從樹根往下比較，每一層都能排除大部分的資料
• 資料量增加很多倍，樹也只會多一兩層，所以就算表格很大，找一個值也只需要讀少數幾個節點
• 最底層的節點依序串在一起，所以範圍查詢（BETWEEN、>、LIKE 'B%'）與排序也能利用索引

索引的種類：

CREATE INDEX 名稱 ON 表格 (欄位) 和 ALTER TABLE 表格 ADD INDEX 名稱 (欄位) 效果相同。 外鍵欄位如果還沒有索引，MySQL 會自動替它建立一個；下面的範例裡，employee 的 departmentID 索引就是這樣來的。第 04 課建表時已經用過 PRIMARY KEY、UNIQUE 與 INDEX，這一課說明它們的原理與取捨。

叢集索引與覆蓋索引

上面最後兩個查詢的順序不同，原因在 InnoDB 存放資料的方式：

• 叢集索引（clustered index）：InnoDB 的資料列本身就依主鍵排列，存在主鍵的 B-tree 裡。 所以 SELECT * FROM employee 讀出來的列，依 employeeID 由小到大。播放器右邊的表格也依主鍵排序，就是這個原因
• 次要索引（secondary index，主鍵以外的索引）：存的是「索引欄位的值＋那一列的主鍵」。 用 idx_name 找到 Ben Smith 後，拿到主鍵 9842，再回主鍵的 B-tree 取出整列
• 覆蓋索引（covering index）：查詢需要的欄位全部都在某個索引裡（次要索引本來就帶著主鍵），MySQL 只讀索引就能回答， 不必回表格取資料，比較快。這時列的順序就是索引的順序

表格沒有主鍵時，InnoDB 會改用第一個所有欄位都是 NOT NULL 的唯一索引當叢集索引；連這種索引都沒有，就自動產生一個看不到的列編號。 所以每張表格都應該定義主鍵，而且主鍵最好短：每個次要索引都帶著一份主鍵，主鍵越長，所有索引都跟著變大。

第 06 課說過「沒有 ORDER BY 時列的順序不保證」，這裡就是實例：多建一個索引，同一個查詢的順序就可能改變。 程式需要固定順序時，一定要寫 ORDER BY。

複合索引與最左前綴

複合索引把幾個欄位依序組成一個索引，例如 (dept, grade)：先依 dept 排序，dept 相同時再依 grade 排序， 就像電話簿先依姓排、同姓再依名排。

電話簿可以很快找到「姓陳的」，也可以很快找到「姓陳、名叫小明的」，但沒辦法直接找到「所有名叫小明的人」， 因為名字只在同一個姓之內才是排好的。複合索引也一樣，這叫做最左前綴（leftmost prefix）規則：

所以設計複合索引時，最常單獨出現在條件裡的欄位放最前面。有了 (dept, grade)，就不需要另外建 (dept) 的索引。 下一課會用 EXPLAIN 實際驗證這張表。

唯一索引除了加快查詢，也是一種資料規則：值重複時 INSERT 會失敗（ERROR 1062）。 學號、帳號、身分證字號這類不可重複的欄位，除了主鍵以外，都可以用唯一索引保護。

哪些欄位適合建索引

索引不是越多越好。每個索引都要另外佔空間，而且每次 INSERT、DELETE，以及修改到索引欄位的 UPDATE， MySQL 都要同時更新每一個相關的索引，索引越多，寫入越慢。

適合建立索引：

• 經常出現在 WHERE 條件裡的欄位
• 多表查詢中用來連接的欄位、外鍵（MySQL 會自動替外鍵建立索引）
• 經常用來排序（ORDER BY）、分組（GROUP BY）的欄位
• 不同的值很多的欄位，例如帳號、學號、姓名。索引能排除的資料越多，效果越好

不適合建立索引：

• 很少出現在查詢條件裡的欄位
• 不同的值很少的欄位，例如只有 0 和 1 的狀態欄位，一個值就對應一半的資料，用索引不比全表掃描快多少
• 表格只有幾十、幾百列，全表掃描就很快
• 非常頻繁更新的欄位，或大量新增、刪除的表格，要考慮維護索引的成本

建立索引之後，要用 EXPLAIN 確認查詢真的用上了（下一課）。用不到的索引只會拖慢寫入，應該刪除：

DROP INDEX idx_name ON employee;
ALTER TABLE employee DROP INDEX idx_name;      -- 效果相同

索引的欄位建好之後不能修改，要換欄位就刪除後重建。MySQL 8 可以改名，也可以先把索引設為看不見（invisible）： 優化器不再使用它，但索引仍然存在、仍會被更新，觀察一陣子確定沒有查詢變慢再刪除；需要時隨時改回 VISIBLE，不必重建：

ALTER TABLE employee RENAME INDEX idx_name TO idx_employee_name;
ALTER TABLE employee ALTER INDEX idx_employee_name INVISIBLE;
ALTER TABLE employee ALTER INDEX idx_employee_name VISIBLE;

（播放器的直譯器沒有支援看不見的索引，這兩行請在真的 MySQL 8 上練習；SHOW INDEX 的 Visible 欄位會變成 NO。）

選擇合適的資料型別

資料型別越小，每一列越短，同樣大小的記憶體與磁碟頁（InnoDB 預設一頁 16 KB）就能放更多列，索引也越小。 原則是：選擇能正確存放資料的最小型別。

整數：依數值範圍選擇

例如 1 到 10 的評分用 TINYINT UNSIGNED。超出範圍時，MySQL 8 預設的嚴格模式會直接拒絕（ERROR 1264），不會偷偷改成最大值。 數字型的代碼也要想清楚：學號、員工編號用 INT 比用 VARCHAR 小，比較也快； 但電話號碼、郵遞區號可能以 0 開頭，也不會拿來做加減，應該用字串。

CHAR 與 VARCHAR

• CHAR(n)：固定長度，存入時在右邊補空白，取出時把尾端空白去掉
• VARCHAR(n)：存多長就是多長，另外用 1～2 個位元組記錄長度，尾端空白會保留

原始課程建議「選固定長度的 CHAR，不用 VARCHAR、TEXT、BLOB」，這是 MyISAM 時代的做法： MyISAM 表格所有欄位都是固定長度時，每一列一樣長，讀寫比較快。MySQL 8 預設的 InnoDB 不靠這個加速， 而且 utf8mb4 的 CHAR 在 InnoDB 裡也不一定佔固定長度。現在的原則是：長度固定的代碼（州別 VA、國家代碼 TW）用 CHAR， 長度會變的文字（姓名、地址）用 VARCHAR，長度上限設成合理的值就好。

DECIMAL 與 FLOAT

• DECIMAL(p, s)：精確的十進位數，DECIMAL(8,2) 是共 8 位數、小數 2 位。金額一定用 DECIMAL
• FLOAT（4 位元組）、DOUBLE（8 位元組）：近似值，0.1 這種數在二進位裡無法精確表示，加總與比較會出現誤差。 適合科學量測這類本來就有誤差的資料

正規化與反正規化

第 03 課的正規化把資料拆成多張表格，每項事實只存一次：員工表格只存 departmentID，部門名稱存在部門表格。 好處是修改部門名稱只要改一列，不會出現同一個部門有兩種名稱的矛盾；表格也比較窄，通常有助於效能。

但查詢時要用 JOIN 把資料接回來，表格拆得越細（4NF、BCNF、5NF），一個查詢要連接的表格越多。 如果某個查詢非常頻繁、又要連接很多張表格，可以考慮反正規化（denormalization）：刻意保留一些重複的資料，換取查詢速度。常見的做法：

反正規化是先量測、確定瓶頸之後才做的取捨，不是設計的起點：先做好正規化與索引，用 EXPLAIN 和慢查詢日誌找到真正慢的查詢，再決定。

維護表格：ANALYZE 與 OPTIMIZE

MySQL 決定要不要用某個索引時，依據的是統計資訊，例如 SHOW INDEX 的 Cardinality（不同值個數的估計）。 InnoDB 不是每次都數過所有資料，而是抽樣幾個資料頁估計；資料大量變動後，統計資訊可能跟實際情況差很多，導致 MySQL 選錯執行方式。

InnoDB 預設會在一張表格大約一成的資料變動後自動重算統計資訊，所以平常不需要手動執行。 對 InnoDB 表格執行 OPTIMIZE TABLE 時，MySQL 會回應一則 note：Table does not support optimize, doing recreate + analyze instead， 意思是 InnoDB 用「重建表格再分析」的方式完成，這是正常的，最後一列的 status OK 表示成功。 重建大表格要花時間、佔用額外的磁碟空間，請在離峰時段執行。

重點整理

下一課用 EXPLAIN 看 MySQL 實際怎麼執行一個查詢，確認索引有沒有用上。