DB

MySQL 퍼포먼스 최적화를 읽고(2) - 쿼리 성능 진단은 최적화의 기초

Just a devlog 2023. 10. 24. 14:12

쿼리 실행 계획

 

DB에 SQL을 요청하면 DB 내부적으로 SQL 파싱, 옵티마이징 과정을 거친 후 데이터를 찾기 시작한다.

 

쿼리 실행 계획이란, DB가 데이터를 찾아가는 일련의 과정을 DB ResultSet으로 보여주는 것이다.

쿼리 실행 계획을 보고 기존 쿼리를 튜닝하거나 성능 분석, 인덱스 전략 수립 등 성능 최적화에 대한 전반적인 업무를 처리할 수 있다.

 

쿼리 실행 계획을 확인할 수 있는 방법은 실행할 쿼리 앞에 EXPLAIN을 붙여주면 된다.

 

실행 계획 순서

동일한 아이디끼리는 위에서 아래 방향으로 읽는다.

 

위에서 아래로 읽는 도중 Table 항목에 <derived번호> 항목을 만나면, 번호에 해당하는 ID로 가서 첫 번째 단계를 수행한 다음 다시 돌아와서 나머지 실행 계획을 읽는다.

 

실행 계획 항목

쿼리 실행 계획의 각 항목은 위와 같이 정리할 수 있다.

 

위 항목 중 Select_type, Type, Extra에 대해서 더 자세히 알아보자.

 

Select_Type

SIMPLE

  • UNION 이나 서브쿼리가 없는 단순 SELECT 

PRIMARY

SELECT * FROM (SELECT * FROM tab01) sub
  • 서브쿼리가 있을 때 가장 바깥쪽에 있는 SELECT

DERIVED

SELECT * FROM (SELECT * FROM tab01) sub
  • FROM 절 안의 서브쿼리

DEPENDENT SUBQUERY

SELECT *
FROM tab01 t1
WHERE EXISTS (SELECT 1 FROM tab02 t2 WHERE t2.t1_id = t1.id)
  • 외부쿼리와 상호 연관된 서브쿼리

 

Type

조회 혹은 조인 시 어떤 방식으로 데이터를 가져오는지에 대한 내용이다.

 

위 그림에서 초록색이나 파란색 상태라면 쿼리가 나쁘지 않은 상태라고 볼 수 있다.

 

system

  • 테이블에 단 한개의 데이터만 있는 경우

const

  • SELECT에서 Primary Key 혹은 Unique Key를 상수로 조회하는 경우로 많아야 한 건의 데이터만 있음

eq_ref

  • 조인을 할 때 Primary Key 혹은 Unique Key로 매칭하는 경우

ref

  • 조인을 할 때 Primary Key 혹은 Unique Key가 아닌 Key로 매칭하는 경우

ref_or_null

  • ref와 같지만 NULL데이터를 포함하여 검색되는 경우

index_merge

  • 두 개의 인덱스가 병합되어 검색이 이루어지는 경우

unique_subquery

  • IN절 안의 서브쿼리 결과 값이 Primary Key인 경우

index_subquery

  • IN절 안의 서브쿼리 결과 값이 인덱스인 경우

range

  • 특정 범위 내에서 인덱스를 사용하여 원하는 데이터를 추출하는 경우
  • 데이터가 방대하지 않다면 단순 SELECT 에서는 나쁘지 않다

index

  • 인덱스를 처음부터 끝까지 찾아서 검색하는 경우로, 인덱스 풀 스캔이라고 한다.

all

  • 테이블을 처음부터 끝까지 검색하는 경우로, 테이블 풀 스캔이라고 한다.

 

Extra

쿼리 실행에 대한 추가적인 정보를 보여준다.

 

아래 항목 중 성능과 직접적인 관련이 있는 네가지 항목을 살펴보자.

 

Using Index

  • 인덱스 자료구조만을 이용해서 데이터를 추출
  • 인덱스 자체가 결과값이 되기 때문에 데이터를 찾기 위해 디스크에 접근하지 않아도 됨
  • 커버링 인덱스라고도 함

Using Where

  • Where 조건으로 데이터를 추출
  • Type이 All 혹은 Index와 함께 표현되면 해당 쿼리 성능이 좋지 않다는 의미

Using Filesort

  • 데이터 정렬이 필요한 경우에 나타남
  • 메모리 혹은 디스크 상에서 정렬됨
  • 결과 데이터가 많은 경우 성능에 직접적인 영향을 미침

Using Temporary

  • 쿼리 처리 시 내부적으로 임시 테이블 (Temporary Table) 을 생성하여 사용하는 경우를 의미

일반적으로 데이터가 많은 경우 Using Filesort, Using Temporary 상태를 좋지 않으며 반드시 쿼리 튜닝이 필요하다.

 

쿼리 프로파일링

쿼리를 처리할 때 DB 내부적으로 Open Table/Close Table, Optimizing, Sending Data 등을 비롯해 여러 단계를 거치며 최종적으로 데이터를 찾아낸다.

 

쿼리 실행 계획이 데이터를 찾아가는 일련의 과정이라면, 쿼리 프로파일링은 실제 쿼리 실행 시 병목이 되는 부분을 찾아낼 수 있는 방법이다.

 

SET PROFILING = 1;
SELECT * FROM tab;

SHOW PROFILE;

위와 같이 프로파일링 세션 변수를 활성화한 후 쿼리를 실행하면 가장 최근에 실행한 쿼리에 대해 프로파일링한 정보를 확인할 수 있다.

 

 

SHOW PROFILES;

(프로파일링 세션 변수 활성화된 상태에서) 실행된 쿼리 리스트를 확인할 수 있다.

 

 

SHOW PROFILE FOR QUERY 2;

프로파일링한 쿼리 리스트 중 특정 쿼리에 대한 프로파일링 정보를 확인할 수 있다.

 

이처럼 프로파일링 결과를 통해 어느 단계에서 병목이 있는 지를 대략적으로 파악하여 쿼리 튜닝에 참고할 수 있다.