쿼리 튜닝 미션 적용기

쿼리 튜닝 미션 적용기

우테코 레벨 4의 새로운 미션으로 쿼리 최적화 미션이 나왔다. 이 적용기를 써보려 한다.
허접함 주의!!!! 쿼리가 그렇게 좋지 않습니다!!! 모수 테이블이 작아야 한다는 원칙도 잘 지키지 못했다!! 그냥 과정만 봐주세요 ㅎㅎ..

연습 쿼리로 위의 요구사항이 주어졌다. 테이블 정보는 다음과 같았다.

요구사항을 일단 되는대로 쿼리를 짜보았다.

select 연봉높은사원신상.사원번호, 연봉높은사원신상.이름, 연봉높은사원신상.연봉, 사원출입기록.지역, 직급.직급명, 사원출입기록.입출입구분, 사원출입기록.입출입시간 
from (
        select 사원.사원번호, 사원.이름, 연봉사원정보.연봉  
        from 사원 
        inner join (
            select 급여.사원번호, 급여. 연봉 
            from 급여 
            inner join (
                        select 부서관리자.사원번호 
                        from 부서 
                        inner join 부서관리자 on 부서.부서번호 = 부서관리자.부서번호 and 부서관리자.종료일자 = '9999-01-01' 
                        where 비고 = 'active' 
                        ) 부서관리자사원번호 on 부서관리자사원번호.사원번호 = 급여.사원번호 and 급여.종료일자 = '9999-01-01' 
            order by 연봉 desc limit 5
        ) 연봉사원정보 on 사원.사원번호 = 연봉사원정보.사원번호
) 연봉높은사원신상 
inner join 사원출입기록 on 사원출입기록.사원번호 = 연봉높은사원신상.사원번호 and 사원출입기록.입출입구분 = 'O' 
inner join 직급 on 직급.사원번호 = 연봉높은사원신상.사원번호 and 직급.종료일자 = '9999-01-01' 
order by 연봉 desc
;

결과는 2.104 sec 이었다. 요구사항은 50ms 이기 때문에 최적화를 시켜야 한다.

쿼리의 실행계획은 위와 같다.

select_type의 정보는 다음과 같다.

type의 정보는 다음과 같다.

extra의 정보는 다음과 같다.

결론은 최대한 다음과 같이 만들어야 한다.

내 쿼리 정보를 보면 사원출입기록의 테이블 풀 스캔이 엄청난 비용을 차지하고 있는 것 확인할 수가 있다. 이 부분을 고쳐야 할 것 같다.

inner join 사원출입기록 on 사원출입기록.사원번호 = 연봉높은사원신상.사원번호 and 사원출입기록.입출입구분 = 'O'

부분인데, 사원출입기록.사원번호 가 뭔가 문제가 있는 것 같다. 테이블의 인덱스 정보를 확인해봤다.

순번, 사원번호로 인덱스가 생성이 되어있고 사원번호 단일로는 생성이 안되어 있었다.
인덱스 레인지 스캔을 위해서는 인덱스 선두 컬럼이 조건절에 있어야 하는데 말이다.
그래서 사원번호 인덱스를 추가하고 돌려보았다.

말도 안되게 몰려있던 부분이 Unique Key를 통한 탐색으로 해결이 되면서 시간은 0.016 sec으로 대폭 단축되었다.
좌측 하단에도 풀 테이블 스캔이 일어나고 있는데 해당 부분은 부서 테이블에서 비고 컬럼이 인덱스 설정이 안되어 있어서 그렇다.
하지만, 비고 컬럼의 cardinality는 2이기에 인덱스를 굳이 걸어줄 필요성을 크게 느끼지 못했다.

두 번째 연습문제는 이것이었다.

테이블은 다음과 같았다.

첫 번째 요구사항인 Coding as a Hobby는 다음과 같은 결과값을 반환해야 한다.

select hobby, round(count(hobby) / (select count(id) from programmer) * 100, 1) 
from programmer 
group by hobby;

hobby에 인덱스를 걸어주었다. round는 반올림을 해주는 함수다. 두 번째 인수에 1을 넣음으로써 소숫점 첫 번째 자리에서 반올림을 하게끔 하였다.
결과로 0.232sec이 나왔다. (m1 기반 도커에 mysql를 올렸을 때 이슈가 있다고 한다. 정상적인 쿼리다! 우분투 ec2 도커 mysql로 돌리니 0.083이 나왔다.)

두 번째 요구사항인 프로그래머별로 해당하는 병원 이름을 반환하세요.

hospital 테이블에는 hospital_id, name 이 존재, programmer 테이블에는 programmer_id, member_id 가 존재, covid 테이블에는 covid_id, hospital_id, member_id, programmer_id 가 존재한다.

순서대로 row의 수는 32개, 9.8만개, 31만개 이다. 모수 테이블의 크기가 최대한 작게끔 설정해야 빠르다고 하니 고려해서 해야한다.

select programmer.id programmer_id, hospital.name hospital_name 
from programmer 
left join covid on programmer.id = covid.programmer_id 
left join hospital on hospital.id = covid.hospital_id;

우선 이렇게 쿼리를 짰다. 결과는 30 sec 이상이 나왔고 connection이 끊겼다.
테이블 들의 정보를 살펴보니 기본 인덱스인 pk 부터 걸려있지 않았다.
그래서 일단 primary key 부터 모두 걸어주고 다시 쿼리를 실행해 보았다.

alter table hospital add primary key (id);
alter table covid add primary key (id);
alter table programmer add primary key (id);

select programmer.id programmer_id, hospital.name hospital_name 
from programmer 
left join covid on programmer.id = covid.programmer_id 
left join hospital on hospital.id = covid.hospital_id;

여전히 문제가 많아 보인다... duration time 5.967sec, fetch time 2.942sec 의 결과가 나왔다.
fetch time은 페치된 결과를 전송하는 데 걸리는 시간이고, duration time은 쿼리를 실행하는 데에 걸리는 시간이다.
쿼리의 조인문에서 사용하는 값 들을 인덱스로 걸어보자! covid의 hospital_id, covid의 programmer_id를 말이다!!

다시 쿼리를 실행하니 duration/fetch time 0.064/0.022 sec 이 나왔다.

+) hospital 테이블을 모수 테이블로 삼으면 더더욱 빨라진다!!!

세 번째 문제인 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. 로 가보자!

programmer 테이블에서 hobby 컬럼과 years_coding_prof 컬럼을 where 조건을 걸어 가져와야 할 것이다.
이를 위해 인덱스를 추가했다. 인덱스를 추가하기 전에는 풀 테이블 스캔을 하였으나 인덱스를 걸어주니 다음과 같이 실행되었다.

실행 쿼리는 select * from programmer where hobby = "Yes" and years_coding_prof = "0-2 years" ; 이다.

인덱스를 다음과 같이 hobby, years_coding_prof 복합 인덱스로 두었다.
이 경우는 아니긴 하지만 범위 검색 컬럼을 포함한 복합 인덱스 시 유의해야 할 사항은 다음과 같다고 한다.

한 번 확인을 해보자.
사원출입기록 테이블의 입출입시간 컬럼을 인덱스 등록을 했다.

select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-08-25';

그러나 그 결과 풀 테이블 스캔이 일어나고 있었다. 대체 왤까.. ? 그런데 재밌는 점은 이 쿼리다.

select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-25';

동일한 컬럼에 대한 조건인데 이 때에는 인덱스 레인지 스캔을 하고 있었다. 왜 그런 것일까??
그 이유는 풀 테이블 스캔은 순차 i/o 탐색이지만, 인덱스를 통한 스캔은 랜덤 i/o 탐색이기 때문이다.

풀 테이블 스캔일 경우 데이터를 읽기 위해 디스크 헤더의 위치를 옮기는 호출이 딱 1번이다. 데이터 시작 점에서부터 쭉 움직이면 될 것이니 말이다. 하지만 인덱스의 경우는 어떨까?? 인덱스는 인덱스 컬럼으로 정렬이 되어있지만 해당 데이터의 위치는 정렬되어 있지 않기 때문에 계속해서 디스크 헤더의 위치를 움직여야 할 것이다.

따라서 읽을 데이터가 일정량을 넘으면 인덱스보다 풀 테이블 스캔이 유리하기 때문에 위의 예시에서 풀 테이블 스캔으로 돌아간 것이다.
즉, 인덱스는 큰 테이블에서 소량 데이터를 검색할 때 사용한다.
여러 케이스를 해보았다.

/* 입출입시간, 사원번호 단일 인덱스 없는 상황에서 밑의 인덱스를 추가 후 실행 */
create index 번호와출입 on 사원출입기록(사원번호, 입출입시간);

select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-31' and 사원번호 > 20000;
select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-31' and 사원번호 > 499000;
select * from 사원출입기록 where 사원번호 > 20000 and 입출입시간 between '2020-01-19' and '2020-01-31';
select * from 사원출입기록 where 사원번호 > 499900 and 입출입시간 between '2020-01-19' and '2020-01-31';

/* 스캔방식, duration/fetch time
풀테이블 스캔, 0.112/0.115
인덱스 레인지 스캔, 0.032/0.000017
풀테이블 스캔, 0.079/0.082
인덱스 레인지 스캔, 0.0090/0.000016  */

create index 출입과번호 on 사원출입기록(입출입시간, 사원번호);

select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-31' and 사원번호 > 20000;
select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-31' and 사원번호 > 499000;
select * from 사원출입기록 where 사원번호 > 20000 and 입출입시간 between '2020-01-19' and '2020-01-31';
select * from 사원출입기록 where 사원번호 > 499900 and 입출입시간 between '2020-01-19' and '2020-01-31';

/*
인덱스 레인지 스캔, 0.040/0.020
인덱스 레인지 스캔, 0.107/0.000016
인덱스 레인지 스캔, 0.032/0.025
인덱스 레인지 스캔, 0.035/0.000013

복합 인덱스 시에 범위 검색컬럼을 뒤에 둬야 하는게 보인다.
select * from 사원출입기록 where 입출입시간 between '2020-01-19' and '2020-01-31' and 사원번호 > 499000;
출입과번호 인덱스 시에 위 쿼리의 결과는 0.107/0.000016 이다.

반면, select * from 사원출입기록 where 사원번호 > 499900 and 입출입시간 between '2020-01-19' and '2020-01-31';
번호와출입 시에는 0.0090/0.000016 이다. 확실히 차이가 난다!!

그리고 (사원번호, 입출입시간) 과 같은 복합인덱스를 사용할 시에는 사원번호에 대한 단일 인덱스를 둘 필요가 없다.
(사원번호, 입출입시간) 인덱스를 대신 사용하기 때문이다. 하지만 입출입시간에 대한 단일 인덱스 효과를 갖지는 못한다.
즉, 복합 인덱스의 선두컬럼을 이용할 때의 얘기다.

이제 본론으로 돌아와서 예제 문제를 다시 풀어보자.

select covid.id covid_id, hospital.name hospital_name, hobbyProgrammer.hobby, hobbyProgrammer.dev_type, hobbyProgrammer.years_coding_prof  
from (
    select id, hobby, dev_type, years_coding_prof 
  from programmer 
  where hobby = "Yes" and (years_coding_prof = "0-2 years" or student <> 'No') 
) hobbyProgrammer 
left join covid on covid.programmer_id = hobbyProgrammer.id 
left join hospital on covid.hospital_id = hospital.id 
order by hobbyProgrammer.id;

요구사항에 맞게끔 쿼리를 짰다. 그 결과 duration/fetch time은 0.050/0.094 sec이 나왔다.
우분투 환경에서는 0.0099

실행 계획도 인덱스를 이용한 것을 알 수가 있었다.

그 다음 문제의 요구사항은 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)다.

create index I_age on member(age);

select covid.stay, count(twenty_member.id)  
from (
 select id from member where age between 20 and 29 
) twenty_member 
inner join programmer on programmer.member_id = twenty_member.id 
inner join covid on covid.member_id = twenty_member.id 
group by covid.stay;

먼저 위와 같은 쿼리를 진행하였다. 결과는 너무 오래 걸려서 도중에 커넥션이 끊겼다... 인덱스를 더 걸어보겠다.

/* covid의 member_id,stay programmer의 member_id 에 인덱스를 걸었다. */

select covid.stay, count(twenty_member.id)  
from (
 select id from member where age between 20 and 29 
) twenty_member 
inner join programmer on programmer.member_id = twenty_member.id 
inner join covid on covid.member_id = twenty_member.id 
group by covid.stay;

이 결과 0.915/0.000018 sec이 걸렸다. 실행계획은 다음과 같았다.

어떻게 이렇게 줄었을까? covid의 stay 컬럼의 인덱스 추가가 가장 큰 영향을 미쳤을 것이다.
**인덱스는 항상 정렬 상태를 유지하므로 인덱스 순서에 따라 ORDER BY, GROUP BY 를 위한 소트 연산을 생략``할 수 있기 때문이다.

그러나 여전히 1초를 넘어간다. programmer 테이블에서 인덱스 풀 스캔을 하는 것을 어떻게든 최적화 시켜야 한다.
** 그런데 지금 다시 보니 서울대병원에 다닌다는 조건이 있다!! + India 조건도 빠졌다!!** 어차피 최적화 시켜야 하는거 쿼리를 바꾸면서 다시 최적화를 시켜보자!

/* programmer의 member_id country, covid의 member_id stay 가 인덱스로 걸린 상태다.*/
select covid.stay, count(twenty_member.id)  
from (
 select id from member where age between 20 and 29 
) twenty_member 
inner join programmer on programmer.member_id = twenty_member.id and programmer.country = "india" 
inner join covid on covid.member_id = twenty_member.id 
left join hospital on covid.hospital_id = hospital.id and hospital.name = "서울대병원" 
group by covid.stay;

위 쿼리의 결과로 1.785/0.000052 sec이 나왔다. 모수 테이블의 사이즈를 줄이는 방식을 먼저 살펴보았다.

select count(*) from member where age between 20 and 29;
select count(*) from programmer where country = "india";

전자는 22745, 후자는 13721 이었다. 따라서 후자를 모수 테이블로 삼고 다시 돌려보았다.

select covid.stay, count(*) 
from (
    select programmer.member_id from programmer where country = "india" 
) indian 
inner join member on member.id = indian.member_id and member.age between 20 and 29 
inner join covid on covid.member_id = indian.member_id 
inner join hospital on covid.hospital_id = hospital.id and hospital.name = "서울대병원" 
group by covid.stay;

결과는 1.644/0.000007 sec 이었다. 아주 미묘하게 줄었다. 이번에는 programmer의 테이블에 걸려있는 member_id, country에 대한 단일 인덱스를 (country, member_id) 의 복합 인덱스로 변경해 보고 돌려보았다.

0.221/0.000030 sec 이었다. 그래서 이상하게 생각해서 복합 인덱스를 지우고 (country) 단일 인덱스로 두었더니 다시 시간초가 늘어났다. 다시 한 번 생각해보니 그 원인이 파악이 됐다. 바로 커버링 인덱스 때문이었다. 만약 Member 테이블에 (age, id) 복합 인덱스를 걸지 않고 age만 걸고 id만 select 해올 때에도 커버링 인덱스가 실행된다. 그 이유는 클러스터 인덱스와 넌클러스터 인덱스를 학습해보면 깨달을 것이다.

커버링 인덱스란 인덱스 스캔과정에서 얻은 정보만으로 처리할 수 있어 테이블 액세스가 발생하지 않는 쿼리 를 의미한다.
country를 where문에서 이용하고 member_id를 바로 인덱스에서 찾았기 때문에 테이블 엑세스가 발생하지 않은 것이다!!

그 증거로 실행 결과에서도 확인할 수가 있었다.

첫 번째 row의 extra를 보면 Using index; 가 나와있는데 이것이 바로 커버링 인덱스가 일어났을 때 나타난다!
커버링 인덱스를 더 활용하고자 In 쿼리를 활용했다.

select covid.stay, count(*) 
from (
    select member_id from programmer where country = "india" 
) indian 
inner join covid on covid.member_id = indian.member_id 
left join hospital on covid.hospital_id = hospital.id and hospital.name = "서울대병원" 
where indian.member_id in (
  select id 
  from member 
  where age between 20 and 29
)
group by covid.stay;

그러나 시간 차이는 없다고 말할 수 있을 정도였다. 그리고 서브 쿼리보다는 조인문을 사용하는 것이 대부분의 경우 성능이 좋다하니 join 형식 습관도 기를 겸 이전의 쿼리를 사용하겠다.

알고보니 m1 속도 이슈가 있었다. 윈도우나 인텔 맥으로 돌릴 시에는 속도가 확연히 빠르다고 한다!
우분투 ec2로 돌려보니 0.048이 나왔다.

서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요. (user.Exercise)

select programmer.exercise, count(programmer.id) 
from programmer 
inner join member on member.id = programmer.member_id and age between 30 and 39 
inner join covid on covid.programmer_id = programmer.id 
inner join hospital on covid.hospital_id = hospital.id and hospital.name = "서울대병원" 
group by programmer.exercise;

Member의 age 컬럼과 Covid에 programmer_id가 인덱스 걸려있는 상태로 돌려보았고,
0.336sec 의 결과를 얻을 수 있었다. 우분투 환경에서 돌려보니 0.056이 나온 것을 확인할 수가 있었다.

쿼리튜닝 시에 유의할 점 및 특이점을 정리하자면,

1. 인덱스를 가공하면 인덱스를 타지 못해서 무용지물이다.
2. 인덱스를 거는 순서를 신경써야한다.(인덱스 (a) == 인덱스(a,b) 이다. 중복 인덱스 조심하자)
3. group by나 order by에 인덱스를 걸게되면 연산에 필요한 정렬 과정을 생략할 수 있다(인덱스는 이미 정렬되어 있기 때문)
4. 인덱스 스캔과정에서 얻은 정보만으로 처리할 수가 있는데 이를 커버링 인덱스라고 한다.
5. 복합 인덱스를 사용할 시에는 범위 검색에 관한 컬럼을 뒤쪽에 두어야 한다.
6. 데이터가 많은 테이블을 드라이빙 테이블로 삼아야 한다.

+)
미션 제출 후 조앤의 피드백 중 count(*) 와 count(column) 의 차이점이 무엇이냐에 대한 피드백이 있어서 조사해보았다.

count()는 row의 수를 리턴해주는 함수다.
count(*), count(1), count(column), count(distinct column name) 의 차이를 알아보자.

count(*) VS count(1)

차이점이 없다. count(*) 는 null value를 포함해서 모든 row를 센다.
count(1) 은 첫 번째 컬럼만 count 하는 것이 아니다.

count(*) VS count(column)

count(column) 은 not null인 row에 대해서만 count 한다. count(*) 는 다 센다.

count(column) VS count(distinct column)

count(distinct column) 은 중복이면 count 하지 않고, 중복되지 않은 것만 count 한다.

---

RFERENCE

CU의 인덱스 강의

https://m.blog.naver.com/loleego/221620178986

https://learnsql.com/blog/difference-between-count-distinct/