您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 聯合索引實戰：解決「員工-部門」多對多關係唯一性約束

編輯：MySQL 綜合教學

MySQL 聯合索引實戰：解決「員工-部門」多對多關係唯一性約束

中間表實現多對多關係時，如何防止同一員工重複關聯同一部門？本文通過完整建表腳本演示聯合唯一索引的創建、驗證與最佳實踐，並解析「最左前綴原理」在查優化中的應用。

一、業務場景與表結構設計

-- 初始化數據
INSERT INTO `employee` (`name`) VALUES ('張三'), ('李四');
INSERT INTO `dept` (`dept_name`) VALUES ('研發部'), ('市場部');

-- 正常關聯
INSERT INTO `employee_dept` (`employee_id`, `dept_id`) VALUES (1, 1);  -- 張三→研發部
INSERT INTO `employee_dept` (`employee_id`, `dept_id`) VALUES (1, 2);  -- 張三→市場部（允許）

-- 重複關聯（將報錯）
INSERT INTO `employee_dept` (`employee_id`, `dept_id`) VALUES (1, 1);  
-- ERROR 1062 (23000): Duplicate entry '1-1' for key 'uk_emp_dept'

三、聯合索引創建的兩種方式

方式	語法	適用場景
建表時內聯	UNIQUE KEY `idx_name` (`col1`, `col2`)	新表設計，推薦
建表後添加	ALTER TABLE `tbl` ADD UNIQUE KEY `idx_name` (`col1`, `col2`)	既有表補充約束
通用創建語法	CREATE UNIQUE INDEX `idx_name` ON `tbl` (`col1`, `col2`)	等價於 ALTER TABLE

四、最左前綴原理與查詢優化

聯合索引 `uk_emp_dept (employee_id, dept_id)` 的 B+ 樹按 `` 排序。

查詢條件	能否用到索引	說明
WHERE employee_id = 1	✅ 是	最左列匹配，索引範圍掃描
WHERE employee_id = 1 AND dept_id = 2	✅ 是	完全匹配，索引精確查找
WHERE dept_id = 2	❌ 否	非最左列，全表掃描（需單獨索引）
WHERE employee_id > 1	✅ 是	範圍查詢，最左列可用
ORDER BY employee_id, dept_id	✅ 是	索引天然有序，避免 filesort