[안정적인 서비스 만들기 3단계] -조회 성능 개선하기

[mindock]

• 주어진 데이터셋을 활용하여 아래 조회 결과를 100ms 이하로 반환
  1. Coding as a Hobby 와 같은 결과를 반환하세요.
  2. 환자별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)
  3. 프로그래밍이 취미인 학생 혹은 주니어(0-2년)들이 다닌 병원 이름을 반환하고 user.id 기준으로 정렬하세요. (covid.id, hospital.name, user.Hobby, user.DevType, user.YearsCoding)
  4. 서울대병원에 다닌 20대 India 환자들을 병원에 머문 기간별로 집계하세요. (covid.Stay)
  5. 서울대병원에 다닌 30대 환자들을 운동 횟수별로 집계하세요. (user.Exercise)

[mindock]

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

---

SELECT Hobby, (COUNT(*) * 100 / (SELECT COUNT(*) FROM programmer)) as Percentage 
FROM programmer 
GROUP BY Hobby;

[결과]

172ms 걸렸다.

💡 GROUP BY에 사용된 컬럼 값으로 정렬이 되어 있으면, 동일한 값을 동일한 그룹으로 묶는 작업을 빠르게 진행할 수 있다. INDEX는 항상 키 컬럼 순으로 정렬된 상태를 유지되기 때문에, GROUP BY에 사용되는 컬럼을 INDEX를 생성하면, 빠르게 검색할 수 있다.

(1) programmer의 Hobby 필드에 인덱스 추가

92ms로 줄어들었다.

[mindock]

환자별로 해당하는 병원 이름을 반환하세요. (covid.id, hospital.name)

---

SELECT C.id, H.name FROM covid as C
JOIN hospital as H
ON C.Hospital_code = H.code;

16ms 걸렸다.

(1) covid의 id, hospital의 code에 PK 추가

(2) covid의 Hospital_code 필드 인덱스 설정

💡 hospital 인덱스를 생성할 때, code, name 순으로 설정한다면, 인덱스 스캔한 결과만 사용해 hospital 테이블에 접근하지 않아도 된다.(covered Index)

(3) hostpital의 인덱스 수정 (code, name 순서)

SELECT 앞에 EXPLAIN을 붙여 데이터를 어떻게 가져오는지 확인하면, hospital 테이블의 Extra 부분에 Using index로 covered Index가 적용되었음을 확인할 수 있다.

---

조인 컬럼에 인덱스를 추가할 때 차이점

1. 두 테이블 모두 인덱스를 설정하지 않은 경우 옵티마이저가 rows 수가 적은 테이블을 Driving Table로 설정한다. BNL(Block Nested Loop) 방식을 사용하지 않으면, Driving 테이블의 데이터 수만큼 Driven 테이블을 full scan을 해야한다. 하지만, BNL 방식은 프로세스 내 버퍼(조인 버퍼)에 Driving 테이블의 데이터를 저장해놓고 Driven 테이블을 스캔하면서 조인 버퍼를 탐색한다. 그래서 빠르게 탐색할 수 있다. 여기서는 rows 수가 작은 H(hospital)이 Driving Table이 되고, 해당 정보는 조인 버퍼에 저장된다. C(covid) 테이블을 전체 스캔을 진행하면서 조인 버퍼에 해당 값이 있는지 탐색하게 된다.

2. 데이터 적은 테이블에 인덱스를 설정한 경우 H(hosptial) 테이블에 index가 설정되었고, index scan을 할 수 있다. C(covid) 테이블은 full scan으로 탐색한다. 즉, 인덱스가 설정되지 않은 C 테이블이 Driving 테이블이 되며 full scan을 진행하고, H 테이블은 Driven 테이블이 되며 index scan을 진행한다.

3. 데이터 많은 테이블에 인덱스를 설정한 경우 인덱스가 설정되지 않은 H 테이블이 Driving 테이블이 되며 full scan을 진행하고, C 테이블은 Driven 테이블이 되며 index scan을 진행한다.

4. 두 테이블 모두 인덱스를 설정한 경우 H와 C 테이블 모두 index 설정이 되어있지만, 실제로 사용된 인덱스는 C의 인덱스이다. (key 부분을 참고) 옵티마이저가 규칙에 따라 우선순위가 높은 테이블을 Driving 테이블로 선정한다.

💡 join 컬럼 중 데이터가 많은 테이블의 조인 컬럼에 index를 거는 것이 효율적이다.

[mindock]

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

---

SELECT U.id, H.name
FROM (
     SELECT id FROM programmer
     WHERE Hobby = "Yes" AND (Student LIKE "Yes%" OR YearsCodingProf = "0-2 years")
) as U
JOIN (
     SELECT covid.id, hospital.name FROM covid
     JOIN hospital
     ON covid.Hospital_code = hospital.code
) as H
ON U.id = H.id
ORDER BY U.id
LIMIT 0, 10;

작업이 너무 오래 걸려 LIMIT으로 제한했지만, 데이터를 가져오지 못한다.

(1) hospital의 code, name 필드에 인덱스 추가

💡 programmer 테이블에는 Hobby 필드에만 인덱스를 생성한다. WHERE절의 OR 조건은 Table Full Scan을 하게 된다. (index를 타지 않는다.) 해당 부분은 UNION을 사용할 수도 있지만, UNION 절로 묶을 경우엔 임시테이블을 새로 작성하고 TABLE FULL SCAN 비용이 발생한다.

(2) programmer의 Hobby 필드에 인덱스 추가

(3) covid의 id 필드에 PK 설정

위와 같은 설정을 했지만, 1422ms 걸렸다... 😢

programmer 테이블에 id 필드에 인덱스를 추가하면, id기준으로 정렬하는 시간을 줄일 수 있다.

(4) programmer의 id 필드에 PK 설정

16ms로 많은 시간을 줄일 수 있었다.

[mindock]

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

---

서울대병원을 다닌 환자 id, Stay 테이블, 20대 India 환자 id 테이블 두 테이블을 JOIN해서 결과를 도출한다.

SELECT P.Stay, Count(*) 
FROM (
    SELECT covid.id, covid.Stay, hospital.name FROM covid
    JOIN hospital ON covid.Hospital_code = hospital.code
    WHERE hospital.name = "서울대병원"
) as P
JOIN (
    SELECT member.id, member.age, programmer.Country FROM member
    JOIN programmer ON member.id = programmer.id
    WHERE member.age BETWEEN 20 AND 29
    AND programmer.Country = "India"
) as U
ON P.id = U.id
GROUP BY P.Stay;

5750ms 시간이 걸렸다.

(1) member의 id, covid의 id, programmer의 id, hospital의 code에 PK 설정

(2) member의 age에 인덱스 추가

(3) programmer의 Country에 인덱스 추가

(4) hospital의 name에 인덱스 추가

(5) covid의 Hospital_code에 인덱스 추가

(6) covid의 Stay에 인덱스 추가 (Hospital_code, Stay 순서)

❓ 뭔가 내 의도대로 돌아가지 않는 것 같다! MySQL select 쿼리 실행 순서는 아래와 같다. (참조: https://bicloud.tistory.com/20)

1. FROM
2. ON
3. JOIN
4. WHERE
5. GROUP BY
6. HAVING
7. SELECT
8. ORDER BY

SELECT covid.id, covid.Stay, hospital.name FROM covid
JOIN hospital ON covid.Hospital_code = hospital.code
WHERE hospital.name = "서울대병원";

covid와 hospital을 code 기준으로 join한 후 hospital.name = "서울대병원"인 것을 필터링한다.

SELECT covid.id, covid.Stay, hospital.name FROM covid
JOIN hospital ON (covid.Hospital_code = hospital.code AND hospital.name = "서울대병원");

SELECT covid.id, covid.Stay FROM covid
JOIN (SELECT code FROM hospital WHERE name = "서울대병원") as H ON covid.Hospital_code = H.code;

hospital 중 name = "서울대병원"인 경우와 covid 테이블을 join한다.

💡 JOIN의 ON절과 WHERE절 차이

• JOIN의 ON절: JOIN을 하기 전에 필터링한다.
• WHERE절: JOIN을 한 후에 필터링한다.

위 코드는 아래와 같이 변경할 수 있다!

SELECT C.Stay, Count(*) 
FROM (
    SELECT covid.id, covid.Stay, hospital.name FROM covid
    JOIN hospital ON (covid.Hospital_code = hospital.code AND hospital.name = "서울대병원")
) as C
JOIN (SELECT id FROM member WHERE age BETWEEN 20 AND 29) as M
ON C.id = M.id
JOIN (SELECT id FROM programmer WHERE Country = "India") as P
ON C.id = P.id
GROUP BY C.Stay;

❓ GROUP BY는 어떻게 해결할 수 있을까?

[mindock]

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

---

SELECT P.Exercise, COUNT(P.id) 
FROM programmer as P
JOIN (SELECT id FROM member WHERE age BETWEEN 30 AND 39) as M
ON P.id = M.id
JOIN (
    SELECT covid.id  FROM covid
    JOIN hospital ON (covid.Hospital_code = hospital.code AND hospital.name = "서울대병원")
) as C
ON P.id = C.id
GROUP BY P.Exercise;

작업이 너무 오래 걸려 데이터를 가져오지 못한다.

(1) programmer의 id, member의 id, covid의 id, hospital의 code에 PK 설정

(2) member의 age에 인덱스 추가

(3) hospital의 name에 인덱스 추가

(4) programmer의 Exercise에 인덱스 추가

(5) covid의 Hospital_code에 인덱스 추가 (Hospital_code, id 순)

SELECT P.Exercise, COUNT(P.id) 
FROM programmer as P
JOIN (SELECT id FROM member WHERE age BETWEEN 30 AND 39) as M
ON P.id = M.id
JOIN (
    SELECT covid.id  FROM covid
       USE INDEX (idx_covid_Hospital_code_id)
    JOIN hospital ON (covid.Hospital_code = hospital.code AND hospital.name = "서울대병원")
) as C
ON P.id = C.id
GROUP BY P.Exercise;

위와 같이 USE INDEX를 사용해 옵티마이저가 인덱스를 자동으로 선택하는 것이 아니라 "idx_covid_Hospital_code_id" 인덱스를 사용하도록 지정한다.

[mindock]

Replication

• 데이터를 복제하는 방식
• DBMS를 나누어서 데이터를 저장하는 방식

사용 목적

• 데이터 백업
• 서버 부하 분산

Master

• 데이터 조작 쿼리 실행 (INSERT, UPDATE, DROP)

  Slave

• 데이터 조회 쿼리 실행 (SELECT)

동기 방식

• Master 노드에 데이터 변경이 발생한 경우, Slave 노드까지 적용되는 것을 보장한다.
• Master 노드에 장애가 발생하더라도 데이터 정합성 문제 없이 Slave 노드를 이용하여 서비스 가능하다.

  비동기 방식

• Master 노드에 데이터 변경이 발생한 경우, 시차를 두고 Slave 노드에 동기화된다.
• Master 노드에 장애가 발생하면, Slave에 아직 반영되지 못한 데이터가 있는 경우, 데이터 정합성 문제가 발생할 수 있다.

MySQL Replication

• 비동기 복제 방식 사용
• Master 노드에서 데이터 변경이 발생한 경우, 실행 순서
  1. 해당 이력을 Binary Log에 기록한다.
  2. Replication Master Thread가 비동기적으로 읽어 Slave에 전송된다.
  3. Slave의 I/O Thread는 전달받은 로그를 slave의 Relay Log에 복제한다.
  4. Slave의 SQL Thread는 Relay Log를 읽어 Slave에 실행하여 데이터를 복제한다.
• Slave Thread가 Master Thread쪽으로 요청
  • Slave Thread가 로그인할 수 있는 계정과 권한이 Master에 필요하다.
  • Slave Thread는 Master에 접속 후 Binary Log 송신을 요청하는 명령어를 전송해, Replication Slave인것을 알려준다.