6. 조회 성능 개선하기

[brainbackdoor]

* 미션 진행 후에 아래 질문의 답을 Comment로 남겨주세요

1. 인덱스 적용해보기 실습을 진행해본 과정을 공유해주세요

2. 페이징 쿼리를 적용한 API endpoint를 알려주세요

[nokchax]

1. 인덱스 적용해보기 실습을 진행해본 과정을 공유해주세요

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

   Query

   SELECT  hobby, 
   COUNT(1) / sub.count * 100 AS 'percentage' 
   FROM programmer 
   CROSS JOIN (
   SELECT COUNT(1) AS count 
   FROM programmer
   ) sub
   GROUP BY hobby, sub.count;

   1.889 sec

PK 및 UNIQUE 건 후

0.147 sec

인덱스 재처리

Hobby 와 Student 가 복합 인덱스 처리되어 있어 이를 삭제하고 Hobby만 재 인덱싱

0.047 sec

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

SELECT  C.id,
    H.name
FROM covid AS C
JOIN hospital AS H
ON C.hospital_code = H.code;

0.0049 sec

PK 및 UNIQUE 건 후

0.0035 sec

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

SELECT  P.id AS id,
    H.name AS name
FROM (
    SELECT id
    FROM programmer
    WHERE   hobby = 'Yes'
    AND (student LIKE 'Yes%' OR yearsCoding = '0-2 years')
) AS P
JOIN (
    SELECT  covid.id AS id,
        hospital.name AS name
    FROM covid
    JOIN hospital
    ON covid.hospital_code = hospital.code
) AS H
ON P.id = H.id;

0.014 sec 이미 인덱스가 다 걸려 있어서 추가적인 작업은 하지 않았음

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

SELECT stay, COUNT(*)
FROM (
    SELECT id
    FROM member
    WHERE age BETWEEN 20 AND 29
) AS M
JOIN (
    SELECT id
    FROM programmer
    WHERE country = 'India'
) AS P
ON M.id = P.id
JOIN (
    SELECT  id,
        stay,
        hospital_code
    FROM covid AS C 
    JOIN (
        SELECT code
        FROM hospital
        WHERE name = '서울대병원'
    ) AS H
    ON C.hospital_code = H.code
) AS CH
ON M.id = CH.id
GROUP BY stay;

0.947 sec

인덱스 처리

member 쪽에서 풀스캔이 발생하여 인덱스 추가 이후 programmer 를 풀스캔하여 country에 인덱스 추가

0.045 sec

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

SELECT exercise, COUNT(1)
FROM (
    SELECT id
    FROM member
    WHERE age BETWEEN 30 AND 39
) AS M
JOIN (
    SELECT  id,
        hospital_code
    FROM covid
) AS C
ON M.id = C.id
JOIN (
    SELECT  id,
        exercise
    FROM programmer
) AS P
ON C.id = P.id
JOIN (
    SELECT code
    FROM hospital
    WHERE name = '서울대병원'
) AS H
ON C.hospital_code = H.code
GROUP BY P.exercise;

0.057 sec 효율적인 쿼리 짜는데 오래걸리긴 했지만 별다른 인덱싱 처리 없이 해결 가능

2. 페이징 쿼리를 적용한 API endpoint를 알려주세요

   https://nokchax.kro.kr/favorites/pages/${pageNo}

로그인 및 즐겨찾기 추가한 뒤에 요청 헤더에 Authorization 값을 추가하여 테스트해 주세요

[vsh123]

1. https://www.notion.so/mrvan/Nextstep-index-1d02c4eb019c44728e1dd4ea928e5ad4
2. https://github.com/vsh123/infra-subway/commit/ca24d22dc3a2518ca3c95f914b4d93289105aa26 링크로 대체합니다!!

[mindock]

1. https://github.com/mindock/infra-subway/issues/4
2. http://mindock-subway.kro.kr/favorites?page=${page}
   • 관련 커밋: 240a2789d8065f407d95d563ecdff9ad55ca6058

[oeeen]

1. https://www.notion.so/20b7c6391910434598f40f40fc502d71
2. https://nextstep-oeeen.p-e.kr/favorites
   • https://github.com/oeeen/infra-subway/commit/b68ae832c1d2f6c634da68344b4088c9a0f6f951

쿼리짜는게 쉽지 않네요.. ㅎㅎ 감사합니다.

[wooyongha]

1. 인덱스 적용해보기 실습을 진행해본 과정을 공유해주세요

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

   # Hobby 컬럼에 인덱싱 적용, COUNT 대상 컬럼을 Hobby 로 설정
   SELECT COUNT(Hobby) / (SELECT COUNT(Hobby) FROM programmer) AS 'HobbyCount'
   FROM programmer
   GROUP BY Hobby;

   63ms

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

   # covid 테이블에 id에 unique index 추가, 결과값은 1000 row
   SELECT c.id, h.name
   FROM covid c
           JOIN hospital h ON c.Hospital_code = h.code;

   78ms

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

   # covid 테이블에 id에 unique index 추가, 결과값은 1000 row
   SELECT c.id, h.name
   FROM covid c
           JOIN hospital h ON c.Hospital_code = h.code
           JOIN (
      SELECT id
      FROM programmer
      WHERE Hobby = 'Yes'
        AND (Student LIKE 'Yes%' OR Yearscoding = '0-2 years')
   ) p ON c.id = p.id;

   15ms

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

   # member 테이블 age 컬럼, programmer 테이블 country 컬럼에 인덱스, hospital 테이블 name 컬럼에 유니크 인덱스, hospital_code 컬럼에 인덱스
   SELECT s.Stay, COUNT(p.id)
   FROM (
           SELECT id
           FROM programmer
           WHERE Country = 'India'
       ) p
           JOIN (
      SELECT id
      FROM member
      WHERE age >= 20
        AND age <= 29
   ) m ON p.id = m.id
           JOIN (
      SELECT id, stay
      FROM covid c
               JOIN (SELECT code FROM hospital WHERE name = '서울대병원') h ON h.code = c.Hospital_code
   ) s ON p.id = s.id
   GROUP BY Stay;

   78ms

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

   # 이전에 적용한 인덱스 활용
   SELECT Exercise, COUNT(Exercise)
   FROM covid c
   JOIN (SELECT code
        FROM hospital
        WHERE name = '서울대병원') h ON c.Hospital_code = h.code
   JOIN (SELECT id
        FROM member
        WHERE age >= 30 AND age <= 39) m ON c.id = m.id
   JOIN (
      SELECT id, Exercise
      FROM programmer
      ) p ON c.id = p.id
   GROUP BY Exercise;

   31ms

2. 페이징 쿼리를 적용한 API endpoint를 알려주세요

   https://yh-line.o-r.kr/stations

   https://github.com/wooyongha/infra-subway/commit/c1e8b1d825946abbf4d3fc6b4c94d30c4be38955

[fistkim101]

1. https://github.com/fistkim101/infra-subway/issues/7
2. https://fistkim.kro.kr/favorites

감사합니다 :D

[brainbackdoor]

@wooyongha pagable을 사용하신다면, 강의자료에도 나와있지만, JPQL을 활용해서 구현하셔야 성능상 이점이 있어요. 그냥 pagable 객체를 사용하는건 후반부로 갈수록 탐색 비용이 증가해서 성능 저하가 있어요

https://edu.nextstep.camp/s/IpGaKSMq/ls/uHKHvXXO

[brainbackdoor]

참고로, PK를 설정하지 않으면 InnoDB를 사용하는 경우, MySQL 서버가 UNIQUE, NOT NULL 칼럼을 PK로 지정하고, 지정할 게 없을 경우 별도로 PK를 생성합니다. (다만, 이 경우 키가 효율적이지 않아 PK는 반드시 설정해주는게 좋습니다. )

https://dev.mysql.com/doc/refman/5.7/en/innodb-index-types.html

[sk1737030]

1. 인덱스 적용해보기 실습을 진행해본 과정을 공유해주세요 https://github.com/sk1737030/infra-subway/issues/10 워크벤치의 visual explain 스샷 찍어놓은게 샤르륵 날라가버렸네요
2. 페이징 쿼리를 적용한 API endpoint를 알려주세요 https://dongguri.kro.kr/favorites https://github.com/sk1737030/infra-subway/commit/ceca1c26087bd51b730ee5b94bb9f7b973237185

감사합니다!