실행 계획

사람·2026년 1월 6일

MySQL

목록 보기
8/8

https://dev.mysql.com/doc/refman/8.4/en/execution-plan-information.html

1. 통계 정보

MySQL 8.0 버전부터는 인덱스되지 않은 컬럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램(Histogram) 정보가 도입되었다.

1) 테이블 및 인덱스 통계 정보

(1) MySQL 서버의 통계 정보

통계 정보를 테이블로 관리할지 여부 설정

  • MySQL 5.6 버전부터 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 영구적으로 저장 및 관리할 수 있게 개선되었다.
    -> MySQL 서버가 재시작되어도 기존의 통계 정보를 유지할 수 있게 되었다.
    • 테이블 생성 시 STATS_PERSISTENT 옵션을 설정할 수 있는데, 이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지 결정할 수 있다.
    CREATE TABLE tab_test (fd1 INT, fd2 VARCHAR(20), PRIMARY KEY(fd1))
ENGINE=InnoDB
STATS_PERSISTENT={ DEFAULT | 0 | 1 }; // 테이블 생성 시점에 값 설정
    // 옵션 설정값 변경
ALTER TABLE employees.empoyees STATS_PERSISTENT={ DEFAULT | 0 | 1 };

    STATS_PERSISTENT=0
    : 테이블의 통계 정보를 테이블에 저장하지 않음.
    STATS_PERSISTENT=1
    : 테이블의 통계 정보를 테이블에 저장해 관리함.
    STATS_PERSISTENT=DEFAULT
    : 테이블 생성 시 별도로 옵션 값을 설정하지 않았을 때의 디폴트 값이다.
    테이블의 통계를 영구적으로 관리할지 말지를 innodb_stats_persistent 시스템 변수의 값으로 결정한다.
    innodb_stats_persistent 시스템 설정 변수는 기본적으로 ON(1)로 설정되어 있어서 옵션 없이 테이블을 생성하면 영구적으로 통계 정보를 저장한다.

통계 정보 자동 수집, 갱신 관련 설정

  • innodb_stats_auto_recalc 시스템 변수의 설정 값을 ON으로 설정하면 InnoDB가 데이터가 충분히 변했다고 판단되는 시점에 자동으로 통계를 다시 계산하는데, OFF면 자동 재계산을 안 하고 ANALYZE TABLE 같은 수동 트리거에 의존하게 된다.

    • 테이블 통계 정보가 자주 갱신되면 동일 데이터/동일 쿼리에서 계획이 갑자기 바뀌는 당혹스러움을 막을 수 있다는 이유로 저자는 innodb_stats_auto_recalc을 OFF할 것을 권하고 있다.
      테이블 생성 시점에 STATS_AUTO_RECALC 옵션을 이용해 개별 테이블 단위로 자동 수집 여부를 결정할 수 있기 때문에 이걸 활용하자는 것.

      STATS_AUTO_RECALC=1
      : 테이블의 통계 정보를 자동으로 수집한다.
      STATS_AUTO_RECALC=0
      : 테이블의 통계 정보는 ANAYLYZE TABLE 명령을 실행할 때만 수집된다.
      STATS_AUTO_RECALC=DEFAULT
      : 테이블 생성 시 별도로 옵션을 설정하면 이 값으로 설정된다. 테이블의 통계 정보 수집을 innodb_stats_auto_reclac 시스템 변수의 값으로 결정한다.

      전역 변수 값은 OFF하고 데이터가 자주 바뀌는 테이블에 대해서만 STATS_AUTO_RECALC=1로 설정해 자동 수집을 하자는 것. 굉장히 합리적이고 이상적인 조합이긴 하다. 근데,
    1. '이 테이블은 안정적이겠지' 하고 STATS_AUTO_RECALC=0으로 설정해놨는데 잘못된 판단이었던 경우
    2. 실수로 STATS_AUTO_RECALC 값을 설정 안 한 채로 테이블을 생성해버려서 자동 수집이 필요한데도 innodb_stats_auto_recalc OFF 설정대로 자동 수집이 안 되어버리는 경우
      등등.... 생각보다 머리 아픈 리스크도 뒤따라온다.
      이 모든 걸 감당할 수 있고 쿼리 최적화에 미쳐 있는 조직이라면 고려할 만하지만, 그렇지 않다면 그냥 innodb_stats_auto_recalc를 켜두는 것이 더 나을 수도 있을 것 같다.
  • innodb_stats_transient_sample_pages
    : 자동으로 통계 정보 수집이 실행될 때 몇 개의 페이지를 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용할지를 정할 수 있는 시스템 변수이다. 기본값은 8이다.
  • innodb_stats_persistent_sample_pages
    : ANALYZE TABLE 명령으로 통계 정보 수집을 수동으로 진행할 때, 몇 개의 페이지를 임의로 샘플링해서 분석하고 그 결과를 통계 정보로 활용할지를 정할 수 있는 시스템 변수이다. 기본값은 20이다.

이 값들을 작게 설정하면 통계의 정확도가 낮아지고, 높게 설정하면 통계 정보 수집 시간이 길어질테니 적당히 설정하자.

테이블에 저장되는 통계 정보 조회

// 통계 정보 조회
SELECT *
FROM innodb_index_stats
WHERE database_name='employees' AND TABLE_NAME='employees';
  • 통계 정보의 각 컬럼은 다음과 같은 값을 저장하고 있다.
    • innodb_index_stats.stat_name='n_diff_pfx%'
      : 인덱스가 가진 유니크한 값의 개수
    • innodb_index_stats.stat_name='n_leaf_pages'
      : 인덱스의 리프 노드 페이지 개수
    • innodb_index_stats.stat_name='size'
      : 인덱스 트리의 전체 페이지 개수
    • innodb_index_stats.n_rows
      : 테이블의 전체 레코드 건수
    • innodb_index_stats.clustered_index_size
      : PK의 크기(InnoDB 페이지 개수)
    • innodb_index_stats.sum_of_other_index_sizes
      : PK를 제외한 세컨더리 인덱스의 크기(InnoDB 페이지 개수)
      • 이 값은 STATS_AUTO_RECALC 옵션 값에 따라 0으로 보일 수도 있는데, 그 경우 다음과 같이 테이블에 대해 ANALYZE TABLE 명령을 실행하면 통곗값이 저장된다.
        ANALYZE TABLE employees.employees;

2) 히스토그램

https://dev.mysql.com/blog-archive/histogram-statistics-in-mysql/
MySQL 8.0으로 업그레이드되면서 MySQL 서버도 컬럼의 데이터 분포도를 참조할 수 있는 히스토그램 정보를 활용할 수 있게 되었다.
히스토그램은 버킷(Bucket) 단위로 레코드 건수나 컬럼값의 범위를 구분해 관리한다.

(1) 히스토그램 정보 수집 및 조회

// 히스토그램 수집
ANALYZE TABLE employees.employees
UPDATE HISTOGRAM ON gender, hire_date;
// 수집된 히스토그램 조회
SELECT *
FROM COLUMN_STASTICS
FROM SCHEMA_NAME='empolyees' AND TABLE_NAME='employees';
  • 히스토그램 정보는 컬럼 단위로 관리된다.
  • 자동으로는 수집되지 않고, ANALYZE TABLE ... UPDATE HISTOGRAM 명령을 실행함으로써 수동으로 수집 및 관리할 수 있다.
  • 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.
    • 그래서 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT해서 참조할 수 있다.
    • information_schema.column_statstics 테이블의 HISTOGRAM 컬럼이 가진 나머지 필드들의 의미는 다음과 같다.
      • sampling-rate
        히스토그램 정보를 수집하기 위해 스캔한 페이지의 비율을 저장한다.
        샘플링 비율이 높아질수록 더 정확한 히스토그램이 되지만, 테이블을 전부 스캔하는 것은 부하가 높으며 시스템의 자원을 많이 소모한다.
        그래서 MySQL 서버는 8.0.19버전부터 histogram_generation_max_mem_size 시스템 변수에 설정된 메모리 크기에 맞게 적절히 샘플링한다. 이 변수의 초기값은 20MB이다.
        8.0.19 미만의 버전에서는 풀 테이블 스캔을 하니 주의하자.
      • histogram-type
        히스토그램의 종류를 저장한다.
      • number-of-buckets-specified
        히스토그램을 생성할 때 설정했던 버킷의 개수를 저장한다.
        기본값은 100개이다.
        최대 1024개를 설정할 수 있지만, 100개면 충분하다고 한다.

(2) 지원되는 히스토그램 타입

MySQL 8.0 버전 기준

  • 싱글톤 히스토그램(Singleton)
    • Value-Based 히스토그램 또는 도수 분포라고도 불린다.
    • 컬럼 값 개별로 레코드 건수를 관리하는 히스토그램이다.
      컬럼이 가지는 값별로 버킷이 할당된다.
    • 각 버킷이 컬럼의 값, 발생 빈도의 비율이라는 2개의 값을 가진다.
    • 주로 코드 값과 같이 유니크한 값의 개수가 상대적으로 적은 경우 사용된다.

  • 높이 균형 히스토그램(Equi-Height)
    • Height-Balanced 히스토그램이라고도 불린다.
    • 컬럼 값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램이다.
      개수가 균등한 컬럼 값의 범위별로 하나의 버킷이 할당된다.
    • 각 버킷이 범위 시작 값, 범위 마지막 값, 발생 빈도율, 각 버킷에 포함된 유니크한 값의 개수라는 4개의 값을 가진다.
    • 컬럼값의 각 범위에 대해 레코드 건수 비율이 누적으로 표시된다.
      누적이기 때문에 위 높이 균형 히스토그램에서 뒤로 갈수록 건수가 많아지는 게 아니다! 그래프의 기울기가 일정하기 때문에 각 범위 내 레코드 건수가 비슷하다는 것을 알 수 있다.

(3) 히스토그램 삭제

히스토그램 자체를 삭제

ANALYZE TABLE employees.employees
DROP HISTOGRAM ON gender, hire_date;
  • 히스토그램의 삭제 작업은 테이블의 데이터를 참조하는 것이 아니라 딕셔너리의 내용만 삭제하기 때문에 다른 쿼리의 성능에 영향을 주지 않고 즉시 완료된다.
  • 히스토그램이 사라지면 쿼리의 실행 계획이 달라질 수 있으므로 주의하자.

옵티마이저가 히스토그램을 사용하지 않도록 하기

SET GLOBAL optimizer_switch='condition_fanout_filter=off';
  • 위와 같이 optimizer_switch 시스템 변수 값을 글로벌로 변경하면 된다. 이렇게 하면 MySQL 서버의 모든 쿼리가 히스토그램을 사용하지 않는다.
    그런데 condition_fanout_filter 옵션에 의해 영향을 받는 다른 최적화 기능들이 사용되지 않을 수도 있다고 한다.
    임시로 잠깐동안만 히스토그램 사용을 끄고 싶은 상황에 사용하면 될 것 같다.
    문제가 생겼을 때 그게 히스토그램 문제인지 알고 싶을 때 등등...

특정 커넥션 또는 특정 쿼리에서만 사용하지 않도록 하기

// 현재 커넥션에서 실행되는 쿼리만 히스토그램을 사용하지 않게 설정
SET SESSION optimizer_switch='condition_fanout_filter=off';
// 현재 쿼리에서만 히스토그램을 사용하지 않게 설정
SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') */ *
FROM ...

(4) 히스토그램의 용도

  • 히스토그램이 도입되기 전에 MySQL 서버가 가지고 있던 통계 정보는 테이블의 전체 레코드 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수 정도였다.
    • 그래서 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등하게 분포되어 있을 것이라고 예측한다.
      -> 테이블의 레코드가 1000건이고 어떤 컬럼의 유니크한 값 개수가 100개였다면 MySQL 서버는 다음과 같은 동등 비교 검색에서 대략 10개의 레코드가 일치할 것이라고 예측하는 것이다.
      SELECT * FROM order WHERE user_id='matt.lee';
  • 하지만 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않는다는 문제가 있다.
    -> 히스토그램은 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 훨씬 정확한 예측을 할 수 있다.
    특정 범위의 데이터가 많고 적음을 식별할 수 있다는 것이다.
    이를 바탕으로 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단하게 되어, 쿼리의 성능에 상당한 영향을 미칠 수 있다.

(5) 히스토그램과 인덱스

인덱스 다이브

  • MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하고 최종적으로 가장 나은 실행 계획을 선택한다.
    • 이때 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴본다.
      -> 이 작업을 인덱스 다이브(Index Dive)라고 한다.
  • MySQL 8.0 서버에서는 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼의 히스토그램은 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다.
    실제 검색 조건의 대상 값에 대한 샘플링을 실행하는 것이므로 항상 히스토그램보다 정확한 결과를 기대할 수 있기 때문이다.
    히스토그램은 레코드 전체를 대상으로 샘플링을 해서 나온 결과인데, 인덱스는 검색 조건에 부합하는 값에 대해서만 샘플링을 진행할 테니까.

(MySQL측에서 주장하는) 인덱스 대신 히스토그램을 고려해볼 수 있는 이유

  1. Maintaining an index has a cost. If you have an index, every INSERT/UPDATE/DELETE causes the index to be updated. This is not free, and will have an impact on your performance. A histogram on the other hand is created once and never updated unless you explicitly ask for it. It will thus not hurt your INSERT/UPDATE/DELETE-performance.
    대충 인덱스가 업데이트가 느리다는 단점을 커버할 수 있다는 얘기 같다. 히스토그램은 사용자가 ANALYZE TABLE을 실행할 때만 생성/갱신되니까.

  2. If you have an index, the optimizer will do what we call “index dives” to estimate the number of records in a given range. This also has a certain cost, and it might become too costly if you have for instance very long IN-lists in your query. Histogram statistics are much cheaper in this case, and might thus be more suitable.
    인덱스 다이브를 수행하는 것도 공짜가 아니다. 히스토그램은 이미 만들어져 있으니 쿼리 수행 시점에 새롭게 레코드 건수를 파악하고 하지 않고 그냥 가져다 쓰면 되는 거니까. 특히 IN 절의 조건이 엄청 많으면 그냥 전체 샘플링이랑 별 차이 없는 경우도 있다는 얘기를 하고 싶은 것 같다.

3) Cost Model

(1) 개요

https://dev.mysql.com/doc/refman/8.0/en/cost-model.html
쿼리를 처리할 때는 다음과 같은 다양한 작업을 필요로 한다.

  • 디스크로부터 데이터 페이지 읽기
  • 메모리로부터 데이터 페이지 읽기
  • 인덱스 키 비교
  • 레코드 평가
  • 메모리 임시 테이블 작업
  • 디스크 임시 테이블 작업
  • MySQL 서버는 사용자의 쿼리에 대해 각각의 작업이 얼마나 많이 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다.
  • 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델(Cost Model)이라고 한다.
  • MySQL 5.7 버전부터 이 값을 조정할 수 있도록 개선되었다.
    하지만 전문 지식을 가지고 있지 않다면 기본값을 함부로 변경하지 않는 것이 좋다. 기본값으로도 MySQL 서버는 20년이 넘는 시간동안 잘 사용되어 왔다.
  • 옵티마이저의 실행 계획 수립에 사용된다.

(2) Cost Model이 사용하는 테이블

두 테이블 모두 mysql DB에 존재한다.

  • server_cost
    인덱스를 찾고, 레코드를 비교하고, 임시 테이블을 처리하는 데 드는 비용 관리
  • engine_cost
    레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리

두 테이블이 공통적으로 가지고 있는 컬럼

  • cost_name
    코스트 모델의 각 단위 작업명
  • default_value
    각 단위 작업의 디폴트 비용 (MySQL 서버 소스 코드에 설정되어 있는 값)
  • cost_value
    DBMS 관리자가 설정한 값 (이 값이 NULL이면 default_value 컬럼의 값을 사용)
  • last_updated
    단위 작업의 비용이 변경된 시점
  • comment
    비용에 대한 추가 설명

last_updated와 comment는 옵티마이저에 영향을 미치는 정보는 아니며, 단순 정보성으로 관리되는 컬럼이다.

engine_cost 테이블이 추가로 더 가지고 있는 컬럼(하지만 의미가 없음)

  • engine
    적용된 스토리지 엔진 (InnoDB만 쓴다면 그냥 냅두자.)
  • device_type
    디스크 타입
    MySQL 8.0에서는 이 컬럼 값을 활용하지 않는다(???)
    그래서 0으로만 설정할 수 있다.
    찾아보니 MySQL 9 버전에서도 여전히 활용 안 하고 있다고 한다.

(3) Cost Model에서 지원하는 단위 작업


Cost Model에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 어떤 실행 계획들이 저비용으로 바뀌는지 파악하는 것이다.

  • io_block_read_cost
    • 이 값이 증가할수록 옵티마이저가 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재되어 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
  • memory_block_read_cost
    • 이 값이 증가할수록 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.
  • disk_temptable_create_cost와 disk_temptable_row_cost
    • 이 값이 증가할수록 옵티마이저가 디스크에 임시 테이블을 만들지 않는 방향으로 실행 계획을 선택할 가능성이 높아진다.
  • key_compare_cost
    • 이 값이 증가할수록 옵티마이저가 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
  • memory_temptable_create_cost와 memory_temptable_row_cost
    • 이 값이 증가할수록 옵티마이저가 메모리 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
  • row_evaluate_cost
    • 스토리지 엔진이 반환한 레코드가 쿼리의 조건에 일치하는지를 평가하는 단위 작업이다.
    • 이 값이 증가할수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리의 비용이 높아지고, 반대로 레인지 스캔과 같이 상대적으로 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아진다.

2. 실행 계획 확인

1) 실행 계획 출력 포맷

FORMAT 옵션을 사용해 실행 계획의 표시 방법을 단순 테이블 형태, TREE, JSON 중 선택할 수 있다.

(1) 테이블 포맷

EXPLAIN
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='ABC';

(2) 트리 포맷

EXPLAIN FORMAT=TREE
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='ABC';

(3) JSON 포맷

EXPLAIN FORMAT=JSON
SELECT *
FROM employees e
  INNER JOIN salaries s ON s.emp_no=e.emp_no
WHERE first_name='ABC';

2) 쿼리 실행 시간 확인

(1) 개요

  • MySQL 8.0.18 버전부터 쿼리의 실행 계획과 단계별 소요 시간 정보를 확인할 수 있는 EXPLAIN ANALYZE 기능이 추가되었다.
  • 항상 결과를 TREE 포맷으로 보여주기 때문에 EXPLAIN 명령에 FORMAT 옵션을 사용할 수 없다.
  • 실행 시간이 아주 오래 걸리는 쿼리라면 EXPLAIN ANALYZE 명령을 실행했을 때 쿼리가 완료되어야 실행 계획의 결과를 확인할 수 있다.
    • 쿼리의 실행 계획이 아주 나쁜 경우라면 EXPLAIN 명령으로 먼저 실행 계획만 확인해서 어느 정도 튜닝한 후 EXPLAIN ANALYZE 명령을 실행하는 것이 좋다.
  • TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미한다. 실제 실행 순서는 다음 기준으로 읽으면 된다.

    들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
    들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행

EXPLAIN ANALYZE
SELECT
    p1_0.id,
    p1_0.status,
    p1_0.payment_id,
    u1_0.name,
    u1_0.email,
    p2_0.category,
    p2_0.title,
    p1_0.total_amount,
    p2_0.platform,
    p1_0.requested_at,
    p1_0.cancelled_amount,
    p1_0.cancel_reason,
    p1_0.cancelled_at,
    m1_0.NAME
FROM
    payments p1_0
        LEFT JOIN
    users u1_0
    ON p1_0.user_id=u1_0.private_id
        LEFT JOIN
    MEMBER m1_0
    ON p1_0.member_id=m1_0.ID
        JOIN
    products p2_0
    ON p2_0.private_id=p1_0.product_id
ORDER BY
    p1_0.requested_at desc
-> Nested loop inner join  (cost=130 rows=113) (actual time=0.186..0.616 rows=113 loops=1)
    -> Nested loop left join  (cost=90.6 rows=113) (actual time=0.176..0.463 rows=113 loops=1)
        -> Nested loop left join  (cost=51.1 rows=113) (actual time=0.174..0.422 rows=113 loops=1)
            -> Sort: p1_0.requested_at DESC  (cost=11.6 rows=113) (actual time=0.156..0.169 rows=113 loops=1)
                -> Filter: (p1_0.product_id is not null)  (cost=11.6 rows=113) (actual time=0.038..0.118 rows=113 loops=1)
                    -> Table scan on p1_0  (cost=11.6 rows=113) (actual time=0.0366..0.11 rows=113 loops=1)
            -> Single-row index lookup on u1_0 using UK85bxs2b2qmo9xv6u02x0dv93q (private_id=p1_0.user_id)  (cost=0.251 rows=1) (actual time=0.00207..0.0021 rows=1 loops=113)
        -> Single-row index lookup on m1_0 using PRIMARY (ID=p1_0.member_id)  (cost=0.251 rows=1) (actual time=213e-6..215e-6 rows=0.0708 loops=113)
    -> Single-row index lookup on p2_0 using UK40t6qkxhq6et2gvdjdfy0i8rv (private_id=p1_0.product_id)  (cost=0.251 rows=1) (actual time=0.00117..0.0012 rows=1 loops=113)

(2) EXPLAIN ANALYZE 결과에서 필드들의 의미

-> Single-row index lookup on u1_0 using UK85bxs2b2qmo9xv6u02x0dv93q (private_id=p1_0.user_id)  (cost=0.251 rows=1) (actual time=0.00207..0.0021 rows=1 loops=113)
  • actual time=0.00207..0.0021
    • payments 테이블에서 읽은 private_id 값을 기준으로 user 테이블에서 일치하는 레코드를 검색하는 데 걸린 시간(밀리초)을 의미한다.
    • 첫 번째 값은 첫 번째 레코드를 가져오는 데 걸린 평균 시간을 의미한다.
    • 두 번째 값은 마지막 레코드를 가져오는 데 걸린 평균 시간을 의미한다.
  • rows=1
    • 검색 결과 일치하는 평균 레코드 건수를 의미한다.
  • loops=113
    • payments 테이블에서 읽은 private_id 값을 기준으로 user 테이블에서 일치하는 레코드를 검색하는 작업이 반복된 횟수를 의미한다.
      payments에서 읽은 private_id의 개수가 113개였음을 알 수 있다.

여기서 말하는 '평균'은 loop를 돌며 반복된 작업들 간에 평균을 냈다는 뜻이다.
loops=113이므로 113번의 작업을 실행하면서 나타난 평균 값이라는 것.

3) 실행 계획 분석

  • 표의 각 레코드는 쿼리 문장에서 사용된 테이블(서브쿼리로 임시 테이블을 생성한 경우 그 임시 테이블까지 포함)의 개수만큼 출력된다.
  • 테이블 포맷의 경우 실행 순서가 위에서 아래로 순서대로 표시된다.
    • UNION이나 상관 서브쿼리와 같은 경우 순서대로 표시되지 않을 수도 있다.

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

(1) id 컬럼

  • 실행 계획에서 가장 왼쪽에 표시되는 컬럼이다.
  • SELECT 쿼리별로 부여되는 식별자 값이다.
  • 하나의 SELECT 문장이 하위 SELECT 문장을 포함하는 형태의 쿼리에서는 실행 계획에 최소 2개 이상의 id 값이 표시될 것이다.
  • 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.
  • 실행 계획의 id 컬럼은 테이블의 접근 순서를 의미하지는 않는다.

(2) select_type 컬럼

  • 각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지를 표시하는 컬럼이다.

SIMPLE

  • UNION이나 서브쿼리를 사용하지 않는 단순한 SELECT 쿼리인 경우이다.
  • 쿼리 문장이 아무리 복잡하더라도 실행 계획에서 select_type이 SIMPLE인 단위 쿼리는 오직 1개만 존재한다.
  • 일반적으로 가장 바깥에 있는 SELECT 쿼리에 표시된다.

PRIMARY

  • UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리에 표시된다.
  • select_type이 PRIMARY인 단위 SELECT 쿼리는 오직 1개만 존재한다.

UNION

  • UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리에 표시된다.

DEPENDENT UNION

  • UNION으로 결합하는 단위 SELECT 쿼리 가운데 외부 쿼리에 의해 영향을 받는 것을 의미한다.

(3) table 컬럼

  • 테이블 단위로 표시된다.
  • 테이블명에 별칭이 부여된 경우 별칭이 표시된다.
  • 별도의 테이블을 사용하지 않는 SELECT 쿼리의 경우(FROM 절이 없는 경우) NULL이 표시된다.
  • '<>'로 둘러싸인 이름이 명시된 경우 임시 테이블을 의미한다.
    • 항상 숫자가 함께 표시되는데, 이 숫자는 단위 SELECT 쿼리의 id 값을 지칭한다.

(4) type 컬럼

  • 각 테이블을 어떻게 읽고 있는지, 접근 방법을 나타낸다.
  • 하나의 단위 SELECT 쿼리는 단 하나의 접근 방법만 사용할 수 있다.
  • index_merge를 제외한 나머지 접근 방법은 하나의 인덱스만 사용한다.
  • 다음에 나열될 type 컬럼의 값들은 성능이 빠른 순서대로 나열된 것이다.

const

EXPLAIN
SELECT * FROM employees WHERE emp_no=10001;
  • 쿼리가 PK나 UK 컬럼을 이용하는 WHERE 절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식을 의미한다.
  • 다중 컬럼으로 구성된 PK나 UK 중에서 인덱스의 일부 컬럼만을 조건으로 사용할 때는 이 접근 방법을 사용할 수 없다.
    • PK의 일부만 조건으로 사용할 때는 const가 아닌 ref로 표시된다.
  • type 컬럼이 const인 실행 계획은 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화한다.

eq_ref

EXPLAIN
SELECT * FROM dept_emp de, employees e
WHERE e.emp_no=de.emp_no AND de.dept_no='d005';

책에서 이 쿼리에 조인이 있다길래 JOIN절이 없는데 뭔 소리지? 했는데 이렇게 하는 게 옛날 구식 조인 문법이란다. 킹받네...

  • 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
  • 조인에서 처음 읽은 테이블의 컬럼 값을, 그 다음에 읽어야 할 테이블의 PK나 UK 컬럼의 검색 조건에 사용함을 의미한다. 이때 두 번째 이후에 읽는 테이블의 type 컬럼에 eq_ref가 표시된다.
  • 두 번째 이후에 읽히는 테이블을 UK로 검색할 때 그 유니크 인덱스는 NOT NULL이어야 하며, 다중 컬럼으로 만들어진 PK 혹은 UK라면 인덱스의 모든 컬럼이 비교 조건에 사용되어야만 이 접근 방법이 사용될 수 있다.
  • 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

ref

  • 인덱스의 종류와 관계 없이 동등(equal) 조건으로 검색할 때 사용된다.
  • eq_ref와는 달리 조인의 순서와 관계 없이 사용되며, PK나 UK 등의 제약 조건도 없다.
    -> 반환되는 레코드가 반드시 1건이라는 보장이 없다.
    그래서 const나 eq_ref보다 빠르진 않지만, 그래도 동등 조건으로만 비교되므로 매우 빠른 조회 방법 중 하나이다.

위 세 가지 접근 방법 모두 WHERE 조건절에 사용하는 비교 연산자가 동등 비교 연산자(=)여야 한다는 공통점이 있다.

여기까지는 마음이 편안해지는 매우 좋은 접근 방법이다.

fulltext

  • MySQL 서버의 전문 검색(Full-text Search) 인덱스를 사용해 레코드를 읽는 접근 방법을 의미한다.
  • 전문 검색 인덱스를 사용하기 위해서는 전문 검색 인덱스가 테이블에 정의되어 있어야 한다.
  • 전문 검색은 MATCH ... AGAINST ... 구문을 사용해서 실행한다.
  • 전문 검색 조건은 우선순위가 상당히 높아서 전문 검색 인덱스와 (const, eq_ref, ref를 제외한) 일반 인덱스가 함께 사용됐다면 일반적으로 MySQL은 전문 인덱스를 사용해서 처리한다.
    • 하지만 fulltext보다 일반 인덱스를 이용하는 range가 더 빨리 처리되는 경우도 많으니 조건별로 성능을 확인해보자.

ref_or_null

  • ref 접근 방법과 같은데, NULL 비교가 추가된 형태이다.
  • 실제 업무에서 많이 활용되지는 않지만, 많약 사용된다면 나쁘지 않은 접근 방법 정도로 기억해 두면 충분하다.

unique_subquery

  • WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법이다.
  • 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 이 접근 방법을 사용한다.

index_subquery

  • 업무 특성상 IN(subquery)에서 subquery가 중복된 값을 반환할 수도 있다. 이때 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 이 접근 방법이 사용된다.

range

EXPLAIN
SELECT * FROM employees WHERE emp_no BETWEEN 10002 AND 10004;
  • 인덱스 레인지 스캔 형태의 접근 방법이다. 인덱스를 하나의 값이 아니라 범위로 검색하는 경우를 의미한다.
  • 주로 <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용한다.
  • 일반적으로 애플리케이션의 쿼리가 가장 많이 사용하는 접근 방법이다.
  • 얘도 상당히 빠르다. 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다.

index_merge

  • 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.
  • 하지만 이름만큼 그렇게 효율적으로 작동하는 것은 아니다.
    • 여러 인덱스를 읽어야 하므로 일반적으로 range보다 효율성이 떨어진다.
    • 전문 검색 인덱스를 사용하는 쿼리에서는 적용되지 않는다.
    • 이 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.

index

  • 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔을 의미한다.
  • 비교해는 레코드의 건수는 풀 테이블 스캔과 같다. 하지만 일반적으로 인덱스는 데이터 파일 전체보다 크기가 작으므로 풀 테이블 스캔보다는 빠르게 처리되며, 쿼리의 내용에 따라 정렬된 인덱스의 장점을 이용할 수 있으므로 훨씬 효율적이다.
  • 다음 조건 중 첫 번째+두 번째 조건을 충족하거나, 첫 번째+세 번째 조건을 충족하는 쿼리에서 사용할 수 있다.
    • range나 const, ref로 인덱스를 사용하지 못하는 경우.
    • 인덱스에 포함된 컬럼만으로 처리할 수 있어서 데이터 파일을 읽지 않아도 되는 쿼리인 경우
    • 인덱스를 이용해 정렬이나 그루핑 작업이 가능해서 별도의 정렬 작업을 피할 수 있는 경우

ALL

  • 풀 테이블 스캔을 의미하는 접근 방법이다. 테이블을 냅다 처음부터 끝까지 전부 읽는다.
  • 위에서 설명된 접근 방법으로는 처리할 수 없을 때 가장 마지막에 선택하는 가장 비효율적인 방법이다.
  • 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다.
    테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리의 성능을 확인해 보고 적용하는 것이 좋다.

(5) possible_keys 컬럼

  • 옵티마이저는 쿼리를 처리하기 위해 여러 가지 처리 방법을 고려하고 그중에서 비용이 가장 낮을 것으로 예상해는 실행 계획을 선택해 쿼리를 실행한다.
    possible_keys 컬럼에 있는 내용은 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록이다.
    • 이 컬럼의 내용이 모두 실제로 사용됐다는 게 아니다!

(6) key 컬럼

  • 최종 선택된 실행 계획에서 사용하는 인덱스를 의미한다.
    쿼리를 튜닝할 때는 이 컬럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.
  • 값이 PRIMARY인 경우 PK를 사용한다는 의미이며, 그 이외의 값은 모두 테이블이나 인덱스를 생성할 때 부여했던 고유 이름이다.
  • 2개 이상의 인덱스를 사용하는 index_merge가 사용된 경우에는 여러 개의 인덱스가 ','로 구분되어 표시된다.
    접근 방법이 ALL일 때와 같이 인덱스를 전혀 사용하지 못하면 값이 NULL로 표시된다.

(7) key_len 컬럼

  • 쿼리를 처리하기 위해 다중 컬럼 인덱스에서 몇 개의 컬럼까지 사용했는지를 보여준다.
    더 정확하게는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.
  • MySQL은 NOT NULL이 아닌, 즉 NULLABLE 컬럼에 대해서는 컬럼 값이 NULL인지 아닌지를 저장하기 위해 1바이트를 추가로 사용한다. 그래서 때로는 key_len 필드의 값이 데이터 타입의 길이보다 조금 길게 표시되는 경우도 발생할 수 있다.

  • 다음 예제는 두 개의 컬럼(dept_no, emp_no)으로 구성된 PK를 가지는 dept_emp 테이블을 조회하는 쿼리이다. 그리고 이 쿼리는 PK 중 dept_no만 비교에 사용한다.

    EXPLAIN
SELECT * FROM dept_emp WHERE dept_no='d005';


    dept_no 컬럼의 타입이 CHAR(4)인데, MySQL 서버는 문자 하나당 고정적으로 4바이트를 할당한다. (실제 utf8mb4 문자의 크기는 1바이트부터 4바이트까지 가변적이지만 최악의 경우로 계산하는 것.)
    그러니 key_len 컬럼의 값이 16으로 표시되어 있는 건 PK에서 앞쪽 16바이트(4*4바이트)만 유효하게 사용했다는 의미이다.

  • 위 예제와 똑같은 인덱스를 사용하지만 dept_no 컬럼과 emp_no 모두를 조건절에 사용하는 다음 쿼리가 있다고 해보자.

    SELECT * FROM dept_emp WHERE dept_no='d005' AND emp_no=10001;


    emp_no 컬럼은 INTEGER 타입이며, INTEGER 타입은 4바이트를 차지한다.
    위 쿼리는 두 인덱스를 모두 사용했기 때문에 key_len 컬럼의 값이 dept_no 컬럼의 길이(16)와 emp_no 컬럼의 길이(4)의 합인 20으로 표시된 것이다.

이런 식으로 인덱스 컬럼 중 몇 개가 사용되었는지를 이 필드를 통해 확인할 수 있다.

(8) ref 컬럼

  • 접근 방법이 ref인 경우, 참조 조건(Equals 비교 조건)으로 어떤 값이 제공되었는지 부여준다.
    • 상숫값을 지정했다면 const가 표시된다.
    • 다른 테이블의 컬럼 값이면 그 테이블명과 컬럼명이 표시된다.
  • 이 컬럼 값은 크게 신경쓰지 않아도 무방하다.
  • 하지만, 값이 func로 표시되면 조금 주의해서 볼 필요가 있다.
    이는 참조값을 그대로 사용하지 않고 콜레이션 반환이나 연산을 거쳤다는 의미이다.
    EXPLAIN
SELECT *
FROM employees e
JOIN dept_emp de ON e.emp_no=(de.emp_no-1);
    위 쿼리는 de.emp_no 값에서 1을 뺀 값으로 employees 테이블과 조인하고 있다.

    이 경우 ref 값으로 조인 컬럼명 대신 func가 표시되고 있음을 알 수 있다.
    • 그런데 이렇게 사용자가 명시적으로 값을 변환할 때뿐만 아니라 MySQL 서버가 내부적으로 값을 변환해야 할 때도 컬럼 값이 func로 출력된다.
      문자집합이 일치하지 않는 두 문자열 컬럼을 조인한다거나, 숫자 타입 컬럼과 문자열 타입의 컬럼을 조인할 때가 그 예이다.
      가능하다면 MySQL 서버가 이러한 변환을 하지 않을 수 있도록 조인 컬럼의 타입을 일치시키자.

(9) rows 컬럼

  • 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
    • 옵티마이저는 각 조건에 대해 가능한 처리 방식을 나열하고, 각 처리 방식의 비용을 비교해 최종적으로 하나의 실행 계획을 수립한다.
      • 이때 각 처리 방식이 얼마나 레코드를 읽고 비교해야 하는지 예측해서 비용을 산정한다.
      • 대상 테이블에 얼마나 많은 레코드가 포함되어 있는지, 또는 각 인덱스 값의 분포도가 어떤지를 통계 정보를 기준으로 조사해서 예측한다.
    • 통계 정보를 참조해 옵티마이저가 산출해 낸 예상값이므로 정확하지는 않다.
    • 반환하는 레코드의 예측치가 아니라, 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 처리해야 할지를 의미한다.
    • 옵티마이저는 이 값을 가지고 어떤 인덱스를 타야 할지, 혹은 그냥 테이블 풀 스캔을 하는 것이 더 효율적일지 등을 판단한다.

(10) filtered 컬럼

  • 인덱스 조건에 일치하는 레코드(이것의 개수가 rows 컬럼 값이다.) 중에서 인덱스를 타지 않는 WHERE 조건에 의해 필터링되고 남은 레코드의 비율을 의미한다.


위 실행 계획에서 ix_firstname 인덱스 조건에 만족하는 레코드의 수는 대략 233건이며, 이중에서 16.03%의 레코드만이 인덱스를 사용하지 못하는 나머지 조건에 일치한다는 뜻이다.
즉, 필터링되고 남은 레코드 수는 대략 37(233 * 0.1603)건 정도이다. 조인을 수행할 레코드 건수가 대략 37건임을 의미한다.

위 이미지는 쿼리에서 조인 순서만 반대로 바꾼 후의 실행 계획이다. 이번에는ix_firstname 인덱스 조건에 만족하는 레코드의 수가 대략 3314건이며, 이중에서 11.11%의 레코드만이 인덱스를 사용하지 못하는 나머지 조건에 일치한다.
즉, 필터링되고 남은 레코드 수는 대략 368(3314 * 0.1111)건 정도이다. 조인 순서를 바꾸니 기존보다 10배에 가까운 조인을 해야 하는 것이다. 그러니 옵티마이저는 이 조인 순서 대신 전자를 선택할 것이다.

이처럼, filtered 컬럼의 표시되는 값이 얼마나 정확히 예측되느냐에 따라 조인의 성능이 달라진다.
그렇긴 한데 이런 예측은 옵티마이저가 알아서 하는 거라 쿼리 최적화와는 큰 관련이 없다.....

(11) Extra 컬럼

  • 이름은 Extra지만 사실 중요한 내용들이 자주 표시된다.
  • 일반적으로 고정된 몇 개의 문장이 2~3개씩 함께 표시된다.

const row not found

  • 쿼리의 실행 계획에서 const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않았다는 뜻이다.
  • 테이블에 적절히 테스토용 데이터를 저장하고 다시 한 번 실행 계획을 확인해보자.

Distinct

  • 중복된 값들은 모두 무시하고 필요한 것만 가져온다는 뜻이다.

FirstMatch

  • 세미 조인에서 FirstMatch 전략이 사용되었다는 뜻이다. (9장 참고)

Full scan on NULL key

  • col1 IN (SELECT col2 FROM ...) 과 같은 쿼리에서 자주 발생한다.
    • 이러한 쿼리에서 col1이 NULL이면 서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔을 할 것임을 알려주는 것이다.
    • col1이 NOT NULL로 정의된 컬럼이라면 표시되지 않는다.
    • NULLABLE 컬럼이라도 다음과 같이 WHERE절에 col1 IS NOT NULL이라는 조건을 지정함으로써 이 문장이 표시되지 않도록 할 수 있다.
    SELECT * 
FROM tb_test1
WHERE col1 IS NOT NULL // col1이 NULL이면 후속 조건이 아예 실행되지 않는다.
  AND col IN (SELECT col2 FROM tb_test2);
  • col1 중에서 NULL인 값이 하나도 없다면 풀 테이블 스캔은 발생하지 않으니 걱정할 필요 없다.

Impossible HAVING / Impossible WHERE

  • 쿼리에 사용된 HAVING 절 또는 WHERE절의 조건을 만족하는 레코드가 없다는 뜻이다.
  • 이 문장들이 표시된다면 쿼리에 오류가 있는 경우가 대부분이니 쿼리를 다시 확인해보자.

LooseScan

  • 세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용되었다는 뜻이다. (9장 참고)

No matching min/max row

  • MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없다는 뜻이다.
  • 일치하는 레코드가 한 건도 없으니 집합 함수는 NULL을 반환할 것이다.

no matching row in const table

  • 조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없다는 뜻이다.
EXPLAIN
SELECT * 
FROM dept_emp de
JOIN (SELECT emp_no FROM employees WHERE emp_no=0) tb1 ON tb1.emp_no=de.emp_no
WHERE de.dept_no='d005';

위 쿼리에서 JOIN절 서브쿼리 결과 일치하는 레코드가 없다면 이 문장이 표시될 것이다.

No matching rows after partition prunung

  • 파티션된 테이블에 대한 UPDATE 또는 DELETE 명령의 실행 계획에서 표시될 수 있다.
  • 해당 파티션에서 UPDATE하거나 DELETE할 대상 레코드가 없다는 뜻이다.

No tables used

  • FROM 절이 없는 쿼리 문장이라는 뜻이다.
  • SELECT 1; 같은 쿼리.

Not exists

  • 옵티마이저가 조인할 때 레코드가 존재하는지 아닌지만 판단한다는 것을 의미한다.
  • 이거 뜨면 그냥 그렇구나~ 하고 넘어가자.

Plan isn't ready yet

https://dev.mysql.com/doc/refman/8.0/en/explain-for-connection.html

  • EXPLAIN FOR CONNECTION 명령을 실행했을 때 표시될 수 있다.
    SHOW PROCESSLIST; // <process_id> 확인
EXPLAIN FOR CONNECTION <process_id>;
    일반 EXPLAIN은 쿼리 실행 중 이렇게 할 예정이라고 말해주는 일종의 계획표다.
    반면 EXPLAIN FOR CONNECTION은 이미 실행 중인 살아있는 쿼리 세션이 실제로 쓰고 있는 실행 계획을 훔쳐볼 수 있는 명령이다. 한 마디로 CCTV 같은 친구.
    실행 계획이랑 실제 실행이 다를 때 유용하게 사용할 수 있다.
    다만 너무 빨리 끝나는 쿼리라면 못 본다.
  • Plan isn't ready yet은 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태에서 EXPLAIN FOR CONNECTION 명령이 실행된 것을 의미한다.
  • 대상 커넥션의 쿼리가 실행 계획을 수립할 여유 시간을 좀 더 주고 다시 명령을 실행하자.

Range checked for each record(index map: N)

Recursive

  • CTE를 이용한 재귀 쿼리의 실행 계획임을 의미한다.

Rematerialize

  • MySQL 8.0 버전부터 래터럴 조인(LATERAL JOIN) 기능이 추가됐는데, 래터럴로 조인되는 테이블은 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다.
    이렇게 임시 테이블이 생성되는 경우 이 문장이 표시된다.

Select tables optimized away

  • MIN() 또는 MAX()만 SELECT 절에 사용되거나, GROUP BY로 MIN(), MAX()를 조회하는 쿼리가 인덱스를 오름차순 또는 내림차순으로 1건만 읽는 형태의 최적화가 적용된다는 뜻이다.

Start temporary, End temporary

  • 세미 조인 최적화 중 Duplicate Weed-out 최적화 전략이 사용된다는 뜻이다.

unique row not found

  • 두 개의 테이블이 각각 유니크 컬럼으로 아우터 조인을 수행하는 쿼리에서, 아우터 테이블에 일치하는 레코드가 존재하지 않음을 의미한다.

Using filesort

  • ORDER BY 처리가 인덱스를 사용하지 못함을 의미한다.
  • 조회된 레코드를 정렬용 메모리 버퍼에 볷해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다.
  • Using filesort가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 퀴리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index

  • 커버링 인덱스로 처리됨을 의미한다. 개꿀 최적화이다.
  • 인덱스 레인지 스캔을 사용하지만 쿼리의 성능이 만족스럽지 못한 경우 인덱스에 있는 컬럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있다.
  • InnoDB의 경우 모든 테이블이 클러스터링 인덱스로 구성되어 있어 PK는 항상 인덱스에 포함되어 있다. 이 특성 때문에 쿼리가 커버링 인덱스로 처리될 가능성이 상당히 높다.
    인덱스 컬럼을 추가로 하나 더 가지고 있는 효과를 얻을 수 있는 것.
  • 하지만 무조건 커버링 인덱스로 처리하려고 인덱스에 많은 컬럼을 추가하면 더 위험한 상황이 초래될 수도 있다. 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수도 있다.

Using index condition

  • 인덱스가 Index condition pushdown 최적화를 사용한다는 뜻이다. (9장 참고)

Using index for group-by

1. 타이트 인덱스 스캔을 통한 GROUP BY 처리

  • 인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG(), SUM(), COUNT()와 같이 조회하려는 값이 모든 인덱스를 다 읽어야 하는 경우.
    -> 이러한 경우는 Loose Index Scan이라고 하지 않는다.
    -> 이러한 경우에는 실행 계획에 'Using index for group-by' 메시지가 출력되지 않는다.

2. Loose Index Scan을 통한 GROUP BY 처리

  • 단일 컬럼 인덱스라면, 그루핑 컬럼 말고는 아무것도 조회하지 않는 쿼리인 경우.
  • 다중 컬럼 인덱스라면, GROUP BY 절이 인덱스를 사용할 수 있으면서 MIN()이나 MAX()와 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리인 경우.
    -> Loose Index Scan이 사용될 수 있다.
    (인덱스를 듬성듬성 필요한 부분만 읽는다.)

1. WHERE 조건절이 없는 경우

  • GROUP BY 절의 컬럼과 SELECT로 가져오는 컬럼이 Loose Index Scan을 사용할 수 있는 조건만 갖추면 된다.

2. WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우

  • 이 경우에는 Loose Index Scan을 이용할 수 없다.

3. WHERE 절의 조건이 있고, 검색을 위해 인덱스를 사용하는 경우

  • WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 Loose Index Scan을 사용할 수 있다.
    • WHERE 절의 조건과 GROUP BY 처리가 사용할 수 있는 인덱스가 다른 경우 일반적으로 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수립하는 경향이 있기 때문.
    • 하지만 이런 경우라도, WHERE 조건에 의해 검색된 레코드 건수가 적으면 Loose Index Scan을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문에 옵티마이저가 적절히 판단하여 사용하지 않을 수도 있다.

Using index for skip scan

  • 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하였음을 나타낸다.

Using join buffer(Block Nested Loop / Batched Key Access / hash join)

  • 조인 버퍼가 사용되는 실행 계획을 의미한다.

    • 조인 버퍼는 드라이빙 테이블의 행들을 모아두는 메모리 공간이다. 드리븐 테이블을 스캔하며 조인 버퍼의 내용과 한 번에 비교함으로써 I/O를 줄이기 위함이다.
    • 조인 조건에 인덱스를 못 쓰는 경우 사용된다.
      1. 조인 컬럼에 인덱스가 없는 경우
      2. 함수, 연산, 타입 변환 때문에 인덱스 사용이 불가한 경우
      3. 드라이빙 테이블은 인덱스로 읽지만, 드리븐 테이블은 풀 스캔해야 하는 경우
        등등...
    • 이렇게 조인 버퍼를 사용하는 조인 방식(알고리즘)에는 Block Nested Loop, Batched Key Access, hash join이 있다.
      원래는 Block Nested Loop만 있었는데 8.0 버전에서 다른 두 방식이 추가되면서 알고리즘명도 실행 계획에 함께 포함되도록 바뀌었다고 한다.
    • join_buffer_size라는 시스템 변수에 최대로 할당 가능한 조인 버퍼 크기를 설정할 수 있다.
      • 조인되는 컬럼에 인덱스가 적절하게 준비되어 있다면 조인 버퍼는 크게 신경쓰지 않아도 된다.
      • 만약 그렇지 않다면, 조인 버퍼를 너무 부족하거나 너무 과다하게 사용되지 않게 적절하게 설정하는 것이 좋다. (일반적인 온라인 웹 서비스용이라면 1MB 정도도 충분하다.)

  • 조인 조건이 없는 카테시안 조인을 수행하는 쿼리는 항상 조인 버퍼를 사용한다.

  • 만약 조인 시 인덱스를 탈 것을 기대했는데 이 메시지가 출력됐다면, 조인 시 인덱스를 안 타고 있다는 뜻이니 확인이 필요하다.

Using MRR

  • MRR(Multi Range Read)
    • PK 값들 하나하나에 대해 클러스터드 인덱스를 읽는데, 이 PK 값 순서가 랜덤하게 되어 있으면 랜덤 I/O 지옥이 펼쳐진다.
    • 이를 막기 위해 MMR은
      1. 보조 인덱스에서 조건에 맞는 PK들을 수집한다.
      2. 이 PK들을 메모리 버퍼에 모은다.
      3. PK들을 정렬한다.
      4. 정렬된 순서대로 클러스터디 인덱스를 읽는다.
        이런 식으로 순차 I/O에 가까워지도록 함으로써 디스크 접근 횟수를 감소시킨다.
profile
사람
알고리즘 블로그 아닙니다.
이전 포스트

프로그래머스 SQL 고득점 Kit - SELECT(4) (MySQL)

0개의 댓글