MySQL 실행계획

용어 정리

옵티마이저

옵티마이저는 통계 정보를 기반으로 여러 방법의 비용을 계산합니다.
그 중 가장 비용이 적은 최적의 처리 방식을 최종적으로 선택합니다.

 

실행 계획

옵티마이저가 쿼리를 어떻게 처리할 것인지 계산해낸 결과입니다.
쿼리 앞에 EXPLAIN, EXMPLAIN ANALYZE 을 붙이면 확인할 수 있습니다.

explain
select * from product_info order by sale_cnt desc;

explain analyze
select * from product_info order by sale_cnt desc;

 

실행 계획을 확인하는 이유

1. 우리의 의도대로 MySQL이 동작하는지 점검한다.
2. 성능 병목이 있는 부분을 해소한다.(인덱스, 쿼리, 시스템 변수 튜닝 등)

 

Type 종류

const

어떤 테이블의 PK를 사용해서 검색하는 경우에 발생합니다.
실행 이전에 결과가 한 건 이하임을 무조건 예측할 수 있는 쿼리입니다.

 

ref

인덱스를 사용한 동등 비교를 하는 경우에 나타나게 됩니다.
PK, UNIQUE NOT NULL이 아닌 경우를 말하고, '<', '=' 처럼 부등호를 포함합니다.
Extra 컬럼에는 Using index condition 이 나타나게 됩니다.

 

Using Index condition

스토리지 엔진이 우리가 디스크에 있는 정보를 가져올 때 전부 다 가져오는 게 아니라 인덱스 조건에 해당하는 값만 필터링을 해서 가져온다는 뜻입니다.
이럴경우 디스크 I/O가 적게 일어나기 때문에 효율적으로 select 절을 처리할 수 있게 됩니다.

 

range

인덱스를 사용해서 범위 검색을 할 때 나타나게 됩니다.
ex. 구입날짜에 인덱스를 걸어 놓고 기간으로 검색을 할 경우

 

eq_ref

테이블의 PK 혹은 UNIQUE NOT NULL 컬럼과 동등 비교로 JOIN 되는 경우에 나타납니다.

1. 참조(JOIN)하는 데이터를 가져온다.
2. PK로 동등 비교를 하여 데이터를 추출한다.

이와 같은 순서로 데이터를 추출합니다.

 

⚠️ index

Index Full Scan을 의미합니다. non-clustered index의 leaf들을 순차적으로 쭉 순회하는 방식이 full scan입니다.

만약, non-clustered index에 있지 않은 정보를 추가적으로 디스크에 찾아가서 그 레코드 하나를 검색을 해서 다른 컬럼들도 참조를 해야 하는 경우에는 많은 디스크 I/O가 발생하게 됩니다. 디스크 I/O가 많은 경우 성능 병목이 크게 발생할 수 있습니다.

 

non-clustered index의 leaf들을 쭉 확인했을 때 필터링이 다 걸려서 몇 개의 디스크I/O만 발생하는 상황이거나,
커버링 인덱스를 사용해서 non-clustered index가 leaf들 만으로 조회 쿼리를 처리할 수 있는 경우에는 디스크I/O가 중간에 추가적으로 많이 발생하지 않으므로 사용해도 괜찮습니다.

Covering Index

team 테이블이 존재하고 join_date 에 인덱스를 걸었습니다.
이 상황에서 select * from team; (모든 컬럼을 보겠다.) 라는 SQL을 실행했다고 가정하겠습니다.
이 경우 type은 ALL이 됩니다. 즉, 모든 테이블을 살펴보면서, 테이블 풀 스캔이 일어나면서 조회를 하게 됩니다.

 

반면에 인덱스를 걸은 타입에 한정해서 조회를 할 경우 select join_date from team;
type이 index로 바뀌면서 Extra 컬럼에 Using index 라는 게 뜨게 됩니다.
이런 방식이 커버링 인덱스를 사용한다는 것입니다.

⚠️ all(Table Full Scan)

1. 테이블이 작아서 Key를 찾아보는게 오히려 손해인 경우(10줄 이하, 짧은 row)
2. 조건절을 보고 유의미하게 인덱스를 활용할 수 없는 경우
3. 인덱스를 활용해도 전체 레코드의 너무 많은 부분을 탑색하는 경우(20~30%)
4. 인덱스가 걸린 컬럼의 커디널리티가 너무 낮은 경우

이와 같은 경우에 테이블 풀스캔이 일어나므로 한 번 더 확인을 해야 합니다.

 

실행 계획 수립은 싼 작업이 아니다.

쿼리가 많이 복잡한 경우 쿼리를 수행할 때보다 실행 계획을 수립하는데 더 많은 리소스가 들기도 합니다.
실행 계획 수립에는 할당된 시스템 메모리가 정해져 있는데, 이 메모리를 초과해서 실행을 수립하다가 말고, 실행 계획 수립 자체를 포기하는 경우들이 생깁니다. 서비스에 맞게 인덱스를 잘 거는게 중요합니다.

 

참고자료

https://www.youtube.com/watch?v=usEsrsaSSuU&list=PLgXGHBqgT2TvpJ_p9L_yZKPifgdBOzdVH&index=4