KJunseo/sql-tuning

🚀 조회 성능 개선하기

실습 환경

M1 Mac에서 workbench로 EC2에 띄워져있는 docker mysql에 연결하여 진행하였습니다.

A. 쿼리 연습

-- tuning 데이터베이스 사용
USE tuning;

활동중인(Active) 부서의 현재 부서관리자 중 연봉 상위 5위안에 드는 사람들이 최근에 각 지역별로 언제 퇴실했는지 조회해보세요. (사원번호, 이름, 연봉, 직급명, 지역, 입출입구분, 입출입시간)

1. 쿼리 작성만으로 1s 이하로 반환한다.

쿼리

SELECT
    `활동중인 부서의 현재 부서관리자 정보`.사원번호, `활동중인 부서의 현재 부서관리자 정보`.이름, `활동중인 부서의 현재 부서관리자 정보`.연봉, `활동중인 부서의 현재 부서관리자 정보`.직급명, `입출입 정보`.입출입시간, `입출입 정보`.지역, `입출입 정보`.입출입구분
FROM(
    SELECT
        `활동중인 부서의 현재 부서관리자 사원번호`.사원번호, 사원.이름, 급여.연봉, 직급.직급명
    FROM(
        SELECT
            사원번호
        FROM (SELECT 부서번호 FROM 부서 WHERE 비고 = 'active') AS 부서
        JOIN (SELECT 사원번호, 부서번호 FROM 부서관리자 WHERE 종료일자 = '9999-01-01') AS 부서관리자
            ON 부서.부서번호 = 부서관리자.부서번호
    ) AS `활동중인 부서의 현재 부서관리자 사원번호`
    JOIN (SELECT 사원번호, 이름 FROM 사원) AS 사원
        ON `활동중인 부서의 현재 부서관리자 사원번호`.사원번호 = 사원.사원번호
    JOIN (SELECT 사원번호, 직급명 FROM 직급 WHERE 종료일자 = '9999-01-01') AS 직급
        ON `활동중인 부서의 현재 부서관리자 사원번호`.사원번호 = 직급.사원번호
    JOIN (SELECT 사원번호, 연봉 FROM 급여 WHERE 종료일자 = '9999-01-01') AS 급여
         ON `활동중인 부서의 현재 부서관리자 사원번호`.사원번호 = 급여.사원번호
    ORDER BY 연봉 DESC
    LIMIT 0, 5
) AS `활동중인 부서의 현재 부서관리자 정보`
JOIN (SELECT 사원번호, 입출입시간, 지역, 입출입구분 FROM 사원출입기록 WHERE 입출입구분 = 'O') AS `입출입 정보`
    ON `입출입 정보`.사원번호 = `활동중인 부서의 현재 부서관리자 정보`.사원번호
ORDER BY 연봉 DESC;

실행결과

2. 인덱스 설정을 추가하여 50 ms 이하로 반환한다.

실행계획 분석

• 많은 rows를 스캔하고 있는 사원출입기록에 인덱스를 걸어주었습니다. 사원번호를 통해 JOIN을 하고 있기 때문에 JOIN 연결 키에 아래와 같이 인덱스를 만들어주었습니다.

CREATE INDEX `idx_사원출입기록_사원번호` on `tuning`.`사원출입기록` (사원번호);

실행결과

B. 인덱스 설계

-- subway 데이터베이스 사용
USE subway;

* 요구사항

• 주어진 데이터셋을 활용하여 아래 조회 결과를 100ms 이하로 반환

  • Coding as a Hobby 와 같은 결과를 반환하세요.

  • 각 프로그래머별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)

  • 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)

  • 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)

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

1. Coding as a Hobby 와 같은 결과를 반환하세요.

쿼리 & 결과

SELECT
    hobby, 
    ROUND(COUNT(*) * 100 / total.count, 1) AS percent
FROM
    programmer
JOIN
    (SELECT COUNT(*) AS count FROM programmer) AS total
GROUP BY hobby, count;

실행결과(before)

개선하기

현재 programmer 테이블을 hobby를 통해 구분짓고 있습니다. 이때 hobby에 대한 인덱스가 걸려있지 않기 때문에 전체 테이블을 Full Scan한 후, 필터를 걸어준다고 생각이됩니다. 따라서 hobby에 대해 인덱스를 만들어줬습니다.

CREATE INDEX `idx_programmer_hobby`  ON `subway`.`programmer` (hobby);

추가적으로 전체 프로그래머 수를 구하기 위한 서브쿼리는 hobby 인덱스가 아닌 클러스터링 인덱스를 사용하면 검색이 빠를 것이라 생각하여 programmer 테이블의 id에 pk와 unique를 걸어줬습니다.

ALTER TABLE `subway`.`programmer` 
CHANGE COLUMN `id` `id` BIGINT(20) NOT NULL,
ADD PRIMARY KEY (`id`),
ADD UNIQUE INDEX `id_UNIQUE` (`id` ASC);

실행결과(after)

hobby 인덱스, id 인덱스를 사용하여 Table Full Scan에서 Index Full Scan으로 변경된 것을 볼 수 있습니다. 다만 Query Cost는 높아졌습니다.

2. 프로그래머별로 해당하는 병원 이름을 반환하세요.

쿼리 & 결과

SELECT
  c.programmer_id, hospital.name AS hospital_name
FROM
    (SELECT hospital_id, programmer_id FROM covid) AS c
JOIN hospital
    ON hospital.id = c.hospital_id
JOIN (SELECT id FROM programmer) AS p
    ON p.id = c.programmer_id

실행결과(before)

개선하기

실행계획을 보면 hospital - covid - programmer 순으로 쿼리가 실행됩니다. 먼저 hospital의 Full Table Scan을 인덱스를 걸어 바꿔줬습니다. hospital은 id를 통해 비교하고 있기 때문에 id 칼럼을 pk, unique로 두어 인덱스를 걸어줬습니다.

ALTER TABLE `subway`.`hospital` 
CHANGE COLUMN `id` `id` INT(11) NOT NULL ,
ADD PRIMARY KEY (`id`),
ADD UNIQUE INDEX `id_UNIQUE` (`id` ASC);

이후 실행계획을 보면 실행 순서가 covid - hospital - programmer 순으로 바뀐걸 볼 수 있습니다.

covid는 여전히 Full Table Scan을 하고 있기때문에 covid에도 인덱스를 걸어줬습니다. programmer의 id와 programmer_id를 통해, hospital의 id와 hospital_id를 통해 JOIN하고 있고 programmer의 id와 hospital의 id는 이미 인덱스로 등록되어 있으므로, (programmer_id, hospital_id) 인덱스를 만들어줬습니다.

❓ 실행계획을 보면 hospital이 먼저 실행되기 때문에 covid 인덱스를 (hospital_id, programmer_id) 순으로 만들어야되지 않을까 라고 생각했었는데, 이 순서로 하니 동작하지 않았습니다. 왜 programmer_id를 인덱스로 넣어야지 잘 동작하는지 모르겠습니다. 😭

다시 처음부터 해보니 (hospital_id, programmer_id)도 정상 동작했습니다.(정신이 없었나ㅎ..😭) 다만 이 경우 hospital은 Full Table Scan을 하게 됩니다. 옵티마이저에 의해 hospital을 먼저 탐색하게 되어 그런 것 같습니다. 또 order by를 해주지 않는 이상 hospital_id를 기준으로 정렬이 되어 programmer_id로 정렬한 결과를 보지 못합니다. 반면에 (programmer_id, hospital_id)로 인덱스를 걸어주면 programmer_id로 정렬된 값이 잘 나오게 됩니다.

CREATE INDEX `idx_covid_programmer_id_hospital_id`  ON `subway`.`covid` (programmer_id, hospital_id);

실행결과(after)

Table Full Scan이 없어진 것을 볼 수 있습니다.

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

쿼리 & 결과

SELECT
  p.id, hospital.name AS hospital_name
FROM
  (SELECT hospital_id, programmer_id FROM covid WHERE programmer_id IS NOT NULL) AS c
    JOIN (SELECT id FROM programmer WHERE (hobby = 'Yes' AND student LIKE 'Yes%') OR years_coding = '0-2 years') AS p
        ON c.programmer_id = p.id
    JOIN hospital
        ON hospital.id = c.hospital_id

실행결과

이전 문제에서 idx_covid_programmer_id_hospital_id와 hospital pk를 등록했었기 때문에 결과가 잘 나왔습니다. 추가적으로 programmer의 Full Table Scan을 바꿔보고 싶었으나 OR 조건을 사용하고 있어서 Full Scan을 할 수 밖에 없는 것 같습니다.

4. 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요.

쿼리 & 결과

SELECT
    c.stay AS period, COUNT(c.id) AS number_of_people
FROM (SELECT id, hospital_id, member_id, programmer_id, stay FROM covid) AS c
JOIN (SELECT id FROM hospital WHERE name = '서울대병원') AS seoul_hospital
	ON seoul_hospital.id = c.hospital_id
JOIN (SELECT id FROM member WHERE age BETWEEN 20 AND 29) AS twenties
	ON twenties.id = c.member_id
JOIN (SELECT id FROM programmer WHERE country = 'India') AS indian
     ON indian.id = programmer_id
GROUP BY period

실행결과(before)

개선하기

실행계획을 보면 programmer - covid - member - hospital 순으로 쿼리가 실행됩니다. 먼저 programmer의 Full Table Scan을 없애주기 위해 인덱스를 걸어줬습니다. programmer의 경우 WHERE 절에서 country를 사용하고 있으므로 country에 인덱스를 걸어줬습니다.

CREATE INDEX `idx_programmer_country` ON `subway`.`programmer` (country);

이후 실행계획을 보면 programmer는 인덱스를 사용하게 변한걸 볼 수 있습니다.

이어서 hospital에도 인덱스를 걸어줬습니다. hospital은 WHERE절에서 name을 사용하고 있으므로 name에 인덱스를 걸어줬습니다.

이때 name이 현재 TEXT 타입이어서 인덱스를 걸 수 없기 때문에 먼저 name의 타입을 varchar로 변경해주고 인덱스를 만들어줬습니다.

ALTER TABLE `subway`.`hospital` 
CHANGE COLUMN `name` `name` VARCHAR(255) NULL DEFAULT NULL;

CREATE INDEX `idx_hospital_name` ON `subway`.`hospital` (name);

실행계획을 보면 두 개의 인덱스를 추가하고 나서 실행 순서가 바뀌면서 멀쩡하던 covid가 Full Table Scan으로 변했습니다.(옵티마이저에 의해 실행순서가 바뀌면서 인덱스를 사용하지 못하게 되지 않았을까 추측하고 있습니다. 혹시 알고 계시다면 알려주세요!🙏)

covid는 먼저 hospital_id, programmer_id, member_id를 통해 JOIN 하기 때문에 (hospital_id, programmer_id, member_id)인덱스를 만들어줬습니다. (복합 인덱스는 지정한 순서대로 조건문을 타야지 인덱스가 적용되어 실행계획을 보고 hospital_id, programmer_id, member_id 순으로 복합인덱스를 생성했습니다. 제 생각에는 이 인덱스가 생긴 후에도 옵티마이저에 의해 실행순서의 변화가 없어서 잘 동작한 것 같습니다.)

CREATE INDEX `idx_covid_hospital_id_programmer_id_member_id`  ON `subway`.`covid` (hospital_id, programmer_id, member_id);

이후 실행계획을 보면 Table Full Scan이 없어진 것을 볼 수 있습니다.

추가적으로 실행계획을 보면 member의 filtered가 비효율적인 것을 볼 수 있습니다. member에서는 age에 BETWEEN 구문을 쓰고 있기 때문에 age에 인덱스를 걸어 정렬되도록 하였습니다.

CREATE INDEX `idx_member_age` ON `subway`.`member` (age);

실행결과(after)

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

쿼리 & 결과

SELECT
    p.exercise AS exercise, COUNT(c.id) AS number_of_exercises
FROM (SELECT id, hospital_id, member_id, programmer_id FROM covid) AS c
JOIN (SELECT id FROM hospital WHERE name = '서울대병원') AS seoul_hospital
    ON seoul_hospital.id = c.hospital_id
JOIN (SELECT id FROM member WHERE age BETWEEN 30 AND 39) AS thirties
    ON thirties.id = c.member_id
JOIN (SELECT id, exercise FROM programmer) AS p
    ON p.id = programmer_id
GROUP BY p.exercise

실행결과

이전 과정에서 만들었던 인덱스들이 잘 동작하여 따로 인덱스를 추가하지 않았습니다.