개선방법 문의 0 4 1,469

by 정수리형 [SQL Query] [2023.05.22 10:21:48]


조회 시 페이지 맨 하단에

총 학생수를 보여주는 데이터 쿼리 입니다.

형태는 총 학생 수, 학원에 소속된 반 수, 현재 재학중인 학교 수 입니다.

예)

총 80명 (청:20, 지,40명, 성:20명) (일고:40명, 이고:40명)
 

table ACT_STUDENT_TB 

st_pk int auto_increament primary key
go school varchar(45)
st_yn  char(1)
leave_date int(11)
cls_name char(2)
index st_yn(st_yn, leave_date, st_grade, go_school, cls_name)
index cls_name

SELECT GO_SCHOOL, COUNT(*), B.* FROM ACT_STUDENT_TB AS A
LEFT JOIN
 (SELECT ST_YN, COUNT(*) AS COUNT, SUM(IF(CLS_NAME="청",1,0)) AS CLS_1, SUM(IF(CLS_NAME="형",1,0)) AS CLS_2, SUM(IF(CLS_NAME="지",1,0)) AS CLS_3 FROM ACT_STUDENT_TB WHERE ST_YN = "Y" and LEAVE_DATE = 0 AND GO_SCHOOL NOT LIKE "%중%") AS B 
ON A.ST_YN = B.ST_YN 
WHERE 
A.ST_YN = 'Y' AND A.LEAVE_DATE = 0 AND GO_SCHOOL NOT LIKE "%중%" //SY_YN ='Y' AND LEAVE_DATE = 0 고정 조건, GO_SCHOOL는 고등학생/중학생 조회에 따라 변경되는 조건 입니다.

GROUP BY GO_SCHOOL;

위와 같이 했을 경우 원하는 형태의 데이터는 나오긴하는데

 

explain  시 

id           select_type table  partitions   type   possible_keys   key     key_len   ref                    rows       filtered   Extra

1             primary      A          null      ref      ST_YN         ST_YN     7     CONST,CONST      183          88.89   Using where, using index, using temporary

2             primary     null      all     null              null     null         null                1              100   Using where, using join buffer(hash join)

3             derived      table명     null     ref      ST_YN         ST_YN    7      CONST,CONST      183          88.89   Using where, using index

 

위와 같이 결과가 출력됩니다.

group by를 쓰다보니 using temporary가 출력되고

join  시 join buffer도 출력되더라구요

 

나름 성능 개선 해보기 위해 구글링을 해보니

usring temporary, join buffer뜨는 쿼리는 쿼리 튜닝이 필요하다는 글들을 봐서

튜닝을 해보려고 하는데 어디서부터 수정해나가 할까요?

 

by 마농 [2023.05.22 11:02:12]

서로 다른 성격의 쿼리를 굳이 원쿼리로 할 필요가 없습니다.
목록성 쿼리와 합계 쿼리를 분리해서 두번 수행하세요.
굳이 원쿼리로 하려고 한다면?
현재 조인 후 카운트를 카운트 후 조인으로 개선할 수 있구요.
추가로 테이블 두번 읽는 것을 한번만 읽도록 개선 가능합니다.

기타 사항으로 비표준 쿼리가 많이 사용되고 있는데
따옴표는 쌍따옴표를 홑따옴표로 바꿔주시고
IF 문은 CASE 문으로 대체
COUNT 와 같은 예약어를 알리아스로 사용하는것 지양

중학교 조건은 '중'이 들어가는 이름의 고등학교가 제외될 가능성이 있습니다.
마찬가지로 '고'가 들어가는 이름의 중학교가 제외될 가능성이 있습니다.


by 정수리형 [2023.05.22 11:31:57]

마농님 답변 감사합니다

달아주신 쿼리도 정말 감사드립니다.

질문이외에 다른 부분도 알려주셔서 감사해요

학교 조건으로에 대해 말씀해주신 부분은 저도 인지하고 있습니다.(좋지않는 방법이죠 ㅜㅜ)

고, 중으로 하는 이유는 주변 학교들이 딱 정해져 있어서 위와같이 쿼리를 계획하게 되었습니다.

게시글 외에 다른 부분이 궁금한것이 있는데

like 시  '문자%' 일때만 인덱스를 탄다고 하는데

이런경우는 '문자'로 시작된 문자열만 찾는데

문자열 안에 포함된 경우를 찾으려면 '%a문자%' 사용해야 하는데

이런경우에는 인덱스를 포기하고 사용해야 할까요?


by 마농 [2023.05.22 11:34:41]

인덱스 풀스캔은 가능할 수도 있습니다.
위 질문의 예는 부정조건인데다가 % 조건으로 인덱스를 태우는게 오히려 불리할 수 있습니다.


by 마농 [2023.05.22 11:03:57]
-- 1. 조인후 카운트 -> 카운트 후 조인
SELECT *
  FROM (SELECT go_school
             , COUNT(*) cnt
          FROM act_student_tb
         WHERE st_yn = 'Y'
           AND leave_date = 0
           AND go_school NOT LIKE '%중%'
         GROUP BY go_school
        ) a
 CROSS JOIN
       (SELECT COUNT(*) tot
             , COUNT(CASE cls_name WHEN '청' THEN 1 END) cls_1
             , COUNT(CASE cls_name WHEN '형' THEN 1 END) cls_2
             , COUNT(CASE cls_name WHEN '지' THEN 1 END) cls_3
          FROM act_student_tb
         WHERE st_yn = 'Y'
           AND leave_date = 0
           AND go_school NOT LIKE '%중%'
        ) b
;

-- 2. 테이블 두번 읽는 것을 한번만 읽도록 개선
SELECT go_school
     , COUNT(*) cnt
     , SUM(COUNT(*)) OVER() tot
     , SUM(COUNT(CASE cls_name WHEN '청' THEN 1 END)) OVER() cls_1
     , SUM(COUNT(CASE cls_name WHEN '형' THEN 1 END)) OVER() cls_2
     , SUM(COUNT(CASE cls_name WHEN '지' THEN 1 END)) OVER() cls_3
  FROM act_student_tb
 WHERE st_yn = 'Y'
   AND leave_date = 0
   AND go_school NOT LIKE '%중%'
 GROUP BY go_school
;

-- 3. 쿼리 분리
-- 3.1. 목록 쿼리 --
SELECT go_school
     , COUNT(*) cnt
  FROM act_student_tb
 WHERE st_yn = 'Y'
   AND leave_date = 0
   AND go_school NOT LIKE '%중%'
 GROUP BY go_school
;
-- 3.2. 합계 쿼리 --
SELECT COUNT(*) tot
     , COUNT(CASE cls_name WHEN '청' THEN 1 END) cls_1
     , COUNT(CASE cls_name WHEN '형' THEN 1 END) cls_2
     , COUNT(CASE cls_name WHEN '지' THEN 1 END) cls_3
  FROM act_student_tb
 WHERE st_yn = 'Y'
   AND leave_date = 0
   AND go_school NOT LIKE '%중%'
;

 

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