MySQL 索引類型全解析：從單列到組合索引的效能實戰

MySQL 索引類型全解析：從單列到組合索引的效能實戰

索引是資料庫高效查詢的基石。本文詳細對比單列與組合索引，深度解析五大索引類型的建立語法、適用場景與避坑指南，並給出索引建立時機與注意事項的最佳實踐。

一、單列索引 vs 組合索引

單列索引： 一個索引僅包含單一欄位，一張表可建立多個單列索引，但它們獨立運作。

組合索引： 一個索引包含多個欄位，遵循「最左前綴」原則，查詢優化器僅能使用最左側連續欄位。

二、五大核心索引類型與建立語法

1. 普通索引

最基本的索引，無任何限制。

-- 方式一：CREATE INDEX
CREATE INDEX idx_username ON mytable(username(10));

-- 方式二：ALTER TABLE
ALTER TABLE mytable ADD INDEX idx_username (username(10));

-- 方式三：建表時指定
CREATE TABLE mytable (
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    INDEX idx_username (username(10))
);

-- 刪除
DROP INDEX idx_username ON mytable;

2. 唯一索引

值必須唯一，允許 NULL。組合唯一索引要求欄位組合唯一。

CREATE UNIQUE INDEX idx_unique_user ON mytable(username(10));
ALTER TABLE mytable ADD UNIQUE idx_unique_user (username(10));
CREATE TABLE mytable (
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE idx_unique_user (username(10))
);

3. 主鍵索引

特殊的唯一索引：不允許 NULL，每表僅一個。

CREATE TABLE mytable (
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    PRIMARY KEY (ID)
);
-- 或
ALTER TABLE mytable ADD PRIMARY KEY (ID);

4. 組合索引實戰

ALTER TABLE mytable ADD INDEX idx_name_city_age (name(10), city, age);

等價於建立三組索引：(name, city, age)、(name, city)、(name)。

生效查詢：

SELECT * FROM mytable WHERE username='admin' AND city='鄭州';
SELECT * FROM mytable WHERE username='admin';

失效查詢：

SELECT * FROM mytable WHERE age=20 AND city='鄭州';  -- 非最左列
SELECT * FROM mytable WHERE city='鄭州';              -- 非最左列

5. 全文索引

僅 MyISAM（5.6 前）與 InnoDB（5.6+）支援，針對長文本搜尋。

CREATE FULLTEXT INDEX idx_ft ON mytable(content);

三、索引建立的最佳時機

1. WHERE / JOIN 條件欄位： SELECT t.Name FROM mytable t LEFT JOIN mytable m ON t.Name=m.username WHERE m.age=20 AND m.city='鄭州' → 需為 age、city、username 建索引
2. LIKE 前綴匹配： username LIKE 'admin%' 可用索引；LIKE '%admin' 全表掃描
3. 避免在索引欄位上運算： YEAR(adddate) < 2007 → 改為 adddate < '2007-01-01'
4. 不使用 NOT IN、!=、<>： 導致索引失效

四、索引的代價與避坑指南

五、實戰優化技巧

• 短索引： VARCHAR(255) 僅索引前 10-20 字元
• 索引欄位排序： WHERE 用到索引時，ORDER BY 不再使用索引；避免多欄排序，改用組合索引
• 定期分析： ANALYZE TABLE 更新統計資訊，讓優化器做出更好決策