해당 글은 제가 직접 DB 최적화 작업을 진행하면서 직접 경험한 내용을 바탕으로 공부하고 알게된 지식을 간단하게 정리하는 글입니다.
통계 정보에 대해서는 이 글을 확인해주세요.
MySQL의 Optimizer
MySQL 옵티마이저는 DB의 성능을 결정짓는 중요한 요소 중 하나로, 사용자가 요청한 SQL 명령을 가장 효율적으로 실행하기 위한 최적의 방법을 찾기 위해 다양한 실행 계획을 세우고 평가합니다.
최적의 실행 계획이 수립되면 쿼리 실행 시간을 단축시키고 시스템 리소스를 절약하는 데 크게 기여합니다.
옵티마이저는 최적의 실행 계획을 평가하기 위해 규칙 기반과 비용 기반 최적화 방식을 사용합니다. 다만 규칙 기반의 최적화는 요즘에는 잘 사용되지 않는 방식이라 해당 글에서 다루지 않습니다.
참고!
MySQL 옵티마이저는 모든 상황을 계획하지 않습니다.그 이유는 실행 계획을 세우는 시간이 길어질 수록 SQL 쿼리 수행 시간에 영향을 끼치기 때문입니다.
MySQL 옵티마이저가 SQL 명령문을 잘 이해하고 최적의 실행 계획을 세울 수 있게 ✨SQL 명령문을 효율적으로 작성하는 것✨이 중요합니다.
Optimizer 동작 제어
다양한 방법을 통해 MySQL 옵티마이저의 동작 방식을 제어할 수 있습니다. 동작 방식 중 자주 사용되는 동작 제어 방식 중 하나인 INDEX 힌트에 대해서
자세한 내용은 문서를 통해 확인할 수 있습니다.
Index 힌트
MySQL 옵티마이저에게 사용하거나 제외할 인덱스 목록을 전달할 수 있습니다.
tbl_name [[AS] alias] [index_hint_list]
index_hint_list:
index_hint [index_hint] ...
index_hint:
USE {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
| {IGNORE|FORCE} {INDEX|KEY}
[FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
index_list:
index_name [, index_name] ...USE INDEX
명명된 인덱스 중 하나를 사용하도록 '제안'합니다.
옵티마이저가 명명된 인덱스가 아닌 다른 인덱스가 더 효율적이라고 판단을 하면, 다른 인덱스를 사용하는 방법으로 실행 계획을 수립합니다.
FORCE INDEX
명명된 인덱스 중 하나를 사용하도록 '강제'합니다.
옵티마이저가 명명된 인덱스가 아닌 다른 인덱스가 더 효율적이라 판단을 하더라도 명명된 인덱스 중 하나를 사용하여 실행 계획을 수립합니다.
IGNORE INDEX
명명된 인덱스를 사용하지 않도록 '강제'합니다.
비용 기반 최적화
비용 기반 최적화는 각 실행 계획에 대해 예상 비용을 계산하고, 비용이 적게드는 실행 계획을 선택하는 방식입니다.
옵티마이저는 예상 비용을 계산할 때 통계 정보를 활용하여 실행 계획을 평가합니다.
참고!
통계 정보가 정확하지 않으면 옵티마이저는 실행 계획을 효율적으로 평가할 수 없습니다.예) 테이블에 100,000건의 데이터가 존재하는데 통계 정보는 갱신되지 않아 10건의 데이터만 있다고 한다면, MySQL 옵티마이저는
Full Scan을 이용하는 방향으로 실행 계획을 세울 수 있습니다.
코스트 모델(Cost Model)
MySQL 옵티마이저가 실행 계획에 대해 예상 비용을 계산할 때 필요한 단위 작업들의 비용을 의미합니다.
MySQL 8.0 기준으로 코스트 모델에서 지원하는 단위 작업은 아래와 같습니다.
engine_cost
레코드를 가진 데이터 페이지를 가져오는데 필요한 비용을 관리합니다.
| cost | 항목 | 설명 |
|---|---|---|
| engine_cost | io_block_read_cost | 디스크 데이터 페이지 읽기 |
| engine_cost | memory_block_read_cost | 메모리 데이터 페이지 읽기 |
server_cost
인덱스를 찾고, 레코드를 비교하고, 임시 테이블 처리에 대한 비용을 관리합니다.
| cost | 항목 | 설명 |
|---|---|---|
| server_cost | disk_temptable_create_cos | 디스크 임시 테이블 생성 |
| server_cost | disk_temptable_row_cost | 디스크 임시 테이블의 레코드 읽기 |
| server_cost | key_compare_cost | 인덱스 키 비교 |
| server_cost | memory_temptable_crate_cost | 메모리 임시 테이블 생성 |
| server_cost | memory_temptable_row_cost | 메모리 임시 테이블의 레코드 읽기 |
| server_cost | row_evaluate_cost | 레코드 비교 |
참고 문서
- Real MySQL 8.0 1권: 개발자와 DBA를 위한 MySQL 실전 가이드
- https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-optimizer-statistics.html
- https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
- https://dev.mysql.com/doc/refman/8.0/en/switchable-optimizations.html
- https://dev.mysql.com/blog-archive/optimization-to-skip-index-dives-with-force-index/
