기초 용어
오브젝트 스캔 유형
오브젝트 스캔 유형은 테이블 스캔과 인덱스 스캔으로 구분한다. 테이블 스캔은 인덱스를 거치지 않고 바로 디스크에 위치한 테이블 데이터에 접근하는 유형이며, 인덱스 스캔은 테이블 데이터를 찾아가는 유형이다.
테이블 풀 스캔
인덱스를 거치지 않고 테이블로 바로 직행하여 처음부터 끝까지 데이터를 훑어보는 방식이다. WHERE 절의 조건문을 기준으로 활용할 인덱스가 없거나, 전체 데이터 대비 대량의 데이터가 필요할 때 테이블 풀 스캔을 활용할 수 있다. 보통 성능 측면에서 부정적으로 해석된다.
인덱스 범위 스캔
인덱스를 범위 기준으로 스캔한 뒤 스캔 결과르 토대로 테이블 데이터를 찾아가는 방식이다. BETWEEN~AND 구문이나 <,>,LIKE 구문 등 비교 연산 및 구문에 포함될 경우 인덱스 범위 스캔으로 수행한다. 넓은 범위를 스캔할 때는 비효율적이다.
인덱스 풀 스캔
말 그대로 인덱스를 처음부터 끝까지 탐색하는 방식이다. 단, 테이블에 접근하지 않고 인덱스로 구성된 열 정보만 요구하는 쿼리에서 인덱스 풀 스캔이 수행된다. 테이블 풀 스캔 방식보다 유리하나, 인덱스 전 영역을 검색하기에 검색 범위를 줄이는 방향으로 쿼리 튜닝이 필요하다.
인덱스 고유 스캔
기본 키, 고유 인덱스로 테이블에 접근하는 방식으로, 인덱스를 사용하는 스캔 중 가장 효율적인 방법이다. WHERE절에 =조건으로 작성하며, 해당 조인 열이 기본 키 또는 고유 인덱스의 선두 열로 설정되었을 때 활용한다.
인덱스 루즈 스캔
인덱스의 필요한 부분만 골라 스캔하는 방식이다. WHERE 절 조건문 기준으로 필요한 데이터와 그렇지 않은 데이터를 구분한 뒤 불필요한 인덱스 키는 무시한다. 보통 GROUP BY 구문이나 MAX,MIN 함수가 포함되면 작동한다. 이미 오름차순으로 정렬된 인덱스에서 최대,최소값이 필요한 경우가 해당한다.
인덱스 병합 스캔
테이블 내에 생성된 인덱스들을 통합해서 스캔하는 방식이다. WHERE 문 조건절의 열들이 서로 다른 인덱스로 존재하면 옵티마이저가 해당하는 인덱스를 가져와서 모두 활용하는 방식을 취한다. 통합 방법으로는 결합,교차가 있으며 모두 실행 계획으로 출력된다. 물리적 개별 인덱스를 각각 수행하므로 인덱스 접근 시간이 몇 배로 걸린다. 따라서 하나의 인덱스로 통합하거나, 쿼리 자체를 독립된 하나의 인덱스만 수행하도록 튜닝을 진행할 수 있다.
디스크 접근 방식
MySQL은 데이터가 저장된 스토리지의 페이지에 접근한다. 페이지는 데이터를 검색하는 최소 단위이다.
시퀀셜 액세스
물리적으로 인접한 페이지에 차례대로 읽는 순차 접근 방식으로, 보통 테이블 풀 스캔에서 활용한다. 데이터를 찾고자 이동하는 디스크 헤더의 움직임을 최소화하여 작업 시간, 리소스 점유 비용을 줄일 수 있다. 테이블 풀 스캔일 때는 인접한 페이지를 여러 개 읽는 다중 페이지 읽기(MPR)방식으로 수행한다.
랜덤 액세스
물리적으로 떨어진 페이지들에 임의로 접근하는 방식으로, 페이지의 물리적 위치를 고려하지 않고 접근한다. 접근 수행 시간이 오래 걸리므로, 최소한의 페이지에 접근할 수 있도록 접근 범위를 줄이고 효율적인 인덱스를 활용할 수 있도록 튜닝해야 한다.
조건 유형
쿼리의 WHERE 조건문 기준으로 데이터가 저장된 디스크에 접근하게 된다. 이때 필요한 데이터에 액세스하는 조건문으로 데이터를 가져오고, 가져온 데이터에서 출력할 데이터만을 추출한다.
액세스 조건
디스크의 데이터에 어떻게 접근할 것인지를 다루는 액세스 조건은 SQL 튜닝의 핵심이다. 옵티마이저는 WHERE 절의 특정 조건문을 이용해 소량의 데이터를 가져오고, 인덱스를 통해 시간 낭비를 줄이는 조건절을 선택하여, 스토리지 엔진의 데이터에 접근하고 MySQL 엔진으로 데이터를 가져온다.
필터 조건
액세스 조건을 이용해 MySQL 엔진으로 가져온 데이터를 기준으로, 추가로 불필요한 데이터를 제거하거나 가공하는 조건이다. 만약 필터 조건에 따라 필터링할 데이터가 없다면 매우 훌륭한 쿼리이고, 필터링된 데이터가 많다면 비효율적인 쿼리라 볼 수 있다. 스토리지 엔진에서 MySQL 엔진으로 데이터를 전달하는 오버헤드가 있으며, 필터 조건으로 제거될 데이터라면 스토리지 엔진의 데이터에 접근하는 과정에서 같이 제외되는 편이 성능적으로 효율적이기 때문이다. 필터 조건으로 제거되는 데이터 비율은 실행 계획의 filtered 항목에서 확인할 수 있다.
응용 용어
선택도
테이블의 특정 열을 기준으로 해당 열의 조건절(WHERE 조건문)에 따라 선택되는 데이터 비율을 의미한다. 만약 해당 열에 중복되는 데이터가 많다면 선택도가 높다고 평가할 수 있으며, 실제로 대량의 데이터가 선택될 것이다. 낮은 선택도는 대용량 데이터에서 원하는 데이터만 골라내는 능력으로 볼 수 있으며, 낮은 선택도를 가지는 열은 데이터를 조회하는 쿼리에서 인덱스 열을 생성할 때 주요 고려대상이 된다.
이런 선택도를 계산하는 수식은 다음과 같다.
선택도 = 선택한 데이터 건수 / 전체 데이터 건수
보통은 중복이 제거된 데이터 건수를 활용하여 선택도를 일반화한다.
변형된 선택도 = 1 / DISTINCT(COUNT 열명)
다음과 같은 쿼리를 통해 선택도를 도출할 수 있다.
SELECT 1 / COUNT(DISTINCT id) -- selectivity
FROM student;카디널리티
사전적 정의는 하나의 데이터 유형으로 정의되는 데이터 행의 개수로 전체 데이터에 접근한 뒤 출력될 것이라 예상되는 데이터 건수를 가리킨다. 현업에서는 전체 행에 대한 특저 열의 중복 수치를 나타내는 지표로 자주 활용한다. 카디널리티는 다음과 같은 수식으로 도출할 수 있다.
카디널리티 = 전체 데이터 건수 x 선택도
MySQL에서 게산하는 방식으로 카디널리티를 정의해보면, 중복을 제외한 유일한 데이터값의 수로 계산할 수 있다. 따라서 특정 열에 중복된 값이 많다면 카디널리티가 낮다고 할 수 있으며, 해당 열을 조회하면 상당 수의 데이터를 거르지 못한 채 대량의 데이터가 출력되리라 예측할 수 있다.
즉, 중복도가 높으면 카디널리티가 낮고 중복도가 낮으면 카디널리티가 높다고 정리할 수 있다. 일상에서 주민번호같은 데이터는 카디널리티가 높고, 성별같은 데이터는 카디널리티가 낮다고 볼 수 있다.
힌트
DB에 힌트를 전달해 데이터를 더 빨리 찾을 수 있게 도울 수 있다. 인덱스를 힌트로 활용하여 빠른 조회를 유도하는 쿼리가 대표적인 예시이다.
--명시적 힌트
SELECT id, major_code
FROM student /*! USE INDEX (student_IDX01) */
WHERE name = '민재'
--쿼리의 일부
SELECT id, major_code
FROM student USE INDEX (student_IDX01)
WHERE name = '민재'
자주 쓰이는 힌트는 다음과 같이 정리할 수 있다.
| 힌트 | 설명 | 활용도 |
|---|---|---|
| STRAIGHT_JOIN | FROM 절에 작성된 테이블 순으로 조인을 유도하는 힌트 | 높음 |
| USE INDEX | 특정 인덱스를 사용하도록 유도하는 힌트 | 높음 |
| FORCE INDEX | 특정 인덱스를 사용하도록 강하게 유도하는 힌트 | 낮음 |
| IGNORE INDEX | 특정 인덱스를 사용하지 못하도록 유도하는 힌트 | 중간 |
강력하지 않은 힌트
명시적으로 힌트를 작성해도 옵티마이저가 비효율적이라 판단하면 힌트를 무시할 수 있다.
힌트 사용시 고려사항
DB의 테이블, 인덱스/뷰 등엔 잦은 변화가 발생한다. 따라서 힌트를 작성한 쿼리는 별도로 관리해야 한다.
SELECT *
FROM student USE INDEX(student_IDX01)
WHERE name=?;만약 student_IDX01 이 삭제된 상태에서 위 쿼리를 실행하면 오류가 발생하고 서비스 장애로 이어질 수 있다. MySQL/MariaDB에서는 작성된 힌트 오브젝트가 존재하지 않으면 에러가 발생하기에 유의해야 한다. 오라클의 경우 이를 무시하고 실행된다.
콜레이션
특정 문자셋으로 DB에 저장된 값을 비교하거나 정렬하는 작업의 규칙을 의미한다. 문자의 경우 이 콜레이션에 따라 대소 관계가 달라질 수 있다. 아래는 일반적으로 많이 사용되는 콜레이션의 대소관계 예시다.
| utf8_bin | utf8_general_ci |
|---|---|
| A | A |
| B | a |
| a | B |
| b | b |
콜레이션은 DB, 테이블, 심지어 열 단위까지 상세히 설정 가능하다.
CREATE TABLE student (
id INT(10) NOT NULL,
name VARCHAR(10) NOT NULL COLLITAE 'utf8_bin',
major_code CHAR(2) NULL DEFAULT NULL,
PRIMARY KEY (id)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;캐릭터셋 vs 콜레이션
캐릭터셋은 ‘데이터 저장을 어떻게 할 것인가’(ex. 영문 + 숫자)에 관한 규칙이고 콜레이션은 ‘데이터 정렬을 어떻게 할 것인가’에 관한 규칙으로 차이가 있다.
히스토그램
테이블의 열값이 어떻게 분포되어 있는지를 확인하는 통계정보이다. 옵티마이저가 실행 계획을 최적화하고자 참고하는 정보로, 잘못된 히스토그램 정보가 있다면 쿼리가 잘못 수행될 수 있다. 기본적으로 중복이 제거된 열값의 개수(COUNT (DISTINCT col))로 대략적인 열값의 분포를 예측하고 실행 계획을 수립한다.
MySQL에서는 내부적으로 높이균형 히스토그램 방식을 사용한다. 데이터 값들을 그룹화하고 버킷만큼 분리해서 열의 통계정보 데이터를 저장한다.
히스토그램 생성 예시
--MySQL
ANALYZE TABLE table_name UPDATE HISTOGRAM ON col_name, col_name2;
SELECT * FROM INFORMATION_SCHEMA_COLUMN_STATISTICS; -- 결과 확인