Note - 在 MySQL 使用 Like 在 Index 上的差異

以下是 SQL 範例使用的 Table Schema

CREATE TABLE IF NOT EXIST `books` (
  `id` int(11) unsigned AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `author` date DEFAULT NULL,
  `publish_year` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name_publish_year` (`name`, `publish_year`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SQL 1

SELECT 
   name,
   publish_year
FROM
  books
WHERE
  name = 'Harry Potter'
  AND publish_year = 2009;

SQL 2

SELECT 
   name,
   publish_year
FROM
  books
WHERE
  name = 'Harry Potter';

SQL 3

SELECT 
   name,
   publish_year
FROM
  books
WHERE
  name LIKE 'Harry%';

依照最左匹配原則，SQL 1 和 SQL 2 是能使用到 Index 的。想特別講到的事 SQL 2 和 SQL3的差異，以下差異是使用 EXPLAIN 來做檢視。

• SQL 2：
  • Extra：Using index
  • type：ref
  • ref：const
• SQL 3：
  • Extra：Using where; Using index
  • type：range
  • ref：NULL

在 SQL 3 的 type: range 表示這是一個在 Index 中進行了範圍的掃描，並且在 Extra: Using where; Using index 指的是雖然使用了 Index 但是還會再進行一層過濾。所以在搜尋上的操作會像是先在 name 利用索引找到開頭是 Harry 的資料，然後再進行模糊搜尋。

延伸思考

延伸思考的話以下兩個 SQL 呢？

SQL 4

SELECT 
   name,
   publish_year
FROM
  books
WHERE
  name LIKE '%Harry%';

SQL 5

SELECT 
   name,
   publish_year
FROM
  books
WHERE
  name LIKE '%Harry';

• SQL 4：
  • Extra：Using where; Using index
  • type：index
  • ref：NULL
• SQL 5：
  • Extra：Using where; Using index
  • type：index
  • ref：NULL

這樣是指說 LIKE 會使用到 Index 嗎？其實跟 Composite Index 有關。用以下兩個範例來說明

SQL 4

SELECT 
   name
FROM
  books
WHERE
   publish_year = 2011;

SQL 5

SELECT 
   name
FROM
  books
WHERE
  publish_year > 1991;

• SQL 6：
  • Extra：Using where; Using index
  • type：index
  • ref：NULL
• SQL 7：
  • Extra：Using where; Using index
  • type：index
  • ref：NULL

會看到 SQL 6 和 SQL 7 的結果跟 SQL 4 和 SQL 5 一樣，是因為有 name 和 publish_year 組成的 composite index。type : index 表示先掃描了 index 紀錄，找到了相關的 index 在進行篩選。我個人的理解是它先去找了有符合所需的 Index 然後才去做搜尋。

在延伸的情況如果不只要 name 和 publish_year 的情況呢？

SELECT
  *
FROM
  books
WHERE
  publish_year = 1991;

因為如果使用了 composite index name_publish_year 找到了，也只能得到 name 和 publish_year 的資料，還要再回去資料庫用 primary key 找到其他欄位的資料，所以直接做全表搜尋，會更快一點。所以結果會是：

• Extra：Using where
• type：ALL
• ref：NULL

Reference

• 其实 MySQL 中的 like 关键字也能用索引！