MySQL 基礎索引設計與選擇

• Tweet
• 

• Tweet
• 

對於關聯式資料庫來說，index (索引) 是一個很重要的東西，如果欄位有 index 的話在查詢速度會很快，如果沒有設計 index 就會需要 scan table，但是加上 index 並不是無成本的，首先 index 需要有空間進行儲存，多餘的 index 會浪費儲存空間，多個索引甚至可能比 table 還大，另外 MySQL 中是使用 B+Tree 進行索引，在 insert 資料時會需要更新樹的結構，當 index 太多的時候就會需要重整太多的樹會消費比較多的時間，本篇介紹可以如何設計 index。

索引的設計

假設現在有個書店，儲存書本的表格如下：

CREATE TABLE `book` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '書名',
  `isbn` varchar(15) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '統一商品編碼',
  `price` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '價格',
  `language` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT '內文語言',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

這邊以這個 table 當作範例

依照使用需求加上索引 – 單一索引

假如現在電商的功能是以價格範圍進行搜尋，查詢的 SQL 如下：

SELECT * FROM book WHERE price >= ? AND price <= ?;

因為 WHERE 條件查詢只有一個欄位，所以這很簡單那索引 (price)

ALTER TABLE `book` 
ADD INDEX `__index__price` (`price`);

依照使用需求加上索引 – 組合索引

而假如現在有情境變成搜尋價格指定語言的書本

SELECT * FROM book WHERE price >= ? AND price <= ? AND language = ?;

這時條件式有兩個欄位，多了 language 這個欄位，這時不能只加一個 index (language)，因為在同一張 table 中， simple query 只為選擇使用一個 index 來查詢

所以要弄一個組合式 index (price, language) 或 (language, price)，而有兩種組合哪一種比較好呢？ 答案是選擇分類比較少的放前面會比較快，例如這邊語系實際上只有約兩百個值，而 price 是數字值比較多變化，所以這邊要選擇 (language, price)

來比較一下單一索引與組合型索引的速度差異

下圖為 (price) + (language) 兩個索引的查詢速度 (0.89 秒)

下圖為 (price, language) 一個索引的查詢速度 (0.42 秒)

下圖為 (language, price) 一個索引的查詢速度 (0.02 秒)