[MYSQL] 개념적인 튜닝 용어

오브젝트 스캔 유형

오브젝트 스캔 유형은 테이블 스캔(table scan)과 인덱스 스캔(index scan)으로 구분

테이블 스캔 : 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형

- 테이블 풀스 캔 방식

인덱스 스캔 : 인덱스로 테이블 데이터를 찾아가는 유형

- 인덱스 범위 스캔, 인덱스 풀 스캔, 인덱스 고유 스캔, 인덱스 루스 스캔, 인덱스 병합 스캔 방식

 

테이블 풀 스캔

인덱스를 거치지 않고 테이블로 바로 직행하여 처음부터 끝까지 데이터를 훑어보는 방식

WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나 전체 데이터 대비 대량의 데이터가 필요할 때 테이블 풀 스캔을 수행할 수 있음

성능 측면에서는 처음부터 끝까지 데이터를 검색하므로 부정적으로 해석 됨

 

 

인덱스 범위 스캔

인덱스 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식

SQL문에서 BETWEEN ~ AND 구문이나 <,>,LIKE 구문 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔으로 수행

좁은 범위를 스캔 : 성능적으로 매우 효율적인 방식

넓은 범위를 스캔 : 비효율적인 방식

 

인덱스 풀 스캔

인덱스를 처음부터 끝까지 수행하는 방식 (단, 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 SQL문에서 인덱스 풀 스캔이 수행)

인덱스는 테이블보다 상대적으로 적은 양을 차지하므로 테이블 풀 스캔 방식보다 인덱스 풀 스캔 방식이 성능상 유리

인덱스라는 오브젝트의 전 영역을 모두 검색하는 방식인 만큼 검색 범위를 최대한 줄이는 방향으로 SQL 튜닝 필요

 

인덱스 고유 스캔

기본 키나 고유 인덱스로 테이블에 접근하는 방식 (인덱스를 사용하는 스캔 방식 중 가장 효율적인 스캔 방법)

WHERE 절에 = 조건으로 작성하며, 해당 JOIN 열이 기본 키 또는 고유 인덱스의 선두 열로 설정되었을 때 활용

 

인덱스 루스 스캔

인덱스의 필요한 부분들만 골라 스캔하는 방식

인덱스 범위 스캔처럼 넓은 범위에 전부 접근하지 않고, WHERE 절 조건문 기준으로 필요한 데이터와 필요하지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시

보통 GROUP BY 구문이나 MAX(), MIN() 함수가 포함되면 작동

이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우

 

인덱스 병합 스캔

테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식

 

WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취함

통합하는 방법으로는 결합(union)과 교차(intersection) 방식이 있음, 모두 실행 계획으로 출력

물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간이 몇배 걸림

별개로 생성된 인덱스들은 보통 하나의 인덱스로 통합하여 SQL 튜닝을 수행하거나 SQL문 자체를 독립된 하나의 인덱스만 수행하도록 변경

---

디스크 접근 방식

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

 

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

시퀀셜 액세스 : 서로 연결된 페이지를 차례대로 읽을 수 있음

랜덤 액세스 : 여기저기 원하는 페이지를 임의로 열어 보면서 데이터를 읽을 수 있음

 

시퀀셜 액세스

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

데이터를 찾고자 이동하는 디스크 헤더(disk header)의 움직임을 최소화하여 작업 시간과 리소스 점유 비용을 줄일 수 있음

테이블 풀 스캔일 때 인접한 페이지를 여러개 읽는 다중 페이지 읽기(multi-page read) 방식으로 수행

페이지 읽는 순서 : 1 → 2 → 3 → 4 → 5 → 6 → 7

 

랜덤 액세스

물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식 (페이지가 위치한 물리적인 위치를 고려하지 않고 접근)

데이터의 접근 수행이 오래 걸림

최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝 필요

페이지 읽는 순서 : 1 → 6 → 3 → 7 → 5 → 2 → 4 

---

조건 유형

SQL문의 WHERE 절 조건문 기준으로 데이터가 저장된 디스크에 접근

필요한 데이터에 액세스하는 조건문으로 데이터를 가져오고 가져온 데이터에서 다시 한번 출력할 데이터만 추출

액세스 조건 : 맨 처음 디스크에서 데이터를 검색하는 조건

필터 조건 : 디스크에서 가져온 데이터에서 추가로 추출하거나 가공 및 연산하는 조건



액세스 조건

디스크에 있는 데이터에 어떻게 접근할 것인지를 다루는 액세스 조건(access condition)은 SQL 튜닝에서 가장 중요한 핵심 사항

옵티마이저는 WHERE 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건절을 선택하여, 스토리지 엔진의 데이터에 접근하고 MYSQL 엔진으로 데이터를 가져옴 

 

1. WHERE ID = 1 AND COde = 'A'

2. ID 열로 생성된 인덱스(Table2_Index)를 활용해서 Table2 테이블의 일부 데이터에 접근

3. ID = 1 조건문이 액세스 조건, 만약 CODE = 'A' 조건문을 액세스 조건으로 삼아 데이터에 접근한다면 인덱스 활용 없이 대량의 데이터에 접근할 것으로 예측 가능

필터 조건

액세스 조건을 이용해 MYSQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건

액세스 조건으로 가져온 데이터를 대상으로 필터조건인 CODE = 'A'를 적용해 필터링 작업

 

필터 조건에 따라 필터링할 데이터가 없다면 매우 훌륭한 SQL문이고, 필터 조건으로 필터링되어 제거된 데이터가 다수 존재한다면 상대적으로 비효율적인 SQL문

 

WHY?

스토리지 엔진에서 MYSQL 엔진으로 데이터를 전달하는 오버헤드가 있으며, 필터 조건으로 제거될 데이터라면 스토리지 엔진의 데이터에 접근하는 과정에서 같이 제외되는 편이 성능적으로 효율적이기 때문

 

필터 조건으로 제거되는 데이터 비율을 확인하고 특정 SQL문의 튜닝이 필요한지 판단할 수 있음(실행 계획의 filtered)

* 참고

- 업무에 바로쓰는 SQL 튜닝(도서) - 2장 SQL 튜닝 용어를 직관적으로 이해하기