MySQL은 큰 데이터베이스에서 중복을 빠르게 제거

MySQL은 큰 데이터베이스에서 중복을 빠르게 제거

---

중복으로 엉망이 된 큰 (> Mil rows) MySQL 데이터베이스가 있습니다. 나는 전체 db의 1/4에서 1/2로 채워질 수 있다고 생각합니다. 빨리 제거해야합니다 (즉, 쿼리 실행 시간). 모양은 다음과 같습니다.
id (index) | text1 | text2 | text3
text1 및 text2 조합은 고유해야하며 중복이있는 경우 text3 NOT NULL이있는 하나의 조합 만 남아 있어야합니다. 예:

1 | abc | def | NULL  
2 | abc | def | ghi  
3 | abc | def | jkl  
4 | aaa | bbb | NULL  
5 | aaa | bbb | NULL  

...된다 :

1 | abc | def | ghi   #(doesn't realy matter id:2 or id:3 survives)   
2 | aaa | bbb | NULL  #(if there's no NOT NULL text3, NULL will do)

새로운 ID는 무엇이든 상관 없으며 이전 테이블 ID에 의존하지 않습니다.
나는 다음과 같은 것을 시도했다.

CREATE TABLE tmp SELECT text1, text2, text3
FROM my_tbl;
GROUP BY text1, text2;
DROP TABLE my_tbl;
ALTER TABLE tmp RENAME TO my_tbl;

또는 SELECT DISTINCT 및 기타 변형.
소규모 데이터베이스에서 작업하는 동안 내 쿼리 실행 시간은 엄청납니다 (실제로는 끝까지 도달하지 못했습니다.> 20 분).

더 빠른 방법이 있습니까? 이 문제를 해결하도록 도와주세요.

---

중복 키 + 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;

그룹 별 또는 개별 쿼리 또는 하위 쿼리 또는 주문 기준이 필요한 모든 것보다 훨씬 빠릅니다. 이것은 대용량 임시 테이블에서 성능을 저하시키는 파일 정렬을 요구하지도 않습니다. 원본 테이블에 대한 전체 스캔이 여전히 필요하지만이를 피할 수는 없습니다.

---

필요한 작업을 정확히 수행하기 위해이 간단한 한 줄 코드를 찾았습니다.

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

출처 : http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

---

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
)

이것은 구별 필드로 모든 레코드, 그룹을 쿼리하고 ID로 순서를 지정합니다 (널이 아닌 첫 번째 텍스트 3 레코드를 선택 함을 의미합니다). 그런 다음 결과에서 ID를 선택하고 (이것들은 좋은 ID입니다 ... 삭제되지 않음) 해당 ID가 아닌 모든 ID를 삭제합니다.

전체 테이블에 영향을 미치는 이와 같은 쿼리는 느려집니다. 이를 실행하고 롤아웃하면 나중에 방지 할 수 있습니다.

이 "수정"을 수행 한 후 해당 테이블에 UNIQUE INDEX (text1, text2)를 적용합니다. 향후 중복 가능성을 방지하기 위해.

"새 테이블 생성 및 이전 테이블 교체"경로로 이동하려는 경우. 내부 select 문을 사용하여 삽입 문을 만들 수 있습니다.

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 를 참조하십시오.

---

외래 키를 제거하지 않고 중복 제거

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);

---

새 테이블을 만들 수있는 경우 text1 + text2 필드의 고유 키를 사용하여 만들 수 있습니다. 그런 다음 INSERT IGNORE 구문을 사용하여 오류를 무시하고 테이블에 삽입합니다.

select * from my_tbl order by text3 desc

• text3 desc의 주문이 NULL을 마지막에 넣을 것이라고 생각하지만 다시 확인하십시오.

이러한 모든 열의 인덱스는 많은 도움이 될 수 있지만 지금 만드는 작업은 상당히 느릴 수 있습니다.

---

중복이 거의없는 큰 테이블의 경우 전체 테이블을 다른 위치로 복사하지 않는 것이 좋습니다. 한 가지 방법은 (중복이있는 각 키에 대해) 보관하려는 행을 포함하는 임시 테이블을 만든 다음 원본 테이블에서 중복을 삭제하는 것입니다.

여기에 예가 있습니다 .

---

MySQL에 대한 경험이 많지 않습니다. 분석 함수가 있으면 다음을 시도하십시오.

my_tbl에서 삭제
 어디 이드 (
     아이디 선택 
       from (select id, row_number ()
                            over (text1로 분할, text3 desc로 text2 순서) rn
               my_tbl에서
               / * 선택 사항 : 'a %'와 같은 text1 * /
             ) t2로
       여기서 rn> 1
     )

선택적 where 절은 각 문자에 대해 하나씩 여러 번 실행해야 함을 의미합니다. text1에 인덱스를 만드시겠습니까?

이것을 실행하기 전에 "text desc"가 MySQL에서 마지막으로 null을 정렬하는지 확인하십시오.

---

이것이 오래된 스레드라는 것을 알고 있지만 속도 측면에서 100 초 (10 : 1) 대신 10 초라고 말하고 싶습니다. 훨씬 빠르고 사용자 정의 가 가능한 다소 지저분한 방법이 있습니다.

내 방법에는 피하려고했던 모든 지저분한 물건 이 필요합니다 .

• 그룹화 (및 보유)
• ORDER BY와 그룹 연결
• 임시 테이블 2 개
• 디스크의 파일 사용!
• 어떻게 든 (php?) 파일 삭제 후

그러나 당신이 수백만 (또는 제 경우에는 수천만)에 대해 이야기 할 때 그만한 가치가 있습니다.

어쨌든 댓글이 포르투갈어로되어 있기 때문에 그다지 많지는 않지만 여기에 내 샘플이 있습니다.

EDIT: if I get comments I'll explain further how it works :)

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;

---

you can remove all the duplicate entries by using this simple query. that will select all the duplicate records and remove them.

 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

참고URL : https://stackoverflow.com/questions/1651999/mysql-remove-duplicates-from-big-database-quick