Extra 칼럼에 표시될 수 있는 문장

Using filesort

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

Using index

• 데이터 파일을 전혀 읽지 않고 인덱스만 읽어서 쿼리를 모두 처리할 수 있을 때 이 문구가 표시된다.
  • 이렇게 인덱스만으로 쿼리를 수행할 수 있는 것을 “커버링 인덱스(Covering index)”라고 한다.
• 인덱스를 이용해 처리하는 쿼리에서 가장 큰 부하를 차지하는 부분은 인덱스 검색에서 일치하는 키 값들의 레코드를 읽기 위해 데이터 파일을 검색하는 작업이다.
  • 최악의 경우 인덱스를 통해 검색된 결과 레코드 한 건마다 디스크를 한 번씩 읽어야 한다.
• InnoDB의 모든 테이블은 클러스터링 인덱스로 구성되어 있기 때문에, 모든 세컨더리 인덱스는 데이터 레코드의 주솟값으로 프라이머리 키 값을 갖는다.

  • 따라서 first_name 칼럼만으로 인덱스를 만들었을 때 그 인덱스에 PK인 emp_no 칼럼이 같이 저장되기 때문에 다음 쿼리는 커버링 인덱스로 처리될 가능성이 상당히 높다.

    SELECT emp_no, first_name
    FROM employees
    WHERE first_name BETWEEN 'Babette' AND 'Gad';

• 쿼리를 커버링 인덱스로 처리할 수 있을 때와 그렇지 못할 때의 성능 차이는 수십 배에서 수백 배까지 날 수 있다.
  • 다만, 무조건 커버링 인덱스로 처리하려고 인덱스에 과도하게 많은 칼럼을 추가하면 인덱스의 크기가 커져서 메모리 낭비가 심해지고 레코드를 저장하거나 변경하는 작업이 매우 느려질 수 있다.
• 커버링 인덱스는 접근 방법(실행 계획의 type 칼럼)에 관계없이 사용될 수 있다.
  • eq_ref, ref, range, index_merge, index 등

Using index condition

• MySQL 옵티마이저가 Index condition pushdown 최적화를 사용하면 이 문구가 표시된다.

Using index for group-by

• GROUP BY 처리가 인덱스를 이용할 때 이 문구가 표시된다.
• GROUP BY 처리가 인덱스(B-Tree 인덱스에 한해서)를 이용하면 정렬된 인덱스 칼럼을 순서대로 읽으면서 그루핑 작업만 수행한다.
  • 레코드의 정렬이 필요하지 않고 인덱스의 필요한 부분만 읽으면 되기 때문에 상당히 효율적이고 빠르게 처리된다.
• GROUP BY 처리를 위해 인덱스를 읽는 방법을 “루스 인덱스 스캔”이라고 한다.
  • GROUP BY 처리를 위해 단순히 인덱스를 순서대로 쭉 읽는 것과 인덱스의 필요한 부분만 읽는 루스 인덱스 스캔은 다르다.

타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리

• 인덱스를 이용해 GROUP BY절을 처리할 수 있더라도 AVG() , SUM(), COUNT() 처럼 조회하려는 값이 모든 인덱스를 다 읽어야 할 때는 필요한 레코드만 듬성듬성 읽을 수가 없다.
• 이러한 쿼리는 루스 인덱스 스캔이라고 하지 않으며, 실행 계획에 Using index for group-by 메시지가 출력되지 않는다.

루스 인덱스 스캔을 통한 GROUP BY 처리

• Using index for group-by 메시지가 표시된다.
• 단일 칼럼으로 구성된 인덱스에서는 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있다.
• 다중 칼럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있고, MIN() 이나 MAX() 같이 조회하는 값이 인덱스의 첫 번째 또는 마지막 레코드만 읽어도 되는 쿼리는 “루스 인덱스 스캔”이 사용될 수 있다.
  • 인덱스를 필요한 부분만 읽는다.

Using index for skip scan

• MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 이 문구를 표시한다.
• MySQL 8.0 버전부터는 루스 인덱스 스캔 최적화를 확장한 인덱스 스킵 스캔 최적화가 도입됐다.

Using join buffer

• 조인 버퍼가 사용되는 실행 계획일 경우 이 문구가 표시된다.
  • 즉, 쿼리가 조인을 수행하기 위해 조인 버퍼를 활용했다는 것을 의미한다.
  • MySQL 5.6 버전부터는 “Batched Key Access”나 “Hash join”이 도입되면서 Using join buffer 문구 뒤에 조인 알고리즘이 추가로 표시된다.
• 일반적으로 빠른 쿼리 실행을 위해 조인되는 칼럼은 인덱스를 생성한다.
  • 실제로 조인에 필요한 인덱스는 조인에서 뒤에 읽는 테이블의 칼럼에만 필요하다.
  • MySQL 옵티마이저는 조인되는 두 테이블에 있는 각 칼럼에서 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면 그 테이블을 먼저 읽어서 조인을 실행한다.
  • 뒤에 읽는 테이블(드리븐 테이블)은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 영향이 크기 때문이다.
• 조인이 수행될 때 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면, MySQL 서버는 블록 네스티드 루프(Block Nested Loop) 조인이나 해시 조인을 사용한다.
  • 이 경우, MySQL 서버는 조인 버퍼를 사용한다.
• 조인되는 칼럼에 인덱스가 적절하게 준비돼 있다면 조인 버퍼는 크게 신경쓰지 않아도 되지만, 그렇지 않다면 조인 버퍼를 너무 부족하거나 너무 과다하게 사용되지 않게 적절히 설정하는 것이 좋다.

Using MRR

• MRR 최적화를 사용할 경우 이 문구가 표시된다.
• MySQL 엔진은 실행 계획을 수립하고 그 실행 계획에 맞게 스토리지 엔진의 API를 호출해서 쿼리를 처리한다.
  • InnoDB를 포함한 스토리지 엔진 레벨에서는 쿼리 실행의 전체적인 부분을 알지 못하기 때문에 최적화에 한계가 있다.
  • 스토리지 엔진은 MySQL 엔진이 넘겨주는 키 값을 기준으로 레코드를 한 건 한 건 읽어서 반환하는 방식으로밖에 작동하지 못하는 한계점이 있다.
• 이 같은 단점을 보완하기 위해 MRR(Multi Range Read) 최적화를 도입했다.
  • MySQL 엔진은 여러 개의 키 값을 한 번에 스토리지 엔진으로 전달하고, 스토리지 엔진은 넘겨받은 키 값들을 정렬해서 최소한의 페이지 접근만으로 필요한 레코드를 읽을 수 있게 최적화한다.
  • 각 스토리지 엔진은 디스크 접근을 최소화할 수 있게 된다.

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

• 쿼리가 index_merge 접근 방법(type 칼럼의 값이 index_merge )으로 실행되는 경우에는 2개 이상의 인덱스가 동시에 사용될 수 있다.
• 이때, 두 인덱스로부터 읽은 결과를 어떻게 병합했는지 조금 더 상세하기 설명하기 위해서 다음 3개 중 하나의 메시지를 선택적으로 출력한다.

Using intersect(…)

• 각각의 인덱스를 사용할 수 있는 조건이 AND로 연결된 경우 각 처리 결과에서 교집합을 추출해내는 작업을 수행했다는 의미다.

Using union(…)

• 각 인덱스를 사용할 수 있는 조건이 OR로 연결된 경우 각 처리 결과에서 합집합을 추출해내는 작업을 수행했다는 의미다.
• 대체로 동등 비교(Equal)처럼 일치하는 레코드 건수가 많지 않은 경우 사용된다.
  • 실제로는 WHERE 조건에 사용된 비교 조건이 모두 동등 조건일 경우 사용되며, 그렇지 않을 경우 Using sort_union이 사용된다.

Using sort_union(…)

• Using union과 같은 작업을 수행하지만 Using union으로 처리될 수 없는 경우 이 방식으로 처리된다.
  • OR로 연결된 상대적으로 대량의 range 조건들
• 두 방식의 차이점은, 이 방식은 프라이머리 키만 먼저 읽어서 정렬하고 병합한 이후 비로소 레코드를 읽어서 반환할 수 있다는 것이다.
• 각 조건이 크다 또는 작다와 같이 상대적으로 많은 레코드에 일치하는 조건이 사용되는 경우 사용된다.

Using temporary

• MySQL 서버에서 쿼리를 처리하는 동안 중간 결과를 담아 두기 위해 임시 테이블(Temporary table)을 사용하는데, 임시 테이블을 사용하면 이 문구가 표시된다.
  • 임시 테이블은 메모리상에도 생성될 수 있고 디스크상에도 생성될 수 있는데, 실행 계획만으론 어디에 생성됐는지 판단할 수 없다.
• 가장 대표적인 케이스는 GROUP BY 칼럼과 ORDER BY 칼럼이 다르기 때문에 임시 테이블이 필요한 경우이다.
• 이 문구가 표시되지는 않더라도 실제 내부적으론 임시 테이블을 사용할 때가 많다.

Using where

• MySQL 엔진 레이어에서 별도의 가공을 해서 필터링(여과) 작업을 처리한 경우에만 이 문구가 표시된다.
  • 작업 범위 결정 조건은 각 스토리지 엔진 레벨에서 처리되지만, 체크 조건은 MySQL 엔진 레이어에서 처리된다.
• 예를 들어,

  EXPLAIN
  SELECT *
  FROM employees
  WHERE emp_no BETWEEN 10001 AND 10100 ## 작업 범위 결정
  	AND gender = 'F'                   ## 체크 조건

  • 작업 범위 결정(emp_no)을 만족하는 레코드 건수가 100개라면, 스토리지 엔진은 100개를 읽어서 MySQL 엔진에 넘겨준다.
  • MySQL 엔진은 그 중 체크 조건을 만족하는 레코드만 남기고 나머지를 필터링해서 버린다.
• 가장 흔히 표시되는 내용이지만, 성능상의 문제를 일으킬지 아닐지를 적절히 선별하는 능력이 필요하다.
• MySQL 8.0에서는 실행 계획에 filtered 칼럼이 표시되므로 쉽게 성능상의 이슈가 있는지 없는지를 알아낼 수 있다.

Zero limit

• MySQL 서버에서 데이터 값이 아닌 쿼리 결과값의 메타데이터만 필요한 경우도 있다.
  • 즉, 쿼리의 결과가 몇 개의 칼럼을 가지고 각 칼럼의 타입은 무엇인지 등의 정보만 필요한 경우
  • 이 경우 쿼리의 마지막에 LIMIT 0 을 사용한다.
• LIMIT 0 을 사용하면 MySQL 옵티마이저는 결과값의 메타 정보만 반환한다.
  • 이 경우 실행 계획의 Extra 칼럼에 Zero limit 가 출력된다.

Reference

참고 서적

📔 Real MySQL 8.0