MySQL无约束大表ship_details去重遇锁超时及DISTINCT失效问题

操作前根因对齐 #

先明确之前所有操作失败的核心原因，避免重复踩坑：

• 直接自连接删除重复的操作：99万行的表自连接无索引时会触发全表扫描，生成的临时结果集过大，长事务持锁时间超过innodb_lock_wait_timeout阈值，哪怕指定单订单过滤也因为没有对应索引，扫描过程中持有大量行锁，直接触发锁等待超时。
• SELECT DISTINCT 去重失效：DISTINCT 是对查询返回的所有字段做整行比对，并不是按你预设的order_number、delivery_id、item_code、picked_quantity维度去重。你之前把item_id、outbound_id这类存在差异的字段也纳入了查询列，自然会把本该判重的条目当成不同结果返回。
• 小批量插入也超时：因为源表没有判重字段的索引，哪怕LIMIT 10的查询，也需要全表扫描99万行数据，扫描过程中会对遍历到的行加共享锁，和其他业务的写锁直接冲突，导致超时。

分步落地操作流程 #

1. 提前建好带唯一约束的干净目标表 #

按照确认的去重规则建表，提前把唯一索引建好，避免后续插入时动态加索引拖慢速度：

CREATE TABLE `clean_ship_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `order_number` varchar(150) DEFAULT NULL,
  `delivery_id` varchar(150) DEFAULT NULL,
  `transaction_type` varchar(150) DEFAULT NULL,
  `pick_date` varchar(150) DEFAULT NULL,
  `pn_note_number` varchar(150) DEFAULT NULL,
  `item_id` varchar(150) DEFAULT NULL,
  `item_code` varchar(150) DEFAULT NULL,
  `picked_quantity` varchar(150) DEFAULT NULL,
  `lot_number` varchar(150) DEFAULT NULL,
  `lot_expiry` varchar(150) DEFAULT NULL,
  `name` varchar(150) DEFAULT NULL,
  `delivered_date` varchar(150) DEFAULT NULL,
  `extra_attrib1` varchar(150) DEFAULT NULL,
  `extra_attrib2` varchar(150) DEFAULT NULL,
  `extra_attrib3` varchar(150) DEFAULT NULL,
  `extra_attrib4` varchar(150) DEFAULT NULL,
  `extra_attrib5` varchar(150) DEFAULT NULL,
  `extra_attrib6` varchar(150) DEFAULT NULL,
  `extra_attrib7` varchar(150) DEFAULT NULL,
  `extra_attrib8` varchar(150) DEFAULT NULL,
  `extra_attrib9` varchar(150) DEFAULT NULL,
  `extra_attrib10` varchar(150) DEFAULT NULL,
  `last_updated` varchar(100) DEFAULT NULL,
  `outbound_id` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  -- 按去重判定字段建唯一索引，自动跳过重复条目
  UNIQUE KEY `uk_judge` (`order_number`,`delivery_id`,`item_code`,`picked_quantity`)
) ENGINE=InnoDB CHARSET=latin1;

2. 源表加在线索引，从根源上降低锁范围 #

利用MySQL的在线DDL能力，给源表的判重字段加普通二级索引，加索引过程不阻塞正常业务读写：

ALTER TABLE `ship_details` 
ADD INDEX `idx_judge` (`order_number`,`delivery_id`,`item_code`,`picked_quantity`),
ALGORITHM=INPLACE, LOCK=NONE;

索引建完后，所有涉及判重的查询都会走索引定位，单条语句持锁时间会从秒级降到毫秒级，不会触发锁等待超时。

3. 主键分片小批量迁移，避免长事务 #

不要一次性全表查询插入，按源表主键id的区间分片，每次处理1-2万行的小范围数据，每个批次是独立小事务，持锁时间极短，不会冲突：

1. 先查源表的主键范围：

SELECT MIN(id), MAX(id) FROM `ship_details`;

2. 循环按id区间处理，用GROUP BY按判重维度去重，每个重复组保留id最小的有效条目（和你最初的删重逻辑保持一致）：

-- 示例：处理id在0-10000区间的数据，后续每次按步长滑动区间
INSERT IGNORE INTO `clean_ship_details` 
(id, order_number, delivery_id, transaction_type, pick_date, pn_note_number, item_id, item_code, picked_quantity, lot_number, lot_expiry, name, delivered_date, extra_attrib1, extra_attrib2, extra_attrib3, extra_attrib4, extra_attrib5, extra_attrib6, extra_attrib7, extra_attrib8, extra_attrib9, extra_attrib10, last_updated, outbound_id)
SELECT 
  MIN(id) AS id, 
  order_number, 
  delivery_id, 
  MAX(transaction_type) AS transaction_type, 
  MAX(pick_date) AS pick_date, 
  MAX(pn_note_number) AS pn_note_number, 
  MAX(item_id) AS item_id, 
  item_code, 
  picked_quantity, 
  MAX(lot_number) AS lot_number, 
  MAX(lot_expiry) AS lot_expiry, 
  MAX(name) AS name, 
  MAX(delivered_date) AS delivered_date, 
  MAX(extra_attrib1) AS extra_attrib1, 
  MAX(extra_attrib2) AS extra_attrib2, 
  MAX(extra_attrib3) AS extra_attrib3, 
  MAX(extra_attrib4) AS extra_attrib4, 
  MAX(extra_attrib5) AS extra_attrib5, 
  MAX(extra_attrib6) AS extra_attrib6, 
  MAX(extra_attrib7) AS extra_attrib7, 
  MAX(extra_attrib8) AS extra_attrib8, 
  MAX(extra_attrib9) AS extra_attrib9, 
  MAX(extra_attrib10) AS extra_attrib10, 
  MAX(last_updated) AS last_updated, 
  MAX(outbound_id) AS outbound_id
FROM `ship_details`
WHERE id BETWEEN 0 AND 10000
GROUP BY order_number, delivery_id, item_code, picked_quantity;

• 用INSERT IGNORE的原因：跨区间分组时可能出现边缘重复值，靠目标表的唯一索引直接跳过，不会报错中断。
• 每批执行完自动提交事务，持锁时间通常在10ms以内，哪怕有少量锁冲突，小事务也会快速重试，不会触发锁等待超时。

4. 数据校验与表切换 #

• 全部分批迁移完成后，对比两个表的行数：去重后的数据量级应该和之前预估的15万左右匹配。
• 校验完成后，将原表重命名做备份，把干净的clean_ship_details改回原表名完成切换。

避坑提醒 #

• 不要尝试全表自连接删重：哪怕加了索引，自连接删除的操作还是要持有大量行锁，长事务很容易打满回滚段，性能极差。
• 不要依赖SELECT DISTINCT做指定字段的去重：distinct是整行比对，和按指定字段去重的逻辑完全不同。
• 不要跑无索引的全量查询插入：哪怕加了limit限制，无索引的全表扫描过程会持有全表范围的共享锁，极易和业务操作冲突超时。

内容的提问来源于stack exchange，提问作者Subhendu Mahanta