실행계획

실행계획이란

사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업 절차이다.
(옵티마이저: SQL을 가장 빠르고 효율적으로 수행할 최적(최저비용)의 처리 경로를 생성해주는 DBMS 내부의 핵심엔진)

MySQL 쿼리 실행계획 보는 방법

MySQL에서는 실행할 쿼리문 앞에 EXPLAN 키워드를 이용해 실행계획을 볼 수 있는데 쿼리 성능과 아주 밀접한 항목인 type(type은 접근 방식을 표시하는 필드), extra 정보, 드라이빙 테이블, select_type(select문 유형)이 DERIVED 인 것이 있는지 확인한다.

-드라이빙 테이블: 조인 시 먼저 액세스되는 테이블
-드리븐 테이블: 조인 시 나중에 액세스되는 테이블

1) type에서는 ALL, index 두 가지는 테이블 또는 특정 인덱스가 전체 행에 접근하기 때문에 테이블 크기가 크면 효율이 떨어진다. ALL인 경우 조인에서 참조되는 컬럼이 인덱스 되지 않은 컬럼인지 다시 한 번 확인해보자.

2) 드라이빙 테이블 - 작업이 되는 행의 수가 적은 테이블부터 액세스되어야 전체 탐색이 줄어든다.

3) select_type DERIVED - 조인으로 해결할 수 있으면 서브쿼리보다는 조인을 사용하자.

4) extra는 mysql이 쿼리를 어떻게 해석했는지에 대한 추가 정보가 있다.

쿼리 작성 시 쿼리 성능을 높이기 위한 커버링 인덱스 활용

대용량 데이터 처리 시 커버링 인덱스를 활용해 쿼리를 작성하면 쿼리의 성능을 올릴 수 있다.
커버링 인덱스는 원하는 데이터를 인덱스에서만 추출할 수 있는 인덱스를 의미한다. 인덱스는 행 전체 크기보다 훨씬 작고 인덱스 값에 따라 정렬이 되어 있기 때문에 순차적으로 읽을 수 있다.

• 활용 예제 쿼리1

select *
from order_test
where created_at like '2021%'
limit 100000, 100

일반 쿼리이며, 실행계획을 보면 Extra: Using where로 되어 있다.
Where에서 like 검색으로 처리된 결과 셋을 limit으로 범위를 축소하고, 추출된 값을
데이터 페이지에서 원하는 값을 가져오기 때문에 수행 속도가 느리다.

select a.*
from (
      select order_no
      from order_test
      where created_at like '2021%'
      limit 100000, 100
) b join order_test a on b.order_no = a.order_no

커버링 인덱스를 활용한 쿼리이다. 실행계획을 보면 Extra: Using where; Using index로 되어 있다. 
Where, limit은 일반 쿼리와 같지만, 테이블의 PK값인 order_no로 인덱스 접근을 통해 서브 쿼리에 있는 결과 수인 100개만 데이터 블록에 접근하게 되므로 좋은 성능이 나온다.

• 활용 예제 쿼리2

select *
from order_test
order by created_at
limit 500000, 100

인덱스가 있음에도 실행계획을 돌려보면 Extra: Using filesort가 발생된다. full scan과 file soring이 일어난다. 레코드 대상 건수가 많아질수록 쿼리 응답 속도가 느려진다.

select a.*
from (
      select order_no
      from order_test
      order by created_at
      limit 500000, 100
) b join order_test a on b.order_no = a.order_no

실행계획을 보면 Extra: Using index로 나온다. file sorting이 일어나지 않는다.

*참고 자료
https://ibks-platform.tistory.com/374
https://juntcom.tistory.com/16
https://gywn.net/2012/04/mysql-covering-index/
https://devuna.tistory.com/36
https://enterone.tistory.com/232