MySQL 索引 (Index) — PRIMARY、UNIQUE 與普通 INDEX 的完整指南

簡介

在資料庫的日常開發與維運工作中，索引是提升查詢效能的關鍵武器。沒有適當的索引，即使是最簡單的 SELECT 也可能因全表掃描而耗盡資源，導致系統回應緩慢、使用者體驗下降。
MySQL 支援多種索引類型，其中最常見且最重要的三種是：

PRIMARY KEY（主鍵）
UNIQUE KEY（唯一鍵）
普通 INDEX（非唯一索引）

本篇文章將從概念、語法、實作範例、常見陷阱與最佳實踐，逐步帶領讀者掌握這三種索引的使用時機與效益，協助你在開發與優化 MySQL 應用時，做出正確的決策。

核心概念

1. PRIMARY KEY（主鍵）

唯一且不允許 NULL：每一筆資料在主鍵欄位上必須有唯一的值，且不可為 NULL。
隱含聚集索引（Clustered Index）：在 InnoDB 引擎中，主鍵同時是聚集索引，資料列會依主鍵值的順序實際儲存在磁碟上，這意味著主鍵的讀寫效能往往是最好的。
自動建立：若在 CREATE TABLE 時未明確指定主鍵，MySQL 仍會自動為第一個 NOT NULL 的唯一索引建立隱藏的主鍵（但不建議依賴此行為）。

範例 1：建立含主鍵的資料表

CREATE TABLE users (
    user_id   BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    username  VARCHAR(50)      NOT NULL,
    email     VARCHAR(100)     NOT NULL,
    created_at TIMESTAMP       DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id)               -- 明確宣告主鍵
);

說明：user_id 為自動遞增的 BIGINT，作為唯一的識別碼。因為是聚集索引，所有資料會依 user_id 的順序排列。

2. UNIQUE KEY（唯一鍵）

保證唯一性：欄位或欄位組合的值不能重複。
允許 NULL：唯一鍵允許 NULL，且在多筆 NULL 時仍被視為不同（除非使用 NOT NULL 限制）。
非聚集索引：在 InnoDB 中，唯一鍵是非聚集索引（Secondary Index），其索引結構儲存了主鍵的值，以便快速定位。

範例 2：為 email 欄位加上唯一鍵

CREATE TABLE members (
    member_id INT AUTO_INCREMENT,
    name      VARCHAR(30) NOT NULL,
    email     VARCHAR(100) NOT NULL,
    PRIMARY KEY (member_id),
    UNIQUE KEY uq_email (email)          -- 確保 email 不會重複
);

說明：即使 email 欄位不是主鍵，我們仍能保證每筆會員的電子郵件地址唯一，避免重複註冊。

3. 普通 INDEX（非唯一索引）

加速查詢：對於常被 WHERE、JOIN、ORDER BY 或 GROUP BY 使用的欄位，建立普通索引可以大幅減少掃描的資料筆數。
允許重複與 NULL：不會限制欄位值的唯一性，且允許多筆 NULL。
覆寫索引（Covering Index）：若索引中已包含查詢所需的所有欄位，MySQL 可以直接從索引返回結果，避免回表（回到資料列），提升效能。

範例 3：為交易紀錄的 `order_date` 加上普通索引

CREATE TABLE orders (
    order_id    BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    user_id     BIGINT UNSIGNED NOT NULL,
    order_date  DATE NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id),
    INDEX idx_order_date (order_date)   -- 普通索引，加速日期範圍查詢
);

說明：若常有「查詢某段時間內的訂單」需求，idx_order_date 能讓 MySQL 直接定位到符合條件的資料頁面。

4. 複合索引（Composite Index）

多欄位組合：一次索引多個欄位，適用於同時出現在查詢條件中的多個欄位。
左前綴原則：只要查詢條件使用了索引的最左側欄位（左前綴），就能利用該索引。
排序與分組：若索引的欄位順序與 ORDER BY、GROUP BY 完全相同，MySQL 甚至可以免除額外的排序步驟。

範例 4：為 `orders` 建立複合索引（user_id + order_date）

ALTER TABLE orders
ADD INDEX idx_user_date (user_id, order_date);

說明：此索引同時支援「某使用者在特定日期範圍內的訂單」查詢，以及「依 user_id 排序」的需求。

程式碼範例（實務操作）

以下示範 5 個常見情境，說明如何正確使用 PRIMARY、UNIQUE 與普通 INDEX。

範例 1：建立含自動遞增主鍵的商品表

CREATE TABLE products (
    product_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    sku        VARCHAR(20) NOT NULL,
    name       VARCHAR(100) NOT NULL,
    price      DECIMAL(8,2) NOT NULL,
    PRIMARY KEY (product_id),               -- 主鍵
    UNIQUE KEY uq_sku (sku)                 -- SKU 必須唯一
);

重點：sku（庫存單位）雖非主鍵，但透過唯一鍵保證不會有重複的商品代碼。

範例 2：為常用搜尋欄位建立普通索引

CREATE TABLE articles (
    article_id BIGINT AUTO_INCREMENT,
    title      VARCHAR(200) NOT NULL,
    author_id  INT NOT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (article_id),
    INDEX idx_author (author_id),           -- 常用於 author_id 的篩選
    INDEX idx_created (created_at)          -- 常用於日期範圍查詢
);

技巧：若同時常用 author_id 與 created_at 兩個條件，可考慮建立複合索引 INDEX idx_author_created (author_id, created_at)。

範例 3：利用覆寫索引（Covering Index）提升查詢效能

SELECT article_id, title
FROM articles
WHERE author_id = 42;

對上述查詢，我們可以建立僅包含 author_id、article_id、title 的索引：

CREATE INDEX idx_cover_author
ON articles (author_id, article_id, title);

結果：MySQL 直接從索引頁面返回 article_id 與 title，不需要回到資料列（回表），查詢速度顯著提升。

範例 4：使用唯一鍵防止重複插入

INSERT INTO members (name, email) VALUES ('Alice', 'alice@example.com');
-- 再次插入相同 email 會失敗
INSERT INTO members (name, email) VALUES ('Bob', 'alice@example.com');
-- -> ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'uq_email'

說明：唯一鍵自動幫我們檢查重複，避免在程式碼中額外寫檢查邏輯。

範例 5：刪除不再需要的索引（維護成本）

-- 查看目前索引
SHOW INDEX FROM orders;

-- 移除已不使用的 idx_order_date
DROP INDEX idx_order_date ON orders;

提醒：過多或未使用的索引會佔用磁碟空間、降低寫入效能，定期檢視與清理是維運必備工作。

常見陷阱與最佳實踐

陷阱	說明	最佳實踐
過度建立索引	每個索引都會在 `INSERT/UPDATE/DELETE` 時維護，過多索引會拖慢寫入速度。	只為常用查詢建立索引，使用 `EXPLAIN` 檢視效能。
忽略左前綴原則	複合索引若查詢未使用最左側欄位，索引將無法被使用。	設計複合索引時，將查詢最常使用的欄位放在左側。
使用過長的字串索引	`VARCHAR(255)` 直接建立索引會產生巨大的 B‑tree，浪費空間。	為長字串加上前綴長度（如 `INDEX (email(50))`），或改用 `HASH` 索引（MySQL 8+）。
未考慮 NULL 值	唯一鍵允許多個 `NULL`，若需要不允許 NULL，必須額外加上 `NOT NULL`。	明確宣告欄位屬性，必要時加 `NOT NULL`。
忽略字符集與排序規則	不同的字符集（utf8mb4）與排序規則（utf8mb4_unicode_ci）會影響索引大小與比較行為。	盡量使用 utf8mb4，對於需要精確比較的欄位選擇 binary 排序規則。
在大量寫入的表上使用過多唯一索引	每次寫入都要檢查唯一性，會產生鎖爭用。	若唯一性不是業務必須，可改用普通索引或在應用層檢查。

其他最佳實踐

使用 EXPLAIN 分析查詢計畫：確保索引真的被使用，並觀察 type、rows、key 等欄位。
定期重建碎片化的索引（OPTIMIZE TABLE），尤其是大量刪除後。
將自增主鍵設為 BIGINT UNSIGNED，避免未來因資料量過大而溢位。
避免在頻繁更新的欄位上加唯一索引，因唯一檢查會加重鎖定。
利用 INVISIBLE INDEX（MySQL 8+）測試索引效益：先隱藏索引觀察查詢變化，再決定是否保留。

實際應用場景

場景	建議索引	為什麼
電商商品搜尋	`PRIMARY KEY(product_id)`、`UNIQUE KEY(uq_sku)`、`INDEX idx_category (category_id)`、`INDEX idx_price (price)`	商品編號唯一、SKU 必須唯一、分類與價格常作為篩選條件。
社交平台貼文排序	`PRIMARY KEY(post_id)`、`INDEX idx_user_time (user_id, created_at)`	常查詢「某使用者最近的貼文」，複合索引同時支援 `ORDER BY created_at DESC`。
會員註冊驗證	`PRIMARY KEY(member_id)`、`UNIQUE KEY(uq_email)`、`UNIQUE KEY(uq_username)`	防止重複註冊，唯一鍵直接在 DB 層保證資料正確性。
日誌資料分析	`PRIMARY KEY(log_id)`、`INDEX idx_ts (timestamp)`、`INDEX idx_type (log_type)`	大量寫入，僅在查詢時依時間或類型篩選，保持寫入效能。
多對多關聯表（如 `order_items`）	`PRIMARY KEY(order_id, product_id)`、`INDEX idx_product (product_id)`	主鍵為複合鍵保證同一訂單不會重複同商品，`product_id` 索引加速商品維度的查詢。

總結

PRIMARY KEY 是唯一且不允許 NULL 的聚集索引，適合作為資料表的唯一識別碼，且在 InnoDB 中提供最佳的讀寫效能。
UNIQUE KEY 讓欄位（或欄位組合）保持唯一性，同時允許 NULL，屬於非聚集索引，適合用於業務上必須避免重複的欄位（如 email、SKU）。
普通 INDEX 用於加速查詢、排序與分組，建立時要考慮左前綴原則、字串長度與使用頻率。
複合索引 能同時支援多欄位條件與排序，但必須依照最左側欄位的使用情況來設計。
最佳實踐 包括：只為常用查詢建立索引、利用 EXPLAIN 確認索引被使用、避免過多索引造成寫入瓶頸、定期檢視與重建索引。

掌握了這三種索引的特性與使用時機，你就能在 MySQL 中設計出既高效又穩定的資料結構，讓系統在面對大量資料與高併發請求時，仍能保持流暢的使用者體驗。祝你在實務開發中玩得開心、效能飛躍！ 🚀