real Mysql - COUNT(*) vs COUNT(DISTINCT)

COUNT(*) 성능

SELECT COUNT(*) where ix_fd = 'A' AND non_ix_fd = 'B';

SELECT * where ix_fd = 'A' AND non_ix_fd = 'B';

ix_fd 조건은 인덱스를 사용할 수 있지만 non_ix_fd는 사용할 수 없음

그래서 ix_fd 컬럼의 인덱스를 사용해서 대상 레코드를 찾은 후 non_ix_fd의 컬럼의 값 비교

• SELECT * 은 LIMIT와 동시에 사용되지만
• SELECT COUNT(*) 는 LIMIT 없이 사용됨

정확한 레코드 수를 확인해야 하는 경우에 최고의 성능 튜닝은 커버링인덱스를 사용하는것

Covering Index
1
SELECT COUNT(*) WHERE ix_fd1 = ? AND ix_fd2 = ?;
2
SELECT COUNT(ix_col2) WHERE ix_fd1 = ?;

NON - Covering Index
3
SELECT COUNT(*) WHERER ix_fd1 AND non_ix_fd1 = ?;
4
SELECT COUNT(non_ix_fd1) WHERE ix_fd1 = ?;

모든 COUNT ALL 쿼리를 커버링 인덱스로 최적화 할 수는 없다.

모든 컬럼들을 인덱스에 추가하기에는 성능적인 장점보다는 단점이 많을 수 있기때문

COUNT(*) vs COUNT(DISTINCT expr)

• COUNT(*)는 레코드 건수만 확인
• COUNT(DISTINCT expr)은 임시 테이블로 중복 제거후 건수 확인

• distinct 쿼리는 많은 메모리 공간과 CPU 자원을 소모하는 편
• 위의 그림과 같이 COUNT(DISTINCT expr)은 중복 레코드 제거를 위해 임시 테이블 생성 해서 조건절에 일치하는 레코드를 찾아서 임시 테이블에 저장
• COUNT DISTINCT 를 위해서 select 와 insert를 내부적으로 수행

COUNT(*) 튜닝

• 최고의 튜닝은 쿼리 자체를 제거하는것

  • 전체 결과 건수 확인 쿼리 제거
  • 페이지 번호 없이, 이전 이후 페이지 이동

• 쿼리를 제거할수 없다면 대략적 건수 활용

  • 부분 레코드 건수 조회
  • 표시할 페이지 번호만큼의 레코드 건수 확인

  SELECT COUNT(*) FROM (SELECT 1 FROM table LIMIT 200) z;

  • 임의의 페이지 번호는 표기
    • 첫 페이지에서 10개 페이지 표시 후, 실제 해당 페이지로 이동하면서 페이지 번호 보정

• 통게 정보 이용

  • 쿼리 조건이 없는 경우, 테이블 통계 활용

  SELECT TABLE_ROWS as rows
  FROM INFORMATION)SCHEMA.tables
  WHERE schema_name = ? AND table _name = ?
  

• 제거 대상

  • WHERE 조건이 없는 것
  • WHERE 조건에 일치하는 건수가 많은 것

• 인덱스 활용하여 최적화 대상

  • 정확한 COUNT(*)가 필요한 경우
  • COUNT(*) 대상 건수가 소량인 경우
  • WHERE 조건이 인덱스로 처리될 수 있는 경우

  COUNT(DISTINCT expr) 튜닝

  ex) disintceAlias.comment_id 는 사실 pk 컬럼이므로 실제 distince 작업ㅇ ㅣ필요하지 않지만 TypeORM에서는 distinct를 사용해서 쿼리를 자동 생성한다.