MySQL优化 数据库索引 MySQL去除“关联表”重复数据，以建立联合唯一索引

MySQL优化实战：告别重复数据，轻松建立联合唯一索引 🚀

场景引入：恼人的重复数据问题

上周五晚上11点，我正美滋滋地准备下班，突然收到报警邮件📧——我们的用户积分系统又出问题了！检查后发现，用户积分记录表里居然出现了大量重复数据，导致用户积分被重复计算，财务小姐姐都快疯了💢。

这种情况已经不是第一次了，每次都要手动清理，既麻烦又容易出错，这次我决定彻底解决这个问题——通过建立联合唯一索引来防止重复数据产生！

问题分析：为什么会有重复数据？

在我们的系统中，user_points表记录了用户积分变动情况,结构大致如下：

CREATE TABLE `user_points` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `point_type` varchar(50) NOT NULL,
  `points` int(11) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

问题出在：同一个用户、同一种积分类型(point_type)在短时间内被重复插入，导致用户积分被多次增加。😫

解决方案：联合唯一索引来拯救

第一步：清理现有重复数据 🧹

在建立唯一索引前,必须先清理现有的重复数据：

-- 找出重复数据
SELECT user_id, point_type, COUNT(*) as cnt 
FROM user_points 
GROUP BY user_id, point_type 
HAVING cnt > 1;
-- 使用临时表法删除重复数据（保留最早的一条）
CREATE TEMPORARY TABLE temp_keep AS
SELECT MIN(id) as id 
FROM user_points 
GROUP BY user_id, point_type;
DELETE FROM user_points 
WHERE id NOT IN (SELECT id FROM temp_keep);
DROP TEMPORARY TABLE temp_keep;

💡 小贴士：在生产环境执行前,一定要先备份数据！

第二步：建立联合唯一索引 🔐

现在可以安全地添加唯一索引了：

ALTER TABLE user_points 
ADD UNIQUE INDEX `idx_user_point` (`user_id`, `point_type`);

这样就能确保同一用户、同一种积分类型的记录只会存在一条，从数据库层面杜绝重复！🎉

进阶技巧：处理特殊情况

情况1：需要保留历史记录但防止短时间内重复

如果业务上需要保留历史记录，只是要防止短时间内重复插入,可以这样优化：

ALTER TABLE user_points 
ADD UNIQUE INDEX `idx_user_point_time` (`user_id`, `point_type`, DATE(created_at));

这样同一天内同一用户的同类型积分就只能有一条记录。

情况2：使用INSERT IGNORE优雅处理重复

应用代码中可以这样处理：

INSERT IGNORE INTO user_points (user_id, point_type, points) 
VALUES (123, 'daily_login', 10);

当遇到重复时,MySQL会静默跳过而不是报错。

情况3：使用ON DUPLICATE KEY UPDATE

如果需要更新而不是跳过：

INSERT INTO user_points (user_id, point_type, points) 
VALUES (123, 'daily_login', 10)
ON DUPLICATE KEY UPDATE points = points + VALUES(points);

性能考量 ⚡

添加唯一索引后需要注意：

1. 写入性能：唯一索引会稍微降低INSERT速度，因为MySQL需要检查唯一性约束
2. 内存使用：唯一索引会占用更多内存
3. 磁盘空间：每个唯一索引都需要额外的存储空间

但在大多数情况下,数据一致性的收益远大于这些开销！

避坑指南 🕳️

1. 大表加索引要小心：如果表很大，直接加索引可能导致长时间锁表，可以考虑使用pt-online-schema-change工具
2. NULL值处理：唯一索引视NULL为特殊值，允许多个NULL值存在
3. 字符集和排序规则：确保联合索引各列的字符集和排序规则一致，否则可能导致意外行为

✨

通过合理使用联合唯一索引，我们不仅解决了重复数据问题，还提升了数据质量，现在财务小姐姐终于可以睡个好觉了，我也能准时下班啦！😄

预防胜于治疗，在数据库设计阶段就考虑好唯一性约束,能省去后期很多麻烦！