옵티마이저가 항상 좋은 실행 계획을 만드는 것은 아니기 때문에 사람의 개입이 필요하다. EXPLAIN 명령어로 실행 계획을 확인할 수 있게 해준다.

이 장에서는 MySQL 서버가 보여주는 실행 계획을 읽는 법, 알고리즘 등을 살펴본다.

10.1 통계 정보

5.7 버전까지 테이블과 인덱스에 대한 개괄적인 정보를 가지고 실행 계획을 수립해서 정확도가 떨어졌다. 8.0 버전부터는 인덱스되지 않은 칼럼들에 대해서도 데이터 분포도를 수집해서 저장하는 히스토그램 정보가 도입됐다.

10.1.1 테이블 및 인덱스 통계 정보

10.1.1.1 MySQL 서버의 통계 정보

• 5.5 버전까지는 각 테이블의 통계 정보가 메모리에만 관리되서 서버가 재시작되면 통계 정보가 모두 사라졌다. 5.6 버전부터는 각 테이블의 통계 정보를 mysql 데이터베이스의 innodb_index_stats 테이블과 innodb_table_stats 테이블로 관리할 수 있게 개선됐다.
• STATS_PERSISTENT 옵션으로 영구적으로 저장할지 말지를 결정할 수 있다. (기본값은 1(on))
• STATS_AUTO_RECALC 옵션으로 통계 정보를 자동으로 수집할지 여부를 결정할 수 있다.

10.1.2 히스토그램

10.1.2.1 히스토그램 정보 수집 및 삭제

• 히스토그램 정보는 칼럼 단위로 관리되는데, 이는 자동으로 수집되지 않고 ANALYZE TABLE … UPDATE HISTOGRAM 명령을 실행해 수동으로 수집 및 관리된다. 수집된 히스토그램 정보는 시스템 딕셔너리에 함께 저장되고, MySQL 서버가 시작될 때 딕셔너리의 히스토그램 정보를 information_schema 데이터베이스의 column_statistics 테이블로 로드한다. 그래서 실제 히스토그램 정보를 조회하려면 column_statistics 테이블을 SELECT해서 참조할 수 있다.
  
• 2종류의 히스토그램 타입이 지원된다.
  • Singleton(싱글톤 히스토그램) 칼럼값 개별로 레코드 건수를 관리하는 히스토그램 (value-based 히스토그램, 도수 분포 라고도 함)
    
  • Equi-Height(높이 균형 히스토그램) 칼럼값의 범위를 균등한 개수로 구분해서 관리하는 히스토그램 (Height-Balanced 히스토그램이라고도 함)
    
• 히스토그램은 버킷 단위로 구분되어 레코드 건수나 칼럼값의 범위가 관리된다. 싱글톤 히스토그램은 칼럼이 가지는 값별로 버킷이 할당되고, 높이 균형 히스토그램은 개수가 균등한 칼럼값의 범위별로 하나의 버킷이 할당된다. 싱글톤은 각 버킷이 칼럼 값, 발생 빈도 비율 값을 가진다.(2개) 높이 균형은 각 버킷이 범위 시작값, 마지막값, 발생 빈도 비율, 각 버킷에 포함된 유니크 값 개수 값을 가진다.(4개)
  
• 싱글톤 히스토그램은 유니크한 값의 개수가 적은 경우 사용된다.

10.1.2.2 히스토그램의 용도

• 실제 응용 프로그램의 데이터는 항상 균등한 분포도를 가지지 않고, 기존 통계 정보는 이런 부분을 고려하지 못했다. 이러한 단점을 보완하기 위해 히스토그램이 도입됐다. 각 범위(버킷)별로 레코드의 건수와 유니크한 값의 개수 정보를 가지기 때문에 정확한 예측을 할 수 있다.
• 히스토그램 정보가 없으면 옵티마이저는 데이터가 균등 분포라고 예측하지만, 히스토그램이 있으면 특정 범위의 데이터가 많고 적음을 식별할 수 있다.
• 각 칼럼에 대해 히스토그램 정보가 있으면 어느 테이블을 먼저 읽어야 조인의 횟수를 줄일 수 있을지 옵티마이저가 더 정확히 판단할 수 있다.

10.1.2.3 히스토그램과 인덱스

• 인덱스 다이브 조건절에 일치하는 레코드 건수를 예측하기 위해 옵티마이저는 실제 인덱스의 B-Tree를 샘플링해서 살펴본다.
  
• 인덱스된 칼럼을 검색 조건으로 사용하는 경우, 인덱스 다이브가 항상 더 정확하기 때문에, 히스토그램은 주로 인덱스되지 않은 칼럼에 대한 데이터 분포도를 참조하는 용도로 사용된다.

10.1.3 코스트 모델

• 전체 쿼리의 비용을 계산하는 데 필요한 단위 작업들의 비용을 코스트 모델이라고 한다.
• 코스트 모델은 mysql DB에 있는 2개 테이블에 저장돼 있는 설정값을 사용한다.
  • server_cost 인덱스를 찾고 레코드를 비교하고 임시 테이블 처리에 대한 비용 관리
    
  • engine_cost 레코드를 가진 데이터 페이지를 가져오는 데 필요한 비용 관리
    
• row_evaluate_cost 스토리지 엔진이 반환한 레코드가 쿼리 조건에 일치하는지를 평가하는 단위 작업 값이 증가할수록 풀 테이블 스캔과 같이 많은 레코드를 처리하는 쿼리의 비용이 높아지고, 반대로 레인지 스캔과 같이 적은 수의 레코드를 처리하는 쿼리의 비용이 낮아진다.
  
• key_compare_cost 키 값의 비교 작업에 필요한 비용 값이 증가할수록 레코드 정렬과 같이 키 값 비교 처리가 많은 경우 쿼리 비용이 높아진다.
  
• 코스트 모델에서 중요한 것은 각 단위 작업에 설정되는 비용 값이 커지면 어떤 실행 계획들이 고비용으로 바뀌고 어떤 실행 계획들이 저비용으로 바뀌는지를 파악하는 것이다.

10.2 실행 계획 확인

실행 계획은 DESC 또는 EXPLAIN 명령으로 확인할 수 있다.

10.2.1 실행 계획 출력 포맷

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

EXPLAIN FORMAT=TREE SELECT * FROM employees … ;

10.2.2 쿼리의 실행 시간 확인

• EXPLAIN ANALYZE를 쓰면 쿼리의 실행 계획과 단계별 소요된 시간 정보를 확인할 수 있다. 이 명령은 항상 결과를 TREE 포맷으로 보여준다.
• TREE 포맷 실행 계획에서 들여쓰기는 호출 순서를 의미하며, 다음 기준으로 읽으면 된다.
  • 들여쓰기가 같은 레벨에서는 상단에 위치한 라인이 먼저 실행
  • 들여쓰기가 다른 레벨에서는 가장 안쪽에 위치한 라인이 먼저 실행
• 실행 계획에 나열된 필드들의 의미
  • actual time 숫자 값이 2개 표시되는데, 첫 번째 숫자 값은 첫 번째 레코드를 가져오는 데 걸린 평균 시간이고, 두 번째 숫자 값은 마지막 레코드를 가져오는 데 걸린 평균 시간이다.
    
  • rows 해당 테이블에서 읽은 조건에 일치하는 평균 레코드 건수를 의미한다.
    
  • loops 해당 테이블에서 읽은 정보를 이용해 해당 레코드를 찾는 작업이 반복된 횟수를 의미한다.
    

10.3 실행 계획 분석

실행 계획에서 위쪽에 출력된 결과일수록 쿼리의 바깥 부분이거나 먼저 접근한 테이블이고, 아래쪽에 출력된 결과일수록 쿼리의 안쪽 부분 또는 나중에 접근한 테이블에 해당한다.

10.3.1 id 칼럼

• id 칼럼은 단위 SELECT 쿼리별로 부여되는 식별자 값이다. 하나의 SELECT 문장 안에서 여러 개의 테이블을 조인하면 조인되는 테이블의 개수만큼 실행 계획 레코드가 출력되지만 같은 id 값이 부여된다.
  
• id 칼럼이 테이블 접근 순서를 의미하지는 않는다. 테이블 포맷으로 보면 접근 순서가 혼란스러울 수 있다. FORMAT=TREE로 보면 순서를 더 정확히 알 수 있다.
  

10.3.2 select_type 칼럼

각 단위 SELECT 쿼리가 어떤 타입의 쿼리인지 표시되는 칼럼이다. 표시될 수 있는 값은 다음과 같다.

10.3.2.1 SIMPLE

• UNION이나 서브쿼리를 사용하지 않은 단순한 SELECT 쿼리
• 아무리 복잡한 쿼리라도 SIMPLE인 단위 쿼리는 하나만 존재한다. 일반적으로 제일 바깥 SELECT 쿼리가 SIMPLE이다.

10.3.2.2 PRIMARY

• UNION이나 서브쿼리를 가지는 SELECT 쿼리의 가장 바깥쪽에 있는 단위 쿼리
• 쿼리에서 PRIMARY인 단위 쿼리는 하나만 존재한다. 제일 바깥 SELECT 쿼리가 PRIMARY이다.

10.3.2.3 UNION

• UNION으로 결합하는 단위 SELECT 쿼리 가운데 첫 번째를 제외한 두 번째 이후 단위 SELECT 쿼리들
• UNION의 첫 번째 단위 SELECT는 select_type이 DERIVED이다.

10.3.2.4 DEPENDENT UNION

• UNION select_type과 같이 UNION이나 UNION ALL로 집합을 결합하는 쿼리에서 표시된다.
• DEPENDENT는 UNION이나 UNION ALL로 결합된 단위 쿼리가 외부 쿼리에 의해 영향을 받는 것을 의미한다.

10.3.2.5 UNION RESULT

• UNION (또는 UNION DISTINCT) 결과를 담아두는 테이블
• UNION (또는 UNION DISTINCT)은 임시 테이블에 결과를 버퍼링한다.
• UNION RESULT는 실제 쿼리에서 단위 쿼리가 아니기 때문에 별도의 id값은 부여되지 않는다.

10.3.2.6 SUBQUERY

FROM절에 사용된 서브쿼리는 DERIVED로 표시되고, 그 밖의 위치에서 사용된 서브쿼리는 전부 SUBQUERY로 표시된다.

10.3.2.7 DEPENDENT SUBQUERY

• 서브쿼리가 바깥쪽 SELECT 쿼리에서 정의된 칼럼을 사용하는 경우
• 외부 쿼리가 먼저 수행된 후 내부 쿼리가 실행돼야 하므로 일반 서브쿼리보다 느리다.

10.3.2.8 DERIVED

• FROM절에 사용된 서브쿼리
• 8.0 버전부터는 최적화가 많이 개선되어 불필요한 서브쿼리는 조인으로 재작성된다.

10.3.2.9 DEPENDENT DERIVED

래터럴 조인으로 FROM절의 서브쿼리에서 외부 칼럼을 참조하는 경우

10.3.2.10 UNCACHEABLE SUBQUERY

• 서브쿼리의 결과는 내부 캐시 공간에 담겨서 재사용할 수 있다.
• 서브쿼리에 아래와 같은 요소들이 포함되면 캐시 자체가 불가능하며, 이때 UNCACHEABLE SUBQUERY로 표시된다.
  • 사용자 변수
  • NOT-DETERMINISTIC 속성의 스토어드 루틴
  • 결괏값이 호출할 때마다 달라지는 함수

10.3.2.11 UNCACHEABLE UNION

캐시가 불가능한 UNION

10.3.2.12 MATERIALIZED

주로 FROM절이나 IN(subquery) 형태의 쿼리에 사용된 서브쿼리의 최적화를 위해 사용된다.

10.3.3 table 칼럼

• 실행 계획은 단위 SELECT 쿼리 기준이 아니라 테이블 기준으로 표시된다. 테이블의 이름에 별칭이 부여된 경우에는 별칭이 표시된다.
• table 칼럼에 <>로 둘러싸인 이름이 명시될 때, 그 테이블은 임시 테이블을 의미한다. <> 안에 표시되는 숫자는 단위 SELECT 쿼리의 id값이다.
  

10.3.4 partitions 칼럼

• 파티션 생성 시 제약 사항 파티션 키로 사용되는 칼럼은 프라이머리 키를 포함한 모든 유니크 인덱스의 일부여야 한다.
  
• 파티션이 여러 개인 테이블에서 불필요한 파티션을 빼고 쿼리를 수행하기 위해 접근해야 할 것으로 판단되는 테이블만 골라내는 과정을 파티션 프루닝이라고 한다.
• 파티션을 참조하는 쿼리의 경우 옵티마이저가 쿼리 처리를 위해 필요한 파티션들의 목록만 모아서 실행 계획의 partitions 칼럼에 표시해준다.
• type 칼럼의 값이 ALL인 이유는, 파티션은 물리적으로 개별 테이블처럼 별도의 저장 공간을 가지기 때문이다. 해당하는 파티션만 풀 스캔을 실행한다.

10.3.5 type 칼럼

• type 이후의 칼럼은 MySQL 서버가 각 테이블의 레코드를 어떤 방식으로 읽었는지를 나타낸다.
• 쿼리를 튜닝할 때 인덱스를 효율적으로 사용하는지 확인하는 것이 중요하므로 type 칼럼은 반드시 체크해야 한다.
• type 칼럼에 표시될 수 있는 값 (성능 빠른 순)
  • system
  • const
  • eq_ref
  • ref
  • fulltext
  • ref_or_null
  • unique_subquery
  • index_subquery
  • range
  • index_merge
  • index
  • ALL
• ALL을 제외한 나머지 방법은 모두 인덱스를 사용하는 접근 방법이다. ALL은 풀 테이블 스캔이다.
  
• 하나의 단위 SELECT 쿼리는 접근법 중 단 하나만 사용할 수 있다.
• index_merge를 제외한 방법들은 하나의 인덱스만 사용한다.

10.3.5.1 system

• 레코드가 1건만 존재하거나 아예 없는 테이블을 참조하는 형태의 접근 방법
• InnoDB 스토리지 엔진을 사용하는 테이블에서는 나타나지 않는다.

10.3.5.2 const

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

10.3.5.3 eq_ref

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

10.3.5.4 ref

조인의 순서와 관계없이 사용되며, 프라이머리 키나 유니크 키 등의 제약 조건도 없다.

인덱스의 종류와 관계없이 동등 조건으로 검색할 때 ref 접근 방법이 사용된다.

• const, eq_ref, ref는 성능이 매우 좋은 방법으로 쿼리 튜닝 시 신경 쓰지 않아도 괜찮다.

10.3.5.5 fulltext

• 전문 검색 인덱스를 사용해 레코드를 읽는 접근 방법
• 전문 검색 인덱스가 테이블에 정의돼 있어야 한다.
• fulltext보다 일반 인덱스를 이용하는 range 접근 방법이 더 빠를 때가 있으니 조건별로 성능을 확인해보는 것이 좋다.

10.3.5.6 ref_or_null

ref 방법과 같은데 null 비교가 추가된 형태

10.3.5.7 unique_subquery

• WHERE 조건절에서 사용될 수 있는 IN(subquery) 형태의 쿼리를 위한 접근 방법
• 서브쿼리에서 중복되지 않는 유니크한 값만 반환할 때 사용
• IN(subquery) 형태의 세미 조인을 최적화하기 위한 많은 기능이 도입되어 실제로는 더 최적화된 다른 실행 계획이 많이 보일 것이다.

10.3.5.8 index_subquery

IN 연산자 특성상 괄호 안에 있는 값의 목록에서 중복된 값이 먼저 제거돼야 한다. 서브쿼리 결과의 중복된 값을 인덱스를 이용해 제거할 수 있을 때 이 방법이 사용된다.

10.3.5.9 range

• 인덱스 레인지 스캔 형태의 접근 방법
• 상당히 빠르다.
• 인덱스 레인지 스캔이라고 하면 const, ref, range 방식을 모두 묶어서 지칭하는 것이다.

10.3.5.10 index_merge

• 2개 이상의 인덱스를 이용해 각각의 검색 결과를 만든 후, 그 결과를 병합해서 처리하는 방식

10.3.5.11 index

• 인덱스를 처음부터 끝까지 읽는 인덱스 풀 스캔 방식
• 인덱스만 읽기 때문에 풀 테이블 스캔보다 빠르다.

10.3.5.12 ALL

풀 테이블 스캔

10.3.6 possible_keys 칼럼

• 옵티마이저가 최적의 실행 계획을 만들기 위해 후보로 선정했던 접근 방법에서 사용되는 인덱스 목록
• 특별한 경우를 제외하고는 무시해도 된다.

10.3.7 key 칼럼

• key 칼럼에 표시되는 인덱스는 최종 선택된 실행 계획에서 사용하는 인덱스
• 쿼리를 튜닝할 때는 key 칼럼에 의도했던 인덱스가 표시되는지 확인하는 것이 중요하다.

10.3.8 key_len 칼럼

• 실행 계획의 key_len 칼럼의 값은 쿼리를 처리하기 위해 다중 칼럼으로 구성된 인덱스에서 몇 개의 칼럼까지 사용했는지 알려준다. 더 정확히는 인덱스의 각 레코드에서 몇 바이트까지 사용했는지 알려준다.
  
• MySQL은 NULL이 될 수 있는지 여부를 나타내기 위해 1바이트를 사용하기 때문에, nullable한 칼럼을 인덱스로 사용했다면 1바이트가 추가되어 key_len에 표시된다.

10.3.9 ref 칼럼

• 접근 방법이 ref면 참조 조건으로 어떤 값이 제공됐는지 보여준다.

10.3.10 rows 칼럼

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

10.3.11 filtered 칼럼

• filtered 칼럼의 값은 필터링되어 버려지는 레코드의 비율이 아니라 필터링되고 남은 레코드의 비율을 의미한다.
• rows에 대한 비율이다.
• 일치하는 레코드 건수가 적은 테이블이 드라이빙 테이블이 되는 것이 조인의 횟수를 줄일 수 있어서 좋다.

10.3.12 Extra 칼럼

10.3.12.1 const row not found

const 접근 방법으로 테이블을 읽었지만 실제로 해당 테이블에 레코드가 1건도 존재하지 않는 경우

10.3.12.2 Deleting all rows

• 스토리지 엔진의 핸들러 차원에서 테이블의 모든 레코드를 삭제하는 기능을 제공하는 경우, 테이블의 모든 레코드를 삭제하는 기능을 호출했을 때 표시될 수 있다.
• 8.0부터는 우리가 사용하는 엔진에서는 표시되지 않는다. DELETE보다는 TRUNCATE TABLE을 사용하자.

10.3.12.3 Distinct

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

10.3.12.4 FirstMatch

세미 조인에서 FirstMatch 전략이 사용되면 Extra칼럼에 FirstMatch(table_name) 메시지를 출력한다.

10.3.12.5 Full scan on NULL key

• col1 IN (SELECT col2 FROM …) 과 같은 조건을 가진 쿼리에서 발생할 수 있다.
• col1이 NULL을 만나면 서브쿼리 테이블에 대해서 풀 테이블 스캔을 사용할 것이라는 사실을 알려주는 키워드다.

10.3.12.6 Impossible HAVING

• 쿼리에 사용된 HAVING절의 조건을 만족하는 레코드가 없을 때 표시된다.
• 쿼리가 제대로 작성되지 못한 경우가 대부분이므로 쿼리를 점검하자.

10.3.12.7 Impossible WHERE

• WHERE 조건이 항상 false인 경우 표시된다.

10.3.12.8 LooseScan

세미 조인에서 LooseScan 전략이 사용된 경우

10.3.12.9 No matching min/max row

MIN(), MAX()와 같은 집합 함수가 있는 쿼리의 조건절에 일치하는 레코드가 한 건도 없을 때 Extra 칼럼에 표시되고, 결과로는 NULL이 반환된다.

10.3.12.10 no matching row in const table

조인에 사용된 테이블에서 const 방법으로 접근할 때 일치하는 레코드가 없을 때 표시된다.

10.3.12.11 No matching rows after partition pruning

해당 메시지는 파티션된 테이블에 대한 UPDATE, DELETE 명령의 실행 계획에서 표시될 수 있다.

해당 파티션에서 UPDATE나 DELETE할 대상 레코드가 없을 때 표시된다.

10.3.12.12 No tables used

• FROM절이 없는 쿼리 문장이나 FROM DUAL 형태의 쿼리 실행 계획에서 표시된다.
• 다른 DBMS와 다르게 MySQL 서버는 FROM 절이 없는 쿼리도 허용한다. (SELECT 1 같은)

10.3.12.13 Not exists

• 안티 조인은 일반 조인을 했을 때 나오지 않는 결과만 가져오는 방법이다.
• 아우터 조인을 이용해 안티 조인을 수행하는 쿼리에서 표시된다.

10.3.12.14 Plan isn’t ready yet

• 8.0 버전부터는 다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다.
• SHOW PROCESSLIST 명령으로 다른 커넥션들에서 실행 중인 쿼리를 볼 수 있다.
• EXPLAIN FOR CONNECTION {id} (id는 show processlist에서 조회한 프로세스의 id) 명령으로 해당 쿼리의 실행 계획을 볼 수 있다.
• 해당 커넥션에서 아직 쿼리 실행 계획을 수립하지 못한 상태일 때 표시된다.

10.3.12.15 Range checked for each record(index map: N)

• 레코드마다 인덱스 레인지 스캔을 체크한다.
• N에 표시되는 index map은 16진수이다. 이진수로 전환하면 이진 비트맵의 각 자리수는 테이블에 나열된 인덱스의 순번을 의미한다. 각 자릿수의 값이 1인 인덱스를 사용 가능한 인덱스 후보로 선정했음을 의미한다.

10.3.12.16 Recursive

8.0 버전부터는 CTE(common table expression)을 이용해 재귀 쿼리를 작성할 수 있다. CTE를 이용한 재귀 쿼리의 실행 계획에는 Recursive가 표시된다.

10.3.12.17 Rematerialize

래터럴 조인의 경우, 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다. 이때 표시된다.

10.3.12.18 Select tables optimized away

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

10.3.12.14 Plan isn’t ready yet

• 8.0 버전부터는 다른 커넥션에서 실행 중인 쿼리의 실행 계획을 살펴볼 수 있다.
• SHOW PROCESSLIST 명령으로 다른 커넥션들에서 실행 중인 쿼리를 볼 수 있다.
• EXPLAIN FOR CONNECTION {id} (id는 show processlist에서 조회한 프로세스의 id) 명령으로 해당 쿼리의 실행 계획을 볼 수 있다.
• 해당 커넥션에서 아직 쿼리 실행 계획을 수립하지 못한 상태일 때 표시된다.

10.3.12.15 Range checked for each record(index map: N)

• 레코드마다 인덱스 레인지 스캔을 체크한다.
• N에 표시되는 index map은 16진수이다. 이진수로 전환하면 이진 비트맵의 각 자리수는 테이블에 나열된 인덱스의 순번을 의미한다. 각 자릿수의 값이 1인 인덱스를 사용 가능한 인덱스 후보로 선정했음을 의미한다.

10.3.12.16 Recursive

8.0 버전부터는 CTE(common table expression)을 이용해 재귀 쿼리를 작성할 수 있다. CTE를 이용한 재귀 쿼리의 실행 계획에는 Recursive가 표시된다.

10.3.12.17 Rematerialize

래터럴 조인의 경우, 선행 테이블의 레코드별로 서브쿼리를 실행해서 그 결과를 임시 테이블에 저장한다. 이때 표시된다.

10.3.12.18 Select tables optimized away

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

10.3.12.19 Start temporary, End temporary

세미 조인 최적화 중에서 Duplicate Weed-out 최적화 전략이 사용되면 표시된다.

10.3.12.20 unique row not found

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

10.3.12.21 Using filesort

• ORDER BY 처리가 인덱스를 사용하지 못할 때 표시된다. 조회된 레코드를 정렬용 메모리 버퍼에 복사해 퀵 소트 또는 힙 소트 알고리즘을 이용해 정렬을 수행하게 된다.
  
• 많은 부하를 일으키므로 쿼리를 튜닝하거나 인덱스를 생성하자.

10.3.12.22 Using index(커버링 인덱스)

• 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 표시된다.
• 인덱스 레인지 스캔을 통해 성능이 만족스럽지 못하다면 인덱스에 있는 칼럼만 사용하도록 쿼리를 변경해 큰 성능 향상을 볼 수 있다.
• InnoDB의 세컨더리 인덱스에는 데이터 레코드를 찾아가기 위한 주소로 사용하기 위해 프라이머리 키를 저장해둔다. 이는 추가 칼럼을 하나 더 가지는 인덱스의 효과를 얻을 수 있다.

10.3.12.23 Using index condition

인덱스 컨디션 푸시 다운 최적화를 사용하면 표시된다.

10.3.12.24 Using index for group-by

GROUP BY 처리를 위해 그루핑 기준 칼럼을 이용해 정렬을 수행하고 다시 정렬된 결과를 그루핑하는 고부하 작업이 필요하다. 하지만 GROUP BY 처리에 인덱스(B-Tree 인덱스)를 이용하면 정렬된 인덱스 칼럼을 읽으면서 그루핑 작업만 수행하기 때문에 효율적이고 빠르다.

GROUP BY 처리가 인덱스를 이용할 때 표시된다.

• 타이트 인덱스 스캔을 통한 GROUP BY 처리 인덱스를 이용해 GROUP BY를 처리할 수 있더라도 AVG(), SUN(), COUNT()처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 이 메시지가 표시되지 않는다.
  
• 루스 인덱스 스캔을 통한 GROUP BY 처리 이때는 메시지가 표시된다.
  

10.3.12.25 Using index for skip scan

인덱스 스캡 스캔 최적화를 사용하면 표시된다.

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

조인 버퍼를 사용한 경우 표시된다. 괄호에는 조인 알고리즘이 표시된다.

10.3.12.27 Using MRR

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

10.3.12.28 Using sort_union(…), Using union(…), Using intersect(…)

쿼리가 인덱스 머지 접근 방법으로 실행되면 2개 이상의 인덱스가 동시에 사용될 수 있다. 이때 결과를 어떻게 병합했는지에 따라 세 가지 메시지 중 하나를 출력한다.

10.3.12.29 Using temporary

임시 테이블이 사용된 경우 이 메시지가 표시된다. 인덱스를 사용하지 못하는 GROUP BY 쿼리는 대표적인 형태의 쿼리다.

10.3.12.30 Using where

• 스토리지 엔진은 디스크나 메모리상에서 필요한 레코드를 읽거나 저장하는 역할이며, MySQL 엔진은 스토리지 엔진으로부터 받은 레코드를 가공, 연산하는 작업을 수행한다.
• MySQL 엔진 레이어에서 별도의 가공을 해서 필터링 작업을 처리한 경우에만 Using where가 표시된다.
• filtered 칼럼과 같이 보면 성능상의 이슈가 있는지 없는지를 알 수 있다.

10.3.12.31 Zero limit

데이터 값이 아닌 쿼리 결괏값의 메타데이터만 필요한 경우에는 쿼리 마지막에 LIMIT 0를 사용하면 된다. 이를 표시하면 옵티마이저는 사용자의 의도를 알아채고 실제 테이블 레코드는 읽지 않고 결괏값의 메타 정보만 반환한다. 이때 이 메시지가 표시된다.