MySQL 실행계획 분석하기

개발하는 구황작물·2024년 11월 3일
0

디프만

목록 보기
4/6

MySQL 실행계획

실행 계획은 클라이언트가 MySQL 서버에 요청한 SQL 문을 어떻게 데이터를 불러올 것인지에 관한 계획, 경로를 의미합니다.
참고로 실제 수행 순서가 아닌 MySQL 통계 정보를 기반으로 계산한 예측값입니다.

실행 계획은 SQL 문 앞에 Explain 을 붙여 확인할 수 있습니다. 

Explain 
SELECT * FROM 
following_memory_log_entity;

실행 결과 위와 같은 표를 볼 수 있는데 각각의 컬럼은 아래와 같은 의미를 가지고 있습니다.

1. id

SELECT 쿼리 별 부여되는 식별자 값

2. select_type

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시

2.1 SIMPLE

단순 SELECT 쿼리(최대 1개 존재)

2.2 PRIMARY

서브 쿼리를 사용할 경우 서브 쿼리의 외부에 있는 쿼리(첫번째 쿼리) UNION 을 사용할 경우 UNION의 첫 번째 SELECT 쿼리

SIMPLE 과 마찬가지로 최대 1개만 존재합니다.

Explain SELECT mm.member_id, 
	CASE when mm.member_id IN (SELECT 
		f.following_id 
        FROM friend_entity f 
        WHERE f.member_id = 1
	) THEN TRUE ELSE FALSE end
FROM member_entity mm
WHERE mm.member_id IN (1,2,3,4,5);

2.3 UNION

Union 쿼리에서 Primary를 제외한 나머지 Select

2.4 DEPENDENT UNION

Union 쿼리 중, 외부 쿼리에 영향을 받는 것을 의미합니다.

2.5 UNION RESULT

Union 쿼리의 결과물을 담아둔 임시 테이블을 의미합니다.

2.6 SUBQUERY

From 절 이외에 사용되는 서브쿼리를 의미합니다.

  • 중첩된 쿼리(Nested Query) : Select 되는 칼럼에서 사용
  • 서브 쿼리(Sub Query) : Where절에서 사용
  • 파생 테이블(Derived) : FROM절에 사용된 서브 쿼리. 일반적으로 RDBMS에서는 인라인 뷰 혹은 서브 셀렉트라고 합니다.

2.7 DEPENDENT SUBQUERY

서브쿼리가 바깥쪽에 생성된 Select 쿼리에 정의된 칼럼을 사용했다는 의미입니다.

explain select mm.member_id, 
 (select count(*) from friend_entity f where mm.member_id = f.member_id) fe
from member_entity mm
where mm.member_id = 2;

2.8 DERIVED

Select로 추출된 테이블, From 절에서의 서브쿼리 혹은 인라인 뷰

보통 서브쿼리 대신 조인으로 해결할 수 있다면 조인을 활용하는 것을 적극 추천한다고 합니다.

2.9 DEPENDENT DERIVED

From 절 서브쿼리에 LATERAL JOIN 이 사용되었다는 의미합니다.

MySQL 8.0 이전에는 From 절의 서브쿼리는 외부 칼럼을 사용하지 못했으나, 8.0 이후로는 래터럴 조인(LATERAL JOIN) 기능이 추가되면서 From절의 서브쿼리에서도 외부 칼럼을 참조할 수 있게 되었다고 합니다.

2.10 UNCACHEABLE SUBQUERY

서브 쿼리 사용시 Cache를 사용할 수 없어 모든 값에 대해 서브 쿼리를 재처리 하는 경우입니다.

보통 서브쿼리는 쿼리에 1개만 있다해도 실행 시 해당 서브쿼리가 1번 이상 사용될 수 있습니다.

그러나 만약 조건이 똑같은 서브쿼리가 실행될 때 이를 Cache에 담아두어 사용합니다.

그러나 서브쿼리를 사용할 때 캐시를 아예 못 쓰는 조건이 존재합니다.

  • 사용자 변수가 서브쿼리에 저장되는 경우
  • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용되는 경우
  • UUID(), RAND() 같이 결과값이 호출될 때마다 달라지는 함수가 서브쿼리에 저장되는 경우

2.11 UNCACHEABLE UNION

Union 사용 시 모든 값에 대해 UNION 쿼리를 재처리 한다는 의미입니다.

3. table

접근하고 있는 테이블에 대한 표시입니다.

MySQL 실행계획은 단위 SELECT 쿼리 기준이 아닌, 테이블 기준으로 표시가 됩니다.

4. partitions

테이블이 파티셔닝 되어 있는 경우 사용되는 필드

5. type

각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타냅니다.

type 칼럼에서 표시될 수 있는 값들은 아래와 같습니다.

  • system, const, eq_ref, ref, fulltext, ref_or_null, unique_subquery, index_subquery, range, index_merge, index, ALL

ALL(테이블 풀 스캔) 을 제외하고는 모두 인덱스를 사용하는 접근 방법입니다.

system -> ALL 로 갈 수록 성능이 나빠집니다.

  • system : 테이블 레코드가 최대 1건 존재하는 테이블을 참조하는 형태의 접근 방식

  • const : 쿼리 결과가 반드시 1건을 반환하는 쿼리의 처리방식(PK 및 Unique Key 컬럼 이용하는 조건절)

  • eq_ref : 여러 테이블이 조인되는 쿼리의 실행계획에서만 표시. 조인에서 처음 읽은 테이블의 칼럼값을, 다음 읽을 테이블의 PK나 유니크 키 칼럼의 검색 조건에서 사용할 때 eq_ref 접근 방식을 사용합니다. (조인에서 두번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용됩니다)

  • ref: 조인의 순서와 상관없이 동등 조건으로 검색될 때 사용됩니다. eq_ref, const 와 달리 검색 결과가 1개라는 보장이 없습니다. 속도는 eq_ref보단 느리지만, 빠른 레코드 조회 방법입니다.

추가적으로 쿼리 튜닝 시, const, eq_ref, ref는 성능이 괜찮으므로, 크게 신경 쓰지 않아도 된다 합니다.

  • fullText: MySQL 서버의 전문 검색 (full-Text Search) 인덱스를 사용해 레코드를 읽는 접근 방법입니다. 해당 방법을 사용하려면, 전문 검색용 인덱스가 존재해야 합니다.

  • ref_or_null: ref와 접근 방법은 비슷하나, Null 비교가 추가된 형태입니다.

  • unique_subquery: WHERE 조건절에서 사용될 수 있는 IN (subquery) 형태를 위한 접근 방법입니다.

  • index_subquery: unique_subquery와 비슷한 상황에 사용됩니다. 차이점이라면 unique_subquery는 IN절 안의 값이 중복된 값을 만들어내지 않는 상황에서 사용되고, index_subquery는 중복이 없다는게 보장되지 않을 경우 사용됩니다.

  • range: 인덱스 레인지 스캔 형태의 접근 방법입니다.

  • index_merge: 2개 이상의 인덱스를 이용하여 각각의 결과값을 병합해서 처리하는 방식입니다.

  • index: 인덱스를 풀 스캔하는 방식입니다. 만약 읽어와야 하는 데이터가 많거나 Limit 가 없는 경우, 성능이 느려집니다.

  • ALL: 풀 테이블 스캔을 뜻합니다.

6. possible_keys

옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정되었던 인덱스의 목록입니다.

7. key

possible_keys에서 선정된 인덱스 중, 최종적으로 사용한 인덱스를 의미합니다. key값은 쿼리가 우리가 의도한대로 인덱스를 활용했는지 알려주는 지표로 사용될 수 있습니다.

8. key_len

쿼리를 처리하기 위해 다중 컬럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려줍니다. 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려줍니다.

9. ref

만약 접근 방법이 ref라면(type 칼럼 값) 참조 조건으로 어떤 값이 제공되었는지 알려줍니다.

10. rows

처리 방식이 얼마나 많은 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한 값입니다. 대상 테이블에 얼마나 많은 레코드가 포함되어 있는지 또는 각 인덱스의 값이 분포도가 어떤지를 통계 정보를 기준으로 조사하여 예측합니다. 다만 예측값이라 값이 정확하지는 않습니다.

11. filtered

MySQL 엔진에 의해 필터링 되고 남은 값입니다.

아래와 같은 쿼리가 있다고 가정해보겠습니다. 

Explain 
SELECT * 
FROM employee e, salaries e
WHERE e.first_name = 'Matt' 
AND e.hire_date BETWEEN '1990-01-01' AND '1991-01-01'
AND s.emp_no = e.emp_no
AND s.from_date BETWEEN '1990-01-01' AND '1991-01-01'
AND s.salary BETWEEN 50000 AND 60000;

employee 테이블의 e.first_name = 'Matt' 와 salary 테이블의 s.salary BETWEEN 50000 AND 60000 만이 인덱스를 타고, 나머지는 인덱스를 타지 않습니다.

아래는 실행계획입니다.

idselect_typetabletypekeyrowsfiltered
1SIMPLEerefix_first_name23316.03
1SIMPLEsrefPRIMARY100.48

employee 테이블에서 인덱스에 해당되는 rows 수가 대략 233개이고, 이 중, 인덱스를 사용하지 않은 조건절을 통과한 rows의 비율이 16.03%라는 뜻입니다.

옵티마이저는 메모리 사용량을 낮추기 위해 대상 건수가 적은 테이블을 드라이빙 테이블로 선택할 가능성이 높습니다. 따라서 filtered 컬럼에 표시되는 값이 얼마나 정확히 예측되느냐에 따라 조인 성능이 달라질 수 있습니다.

12. Extras

쿼리의 실행 계획에 성능에 관련된 중요한 내용을 나타냅니다.

아래는 Extras에서 볼 수 있는 문장들입니다.

  • const rows not found
  • Deleting all rows
  • Distinct
  • FirstMatch
    ...

Extras에서 사용되는 문장은 MySQL 서버 버전이 업그레이드 될 수록 새로운 내용이 추가될 수 있습니다.

이 중 Using Filesort, Using Temporary에 대해서 설명하겠습니다.

Using Filesort : ORDER BY를 처리하기 위해 인덱스를 이용할 수 있으나 적절한 인덱스를 사용하지 못한다면 서버에서 직접 정렬을 해주어야 합니다.

조회한 레코드를 정렬용 메모리 버퍼에 복사하여 퀵 소트/힙 소트로 정렬을 합니다.

이 방법은 많은 부하를 일으키므로 이를 튜닝하는 것이 좋습니다.

Using Temporary : 정렬 시 인덱스를 사용하지 못하고 filesort로도 정렬이 되지 않으면 메모리에 가상 테이블을 직접 만든 후, 조회된 레코드를 넣어 정렬하는 방식으로 진행됩니다.

해당 방법은 filesort 보다 성능이 나쁘며, 만약 정렬해야 할 레코드의 양이 많아 메모리에서도 감당하지 못한다면 디스크 공간에 임시 테이블을 만들게 되므로 성능이 더 나빠질 수 있습니다.

profile
개발하는 구황작물
어쩌다보니 개발하게 된 구황작물
이전 포스트

Spring Batch 로 데이터 일괄 처리하기

다음 포스트

MySQL 실행계획 분석 및 Using Filesort 제거하기

0개의 댓글