쿼리 실행계획 (Query Plan)이란?

이슈

최근 회사에서 애플리케이션 기능개선 작업을 진행하던 중, 신규 테이블을 1개 생성해야하는 상황이 생기게 되었다. 

그리고 기존 SQL들에서 해당 테이블을 사용하게끔 대거 수정하였다.

 

헌데 해당 테이블을 생성할 때 컬럼으로 INDEX를 덜 지정해둔 것 같아, 이로 인해 쿼리 성능이 낮아졌는지 체크하는 게 좋을 것 같아

쿼리 실행계획을 파악해보기로 하였다

 

INDEX는 추가적인 쓰기 작업과 저장 공간을 활용하여 DB 테이블 검색 속도를 향상시키기 위한 자료구조이니, 그냥 모든 컬럼 싹 다 INDEX 걸면 안되냐?! 

 

라고 하면 필자도 사실 혹하긴하나, 아래와 같은 이슈가 있으니 배제하고 필요한 것만 INDEX를 걸기 위해 현재 쿼리 실행계획을 파악하고자 한다. 

 

* INDEX를 다 걸면 곤란한 이유

• 인덱스를 추가하게되면 될수록 인덱스를 관리하기 위한 저장공간 용량이 낭비됨
• 해당 인덱스가 수정/삭제되는 작업이 발생할 경우, 작업 지연시간이 생김
• DBMS는 인덱스를 항상 최신의 정렬 상태로 유지해아 원하는 값을 빠르게 검색할 수 있으므로, 인덱스가 적용된 컬럼에 INSERT/UPDATE/DELETE가 수행되면 아래와 같은 연산을 추가적으로 해줘야 함.
  • INSERT: 새로운 데이터에 대한 인덱스를 추가함
  • DELETE: 삭제하는 데이터의 인덱스를 사용하지 않는다는 작업을 진행함
  • UPDATE: 기존의 인덱스를 사용하지 않음 처리하고, 갱신된 데이터에 대해 인덱스를 추가함

위 이유로 인해 CREATE/ DELETE/UPDATE가 빈번한 속성에 인덱스를 걸게되면 인덱스의 크기가 비대해져, 실제 데이터는 10만건이지만 인덱스는 훨씬 많이 존재하게 되어 SQL문 처리 시 비대해진 인덱스에 의해 오히려 성능이 떨어질 수 있음

 

쿼리 실행계획 (Query Plan)이란?

- 데이터베이스 관리시스템 (DBMS)가 쿼리를 어떻게 처리할 것인지를 결정하는 일련의 단계를 의미

- 위 계획은 데이터베이스 옵티마이저에 의해 생성되며, 쿼리를 가장 효율적으로 실행하기 위해 선택된 작업의 순서와 방법을 제시함

- 이를 보고 개발자는 옵티마이저가 만들어 실행한 실행계획 결과를 조회하고 분석하며 불합리한 내용이 존재하면 이를 더 개선시켜 최적화된 실행계획을 만들도록 유도해야 함

 

실행계획을 보기 위해 해야하는 것들

필자는 새로 추가된 컬럼을 사용하는 쿼리문들을 편하게 가져오고 싶어, general_log를 활성화시켰다.

활성화 방법은 아래와 같다. 

 

* General Log란?

MySQL서버에 대한 모든 쿼리 실행과 관련된 정보를 기록함. (시간정보 포함)

SELECT , INSERT, UPDATE, DELETE 등 모든 유형의 쿼리를 포함함

 

1. 우선 general_log가 활성화 되어있는지 확인  

SHOW VARIABLES LIKE '%general_log%';

결과는 2줄이 나온다

general_log : OFF

general_log_file  : 파일 경로

 

2. OFF되어 있으니, ON 시키기 위해 아래 쿼리문 실행
다시 보면 이제 ON으로 바뀌게 되며, 5366a9921af0.log 파일에 관련 정보가 박히게 된다

SET global general_log='ON';

 

이제 위에서 만들었던 신규 테이블을 사용하는 view단으로 이동하게 되면 알아서 general_log가 찍히게 될 것이다. 

general_log는 아래와 같은 방식으로 찍힌다. (전체 다 보여주기에는 좀 그러니, 짤린 이미지로 대체)

 

필자는 여기서 신규 테이블을 사용하는 쿼리만 뽑아내서 쿼리 실행계획을 볼 것이다. 

 

쿼리 실행계획을 보는 방법은 쉽다. 아래와 같이 실행하고자 하는 SQL의 앞에 EXPLAIN을 붙이고 실행하면 된다. 

EXPLAIN SELECT * FROM table1;

 

그럼 아래와 같은 예시로 결과가 뜬다

 

이제부터 위 표를 보는 법에 대해서 알면된다. 

아래에 정리해 두도록 하겠다. 

 

우선 필자는 신규 생성한 테이블에 추가로 생성할 INDEX는 필요 없는 것으로 판명되었다.

 

쿼리 실행계획 보는법

우선 EXPLAIN 실행 시 나오는 컬럼들에 대한 설명은 아래와 같다 

튜닝 대상을 찾을 때엔 

select_type, type, extra 컬럼 위주로 보는 것이 파악하기에 더 용이하다.

실행 계획	설명
id	SQL이 수행되는 쿼리 별 부여되는 식별값
select_type	각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼
table	접근하고 있는 테이블에 대한 표시
partitions	테이블에 파티셔닝이 되어 있는 경우 사용되는 필드
type	각 테이블의 레코드를 어떻게 읽었는지에 대한 접근 방식
possible_keys	“사용될 법했던 인덱스의 목록”이며 반드시 사용된 것은 아님
key	최종 선택된 실행 계획에서 사용되는 인덱스
key_len	선택된 인덱스의 길이를 의미
ref	접근 방법이 ref면 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 표시
rows	실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다. (정확하지는 않음)
filtered	필터링되고 남은 레코드의 비율 (정확하지는 않음)
Extra	쿼리 실행 계획에서 성능에 관련된 중요한 내용이 Extra 칼럼에 자주 표시됨 내부적인 처리 알고리즘에 대해 조금 더 깊이있는 내용을 보여주는 경우가 많음 옵티마이저가 어떻게 동작하는지에 대해 알려주는 힌트 값

 

 

ㅁ Select_type

구분	설명
SIMPLE	Union이나 Sub Query가 없는 단순 Select
PRIMARY	Sub Query에서 가장 바깥쪽(Outer) Select
DERIVED	단위 SELECT 쿼리의 실행 결과로 생성된 임시테이블
SUBQUERY	select와 from 절 사이에서 독립적 수행 쿼리 select (select from) from
UNION	Union 쿼리의 Select 중 두번째, 첫번째는 결과를 담는 임시 테이블(DERIVED)
UNION RESULT	UNION RESULT는 UNION 결과를 담아두는 테이블
MATERIALIZED	서브쿼리의 내용을 임시테이블로 구체화(Materialization)한 상태
DEPENDENT SUBQUERY	SUBQUERY에서 외부와 where 조건이 연결되어 있는 쿼리
DEPENDENT DERIVED	MySQL8.0 이후 LATERAL JOIN이 지원되면서 서브쿼리에서 외부컬럼을 참조 가능.
DEPENDENT UNION	UNION 또는 UNION ALL 쿼리에서 두번째 단위 쿼리
UNCACHEABLE UNION	캐시를 사용하지 못하는 UNION
UNCACHEABLE SUBQUERY	캐시를 사용하지 못하는 SUBQUERY(사용자 변수 사용 시, UUID RAND 등 결과값이 달라지는 경우)

 

ㅁ TYPE

- 위에서 아래로 갈수록 쿼리 성능이 안 좋은 것임을 의미한다

- 테이블의 데이터를 어떻게 찾을지에 관한 정보를 제공함 (실행한 쿼리가 적절하게 인덱스를 참조했는지를 알 수 있는 핵심컬럼)

- 테이블 전체 스캔을 지양하고, 인덱스를 통한 커버링 인덱스를 지향하도록 튜닝이 필요하다.

- 주요 튜닝포인트는 검색하는 데이터의 수를 줄이는 방법에 있다

실행 계획	설명
system	테이블에 단 한개의 데이터만 있는 경우
const	SELECT에서 Primary Key 혹은 Unique Key를 기준으로 조회하는 경우로 많아야 한 건의 데이터만 있음
eq_ref	조인을 할 때 Primary Key로 검색되는 경우
ref	조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우
ref_or_null	ref 와 같지만 null 이 추가되어 검색되는 경우
index_merge	두 개의 인덱스가 병합되어 검색이 이루어지는 경우
unique_subquery	다음과 같이 IN 절 안의 서브쿼리에서 Primary Key가 오는 특수한 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT Primary Key FROM tab01);
index_subquery	unique_subquery와 비슷하나 Primary Key가 아닌 인덱스인 경우 SELECT * FROM tab01 WHERE col01 IN (SELECT key01 FROM tab02);
range	특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우로, 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않음
index	인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 일반적으로 인덱스 풀스캔이라고 함
all	테이블을 처음부터 끝까지 검색하는 경우로, 일반적으로 테이블 풀스캔이라고 함 앞선 방식으로 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법

 

 

ㅁ EXTRA

- SQL문을 어떻게 수행할 지 가장 구체적인 정보를 제공해 줌

구분	설명
using index	물리적 데이터를 읽지 않고 인덱스를 이용해서 데이터를 추출 하는 경우  인덱스만으로 처리되는 방법을 "커버링 인덱스(Covering Index)"라고 함
using where	where 조건으로 데이터를 추출. type이 ALL 혹은 Indx 타입과 함께 표현되면 성능이 좋지 않다는 의미
distinct	조건을 만족하는 레코드를 찾았을 때 같은 조건을 만족하는 또 다른 레코드가 있는지 검사하지 않음
not exist	left join 조건을 만족하는 하나의 레코드를 찾았을 때 다른 레코드의 조합은 더 이상 검사하지 않는다
range checked for each record	최적의 인덱스가 없는 차선의 인덱스를 사용한다는 의미
using filesort	이미 정렬된 인덱스를 사용하지 못할 경우, 추가적인 데이터 정렬을 위해 메모리 혹은 디스크에서 정렬하는 상황임. 속도개선을 위해서는 인덱스 추가 대상이됨.
using temporary	데이터의 중간 결과를 임시 테이블로 생성 보통 DISTINCT, GROUP BY, ORDER BY 구문이 포함된 경우임.

 

이외의 것들은 간단히 정리해두려고 한다!

 

Table 컬럼

실행계획을 조회할 때 쿼리별로 조회하는 것이 아닌 테이블 단위로 분류해서 표시된다.

조회된 테이블에 대한 정보를 나타내며 <>로 감싸져 있는 table의 경우엔 임시테이블을 사용한다.

 

partitions 컬럼

파티셔닝(partitioning)하여 테이블을 관리한다고 가정할 때, 어떤 파티션을 읽었는지를 알려주는 정보다.

5.7 이전에는 EXPLAIN PARTITION 명령을 해야 보였으나 8.0 부터는 EXPLAIN 명령만해도 보이게 되었다.

 

possible_keys 컬럼

옵티마이저가 쿼리를 처리하기 위해 여러가지 방법을 모색하던 중 찾은 후보 인덱스 목록

 

key 컬럼

possible_keys의 후보중에 실제로 사용된 인덱스를 의미 인덱스를 타지 못한경우는 null로 표기

 

key_len 컬럼

실무에선 인덱스를 단일 컬럼으로 만들기 보단 다중 컬럼으로 만들어지는 경우가 더 많은데

이 때 다중 컬럼 인덱스 중에서 몇 바이트를 사용했는지 표시해준다.

각 인덱스 컬럼에 할당된 바이트를 알 수 있으므로 몇 개의 인덱스 컬럼이 사용했는지를 추측할 수 있다.

 

아래 쿼리는 w_work(work_no , p_work_no, member_no 등 key가 있는 ) 테이블을 work_no로 작업을 조회하는 쿼리이다.

select * from w_work where work_no = 2000;

key_len은 8(bigint = 8byte)이 나온다. work_no컬럼(bigint)

 

 

ref 컬럼

type 컬럼에서 접근 방법이 ref일때 표시되는데 어떤 컬럼이 조건에 사용되었는지를 보여준다.

"func"라고 표시될 때도 있는데 이는 단순 컬럼이 아닌 어떤 가공된 컬럼이 사용될 때 표시된다.

 

row 컬럼

옵티마이저가 비용을 산정하기 위해 얼마나 많은 레코드를 읽고 비교 했는지를 예측해본 레코드의 수

실제 레코드 수와 일치하지 않은 경우가 많으며 대략적인 예측이다.

 

 

 

 

참고자료

https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_ref

https://peterica.tistory.com/417
https://1-7171771.tistory.com/152