3) COUNT(*) & COUNT(DISTINCT) 튜닝 4) 페이징 쿼리 작성

• 대부분은 Count(*) 쿼리가 select * 보다 빠를것으로 예상하지만 실제로는 동일한 경우가 더 많다. limit조건 때문에 차이가 나게 된다. 
  • count의 경우 limit조건이 무의미하기 때문이다.
• orm에서 자동 생성된 쿼리 실행때문에도 문제가 생긴다.
  • select count(distinct(id)) as counter from tab where fd1 = ?;
  • 단순히 count *으로 fd1에 해당되는 row의 숫자만 세려고 했는데 훨씬 부하가 더 많은 count(distinct(id)) 로 처리하는 경우도 있다.

 

2개의 쿼리 둘다  index가 걸린 column을 기준으로 record를 다 찾은다음에 index가 걸리지않은 column에서 B값과 일치하는것을 하나하나 확인하는 방식으로 동작한다. , 즉 성능이 비슷하다

네트워크에 보내는 바이트양은 select가 더 많다.

1,2 번쿼리는 둘 다 index 에 존재하기 떄문에 covering index에 해당된다.

하지만 3,4번은 쿼리에 index가 아닌 column이 껴 있기 때문에 non covering index으로 느리다. 그렇다 하더라도 모든 쿼리를 covering index로 튜닝하는것은 적절하지 않다.

 

 

count distinct는 많은 memory, cpu를 소모하게 된다.

중복 제거용 임시테이블에서 select를 하고 이와 중복이 되는지 확인한 후에 insert하는 작업이 추가로 진행되게 된다.

임시테이블을 disk에까지 저장하는 과정도 생긴다면 시간또한 오래걸리게 될것이다.

 

Count * 튜닝

• 최고의 튜닝은 쿼리 자체를 제거하는것
  • 전체 결과 건수 확인 쿼리제거
  • 페이지 번호없이 "이전" , "이후" 페이지 이동
• 쿼리를 제거 할 수 없다면 , 대략적 건수 활용
  • 부분 레코드 건수 조회
  • 표시할 페이지 번호만큼의 레코드만 건수 확인
  • select count(*) from (select 1 from table limit 200) z;
  • 임의의 페이지 번호는 표기
  • 첫 페이지에서 10개 페이지 표시 후, 실제 해당 페이지로 이동하면서 페이지 번호 보정
    
    

---

페이징 쿼리

• 원하는 전체 데이터에 대해 부분적으로 나눠서 데이터를 조회 및 처리하는 방법
• db 및 앱 서버의 리소스 사용 효율 증가 
• 앱 단의 처리시간 단축

db서버에서 제공하는 limit & offset 구문을 사용하는 경우가 많은데 이게 오히려 더 부하를 발생 시킴

db는 지정된 offset 이후 데이터만 바로 가져올수 없고 순차적으로 읽는 과정이 반드 시필요함

이러한 쿼리 실행 횟수가 늘어날 수록 점점 더 읽는 데이터가 많아지고 응답시간이 길어짐

되도록이면 limit & offset 구문을 사용하지 않으면서 데이터를 원하는 만큼만 조회해서 가져갈 수 있도록 쿼리를 작성해야한다. 

 

1) 범위 기반 방식

• 날짜 기간이나 숫자 점위로 나눠서 데이터를 조회하는 방식
• 매 쿼리 실행시 where 절에서 조회 범위를 직접 지정하는 형태로 , 쿼리에서 limit 절이 사용되지 않는다.
• 주로 배치 작업등에서 테이블의 전체 데이터를 일정한 날짜/ 숫자 범위로 나눠서 조회 할때 사용한다.
• 쿼리에서 사용되는 조회 조건도 굉장히 단순하며, 여러 번 쿼리를 나누어 실행하더라도 사용하는 쿼리 형태는 동일하다.

 

2) 데이터 개수 기반 방식

• 지정된 데이터 건수만큼 결과 데이터를 반환하는 형태로 구현된 방식
• 배치보다 주로 서비스단에서 많이 사용되는 방식으로 쿼리에서 order by & limit 절이 사용된다
• 처음 쿼리를 실행할 때 (1회차) 와 그 이후 쿼리를 실행할 때 (N회차) 쿼리형태가 달라진다.
• 쿼리의 where 절에서도 사용되는 조건 타입에 따라서 N회차 실행시의 쿼리 형태도 달라진다.

offset을 사용하지않고 order by + where 를 통해서 offset 기능처럼 활용하지만 그 이전의 row processing이 사라졌다.

왜 order by에 id말고 추가적으로 finished _at이 필요한것일까?

id 컬럼만 명시하는 경우, where 절 조건을 만족하는 데이터들을 모두 읽어들인 후 id로 정렬한 다음
limit에 지정된 건수 만큼 반환하게 된다.  ->  (finished_at, id) 인덱스가 있음에도 정렬작업이 발생한다.

finished_at 컬럼을 선두에 명시하면, (finished_at, id) 인덱스를 사용해서 정렬 작업 없이 원하는 건수 만큼 순차적으로 데이터를 읽을 수 있으므로 처리 효율이 향상된다.

 

n회차 쿼리를 쓰기 전 1회차 쿼리이다.

n회차 쿼리 작성시 id 조건만 where에 추가한경우 데이터 누락(4,6,7) 이 발생한다.

table은 finished_at이 같으면 id로 정렬이되어있다. 

1번 쿼리에서 2초에 끝났는데 8보다큰 row를 가져오고 (같은 경우)

2번 쿼리에서 2초 이후 부터 다음 페이지 까지 필요한 row를 가져온다.

정리

• limit & offset 구문은 db 서버 부하를 발생시키므로 사용을 지양해야함
• 페이징 쿼리는 대표적으로 2가지로 구분 할 수 있음
  • 범위 기반 방식
  • 데이터 개수 기반 방식
• 범위 기반방식은 단순하게 날짜/ 숫짜 값을 특정 범위로 나눠서 쿼리를 실행하는 형태로, 1회차 N회차 쿼리 형태가 동일하다.
• 데이터 개수 기반 형식은 지정한 데이터 개수만큼만 조회하는 형태로, 1회차와 N회차 쿼리 형태가 다르다.
  • 쿼리에 사용되는 조건타입에 따라, 또 경우에 따라 쿼리 형태가 달라지므로 페이징을 적용하고자 하는 쿼리에 맞는 형태로 페이징 쿼리 작성이 필요하다.