업무에 바로 쓰는 SQL 튜닝 1 - SQL 튜닝 이론

해당 포스팅은 업무에 바로 쓰는 SQL 튜닝 도서를 기반으로 작성하였습니다.

 

이전 직장에서는 주로 오라클을 사용하였고, 앞으로는 MySQL(MariaDB, Postgresql...) 을 사용할 예정이기에 관련하여 실행 계획 및 여러 SQL 튜닝 기법들에 대해 정리해 보고자 합니다. 예전에 Real MySQL 도서를 통해 이론적으로 학습하였지만, 오라클을 주로 사용하다보니 MySQL의 주요 실행 계획에 대해서는 정리 차원에서 다시 살펴보겠습니다.

 

책의 예제들을 통해 평소에 이론으로만 알고 있었던 내용들이나 경험이 있었던 튜닝들 등 다양하게 경험해볼 수 있어 좋았습니다.

책의 예제들을 통해 전혀 새로운 접근이나, 평소에 이론으로만 알고 있었던 내용들, 조금이나마 경험이 있었던 쿼리 등 정해진 데이터 형식이긴 하지만 다양하게 경험해볼 수 있어서 좋았습니다.

 

실제 SQL 튜닝을 진행하기 전 간단하게 튜닝에 필요한 용어들을 정리하고, 쿼리를 작성하며 개선해봅니다.

 

1. SQL 튜닝 용어를 직관적으로 이해하기

 

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

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

• 드라이빙 테이블: 먼저 접근하는 테이블 (학생)
• 드리븐 테이블: 후행으로 접근하는 테이블 (비상연락망)
• 드라이빙 테이블 -> 가능한 적은 결과가 반환될 것으로 예상되는 테이블

 

개념적인 튜닝 용어

• 오브젝트 스캔 유형 -> 테이블 스캔과 인덱스 스캔으로 구분합니다.
• 테이블 스캔: 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형
• 인덱스 스캔: 인덱스로 테이블 데이터를 접근하는 유형

테이블 풀 스캔 (table full scan)

• 인덱스를 거치지 않고 테이블로 바로 접근하여 처음부터 끝까지 데이터를 훑어보는 방식
• WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때, 인덱스 활용보다 테이블 풀 스캔이 효율적이라고 판단할 때 수행할 수 있습니다.

인덱스 범위 스캔 (index range scan)

• 인덱스를 범위(range) 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾는 방식
• ex) BETWEEN, AND, <, >, LIKE 구문 등에 활용
• 좁은 범위의 데이터를 스캔할 때는 효율적일 수 있으나, 넓은 범위 스캔 시 비효율적일 수 있습니다.

인덱스 풀 스캔 (index full scan)

• 인덱스를 처음부터 끝까지 수행하는 방식
• 테이블에 접근하지 않고, 인덱스로 구성된 열 정보만 요구하는 SQL 문에서 인덱스 풀 스캔이 수행
• 인덱스는 테이블보다 상대적으로 적은 양을 차지하므로 테이블 풀 스캔보다는 성능상 유리
• 하지만, 인덱스라는 오브젝트의 전 영역을 검색하는 방식인 만큼 검색 범위를 최대한 줄이는 방향으로 튜닝

인덱스 고유 스캔 (index unique scan)

• 기본 키나 고유 인덱스로 테이블에 접근하는 방식으로, 인덱스 사용 스캔 방식 중 가장 효율적인 방식
• WHERE 절에 = 조건문으로 작성하며, 해당 열이 PK나 unique index의 선두 열로 선정되었을 때 활용

최소한의 페이지(데이터 검색 최소 단위) 에 접근할 수 있도록 접근 범위를 줄이고, 효율적인 인덱스를 활용할 수 있도록 튜닝

 

카디널리티 (cardinality)

• '하나의 데이터 유형으로 정의되는 데이터 행의 갯수'
• 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수
• 전체 행에 대한 특정 열의 중복 수치를 나타내는 지표
• ex) 주민등록번호 -> unique, 카디널리티가 높다.
• ex) 성별 -> M/W, 카디널리티가 낮다.

콜레이션 (collation)

• 특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙 (utf8_bin, utf_8_general_ci)
• 캐릭터셋 (charset): 데이터 저장을 어떻게 할 것인가?
• 콜레이션 (collation): 데이터 정렬을 어떻게 할 것인가?

 

2. SQL 튜닝의 실행 계획 파헤치기

아래 실행 계획은 간단하게 정리하였습니다. 상세한 내용에 대해 학습하고 싶다면 아래 블로그를 참고해주세요:

https://zzang9ha.tistory.com/436

MySQL EXPLAIN 실행계획 마스터하기(feat. RealMySQL 8.0)

 

 

id

• SQL 문이 수행되는 실행 순서를 표시하는 숫자
• 조인할 때는 동일한 id

select_type

• SQL 문을 구성하는 SELECT 문의 유형을 출력하는 항목
• SIMPLE, PRIMARY, SUBQUERY, DERIVED, UNION, MATERIALIZED ...

table

• 테이블명 표시, 서브쿼리나 임시 테이블 시 <subquery#>, <derived#> 라고 출력

partitions

• 실행 계획의 부가 정보, 데이터가 저장된 논리적인 영역
• 너무 많은 영역의 파티션에 접근하는 것으로 출력된다면 파티션 정의를 튜닝

type

• 테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공하는 항목
• system, const, eq_ref, ref, range, index, ALL ...

possible_keys

• 옵티마이저가 SQL 문을 최적화하고자 사용할 수 있는 인덱스 목록 (실제로는 다를 수 있음)

keys

• 옵티마이저가 SQL 문을 최적화하고자 사용한 기본 키(PK) 또는 인덱스명
• 비효율적인 인덱스를 사용했거나, 인덱스 자체를 사용하지 않았다면 SQL 튜닝 대상

key_len

• 사용한 인덱스의 바이트 수

ref

• 테이블 조인을 수행할 때 어떤 조건으로 해당 테이블에 액세스 되었는지를 알려주는 정보

rows

• SQL 문을 수행하고자 접근하는 데이터의 모든 행(row) 수를 나타내는 예측 항목
• 디스크에서 데이터 파일을 읽고 메모리에서 처리해야 할 행 수를 예상하는 값 (정확하진 않음)
• SQL 문의 최종 결과 건수 대비 rows와 차이가 클 경우, 불필요하게 MySQL 엔진까지 데이터를 많이 가져왔다는 뜻이므로 SQL 튜닝의 대상

filtered

• SQL 문을 통해 DB 엔진으로 가져온 데이터 대상으로 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는지를 의미하는 항목
• DB 엔진으로 100건의 데이터 조회, 이후 WHERE 절으로 10건 필터링 되었다면, 값은 10(%)

extra

• SQL 문을 어떻게 수행할 것인지에 관한 추가 정보를 보여주는 항목
• Distinct, Using where, Using temporary, Using index, ...

 

이상으로 튜닝에 필요한 용어와 실행 계획들을 간략히 살펴보았고, 내용이 길어져 이후 포스팅에서 실제 데이터를 기반으로 다양한 쿼리의 문제점들에 대해 튜닝하며 개선해 보겠습니다.