MySQL에서 행 수 증가 속도 향상

MySQL에서 행 수 증가 속도 향상

예를 들어 다음과 같이 3개의 컬럼이 있는 단순한 MySQL "books" 테이블을 사용하여 라이브러리를 실행하고 있다고 가정합니다.

(ID, 직함, 상태)

• id는 프라이머리 키입니다.
• 제목은 책의 제목이다.
• 상태는 책의 현재 상태를 설명하는 열거형일 수 있습니다(예: AVAILABLE, CHECKEDOUT, PROCSINGING, MISSING).

각 주에 해당하는 책의 수를 보고하는 간단한 질문은 다음과 같습니다.

SELECT status, COUNT(*) FROM books GROUP BY status

또는 구체적으로 몇 권의 책을 구할 수 있는지 알아보려면:

SELECT COUNT(*) FROM books WHERE status = "AVAILABLE"

그러나 테이블이 수백만 행으로 증가하면 이러한 쿼리를 완료하는 데 몇 초가 걸립니다.상태 열에 인덱스를 추가해도 내 경험에 차이가 없는 것 같습니다.

(트리거 또는 기타 메커니즘을 통해) 책이 상태를 변경할 때마다 결과를 정기적으로 캐싱하거나 별도의 테이블에 요약 정보를 명시적으로 업데이트하는 것 외에 이러한 종류의 쿼리를 가속화하는 기술이 있습니까?COUNT 쿼리는 모든 행을 보게 되는 것 같습니다만, (자세한 내용은 알 수 없지만) 인덱스에서 이 정보를 확인할 수 없는 것은 조금 놀랐습니다.

갱신하다

200만 행의 샘플 테이블(색인된 "상태" 열 포함)을 사용하여 GROUP BY 쿼리를 벤치마킹했습니다.InnoDB 스토리지 엔진을 사용하여 쿼리 시간은 3.0~3.2초입니다.MyISAM을 사용하는 경우 쿼리에 걸리는 시간은 0.9~1.1초입니다어느 경우든 count(*), count(status) 또는 count(1) 사이에는 유의한 차이가 없었습니다.

MyISAM이 조금 더 빠르기는 하지만 캐싱과 트리거의 정신적 오버헤드 없이 동등한 쿼리를 훨씬 더 빠르게 실행할 수 있는 방법(예를 들어 트래픽이 적은 사이트의 모든 웹 페이지 요청에서 호출할 수 있는 속도)이 있는지 궁금했습니다.답은 "직접 쿼리를 빠르게 실행할 방법은 없습니다"인 것 같습니다.그것은 제가 기대했던 것입니다.그냥 간단한 대안을 놓치고 있지 않은지 확인하고 싶었습니다.

그래서 문제는

이런 종류의 질문을 빠르게 할 수 있는 기술이 있나요?

글쎄, 그렇진 않아.열 기반 스토리지 엔진은 SELECT COUNT(*) 쿼리에서 더 빠를 수 있지만 다른 쿼리에서는 성능이 거의 떨어집니다.

트리거를 통해 요약 테이블을 유지하는 것이 가장 좋습니다.오버헤드가 크지 않고 테이블이 아무리 커도 SELECT 파트는 순간적입니다.보일러 플레이트 코드는 다음과 같습니다.

DELIMITER //

CREATE TRIGGER ai_books AFTER INSERT ON books
FOR EACH ROW UPDATE books_cnt SET total = total + 1 WHERE status = NEW.status
//
CREATE TRIGGER ad_books AFTER DELETE ON books
FOR EACH ROW UPDATE books_cnt SET total = total - 1 WHERE status = OLD.status;
//
CREATE TRIGGER au_books AFTER UPDATE ON books
FOR EACH ROW
BEGIN
    IF (OLD.status <> NEW.status)
    THEN
        UPDATE books_cnt SET total = total + IF(status = NEW.status, 1, -1) WHERE status IN (OLD.status, NEW.status);
    END IF;
END
//

MyISAM은 실제로 카운트(*)가 매우 빠릅니다.단점은 MyISAM 스토리지의 신뢰성이 높지 않고 데이터 무결성이 중요한 경우에는 피하는 것이 가장 좋다는 것입니다.

InnoDB는 동일한 데이터를 여러 번 동시에 볼 수 있도록 설계되었기 때문에 카운트(*) 유형의 쿼리를 실행하는 데 매우 느릴 수 있습니다.따라서 언제든지 인덱스로 이동하여 카운트를 얻는 것만으로는 충분하지 않습니다.

송신원:http://www.mail-archive.com/mysql@lists.mysql.com/msg120320.html

데이터베이스는 1000개의 레코드로 시작합니다.트랜잭션을 시작합니다.트랜잭션을 시작합니다50개의 레코드를 삭제합니다50개의 레코드를 더하면 카운트()를 하고 950개의 레코드가 표시됩니다. 카운트()를 실행하면 1050개의 레코드가 표시됩니다.거래를 약속합니다.데이터베이스에는 현재 950개의 레코드가 있습니다.트랜잭션을 커밋합니다.데이터베이스에 다시 1000개의 레코드가 있습니다.

InnoDB가 트랜잭션과 관련하여 "보여지는" 기록과 "수정 가능한" 기록을 유지하는 방법은 행 수준의 잠금, 트랜잭션 분리 수준 및 다중 버전을 통해 이루어집니다.http://dev.mysql.com/doc/refman/4.1/en/innodb-transaction-model.html http://dev.mysql.com/doc/refman/4.1/en/innodb-multi-versioning.html

그렇기 때문에 각자가 볼 수 있는 기록의 수를 세는 것은 그리 간단하지 않다.

즉, 이 정보를 자주, 신속하게 입수할 필요가 있는 경우는, 테이블로 이동하는 것이 아니라, 어떻게든 카운트를 캐싱하는 것을 검토할 필요가 있습니다.

출처 : http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.html

InnoDB는 테이블 내의 행 수를 유지하지 않습니다.(실제로 이것은 멀티버전화로 인해 다소 복잡합니다).SELECT COUNT(*) FROM t 문을 처리하려면 InnoDB는 테이블의 인덱스를 스캔해야 합니다.인덱스가 버퍼 풀에 완전히 존재하지 않으면 시간이 걸립니다.

제안하는 솔루션은 다음과 같습니다.

빠른 카운트를 얻으려면 사용자가 직접 작성한 카운터 테이블을 사용하여 삽입 및 삭제에 따라 응용 프로그램에서 업데이트할 수 있도록 해야 합니다.대략적인 행 수가 충분한 경우에도 SHOW TABLE STATUS를 사용할 수 있습니다.

즉, count(*)(innoDB의 경우)는 많은 수의 행을 포함하는 테이블에 대해 오랜 시간이 걸립니다.이건 고의로 한 일이니 어쩔 수 없어요.

독자적인 회피책을 작성합니다.

카운트(*), 카운트(상태) 또는 카운트(1) 사이에 유의한 차이가 없습니다.

count(column)는 열이 NOT NULL이 아닌 행 수를 반환합니다. 1은 NOT NULL이 아니며 상태도 NOT NULL일 수 있으므로 데이터베이스는 테스트를 최적화하고 모두 카운트(*)로 변환합니다.아이러니하게도 "모든 열이 null이 아닌 카운트 라인"(또는 다른 조합)이 아니라 "카운트 라인"을 의미합니다.

자, 다시 질문으로 돌아가자면, 케이크를 먹고 살 수는 없어요...

• "정확한" 카운트를 항상 사용할 수 있도록 하려면 트리거를 통해 실시간으로 증가 및 감소해야 하며, 이로 인해 쓰기가 느려집니다.

• 또는 카운트(*)를 사용할 수 있지만 속도가 느려집니다.

• 또는 대략적인 추정치 또는 오래된 값으로 만족하고 캐싱 또는 기타 확률론적 접근 방식을 사용할 수 있습니다.

일반적으로 몇 개 이상의 값에서는 NO-ONE은 정확한 실시간 카운트에 관심이 있습니다.어쨌든 이것은 속임수입니다.그것을 읽을 때쯤이면 그 값이 바뀌었을 가능성이 큽니다.

여기 많은 답변들이 지수가 도움이 안 될 거라고 했지만 내 경우에는 도움이 되었다...

MyISAM을 사용한 테이블은 10만 행 정도밖에 없었습니다.쿼리:

select count(*) from mytable where foreign_key_id=n

완료하는 데 7~8초가 걸렸습니다.

에 인덱스를 추가했습니다.foreign_key_id:

create index myindex on mytable (foreign_key_id) using btree;

인덱스를 작성한 후 위의 select 문에서 실행 시간이 0.00초로 보고되었습니다.

언급URL : https://stackoverflow.com/questions/1332624/speeding-up-row-counting-in-mysql