问题分析

在 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. 读写分离：对唯一性要求极高但读多写少的场景，可将唯一性检查放在从库进行。

根据你的具体业务场景选择最合适的方案，通常复合索引能在保留约束的同时提供良好的性能。