Mysql 카테고리 별 카운팅 상위 5개 구하기 질문입니다. 0 6 880

by 김민재 [SQL Query] Mysql 속도 [2022.05.17 10:07:45]


안녕하세요. 현재 쿼리를 짜고 있는데 속도가 잡히지 않아 질문 드립니다..

구하고 싶은 내용은 'DataID 별 각 Category 안에서 가장 많은 name들을 계산'하고 싶습니다.

 

Data_Category { DataId : number, name:string, category:string }

이며 전체적으로 중복하여 입력이 가능합니다. 

 

select dataId, name, category, count(*) as cnt
, rank() over (partition by dataId, category order by count(*) desc) as "ranking"
from data_category
group by dataId, name, category

 

위의 쿼리에서 rank 부분이 있고 없고로 속도가 너무 차이가 납니다.

rank가 없다면 0.0053, 있을 땐 0.5초가 소요가 됩니다.

 

select *, rank() over (partition by A.dataId, A.category order by cnt desc) as "ranking"
from(
  select dataId, name, category, count(*) as cnt
  from data_category
  group by dataId, name, category
) A

위의 쿼리도 거의 동일한 속도가 나옵니다. select 문으로 감싸는 순간 임시테이블 생성으로 인해 느려지고 있습니다.

데이터는 40만건 정도입니다. 

 

데이터Id별 각 카테고리 별 개수가 가장 많은 name들을 5건씩 추출하고 싶은데 쿼리 방향을 못잡겠습니다..

 

쿼리를 짜주시지 않더라도 간단한 힌트만이라도 간절히 부탁드립니다..!

 

감사합니다.

by 마농 [2022.05.17 10:24:54]

1. 조건을 안줘서 느린게 아닐런지?
2. rank 는 동순위 처리를 합니다.
- 5건 과는 조금 다른 의미, 5등의 의미로 5등이 여러명 나오면 5건 이상 나올 수 있습니다.
- 원하는 결과가 5등이 맞는지? 5건이 맞는지?
- 5건이 맞다면? ROW_NUMBER() 를 시용해야 하는데
- 이 때, 정렬순위가 동순위가 나오지 않도록하는 유니크한 추가 정렬 조건이 필요합니다.
- 예) ORDER BY COUNT(*) DESC, name -- 동순위의 경우 이름이 빠른게 우선
3. 별칭은 홑따옴표(비표준)가 아닌 쌍따옴표(표준) 사용 권장

SELECT *
  FROM (SELECT dataId, category, name
             , COUNT(*) cnt
             , RANK() OVER(PARTITION BY dataId, category ORDER BY COUNT(*) DESC) rk
          FROM data_category
         GROUP BY dataId, category, name
        ) a
 WHERE rk <= 5
;

 


by 김민재 [2022.05.17 10:31:39]

답변 감사합니다! 해주신 쿼리로 확인해봤는데

rank가 있을 경우 0.9초, rank가 없을 경우엔 0.2초가 소요가 됩니다. (rk <=5도 제외)

혹시 속도를 조금 더 줄일 수 있는 방법이 있을지 궁금합니다.

 

현재 인덱스는 강제로 (dataId, category, name)에 주고 있습니다!


by 마농 [2022.05.17 10:34:43]

rk <=5 조건을 주셔야죠.


by 김민재 [2022.05.17 10:37:56]

rk <=5 를 제외했다는 건 rank가 없을 때 속도랑 비교하기 위해서 rank를 주석 처리 했을때 같이 했었습니다.

rank가 있을때는 rk<=5도 지우지 않았습니다

 

추가 답변 달아주신 것 확인했습니다.

1. 해당 쿼리로 뷰를 생성할 예정이라 따로 조건은 추가되는 부분이 없을 것 같습니다.!

2. 감사합니다 ROW_NUMBER()로 사용하겠습니다! 상위 5건이 맞습니다

3. 쌍따옴표로 모두 교체하겠습니다


by 마농 [2022.05.17 10:48:07]

1. rank 가 없을 때 빠르게 나오는 것은 부분범위처리 때문일 수 있습니다.
- 40만건이 다 출력되는게 아니라 일부(첫페이지)만 우선 출력
2. 사용되는 3개 항목으로 구성된 인덱스가 있다면 좋을 것 같습니다.


by 김민재 [2022.05.17 11:15:47]

2. 인덱스는 위 댓글에 적어놓았듯이 (dataId, category, name) 이렇게 생성되어있고 explain으로 확인 결과 정상적으로 타고 있습니다.

row_number()를 붙이면 Extra에 Using temporary; Using filesort가 추가되는 것 외에 다른 점은 없습니다

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입