EXPLAIN 사용법 찍먹 해보기

MySql의 옵티마이저가 항상 최적의 쿼리 최적화를 하는 것은 아니기 때문에 EXPLAIN을 사용해 실행 계획을 확인하는 것은 중요하다. 부끄럽게도 나는 DB를 잘 사용하고 다루는 것에 많이 부족하다.

최근 Real Mysql 8.0이라는 좋은 책을 읽었지만 내용도 많고 나에게는 어려운 부분이 많지만 그 중에서도 특히 EXAPLIN 활용에 대한 부분은 요약해서 기록하는게 필요 할 것 같다는 생각에 포스트로 작성하게 되었다.

EXAPLIN 이란?

SQL 데이터베이스에서 EXPLAIN은 쿼리의 실행 계획(execution plan)을 보여주는 명령어로, 개발자와 DBA가 쿼리의 성능을 분석하고 최적화하는 데 사용된다. 이 명령어는 쿼리를 실제로 실행하지 않고, 데이터베이스가 어떻게 쿼리를 처리할지 계획을 보여준다.

• 실행 계획: EXPLAIN은 테이블 접근 방식, 인덱스 사용 여부, 조인 순서, 예상 행 수 등 쿼리 실행에 대한 세부 정보를 제공
• 성능 최적화: 예를 들어, 쿼리가 전체 테이블 스캔(full table scan)을 할 경우 성능이 저하될 수 있으므로, 인덱스를 추가하거나 쿼리를 재작성할 수 있다.

주요 데이터베이스 지원

대부분의 SQL 데이터베이스는 EXPLAIN 또는 유사한 명령어를 지원한다

• MySQL: "EXPLAIN SELECT ..." 명령어를 사용하며, 실행 계획을 JSON 또는 트리 형식으로 보여준다 MySQL EXPLAIN.
• PostgreSQL: "EXPLAIN" 명령어를 사용하며, 실행 계획을 텍스트 또는 JSON 형식으로 제공한다 PostgreSQL EXPLAIN.
• Oracle: "EXPLAIN PLAN FOR" 명령어를 사용하며, 실행 계획을 PLAN_TABLE에 저장해 분석한다 Oracle EXPLAIN PLAN.

---

EXPLAIN 명령어를 사용하면 MySQL이 쿼리를 실행하는 방법에 대한 실행 계획을 확인할 수 있다. 하지만 많은 정보가 표시되기 때문에 어떤 부분을 확인하고 어떻게 튜닝해야 하는지 보기 어려운데 EXPLAIN에서 나오는 중요한 컬럼과 그 컬럼을 통해 확인할 수 있는 주요 사항들을 간단하게 알아보자.

EXPLAIN 결과에서 중요한 컬럼들

1. type (접근 방식)
   • 설명: 테이블에 어떻게 접근하는지 나타낸다. 이 값이 쿼리 성능의 중요한 지표 중 하나이다.
   • 주요 값 (최적의 순서로):
     • const/system: 최적의 접근 방식. 단 한 행만 읽는 경우.
     • eq_ref: 고유 인덱스를 사용하여 정확히 하나의 행을 가져오는 경우. 효율적이다.
     • ref: 인덱스를 사용해 여러 행을 가져오는 경우.
     • range: 인덱스를 사용하여 특정 범위를 읽는 경우.
     • ALL: 풀 테이블 스캔으로, 테이블의 모든 행을 읽음, 피해야 하는 경우이다.
   • 튜닝 방법: ALL로 표시되면 전체 테이블을 스캔하는 것이므로, 쿼리 조건에 적절한 인덱스를 추가하거나 조건문을 최적화, 더 효율적인 접근 방식을 사용하게 해야 된다.
2. possible_keys와 key (인덱스 사용 여부)
   • possible_keys: 쿼리에서 사용할 수 있는 인덱스 목록을 보여준다.
   • key: 실제로 쿼리에서 사용된 인덱스를 보여준다.
   • 확인할 사항:
     • possible_keys에 인덱스가 있지만 key가 <null>로 표시된다면, 인덱스가 사용되지 않는 상황.
     • 이 경우, 인덱스를 제대로 사용하도록 인덱스 추가 또는 쿼리 구조 변경을 고려해야 한다.
3. rows (예상 읽기 행 수)
   • 설명: MySQL 옵티마이저가 예상하는 읽어야 할 행의 수 이다.
   • 확인할 사항:
     • rows 값이 클수록 쿼리 실행 비용이 높아진다. 이 값을 줄이기 위해 적절한 필터 조건과 인덱스 사용을 고려해야 한다.
   • 튜닝 방법: 행 수를 줄이기 위해 인덱스를 추가하거나, 불필요한 행이 읽히지 않도록 쿼리 조건을 수정 한다.
4. Extra (추가 정보)
   • 설명: 쿼리 실행에 대한 추가 정보를 제공하는데 특히 성능에 영향을 미치는 요소들을 확인할 수 있다.
   • 주요 값:
     • Using where: WHERE 조건을 사용하여 필터링 중임을 나타냄. 일반적인 경우
     • Using index: 인덱스만으로 모든 데이터를 조회했음을 의미, 매우 좋은 경우이다.
     • Using temporary: 임시 테이블을 사용한다는 의미로, 성능을 저하시킬 수 있다
     • Using filesort: 정렬을 위해 파일을 사용하는 경우로, 성능 저하의 주요 원인 중 하나
   • 튜닝 방법:
     • Using temporary, Using filesort는 피하는 것이 좋다 이를 피하려면 인덱스를 추가하여 정렬이 필요 없도록 하거나, 정렬이 효율적으로 이루어지도록 쿼리를 변경해야 된다.

쿼리 튜닝을 위한 주요 팁

1. 인덱스 최적화:
   • 쿼리에 사용된 컬럼들에 대해 적절한 인덱스를 추가하여 전체 테이블 스캔을 줄인다
   • 조인(JOIN)을 수행할 때 조인 조건으로 사용되는 컬럼에 인덱스를 설정하는 것이 좋다.
2. type 컬럼의 개선:
   • ALL 접근 방식은 전체 테이블을 읽기 때문에 성능에 문제가 발생, 이를 피하기 위해 인덱스 추가나 쿼리 조건 변경을 통해 ALL이 아닌 접근 방식을 사용하게 한다.
3. 불필요한 읽기 줄이기:
   • rows 값이 너무 큰 경우, 불필요한 행을 줄이기 위해 쿼리의 필터링 조건을 개선하고, 해당 조건이 인덱스를 통해 효율적으로 필터링될 수 있도록 해야 한다.
4. 임시 테이블 및 파일 정렬 피하기:
   • Using temporary나 Using filesort가 표시될 경우, 성능을 저하시킬 수 있는 주요 원인이 되므로 인덱스 추가나 쿼리 재작성으로 이를 피해야 한다.

간단한 예시

다음과 같은 쿼리가 있다고 가정

EXPLAIN SELECT * FROM employees WHERE department_id = 5 ORDER BY salary;

EXPLAIN 결과:

• type이 ALL로 나타난다면, department_id에 적절한 인덱스를 추가하여 ALL을 ref나 range로 바꾸는 것이 좋다.
• Extra에 Using filesort가 표시되면, salary 컬럼에 정렬을 위해 인덱스를 추가함으로써 파일 정렬을 피할 수 있다.

요약

EXPLAIN 결과를 분석할 때는 type, key, rows, Extra 컬럼에 주목해야 하고, 쿼리 성능을 개선하기 위해 가능한 한 테이블의 전체 스캔(ALL)을 피하고, 적절한 인덱스를 사용하도록 튜닝해야 된다. EXPLAIN을 통해 쿼리의 실행 계획을 분석하고, 필요에 따라 쿼리나 인덱스를 조정하면서 성능을 개선해 나가는 것이 중요 하다.