数据库优化技巧：MySQL 重复数据查询与删除（仅保留一条）的性能优化策略

目录

一、查询重复数据

二、删除重复数据

方法 1：创建临时表，操作完成后再删除临时表（安全可靠，适合大表）

        步骤 1：创建临时表存储需删除的 ID

        步骤 2：根据临时表删除数据

方法 2：使用子查询嵌套删除重复记录（简洁高效，适合小表）

一、查询重复数据

场景：按单个字段或多个字段分组，查询重复组中的任意一条记录。

方法：使用 GROUP BY + MIN()/MAX()

假设表中有主键（如 id），按 name字段查询重复数据，并保留每组中 id 最小的记录：

select 
  MIN(id) AS id,  -- 保留最小/最大的主键值
  name
from sys_user
GROUP BY name         -- 按重复字段分组
HAVING COUNT(*) > 1;  -- 筛选出重复组（计数>1）

注意：

1. 聚合函数选择：
   • 使用 MIN(id) 保留最早的记录
   • 使用 MAX(id) 保留最新的记录
2. SELECT 字段限制：
   • 非聚合字段（如name）必须出现在GROUP BY中
   • MySQL 5.7+ 默认启用 ONLY_FULL_GROUP_BY 模式，需严格遵守此规则

二、删除重复数据

方法 1：创建临时表，操作完成后再删除临时表（安全可靠，适合大表）

        步骤 1：创建临时表存储需删除的 ID

-- 1、删除临时表（如果存在）
DROP TABLE IF EXISTS temp_sys_user;

-- 2、创建临时表，存储需要删除的重复记录ID
CREATE TABLE temp_sys_user AS (
    SELECT
        a.id
    FROM
        sys_user a
    WHERE
        (
            -- 指定需要去重的字段，可根据实际情况添加更多字段
            a.name
        ) IN (
            SELECT
                c.name
            FROM
                sys_user c
            GROUP BY
                c.name
            HAVING
                COUNT(*) > 1
        )
    AND a.id NOT IN (
        SELECT
            MIN(b.id)
        FROM
            sys_user b
        GROUP BY
            b.username
        HAVING
            COUNT(*) > 1
    )
);

        步骤 2：根据临时表删除数据

-- 1、根据临时表删除sys_user表中的重复数据
DELETE FROM sys_user WHERE id IN (SELECT id FROM temp_sys_user );

-- 2、删除临时表，释放资源
DROP TABLE IF EXISTS temp_sys_user ;

好处：

• 避免直接操作原表，减少死锁风险
• 支持复杂筛选条件
• 适合处理百万级数据

方法 2：使用子查询嵌套删除重复记录（简洁高效，适合小表）

DELETE FROM sys_user WHERE id IN (
    SELECT id FROM (
        SELECT id
        FROM sys_user a
        WHERE (
            -- 指定需要去重的字段，保持与方法1一致
            a.name
        ) IN (
            SELECT name
            FROM sys_user
            GROUP BY name
            HAVING COUNT(*) > 1
        )
        AND a.id NOT IN (
            SELECT MIN(id)
            FROM sys_user
            GROUP BY name
            HAVING COUNT(*) > 1
        )
    ) AS temp
);

注意：备份数据！备份数据！备份数据！

（重要的事情说三遍，防止操作失误导致数据丢失）