MySQL에서 수백만 개의 행 삭제

MySQL에서 수백만 개의 행 삭제

---

최근에 작업중인 사이트에서 버그를 발견하고 수정하여 테이블에 수백만 개의 중복 데이터 행이 생성되어 테이블이 없어도 상당히 커집니다 (여전히 수백만 개). 이러한 중복 행을 쉽게 찾을 수 있으며 단일 삭제 쿼리를 실행하여 모두 제거 할 수 있습니다. 문제는 한 번에이 많은 행을 삭제하려고하면 테이블이 오랫동안 잠기므로 가능하면 피하고 싶습니다. 사이트를 중단하지 않고 (테이블을 잠궈 서) 이러한 행을 제거 할 수있는 유일한 방법은 다음과 같습니다.

1. 루프에서 수천 개의 작은 삭제 쿼리를 실행하는 스크립트를 작성하십시오. 이것은 이론적으로 다른 쿼리가 큐에 들어갈 수 있고 삭제 사이에서 실행될 수 있기 때문에 잠긴 테이블 문제를 해결합니다. 그러나 여전히 데이터베이스의 부하를 상당히 증가시키고 실행하는 데 오랜 시간이 걸립니다.
2. 테이블 이름을 바꾸고 기존 테이블을 다시 만듭니다 (이제 비어 있음). 그런 다음 이름이 바뀐 테이블에서 정리를 수행하십시오. 새 테이블의 이름을 바꾸고 이전 테이블의 이름을 다시 지정한 다음 새 행을 이름이 바뀐 테이블에 병합합니다. 이것은 훨씬 더 많은 단계를 필요로하지만 최소한의 중단으로 작업을 완료해야합니다. 여기서 까다로운 부분은 문제의 테이블이보고 테이블이라는 것이므로 이름을 바꾸고 빈 테이블을 제자리에 넣으면 다시 제자리에 놓을 때까지 모든 기록 보고서가 사라집니다. 또한 병합 프로세스는 저장되는 데이터 유형으로 인해 약간의 고통이 될 수 있습니다. 전반적으로 이것은 지금 당장 내 가능성이있는 선택입니다.

다른 사람이 이전에이 문제를 겪은 적이 있는지 궁금합니다. 그렇다면 사이트를 중단하지 않고 어떻게 처리했는지, 그리고 사용자에게 방해가되지 않으면 최소한으로 처리 했나요? 2 번이나 다른 유사한 접근 방식을 사용하면 밤 늦게 실행되도록 예약하고 다음날 아침 일찍 병합을 수행하고 사용자에게 미리 알릴 수 있으므로 큰 문제가 아닙니다. 나는 누군가가 더 나은 또는 더 쉬운 정리 방법에 대한 아이디어를 가지고 있는지 찾고 있습니다.

---

DELETE FROM `table`
WHERE (whatever criteria)
ORDER BY `id`
LIMIT 1000

0 줄이 영향을받을 때까지 씻고 헹구고 반복하십시오. 반복 사이에 1 ~ 3 초 동안 잠자는 스크립트 일 수 있습니다.

---

또한 이러한 일이 다시 발생하지 않도록 테이블에 몇 가지 제약 조건을 추가하는 것이 좋습니다. 샷당 1000 개의 행이 백만 개이면 완료하는 데 스크립트가 1000 번 반복됩니다. 스크립트가 3.6 초마다 한 번씩 실행되면 한 시간 안에 완료됩니다. 걱정 마. 귀하의 고객은 눈치 채지 못할 것입니다.

---

다음은 1,000,000 개의 레코드를 한 번에 하나씩 삭제합니다.

 for i in `seq 1 1000`; do 
     mysql  -e "select id from table_name where (condition) order by id desc limit 1000 " | sed 's;/|;;g' | awk '{if(NR>1)print "delete from table_name where id = ",$1,";" }' | mysql; 
 done

당신은 그들을 함께 그룹화하고 테이블 이름을 삭제할 수 있습니다. 여기서 IN (id1, id2, .. idN)은 너무 어렵습니다.

---

MySQL의 2,500 만 개 이상의 행 테이블에서 1M 이상의 행을 삭제하는 사용 사례가있었습니다. 일괄 삭제와 같은 다른 접근 방식을 시도했습니다 (위에서 설명).
가장 빠른 방법 (필요한 레코드를 새 테이블로 복사)을 발견했습니다.

1. ID 만 보유하는 임시 테이블을 만듭니다.

CREATE TABLE id_temp_table (temp_id int);

2. 제거해야하는 ID를 삽입하십시오.

id_temp_table (temp_id)에 삽입 선택 .....

3. 새 테이블 생성 table_new

4. id_temp_table에있는 불필요한 행없이 table의 모든 레코드를 table_new에 삽입

table_new에 삽입 .... where table_id NOT IN (select distinct (temp_id) from id_temp_table);

5. 테이블 이름 바꾸기

전체 과정은 약 1 시간이 걸렸습니다. 내 사용 사례에서는 100 개의 레코드에 대한 배치를 간단히 삭제하는 데 10 분이 걸렸습니다.

---

내가 사용하는 거라고 MK-아카이버를 우수한에서 Maatkit의 유틸리티 패키지 (MySQL의 관리를위한 Perl 스크립트의 무리) Maatkit 남작 슈워츠, 오라일리 "고성능 MySQL은"책의 저자입니다.

목표는 OLTP 쿼리에 많은 영향을주지 않으면 서 테이블에서 오래된 데이터를 조금씩 빼내는 영향이 적은 전진 전용 작업입니다. 동일한 서버에 있지 않아도되는 다른 테이블에 데이터를 삽입 할 수 있습니다. LOAD DATA INFILE에 적합한 형식으로 파일에 쓸 수도 있습니다. 또는 둘 다 수행 할 수 없습니다.이 경우 증분 DELETE 일뿐입니다.

원치 않는 행을 작은 배치로 보관하기 위해 이미 구축되었으며 제거 할 행을 선택하는 쿼리를 망칠 경우 삭제 된 행을 파일에 저장할 수 있습니다.

설치가 필요하지 않습니다. http://www.maatkit.org/get/mk-archiver를 잡고 perldoc을 실행 (또는 웹 사이트 참조)하여 문서화하십시오.

---

비슷한 문제에 직면했습니다. 우리는 파티션이없는 약 500GB 크기의 정말 큰 테이블과 primary_key 열에 하나의 인덱스 만 가지고있었습니다. 우리의 마스터는 하나의 기계, 128 개의 코어, 512Gig의 RAM이었고 여러 개의 슬레이브도있었습니다. 대규모 행 삭제 문제를 해결하기 위해 몇 가지 기술을 시도했습니다. 우리가 찾은 최악에서 최고까지 모두 여기에 나열하겠습니다.

1. 한 번에 한 행씩 가져 오기 및 삭제. 이것은 당신이 할 수있는 최악의 상황입니다. 그래서 우리는 이것을 시도하지 않았습니다.
2. primary_key 열에 대한 제한 쿼리를 사용하여 데이터베이스에서 처음 'X'행을 가져온 다음 애플리케이션에서 삭제할 행 ID를 확인하고 primary_key ID 목록이있는 단일 삭제 쿼리를 실행합니다. 따라서 'X'행당 2 개의 쿼리가 있습니다. 이제이 접근 방식은 괜찮 았지만 배치 작업을 사용하여이 작업을 수행하면 10 분 내에 약 5 백만 개의 행이 삭제되어 MySQL DB의 슬레이브가 105 초 지연되었습니다. 10 분 활동에서 105 초 지연. 그래서 우리는 멈춰야했습니다.
3. 이 기술에서는 후속 일괄 가져 오기와 크기 'X'삭제 사이에 50ms 지연을 도입했습니다. 이를 통해 지연 문제가 해결되었지만 이제는 기술 # 2의 5 백만 행과 비교하여 10 분당 120 ~ 130 만 행을 삭제했습니다.
4. 데이터베이스 테이블을 파티셔닝 한 다음 필요하지 않은 경우 전체 파티션을 삭제합니다. 이것은 우리가 가지고있는 최선의 솔루션이지만 미리 분할 된 테이블이 필요합니다. primary_key 열에 대한 인덱싱 만있는 파티션되지 않은 매우 오래된 테이블이 있었기 때문에 3 단계를 따랐습니다. 파티션을 만드는 데 너무 많은 시간이 걸리고 위기 모드에있었습니다. 다음은 내가 유용한 파티셔닝 관련 링크입니다. 공식 MySQL 참조 , Oracle DB 일일 파티셔닝 .

따라서 IMO는 테이블에 파티션을 생성 할 여유가 있다면 옵션 # 4를 선택하십시오. 그렇지 않으면 옵션 # 3을 사용해야합니다.

---

한 번에 2000 행을 일괄 처리하십시오. 중간에 커밋하십시오. 백만 개의 행은 그다지 많지 않으며 테이블에 많은 인덱스가 없으면 빠릅니다.

---

에 따르면 MySQL의 문서 , TRUNCATE TABLE빠르고 대안입니다 DELETE FROM. 이 시도:

TRUNCATE TABLE table_name

나는 이것을 5 천만 행에서 시도했고 2 분 안에 완료되었습니다.

참고 : 자르기 작업은 트랜잭션에 안전하지 않습니다. 활성 트랜잭션 또는 활성 테이블 잠금 중 하나를 시도 할 때 오류가 발생합니다.

---

우리에게는 DELETE WHERE %s ORDER BY %s LIMIT %dWHERE 기준이 느리고 (인덱싱되지 않은 열) 마스터를 누르기 때문에 대답은 옵션이 아니 었습니다.

삭제하려는 기본 키 목록을 읽기 복제본에서 선택합니다. 다음과 같은 형식으로 내보내기 :

00669163-4514-4B50-B6E9-50BA232CA5EB
00679DE5-7659-4CD4-A919-6426A2831F35

다음 bash 스크립트를 사용하여이 입력을 가져 와서 DELETE 문으로 청크합니다 [ mapfile내장되어 있기 때문에 bash ≥ 4 필요 ].

sql-chunker.sh (remember to chmod +x me, and change the shebang to point to your bash 4 executable):

#!/usr/local/Cellar/bash/4.4.12/bin/bash

# Expected input format:
: <<!
00669163-4514-4B50-B6E9-50BA232CA5EB
00669DE5-7659-4CD4-A919-6426A2831F35
!

if [ -z "$1" ]
  then
    echo "No chunk size supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

if [ -z "$2" ]
  then
    echo "No file supplied. Invoke: ./sql-chunker.sh 1000 ids.txt"
fi

function join_by {
    local d=$1
    shift
    echo -n "$1"
    shift
    printf "%s" "${@/#/$d}"
}

while mapfile -t -n "$1" ary && ((${#ary[@]})); do
    printf "DELETE FROM my_cool_table WHERE id IN ('%s');\n" `join_by "','" "${ary[@]}"`
done < "$2"

Invoke like so:

./sql-chunker.sh 1000 ids.txt > batch_1000.sql

This will give you a file with output formatted like so (I've used a batch size of 2):

DELETE FROM my_cool_table WHERE id IN ('006CC671-655A-432E-9164-D3C64191EDCE','006CD163-794A-4C3E-8206-D05D1A5EE01E');
DELETE FROM my_cool_table WHERE id IN ('006CD837-F1AD-4CCA-82A4-74356580CEBC','006CDA35-F132-4F2C-8054-0F1D6709388A');

Then execute the statements like so:

mysql --login-path=master billing < batch_1000.sql

For those unfamiliar with login-path, it's just a shortcut to login without typing password in the command line.

---

I think the slowness is due to MySQl's "clustered index" where the actual records are stored within the primary key index - in the order of the primary key index. This means access to a record via the primary key is extremely fast because it only requires one disk fetch because the record on the disk right there where it found the correct primary key in the index.

In other databases without clustered indexes the index itself does not hold the record but just an "offset" or "location" indicating where the record is located in the table file and then a second fetch must be made in that file to retrieve the actual data.

You can imagine when deleting a record in a clustered index that all records above that record in the table must be moved downwards to avoid massive holes being created in the index (well that is what I recall from a few years ago at least - later versions may have changed this).

Knowing the above what we found that really sped deletes up in MySQL was to perform the deletes in reverse order. This produces the least amount of record movement because you are delete records from the end first meaning that subsequent deletes have less objects to relocate.

---

I have not scripted anything to do this, and doing it properly would absolutely require a script, but another option is to create a new, duplicate table and select all the rows you want to keep into it. Use a trigger to keep it up-to-date while this process completes. When it is in sync (minus the rows you want to drop), rename both tables in a transaction, so that the new one takes the place of the old. Drop the old table, and voila!

This (obviously) requires a lot of extra disk space, and may tax your I/O resources, but otherwise, can be much faster.

Depending on the nature of the data or in an emergency, you could rename the old table and create a new, empty table in it's place, and select the "keep" rows into the new table at your leisure...

참고URL : https://stackoverflow.com/questions/1318972/deleting-millions-of-rows-in-mysql