問題分析

在 MySQL 中，當一個字段同時存在唯一索引和普通索引時，查詢優化器通常會優先選擇最嚴格的索引（即能最快縮小結果集的索引）。在你的場景中，優化器選擇了唯一索引并將查詢視為const類型，這通常是高效的，但實際性能卻不理想。

唯一索引導致全索引掃描：如果查詢需要返回其他字段，即使使用唯一索引，也可能需要回表或全索引掃描。

問題就是出在這！

事實上走了唯一索引，很慢，我把唯一索引刪了，走普通索引就很快，但是我又希望確保這個字段的唯一性，怎么辦

解決方案：保留唯一性約束但優化索引使用

你遇到的情況很常見：唯一索引雖然保證了數據唯一性，但在某些查詢模式下可能不如普通索引高效。以下是幾種保留唯一性約束同時提升查詢性能的方法：

方案一：復合索引優化（推薦）

原理：創建包含唯一字段和常用查詢字段的復合索引，讓優化器選擇更合適的索引路徑。

示例：
假設表結構如下：

sql

CREATE TABLE users (id INT PRIMARY KEY,unique_id VARCHAR(20) UNIQUE,  -- 導致性能問題的唯一索引name VARCHAR(50),age INT,INDEX idx_name_age (name, age)  -- 普通復合索引
);

優化步驟：

1. 刪除原唯一索引：

   sql

   ALTER TABLE users DROP INDEX unique_id;
   

2. 創建包含唯一性約束的復合索引：

   sql

   CREATE UNIQUE INDEX idx_unique_id_name ON users (unique_id, name);
   

3. 查詢優化：

   sql

   -- 原查詢（可能變慢）
   SELECT * FROM users WHERE name = 'John';-- 優化后查詢（利用復合索引）
   SELECT unique_id, name FROM users WHERE name = 'John';
   

優勢：

• 保留了unique_id的唯一性約束
• 復合索引可同時滿足唯一性和查詢性能需求
• 覆蓋索引避免回表，提升查詢效率

方案二：觸發器實現唯一性檢查

原理：通過觸發器手動檢查插入 / 更新操作的唯一性，替代數據庫級別的唯一索引。

示例：

sql

-- 創建觸發器確保唯一性
DELIMITER $$
CREATE TRIGGER check_unique_id
BEFORE INSERT ON users
FOR EACH ROW
BEGINIF EXISTS (SELECT 1 FROM users WHERE unique_id = NEW.unique_id) THENSIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Duplicate entry for unique_id';END IF;
END$$
DELIMITER ;

優勢：

• 完全移除唯一索引，釋放索引維護開銷
• 查詢可自由使用普通索引

注意事項：

• 觸發器會增加插入 / 更新的開銷
• 并發寫入時可能出現競態條件（需配合事務）
• 建議結合應用層唯一性檢查使用

方案三：應用層保證唯一性

原理：在應用代碼中實現唯一性檢查，而非依賴數據庫。

示例偽代碼：

python

運行

# Python示例
def create_user(unique_id, name):# 1. 開始事務with db.transaction():# 2. 檢查唯一性existing = User.query.filter_by(unique_id=unique_id).first()if existing:raise ValueError("Unique ID already exists")# 3. 插入數據user = User(unique_id=unique_id, name=name)db.session.add(user)db.session.commit()

優勢：

• 數據庫完全不維護唯一索引，查詢性能最優
• 靈活性高，可自定義錯誤處理邏輯

注意事項：

• 需要強事務保證（Serializable 隔離級別或行鎖）
• 高并發場景下性能可能受影響
• 必須在所有寫入路徑中統一實現檢查

方案四：哈希索引優化

原理：對唯一字段生成哈希值，用哈希值的唯一索引替代原始字段的唯一索引。

示例：

sql

-- 添加哈希字段
ALTER TABLE users ADD COLUMN unique_id_hash INT;-- 計算哈希值并創建唯一索引
UPDATE users SET unique_id_hash = CRC32(unique_id);
CREATE UNIQUE INDEX idx_unique_hash ON users (unique_id_hash);-- 觸發器自動維護哈希值
DELIMITER $$
CREATE TRIGGER update_hash
BEFORE INSERT ON users
FOR EACH ROW
BEGINSET NEW.unique_id_hash = CRC32(NEW.unique_id);
END$$
DELIMITER ;

查詢優化：

sql

-- 原始查詢（走普通索引）
SELECT * FROM users WHERE name = 'John';-- 唯一性檢查（走哈希唯一索引）
SELECT 1 FROM users WHERE unique_id_hash = CRC32('test_id');

優勢：

• 哈希索引更小、查詢更快
• 保留了唯一性約束
• 原始字段可自由使用普通索引

注意事項：

• 哈希碰撞概率（雖然極低，但需處理）
• 額外存儲開銷
• 插入 / 更新性能略有下降

最佳實踐建議

1. 優先選擇復合索引：如果查詢模式固定，復合索引是最簡單有效的方案。

2. 監控索引使用情況：定期使用EXPLAIN分析關鍵查詢，確保優化器選擇預期的索引。

3. 分區表考慮：如果表數據量極大，可考慮按unique_id進行哈希分區，分散索引負載。

4. 讀寫分離：對唯一性要求極高但讀多寫少的場景，可將唯一性檢查放在從庫進行。

根據你的具體業務場景選擇最合適的方案，通常復合索引能在保留約束的同時提供良好的性能。