MySQL удаляет дубликаты из большой базы данных быстро

Ответ 1

Я считаю, что это будет сделано, используя дубликат ключа + ifnull():

create table tmp like yourtable;

alter table tmp add unique (text1, text2);

insert into tmp select * from yourtable 
    on duplicate key update text3=ifnull(text3, values(text3));

rename table yourtable to deleteme, tmp to yourtable;

drop table deleteme;

Должно быть намного быстрее, чем все, что требует от группы или отдельного или подзапроса или даже порядка. Это даже не требует файловой системы, которая будет убивать производительность на большой временной таблице. По-прежнему потребуется полное сканирование по исходной таблице, но этого не избежать.

Ответ 2

Нашел этот простой 1-строчный код, чтобы сделать то, что мне нужно:

ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);

Взято из: http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

Ответ 3

DELETE FROM dups
WHERE id NOT IN(
    SELECT id FROM (
        SELECT DISTINCT id, text1, text2
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC
    ) as tmp
)

Это запрашивает все записи, группы по полям различий и порядкам по идентификатору (означает, что мы выбираем первую не нулевую запись в формате text3). Затем мы выбираем id из этого результата (это хорошие идентификаторы... они не будут удалены) и удалите все идентификаторы, которые НЕ являются этими.

Любой такой запрос, влияющий на всю таблицу, будет медленным. Вам просто нужно запустить его и позволить ему развертываться, чтобы вы могли предотвратить его в будущем.

После того, как вы сделали это "исправление", я применил бы UNIQUE INDEX (text1, text2) к этой таблице. Чтобы предотвратить возможность дублирования в будущем.

Если вы хотите перейти на "создать новую таблицу и заменить старый". Вы можете использовать сам внутренний оператор select, чтобы создать инструкцию insert.

Спецификация MySQL (предполагается, что новая таблица называется my_tbl2 и имеет точно такую ​​же структуру):

INSERT INTO my_tbl2
    SELECT DISTINCT id, text1, text2, text3
            FROM dups
        GROUP BY text1, text2
        ORDER BY text3 DESC

Подробнее см. MySQL INSERT... SELECT.

Ответ 4

удалить дубликаты без удаления внешних ключей

create table tmp like mytable;
ALTER TABLE tmp ADD UNIQUE INDEX(text1, text2, text3, text4, text5, text6);
insert IGNORE into tmp select * from mytable;
delete from mytable where id not in ( select id from tmp);

Ответ 5

Если вы можете создать новую таблицу, сделайте это с помощью уникального ключа в полях text1 + text2. Затем вставьте в таблицу игнорирование ошибок (используя синтаксис INSERT IGNORE):

select * from my_tbl order by text3 desc

• Я думаю, что порядок с помощью text3 desc поместит NULLs последним, но дважды проверьте это.

Индексы во всех этих столбцах могут многое помочь, но их создание теперь может быть довольно медленным.

Ответ 6

Для больших таблиц с несколькими дубликатами вам может потребоваться избежать копирования всей таблицы в другое место. Один из способов - создать временную таблицу, содержащую строки, которые вы хотите сохранить (для каждого ключа с дубликатами), а затем удалить дубликаты из исходной таблицы.

Приведен пример здесь.

Ответ 7

У меня нет большого опыта работы с MySQL. Если у него есть аналитические функции, попробуйте:

delete from my_tbl
 where id in (
     select id 
       from (select id, row_number()
                            over (partition by text1, text2 order by text3 desc) as rn
               from my_tbl
               /* optional: where text1 like 'a%'  */
             ) as t2
       where rn > 1
     )

необязательное предложение where позволяет вам запускать его несколько раз, по одному для каждой буквы и т.д. Создайте индекс в text1?

Прежде чем запускать это, убедитесь, что "text desc" будет сортировать нули, последние в MySQL.

Ответ 8

Я знаю, что это старый поток, но у меня есть несколько грязный метод, который намного быстрее и настраивается, с точки зрения скорости я бы сказал 10 секунд вместо 100 секунд (10: 1).

Мой метод требует всего того грязного материала, которого вы пытались избежать:

• Группировать по (и после)
• группа concat с ORDER BY
• 2 временных таблицы
• использование файлов на диске!
• somehow (php?) удаление файла после

Но когда вы говорите о МИЛЛИОНАХ (или в моем случае Десяти миллионов), это того стоит.

в любом случае его не так много, потому что комментарий находится на португальском языке, но вот мой пример:

EDIT: если я получу комментарии, я объясню далее, как это работает:)

START TRANSACTION;

DROP temporary table if exists to_delete;

CREATE temporary table to_delete as (
    SELECT
        -- escolhe todos os IDs duplicados menos os que ficam na BD
        -- A ordem de escolha dos IDs é dada por "ORDER BY campo_ordenacao DESC" em que o primeiro é o que fica
        right(
            group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','),
            length(group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ',')) 
                - locate(",",group_concat(id ORDER BY campos_ordenacao DESC SEPARATOR ','))
        ) as ids,

        count(*) as c

    -- Tabela a eliminar duplicados
    FROM teste_dup

    -- campos a usar para identificar  duplicados
    group by test_campo1, test_campo2, teste_campoN
    having count(*) > 1 -- é duplicado
);

-- aumenta o limite desta variável de sistema para o máx 
SET SESSION group_concat_max_len=4294967295;

-- envia os ids todos a eliminar para um ficheiro
select group_concat(ids SEPARATOR ',') from to_delete INTO OUTFILE 'sql.dat';

DROP temporary table if exists del3;
create temporary table del3 as (select CAST(1 as signed) as ix LIMIT 0);

-- insere os ids a eliminar numa tabela temporaria a partir do ficheiro
load data infile 'sql.dat' INTO TABLE del3
LINES TERMINATED BY ',';

alter table del3 add index(ix);

-- elimina os ids seleccionados
DELETE teste_dup -- tabela 
from teste_dup -- tabela

join del3 on id=ix;

COMMIT;

Ответ 9

вы можете удалить все повторяющиеся записи, используя этот простой запрос. который выберет все дубликаты записей и удалит их.

 DELETE i1 
FROM TABLE i1
LEFT JOIN TABLE i2
  ON i1.id = i2.id
 AND i1.colo = i2.customer_invoice_id
 AND i1.id < i2.id
WHERE i2.customer_invoice_id IS NOT NULL