[책] 업무에 바로 쓰는 SQL튜닝

업무에 바로 쓰는 SQL 튜닝 - YES24

업무에 바로 쓰는 SQL 튜닝 - YES24

 

우리 회사에서는 인덱스를 중요하게 생각하고 있고 면접때도 인덱스 질문을 받았다. 하지만 저는 당시 인덱스에 대해 전혀 개념이 없어 유튜브 설명영상도 보고 블로그도 찾아봤지만 ‘그래서 난 뭘 해야하지?’ 하는 막연함만 가진 채 시간을 보냈다. 그러던 중에 이 책을 접하게 되었고, 인덱스의 개념과 실행계획 보는 법, SQL 튜닝에 대해 공부할 수 있게 되었다. 책의 모든 내용이 다 주옥같지만 제가 가장 기억에 남고 향후 실무에 도움이 되겠다 싶은 내용 위주로 정리해보았다.

조인 알고리즘

다수의 테이블을 조인할 때 접근 우선순위를 정한다

드라이빙 테이블 & 드리븐 테이블

SELECT 학생.학번,
             학생.이름,
             비상연락망.관계,
             비상연락망.연락처
FROM 학생
JOIN 비상연락망
    ON 학생.학번 = 비상연락망.학번 
WHERE 학생.학번 IN (1,100)

• 드라이빙 테이블 : 먼저 접근하는 테이블 - 학생
  • 학생 테이블에 먼저 접근함
• 드리븐 테이블 : 그 다음에 접근하는 테이블 - 비상연락망
  • 학생 테이블의 검색결과(학번 in 1, 100)를 통해 비상연락망 테이블에 접근함

중첩루프조인 (Nested Loop Join, NL조인)

드라이빙 테이블의 데이터 1건당 드리븐 테이블을 반복해 검색하여 최종적으로는 양쪽 테이블에 공통된 데이터를 출력하는 것

• (최악의 경우 N*M건의 데이터를 조회하게 됨)
• 필연적으로 데이터 접근 시 인덱스에 의한 랜덤 엑세스가 발생
• 2중 반복문과 작동 방식이 유사함

• for(i=0; i<dept.length; i++) { -- driving table 
      for(j=0; j<emp.length; j++) { -- driven table
         // Search
      } 
  }

블록중첩루프조인 (Block Nested Loop Join, BNL 조인)

• 중첩루프조인을 개선하기 위해 등장
• 드라이빙 테이블에서 해당하는 데이터 검색 후 조인버퍼에 적재
• 조인버퍼와 드리븐테이블의 데이터를 조인하여 데이터에 모두 접근
• 드리븐테이블의 테이블 풀 스캔을 줄이는 것이 목적

배치 키 액세스 조인 (Batched Key Access Join, BKA 조인)

• 랜덤 액세스의 단점을 해결하고자 등장한 알고리즘
• 블록중첩루프조인의 조인버퍼 개념을 그대로 사용
• 드리븐 테이블에 필요한 데이터를 미리 예측하고 정렬된 상태로 담는 랜덤 버퍼 개념 도입

해시 조인(Hash Join)

• 조인에 참여하는 각 테이블의 데이터를 내부적으로 해시값으로 만들어 내부 조인을 수행.
• 조인 수행 결과를 조인버퍼에 저장하여 조인열의 인덱스를 필수로 요구하지 않아도 됨

💡 MySQL은 대부분 중첩루프조인방식으로 조인을 수행하는 한편, 오라클은 중첩루프조인 뿐 아니라 정렬 병합 조인(Sort Merge Join)과 해시 조인 (Hash Join) 방식도 제공 MySQL 8.0.18 버전에서도 제약적으로 해시 조인을 제공하지만 여전히 대부분의 조인은 중첩 루프 조인으로 수행됨 MariaDB 5.3 이후 버전부터 블록 중첩루프 해시라는 이름으로 해시조인기능 제공 중

중첩루프조인과 인덱스

SELECT 학생.학번,
             학생.이름,
             비상연락망.관계,
             비상연락망.연락처
FROM 학생
JOIN 비상연락망
    ON 학생.학번 = 비상연락망.학번
WHERE 학생.학번 IN (1,100)

### 전제
# 학생 테이블의 학번은 기본키이다.
# 비상연락망 테이블엔 학번 당 2개의 데이터가 존재한다. (1:N관계)
# 학생 테이블은 총 100행, 비상연락망 테이블은 총 10000행이다.

인덱스가 없을 경우

• 학생 테이블에서 학번1을 찾기 위해 100개 행을 스캔 → 비상연락망 테이블에서 학번 1을 찾기 위해 1000개의 행을 스캔
• 학생 테이블에서 학번 10을 찾기 위해 100개 행을 스캔 → 비상연락망 테이블에서 학번 10을 찾기 위해 1000개의 행을 스캔
• 대략 2200건의 데이터에 접근함 (100 + 1000 + 100 + 1000 = 2200)

인덱스가 있을 경우

• 학생.학번 테이블과 비상연락망.학번 테이블에 각각 인덱스가 있을 경우
• 학생 인덱스에서 학번1에 접근(1) → 비상연락망 인덱스에서 학번 1에 접근 → 학번 1인 데이터 탐색 (2)
• 학생 인덱스에서 학번10에 접근(1) → 비상연락망 인덱스에서 학번 10에 접근 → 학번 10인 데이터 탐색 (2)
• 총 6건의 데이터에 접근 (1+2+1+2)

💡 인덱스에 접근할 범위가 넓으면 `랜덤 엑세스`가 발생하기 때문에 인덱스를 사용한다면 범위가 좁거나 1개인 경우가 가장 좋다.

오브젝트 스캔 유형

테이블 풀 스캔, 인덱스 범위 스캔, 인덱스 풀 스캔, 인덱스 고유 스캔, 인덱스 루스 스캔, 인덱스 병합 스캔

잘 정리된 다른 블로그 글 첨부합니다.

2장 인덱스 기본 - 백과사전에서 내가 원하는 단어 빨리 찾는 방법

2장 인덱스 기본 - 백과사전에서 내가 원하는 단어 빨리 찾는 방법

디스크 접근 방식

MySQL은 원하는 데이터를 찾기 위해 데이터가 저장된 스토리지의 페이지에 접근

💡 페이지
- 데이터를 검색하는 최소 단위
- 페이지 단위로 데이터 읽고 쓰기 수행 가능

시퀀셜 액세스(Sequential Access)

• 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식
• 보통 테이블 풀 스캔에서 활용

랜덤 엑세스(Random Access)

• 물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식
• 페이지가 위치한 물리적인 위치를 고려하지 않음
• 정해진 순서 없이 이동하는 만큼 디스크의 물리적인 움직임이 필요하고, 데이터의 접근 수행 시간이 오래걸림
• 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝 필요

---

실행계획

SQL문으로 요청한 데이터를 어떻게 불러올 것인지에 관한 계획(=경로)

EXPLAIN, DESCRIBE, DESC 중 하나의 키워드를 확인하고자 하는 SQL문 앞에 작성

EXPLAIN
SELECT *
FROM 사원
WHERE 사원번호 BETWEEN 100001 AND 200000;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	사원	NULL	range	PRIMARY	PRIMARY	4	NULL	20080	100.00	Using where

 

실행계획 보는 법은 책에도 나와있고, 정리를 잘 해주신 블로그가 있어 링크 첨부합니다.

[MySQL] Query Plan 보는 법

[MySQL] Query Plan 보는 법

 

좋고 나쁨을 판단하는 기준

실행계획의 select_type, type, extra 항목을 주로 검토함

좋은건 파란색, 나쁜건 빨간색으로 표시

• SIMPLE : UNION이나 내부 쿼리가 없는 SELECT문. 단순 SELECT문을 의미.
• PRIMARY : 서브쿼리나 UNION이 포함된 SQL문에서 첫번째로 SELECT키워드가 작성된 구문을 의미

  id	select_type	table	type	extra
  1	PRIMARY	사원	const	NULL
  2	SUBQUERY	매핑	index	Using index

  SELECT 사원.사원번호, 
          사원.이름, 
          사원.성,
          (
              SELECT MAX(사원번호)
              FROM 부서사원_매핑 AS 매핑
              WHERE 매핑.사원번호 = 사원.사원번호
          ) AS "카운트"
  FROM 사원
  WHERE 사원.사원번호 = 100001;​

• DERIVED : FROM절에 작성된 서브쿼리를 의미
• DEPENDENT * : 서브쿼리가 메인 테이블에 영향을 받는 경우
  • DEPENDENT SUBQUERY, DEPENDENT UNION

    id	select_type	table	type	Extra
    1	PRIMARY	관리자	index	Using index
    2	DEPENDENT SUBQUERY	사원1	eq_ref	Using where
    3	DEPENDENT UNION	사원2	eq_ref	Using where

  • SELECT 관리자.부서번호,
           (
            SELECT 사원1.이름
            FROM 사원 AS 사원1
            WHERE 성별 = 'F'
             AND 사원1.사원번호 = 관리자.사원번호 #DEPENDENT SUBQUERY
    
            UNION ALL
    
            SELECT 사원2.이름
            FROM 사원 AS 사원2
            WHERE 성별 = 'M'
             AND 사원2.사원번호 = 관리자.사원번호 #DEPENDENT UNION
           ) AS "이름"
    FROM 부서관리자 AS "관리자"

• UNCACHEABLE SUBQUERY : 메모리가 상주하여 재활용되어야 할 서브쿼리에 재사용되지 못할 때유형2 - RAND(), UUID() 함수 등을 사용하여 매번 조회 시마다 결과가 달라지는 경우
  • 유형1 - 해당 서브쿼리 안에 사용자 정의 함수 or 사용자 변수 포함됐을 경우
  • 유형2 - RAND(), UUID() 함수 등을 사용하여 매번 조회 시마다 결과가 달라지는 경우

  • SELECT *
    FROM 사원
    WHERE 사원번호 = (SELECT ROUND(RAND() * 1000000));
    # RAND : 0~1 사이의 소숫점 17자리 랜덤소수 출력 (ex. 0.2184567844670694)​

    id	select_type	table	type	Extra
    1	PRIMARY	사원	ALL	Using where
    2	UNCACHEABLE SUBQUERY	NULL	NULL	No tables used

• system : 테이블에 데이터가 없거나 1개만 있는 경우. 성능 상 최상의 타입
• const : 조회되는 데이터가 단 1건일 때 출력되는 유형. 성능 상 매우 유리한 방식
• eq_ref : 조인이 수행될 때 드리븐 테이블의 데이터에 접근하여 고유 인덱스 또는 기본 키로 단 1건의 데이터를 조회하는 방식. 조인 수행 시 성능상 가장 유리한 경우
• index : 인덱스 풀 스캔을 의미. 물리적인 인덱스 블록을 처음부터 끝까지 훑는 방식
• 인덱스는 보통 테이블보다 크기가 작으므로 테이블 풀 스캔 방식보다는 빠를 가능성이 높음
• ALL : 테이블 풀 스캔을 의미. 활용할 수 있는 인덱스가 없거나, 인덱스를 활용하는 게 오히려 비효율적이라고 옵티마이저가 판단했을 때 선택됨
• ALL 유형이 나오면 인덱스를 새로 추가하거나 기존 인덱스를 변경하는 등의 튜닝이 가능하지만, 전체 테이블 중 10~20% 이상 분량의 데이터를 조회할 때는 ALL유형이 오히려 성능상 유리할 수도 있음

 

• Using index : 물리적인 데이터 파일을 읽지 않고 인덱스만 읽어서 SQL문의 요청사항을 처리할 수 있는 경우를 의미. (=커버링 인덱스 방식)
• Using filesort : 정렬이 필요한 데이터를 메모리에 올리고 정렬 작업을 수행한다는 의미. 보통 이미 정렬된 인덱스를 사용하면 추가적인 정렬 작업이 필요 없지만, 인덱스를 사용하지 못할 때는 정렬을 위해 메모리 영역에 데이터를 올림. 이때는 인덱스를 활용하도록 SQL 튜닝 검토 대상이 될 수 있음
• Using temporary : 데이터의 중간 결과를 저장하고자 임시 테이블을 생성하겠다는 의미.임시 테이블을 메모리에 생성하거나, 메모리 영역을 초과하여 디스크에 임시 테이블을 생성하면 Using temporary는 성능 저하의 원인이 될 수 있어 SQL 튜닝 검토 대상이 될 수 있음
• 주로 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우 출력됨

  ---

SQL 튜닝 사례

책의 튜닝 핵심은 결국 원하는 데이터를 꼬집는 것👌이었다.

풀스캔보다는 범위스캔, 범위스캔보다는 1건의 데이터에 바로 도달하는 것이 성능 상 유리한 것이다.

(상황에 따라 인덱스 풀 스캔보다 테이블 풀 스캔이 나은 경우도 있다)

책에서는 여러가지 튜닝 사례를 Before/After로 보여주었는데, 그 중 2가지를 공유하고자 한다.

사례1] 기본 키를 변형하는 나쁜 SQL

SELECT *
  FROM 사원
 WHERE SUSTRING(사원번호, 1, 4) = 1100
   AND LENGTH(사원번호) = 5

튜닝 전 실행계획

id	select_type	table	type	key	ref	rows	Extra
1	SIMPLE	사원	ALL	NULL	NULL	299157	Using where

문제점

• 사원번호에는 인덱스가 있지만 SUBSTRING()으로 가공하여 인덱스를 사용하지 못했음
• 테이블 풀 스캔이 발생 (type : ALL)
• 인덱스를 사용하지 않았음 (ref : NULL)
• 299157건의 데이터를 조회 (rows: 299157)

튜닝 결과

SELECT *
  FROM 사원
 WHERE 사원번호 BETWEEN 11000 AND 11009

튜닝 후 실행계획

id	select_type	table	type	key	ref	rows	Extra
1	SIMPLE	사원	range	NULL	PRIMARY	10	Using where

• 기본키 사용 (ref: PRIMARY)
• BETWEEN구문에 의한 특정 범위 스캔 (type: range)
• 10건의 데이터 조회 (rows: 10)

사례2] 비효율적인 인덱스를 사용하는 나쁜 SQL

SELECT 사원번호, 이름, 성
  FROM 사원
 WHERE 성별 = 'M'
   AND 성 = 'Baba' # (성 = Family name)

튜닝 전 실행계획

id	select_type	table	type	key	ref	rows	filtered	Extra
1	SIMPLE	사원	ref	I_성별_성	const, const	135	100.00	Null

문제점

• 이 쿼리는 성별을 먼저 조회하고 성을 조회함
• 성별은 M, F 2가지 종류만 있기 때문에 한 조건당 데이터의 수가 많음
• 성은 종류가 성별보다 많으므로 한 조건당 데이터의 수가 적음
• 데이터의 양이 많아질 수록 성능 차이를 불러올 수 있음

튜닝 수행

ALTER TABLE 사원
    DROP INDEX I_성별_성,
    ADD INDEX I_성_성별(성, 성별);

# 쿼리는 동일
SELECT 사원번호, 이름, 성
  FROM 사원
 WHERE 성별 = 'M'
   AND 성 = 'Baba'

• SQL튜닝의 핵심은 적은 데이터부터 꼬집어 나가는 것이므로 인덱스를 위와 같이 수정
• 데이터 가짓수가 많은 성을 먼저 조회하고 그 후에 성별을 조회하면 더 적은 데이터를 조회하여 결과값을 얻을 수 있음

튜닝 후 실행계획

id	select_type	table	type	key	ref	rows	filtered	Extra
1	SIMPLE	사원	ref	I_성_성별	const, const	135	100.00	Null

• 소요시간과 실행계획에 큰 차이

---

업무 중 활용 사례

Querydsl로 동적쿼리를 만들고 테스트를 하던 중 hibernate에서 만든 쿼리의 실행 속도가 유독 느렸다.

어디가 문제인지 raw query와 hibernate query의 실행계획을 각각 출력하여 비교하였다

Hibernate Query에서 2군데에 유독 rows 수가 많다.

알고보니 querydsl에서 쿼리문을 만들 때 ON절에 조건문을 잘못 넣었던 것이었다.

책을 보기 전엔 실행계획을 어떻게 봐야할지도 몰랐는데, 이렇게 실행계획으로 내 쿼리의 문제점을 발견하게 되어 뿌듯했다 😄

총평

좋았던 점

• 앞부분에서 기본적인 용어와 개념을 설명해주고, 뒷부분에서 실제 사례와 함께 쿼리 튜닝 과정을 설명해줌
• 맨뒷장에 용어별 인덱스가 있어서 튜닝설명 도중 모르는 용어가 나오면 바로 앞의 설명을 찾아볼 수 있었음
• 실사례 기반의 튜닝 과정 보여주는 게 좋았음
• 책이 가벼워서 출퇴근길에 들고다니며 읽기 좋았음

아쉬운 점

• 드라이빙테이블, 드리븐 테이블을 반대로 얘기하거나, 첨부한 이미지가 설명 내용과 다른 등의 몇몇 오류가 있었음. 초판이라 그렇고 이후 수정될 것이라 생각됨