mysql 复合唯一索引

基础概念

MySQL中的复合唯一索引（Composite Unique Index）是指在一个表上创建的包含多个列的唯一索引。它确保了这些列的组合值在整个表中是唯一的。复合唯一索引可以有效地防止数据冗余和重复记录。

优势

1. 数据完整性：确保多个列的组合值在表中唯一，维护数据的完整性和一致性。
2. 查询优化：复合索引可以提高涉及多个列的查询效率。
3. 减少存储空间：相比于为每个列单独创建唯一索引，复合索引可以更有效地利用存储空间。

类型

复合唯一索引只有一种类型，即包含多个列的唯一索引。

应用场景

1. 用户表：在用户表中，通常会有用户名和邮箱两个字段，为了确保每个用户的用户名和邮箱组合是唯一的，可以创建一个复合唯一索引。
2. 订单表：在订单表中，订单号和客户ID的组合通常是唯一的，可以创建一个复合唯一索引来确保这一点。

示例代码

假设我们有一个用户表 users，包含以下字段：

• id (主键)
• username
• email

我们可以创建一个复合唯一索引，确保 username 和 email 的组合是唯一的：

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE INDEX idx_unique_username_email (username, email)
);

遇到的问题及解决方法

问题：为什么创建了复合唯一索引后，仍然可以插入重复的组合值？

原因：

1. 索引未正确创建：可能是由于SQL语句错误或权限问题导致索引未正确创建。
2. 数据类型不匹配：如果列的数据类型不匹配，MySQL可能无法正确识别唯一性。

解决方法：

1. 检查索引创建语句：确保SQL语句正确无误，并且具有创建索引的权限。
2. 检查数据类型：确保所有列的数据类型一致且匹配。

-- 检查索引是否存在
SHOW INDEX FROM users WHERE Key_name = 'idx_unique_username_email';

-- 如果索引不存在，重新创建
ALTER TABLE users ADD UNIQUE INDEX idx_unique_username_email (username, email);

问题：复合唯一索引对查询性能有何影响？

原因： 复合唯一索引可以提高涉及多个列的查询效率，但也可能增加插入和更新操作的开销。

解决方法：

1. 优化查询：确保查询条件中包含索引列，以提高查询效率。
2. 平衡读写性能：根据实际需求，权衡读取和写入性能，选择合适的索引策略。

-- 示例查询
SELECT * FROM users WHERE username = 'example' AND email = 'example@example.com';

参考链接

MySQL官方文档 - 索引

通过以上内容，您可以全面了解MySQL复合唯一索引的基础概念、优势、类型、应用场景以及常见问题及其解决方法。