mysql创建唯一索引与普通索引的区别与应用_网络技术

唯一索引会拒绝重复值插入，普通索引仅加速查询且允许重复；两者均支持多NULL值，联合唯一索引中全NULL行视为可重复；唯一索引写入时需查重，影响高并发性能，但等值查询可触发优化器的const/eq_ref优化。

这是最核心的区别。当你在字段上创建 UNIQUE INDEX，MySQL 会在写入时强制校验该字段（或字段组合）的值是否已存在；若存在，直接报错 ERROR

1062 (23000): Duplicate entry ... for key ...。而普通索引（INDEX）只加速查询，对重复值完全放行。

常见踩坑点：

建表时用 UNIQUE 约束比事后加 UNIQUE INDEX 更清晰，语义明确且自动绑定约束名
如果字段允许 NULL，唯一索引允许多个 NULL（MySQL 行为，符合 SQL 标准），但普通索引也一样 —— 这点常被误认为“唯一索引不支持 NULL”
联合唯一索引中，只要任意一列不同，就视为不重复；全为 NULL 的行仍算作可重复（因 NULL != NULL）

两者底层都使用 B+ 树，查找效率无本质差异。但唯一索引在写入时多了一步“查重”动作：每次 INSERT 或 UPDATE 涉及索引列，MySQL 必须先定位到对应叶子节点，确认无冲突后才写入。这在高并发写入场景下可能成为瓶颈。

实操建议：

不要为了“以后可能去重”提前加 UNIQUE，除非业务逻辑真正需要强唯一性保障
对高频写入、低频查询的字段（如日志表的 trace_id），优先用普通索引；若需去重，改由应用层或异步任务处理
如果已有普通索引，想升级为唯一索引，先用 SELECT COUNT(*) - COUNT(DISTINCT col) 检查是否存在隐性重复，否则 ALTER TABLE ... ADD UNIQUE INDEX 会失败

当查询条件命中唯一索引且是等值匹配（如 WHERE user_id = 123），MySQL 优化器知道最多返回一行，因此可能跳过某些检查步骤，甚至在 EXPLAIN 中显示 type: const 或 type: eq_ref，而普通索引通常只能到 type: ref。

这意味着：

主键索引（本质是聚簇唯一索引）和显式 UNIQUE INDEX 都能触发这类优化
如果查询用的是 IN 或范围条件（如 WHERE code >= 'A' AND code ），即使走唯一索引，也不会有 const 优化
复合唯一索引只有满足最左前缀且等值匹配时才生效，例如索引 (a, b)，WHERE a = 1 AND b = 2 可用，但 WHERE b = 2 就无法利用

这是上线前最常见的落地动作。不能直接加唯一索引，必须先清理脏数据。

DELETE t1 FROM users t1
INNER JOIN users t2 
WHERE 
    t1.id > t2.id 
    AND t1.email = t2.email;

然后验证：

SELECT email, COUNT(*) c FROM users GROUP BY email HAVING c > 1;

确认无结果后，再执行：

ALTER TABLE users ADD UNIQUE INDEX uk_email (email);

注意：DELETE ... JOIN 写法比子查询更安全（避免 MySQL 的 “You can't specify target table for update in FROM clause” 错误），且性能更好。

真正麻烦的不是语法，而是如何定义“重复”——比如邮箱大小写是否敏感、空格是否忽略、是否要保留最新/最早的一条。这些逻辑必须在 DELETE 前明确，否则补救成本远高于预防。