3강. COUNT(*) & COUNT(DISTINCT) 튜닝

1. 잘못된 기대

• COUNT() 쿼리는 빠를 것으로 기대한다. → 하지만 대부분은 성능 차이가 없거나 COUNT() 쿼리가 더 오래 걸린다. → 왜냐면, SELECT * 쿼리는 대부분 LIMIT 조건이 걸려져 있다.

-- 하지만 대부분의 경우 아래 두 쿼리는 성능차이가 없거나 미비하다.
-- MyISAM 엔진과 InnoDB 엔진의 차이로 동작이 다를 수 있기 때문에, 엔진의 차이를 한번 확인해보자
SELECT COUNT(*) >>> SELECT *

• ORM 에서 자동 생성된 쿼리 → 어떤 이유에서 인지 더 많은 컴퓨팅 파워를 요구하는 COUNT(DISTINCT(id)) 로 사용한다. → 때문에, ORM 서비스가 많든 쿼리를 실제로 확인할 필요가 있다!

SELECT COUNT(DISTINCT(id)) as counter // COUNT(DISTINCT(id)) : 해당 컬럼의 카디널리티 확인
FROM tab 
WHERE fd1 = ?;

2. COUNT(*) 성능

• 아래 쿼리의 성능 차이를 생각해보자. (ix_fd 는 인덱스로 설정되어 있다)

  • 조건을 따른 데이터를 가져오는 방식 : 커버링 인덱스는 사용할 수 없고, ix_fd 인덱스를 통해서 데이터 페이지의 결과를 가져오고, 그 안에서 non_ix_fd 의 조건이 맞는 데이터를 클라이언트에게 보여주게 된다.

  • 아래 데이터 데이터 조회에 필요한 데이터 수가 동일하기 때문에, 쿼리의 성능 차이는 거의 없다.

• 하지만, 일반적인 상황을 생각해보자

  • SELECT * 와 LIMIT 는 같이 사용된다.

  • SELECT COUNT(*) 과 LIMIT 없이 사용된다.

  • 만약에 총 데이터가 10만개라면 LIMIT 가 있는 쿼리의 성능이 좋을 수 밖에 없다.

// 1
SELECT COUNT(*)
FROM ...
WHERE ix_fd = 'A' AND non_ix_fd = 'B'

// 2
SELECT *
FROM ...
WHERE ix_fd = 'A' AND non_ix_fd = 'B'

3. COUNT(*) 성능 개선

• Covering Index

SELECT COUNT(*) FROM ... WHERE ix_fd1 = ? AND ix_fd2 = ?
SELECT COUNT(ix_fd2) FROM ... WHERE ix_fd1 = ?

• Non Covering Index

SELECT COUNT(*) FROM ... WHERE ix_fd1 = ? AND non_ix_fd2 = ?
SELECT COUNT(non_ix_fd2) FROM ... WHERE ix_fd1 = ?

• 하지만 일반적으로 COUNT(*) 쿼리에서 수 많은 조건들이 있을텐데, 이 때 모든 조건을 커버링 인덱스로 사용하기에는, 메모리 사용량이 늘어날 수 있기 때문에, 장점보다 단점이 많을 수 있다. → 정말 필요한지를 고민해보아야 한다.

4. COUNT(*) vs COUNT(DISTINCT expr)

• COUNT(*) 는 레코드 건수만 확인한다.

• COUNT(DISTINCT expr) 은 중복이 제거된 데이터를 임시 테이블로 복사 후 건수를 확인한다. → 중복이 제거된 데이터를 임시 테이블로 복사 하기 위해서 SELECT → INSERT or UPDATE 의 과정을 거치게 된다. → 테이블 레코드를 모두 임시 테이블로 복사 후 임시 테이블의 최종 레코드 건수 반환

5. COUNT(*) 쿼리 튜닝

5-1. 응용 프로그램 변경 없이 튜닝

응용 프로그램을 변경하지 않으면서 튜닝하는 방법은, 커버링 인덱스를 사용하는 쿼리 튜닝이 거의 유일하다고 볼 수 있다.

5-2. 응용 프로그램과 함께 튜닝

최고의 튜닝은 쿼리 자체를 없애 버리는 것

• 전체 결과 건수 확인 쿼리 제거

• 페이지 번호 없이, ‘이전’, ‘이후’ 페이지 이동

쿼리를 제거할 수 없다면, 대략적 건수를 확인!

• 부분 레코드 건수 조회

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

• 임의의 페이지 번호를 표기

  • 첫 페이지에서 10개 페이지 표기 후, 예를 들어 7페이지를 클릭했다면 아래 쿼리 실행 SELECT COUNT(*) FROM table LIMIT 10 OFFSET 60;

  • 결과가 10개 미만이라면 페이지를 7로 줄이고, 데이터 표현

  • 결과가 0개라면 아래 쿼리를 실행해 실제 데이터 건수를 파악 후, 페이지 표현 SELECT COUNT(*) FROM table; → 실제 데이터 수가 많지 않을 것이기 때문에, DB 부하는 생각하지 않아도 된다.

  • 실제 Google 에서도 확인할 수 있는 방법이다.

• 통계 정보 활용

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

  • 성능은 확실하지만, 정확도가 낮기에 페이지 이동하면서 보정이 필요하다. → 통계 정보는 예측치일 뿐이다..

  SELECT TABLE_ROWS as rows
  FROM INFORMATION_SCHEMA.tables
  WHERE table_schema = 'my_database' AND table_name = 'my_table';

5-3. 튜닝 시 고민점 ..

제거 대상

• WHERE 조건 없는 COUNT(*)

• WHERE 조건에 일치하는 레코드 건수가 많은 COUNT(*) → 레코드 수가 많다는 것은, 가져와야 할 데이터가 많다는 것을 의미한다..

인덱스 활용한 최적화 대상

→ 인덱스를 활용한 최적화는 몇 만건 까지는 유의미하게 튜닝이 가능하지만, 그 이상은 큰 효과를 거두기가 어렵다.. 다른 방법을 고민해보자

• 정확한 COUNT(*) 가 필요한 경우

• COUNT(*) 대상 건수가 소량인 경우

• WHERE 조건이 인덱스로 처리될 수 있는 경우(커버링 인덱스)