[MySQL] count(*) 과 count(distinct)

 

 

많은 사람들이 select * 보다 select count(*) 이 더 빠를 것으로 생각합니다. 🤨

그런데, 일반적으로 둘의 성능이 비슷하게 측정되는 경우가 훨씬 많다고 합니다.

왜냐하면, 서비스 구현 요건상 select * 쿼리는 limit 조건이 있기 때문입니다.

반면에 select count(*) 쿼리는 limit 조건 없이 실행되는것이 일반적 입니다…!

 

그리고 요즘 orm을 대부분 사용하는데, orm을 사용하다보면 단순히 레코드 건수를 확인하는 쿼리를 작성했을뿐인데,

부하가 훨씬 높은 select count(distinct(id)) 같은 쿼리를 실행하는 경우가 있습니다.

 

select count(*)과 select * 성능

아래의 쿼리의 경우에는 2개의 쿼리는 동일한 성능을 내게 됩니다.

select count(*)
    where ix_name = 'apple' and non_ix_name = 'banana';

select *
    where ix_name = 'apple' and non_ix_name = 'banana';

그러나 일반적으로 select * 쿼리는 limit 조건과 동시에 사용하게 되어,

select count(*) 쿼리보다 select * 쿼리가 더 좋은 성능을 발휘하게 됩니다.

 

다만 count(*) 쿼리가 네트워크 사용량은 적겠죠?

 

count(*) 성능 개선!

covering index

select count(*) where ix_name = 'apple' and ix_price = 100;

select count(ix_name) where ix_price = 100;

covering index 방법을 적용하면, 인덱스만 읽으면 쿼리 처리를 완료 할 수 있습니다.!!

하지만, 모든 쿼리를 covering index로 튜닝할 수는 없습니다….

일반적으로 count(*) 쿼리라고 하더라도 where 절에 많은 조건들이 있기 때문입니다. ㅜㅜ

 

non-covering index

select count(*) where ix_name = 'apple' and non_ix_name = 'banana';

select count(non_ix_name) where ix_name = 'apple';

 

count(*) vs count(distinct expr)

count (*)는 조건에 일치하는 레코드를 찾아서 레코드 건수만 확인합니다.

반면에, count (distinct expr)은 임시 테이블로 중복 제거후 건수를 확인합니다.

따라서 많은 메모리와 cpu 자원을 소모하게 됩니다. 🥲

 

따라서 orm을 사용할때 정확하게 어떤 쿼리가 실행되는지 명확하게 확인하는 과정이 반드시 필요합니다!!

그리고 한가지 더 팁을 드리자면 mysql sever에 general log를 활성시키는 것이 좋습니다. 👍

 

count(*) 튜닝

만약에 Application 의 로직을 변경하지 않고 count(*) 튜닝하는 방법은
covering index를 사용하는 것이 유일한 튜닝방법인 것 같습니다.

count(*)를 제거하자!

• count(*) 쿼리가 반드시 필요한지 검토후 필요 없으면 제거하자!
  • 전체 결과 건수 확인 쿼리를 제거
  • 페이지 번호 없이 “이전”, “이후” 같은것으로 풀어보자!
  • 무한 스크롤 방식으로 해결하는 것도 방법이다!
    • 커서 방식을 이용!

 

count(*) 쿼리를 제거할 수 없다면…?

• 부분 레코드 건수로 해결해보자.
  • 표시할 페이지 번호만큼 레코드만 건수를 확인
    • select count(*) from (select 1 from table limit 200) z;
    • 👆 위 경우 200건 미만이면 정확한 count를 출력하고, 200건 이상이면 200건 이상 같은 것으로 출력 한다…!

 

• 정확한 건수를 제공하지 않고 임의의 페이지 번호를 표기하는 방식
  • 첫 페이지에 10개 페이지 표시 후, 실제 해당 페이지로 이동하면 그때 count(*) 쿼리가 아니라 select * 쿼리를 실행해서 결과 존재 여부를 페이지 번호로 갱신해주는 방법
  • 예를 들어 사용자가 5페이지를 클릭하면 select * from table limit 10 offset 40 쿼리가 실행되는데 결과가 10개 미만이면, 5페이지를 마지막 페이지 번호로 표시해주고 10개면 페이지 번호를 그대로 10까지 유지하는 방법
  • 구글도 처음에는 10 페이지가 있다고 하는데, 페이지를 넘어가다보면 4페이지가 마지막인 경우가 있다.

 

• 통계 정보 확인
  • mysql 서버에서는 information schema의 tables라는 뷰를 이용하면 특정 테이블의 전체 레코더 건수를 확인할 수 있는데, count(*) 쿼리가 별도의 where 절 조건이 없는 경우에 통계 정보에서 테이블의 레코더 건수를 조회하면 좋습니다!
    • select t.TABLE_ROWS from information_schema.TABLES twhere t.TABLE_SCHEMA = ? and t.TABLE_NAME = ?;
  • 쿼리 조건이 있는 경우(실행 계획(explain) 활용)
    • 쿼리의 실행 계획에서 rows 컬럼을 이용해서 그 예측된 값(row 값)을 확인하는 방법을 사용할 수 있습니다.
      • 물론 실행 계획(explain)의 rows 컬럼 값은 count(*) 쿼리보다는 정확도가 많이 떨어질 수 있습니다.
    • join 이나 subQuery가 존재하는 복잡한 쿼리에서는 정확도가 많이 떨어져서 사용이 어려울 수도 있습니다.
    • 적절하게 index만 잘 활용한다면, 좋은 성능을 발휘할 수 있습니다!

 

count(*) 튜닝 정리

제거

• where 조건이 없는 count(*)
• where 조건에 일치하는 건수가 많은 count(*)

인덱스

• 정확한 count(*)이 필요한 경우
• count(*) 대상 건수가 소량인 경우
• where 조건이 index로 처리될 수 있는 경우

 

참고

• https://www.inflearn.com/course/real-mysql-part-1/dashboard

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런