우아한테크코스 Lv4 - MYSQL 최적화 미션 (학습자료 정리)

Lv4 미션으로 쿼리 튜닝해보는 미션이 주어졌다.

먼저 본인의 쿼리 작성 능력을 테스트해볼 겸 실습 사이트에서 아래의 쿼리를 직접 작성해보자!

1. 200개 이상 팔린 상품명과 그 수량을 수량 기준으로 내림차순으로 보이게 하라.
2. 많이 주문한 순으로 고객 리스트(ID, 고객명), 총수량을 구하라.
3. 많은 돈을 지출한 순으로 고객리스트를 구하라.

위 튜토리얼(?)에 대한 나의 쿼리는 아래와 같다. (주의 성능은 1도 생각안하고 결과만 제대로 나오는 쿼리다!)

1번 쿼리

SELECT p.ProductId '상품아이디', p.ProductName '상품이름', SUM(od.Quantity) '총수량'
FROM Products p
JOIN OrderDetails od 
ON od.ProductId = p.ProductId
JOIN Orders o
ON o.OrderId = od.OrderId
GROUP BY p.ProductId
HAVING SUM(od.Quantity) >= 200
ORDER BY SUM(od.Quantity) DESC

1번 결과

2번 쿼리

SELECT c.CustomerID 'ID', c.CustomerName '고객이름', SUM(od.Quantity) '주문량'
FROM OrderDetails od
JOIN Orders o ON od.OrderId = o.OrderId
LEFT JOIN Customers c ON c.CustomerID = o.CustomerID
GROUP BY c.CustomerId
ORDER BY SUM(od.Quantity) DESC

2번 결과

주의 해당 쿼리 결과는 틀렸다! 총 91건의 데이터가 나와야한다.

총 주문량이 0건인 고객도 있기 때문에 고객을 기준으로 JOIN을 해줘야한다. (고객 테이블이 왼쪽에 있도록해서 LEFT JOIN 시켰다.)

수정 후 (총 91건의 데이터)

SELECT c.CustomerID 'ID', c.CustomerName '고객이름', SUM(od.Quantity) '주문량'
FROM Customers c 
LEFT JOIN Orders o ON c.CustomerID = o.CustomerID
LEFT JOIN OrderDetails od ON od.OrderId = o.OrderId
GROUP BY c.CustomerId
ORDER BY SUM(od.Quantity) DESC

3번 쿼리

SELECT c.CustomerID 'ID', c.CustomerName '고객이름', SUM(od.Quantity) '총수량', SUM(od.Quantity * p.Price) '지출금액'
FROM Customers c 
LEFT OUTER JOIN Orders o ON c.CustomerID = o.CustomerID
LEFT OUTER JOIN OrderDetails od ON od.OrderId = o.OrderId
LEFT OUTER JOIN Products p ON p.ProductID = od.ProductId
GROUP BY c.CustomerId
ORDER BY SUM(od.Quantity * p.Price) DESC

3번 결과

SQL 기본

기본 내용 중에서 잘 모르는 내용만 기록

SELECT * FROM Orders
JOIN OrderDetails ON Orders.OrderID = OrderDetails.OrderID
WHERE Orders.OrderID IN (1, 30)

• OrderID이 1, 30인 데이터를 검색하기 위해 Orders 테이블을 먼저 찾아본다.
  • 그 이유는 테이블에는 동시에 접근할 수 없기 때문이다.
  • 이처럼 먼저 접근하는 테이블을 드라이빙 테이블, 검색 결과를 통해 뒤늦게 검색하는 테이블을 드리븐 테이블이라고 한다.
• 가능하면 적은 결과가 반환될 것으로 예상되는 테이블을 드라이빙 테이블로 선정해야 한다. 드라이빙 테이블의 추출 건수가 곧 드리븐 테이블의 액세스 반복 횟수가 되기 때문이다.

DB 최적화 대상은...?

DB에서 최적화를 할 요소는 총 세가지가 있는데, Client, Database Engine, Filesystem이다.

• Client
  • 복수 건의 레코드를 한 번의 호출로 처리하거나, 여러개의 쿼리를 한 쿼리로 통합 처리함으로써 호출 수를 줄일 수 있다.
  • Java 진영에서 DB를 조작하는 JDBC Statement는 쿼리 문장 분석, 컴파일, 실행의 단계를 캐싱한다.
    • 반면에 PreparedStatement는 처음 한 번만 세 단계를 거친 후 캐시에 담아서 재사용한다. (따라서 가급적이면 PreparedStatement를 쓰는게 좋다)
  • DB Connection Pool을 사용하여 객체를 생성하는 부분에서 발생하는 대기시간을 줄이고 네트워크 부담을 줄일 수 있다.
  • Fetchsize 조정하거나 Paging을 활용한다.
• Database Engine
  • 파일시스템에 저장된 데이터가 조회되면 해당 데이터를 메모리에 저장해 이후 동일 데이터 조회 시 파일시스템의 물리적인 입출력이 발생하지 않도록 한다.
  • 서버 파라미터를 튜닝한다.
• FileSystem
  • SSD를 사용
  • SQL을 최적화하여 필요이상의 데이터 블록을 읽는 것을 방지한다.
    • SQL 튜닝이란 읽는 블록 수를 줄여주는을 의미한다.

참고해볼 자료: 쿼리 최적화: 빠른 쿼리를 위한 7가지 체크리스트

그렇다면 SQL 최적화의 대상은 무엇인가? 🤔

쿼리 동작 방식

0. Query Caching
   SQL문이 Key, 쿼리의 실행결과가 Value인 Map이다. 정해진 확인절차를 거쳐 쿼리 캐시의 결과를 반환한다.

확인절차

• 요청된 쿼리 문장이 쿼리 캐시에 존재하는지
• 해당 사용자가 그 결과를 볼 수 있는 권한이 있는지
• 트랜잭션 내에서 실행된 쿼리라면 가시 범위 내에 있는 결과인지
• 호출 시점에 따라 결과가 달라지는 요소(RAND, CURRENT_DATE 등)가 있는지
• 캐시가 만들어지고 난 이후 해당 데이터가 다른 사용자에 의해 변경되지 않았는지
• 쿼리에 의해 만들어진 결과가 캐시하기에 너무 크지 않은지

1. Parsing
   사용자로부터 요청된 SQL을 잘게 쪼개서 서버가 이해할 수 있는 수준으로 분리한다.

2. Preprocessor
   쿼리가 문법적으로 틀리지 않은지 확인하여 부정확한 경우 여기서 처리를 중단한다. (batch와 같은 일괄처리 내에 있다면 일괄처리 전체를 중단)

3. Optimization
   실행계획(Execution Plan)이란, 이 단계에서의 출력을 의미한다.

* 쿼리 분석: Where 절의 검색 조건인지, Join 조건인지 판단
* 인덱스 선택: 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
* 조인 처리: 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정 

4. Handler (Storage Engine)
   MySQL 실행엔진의 요청에 따라 데이터를 디스크로 저장하고 디스크로부터 읽어오는 역할을 담당. MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행

Sequential Access vs Random Access

Index Range Scan vs Table Full Scan

DB 테이블에서 데이터를 찾는 방법은 두 가지가 있다.

• 테이블 전체를 스캔 (= Table Full Scan)
  • Sequential Access와 Multiblock I/O 방식으로 디스크를 읽어 한 블록에 속한 모든 레코드를 한번에 읽는다.
• 인덱스를 이용 (= Index Range Scan)
  • Random Access와 Single Block I/O로 레코드 하나를 읽기 위해 매번 I/O가 발생한다.

위 두 특징을 통해서 알 수 있는 사실은 읽을 데이터가 일정량을 넘으면 Index 보다 Table Full Scan이 유리하다는 것이다.

그렇다면 우리의 목표는...?

• Index를 사용할 경우 Random I/O 횟수를 줄이는 것이다.

인덱스 탐색과정은 스캔 시작지점을 찾는 수직적 탐색과 데이터를 찾는 수평적 탐색, 두 가지로 나눌 수 있다. (cf. MySQL InnoDB의 경우 B-Tree 인덱스 구조가 기본이다)

(이미지 참조: https://simpledb.tistory.com/22)

수직적 탐색

• 인덱스 수직적 탐색은 루트 노드에서부터 시작한다.
• 루트 노드와 브랜치 노드는 인덱스 키와 노드 정보로 구성된 페이지 단위이다.
• 수직적 탐색 과정에 찾고자 하는 값보다 크거나 같은 값을 만나면, 바로 직전 레코드가 가리키는 하위 노드로 이동한다.

수평적 탐색

• 수직적 탐색을 통해 스캔 시작점을 찾았으면 수평적 탐색을 통해 데이터를 찾는다.
• 인덱스 리프노드끼리는 양방향 LinkedList이므로 서로 앞뒤 블록에 대한 주소값을 갖는다.
  • 필요한 컬럼을 인덱스가 모두 갖고있는 경우 인덱스만 스캔하고 끝난다.
  • 그렇지 않은 경우에는 테이블도 액세스해야한다.
  • 이때, ROWID가 필요하다.
    • ROWID는 데이터블록 주소 + 로우 번호(블록내 순번)으로 구성된다.
    • cf. InnoDB의 경우, Primary Key가 ROWID 역할을 한다. (PK는 Clustered Index로 순차적으로 저장되어있어 Data Record의 물리적인 위치를 알 수 있기 때문)
    • ROWID가 가리키는 데이터 페이지를 버퍼풀에서 먼저 찾아보고 못찾을 때만 디스크에서 블록을 읽는다. (읽은 후에는 버퍼풀에 적재한다)
      • 잘 생각해보면 이 요소는 JPA의 영속성 컨텍스트와 유사해보인다. 아니 사실은 JPA 영속성 컨텍스트가 DB에 유사하게 구현됐다는 표현이 맞는 듯 하다.

인덱스 튜닝

인덱스 튜닝은 크게 2가지로 인덱스 스캔 효율화와 랜덤 엑세스 초기화이 있다.
(랜덤 엑세스 초기화는 인덱스 스캔 후 테이블 레코드를 액세스할 때, 랜덤 I/O 횟수를 줄이는 것을 의미한다.)
랜덤액세스 최소화 튜닝이 더 중요하다.

인덱스 손익분기점

1건을 조회하든, 1000만건을 다 조회하든 Table Full Scan의 성능은 일정하게 유지된다.
하지만 인덱스를 이용해 테이블을 액세스할 경우에는 랜덤 액세스 때문에 점점 느려진다.
Table Full Scan은 Sequantial Acess인 반면, 인덱스 ROWID를 이용한 테이블 액세스는 랜덤 액세스 방식이기 때문이다.

(테이블 데이터가 10~100만건 이내의 경우 5 ~ 20% 가량에서 손익분기점이 형성된다고 함)
조회건수가 늘수록 데이터를 버퍼캐시에서 찾을 가능성이 낮아진다.

DB 서버 튜닝

• 메모리 튜닝
  • Thread
    • thread_cache_size
  • Caching
    • innodb_buffer_pool_size
• 커넥션 튜닝
  • connect_timeout
  • interactive_timeout
  • wait_timeout
  • max_connections
  • back_log

참고자료

우아한테크코스 Lv4 - 조회 성능 개선하기 수업자료
[파일구조]- 레코드 접근과 성능
MySQL 다시 공부하기(1)