1. 쿼리 계획이란?
- 개념: 데이터베이스가 SQL 쿼리문을 최적화하여 실행하는 방식을 설명한 것
- 중요성: 성능에 직접적으로 영향을 미친다. 데이터베이스가 쿼리를 효율적으로 처리하기 위해 어떤 선택을 하는지 사용자가 확인하고 개선할 수 있는 방향을 제시하기 때문에 운영 환경에 적용하기 전에 꼭 확인하면 좋다.
2. 쿼리 계획을 확인하는 방법
- EXPLAIN 명령어: DBMS에서 쿼리 실행 계획을 확인하는 명령어이다.
- 예제: EXPLAIN <쿼리 질의문>
- 내가 작성한 쿼리문 앞에 EXPLAIN 명령어만 붙여주면 되니 편리하게 확인이 가능하다.
3. 구성 요소
- id: 쿼리의 단계별 실행 순서
- select_type: 쿼리의 유형(SIMPLE, PRIMARY, UNION 등)
- table: 접근하는 테이블 이름
- type: 테이블 접근 방식(all, index, range 등)
- possible_keys: 쿼리에 사용 가능한 인덱스
- key: 실제로 선택된 인덱스
- rows: 예상되는 읽어야 할 행 수
- Extra: 추가 정도
4. type 종류와 최적화
- system: 테이블에 하나의 행만 존재하는 경우
- const: 기본 키 또는 고유 인덱스를 사용해서 하나의 행만 반환하는 경우
- eq_ref: 다른 테이블과의 조인 관계에서 사용되는 방식으로, 기본 키 또는 고유 인덱스를 사용하여 하나의 행만 반환하는 경우
- ref: 인덱스를 사용하여 다수의 행을 검색하는 경우
- fulltext: 텍스트 검색을 하는 경우
- ref_or_null: ref 타입과 유사하지만, 조건에 NULL 값이 포함될 수 있는 경우
- index_merge: 다수의 인덱스를 활용하여 여러 조건을 충족하는 인덱스 결과가 나타나는 경우
- range: 인덱스를 활용하여 특정 범위의 값을 검색하는 경우
- index: 인덱스를 사용하여 모든 행을 검색하는 경우(테이블 풀 스캔을 하기 때문에 ALL 타입과 유사함)
- ALL: 테이블의 모든 행을 스캔하는 경우(인덱스를 사용하지 않으며 매우 비효율적인 방식)
몇몇 타입만 예시로 해보자
create table movie_ranker
(
movie_cd varchar(255) not null,
reference_date int not null,
audi_acc bigint not null,
audi_change float not null,
audi_cnt bigint not null,
audi_inten bigint not null,
movie_nm varchar(255) null,
open_dt int not null,
`rank` int not null,
rank_inten int not null,
rank_old_and_new varchar(255) null,
sales_acc bigint not null,
sales_amt bigint not null,
sales_change float not null,
sales_inten bigint not null,
sales_share float not null,
scrn_cnt int not null,
show_cnt int not null,
primary key (movie_cd, reference_date)
);
예제로 쓰일 테이블은 movie_cd, reference_date 칼럼이 복합키로 작용하는 테이블이다.
보이는 것처럼 단순하게 테이블을 조회하는 경우 모든 행을 스캔하기 때문에 타입에서 ALL 풀 스캔으로 수행되는 것을 확인할 수 있다.
그러면 복합키 두 개를 모두 조건에 추가하여 const 타입으로 조회되는지 확인해 보자.
두 조건을 모두 추가했으나 ALL 풀 스캔으로 수행되는 것을 볼 수 있다. 왜 그럴까?
이런 결과가 나타나는 것은 테이블 데이터 타입을 맞춰주지 않았기 때문이다.
위에 movie_cd 타입은 varchar 타입인데 수행하려는 쿼리문에서는 int 타입으로 수행하려고 했다.
잘못된 타입으로 질의문을 작성하게 되면 데이터베이스에서는 인덱스가 제대로 사용되지 못하고 숫자와 문자열을 형변환하면서 모든 테이블을 스캔하는 방식으로 처리하게 되기 때문이다.
다시 타입을 올바르게 바꾸어 조회해 보니 const 타입으로 나타나는 것을 확인할 수 있다.
데이터베이스에서는 varchar 타입과 int 타입을 자동 타입 변환을 통해서 사용자가 굳이 타입을 맞춰주지 않아도 쿼리문이 수행되도록 도와주는 기능을 갖고 있다.
단순해 보이지만 이 편리한 기능 때문에 많은 사람들이 타입을 맞춰주지 않아 쿼리문의 성능이 떨어지는 경우가 많다.
그리고 하드웨어의 성능이 좋아짐에 따라서 쿼리문 개선을 놓치는 경우가 많은데, 규모가 커지고 반복되는 작업이 많을 때는 이슈가 될 수 있기 때문에 기본적인 쿼리 튜닝은 알아두는 것이 좋다.
- ref 추가 -
