MySQL 索引鍵類型深度解析：KEY、PRIMARY KEY、UNIQUE KEY 與 INDEX 差異總整理

MySQL 索引機制核心概念釐清

索引是 MySQL 效能優化的關鍵，但 KEY、PRIMARY KEY、UNIQUE KEY、INDEX 等術語常令人困惑。本文從邏輯層面與物理實作層面雙向剖析，釐清它們的定義、差異與適用場景。

一、KEY 與 PRIMARY KEY 的本質區別

1. 程式碼範例解析

CREATE TABLE wh_logrecord (
    logrecord_id int(11) NOT NULL auto_increment,
    user_name varchar(100) default NULL,
    operation_time datetime default NULL,
    logrecord_operation varchar(100) default NULL,
    PRIMARY KEY (logrecord_id),
    KEY wh_logrecord_user_name (user_name)
);

2. 關鍵點解析

• KEY wh_logrecord_user_name (user_name)：這是一個**普通索引**（非外鍵），名稱為 wh_logrecord_user_name，作用於 user_name 欄位
• KEY ≠ 外鍵：MySQL 中 KEY 是索引的同義詞，外鍵必須顯式使用 FOREIGN KEY 關鍵字
• PRIMARY KEY：主鍵約束，隱含 NOT NULL + UNIQUE，一張表僅能有一個

3. 多欄位索引與最左前綴原則

KEY forum (status, type, displayorder)  -- 多欄位聯合索引
KEY tid (tid)                            -- 單欄位索引

查詢優化器使用規則：

• WHERE status=1 AND type=2 AND displayorder=3 → 可用索引
• WHERE status=1 AND type=2 → 可用索引（最左前綴）
• WHERE type=2 AND displayorder=3 → 無法使用（未包含最左列）

二、KEY 與 INDEX：邏輯層 vs 物理層

官方文檔定義

KEY 是邏輯層面概念，描述表與資料庫設計（如參考完整性、唯一性約束）
INDEX 是物理層面實作，用於加速查詢存取

關鍵差異對照表

重要結論

• 每個 PRIMARY KEY 都會自動建立對應的 UNIQUE INDEX
• 設計表時專注邏輯鍵（主鍵、外鍵、唯一鍵）
• 效能調優時針對查詢模式建立額外索引

三、PRIMARY KEY vs UNIQUE KEY 詳細比較

NULL 值特殊行為實測

ALTER TABLE t ADD CONSTRAINT uk_t_1 UNIQUE (a,b);
INSERT INTO t (a,b) VALUES (NULL,1);    -- 不可重複
INSERT INTO t (a,b) VALUES (NULL,NULL); -- 可重複（全 NULL 不記錄於索引）

四、UNIQUE KEY 實戰應用指南

1. 建表時定義

-- 單欄位
CREATE TABLE Persons (
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    UNIQUE (Id_P)
);

-- 多欄位 + 命名約束
CREATE TABLE Persons (
    Id_P int NOT NULL,
    LastName varchar(255) NOT NULL,
    CONSTRAINT uc_PersonID UNIQUE (Id_P, LastName)
);

2. 事後新增 (ALTER TABLE)

-- 單欄位
ALTER TABLE Persons ADD UNIQUE (Id_P);

-- 多欄位 + 命名
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P, LastName);

3. 移除唯一約束

-- MySQL 使用 DROP INDEX（因為唯一約束實作為 UNIQUE INDEX）
ALTER TABLE Persons DROP INDEX uc_PersonID;

五、總結與最佳實踐

1. 語義正確：業務主鍵用 PRIMARY KEY，業務唯一性用 UNIQUE KEY，單純加速查詢用 INDEX
2. 避免過度索引：每個索引增加寫入成本，僅在查詢模式明確時建立
3. 命名規範：pk_表名、uk_表名_欄位、idx_表名_欄位
4. 複合索引順序：高選擇性欄位優先，符合最左前綴查詢模式

深入理解這四者差異，能幫助你設計出既符合業務語義又具備高效能的資料庫結構。