MySQL 5.7大表关联标记字段超时，求可行解决方案

核心思路：先优化小表，再分批次处理大表，避免全表操作导致的资源耗尽 #

1. 预处理小表，减少关联数据量并添加索引
   小表存在重复邮箱，先去重生成临时表，再添加主键索引（索引构建速度远快于原表）：

-- 生成去重后的小表
CREATE TABLE `2ndTable_distinct` AS SELECT DISTINCT `email` FROM `2ndTable`;
-- 给去重表的email加主键索引，加速关联查询
ALTER TABLE `2ndTable_distinct` ADD PRIMARY KEY (`email`);

2. 分批次初始化大表标记字段
   直接全表更新会触发超时，分批次执行初始化：

-- 每次更新10万条，可根据服务器性能调整批次大小
UPDATE `mainTable` SET `in_2nd_table` = 'N' WHERE id BETWEEN 1 AND 100000;
-- 重复执行，替换BETWEEN的范围，直到全表所有记录的in_2nd_table都设为'N'

注：如果大表没有自增id，可以用email的范围拆分，比如WHERE email > 'a' AND email < 'm'

3. 分批次更新存在于小表的记录
   用JOIN替代IN子查询（性能更优），分批次更新：

-- 每次更新10万条匹配的记录
UPDATE `mainTable` m
JOIN `2ndTable_distinct` s ON m.`email` = s.`email`
SET m.`in_2nd_table` = 'Y'
LIMIT 100000;

重复执行该语句，直到返回的Rows matched为0，说明所有匹配记录已更新完成。

4. 可选：给大表添加在线索引（若后续还有类似操作）
   直接给大表加索引超时，可使用MySQL 5.7支持的在线DDL，减少锁表时间：

ALTER TABLE `mainTable` ADD INDEX idx_email (`email`) ALGORITHM=INPLACE LOCK=NONE;

5. 若需要生成新表（替代原CREATE TABLE语句）
   分批次插入数据，避免全表扫描超时：

-- 先创建与mainTable结构一致的空表
CREATE TABLE `test` LIKE `mainTable`;
-- 分批次插入不在小表的记录
INSERT INTO `test`
SELECT m.* FROM `mainTable` m
LEFT JOIN `2ndTable_distinct` s ON m.`email` = s.`email`
WHERE s.`email` IS NULL
LIMIT 100000;
-- 重复执行，直到插入行数为0

内容的提问来源于stack exchange，提问作者John Beasley