[SQL] 큰 데이터베이스의 빠른에서 중복을 제거 MYSQL

큰 데이터베이스의 빠른에서 중복을 제거 MYSQL

나는 큰 (> 밀 행) 중복에 의해 엉망 MySQL 데이터베이스를 가지고있다. 나는 그것이 1/4에서 그들과 함께 가득 전체 DB의 1/2이 될 수 있다고 생각. 나는 빠른 (I 평균 쿼리 실행 시간) 그들을 제거 할 필요가있다. 여기 보이는 방법은 다음과 같습니다 ID (인덱스) | 텍스트 1 | 텍스트 2 | 텍스트 3 텍스트 1 및 텍스트 2의 조합은 고유해야합니다 어떤 중복이있는 경우, 텍스트 3 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는 그들이 이전 테이블 식별자에 의존하지 않는, 아무것도 할 수 콜드. 나는 일을 같이 시도했다 :

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;

또는 DISTINCT과 다른 변화를 선택합니다. 그들은 작은 데이터베이스에 작업하는 동안, 내 쿼리 실행 시간 (실제로 끝에 없었어;> 20 분) 단지 거대하다

그렇게 할 수있는 빠른 방법이 있나요? 내가이 문제를 해결 도와주세요.

해결법

1. ==============================

   1.나는 이것이) (중복 키 +의 IFNULL에 사용하여, 그것을 할 것입니다 생각 :

   나는 이것이) (중복 키 +의 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;
   

   훨씬 빠른 별개의 또는 하위 쿼리, 또는로도 주문 또는에 의해 그룹이 필요합니다 무엇보다이어야한다. 이 경우에도 큰 임시 테이블에 성능을 죽일려고하는 filesort를 필요로하지 않습니다. 여전히 원래 테이블에 전체 검사를 필요로하지만, 어떤 것을 피할 수 없다 할 것이다.

2. ==============================

   2.이 간단한 한 줄의 코드는 내가 필요 정확히 무엇을 찾았습니다

   이 간단한 한 줄의 코드는 내가 필요 정확히 무엇을 찾았습니다

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

   에서 발췌 : http://mediakey.dk/~cc/mysql-remove-duplicate-entries/

3. ==============================

   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
   )
   

   이것은 (우리가 첫 번째 null이 아닌 텍스트 3 기록을 선택 수단) 구분 필드와 주문 ID로 모든 기록, 그룹을 쿼리합니다. 그리고 우리는 그 결과 (이 그들이이 삭제 실 거예요 ... 좋은 식별자입니다)에서의 id를 선택하고 그되지 않은 모든 ID를 삭제합니다.

   전체 테이블에 영향을 미치는이 같은 모든 쿼리 속도가 느려질 수 있습니다. 당신은 그것을 실행하고 나중에 그것을 방지 할 수 있도록이 선보일 수 있도록해야합니다.

   이 "수정"을 수행 한 후 그 테이블에 UNIQUE INDEX (텍스트 1, 텍스트 2)를 적용한다. 미래의 중복의있는 posibility을 방지합니다.

   당신이 가고 싶은 경우 "새 테이블을 생성하고 이전 대체"경로를. 당신은 당신의 INSERT 문을 생성하기 위해 매우 내부 SELECT 문을 사용할 수 있습니다.

   MySQL의 특정은 (새 테이블이 my_tbl2이라는 가정 정확히 같은 구조를 가지고있다)

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

   자세한 내용은 SELECT ... MySQL의 INSERT를 참조하십시오.

4. ==============================

   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. ==============================

   5.새 테이블을 만들 수 있다면, 텍스트 1 + 텍스트 2 필드에 고유 키와 그렇게. 그런 다음 오류를 무시합니다 (INSERT 구문을 무시 사용) 테이블에 삽입 :

   새 테이블을 만들 수 있다면, 텍스트 1 + 텍스트 2 필드에 고유 키와 그렇게. 그런 다음 오류를 무시합니다 (INSERT 구문을 무시 사용) 테이블에 삽입 :

   select * from my_tbl order by text3 desc
   

   모든 컬럼의 인덱스가 많은 도움이 될 수 있지만 지금을 만드는 것은 매우 느려질 수 있습니다.

6. ==============================

   6.몇 중복 대형 테이블의 경우, 당신은 다른 곳으로 전체 테이블을 복사 방지 할 수 있습니다. 한 가지 방법은 (중복 각 키) 유지하려는 행을 들고 임시 테이블을 만든 다음 원래 테이블에서 중복 항목을 삭제하는 것입니다.

   몇 중복 대형 테이블의 경우, 당신은 다른 곳으로 전체 테이블을 복사 방지 할 수 있습니다. 한 가지 방법은 (중복 각 키) 유지하려는 행을 들고 임시 테이블을 만든 다음 원래 테이블에서 중복 항목을 삭제하는 것입니다.

   예는 여기에 제공됩니다.

7. ==============================

   7.나는 MySQL의에 많은 경험을 가지고 있지 않습니다. 이 경우 분석 기능이 시도 :

   나는 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
        )
   

   절은 당신이 등, 각 문자 하나, 그것을 여러 번 실행 텍스트 1에 인덱스를 생성해야 수단을 만드는 옵션?

   이, "텍스트 내림차순은"MySQL의 마지막 null을 정렬합니다 있는지 확인을 실행하기 전에.

8. ==============================

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

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

   내 방법을 사용하면 피하려고 한 모든 지저분한 물건을 필요한 않습니다 :

   하지만 당신은 (수백만의 내 경우의 수만 나) 수백만에 대해 그것의 가치를 이야기 할 때.

   어쨌든 그별로 댓글이 포르투갈어하지만 여기 내 샘플이기 때문에 :

   편집 : 나는 의견을 얻는 경우에 나는 그것이 어떻게 작동하는지 더 설명 할 것이다 :

   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. ==============================

   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
   

from https://stackoverflow.com/questions/1651999/mysql-remove-duplicates-from-big-database-quick by cc-by-sa and MIT license