mysql一张表，其中一个字段设置了唯一索引，又设置了普通索引，查询的时候很慢，没有走普通索引，是const
问题分析
在 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) -- 普通复合索引
);
优化步骤：
-
删除原唯一索引：
sql
ALTER TABLE users DROP INDEX unique_id; -
创建包含唯一性约束的复合索引：
sql
CREATE UNIQUE INDEX idx_unique_id_name ON users (unique_id, name); -
查询优化：
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');
优势：
- 哈希索引更小、查询更快
- 保留了唯一性约束
- 原始字段可自由使用普通索引
注意事项：
- 哈希碰撞概率（虽然极低，但需处理）
- 额外存储开销
- 插入 / 更新性能略有下降
最佳实践建议
-
优先选择复合索引：如果查询模式固定，复合索引是最简单有效的方案。
-
监控索引使用情况：定期使用
EXPLAIN分析关键查询，确保优化器选择预期的索引。 -
分区表考虑：如果表数据量极大，可考虑按
unique_id进行哈希分区，分散索引负载。 -
读写分离：对唯一性要求极高但读多写少的场景，可将唯一性检查放在从库进行。
根据你的具体业务场景选择最合适的方案，通常复合索引能在保留约束的同时提供良好的性能。