筆記 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 关键字也能用索引！

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

Comments

Software development

知識閱讀 - 5 種 useEffect 無限迴圈的模式

More from this blog

如何開始入門軟體工程領域 - 名詞解釋（長期更新）

我的 MacBook Pro (Apple Silicon) 設定

ChatGPT 下的發展預想

一些覺得有用的工具蒐藏

LeetCode Solution, Medium, 607. Sales Person

延伸思考

Reference

Command Palette

Comments

Software development

知識閱讀 - 5 種 useEffect 無限迴圈的模式

More from this blog

延伸思考

Reference