• MySQL 서버에서 보여주는 실행 계획을 읽고 이해하려면 MySQL 서버가 데이터를 처리하는 로직을 이해할 필요가 있다.

통계 정보

  • MySQL 서버는 5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립했다.
    • 하지만 이는 테이블 컬럼의 값이 실제로 어떻게 분포돼 있는지에 대한 정보가 없기 때문에 실행 계획의 정확도가 떨어지는 경우가 많았다.
  • MySQL 8.0 버전부터는 인덱스되지 않는 컬럼들에 대해서도 데이터 분포도를 수집해 저장하는 히스토그램 정보가 도입됐다.

테이블 및 인덱스 통계 정보

  • 비용 기반 최적화에서 가장 중요한 것은 통계 정보다.
    • 예를 들어, 1억 건의 레코드가 저장된 테이블의 통계 정보가 갱신되지 않아서 레코드가 10건 미만인 것처럼 돼 있다면 옵티마이저는 실제 쿼리를 실행할 때 인덱스 레인지 스캔이 아니라 풀 테이블 스캔으로 실행해 버릴 수도 있다.
  • MySQL 5.5 버전까지는 각 테이블의 통계 정보가 메모리에만 관리되어서, MySQL 서버가 재시작되면 지금까지 수집된 통계 정보가 모두 사라진다.
  • MySQL 5.6 버전부터는 각 테이블의 통계 정보는 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됐다.
  • MySQL 5.6에서 테이블을 생성할 때 STAT_PERSISTENT 옵션을 설정할 수 있는데, 이 설정값에 따라 테이블 단위로 영구적인 통계 정보를 보관할지 말지 결정할 수 있다.
    • 0: 테이블 통계 정보를 메모리에서 관리
    • 1: 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리
    • DEFAULT: innodb_stats_persistent 시스템 변수의 값으로 결정되며 기본 값은 1.
  • ALTER TABLE 명령으로 테이블의 통계 정보를 영구적으로 또는 단기적으로 변경하는 것을 실행할 수 있다.
    • ALTER TABLE employees.employees STATS_PERSISTENT=1;
  • SELECT * FROM innodb_index_stats WHERE database_name='employees' AND TABLE_NAME='employees';
  • 다음과 같은 이벤트가 발생하면 자동으로 통계 정보가 갱신된다.
    • 테이블이 새로 오픈되는 경우
    • 테이블의 레코드가 대량으로 변경되는 경우(테이블의 전체 레코드 중에서 1/16 정도의 UPDATE 또는 INSERT나 DELETE가 되는 경우)
    • ANALYZE TABLE 명령이 실행되는 경우
    • SHOW TABLE STATUS 명령이나 SHOW INDEX FROM 명령이 실행되는 경우
    • InnoDB 모니터가 활성화 되는 경우
    • innodb_stats_on_metadata 시스템 설정이 ON인 상태에서 SHOW TALBE STATUS 명령이 실행되는 경우
  • 영구적인 통계 정보를 사용하고 있다면 innodb_stats_auto_recalc 시스템 변수를 OFF로 설정해서 통계 정보가 자동으로 갱신되는 것을 막을 수 있다.
  • 통계 정보 샘플링 관련 시스템 변수 2가지
    • innodb_stats_transient_sample_pages(기본값 8): 자동으로 통계 정보 수집이 실행될 때 8개의 페이지만 임의로 샘플링해서 분석하고 그 결과로 통계 정보로 활용한다.
    • innodb_stat_persistent_sample_pages(기본값 20): ANALYZE TABLE 명령이 실행되면 임의로 20개의 페이지만 샘플링해서 분석하고 그 결과를 영구적인 통계 정보 테이블에 저장하고 활용한다.
  • 영구적인 통계 정보의 정확도를 높이고싶다면 innodb_stats_persistent_sample_pages 시스템 변수에 높은 값을 설정하면되지만, 너무 높이면 통계 정보 수집 시간이 길어지므로 주의해야 한다.

히스토그램

  • 히스토그램은 자동으로 수집되지 않고 ANALYZE TALBE ... UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다.
    • 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다.
    • 2종류의 히스토그램 타입을 지원한다.
      • Singleton(싱글톤 히스토그램): 컬럼값 개별로 레코드 건수를 관리하는 히스토그램
        • 컬럼의 값과 발생 빈도의 비율 2개의 값을 가진다.
        • 유니크한 값의 개수가 상대적으로 적은 경우 사용된다.
      • Equi-Height(높이 균형 히스토그램): 컬럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램
        • 범위 시작 값과 마지막 값, 발생 빈도 비율과 각 버킷에 포함된 유니크한 값의 개수 4개의 값을 가진다.
    • 히스토그램의 레코드 건수 비율은 누적으로 표시된다.
      • 아래 예에서 남자는 0.5998 정도이며 여자는 (1 - 0.5998) 정도이다.
    • sampling-rate: 히스토그램 정보를 수집하기 위해서 전체 테이블에 대비한 스캔한 페이지의 비율
    • histogram-type: 히스토그램 종류
    • number-of-buckets-specified: 히스토그램을 생성할 때 설정했던 버팃의 개수를 설정한다. 기본값은 100이고 최대 1024개를 설정할 수 있다.
    • 생성된 히스토그램을 삭제하려면 아래와 같이 실행하면 된다.
      • ANALYZE TABLe employees.employees DROP HISTOGRAM ON gentder, hire_date;

  • 히스토그램이 도입되기 전에는 테이블 전체 건수와 인덱스된 컬럼이 가지는 유니크한 값의 개수만 가지고 있었다.
    • 테이블의 레코드가 1000건이고 어떤 컬럼의 유니크한 값의 개수가 100개였다면 MySQL 서버는 동등 비교 검색시 대략 10개의 레코드가 일치할 것이라고 예측했다.
    • 히스토그램을 사용함으로써 옵티마이저는 테이블의 조인 순서 등의 좀 더 정확한 판단을 할 수 있게 되었다.
  • 인덱스 다이브: MySQL 서버에서는 쿼리의 실행 계획을 수립할 때 사용 가능한 인덱스들로부터 조건절에 일치하는 레코드 건수를 대략 파악하기 위해서 실제 인덱스의 B-Tree를 샘플링해서 살펴본다. 이를 인덱스 다이브라고 부른다.
    • 인덱스된 컬럼을 검색 조건으로 사용하는 경우 그 컬럼으로 사용하는 경우 그 컬럼의 히스토그램을 사용하지 않고 실제 인덱스 다이브를 통해 직접 수집한 정보를 활용한다.
    • 따라서 히스토그램은 주로 인덱스되지 않은 컬럼에 대한 데이터 분포도를 참조하는 용도로 사용한다.
    • 인덱스 다이브 작업도 IN 절에 값이 많이 명시된 경우 비용이 커질 수 있다.

코스트 모델(Cost Model)

  • MySQL 서버가 쿼리를 처리하려면 다음과 같은 작업을 필요로한다.
    • 디스크로부터 데이터 페이지 읽기
    • 메모리(InnoDB 버퍼 풀)로부터 데이터 페이지 읽기
    • 인덱스 키 비교
    • 레코드 평가
    • 메모리 임시 테이블 작업
    • 디스크 임시 테이블 작업
  • MySQL 서버는 사용자의 쿼리에 대해 이러한 다양한 작업이 얼마나 필요한지 예측하고 전체 작업 비용을 계산한 결과를 바탕으로 최적의 실행 계획을 찾는다. 이렇게 전체 쿼리의 비용을 계산하는데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.
  • MySQL 8.0 서버의 코스트 모델은 다음 2개 테이블에 저장돼 있는 설정값을 사용한다. 모두 mysql DB에 존재한다.
    • server_cost: 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
    • engine_cost: 레코드를 가진 데이터 페이지를 가져오는데 필요한 비용 관리
  • server_cost와 engine_cost 테이블은 공통으로 5개의 컬럼을 가지고 있다.
    • cost_name: 코스트 모델의 각 작업 단위
    • default_value: 각 단위 작업의 비용 기본값
    • cost_value: DBMS 관리자가 설정한 값. NULL이면 기본값 사용
    • last_updated: 단위 작업의 비용이 변경된 시점
    • comment: 비용에 대한 추가 설명
  • engine_cost 테이블은 위 5개 컬럼에 2개 컬럼을 더 가지고 있다.
    • engine_name: 비용이 적용된 스토리지 엔진
    • device_type: 디스크 타입(MySQL 8.0 기준으로 해당 컬럼은 사용하지 않고 있다.)
  • 코스트 모델과 작업 비용 조절의 기준
    • key_compare_cost 비용을 높이면 가능하면 정렬을 수행하지 않는 방향의 실행 계획을 선택할 가능성ㅇ이 높아진다.
    • row_evaluate_cost 비용을 높이면 풀 스캔을 실행하는 쿼리들의 비용이 높아지고, 가능하면 인덱스 레인지 스캔을 사용하는 실행 계획을 선택할 가능성이 높아진다.
    • disk_temptable_create_cost와 disk_temptable_row_cost 비용을 높이면 디스크에 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
    • memory_temptable_create_cost와 memory_temptable_row_cost 비용을 높이면 메모리 임시 테이블을 만들지 않는 방향의 실행 계획을 선택할 가능성이 높아진다.
    • io_block_read_cost 비용이 높아지면 가능하면 InnoDB 버퍼 풀에 데이터 페이지가 많이 적재돼 있는 인덱스를 사용하는 실행 계획을 선택할 가능성이 높아진다.
    • memory_block_read_cost 비용이 높아지면 InnoDB 버퍼 풀에 적재된 데이터 페이지가 상대적으로 적다고 하더라도 그 인덱스를 사용할 가능성이 높아진다.
  • 코스트 모델을 꼭 바꿔서 사용해야 하는 것은아니다. 전문 지식을 가지고 있지 않다면 서비스에 사용되는 MySQL 서버의 engine_cost 테이블과 server_cost 테이블의 기본값을 함부로 변경하지 않는 게 좋다.

실행 계획 확인

실행 계획 출력 포맷

  • MySQL 8.0 버전부터는 FORMAT 옵션을 사용해 실행 계획의 표시 방법을 JSON이나 TREE, 단순 테이블 형태로 선택할 수 있다.

쿼리의 실행 시간 확인

  • EXPLAIN ANALYZE 기능으로 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다.
  • TREE 포맷의 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 실제 실행 순서는 다음 기준으로 읽는다.
    • 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
    • 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
  • 아래 쿼리는 다음의 실행 순서를 의미한다.
  • 실행 계획과 순서를 묶어서 다음과 같인 한글로 풀어 쓸 수 있다.
  • actual time=0.007..0.009: employees 테이블에서 읽은 emp_no 값을 기준으로 salaries 테이블에서 일치하는 레코드르 검색하는 데 걸린 시간(ms)를 의미한다. 첫 번째 숫자 값은 첫 번째 레코드르 가져오는데 걸린 평균 시간, 두 번째 숫자 값은 마지막 레코드까지 가져오는데 걸린 평균 시간을 의미한다.
  • rows=10: employees 테이블에서 읽은 emp_no에 일치하는 salaries 테이블의 평균 레코드 건수를 의미한다.
  • loops=233: employees 테이블에서 읽은 emp_no를 이용해 salaries 테이블의 레코들를 찾는 작업이 반복된 횟수를 의미한다.

실행 계획 분석

id 컬럼

  • 하나의 SELECT 문장은 다시 1개 이상의 하위 SELECT 문장을 포함할 수 있다.
  • SELECT 키워드 단위로 구분한 것을 “단위 쿼리"라고 한다.
  • 각 단위 쿼리는 다른 id 값을 가지고 있다.
  • id 컬럼이 테이블의 접근 순서를 의미하지는 않는다.

  • 테이블의 접근 순설르 확인해보고 싶다면 EXPLAIN FORMAT=TREE 명령으로 확인하면 테입르 접근 순서를 더 정확히 알 수 있다.

select_type 컬럼

  • SIMPLE
    • 서브 쿼리를 사용하지 않는 단순한 SELECT 쿼리
    • 쿼리 문장이 아무리 복잡하더라도 일반적으로 제일 바깥 SELECT 쿼리의 select_type은 SIMPLE로 표시된다.
  • PRIMARY
    • UNION이나 서브쿼리를 가지는 SELECT 쿼리의 실행 계획에서 가장 바깥쪽에 있는 단위 쿼리는 select_type이 PRIMARY로 표시된다.
  • UNION
    • UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리의 select_type은 UNION으로 표시된다.
    • UNION은 첫번 째 쿼리는 전체 UNION 결과를 대표하는 select_type으로 설정된다.
  • DEPENDENT UNION
    • UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.
    • 아래 쿼리에서 IN 이하 서브쿼리에서는 두 개의 쿼리가 UNION으로 표시된 것을 알 수 있다.
  • UNION RESULT
    • MySQL 8.0 이전에는 UNION ALL이나 UNION 쿼리는 모두 UNION 결과를 임시 테이블로 생성헀는데, MySQL 8.0 버전부터는 UNION ALL의 경우 임시 테이블을 사용하지 않도록 기능이 개선됐다.
    • MySQL 8.0 버전에도 여전히 UNION은 MySQL 8.0 버전에서도 여전히 임시 테이블에 결과를 버퍼링한다.
    • 임시 테이블의 가리키는 라인의 select_type이 UNION RESULT다.
    • UNION ALL은 임시 테이블을 사용하지 않으므로 UNION RESULT 라인이 없다.
  • SUBQUERY
    • FROM 절 이외에서 사용되는 서브쿼리는 select_type이 SUBQUERY이다.
    • FROM 절에 사용된 서브쿼리는 select_type이 DERIVED로 표시된다.
  • DEPENDENT SUBQUERY
    • 서브쿼리가 바깥쪽 SELECT 쿼리에 정의된 컬럼을 사용하는 경우, select_type에 DEPENDENT SUBQUERY라고 표시된다.
  • DERIVED
    • 서브 쿼리가 FROM 절에 사용된 경우이다.
    • MySQL 5.6 버전부터는 옵티마이저 옵션에 따라 FROM 절의 서브쿼리를 외부 쿼리와 통합하는 형태의 최적화가 수행되기도 한다.
    • DERIVED는 단위 SELECT 쿼리의 실행 결과로 메모리나 디스크에 임시 테이블을 생성하는 것을 의미한다.
  • DEPENDENT DERIVED
    • MySQL 8.0 버전부터는 래터럴 조인 기능이 추가되면서 FROM 절의 서브쿼리에서도 외부 컬럼을 참조할 수 있게 됐다.
    • 래터럴 조인을 사용하면 select_type이 DEPENDENT DERIVED가 된다.
  • UNCACHEABLE SUBQUERY
    • 하나의 쿼리 문장에 서브쿼리가 하나만 있더라도 그 서브쿼리가 한 번만 실행되는 것은 아니다.
    • 조건이 똑같은 서브쿼리가 실행될 때는 다시 실행하지 않고 이전의 실행 결과를 그대로 사용할 수 있게 서브쿼리의 결과를 내부적인 캐시 공간에 담아둔다.
    • 서브쿼리에 포함된 요소에 의해 캐시 자체가 불가능할 수가 있는데, 그럴 경우 select_type이 UNCACHEABLE SUBQUERY로 표시된다.
      • 사용자 변수가 서브쿼리에 사용된 경우
      • NOT-DETERMINISTIC 속성의 스토어드 루틴이 서브쿼리 내에 사용된 경우
      • UUID()나 RAND()와 같이 결괏값이 호출할 때마다 달라지는 함수가 서브쿼리에 사용된 경우
  • UNCACHEABLE UNION
    • UNCACHEABLE SUBQUERY와 비슷한 ㄱ내며
  • MATERIALIZED
    • MySQL 5.6 버전부터 도입된 select_type으로, 주로 FROM 절이나 IN 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

table 컬럼

  • 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다.
  • table 컬럼에 <derived N> 같은 이름은 임시 테이블을 의미한다.

partitions 컬럼

  • MySQL 8.0 버전부터는 EXPLAIN 명령으로 파티션 관련 실행 계획까지 모두 확인할 수 있게 변경됐다.

type 컬럼

  • type 컬럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.
  • MySQL의 매뉴얼에서는 type 컬럼을 “조인 타입"으로 소개한다.
  • ALL을 제외한 나머지는 모두 인덱스를 사용하는 접근 방법이다.

system

  • InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않고, MyISAM이나 MEMORY 테이블에서만 상요되는 접근 방법이다.
  • 레코드가 1건만 존재하는 테이블 또는 한 건도 존재하지 않는 테이블을 참조하는 형태의 접근 방법

const

  • 쿼리가 프라이머리 키나 유니크 컬럼을 이용하는 WHERE 조건절을 가지고 있으며, 반드시 1건을 반환하는 쿼리의 처리 방식
  • 유니크 인덱스 스캔
  • 다중 컬럼으로 구성된 프라이머리 키나 유니크 키 중에서 인덱스의 인덱스의 일부 컬럼만 조건으로 사용할 때는 const 타입의 접근 방법을 사용할 수 없다.
  • type 컬럼이 const인 실행 계획은 MySQL의 옵티마이저가 쿼리를 최적화하는 단계에서 쿼리를 먼저 실행해서 통째로 상수화 한다.

eq_ref

  • 여러 테이블이 조인되는 쿼리의 실행 계획에서만 표시된다.
  • 두 번째 이후에 읽는 테이블이 프라이머리 키나 유니크 인덱스를 사용한다면 eq_ref가 표시된다.
  • 조인에서 두 번째 이후에 읽는 테이블에서 반드시 1건만 존재한다는 보장이 있어야 사용할 수 있는 접근 방법이다.

ref

  • 조인 순서와 관계없이 사용된다.
    • 인덱스 종류와 관계 없이 동등건으로 검색하는 경우
  • 1건의 레코드만 반환된다는 보장이 없어도 됨

fulltext

  • 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법
  • MATCH (…) AGAINST (…)

ref_or_null

  • ref 접근 방법과 같은데, NULL 비교가 추가된 형태다.

unique_subquery

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

index_subquery

  • IN 연산자의 특성상 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다.
  • IN(subquery)에서 서브쿼리 결과의 중복된 값을 인덱스를 이용해서 제거할 수 있을 때 index_subquery 접근 방법이 사용된다.
    • unique_subquery: IN(subquery) 형태의 조건에서 subquery의 반환 값에는 중복이 없으므로 별도의 중복 제거 작업이 필요하지 않음
    • index_subquery: IN(subquery) 형태의 조건에서 subquery의 반환 값에 중복된 값이 있을 수 있지만 인덱스를 이용해 중복된 값을 제거할 수 있음

range

  • 인덱스 레인지 스캔 형태의 접근 방법
  • <, >, IS NULL, BETWEEN, IN, LIKE 등의 연산자를 이용해 인덱스를 검색할 때 사용한다.
  • MySQL 서버가 가지고 있는 접근 방법 중에 상당히 우선순위가 낮다. 하지만, range 접근 방법도 상당히 빠르며, 모든 쿼리가 이 접근 방법만 사용해도 최적의 성능이 보장된다고 볼 수 있다.

index_merge

  • 지금까지의 접근 바업과 달리 index_merge는 2개 이상의 인덱스를 이용해 각 검색 결과를 만들어낸 후, 그 결과를 병합해서 처리하는 방식이다.
  • 아래와 같은 특징이 있다.
    • 여러 인덱스를 읽어야 하므로 일반적으로 range 접근 방법보다 효율성이 떨어진다.
    • 전문 검색 인덱스를 사용하는 쿼리에서는 index_merge가 적용되지 않는다.
    • index_merge 접근 방법으로 처리된 결과는 항상 2개 이상의 집합이 되기 때문에 그 두 집합의 교집합이나 합집합, 또는 중복 제거와 같은 부가적인 작업이 더 필요하다.
  • 아래는 쿼리는 프라이머리 키를 이용해 조회하고, first_name='Simith' 조건은 inx_firstname 인덱스를 이용해 조회한 후 두 결과를 병합하는 형태로 처리하는 실행 계획을 만들어 낸다.

index

  • 인덱스 풀 스캔
  • 아래 조건 가운데 (첫 번째+두 번째) 조건을 충족하거나 (첫 번재+세 번째) 조건을 충족하는 쿼리에서 사용되는 읽기 방식이다.
    • range나 const, ref 같은 접근 방법으로 인덱스를 사용하지 못하는 경우
    • 인덱스를 포함된 컬럼만으로 처리할 수 있는 쿼리인 경우
    • 인덱스를 이용해 정렬이나 그루핑 작업이 가능한 경우
  • 아래 쿼리는 인데스 풀 스캔이지만, LIMIT 조건이 있기 때문에 상당히 효율적이다.

ALL

  • 풀 테이블 스캔
  • 가장 비효율적인 방법
  • 다른 DBMS와 같이 InnoDB도 풀 테이블 스캔이나 인덱스 풀 스캔 같은 대량의 디스크 I/O를 유발하는 작업을 위해 한꺼번에 많은 페이지를 읽어 들이는 기능을 제공한다.
    • InnoDB에서는 이를 리드 어헤드(read ahead)라고 한다.
    • 인접한 페이지가 연속해서 몇 번 읽히면 백그라운드로 작동하는 읽기 슬드가 최대 64개의 페이지씩 한꺼번에 디스크로부터 읽어 들이기 때문에 한 번에 한 페이지씩 읽어 들이는 작업보다 상당히 빠르다.
    • MySQL 8.0부터는 병렬 쿼리 기능이 도입됐는데, 아직 초기 구현 상태라서 조건없이 전체 테이블 건수를가져오는 쿼리 정도만 동작한다.
  • 일반적으로 index와 ALL은 작업 범위를 제한하는 조건이 아니므로 빠른 응답을 사용자에게 보내야 하는 웹 서비스 등과 같은 온라인 트랜잭션 처리 환경에는 적합하지 않다.
  • 테이블이 매우 작지 않다면 실제로 테이블에 데이터를 어느 정도 저장한 상태에서 쿼리 성능을 확인해 보고 적용하는 것이 좋다.

possible_keys 컬럼

  • 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스의 목록일 뿐이다.
  • 실제로 실행 계획을 보면 그 테이블의 모든 인덱스가 목록에 포함되어 나오는 경우가 허다하기에 쿼리를 튜닝하는 데 크게 도움이 되지 않는다.

key 컬럼

  • 최종 선택된 실행 계획에서 사용되는 인덱스
  • key 컬럼에 표시되는 값이 PRIMIARY 인 경우에는 프라이머리 키를 사용한다는 의미
  • type 컬럼이 index_merge가 아닌 경우에는 반드시 테이블 하나당 하나의 인덱스만 사용할 수 있다.
  • 실행 계획의 type이 ALL일 때와 같이 인덱스를 전혀 사용하지 못한다면 key 컬럼은 NULL로 표시된다.

key_len 컬럼

  • 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려주는 값이다.
  • 다중 컬럼으로 만들어진 인덱스에서 몇 개의 컬럼까지 사용했는지 우리에게 알려준다.
  • 아래 예제는 (dept_no, emp_no)로 구성된 프라이머리 키를 가지는 depth_emp 테이블을 조회하는 쿼리다.
    • dept_no 컬럼의 타입은 CHAR(4)이고 uff8mb4 문자 집합을 사용한다.
    • utf8mb4 문자 집합에서는 문자 하나가 차지하는 공간이 1~4바이트 가변적이지만, MySQL 서버가 utf8mb4 문자를 위해 메모리 공간을 할당해야 할 때는 문자와 관계없이 고정적으로 4바이트로 계산한다.
    • 따라서 4*4=16 바이트의 인덱스를 사용하므로 dept_no 컬럼만 사용한다는 것을 확인할 수 있다.
  • 아래의 경우는 4바이트인 INTEGER 타입인 컬럼까지 사용하여 20바이트로 조회를 한다.
  • NULL이 저장될 수 있는 컬럼은 1바이트를 더 사용한다.
    • DATE 타입은 3바이트를 사용하는데, nullable 하므로 4바이트로 표기된다.

ref 컬럼

  • 참조 조건(equal 비교 조건)으로 어떤 값이 제공됐는지 보여준다.
  • 상숫값을 지정했다면 const로 표시되고, 다른 테이블의 컬럼값이라면 그 테이블명과 컬럼명이 표시된다.
  • 참조용을 ㅗ사용되는 값을 그대로 사용한 것이 아니라 콜레이션 변환이나 값 자체의 연산을 거쳐서 참조됐다면, func라고 표기된다. (Function의 줄임말)
  • MySQL 서버가 내부적으로 값을 변환하거나, 문자집합이 일치하지 않는 두 문자열 컬럼을 조인한다다거나, 숫자 타입의 컬럼과 문자열 타입의 컬럼으로 조인할 때 func가 출력된다.
    • 가능하다면 MySQL 서버가 이런 변환을 하지 않도록 되게 조인 컬럼의 타입을 일치시키는 편이 좋다.

rows 컬럼

  • 실행 계획의 효율성 판단을 위해 예측했던 레코드 건수를 보여준다.
  • 이 값은 각 스토리지 엔진별로 가지고 있는 통계 정보를 참조해 MySQL 옵티마이저가 산출해 낸 예상값이라 정확하지 않다.
  • 반환하는 레코드의 예측치가 아니라 쿼리를 처리하기 위해 얼마나 많은 레코드를 읽고 체크해야 하는지를 의미한다.

filtered 컬럼

  • WHERE 절에 사용되는 조건 중에 인덱스를 사용해서 읽은 레코드 중에 필터링 되고 남는 비율
  • 두 개 이상의 테이블을 조인할 때 조인횟수를 줄이기 위해서 filtered 컬럼의 값을 정확히 예측하는게 중요하다.
  • filtered 값을 정확히 에측할 수 있도록 히스토그램 기능을 사용할 수 있다.

Extra 컬럼

  • 이름과 달리 쿼리의 실행 계획에서 성능에 관련된 중요한 내용이 Extra 컬럼에 자주 표시된다.
  • MySQL 서버의 버전이 업데이트되고 최적화 기능이 도입될수록 새로운 내용이 더 추가될 것으로 보인다. 이 책에 언급되지 않은 내용의 Extra 컬럼에 표기된다면 매뉴얼의 내용을 참조하자

const row not found

  • const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않으면 이 내용이 표시된다.

Deleting all rows

  • MyISAM 스토리지 엔진과 같이 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 스토리지 엔진 테이블인 경우 “Deleting all rows” 문구가 표시된다.
  • MySQL 8.0 버전에서는 InnoDB 스토리지 엔진과 MyISAM 엔진 모두 더이상 실행계획에 “Deleting all rows” 최적화는 표시되지 안흔ㄴ다. 테이블의 모든 레코드를 삭제하고자 한다면 WHERE 조건절이 없는 DELETE 보다 TRUNCATE TABLE 명령을 사용할 것을 권장한다.

Distinct

  • DISTINCT를 처리하기위해 조인하지 않아도 되는 항목은 모두 무시하고 필요한 것만 조인했다는 표현이다.

FirstMatch

  • 세미 조인의 여러 최적화 중에 FirstMatch 전략이 사용되면 “FirstMatch(table_name)” 메시지를 출력한다.
  • 아래 예시는 employees 테이블 기준으로 titles 테이블에서 첫 번째로 일치하는 한 건만 검색한다는 것을 의미한다.

Full scan on NULL key

  • col1 IN (SELECT col2 FROM ...)과 같은 조건을 가진 쿼리에서 자주 발생하는데, col1의 값이 NULL이라면 결과적으로 조건은 NULL IN (SELECT col2 FROM ...)과 같이 바뀐다.
  • SQL 표준에서는 값이 NULL을 “알 수 없는 값"으로 정의하고 있으며, 그 정의대로 연산을 수행하기 위해서 이 조건은 다음과 같이 비교돼야 한다.
    • 서브쿼리가 1건이라도 결과 레코드를 가진다면 최종 비교 결과는 NULL
    • 서브쿼리가 1건도 결과 레코드를 가지지 않는다면 최종 비교 결과는 FALSE
  • col1이 NULL이면 서브쿼리에 사용된 테이블에 대해서 풀 테이블 스캔을 해야만 결과를 알아낼 수 있다.
  • 아래와 같이 IS NOT NULL 조건이 있다면, Full scan on NULL key 가 발생하지 않는다.

Impossible HAVING

  • HAVING 절의 조건을 만족하는 레코드가 없을 때 표시된다.

Impossible WHERE

  • WHERE 조건이 항상 FALSE 가 될 수밖에 없는 경우 표시된다.

LooseScan

  • 세미 조인 최적화 중에서 LooseScan 최적화 전략이 사용되면 표시된다.

No matching min/max row

  • MIN()이나 MAX()와 같은 집합 함수가 있는 쿼리 조건절에 일치하는 레코드가 한 건도 없을 때 출력된다.

no matching row in const table

  • 아래 쿼리와 같이 조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없으면 표시한다.

No matching rows after partition pruning

  • UPDATE하거나 DELETE 할 때 대상 파티션이 없다는 것을 의미한다.
  • 아래와 같이 삭제할 레코드는 없지만 대상 파티션이 있으면 메시지가 표시되지 않는다.

No tables used

  • FROM 절이 없는 쿼리 문장이나 “FROM DUAL” 형태의 쿼리 실행 계획에서 출력된다.

Not exists

  • A 테이블에는 존재하지만 B 테이블에는 없는 값을 조회해야 하는 쿼리를 작성할 수 있다.
    • 이럴 때 주로 NOT IN(subquery) 형태나 NOT EXISTS 연산자를 주로 사용한다. 이를 안티-조인이라고 한다.
    • 하지만 레코드 건수가 많을 때는 아우터 조인을 이용하면 빠른 성능을 낼 수 있다.
  • 아우터 조인을 이용해 안티-조인을 구현하면 “Not exists” 메시지가 표시된다.
    • 이는 조인 조건에 일치하는 레코드가 여러 건이 있다고 하더라도 딱 1건만 조회해보고 처리를 완료하는 최적화를 한다.

Plan isn’t ready yet

  • MySQL 8.0 버전에서는 다른 커넥션에서 실행하고 있는 쿼리의 실행 계획을 EXPLAIN FOR CONNECTION 명령으로 확인할 수 있다.
  • EXPLAIN FOR CONNECTION을 실행했을때 해당 커넥션에서 아직 쿼리의 실행 계획을 수립하지 못한 상태일 때 “Plain isn’t not ready yet"이 표시된다.

Range checked for each record(index map: N)

  • 두 개의 테이블을 조인할 때, 레코드마다 사용하는 인덱스를 체크해야할 수 있다.
  • 아래 쿼리에서 e1 테이블의 emp_no가 작을 때는 e2 테이블을 풀 테이블 스캔으로 접근하고, e1 테이블의 emp_no가 큰 값일 때는 e2 테이블을 인덱스 레인지 스캔으로 접근하는 형태를 수행하는 것이 최적의 조인 방법이다.
  • “레코드마다 인덱스 레인지 스캔을 체크한다"라고 할 때 “Range checked for each record"의 의미다.
    • “index map"에 표시된 후보 인덱스를 사용할지 여부를 검토해서 이 후보 인덱스가 별로 도움이 되지 않는다면 최종적으로 풀 테이블 스캔을 사용하기 때문에 type에는 ALL로 표시된다.
  • index map은 비트맵을 16진수로 표현한 것이다.
    • 아래 테이블에서 index map이 0x19라면 비트 값으로 변환해보면 11001이다.
      • 따라서, PRIMARY KEY, ix_nick_gender, ix_nick_phone가 인덱스 후보로 선정했음을 의미한다.

Select tables optimized away

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

Start temporary, End temporary

  • 세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용되면 문구가 표기된다.
  • Duplicate Weed-out: 서브쿼리를 일반적인 INNER JOIN 쿼리로 바꿔서 실행하고 마지막에 중복된 레코드를 제거하는 최적화 알고리즘이다.
-- 이런 쿼리를
SELECT * FROM employees e
WHERE e.emp_no IN (SELECT s.emp_no FROM salaries s WHERE s.salary > 150000);

-- 이렇게 수정
SELECT e.* FROM employees e, salaries s
WHERE e.emp_no = s.emp_no AND s.salary > 150000
GROUP BY e.emp_no;
  • 불필요한 중복 건을 제거하기 위해서 내부 임시 테이블을 사용하는데, 이때 조인되어 내부 임시 테이블에 저장되는 테이블을 식별할 수 있게 조인의 첫 번째 테이블에 “Start temporary” 문구를 보여주고 조인이 끝나는 부분에 “End temporary” 문구를 표기한다.

unique row not found

  • 두 개의 테이블이 각각 유니크(프라이머리 키 포함) 컬럼으로 아우터 조인을 수행하는 쿼리에서 아우터 테이블에 일치하는 레코드가 존재하지 않을 때 표시된다.

Using filesort

  • ORDER BY를 처리하기 위해 인덱스를 이용할 수도 있지만, 인덱스를 사용하지 못할 때는 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행한다. 이 때 “Using filesort” 코멘트가 표시된다.
  • “Using filesort"가 출력되는 쿼리는 많은 부하를 일으키므로 가능하다면 쿼리를 튜닝하거나 인덱스를 생성하는 것이 좋다.

Using index(커버링 인덱스)

  • 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때를 의미한다.
  • Extra 컬럼에 “Using index"가 표시된다.
  • 레코드 건수에 따라 차이가 있겟지만 쿼리를 커버링 인덱스로 처맇라 수 있을 때와 그렇지 못할 때의 성능 차이는 수십 배에서 수백 배까지 날 수 있다.
    • 하지만 무조건 버커링 인덱스로 처리하려고 인덱스에 많은 컬럼을 추가하면 더 위험한 상황이 초래할 수 있다.
  • 접근 방법이 eq_ref, ref, range, index_merge, index 등과 같이 인덱스를 사용하는 실행 계획에서는 모두 커버링 인덱스를 사용할 수 있다.

Using index for group-by

  • GROUP BY 처리를 위해 그루핑 기준 컬럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그루핑하는 형태의 고부하 작업을 필요로 한다.
  • 하지만 GROUP BY 처리가 인덱스를 이용하면 정렬된 인덱스 컬럼을 순서대로 읽으면서 그루핑 작업만 수행한다.
    • 이렇게 인덱스를 이용하면 레코드의 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽음녀 되기 때문에 상당히 효율적이고 빠르게 처리한다.
  • 타이트 인덱스 스캔을 통한 GROUP BY 처리
    • 인덱스를 이용해 GROUP BY 절을 처리할 수 있더라도 AVG(), SUM(), COUNT() 처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수가 없다.
    • 이러한 쿼리를 루스 인덱스 스캔이라고 한다.
    • 이는 “Using index for group-by” 메시지가 출력되지 않는다.
  • 루스 인덱스 스캔을 통한 GROUP BY 처리
    • GROUP BY 절이 인덱스를 사용할 수 있고, MIN()이나 MAX() 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 루스 인덱스 쿼리를 사용할 수 있다.
    • 이 때는 “Using index for group-by” 메시지가 출력된다.
    • WHERE 절에서 사용하는 인덱스에 의해서도 GROUP BY 절의 인덱스 사용 여부가 영향을 받는다.
      • WHERE 조건절이 없는 경우: GROUP BY 절의 컬럼과 SELECT로 가져오는 컬럼이 “루스 인덱스 스캔"을 사용할 수 있는 조건만 갖추면 된다.
      • WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못한 경우: 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야만 한다. 그래서 이 경우에도 루스 인덱스 스캔을 이용할 수 없으며, 타이트 인덱스 스캔을 통해 GROUP BY가 처리된다.
      • WHERE 절의 조건이 있고, 검색을 위해 인덱스를 사용하는 경우: WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있다. WHERE 조건절이 사용할 수 있는 인덱스와 GROUP BY 절이 사용할 수 있는 인덱스가 다른 경우 일반적으로 옵티마이저는 WHERE 조건절이 인덱스를 사용하도록 실행 계획을 수행하는 경향이 있다.
    • WHERE 절의 조건을 위해 인덱스를 이용하고, GROUP BY가 같은 인덱스를 사용할 수 있는 쿼리라고 하더라도 루스 인덱스 스캔을 사용하지 않을 수 있다.
      • WHERE 조건에 의해 검색된 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리될 수 있기 때문이다.

Using index for skip scan

  • MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 “Using index for skip scan"이 표시된다.

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

  • 테이블 조인할 때 드리븐 테이블에 적절한 인덱스가 없다면 블록 네스티드 루프 조인이나 해시 조인을 사용한다.
  • 블록 네스티드 루프 조인이나 해시 조인을 사용하면 MySQL 서버는 조인 버퍼를 사용한다.
  • “Using join buffer” 문구 뒤에 조인 알고리즘이 표시된다.

Using MRR

  • InnoDB를 포함한 스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 부분을 알지 못하기 때문에 최적화에 한계가 잇다.
    • 아무리 많은 레코드를 읽는 과정이라 하더라도 스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준을 ㅗ레코드를 한 건 한 건 읽어서 반환하는 방식으로 밖에 작동하지 못하는 한계점이 있따.
    • 실제 매번 읽어서 반환하는 레코드가 동일 페이지에 있다고 하더라도 레코드 단위로 API 호출이 발생한다.
  • MySQL 서버에서 이 같은 단점을 보안하기 위해 MRR(Multi Range Read)이라는 최적화를 도입했다.
    • MySQL 엔진은 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨 받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화한다.

Using sort_union(…), Using uinkon(…), Using intersect(…)

  • 쿼리가 index_merge 접근 방법으로 실행되는 경우에, Extra 컬럼에는 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 출력한다.
    • Using intersect: 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미다.
    • Using union: 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.
    • Using sort_union: Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우() 이 방식으로 처리된다. Using sort_union은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환할 수 있다.
  • Using union은 대체로 동등 비교처럼 일치하는 레코드 건수가 많지 않은 경우 사용되고, 각 조건이 크다 또는 작다 같이 상대적으로 많은 레코드에 일치하는 조건이 사용되는 경우는 Using sort_union이 사용된다.

Using temporary

  • 임시 테이블이 생성되면 “Using temporary"가 출력된다.
  • Extra 컬럼에 “Using temporary"가 표시되지 않지만, 실제 내부적으로는 임시테이블을 사용할 때도 많다.
    • FROM 절에 사용되는 서브쿼리는 무조건 임시 쿼리를 생성한다.
    • COUNT(DISTINCT column1)를 포함하는 쿼리가 인덱스를 사용할 수 없는 경우
    • UNION 이나 UNION DISTICT가 사용된 쿼리
    • 인덱스를 사용하지 못하는 정렬 작업
  • 임시 테이블이나 버퍼가 메모리에 저장됐는지, 디스크에 저장됐는지는 MySQL 서버의 상태 변숫값으로 확인할 수 있다.

Using where

  • MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에는 Extra 컬럼에 “Using where” 코멘트가 표시된다.
  • 프라이머리 키로 한 건의 레코드만 조회해도 “Using where"로 출력되는 현상같이 왜 “Using where"로가 표시됐는지 이해할 수 없을 때가 있다.
    • 이 때는 실행 계획의 filtered 컬럼을 확인해서 성능상 이슈가 있는지 확인할 수 있다.

Zero limit

  • MySQL 서버에서 데이터 값이 아닌 쿼리 결괏값의 메타데이터(컬럼 개수, 컬럼 타입 등)만 읽는 경우 “LIMIT 0"를 사용하면 된다.
  • 이 경우 Extra 컬럼에는 “Zero limit” 메시지가 출력된다.