[sql] SQL 쿼리 : 최신 N을 제외한 모든 레코드를 테이블에서 삭제 하시겠습니까?

나는 꽤 오래된 질문을 부활시키고 있다는 것을 알고 있지만 최근 에이 문제에 직면했지만 많은 수로 확장되는 것이 필요했습니다 . 기존 성능 데이터가 없었고,이 질문에 상당한 관심이 있었기 때문에 내가 찾은 것을 게시 할 것이라고 생각했습니다.

실제로 작동하는 솔루션은 Alex Barrett의 이중 하위 쿼리 /NOT IN 메서드 ( Bill Karwin의 )와 Quassnoi의LEFT JOIN 방법이었습니다.

불행히도 위의 두 방법 모두 매우 큰 중간 임시 테이블을 만들고 삭제 되지 않는 레코드 수가 많아지면 성능이 빠르게 저하 됩니다.

내가 정한 것은 Alex Barrett의 이중 하위 쿼리 (감사합니다!)를 사용하지만 <=대신 사용 합니다 NOT IN.

DELETE FROM `test_sandbox`
  WHERE id <= (
    SELECT id
    FROM (
      SELECT id
      FROM `test_sandbox`
      ORDER BY id DESC
      LIMIT 1 OFFSET 42 -- keep this many records
    ) foo
  )

그것은 사용 OFFSET의 ID를 얻기 위해 N 번째 기록과 그 기록 및 이전의 모든 기록을 삭제합니다.

주문은 이미이 문제 ( ORDER BY id DESC) 의 가정이므로 <=완벽하게 적합합니다.

서브 쿼리에 의해 생성 된 임시 테이블에는 N 대신 하나의 레코드 만 포함되므로 훨씬 빠릅니다. .

테스트 케이스

위의 세 가지 작업 방법과 두 가지 테스트 사례에서 새로운 방법을 테스트했습니다.

두 테스트 사례 모두 10000 개의 기존 행을 사용하는 반면 첫 번째 테스트는 9000 개 (가장 오래된 1000 개 삭제)를 유지하고 두 번째 테스트는 50 개 (가장 오래된 9950 개 삭제)를 유지합니다.

+-----------+------------------------+----------------------+
|           | 10000 TOTAL, KEEP 9000 | 10000 TOTAL, KEEP 50 |
+-----------+------------------------+----------------------+
| NOT IN    |         3.2542 seconds |       0.1629 seconds |
| NOT IN v2 |         4.5863 seconds |       0.1650 seconds |
| <=,OFFSET |         0.0204 seconds |       0.1076 seconds |
+-----------+------------------------+----------------------+

흥미로운 점은이 <=방법이 전반적으로 더 나은 성능을 보이지만 실제로는 더 나쁘지 않고 더 많이 유지할수록 더 좋아진다는 것입니다.