9. 옵티마이저와 힌트
MySQL에서도 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 최적의 실행 계획을 수립하는 작업을 담당한다.
- 쿼리 실행 절차
- SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
- SQL 파싱이라고 하며,
SQL 파서라는 모듈로 처리한다. - 문법적으로 잘못됐다면 이 단계에서 걸러진다. 또한 이 단계에서
SQL 파스 트리가 만들어 진다.
- SQL 파싱이라고 하며,
- SQL의 파싱 정보를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
- 불필요한 조건 제거 및 복잡한 연산의 단순화
- 여러 테이블 조인이 있는 경우 어떤 순서로 읽을지 결정
- 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
- 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
- 두 번째 단계에서 결정된 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로 부터 데이터를 가져온다
- SQL 문장을 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리한다.
- 옵티마지어의 종류
- 비용 기반 최적화
- 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.
- 대부분의 DBMS 채택
- 규칙 기반 최적화
- 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
- 비용 기반 최적화
기본 데이터 처리
풀 테이블 스캔과 풀 인덱스 스캔
MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.
- 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 빠른 경우 (일반적으로 1개의 페이지인 경우)
WHERE or JOIN ON에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우- 인덱스 레인지 스캔을 사용할 수 있지만 옵티마이저가 판단하기에 일치 레코드 건수가 너무 많은 경우
대부분의 DBMS는 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다. 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤이(Read ahead) 작업이 자동으로 시작된다. 리드 어헤이란 어떤 영역의 데이터가 앞으로 필요할 것을 예측해서 요청이 오기 전에 미리 읽어 버퍼 풀에 가져다 주는 것을 말한다.
SQL 요청 -> 포그라운드 스레드가 페이지를 읽음 -> 백그라운드 스레드가 읽기를 넘겨 받음 -> 4개 8개 .. 최대 64개의 페이지를 한번에 읽어 버퍼 풀에 데이터를 올림
innodb_read_ahead_threshold로 리드 어헤이 시작 값을 설정할 수 있다. 리드 어헤드는 풀 인덱스 스캔에도 동일하게 사용된다.
병렬 처리
innodb_parallel_read_threads 변수를 통해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다. WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.
ORDER BY 처리(Using filesort)
| 장점 | 단점 | |
|---|---|---|
| 인덱스 이용 | DML 실행 시 이미 인덱스가 정렬돼 있어서 매우 빠르다 | 디스크 WRITE 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 공간이 더 필요하다 인덱스가 늘어날수록 버퍼 풀을 위한 메모리가 필요하다 |
| Filesort 이용 | 인덱스를 생성하지 않아도 됨으로 인덱스의 단점이 장점이 된다. 레코드가 많이 않으면 메모리에서 정렬함으로 충분히 빠르다 | 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다. |
소트 버퍼
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 공간을 소트 버퍼라고 한다. 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 공간은 sort_buffer_size 값으로 설정할 수 있다. 만약 처리해야 할 레코드 건수가 버퍼 크기보다 큰 경우는 임시 저장을 위해 디스크를 사용한다.
그렇다면 버퍼 공간을 무작정 크게 늘린다고 해도, 비례해서 성능이 높아지는 것은 아니다. MySQL의 소트 버퍼 크기가 256KB ~ 8MB 사이에서 최적의 성능을 보였다. 소트 버퍼의 크기는 세션별 생성됨으로 큰 공간을 부여하게 되면 메모리 부족으로 인해 프로세스가 멈출 수 있다.
정렬 알고리즘
- 싱글 패스
정렬 기준 칼럼을 포함해SELECT대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 방법 - 투 패스 정렬 방식
정렬 대상 칼럼과 PK 키 값만 소트 버퍼에 담아서 정렬을 수행하고 정렬된 순서대로 다시 PK 키로 테이블을 읽어서SELECT할 칼럼을 가져오는 방식
일반적으로 싱글 패스 정렬 방식을 사용하며, 다음의 경우 투 패스 정렬 방식을 사용한다.
- 레코드의 크기가
max_length_for_sort_data설정된 값보다 클 때 - BLOB 이나 TEXT 타입의 칼럼이
SELECT대상에 포함될 때
정렬 처리 방법
쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 방법 중 하나로 처리된다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.
| 정렬 처리 방법 | 실행 계획의 Extra 컬럼 내용 | 정렬 방법 선택 |
|---|---|---|
| 인덱스를 사용한 정렬 | 별도 표기 없음 | ORDER BY에 명시된 칼럼이 드라이빙 테이블에 속하고, 순서대로 생성된 인덱스가 있어야한다. |
| 조인에서 드라이빙 테이블만 정렬 | "Using filesort" | 조인이 수행되면 결과 레코드가 몇 배로 불어나기에, 조인을 실행하기전 첫 번째 테이블의 레코드를 먼저 정렬한 다음에 조인을 실행ORDER BY에 명시된 칼럼이 드라이빙 테이블 |
| 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 | "Using temporary; Using filesort" | ORDER BY에 드리븐 테이블 칼럼인 경우 |
정렬 처리 방법의 성능 비교
SQL 쿼리에서 ORDER BY와 함께 LIMIT이 거의 필수로 사용되는 경향이 있다. 일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기 때문에 처리해야 할 작업량을 줄일 수 있다. 하지만 ORDER BY or GROUP BY는 만족하는 레코드를 모두 가져와 정렬 후에 LIMIT로 건수를 제한할 수 있음으로 주의해야 한다.
인덱스를 사용하지 못하는 sort or grouping 작업이 느리게 작동할 수 밖에 없는지 알아보자
- 스트리밍 방식
조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송하는 방식, 빠른 응답 시간을 보장해준다
마지막 레코드는 언제 받는지 중요하지 않고LIMIT를 추가하면 시간을 상당히 줄일 수 있다. - 버퍼링 방식
ORDER BY or GROUP BY는 전체 데이터를 가져온 후 처리됨으로 스트리밍은 불가능 하다. 클라이언트는 기달려야하기에 응답 속도가 느려지고 이것을 버퍼링이라고 표현
GROUP BY 처리
GROUP BY 있는 쿼리에서는 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없음으로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다
GROUP BY 인덱스를 사용하는 경우는 인덱스 스캔 방법과 루스 인덱스 스캔이다. 사용하지 못하는 이유는 임시 테이블을 사용한다.
DISTINCT 처리
SELECT DISTINCT
단순히 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 GROUP BY와 동일한 방식으로 처리된다.
모든 칼럼의 조합이 유니크한 것들만 가져온다.
집합 함수와 함께 사용된 DISTINCT
집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.
DISTINCT 처리를 위해 임시 테이블을 만들지만 실행 계획에서는 표시하지 않고 있다.
내부 임시 테이블 활용
레코드를 정렬하거나 그루핑할 때는 내부 임시 테이블을 사용한다. 여기서 '내부' 라는 단어가 포함되는 이유는 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과 다르기 때문이다. 메모리에 생성되었다가 크기가 커지면 디스크에 기록한다.
임시 테이블이 메모리를 사용할 때는 TempTable이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용한다. TempTable은 temptable_max_ram 변수로 제어할 수 있다 기본값은 1GB로 되어있다. 1GB보다 커지는 경우 디스크로 기록하게 되는데 MMAP 파일로 디스크에 기록(default), InnoDB 테이블로 기록 중 하나를 선택한다.
고급 최적화
옵티마이저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마지어 스위치로 구분 할 수 있다.
옵티마이저 스위치 옵션
| 옵티마이저 스위치 이름 | 기본 값 | 설명 | 상세 | extra |
|---|---|---|---|---|
| batched_key_access | off | BKA 조인 알고리즘을 사용할지 여부 설정 | 조인 버퍼에 버퍼링 후 한번에 요청 | |
| block_nested_loop | on | Block Nested Loop 조인 알고리즘을 사용할지 여부 설정 | Join 버퍼를 사용 | Using join buffer |
| engine_condition_pushdown | on | Engine Condition Pushdown 기능을 사용할지 여부 설정 | ||
| index_conditoin_pushdown | on | Index Condition Pushdown 기능을 사용할지 여부 설정 | Index Table에서 최대한 필터링 이후 레코드 조회 방법 | Using index condition |
| use_index_extionsions | on | Index Extension 최적화를 사용할지 여부 설정 | 세컨더리 인덱스에 자동으로 추가된 PK key 활용 여부 세컨더리 key , PK1 key, PK2 key | key_len을 통해 확인 |
| index_merge | on | Index Merge 최적화 사용할지 여부 설정 | 하나의 테이블에 2개 이상의 인덱스 적용 여부 | |
| index_merge_intersection | on | Index Merge Intersection 최적화 사용할지 여부 설정 | 각각의 인덱스를 통해 조회 후 교집합만 반환한다 | Using intersect |
| index_merge_sort_union | on | Index Merge Sort Union 최적화 사용할지 여부 설정 | or 조회인 경우 각각의 인덱스를 조회 후 정렬 후 합집합 처리 | Using sort_union |
| index_merge_union | on | Index Merge Union 최적화 사용할지 여부 설정 | or 조회인 경우 각각의 인덱스를 조회 후 합집합처리 | Using union |
| mrr | on | MRR 최적화 사용할지 여부 설정 | ||
| mrr_cost_based | on | 비용 기반의 MRR 최적화 사용할지 여부 설정 | ||
| semijoin | on | 세미 조인 최적화 사용할지 여부 설정 | 서브 쿼리 성능을 개선하기 위한 최적화 전략 | |
| firstmatch | on | FristMatch 세미 조인 최적화 사용할지 여부 설정 | 해당 하는 조건 1건만 찾으면 더 이상의 테이블 검색은 하지 않는다. | FirstMatch(e) |
| loosescan | on | LooseScan 세미 조인 최적화 사용할지 여부 설정 | 조회할 값의 유니크한 값으로 조회 | LooseScan |
| materilaization | on | Materialization 최적화 사용할지 여부 설정 | 임시테이블을 통한 조회 | <\subquery> |
| subquery_materialization_cost_based | on | 비용 기반의 Materialization 최적화 사용할지 여부 설정 |
쿼리 힌트
인덱스 힌트
옵티마이저 힌트가 도입되기 전에 사용되는 기능. SELECT or UPDATE 명령에서만 사용할 수 있다. 가능하다면 옵티마이저 힌트를 사용할 것
STRAIGHT_JOIN
여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할
USE INDEX / FORCE INDEX / IGNORE INDEX
사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시
- USE INDEX : 인덱스를 사용하도록 권장
- FORCE INDEX : USE INDEX 보다 더 강한 힌트
- IGNORE INDEX : 인덱스를 사용 못하게 사용
SQL_CALC_FOUND_ROWS
LIMIT을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행한다.
성능 향상을 위해 만들어진 힌트가 아니라 개발자의 편의를 위해 만들어진 힌트라는 것.
옵티마이저 힌트
| 힌트 이름 | 설명 | 영향 범위 |
|---|---|---|
| MAX_EXECUTION_TIME | 쿼리의 실행 시간 제한 | 글로벌 |
| RESOURCE_GROUP | 쿼리 실행의 리소스 그룹 설정 | 글로벌 |
| SET_VAR | 쿼리 실행을 위한 시스템 변수 제어 | 글로벌 |
| SUBQUERY | 서브쿼리의 세미 조인 최적화 전략 | 쿼리 블록 |
| BKA, NO_BKA | BKA 조인 사용 여부 제어 | 쿼리 블록, 글로벌 |
| DERIVED_CONDITION_PUSHDOWN NO_DERIVED_CONDITION_PUSHDOWN | 외부 쿼리의 조건을 서브쿼리로 옮기는 최적화 사용 여부 제어 | 쿼리 블록, 글로벌 |
| BNL, NO_BNL | 해시 조인 사용 여부 제어 | 쿼리블록, 글로벌 |
| JOIN_FIXED_ORDER | FROM 절에 명시된 테이블 순서대로 조인 실행 | 쿼리 블록 |
| JOIN_ORDER | 힌트에 명시도니 테이블 순서대로 조인 실행 | 쿼리 블록 |
| JOIN_PREFIX | 힌트에 명시된 테이블을 조인의 드라이빙 테이블로 조인 실행 | 쿼리 블록 |
| JOIN_SUFFIX | 힌트에 명시도니 테이블을 조인의 드리븐 테이블로 조인 실행 | 쿼리 블록 |
| QB_NAME | 쿼리 블록의 이름 설정을 위한 힌트 | 쿼리 블록 |
| SEMIJOIN, NO_SEMIJOIN | 서브쿼리의 세미 조인 최적화 전략 제어 | 쿼리 블록 |
| MERGE, NO_MERGE | FROM 절 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는 최적화를 수행할지 여부 제어 | 테이블 |
| INDEX_MERGE, NO_INDEX_MERGE | 인덱스 병합 실행 계획 사용 여부 제어 | 테이블, 쿼리 블록 |
| MRR, NO_MRR | MRR 사용 여부 제어 | 테이블, 쿼리 블록 |
| NO_ICP | ICP 최적화 전략 사용 여부 제어 | 테이블, 쿼리 블록 |
| NO_RANGE_OPTIMIZAION | 인덱스 레인지 액세스를 비활성화 | 테이블, 쿼리 블록 |
| SKIP_SCAN, NO_SKIP_SCAN | 인덱스 스킵 스캔 사용 여부 제어 | 테이블, 쿼리 블록 |
| INDEX, NO_INDEX | GROUP BY or ORDER BY or WHERE 절의 처리를 위한 인덱스 사용 여부 제어 | 인덱스 |
| GROUP_INDEX, NO_GROUP_INDEX | GROUP BY 절의 처리를 위한 인덱스 사용 여부 제어 | 인덱스 |
| JOIN_INDEX, NO_JOIN_INDEX | WHERE 절의 처리를 위한 인덱스 사용 여부 제어 | 인덱스 |
| ORDER_INDEX, NO_ORDER_INDEX | ORDER BY 절의 처리를 위한 인덱스 사용 여부 제어 | 인덱스 |
