MySQL 基礎索引設計與選擇

      在〈MySQL 基礎索引設計與選擇〉中尚無留言

對於關聯式資料庫來說,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 秒)

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

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