대규모 MySQL InnoDB 테이블에서 전체 카운트 쿼리가 실제로 그렇게 느립니까?

Question

수백만 개의 항목이 포함된 큰 테이블이 있습니다. 전체 계산은 매우 느립니다. 아래 코드를 참조하세요. 이것이 MySQL InnoDB 테이블에 공통적으로 적용됩니까? 이 프로세스 속도를 높일 수 있는 방법이 없나요? 쿼리 캐싱을 사용해도 여전히 "느립니다". 또한 280만 개의 항목이 있는 "통신" 테이블의 개수가 450만 개의 항목이 있는 "트랜잭션" 테이블의 개수보다 느린 이유도 알고 싶습니다.

where 절을 사용하는 것이 더 빠르다는 것을 알고 있습니다. 성능이 떨어지는 것이 정상적인 것인지 궁금합니다.

저희는 Amazon RDS MySQL 5.7 및 m4.xlarge(CPU 4개, 16GB RAM, 500GB 스토리지)를 사용합니다. 또한 더 많은 CPU와 RAM을 갖춘 더 큰 인스턴스를 시도했지만 쿼리 시간에는 큰 변화가 없었습니다.

으아악

P粉401901266 · Answer

이는 다중 버전 동시성 제어(MVCC)를 사용하여 지원됩니다.

InnoDB를 사용하면 데이터 행을 읽고 쓰는 다른 동시 클라이언트를 차단하지 않고 쿼리를 트랜잭션 내에서 격리할 수 있습니다. 이러한 동시 업데이트는 거래의 데이터 보기에 영향을 미치지 않습니다.

그런데 계산하는 동안 많은 행이 추가되거나 삭제되는 것을 고려하면 테이블의 행 수는 얼마나 됩니까? 대답은 모호합니다.

트랜잭션이 시작된 후 생성된 행 버전을 트랜잭션에서 "볼" 수 없어야 합니다. 마찬가지로 다른 사람이 행 삭제를 요청하는 경우에도 트랜잭션은 행을 계산해야 하지만 트랜잭션이 시작된 후에 그렇게 합니다.

답은 SELECT COUNT(*) 또는 여러 행을 확인해야 하는 다른 유형의 쿼리를 실행할 때 InnoDB가 모든 행에 액세스하고 데이터베이스의 트랜잭션 뷰에 표시되는 해당 행의 현재 버전을 확인하고 쿼리를 수행해야 한다는 것입니다. 눈에 보이는 개수인 경우입니다.

트랜잭션이나 동시 업데이트(예: MyISAM)를 지원하지 않는 테이블에서 스토리지 엔진은 총 행 수를 테이블의 메타데이터로 유지합니다. 스토리지 엔진은 행을 동시에 업데이트하는 여러 스레드를 지원할 수 없으므로 행 합계가 덜 모호합니다. 따라서 MyISAM 테이블에서 SELECT COUNT(*)를 요청하면 메모리에 있는 행 수만 반환됩니다. (하지만 SELECT COUNT(*)를 수행하면 아무 소용이 없습니다.) WHERE 절을 사용하여 일부 기준에 따라 쌍을 이루세요. 행의 일부 하위 집합이 계산되므로 이 경우에는 실제로 계산해야 합니다.

전반적으로 대부분의 사람들은 InnoDB의 동시 업데이트 지원이 그만한 가치가 있다고 생각하며 최적화를 기꺼이 희생SELECT COUNT(*)합니다.

P粉356128676 · Answer

빌이 말한 것 외에는...

최소 지수

InnoDB는 보조 인덱스와 함께 포함된 COUNT(*)。可能所有communication的索引都大于transaction的最小索引，因此存在时间差。判断索引的大小时，请将 PRIMARY KEY 열을 수행하기 위해 "최소" 인덱스를 선택합니다.

으아악

사이즈 측정을 위해 PRIMARY KEY 很大，因为它包含（由于集群）表的所有列。 INDEX(flag) 是“5 个字节”。 INDEX(name) 平均可能有几十个字节。 SELECT COUNT(*) 将明确选择 INDEX(flag).

분명히交易有一个“小”索引，但通信아니요.

TEXT/BLOG 열은 때때로 "로그되지 않은" 상태로 저장됩니다. 따라서 PK 인덱스 크기에는 포함되지 않습니다.

쿼리 캐시

"쿼리 캐시"가 켜져 있으면 쿼리의 두 번째 실행이 첫 번째 실행보다 훨씬 빠를 수 있습니다. 그러나 이는 테이블이 변경되지 않은 경우에만 발생합니다. 테이블을 변경하면 해당 테이블의 모든 QC 항목이 무효화되므로 QC는 프로덕션 시스템에서 거의 유용하지 않습니다. "더 빠르다"는 것은 1.44초가 아니라 약 0.001초를 의미합니다.

1m38s와 1.44s의 차이는 아마도 buffer_pool(InnoDB의 일반 캐시 영역)에 캐시되는 내용 때문일 것입니다. 첫 번째 실행에서는 RAM에서 "최소" 인덱스를 찾을 수 없었기 때문에 많은 I/O를 수행하여 해당 인덱스에 대한 450만 개의 행을 모두 가져오는 데 98초가 걸렸습니다. 두 번째 실행에서는 모든 데이터가 buffer_pool에 캐시되어 CPU 속도(I/O 없음)로 실행되었으므로 훨씬 더 빨랐습니다.

충분히 좋아요

이런 경우에는 시행COUNT(*)의 필요성에 근본적으로 의문이 듭니다. 유효 숫자 2개가 "충분하다"인 것처럼 "280만 입력"이라고 말하는 방식에 유의하세요. UI에서 사용자에게 개수를 표시하면 "충분히 좋지" 않습니까? 그렇다면 성능을 위한 한 가지 해결책은 하루에 한 번 계산하여 어딘가에 저장하는 것입니다. 이렇게 하면 "충분히 좋은" 값에 즉시 액세스할 수 있습니다.

다른 기술도 있습니다. 하나는 활동 태그나 일부 형태의 요약 테이블을 사용하여 카운터를 최신 상태로 유지하는 것입니다.

하드웨어를 던져보세요

하드웨어를 변경해도 도움이 되지 않는다는 것을 알게 되셨습니다.

98은 RDS의 I/O 제품만큼 빠르게 실행됩니다.
1.44s는 RDS CPU만큼 빠르게 실행됩니다.
MySQL(및 그 변형)은 쿼리당 여러 CPU를 사용하지 않습니다.
충분한 RAM이 있으므로 전체 "작은" 인덱스를 두 번째 작업까지 buffer_pool에 넣을 수 있습니다. SELECT COUNT(*).. (RAM이 너무 적으면 두 번째 실행이 매우 느려집니다.)