롤링 기준으로 MySQL에서 오래된 행을 삭제하는 가장 좋은 방법은 무엇입니까?

롤링 기준으로 MySQL에서 오래된 행을 삭제하는 가장 좋은 방법은 무엇입니까?

많은 어플리케이션에서 (x)일보다 오래된 행을 롤 단위로 삭제하고 싶다고 생각하고 있습니다.교통량이 많은 테이블에서 이를 가장 효율적으로 수행하기 위한 가장 좋은 방법은 무엇일까요?

예를 들어 알림을 저장하는 테이블이 있는데 이 테이블을 7일 동안만 유지하려고 하는 경우입니다.아니면 31일 동안만 유지하고 싶은 고득점.

현재 게시된 Epoch 시간을 저장하는 행을 유지하고 시간당 1회 실행되며 다음과 같이 증분하여 삭제하는 cron 작업을 수행합니다.

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100

mysql_affected_rows가 0을 반환할 때까지 이 작업을 수행합니다.

예전에는 한 번에 했었지만, INSERTS가 쌓이는 동안 어플리케이션의 모든 것이 30초 정도 중단되었습니다.LIMIT를 추가하는 것은 이 문제를 완화할 수 있었지만, 더 나은 방법이 없을까 생각하고 있습니다.

원하는 시간 간격이 지나면 데이터베이스에서 자동으로 실행되는 이벤트를 만들어 보십시오.

다음은 예를 제시하겠습니다.일부 테이블 'tableName'에서 30일 이상 된 항목을 삭제하려면 열 항목이 'datetime'인 경우.그런 다음 매일 필요한 정리 작업을 수행하는 다음 쿼리를 실행합니다.

CREATE EVENT AutoDeleteOldNotifications
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 DAY 
ON COMPLETION PRESERVE
DO 
DELETE LOW_PRIORITY FROM databaseName.tableName WHERE datetime < DATE_SUB(NOW(), INTERVAL 30 DAY)

더해야 요.ON COMPLETION PRESERVE각 주행 후 이벤트를 보관합니다.상세한 것에 대하여는, http://www.mysqltutorial.org/mysql-triggers/working-mysql-scheduled-event/ 를 참조해 주세요.

MySQL 파티셔닝 확인:

유용성을 잃은 데이터는 종종 해당 데이터만 포함된 파티션을 삭제하여 분할된 테이블에서 쉽게 제거할 수 있습니다.반대로 새로운 데이터를 추가하는 프로세스는 특정 데이터를 저장하기 위한 하나 이상의 새로운 파티션을 추가하는 것으로 크게 촉진될 수 있습니다.

응용 방법에 대한 자세한 내용은 이 섹션을 참조하십시오.

MySQL 파티션 프루닝

그리고 이건...

날짜별 파티션 분할: 빠른 사용 방법

테이블만 삭제하지 말고 먼저 일치하는 키를 수집한 후 DELETE JOIN을 실행해 보십시오.

위의 샘플 쿼리

DELETE FROM my_table WHERE time_stored < 1234567890 LIMIT 100 ;

LIMIT는 빼도 됩니다.

31일 이상 지난 데이터를 삭제한다고 가정해 보겠습니다.

31일을 초 단위로 계산(86400 X 31 = 2678400)

• 키 수집부터 시작
• 다음으로, 키를 색인화합니다.
• 그런 다음 DELETE JOIN을 수행합니다.
• 마지막으로 수집된 키를 놓습니다.

여기 알고리즘이 있습니다.

CREATE TABLE delete_keys SELECT id FROM my_table WHERE 1=2;
INSERT INTO delete_keys
SELECT id FROM
(
    SELECT id FROM my_table
    WHERE time_stored < (UNIX_TIMESTAMP() - 2678400)
    ORDER BY time_stored
) A LIMIT 100;
ALTER TABLE delete_keys ADD PRIMARY KEY (id);
DELETE B.* FROM delete_keys
INNER JOIN my_table B USING (id);
DROP TABLE delete_keys;

키 수집이 5분 미만일 경우 이 쿼리를 5분마다 실행합니다.

시험해 보세요!!!

업데이트 2012-02-27 EDT 16:55

여기 키 수집을 좀 더 빠르게 할 수 있는 것이 있습니다.다음 인덱스를 추가합니다.

ALTER TABLE my_table ADD INDEX time_stored_id_ndx (time_stored,id);

그러면 delete_keys 테이블을 채우는 서브쿼리가 더 잘 지원됩니다.이는 필드가 인덱스에서만 검색되도록 커버링 인덱스를 제공하기 때문입니다.

업데이트 2012-02-27 EDT 16:59

자주 삭제해야 하므로 두 달에 한 번꼴로 이 작업을 시도해 보십시오.

OPTIMIZE TABLE my_table;

2개월 동안 5분마다 모든 귀찮은 작은 삭제 후 테이블 조각 모음을 수행합니다.

우리 회사도 비슷한 상황이에요.유효기간이 지난 키가 들어 있는 테이블이 있습니다.cron이 실행되어 그것을 소거합니다.

DELETE FROM t1 WHERE expiration < UNIXTIME(NOW());

이 작업은 한 시간에 한 번 실행되었지만, 고객님이 겪고 있는 것과 유사한 문제가 발생했습니다.1분에 1번으로 늘렸습니다.그러면 1분에 6번.기본적으로 쿼리를 실행하는 bash 스크립트를 사용하여 cron을 설정하고 나서 몇 초간 sleeve 상태를 유지한 후 1분이 될 때까지 반복합니다.

빈도가 증가했기 때문에 삭제할 행의 수가 대폭 감소했습니다.그게 논쟁을 완화시켰죠이 길은 내가 갈 길이다.

그러나 삭제해야 할 행이 아직 너무 많은 경우 제한을 사용하고 그 사이에 sleeve를 수행합니다.예를 들어 50k 행을 삭제할 경우 10k 청크를 2초 간격으로 수행합니다.이렇게 하면 쿼리가 쌓이는 데 도움이 되고 서버가 이러한 대량 삭제 간에 몇 가지 일반 작업을 수행할 수 있습니다.

설계에 마스터/슬레이브(복제) 솔루션을 도입하는 것을 고려해 보는 것이 좋습니다.모든 읽기 트래픽을 슬레이브로 이동하면 마스터가 열리고 '즉각' CRUD 작업이 처리됩니다. CRUD 작업은 슬레이브(읽기 서버)로 복제됩니다.

또한 삭제되는 레코드가 너무 많기 때문에 행이 삭제되는 테이블에서 최적화를 실행하는 것이 좋습니다.

이 기능을 사용하여 마지막 행이 100개밖에 남지 않았기 때문에 자주 실행할 때(매분) 상당한 지연이 발생함

delete a from tbl a left join (
    select ID
    from tbl
    order by id desc limit 100
) b on a.ID = b.ID
where b.ID is null;

언급URL : https://stackoverflow.com/questions/9472167/what-is-the-best-way-to-delete-old-rows-from-mysql-on-a-rolling-basis