MySQL - SQL 튜닝 용어(4): 개념적인 튜닝 용어

최건우·2023년 9월 2일

Database RDBMS sql

데이터베이스/SQL

목록 보기

6/13

개념적인 튜닝 용어

1. 오브젝트 스캔 유형

오브젝트를 스캔하는 유형에는 테이블 스캔(table scan), 인덱스 스캔(index scan)으로 구분한다.

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

현실에서 많이 사용하는 스캔 방식을 알아보자.

테이블 풀 스캔

테이블 풀 스캔(table full scan)은 인덱스를 거치지 않는 유일항 방식으로, 테이블을 처음부터 끝까지 조회하여 조건에 맞는 데이터를 검색하는 방식이다.
활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때 활용할 수 있다. 단, 전체 데이터를 끝까지 모두 검색하므로 인덱스 스캔에 비해 성능이 다소 떨어진다.

인덱스 범위 스캔

인덱스 범위 스캔(index range scan)은 인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식이다.
SQL 문에서 BETWEEN ~ AND, <, >, LIKE 과 같은 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔으로 수행한다.

좁은 범위에서는 효율이 매우 좋지만, 넓은 범위를 스캔할 때는 비효율적인 방식이다.

인덱스 풀 스캔

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

일반적으로 인덱스는 테이블보다 적은 양을 차지하므로, 테이블 풀 스캔보다는 성능상 유리하다. 그러나 역시 풀 스캔인 만큼 검색 범위를 최대한 줄이는 방향으로 SQL 튜닝이 필요하다.

인덱스 고유 스캔

인덱스 고유 스캔(index unique scan)은 Primary Key나 Unique index로 테이블에 접근하는 방식으로, 인덱스를 사용하는 스캔 방식 중 가장 효율적인 스캔 방법이다. WHERE 절에 = 조건으로 작성하며, 해당 조인 열이 Primary Key 혹은 Unique index의 선두 열로 설정되었을 때 활용한다.

인덱스 루즈 스캔

인덱스 루즈 스캔(index loose scan)은 인덱스의 필요한 부분들만 골라 스캔하는 방식이다. 넓은 범위에 전부 접근하지 않고, WHERE절 조건문 기준으로 불필요한 데이터의 인덱스 키는 무시한다. 보통 GROUP BY, MAX(), MIN() 함수가 포함되면 작동한다. 이미 오름차순으로 정렬된 인덱스에서 최댓값이나 최솟값이 필요한 경우가 이에 해당한다.

인덱스 병합 스캔

인덱스 병합 스캔(index merge scan)은 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식이다. WHERE 문 조건절이 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취한다. 통합 방법으로 결합(union), 교차(intersection) 방식이 있으며 이들 모두 실행 계획으로 출력된다.

인덱스 병합 스캔은 개별 인덱스를 각각 수행하므로, 인덱스 접근 시간이 몇 배로 걸린다. 이를 해결하기 위해 다음과 같은 SQL 튜닝 방식을 시도해 볼 수 있다.

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

2. 디스크 접근 방식

MySQL은 스토리지(storage)에 저장된 데이터를 검색하는 최소 단위를 페이지(page)라고 부른다. 페이지 단위로 데이터 읽기/쓰기를 수행할 수 있는데, 서로 연결된 페이지를 차례대로 접근하는 방식을 시퀀셜 액세스, 임의로 접근하며 데이터를 읽는 방식을 랜덤 액세스 라고 한다.

시퀀셜 액세스

시퀀셜 액세스(sequential access)는 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식으로, 테이블 풀 스캔에서 주로 활용한다. 디스크 헤더(disk header)가 데이터를 찾기 위해 이동하는 움직임을 최소화하여 작업 시간과 리소스 점유 비용을 줄일 수 있다.

테이블 풀 스캔에서는 다중 페이지 읽기(multi-page read) 방식을 수행하여, 인접한 페이지 여러 개를 읽는다.

랜덤 액세스

랜덤 액세스(random access)는 물리적으로 떨어진 페이지들에 임의로 접근하는 임의 접근 방식이다. 페이지가 위치한 물리적인 위치를 고려하지 않고 접근한다.

디스크 헤더가 정해진 순서 없이 이동하고 다중 페이지 읽기가 불가능하기 때문에, 데이터 접근 수행 시간이 오래 걸린다. 따라서 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝해야 한다.

3. 조건 유형

SQL 문의 WHERE 절의 조건문을 기준으로 데이터가 저장된 디스크에 접근하게 된다. 이때, 조건문에서 사용되는 조건의 유형은 다음과 같이 두 가지이다.

액세스 조건: 맨 처음 디스크에서 필요한 데이터를 검색하기 위한 조건.
필터 조건: 디스크에서 가져온 데이터에서 추가로 추출하거나, 가공/연산하는 조건.

액세스 조건

액세스 조건(access condition)은 데이터에 어떤 접근할 것인지를 다룬다. 옵티마이저는 WHERE 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건절을 선택하여, 스토리지 엔진의 데이터에 접근하고 MySQL 엔진으로 데이터를 가져온다.

다음 SQL 문에서는 ID=1, CODE='A'라는 두 가지 조건문이 있지만, 가장 먼저 ID열로 생성된 인덱스를 활용해 테이블의 일부 데이터에 접근한다. 즉, ID=1이 액세스 조건인 것이다. 만약 인덱스가 없는 조건문을 액세스 조건으로 삼아 데이터에 접근한다면, 대량의 데이터에 접근하여 검색 효율이 떨어질 것이다.

SELECT *
FROM table
WHERE ID = 1
AND CODE = 'A';

필터 조건

필터 조건(filter condition)은 액세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건이다. 앞서 ID=1으로 액세스한 데이터에 CODE = 'A'라는 필터 조건을 적용해 필터링 작업을 한다.

스터리지 엔진에서 MySQL 엔진으로 데이터를 전달할 때는 오버헤드가 있다. 따라서 필터 조건으로 제거될 데이터라면 액세스 조건에서 같이 제외되는 편이 성능적으로 효율적일 것이다. 그러므로, 필터 조건에 따라 필터링할 데이터가 적거나 없다면 좋은 SQL 문이고, 다수 존재한다면 비교적 효율이 떨어지는 SQL 문이다.

이때, SQL 실행 계획의 filtered 항목을 보면 필터 조건으로 제거되는 데이터의 비율을 확인할 수 있고, 특정 SQL 문의 튜닝이 필요한지 판단할 수 있다.

4. 그 외의 개념적인 용어들

선택도

선택도(selectivity)란, 테이블의 특정 열을 기준으로 해당 열의 WHERE 절 조건문에 따라 선택되는 데이터 비율을 의미한다. 만약 해당 열에 중복되는 데이터가 많다면 선택도가 높고 대량의 데이터가 선택될 것이고, 중복되는 데이터가 적다면 선택도가 낮고 소량의 데이터가 선택될 것이다.

낮은 선택도를 가지는 열은 데이터를 조회하는 SQL 문에서 원하는 데이터를 빨리 찾기 위한 인덱스 컬럼을 생성할 때 주요 고려대상이 된다.
선택도를 계산하는 수식은 다음과 같다. 특정 열에 대한 조건문을 작성하고, 해당 조건문에 포함되는 열의 선택도를 산출한다.

$선택도=선택한 \ 데이터 \ 건수 / \ 전체 \ 데이터 \ 건수$

그러나 보통은 중복이 제거된 데이터의 건수를 활용하여 선택도를 일반화한다.

$선택도= 1 \ / \ COUNT(DISTINCT \ column\_name)$

선택도를 구하는 예시를 살펴보자.

전체 데이터가 n개인 테이블에서 Primary Key인 컬럼은 어떤 것을 선택하더라도 항상 하나의 유일한 값을 출력하므로, 선택도는 1/n이다.
총 100건의 데이터 중 '성별' 컬럼에서 '남', '여'가 각각 50건 씩이라면, 성별 컬럼에서 '여' 조건에 대한 선택도는 50/100 = 0.5이다.

카디널리티

카디널리티(cardinality)란 하나의 데이터 유형으로 정의되는 데이터 행의 개수로, 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수를 의미한다. 전체 행에 대한 특정 열의 중복 수치를 나타내는 지표로 자주 활용한다.

카디널리티는 전체 데이터 건수에 해당 열의 선택도를 곱해서 계산할 수 있다. 열별 선택도를 알고 있다면 카디널리티를 정량적 수치로 산출할 수 있다.

$카디널리티= 전체 \ 데이터 \ 건수 \ * \ 선택도$

특정 열에 중복되는 값이 적다면 카디널리티가 높다고 하며, 중복된 데이터를 제거한 뒤 소수의 데이터만 출력되리라 예상할 수 있다. 즉, 중복도가 높으면 카디널리티가 낮고, 중복도가 낮으면 카디널리티가 높다.

카디널리티가 높은 데이터 예시
- 주민등록번호, 휴대폰 번호, 계좌번호(중복이 전혀 없음, 중복성 0)
카디널리티가 보통인 데이터 예시
- 이름(동명이인이 존재함)
카디널리티가 낮은 데이터 예시
- 성별(남성, 여성 2가지 종류만 있음).
- 질병 여부, 졸업 여부 등 true, false 유형의 데이터

힌트

힌트(hint)란 데이터베이스가 데이터를 빨리 찾을 수 있도록 전달하는 추가 정보를 의미한다. 힌트는 옵티마이저에게 전달되는데, 옵티마이저는 실행계획을 세울 때 이 힌트를 참고해서 세울 수 있다. 단, 작성된 힌트가 비효율적이라고 예측할 경우 옵티마이저는 힌트를 무시한다.

힌트는 SQL 문에 직접 표시하는데, 주석의 형태(/*! */ 사용)로 쓰는 방법도 있고 주석 없이 쿼리의 일부로 작성하는 방법이 있다.

# 1. 주석으로 힌트 명시
SELECT
	학번, 전공코드
FROM
	학생 /*! USE INDEX (학생_IDX01) */
WHERE 이름 = '최건우';


# 2. 주석 표기 없이 쿼리의 일부로 작성
SELECT
	학번, 전공코드
FROM
	학생 USE INDEX (학생_IDX01)
WHERE 이름 = '최건우';

MySQL에는 수십 개의 힌트가 있는데, 자주 쓰이는 힌트를 몇 가지 꼽자면 다음과 같다.

힌트	설명	활용도
STRAIGHT_JOIN	FROM 절에 작성된 테이블 순으로 조인을 유도하는 힌트	높음
USE INDEX	특정 인덱스를 사용하도록 유도하는 힌트	높음
FORCE INDEX	특정 인덱스를 사용하도록 강하게 유도하는 힌트	낮음
IGNORE INDEX	특정 인덱스를 사용하지 못하도록 유도하는 힌트	중간

힌트를 적용할 때 주의할 점은, 힌트가 적용된 서비스 환경에서는 테이블이나 인덱스/뷰 등에 변화가 생기면 SQL 문 실행 시 오류가 발생할 가능성이 있다는 점이다. 예를 들어 어느 날 어떤 인덱스가 삭제되었는데 SQL 문의 힌트가 여전히 해당 인덱스를 참조하도록 되어있다면, SQL 문은 실행되지 못하고 서비스 장애로 이어질 것이다. 따라서 SQL 문에 명시한 힌트는 별도로 관리를 해야 한다.

콜레이션

콜레이션(collation)은 특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙을 의미한다. 설정된 콜레이션에 따라 문자열의 대소관계 판단 결과가 달라진다.

예를 들어 utf8_bin과 utf8_general_ci는 알파벳 대소문자의 대소관계를 다음과 같이 판단한다.

- utf8_bin: A > B > a > b
- utf8_general_ci: A > a > B > b

콜레이션은 데이터베이스 단위, 테이블 단위, 컬럼 단위까지 세세하게 설정될 수 있다. 만약 어떤 테이블의 콜레이션이 utf8_general_ci라면, 컬럼 단에 콜레이션이 명시되어있지 않더라도 테이블의 콜레이션이 그대로 적용된다. 하지만 컬럼에 utf8_bin으로 콜레이션을 명시했다면, 상위 단위인 테이블의 콜레이션을 무시하고 utf8_bin으로 설정된다.

참고로, 캐릭터셋(character set)과 콜레이션(collation)은 다음과 같은 차이가 있다(참고: MySQL Character Sets 과 Collation 의 의미).

캐릭터셋(character set): 데이터 저장을 어떻게 할 것인지에 대한 방식
- 영문 + 숫자
- 중국어
- 다국어(utf8)
- 다국어 + 이모지(utf8mb4)
콜레이션(collation): 데이터 정렬을 어떻게 할 것인지에 대한 방식
- a와 A 간의 대소관계 정의
- a와 b 간의 대소관계 정의

통계정보

옵티마이저는 통계정보에 기반을 두고 SQL 문의 실행계획을 수립한다. 통계정보가 최신으로 관리되지 않으면, 오래된 통계정보 때문에 SQL 문이 엉뚱한 방향으로 수행될 수도 있다.

MySQL에서는 시스템 변수를 통해 활용할 통계정보의 수준을 정의할 수 있다. 기본적으로 테이블, 인덱스, 선택적인 열 통계정보 등을 토대로 어떤 인덱스를 활용해 데이터에 액세스할 것인지, 어떤 테이블을 드라이빙 테이블로 선택할지 등을 결정한다. 따라서 통계정보의 최신성 유지 및 관리가 중요하다.

통계정보에 대해서는 아래 공식 문서를 참고해 보자.

히스토그램

히스토그램(histogram)은 테이블의 컬럼값이 어떻게 분포되어 있는지를 확인하는 통계정보이다. 옵티마이저가 실행계획을 최적화하고자 참고하는 정보로, 잘못된 히스토그램 정보가 있다면 잘못된 실행계획으로 SQL 문이 수행될 수 있다.

컬럼의 분포를 저장할 때는 높이균형 히스토그램(height balanced histogram) 방식을 사용한다. 수백, 수천, 수만 가지의 저장된 데이터값들을 그룹화하고, 정해진 수의 bucket만큼 분리해서 열의 통계정보 데이터를 저장하는 방식이다. 이때 각각의 버킷에 동일한 건수의 데이터가 들어가도록 데이터를 정렬 및 그룹화한다.