쿼리익스플랜

정의와 간단한 설명

• 쿼리 플랜, 쿼리 실행계획이라고도 부른다.
• “어떤 테이블을 어떤 순서로 읽고, 어떤 인덱스를 사용하며, 어떤 연산을 수행할지”에 대한 것이 쿼리 익스플랜이다. Query Execution Plan의 약어이다.
• 데이터베이스 관리 시스템(DBMS)은 쿼리를 처리할 때 최적의 쿼리 플랜을 선택한다.
• SQL 쿼리는 선언적인 특성을 가지고 있어서, 동일한 쿼리라도 다양한 실행 방법이 가능합니다. 쿼리 옵티마이저는 이러한 다양한 실행 계획 중에서 가장 효율적인 것을 선택하여 쿼리 성능을 최적화합니다
• SQL 문으로 요청한데이터를 어떻게 불러올 것인지에 관한 계획, 즉 경로설정계획이라고 볼 수 있다.
• SQL을 처리하는 최저비용의 경로를 생성해주는 DBMS 내부 핵심엔진인 쿼리 옵티마이저가 쿼리를 수행할 때 생성한 최적의 처리경로를 실행계획(Query Plan)이라고 한다.
  
  
  
  

MySQL에서 쿼리 익스플랜 보는 방법

• 실행 계획을 확인하는 키워드로는EXPLAIN,DESCRIBE,DESC 가 있는데,
• MySQL에서는 실행할 쿼리문앞에 EXPLAIN 키워드를 붙여 쿼리 실행계획에 대한 정보를 리턴받을 수 있다.
• 위처럼 SELECT, DELETE, INSERT, UPDATE 문 앞에 EXPLAIN 키워드를 붙이면 쿼리가 실행될 때 DB가 선택할 실행계획을 확인할 수 있습니다.

EXPLAIN SELECT * FROM users WHERE age > 30;

정리

• EXPLAIN은 쿼리 튜닝의 핵심 도구이다. 쿼리익스플랜을 확인해서 효율적으로 설계해야한다.
• 대부분의 경우, type이 ALL이면 성능 저하 가능성, 테이블 풀스캔이라서 bad, 인덱스 최적화 필요
• Extra의 Using filesort, Using temporary는 불필요한 연산이 있는지 의심, 추가적인 기능이라 굳이 안봐도 된다.
• 쿼리 실행 계획을 주기적으로 확인하고 인덱스 설계를 점검하는 습관이 중요

쿼리 익스플랜 조인 시 적용하기 좋은 Type순서 (위→아래)

const / eq_ref	매우 빠른 방식 (기본키나 고정된 값)
ref	인덱스를 사용한 단일 행 접근
range	범위 검색 (ex. BETWEEN, >, < 등)
index	인덱스 full scan
ALL	테이블 full scan (가장 느림) ← 주의

쿼리 익스플랜 주요 컬럼

id	실행 순서 ID. 서브쿼리나 조인 순서를 구분
select_type	쿼리 유형. (SIMPLE, PRIMARY, SUBQUERY 등)
table	접근 대상 테이블 이름
partitions	사용할 파티션 (있을 경우)
type	조인 방식. 성능을 좌우하는 중요한 항목
possible_keys	사용 가능한 인덱스 목록
key	실제 사용된 인덱스
key_len	사용된 인덱스의 길이 (바이트 단위)
ref	인덱스와 비교되는 컬럼 또는 상수
rows	DB가 예상하는 읽게 될 행 수
filtered	조건에 의해 필터링될 확률 (%)
Extra	추가적인 정보 (Using index, Using where 등)

예시

• 학생테이블, 강의(course)테이블이 있다고 가정했을때

• 쿼리익스플랜를 확인하기위해 explain 키워드를 SQL명령어 상단에 붙인다.

explain
select
    students.student_name,
    courses.subject_name
from
    STUDENT_COURSES student_courses
    inner join STUDENTS students on students.student_id = student_courses.student_id
    inner join COURSES courses on courses.subject_code = student_courses.subject_code

• 리턴값(쿼리실행정보)
  • MySQL은 이 SELECT 쿼리의 실행에 필요한 각 단계와 각 단계별로 예상되는 행 수를 출력합니다.
  • 리턴값에 조인이 어떻게 수행되고, 인덱스가 어떻게 사용되고, 테이블이 어떤 순서로 스캔되는 지 등의 정보가 포함된다.

+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+
|id|select_type|table          |partitions|type  |possible_keys       |key    |key_len|ref                                     |rows|filtered|Extra      |
+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+
|1 |SIMPLE     |student_courses|null      |ALL   |FK_Student,FK_Course|null   |null   |null                                    |4385|100     |Using where|
|1 |SIMPLE     |students       |null      |eq_ref|PRIMARY             |PRIMARY|8      |playgrounds.student_courses.student_id  |1   |100     |null       |
|1 |SIMPLE     |courses        |null      |eq_ref|PRIMARY             |PRIMARY|202    |playgrounds.student_courses.subject_code|1   |100     |null       |
+--+-----------+---------------+----------+------+--------------------+-------+-------+----------------------------------------+----+--------+-----------+

확인해야할 정보

• id
  • 실행순서를 의미
  • id 항목은 연결된 SELECT 문의 각 단계를 식별합니다. 만약 id가 같다면, 이는 해당 단계가 동일한 SELECT 문인것.
  • 이 쿼리에서 id 값의 차이는 두 SELECT 문이 별개의 단계로 처리되는 것을 의미합니다.
  • 여기서 주의해야 할 점은, 실행 계회의 id 컬럼이 테이블의 접근 순서를 의미하지는 않는다는 것입니다.
    
    
• select _type
  • 어떤 유형의 작업이 수행되는지를 의미
  • SIMPLE:
    • 이 유형은 서브쿼리나 UNION이 없는 기본 SELECT문을 나타냅니다. 여기선 복잡한 구조를 다루지 않고 단일 테이블에 대한 간결한 조회를 실행합니다.
  • PRIMARY:
    • 이 유형은 가장 외부 레벨의 SELECT문이나, 관계형 서브쿼리 또는 UNION을 가진 쿼리를 나타냅니다.
  • UNION:
    • 복수의 SELECT 쿼리 결과를 통합할 때 UNION 명령어를 사용하게 됩니다. 이때 첫 번째 SELECT 이후의 모든 SELECT 절에 대해 이 타입이 표시됩니다.
  • UNION RESULT:
    • UNION 작업이 마무리되면, 그 결과는 UNION RESULT로 나타나게 됩니다. 이것은 UNION 연산의 최종 결과를 제공합니다.
  • SUBQUERY:
    • 쿼리 내 서브쿼리가 메인 쿼리와 별개로 작동할 때 이를 SUBQUERY라 합니다. 그러나 이 유형은 서브쿼리가 FROM 절 이외의 위치에 사용될 때만 적용됩니다.
  • DEPENDENT SUBQUERY:
    • 기본 쿼리의 결과에 따라 실행될 수 있는 하위 쿼리를 의미합니다. 보조 쿼리의 종류 중 하나로, 외부 쿼리에 의존적인 보조 쿼리는 외부 쿼리의 결과에 따라 그 결과가 달라지는 보조 쿼리입니다.
  • DERIVED:

    • FROM 절에서 서브쿼리가 사용될 때, 그 서브쿼리에 대한 결과는 일종의 일시적인 테이블을 만드는 것으로 간주됩니다. MySQL에서는 이렇게 FROM 절에서 사용된 서브쿼리를 파생 테이블이라고 하며, 일반적인 RDBMS는 인라인 뷰(Inline View) 또는 서브 셀렉트(Sub Select)라고 부릅니다.

      
      
• table
  • table 필드는 쿼리가 참조하는 테이블의 이름을 의미한다.
  • 만약 쿼리가 둘 이상의 테이블을 참조한다면, EXPLAIN 결과에 각각의 테이블에 대한 레코드가 표시됩니다.
  • 여기서 주의할 점은 table 필드가 언제나 물리적 테이블을 나타내는 것은 아니라는 점입니다.
  • 따라서, 복합 쿼리의 일부인 서브쿼리나, 별도의 테이블로 생성된 결과 집합을 표현하는 데 사용할 수 있습니다. 조인테이블같은 것들을 말한다.

    explain
    select
        users.id,
        orders.order_number,
        products.product_name
    from
        USERS users
        inner join ORDERS orders on orders.user_id = users.id
        inner join PRODUCTS products on products.id = orders.product_id
    where
        users.country = 'USA';
    
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+
    | id  | select_type |  table   | type  | possible_keys |   key    | key_len |          ref               | rows |   Extra     |
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+
    |  1  | SIMPLE      | users    | ALL   | PRIMARY       | NULL     | NULL    | NULL                       | 1000 | Using where |
    |  1  | SIMPLE      | orders   | ref   | user_id       | user_id  | 5       | database.users.id          |   10 |             |
    |  1  | SIMPLE      | products | eq_ref| PRIMARY       | PRIMARY  | 4       | database.orders.product_id |    1 |             |
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+

    
    
• partition

  • 파티셔닝된 테이블에 대한 쿼리를 수행할 때 참조되는 파티션이 무엇인지를 나타내는 속성

  • 만약 쿼리가 테이블의 모든 파티션을 스캔한다면, partitions 필드는 NULL 값이 된다.

    • 파티셔닝 최적화가 잘 이루어지지 않은 것이라고 볼 수 있다.

  • 쿼리가 파티션을 올바르게 이용해 한 개 혹은 일부 파티션만 스캔한다면, 이 필드는 해당 파티션 이름을 표시합니다.

    explain
    select
        users.id,
        orders.order_number,
        products.product_name
    from
        USERS users
        inner join ORDERS orders on orders.user_id = users.id
        inner join PRODUCTS products on products.id = orders.product_id
    where
        users.country = 'USA';
    
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+
    | id  | select_type |  table   | type  | possible_keys |   key    | key_len |          ref               | rows | partiitions |   Extra     |
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+
    |  1  | SIMPLE      | users    | ALL   | PRIMARY       | NULL     | NULL    | NULL                       | 1000 | p0,p1       | Using where |
    |  1  | SIMPLE      | orders   | ref   | user_id       | user_id  | 5       | database.users.id          |   10 | p0          |             |
    |  1  | SIMPLE      | products | eq_ref| PRIMARY       | PRIMARY  | 4       | database.orders.product_id |    1 | NULL        |             |
    +-----+-------------+----------+-------+---------------+----------+---------+----------------------------+------+-------------+-------------+

    
    
• type

  • MySQL서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타내는 속성이다.

  • 아래 표를 참고하자.

    const / eq_ref	매우 빠른 방식 (기본키나 고정된 값)
    ref	인덱스를 사용한 단일 행 접근
    range	범위 검색 (ex. BETWEEN, >, < 등)
    index	인덱스 full scan
    ALL	테이블 full scan (가장 느림) ← 주의