[sql-tuning 미션] 쿼리 최적화

이미 sql 미션을 진행한지는 1달도 더 지난 듯 싶지만, 내용을 떠올리며 DB에 대해 학습하는 내용 위주로 작성해본다. 원하는 결과가 나올 수 있게 쿼리를 작성하고 일정시간(ex, 100ms)안에 조회할 수 있도록 하는 미션이었다.

서브쿼리

쿼리안에 쿼리가 들어가는 것을 서브쿼리(nested query)라고 한다. select 절, from 절, where 절 등에 들어갈 수 있다. 

Join

조인은 두 테이블을 합쳐 보여주는 것이다. 닫힌연산이라 여러번 가능하다. 크로스조인은 상호조인이라고도 불리며, 한쪽 테이블의 모든 행과 다른쪽 테이블의 모든 행을 곱하게 된다. 10개의 행을 가진 테이블과 13개의 행을 가진 테이블을 크로스조인하면 130행의 테이블이 생성된다. (10rows X 13rows = 130rows) 두 테이블의 모든 경우의 수의 곱을 합치게 되는데, 카테시안 곱이라고도 한다. 흔히 사용하는 join은 cross join + 조건 필터링 이다.

join의 동작

inner join은 카테시안 곱의 조건 필터링 방식으로 이루어진다. MySQL에서 join 은 nested-join 알고리즘이나 그 변형을 사용한다. 

Table   Join Type
t1      range
t2      ref
t3      ALL

-- 기본적인 nested-loop algorithm은 다음과 같고 join의 동작방식이다.
for each row in t1 matching range {
  for each row in t2 matching reference key {
    for each row in t3 {
      if row satisfies join conditions, send to client
    }
  }
}

join의 종류

inner join, left join, left join, outer join 등이 있다.

https://dsin.wordpress.com/2013/03/16/sql-join-cheat-sheet/

비교하기

join  -vs-  서브쿼리

A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server might be able to optimize it better—a fact that is not specific to MySQL Server alone. 

- mysql 공식문서

서브쿼리보단 join을 사용해야한다고 한다. 성능최적화를 해주기 때문에 더 빠르다고 한다.

on   -vs-   where

on, where 문 모두 조건을 걸 때 사용된다. join 문에서 on절이 선행된다. 

• on 으로 필터링 된 결괏값 -> (join의 결괏값을) where 로 필터링

where   -vs-  having

• group by 뒤에 having 절로 조건 설정 (집합함수는 having 절로만 조건 기술 가능)
• WHERE 로 필터링 -> GROUP BY 로 그룹형성 -> HAVING 그룹결과에 대한 필터링

---

Index

인덱스는 데이터베이스 분야에 있어서 테이블에 대한 동작의 속도를 높여주는 자료 구조를 일컫는다. 추가적인 저장공간을 활용하여 검색(읽기)속도를 향상시키는 자료구조이다. MySQL에서는 B-Tree를 개선한 B+Tree가 사용되는 것으로 알고 있다. 정렬된 상태를 유지한다.

B+Tree

• B-Tree와 구조는 같음. 발전된 형태
• 키만 가지고 있음 → 키, 값을 가지는 것보다 용량이 작아서 더 많은 키를 가져서 높이가 낮음
• 같은 레벨끼리는 doubly-linkedlist
• 자식노드와는 singly-linkedlist

같은 레벨끼리는 double-linkedlist를 활용하기 때문에 수평적인 탐색이 가능하다. 기존 B-Tree에서는 상위노드에 가서 다시 내려가는 탐색을 했다.

Clustered Index, Non-Clustered Index

• Clustered Index는 보통 PK에서 사용되는 인덱스이다. 마지막 리프노드에는 데이터에 직접 접근이 가능하도록 실제 레코드(데이터 페이지) 주소를 가진다.
• Non-Clustered Index는 FK에서 사용되고 리프노드에는 PK를 저장한다.
  • 실제 데이터를 찾으려면 두번 인덱스 검색이 일어남
    1. secondary index로 해당 레코드의 PK값을 찾고
    2. PK값으로 clustered index를 검색하여 레코드를 찾음

---

데이터 접근

디스크에서 데이터에 엑세스하는 방식에 Sequential Access, Random Access 방식이 있다.

• Sequential Access: 물리적으로 인접한 페이지를 차례대로 읽는 방식이다.
• Random Access: 정해진 순서가 없어 디스크를 읽을 때 물리적인 움직임이 더 요구된다.

 

데이터베이스 테이블에서 데이터를 찾는 방식은 테이블 전체를 스캔하는 방식, 인덱스를 이용하는 방식이 있다.

• Table Full Scan: 시퀀셜 엑세스와 Multi Block I/O로 효율적 디스크 읽기를 진행한다.
• Index Range Scan: 랜덤 엑세스와 Single Block I/O로 레코드 하나하나를 읽을 때마다 I/O가 발생한다. 일정 기준을 넘으면 Index Range Scan 보다 Table Full Scan이 더 유리하다고 한다. 인덱스가 유리한 경우는 많은 데이터 중에서 소량의 데이터를 읽을 때 유리하다.

인덱스를 사용할 때는 Random I/O 횟수를 줄이는 것이 중요하다. 즉, 스캔 범위를 줄이는 것이 중요하다.

---

쿼리 튜닝 하기

- 실행계획을 통해 개선해야할 부분을 확인할 수 있다. (explain 또는 돋보기 모양의 visual explain을 활용)

인덱스

• 인덱스 컬럼을 가공하지 말아야 한다.
• 인덱스의 순서를 고려한다.
• 인덱스를 제대로 사용하는지 확인한다.
• 복합 인덱스 사용시, 범위 검색 컬럼을 뒤에 두어야 한다.

조인문

• 조인 연결 key들을 양쪽 다 인덱스를 가지는 것이 좋음
  • 효율이 떨어짐
  • 테이블 크기와 상관 없이 인덱스가 있는 쪽이 드라이빙 테이블이 됨)
• 데이터가 적은 테이블을 랜덤액세스
• 모수 테이블 크기를 줄여라
• 서브쿼리보단 조인문

---

정리

JPA를 사용하면서 만들어 주는 쿼리를 사용하느라 직접 쿼리를 작성하는데 걱정이 되었다. 그 시기에 sql 튜닝 미션을 하게 되었고, 기존에 애매하게 알고 있던 조인에 대해 더 알게 되는 시간이었다. 아직, 극도의 최적화까지는 어렵지만 시간을 줄여나가는 연습을 하면서 어느정도 기준까지의 최적화는 해볼 수 있었다. 그리고 기존에는 쿼리작성에 자신감이 떨어졌는데 어찌되었든 원하는 결과가 나오는 쿼리는 만들 수 있다는 자신감이 생겼다. 😀

참고

- MySQL 공식문서 _ nested join

- MySQL공식문서 rewriting subquery

- 씨유 우아한테크코스 수업자료

- SQL 튜닝 미션 수행 내용