데이터베이스 질문과 답변
조회 시 페이지 맨 하단에
총 학생수를 보여주는 데이터 쿼리 입니다.
형태는 총 학생 수, 학원에 소속된 반 수, 현재 재학중인 학교 수 입니다.
예)
총 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뜨는 쿼리는 쿼리 튜닝이 필요하다는 글들을 봐서
튜닝을 해보려고 하는데 어디서부터 수정해나가 할까요?
서로 다른 성격의 쿼리를 굳이 원쿼리로 할 필요가 없습니다.
목록성 쿼리와 합계 쿼리를 분리해서 두번 수행하세요.
굳이 원쿼리로 하려고 한다면?
현재 조인 후 카운트를 카운트 후 조인으로 개선할 수 있구요.
추가로 테이블 두번 읽는 것을 한번만 읽도록 개선 가능합니다.
기타 사항으로 비표준 쿼리가 많이 사용되고 있는데
따옴표는 쌍따옴표를 홑따옴표로 바꿔주시고
IF 문은 CASE 문으로 대체
COUNT 와 같은 예약어를 알리아스로 사용하는것 지양
중학교 조건은 '중'이 들어가는 이름의 고등학교가 제외될 가능성이 있습니다.
마찬가지로 '고'가 들어가는 이름의 중학교가 제외될 가능성이 있습니다.
마농님 답변 감사합니다
달아주신 쿼리도 정말 감사드립니다.
질문이외에 다른 부분도 알려주셔서 감사해요
학교 조건으로에 대해 말씀해주신 부분은 저도 인지하고 있습니다.(좋지않는 방법이죠 ㅜㅜ)
고, 중으로 하는 이유는 주변 학교들이 딱 정해져 있어서 위와같이 쿼리를 계획하게 되었습니다.
게시글 외에 다른 부분이 궁금한것이 있는데
like 시 '문자%' 일때만 인덱스를 탄다고 하는데
이런경우는 '문자'로 시작된 문자열만 찾는데
문자열 안에 포함된 경우를 찾으려면 '%a문자%' 사용해야 하는데
이런경우에는 인덱스를 포기하고 사용해야 할까요?
인덱스 풀스캔은 가능할 수도 있습니다.
위 질문의 예는 부정조건인데다가 % 조건으로 인덱스를 태우는게 오히려 불리할 수 있습니다.
-- 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 '%중%'
;
) 버튼을 클릭하여 작성 하시면 됩니다.