SQL 튜닝 용어 정리(3)

개념적인 튜닝 용어

• 오브젝트들을 스캔하는 유형, 디스크 접근 방식 등 쿼리 튜닝과 관련된 용어를 설명한다.

오브젝트 스캔 유형

오브젝트 스캔 유형은 테이블 스캔(table scan)과 인덱스 스캔(index scan)으로 구분한다. 테이블 스캔은 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형이며, 인덱스 스캔은 인덱스로 테이블 데이터를 찾아가는 유형이다.

• 테이블 스캔 유형
  • 테이블 풀 스캔(Table Full Scan)
• 인덱스 스캔 유형
  • 인덱스 범위 스캔(Index Range Scan)
  • 인덱스 풀 스캔(Index Full Scan)
  • 인덱스 고유 스캔(Index Unique Scan)
  • 인덱스 루스 스캔(Index Loose Scan)
  • 인덱스 병합 스캔(Index Merge Scan)

테이블 풀 스캔

• 인덱스를 거치지 않고 테이블로 바로 직행하여 처음부터 끝까지 데이터를 훑어보는 방식.
• WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때 테이블 풀 스캔을 수행할 수 있다.
• 보통 처음부터 끝까지 데이터를 검색하므로 성능 측면에서는 부정적으로 해석된다.
• 인덱스 없이 사용하는 유일한 방식.

인덱스 범위 스캔

• 인덱스를 범위 기준으로 스캔한 뒤 스캔 결과를 토대로 테이블의 데이터를 찾아가는 방식.
• SQL 문에서 BETWEEN ~ AND 구문이나 <, >, LIKE 구문 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔으로 수행한다.
• 좁은 범위를 스캔할 때는 성능적으로 매우 효율적인 방식이지만 넓은 범위를 스캔할 때는 비효율적인 방식.

인덱스 풀 스캔

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

인덱스 고유 스캔

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

인덱스 루스 스캔

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

인덱스 병합 스캔

• 테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식.
• WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취한다.
• 통합하는 방법으로는 결합(union)과 교차(intersection) 방식이 있으며 이들 방식은 모두 실행 계획으로 출력된다.
• 물리적으로 존재하는 개별 인덱스를 각각 수행하므로 인덱스에 접근하는 시간이 몇 배로 걸린다. 따라서 별개로 생성된 인덱스들은 보통 하나의 인덱스로 통합하여 SQL 튜닝을 수행하거나, SQL 문 자체를 독립된 하나의 인덱스만 수행하도록 변경할 수 있다.

디스크 접근 방식

MySQL은 원하는 데이터를 찾으려고 데이터가 저장된 스토리지(storage)의 페이지(page)에 접근한다. 여기서 페이지란 데이터를 검색하는 최소 단위로, 페이지 단위로 데이터 읽고 쓰기를 수행할 수 있다. 서로 연결된 페이지를 차례대로 읽을 수도 있고, 여기저기 원하는 페이지를 임의로 열어보면서 데이터를 읽을 수도 있다.

• 시퀀셜 액세스(Sequential Access)
• 랜덤 액세스(Random Access)

시퀀셜 액세스

• 물리적으로 인접한 페이지를 차례대로 읽는 순차 접근 방식. 보통 테이블 풀 스캔에서 활용.
• 데이터를 찾고자 이동하는 디스크 헤더(disk header)의 움직임을 최소화하여 작업 시간과 리소스 점유 비용을 줄일 수 있다.
• 테이블 풀 스캔일 때는 인접한 페이지를 여러 개 읽는 다중 페이지 읽기(multi-page read) 방식으로 수행한다.

랜덤 액세스

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

조건 유형

SQL 문의 WHERE 절 조건문 기준으로 데이터가 저장된 디스크에 접근하게 된다. 이때 필요한 데이터에 액세스하는 조건문으로 데이터를 가져오고, 가져온 데이터에서 다시 한번 출력할 데이터만 추출한다. 이때 맨 처음 디스크에서 데이터를 검색하는 조건을 액세스 조건이라 하고, 디스크에서 가져온 데이터에서 추가로 추출하거나 가공 및 연산하는 조건을 필터 조건이라 한다.

• 액세스 조건(Access Condition)
• 필터 조건(Filter Condition)

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

액세스 조건

• 디스크에 있는 데이터에 어떻게 접근할 것인지를 다루는 액세스 조건은 SQL 튜닝에서 가장 중요한 핵심 사항이다.
• 옵티마이저는 WHERE 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건절을 선택하여, 스토리지 엔진의 데이터에 접근하고 MySQL 엔진으로 데이터를 가져온다.
• 위의 예제에서는 ID = 1 이 액세스 조건이 된다.

필터 조건

• 액세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건이다.
• 위의 예제에서는 CODE = 'A'가 필터 조건으로 액세스 조건으로 가져온 데이터를 필터링한다.
• 만약 필터 조건에 따라 필터링할 데이터가 없으면 훌륭한 SQL 문이고, 필터링되는 데이터가 많다면 비효율적인 SQL문이다.
  • 스토리지 엔진에서 MySQL 엔진으로 데이터를 전달하는 오버헤드가 있으며, 필터 조건으로 제거될 데이터라면 스토리지 엔진의 데이터에 접근하는 과정에서 같이 제외되는 편이 성능적으로 효율적이기 때문이다.
• 필터 조건으로 제거되는 데이터 비율을 확인하고 특정 SQL 문의 튜닝이 필요한지 판단할 수있다. 해당 비율은 실행 계획의 filtered 항목에서 확인할 수 있다.

응용 용어

• 선택도(Selectivity)
• 카디널리티(Cardinality)
• 힌트(Hint)
• 콜레이션(collation)
• 통계정보
• 히스토그램(histogram)

선택도

• 선택도란 테이블의 특정 열을 기준으로 해당 열의 조건절(WHERE 절 조건문)에 따라 선택되는 데이터 비율을 의미한다.
• 해당 열에 중복되는 데이터가 많다면 ‘선택도가 높다’이고 적다면 ‘선택도가 낮다’고 평가할 수 있다.
• 낮은 선택도를 가지는 열은 데이터를 조회하는 SQL문에서 원하는 데이터를 빨리 찾기 위한 인덱스 열을 생성할 때 주요 고려대상이 된다.
• 선택도 = 선택한 데이터 건수 / 전체 데이터 건수
  • 선택하는 조건절의 데이터 건수를 매번 계산할 수 없고 데이터의 삭제와 수정, 삽입이 수시로 발생하는 만큼, 보통은 중복이 제거된 데이터의 건수를 활용하여 선택도를 일반화한다.
  • 변형된 선택도 = 1 / DISTINCT(COUNT 열명)

카디널리티

• 사전적 정의는 하나의 데이터 유형으로 정의되는 데이터 행의 개수로 여기서는 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수를 가리킨다.
• 현업에서는 전체 행에 대한 특정 열의 중복 수치를 나타내는 지표로 자주 활용한다.
• 카디널리티 = 전체 데이터 건수 * 선택도
• MySQL에서는 중복을 제외한 유일한 데이터값의 수로 계산한다. 따라서 특정 열에 중복된 값이 많다면 카디널리티가 낮다고 할 수 있으며, 해당 열을 조회하면 상당수의 데이터를 거르지 못한 채 대량의 데이터가 출력되리라 예측할 수 있다.

힌트

• SQL문으로 뒤죽박죽 섞인 데이터를 찾아내라는 어려운 문제를 풀어야 할 때, DB에게 힌트를 전달함으로써 의도대로 작동하도록 도울 수 있다. 즉, 데이터를 빨리 찾을 수 있게 추가 정보를 전달하는 게 힌트이다.
• (학번(PK), 이름, 전공코드)인 테이블이 있고, 이름과 전공코드에 대한 인덱스가 생성되어 있다고 가정한다.

SELECT 학번, 전공코드
  FROM 학생
 WHERE 이름 = '유재석';

• 이름 인덱스를 통해 접근하는 방식이 효율적이므로 이름 인덱스를 사용하도록 명시한다.

# 주석처럼 힌트를 명시하는 방법
SELECT 학번, 전공코드
  FROM 학생 /*! USE INDEX (학생_IDX이름) */
 WHERE 이름 = '유재석';

# 쿼리의 일부로 힌트를 명시하는 방법
SELECT 학번, 전공코드
  FROM 학생 USE INDEX (학생_IDX이름)
 WHERE 이름 = '유재석';

주요 힌트 목록

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

강력하지 않은 힌트
명시적으로 힌트를 작성해도 옵티마이저는 무조건 힌트를 참고하지 않는다. 옵티마이저가 비효율적이라고 예측하면 사용자가 작성한 힌트는 무시될 수 있다.

힌트 사용 시 고려사항
힌트가 적용된 서비스 환경에서는 데이터 건수가 수시로 급변할 수 있고, 테이블이나 인덱스/뷰 등에 변화가 생기면 SQL 문 실행 시 오류가 발생할 가능성도 있다. 이때는 SQL 문에 힌트를 작성하면 별도로 관리가 필요하다.
- 예를 들어, MySQL과 MariaDB에서는 USE INDEX 힌트를 사용한 SQL 문이 있을 때, 해당 인덱스를 삭제하면 그 SQL 문은 에러를 발생시킨다.
- 오라클의 경우에는 해당 힌트를 무시하고 SQL 문을 실행한다.

콜레이션

• 특정 문자셋으로 데이터베이스에 저장된 값을 비교하거나 정렬하는 작업의 규칙을 의미한다.
• 숫자 10과 숫자 11 중에 어느 값이 더 큰 값일까? => 11 > 10 임을 알 수 있다.
• a와 A 중에 무엇이 더 클까? => 설정한 콜레이션에 따라 다르다.

콜레이션의 대소관계 비교

utf8_bin	utf8_general_ci
A	A
B	a
a	B
b	b

• 콜레이션은 데이터베이스 단위, 테이블 단위, 열 단위까지 세세하게 설정할 수 있다.
• 테이블에 콜레이션을 지정하면 컬럼 까지도 모두 같은 콜레이션이 지정되지만 별도로 컬럼까지 지정한다면 테이블과 컬럼 콜레이션은 다르게 지정될 수 있다.

CREATE TABLE '학생' (
  '학번' INT(10)      NOT NULL,
  '이름' VARCHAR(10)  NOT NULL COLLATE 'utf8_bin',
  '전공코드' CHAR(2)   NULL DEFAULT NULL,
  PRIMARY KEY('학번')
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

통계정보

• 옵티마이저는 통계정보에 기반을 두고 SQL 문의 실행 계획을 수립한다.
• 통계정보는 DBA가 맡을 역할이긴 하지만, 쿼리를 수행하는 역할자 또한 통계정보가 현재 최신으로 관리되는지, 오래된 통계정보 때문에 SQL 문이 엉뚱한 방향으로 수행되는지는 않는지를 확인할 수 있다.
• MySQL은 시스템 변수를 통해 활용할 통계정보의 수준을 정의할 수 있다.
  • 기본적으로는 테이블 통계정보, 인덱스 통계정보, 선택적인 열 통계정보를 토대로 어떤 인덱스를 활용해 데이터에 액세스할 것인지, 어떤 테이블을 드라이빙 테이블로 선택할지 등을 결정한다. 따라서 통계정보의 최신성 유지 및 관리가 매우 중요하다.

히스토그램

• 테이블의 열값이 어떻게 분포되어 있는지를 확인하는 통계정보이다.
• 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보로, 잘못된 히스토그램 정보가 있다면 잘못된 실행 계획으로 SQL 문이 수행될 수 있다. 만약 특정 열값들의 통계정보가 히스토그램으로 수집되지 않았다면, 중복이 제거된 열값의 개수(COUNT(DISTINCT 열명))로 대략적인 열값의 분포를 예측하고 실행 계획을 수립한다.
• MySQL에서는 내부적으로 열의 분포를 저장할 때는 높이균형 히스토그램(height balanced histogram)방식을 사용한다. 즉, 저장된 데이터값의 종류가 수백, 수천, 수만 개 이상이므로 이 데이터 값들을 그룹화하고, 정해진 양동이(bucket)만큼 분리해서 열의 통계정보 데이터를 저장한다.

실제 데이터베이스에서 관리하는 히스토그램의 버킷은 최댓값을 보관한다.
- WHERE 절 조건문에 Col1 = 'A' 라고 입력하면 버킷 1에만 접근하여 데이터 분포를 파악한다.
- Col1 BETWEEN E AND O라고 작성된 조건절이 있다면, 버킷 2부터 6까지 총 5개 버킷에 접근해야 한다.
- 이떄 전체 데이터 대비 50% 이상의 영역을 스캔해야 하므로 인덱스 스캔보다 테이블 풀 스캔으로 쿼리가 수행되도록 통계정보를 제공한다.

• 히스토그램 정보 직접 생성하기

# MySQL
# 문법
ANALYZE TABLE 테이블명 UPDATE HISTOGRAM ON 열명 나열;
# 예시
ANALYZE TABLE 사원 UPDATE HISTOGRAM ON 이름;
# 확인
SELECT * FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;