Mysql如何删除重复数据

项目背景：

在RocketMQ的消费者服务中，发现了重复消费的情况，为了处理，需先将消费者停止消费，去重消息ID重复的数据，再给消息ID设置唯一索引。

方法一：使用临时表

创建一个临时表，用于存储要保留的数据。

1	CREATE TABLE consumer_crm_record_tmp LIKE consumer_crm_record;

将要保留的数据存储到临时表中

INSERT INTO consumer_crm_record_tmp
SELECT MAX(id) AS id,message_id,message,response
FROM consumer_crm_record
GROUP BY message_id;

删除原表中的数据

1	delete from consumer_crm_record

将临时表中的数据加入到原表中

1
2
3

INSERT INTO consumer_crm_record
SELECT  id,message_id,message,response
FROM consumer_crm_record_tmp;

删除临时表

1	DROP TABLE consumer_crm_record_tmp;

创建临时表的优点：

简单直观：通过创建临时表，您可以明确地将要删除的数据和要保留的数据分开存储，易于理解和管理。
可读性好：临时表的使用可以使查询语句更易读，特别是对于复杂的逻辑操作。
可以灵活处理：临时表可以在多个查询之间使用，可以执行额外的操作，例如插入、更新或查询临时表的数据。

创建临时表的缺点：

需要额外的存储空间：创建临时表需要占用额外的存储空间，特别是在处理大量数据时可能会对磁盘空间造成一定的压力。
需要额外的操作及权限：创建、插入和删除临时表需要执行额外的数据库操作，增加了一定的复杂性。

方法二：使用left join 删除（强烈推荐）

DELETE t1 
FROM
        consumer_crm_record t1
        LEFT JOIN ( SELECT MAX( id ) AS max_id, message_id FROM consumer_crm_record GROUP BY message_id ) t2 ON t1.message_id = t2.message_id 
        AND t1.id = t2.max_id 
WHERE
        t2.max_id IS NULL;

这个查询将根据message_id进行分组，并保留每个组中的最大id。然后，它使用左连接将原始表与这些最大id进行比较。如果连接失败（即max_id为NULL），则表示该行不是具有最大id的行，因此将被删除。

LEFT JOIN的优点：

可以利用索引：LEFT JOIN 可以利用索引来加速查询，特别是在连接字段上存在索引的情况下。
更高的灵活性：LEFT JOIN 可以与其他表达式和条件组合使用，使得查询可以更加灵活。

LEFT JOIN的缺点：

性能可能受限：当处理大量数据时，LEFT JOIN 可能会导致较慢的查询速度，尤其是在连接字段没有索引或使用了复杂的连接条件时。
查询复杂度高：使用 LEFT JOIN 进行连接查询时，需要编写较为复杂的查询语句，对于新手而言可能会比较困难。

方法三：使用not in 删除

DELETE 
FROM
 consumer_crm_record t1 
WHERE
 id NOT IN ( SELECT max_id FROM ( SELECT MAX( id ) AS max_id FROM consumer_crm_record GROUP BY message_id) tmp );

NOT IN的优点：

简单易用：NOT IN 是一种直观简单的方式来筛选出不在指定列表中的数据。
可读性好：NOT IN 子句的语义明确，易于理解和维护。

NOT IN的缺点：

性能可能较低：NOT IN 子查询对于大型数据集可能会导致较慢的查询速度，尤其是在子查询中返回大量结果时。
NULL 值处理：NOT IN 子句在处理 NULL 值时需要特别注意，因为 NULL 的处理可能会导致意外的结果。

方法四：使用窗口函数（MySQL 8.0以上支持）

DELETE
FROM consumer_crm_record
WHERE id in (
    SELECT id
    FROM(
        SELECT *
        FROM (
            SELECT ROW_NUMBER() OVER w AS row_num,id
            FROM consumer_crm_record  where message_id is not null
            WINDOW w AS (PARTITION BY message_id ORDER BY id)
        )t
        WHERE row_num >1
    )e
)

窗口函数的优点：

简化查询逻辑：使用窗口函数可以在单个查询中完成去重操作，而不需要使用其他复杂的子查询或连接操作。这简化了查询逻辑，使查询更易读和维护。
高效性能：窗口函数在处理大型数据集时通常比传统的子查询或连接操作更高效。窗口函数可以在单个扫描中计算结果，而不需要多次访问数据表。
保留原始数据：使用窗口函数进行去重操作时，您可以保留原始数据表中的所有列，并在结果中添加去重的标识。这使得您可以查看去重结果与原始数据之间的关系，并进行进一步的分析和处理。

窗口函数的缺点：

学习曲线：窗口函数是一种高级的SQL功能，相对于传统的查询语法来说，使用窗口函数可能需要一些学习和理解。如果您对窗口函数不熟悉，可能需要花费一些时间来学习和了解其语法和用法。
版本兼容性：窗口函数在MySQL中的支持是在版本8.0之后引入的。如果您使用的是较旧的MySQL版本，那么窗口函数可能不可用，您将无法使用窗口函数进行去重操作。
内存消耗：窗口函数可能需要在内存中保存一些额外的中间结果，尤其是在处理大型数据集时。这可能会导致一些额外的内存消耗。因此，在使用窗口函数进行去重操作时，需要注意内存使用情况，并确保系统具有足够的内存资源。

创建唯一索引

1	CREATE UNIQUE INDEX idx_message_id on consumer_crm_record(message_id);

在百万级表中执行中发现报错：1206- the total number of locks exceeds the lock table size

innodb_buffer_pool mysql缓冲池太小默认为16M

1	show variables like '%innodb_buffer_pool_size%'

修改缓冲池大小为2G

1	SET GLOBAL innodb_buffer_pool_size=2147483648