本篇介紹 MySQL Index 的設計原則，以及如何透過 EXPLAIN 分析 SQL 查詢效能，找出慢查詢的根本原因。

為什麼需要 Index？

當資料表有 100 萬筆資料，沒有 Index 的查詢會做 Full Table Scan，從第一筆掃到最後一筆。Index 的本質是一棵 B-Tree，讓 MySQL 可以用二分搜尋快速定位資料，查詢複雜度從 O(n) 降到 O(log n)。

直白的比喻：Index 就像書本的目錄，沒有目錄就只能一頁一頁翻。

Index 的種類

種類	說明	範例
PRIMARY KEY	唯一且非 NULL，每張表只有一個	`id`
UNIQUE INDEX	值必須唯一，可為 NULL	`email`
INDEX	一般索引，加速查詢用	`status`、`created_at`
複合 INDEX	多欄位組合成一個 Index	`(user_id, status)`
FULLTEXT INDEX	全文搜尋用	`content`

建立 Index 語法

-- 建立單欄 Index
ALTER TABLE orders ADD INDEX idx_status (status);

-- 建立複合 Index
ALTER TABLE orders ADD INDEX idx_user_status (user_id, status);

-- 建立 UNIQUE Index
ALTER TABLE users ADD UNIQUE INDEX idx_email (email);

-- 查看目前 Index
SHOW INDEX FROM orders;

Laravel Migration 寫法：

Schema::table('orders', function (Blueprint $table) {
    $table->index('status');                      // 單欄 Index
    $table->index(['user_id', 'status']);          // 複合 Index
    $table->unique('email');                       // UNIQUE Index
});

EXPLAIN 怎麼看

在 SQL 前面加上 EXPLAIN，MySQL 會回傳查詢的執行計畫，而不是實際結果：

EXPLAIN SELECT * FROM orders WHERE user_id = 1 AND status = 'paid';

重點欄位解讀：

欄位	說明	注意
`type`	查詢類型，越右越好	看到 ALL 要立刻處理
`key`	實際使用的 Index 名稱	NULL 代表沒用到 Index
`rows`	預估掃描的資料筆數	越小越好
`Extra`	額外資訊	Using filesort / Using temporary 要注意

type 等級（由差到好）

type	說明	評估
`ALL`	Full Table Scan，掃全表	極差，必須優化
`index`	掃整棵 Index Tree	差
`range`	Index 範圍掃描（BETWEEN、>、<）	還可以
`ref`	非唯一 Index 的等值查詢	好
`eq_ref`	JOIN 時使用唯一 Index	很好
`const`	PRIMARY KEY 或 UNIQUE 等值查詢，最多一筆	最好

實戰案例

案例 1：沒有 Index（Full Table Scan）

EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- type: ALL, key: NULL, rows: 1000000
-- 掃全表 100 萬筆，耗時 3.2 秒

案例 2：加上 Index 後

ALTER TABLE orders ADD INDEX idx_status (status);

EXPLAIN SELECT * FROM orders WHERE status = 'paid';
-- type: ref, key: idx_status, rows: 12000
-- 耗時降至 0.08 秒

案例 3：複合 Index 的最左前綴原則

建立複合 Index (user_id, status, created_at)，以下查詢的使用情況：

-- 完整使用 Index ✓
WHERE user_id = 1 AND status = 'paid' AND created_at > '2024-01-01'

-- 使用前兩個欄位 ✓
WHERE user_id = 1 AND status = 'paid'

-- 只使用第一個欄位 ✓
WHERE user_id = 1

-- 跳過第一個欄位，Index 無效 ✗
WHERE status = 'paid'

-- 跳過中間欄位，只用到 user_id ✗（status 之後失效）
WHERE user_id = 1 AND created_at > '2024-01-01'

案例 4：LIKE 前綴萬用字元無法使用 Index

-- 可以用 Index ✓（後綴萬用）
WHERE name LIKE 'John%'

-- 無法用 Index ✗（前綴萬用，必須掃全表）
WHERE name LIKE '%John%'
WHERE name LIKE '%John'

開啟 Slow Query Log

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1        # 超過 1 秒的查詢記錄下來
log_queries_not_using_indexes = 1  # 沒用 Index 的查詢也記錄

-- 也可以在 MySQL Session 臨時開啟
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

Index 設計原則總結

WHERE、JOIN ON、ORDER BY、GROUP BY 的欄位優先考慮加 Index
複合 Index 欄位順序：區分度高的欄位放前面，等值查詢欄位優先於範圍查詢
Index 不是越多越好，寫入（INSERT / UPDATE / DELETE）時需要維護 Index，過多會拖慢寫入效能
字串欄位 Index 考慮使用 前綴 Index：ADD INDEX idx_name (name(20))
定期用 SHOW INDEX FROM table 清理沒有被使用到的 Index

參考文獻：

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html

【MySQL】Index 設計與 EXPLAIN 分析實戰

本篇介紹 MySQL Index 的設計原則，以及如何透過 EXPLAIN 分析 SQL 查詢效能，找出慢查詢的根本原因...