책너두 (Real MySQL 8.0 1권) 46일차 (~484p)

요약

• Extra 칼럼의 나머지 값들을 이해하게 됨.
  • Using index condition
    • 인덱스 컨디션 푸시다운 최적화
  • Using index for group-by
    • 그루핑에 따른 인덱스 처리 최적화 방식
      • 타이트 인덱스 스캔(인덱스 스캔)을 통한 GROUP BY 처리
      • 루스 인덱스 스캔을 통한 GROUP BY 처리
  • Using index for skip scan
    • 루스 인덱스 스캔 최적화를 확장한 버전
  • Using join buffer(Block Nested Loop), Using join buffer(Batched Key Access), Using join buffer(hash join)
    • 드리븐 테이블에 적절한 인덱스 칼럼이 있는게 좋음.
    • 드리븐, 드라이브 테이블을 결정하며 필요에 따라 조인 버퍼를 할당함.

메모

Using index condition

• MySQL 옵티마이저가 인덱스 컨디션 푸시 다운(Index condition pushdown) 최적화를 사용하면 Extra 칼럼에 “Using index condition” 메시지가 표시됨.
  • 9.3.1.3절 “인덱스 컨디션 푸시다운”을 참조하자.

Using index for group-by

• GROUP BY 처리를 위해 MySQL 서버는 그루핑 기준 칼럼을 이용해 정렬 작업을 수행하고 다시 정렬된 결과를 그루핑하는 형태의 고부하 작업을 필요로 함.
  • 하지만 GROUP BY 처리가 인덱스(B-Tree 인덱스에 한해)를 이용하면 별도의 추가 정렬 작업 없이, 정렬된 인덱스 칼럼을 순서대로 읽으면서 그루핑 작업만 수행한다.
    • 상당히 효율적이고 빠르게 처리됨.
• GROUP BY 처리가 인덱스를 이용할 때 쿼리의 실행 계획에서 Extra 칼럼에 “Using index for group-by” 메시지가 표시됨.
  • GROUP BY 처리를 위해 인덱스를 읽는 방법을 “루스 인덱스 스캔”이라고 함.
    • 뒤에서 다시 살펴본다.
  • GROUP BY 처리를 위해 단순히 인덱스를 순서대로 쭉 읽는 것과 인덱스의 필요한 부분만 듬성듬성 읽는 루스 인덱스 스캔은 다름.

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

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

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

• 단일 칼럼으로 구성된 인덱스에서는 그루핑 칼럼 말고는 아무것도 조회하지 않는 쿼리에서 루스 인덱스 스캔을 사용할 수 있음.
• 다중 칼럼으로 만들어진 인덱스에서는 GROUP BY 절이 인덱스를 사용할 수 있어야 함은 물론이고, MIN(), MAX() 같은 조회하는 값이 인덱스의 첫 번째, 또는 마지막 레코드만 읽어도 되는 쿼리는 “루스 인덱스 스캔”이 사용될 수 있음.
  • 이때, 인덱스를 듬성듬성하게 필요한 부분만 읽는다.
• WHERE 절에서 사용하는 인덱스에 의해서도 GROUYP BY 절의 인덱스 사용 여부가 영향을 받는다.
  • WHERE 조건절이 없는 경우
    • GROUP BY 절의 칼럼과 SELECT로 가져오는 칼럼이 “루스 인덱스 스캔”을 사용할 수 있는 조건만 갖추면 됨.
    • 그렇지 않은 쿼리는 아티으 인덱스 스캔이나 별도의 정렬 과정을 통해 처리됨.
  • WHERE 조건절이 있지만 검색을 위해 인덱스를 사용하지 못하는 경우
    • 인덱스를 사용하지 못하므로 먼저 GROUP BY를 위해 인덱스를 읽은 후, WHERE 조건의 비교를 위해 데이터 레코드를 읽어야함. → 루스 인덱스 스캔을 이용할 수 없고 타이트 인덱스 스캔 과정을 통해 GROUP BY가 처리됨.
  • WHERE 절의 조건이 있고, 검색을 위해 인덱스를 사용하는 경우
    • 하나의 단위 쿼리가 실행되는 경우, index_merge 이외의 접근 방법에서는 단 하나의 인덱스만 사용할 수 있음.
      • 그래서 WHERE 절의 조건이 인덱스를 사용할 수 있으면 GROUP BY가 인덱스를 사용할 수 있는 조건이 더 까다로워짐
      • 즉, WHERE 절의 조건과 GROUP BY 처리가 똑같은 인덱스를 공통으로 사용할 수 있을 때만 루스 인덱스 스캔을 사용할 수 있음.
• 루스 인덱스 스캔은 옵티마이저가 레코드 건수를 보며 적절히 손익 분기점을 판단한다.
  • 그래서 루스 인덱스 스캔을 사용할 수 있어도, 레코드 건수가 적으면 루스 인덱스 스캔을 사용하지 않아도 매우 빠르게 처리할 수 있음.

Using index for skip scan

• MySQL 옵티마이저가 인덱스 스킵 스캔 최적화를 사용하면 Extra 칼럼에 “Using index for skip scan”메시지를 표시함.
• 8.0 버전부터 루스 인덱스 스캔 최적화를 확장한 인덱스 스킵 스캔 최적화가 도입됨.
  • 아직 부족한 부분은 있지만 그래도 인덱스 스킵 스캔은 쿼리 최적화에 중요한 기능이므로 내부적인 처리방법을 잘 이해해 두자.
    • 8.3.4.4절 인덱스 스킵 스캔을 참조하자.

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

• 일반적으로 빠른 쿼리 실행을 위해 조인되는 칼럼은 인덱스를 생성함.
• 실제로 조인에 필요한 인덱스는 조인되는 양쪽 테이블 칼럼 모두가 필요한 것이 아니라 조인에서 뒤에 읽는 테이블의 칼럼에만 필요함.
• MySQL 옵티마이저도 조인되는 두 테이블에 있는 각 칼럼에서 인덱스를 조사하고, 인덱스가 없는 테이블이 있으면, 그 테이블을 먼저 읽어서 조인을 실행함.
  • 뒤에 읽는 테이블(드리븐 테이블)은 검색 위주로 사용되기 때문에 인덱스가 없으면 성능에 미치는 여향이 매우 크기 때문임.
• 조인 수행시, 드리븐 테이블의 조인 칼럼에 적절한 인덱스가 있다면 아무런 문제가 되지 않음.
  • 하지만 드리븐 테이블에 검색을 위한 적절한 인덱스가 없다면 MySQL 서버는 블록 네스티드 루프 조인이나 해시 조인을 사용함.
    • 이 조인을 사용하면 MySQL 서버는 조인 버퍼를 사용함.
      • Extra 칼럼에 “Using join buffer” 라는 메시지가 표시됨.
• join_buffer_size 시스템 변수에 최대로 할당 가능한 조인 버퍼 크기를 설정할 수 있음.
  • 조인되는 칼럼에 인덱스가 적절히 준비되어 있다면 조인 버퍼는 크게 고려하지 않아도 됨.
  • 만약 그렇지 않다면 조인 버퍼를 너무 부족하게, 혹은 너무 과다하게 사용되지 않도록 적절히 설정하는게 좋음.
    • 일반적인 온라인 웹 서비스용 MySQL 서버라면 조인 버퍼 크기는 1MB 정도도 충분함.
    • 8.0 버전부터 해시 조인이 도입됐는데, 해시 조인 또한 조인 버퍼를 이용하도록 구현됨.
    • 데이터 웨어하우스 처럼 대용량 쿼리를 실행해야 한다면 조인 버퍼를 더 크게 설정하는 것이 좋음.
      • ex) 조인 조건이 없는 카테시안 조인을 수행하는 쿼리는 항상 조인 버퍼를 사용함.