MySQL查询仅admin类型用户时遇连接丢失问题求助（表超500万行）

原查询因嵌套子查询导致多次全表扫描，在500万行的大表上执行效率极低，最终引发连接超时。以下是针对性优化方案：

1. 添加必要索引（核心前提） #

先给查询涉及的字段创建索引，避免全表扫描拖慢速度：

-- 快速定位admin/normaluser的类型ID
CREATE INDEX idx_user_type_type ON users.user_type(user_type);

-- 快速筛选指定类型的用户
CREATE INDEX idx_user_type_id ON users.user(user_type_id);

-- 加速邮箱字段的关联查询
CREATE INDEX idx_user_email ON users.user(email_add);

2. 优化查询语句 #

方案一：用NOT EXISTS替代NOT IN（性能更优） #

NOT EXISTS找到匹配记录后会立即停止扫描，且不会因子查询返回NULL导致结果异常：

SELECT u.id
FROM users.user u
WHERE u.user_type_id = (SELECT id FROM users.user_type WHERE user_type = 'admin')
AND NOT EXISTS (
    SELECT 1
    FROM users.user u_normal
    WHERE u_normal.email_add = u.email_add
    AND u_normal.user_type_id = (SELECT id FROM users.user_type WHERE user_type = 'normaluser')
);

方案二：预存类型ID，减少重复查询 #

先把admin和normaluser的类型ID存入变量，避免多次查询user_type表：

-- 预存类型ID
SET @admin_type_id = (SELECT id FROM users.user_type WHERE user_type = 'admin');
SET @normal_type_id = (SELECT id FROM users.user_type WHERE user_type = 'normaluser');

-- 执行查询
SELECT u.id
FROM users.user u
WHERE u.user_type_id = @admin_type_id
AND NOT EXISTS (
    SELECT 1
    FROM users.user u_normal
    WHERE u_normal.email_add = u.email_add
    AND u_normal.user_type_id = @normal_type_id
);

方案三：LEFT JOIN筛选无匹配记录 #

通过左连接后筛选NULL值，逻辑直观且性能稳定：

SELECT u.id
FROM users.user u
INNER JOIN users.user_type ut_admin 
    ON u.user_type_id = ut_admin.id 
    AND ut_admin.user_type = 'admin'
LEFT JOIN users.user u_normal 
    ON u.email_add = u_normal.email_add 
    AND u_normal.user_type_id = (SELECT id FROM users.user_type WHERE user_type = 'normaluser')
WHERE u_normal.id IS NULL;

3. 临时调整MySQL配置（辅助手段） #

若优化查询后仍有超时问题，可临时调整超时参数（需管理员权限）：

-- 延长连接超时时间（单位：秒）
SET GLOBAL wait_timeout = 3600;
SET GLOBAL interactive_timeout = 3600;

-- 增大允许的数据包大小（单位：字节）
SET GLOBAL max_allowed_packet = 1073741824;

注意：配置调整仅作临时补救，核心优化方向仍是索引和查询逻辑优化。

内容的提问来源于stack exchange，提问作者Rajendra Gupta