[Real MySQL] 옵티마이저와 힌트 - 2

Real MySQL 8.0 전면 개정판을 읽으며 정리한 내용입니다.

쿼리 튜닝의 Base는 내가 실행한 쿼리가 어떻게 처리되는지 아는 것이다!

✔️ GROUP BY 처리

버퍼링 방식으로 쿼리를 처리해야 한다.

GROUP BY에 대한 결과물을 필터링하기 위해서 HAVING 절을 사용한다.
이때 GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로, HAVING절 튜닝을 위한 인덱스 생성 등은 필요 X

• 인덱스 스캔을 이용한 GROUP BY(타이트 인덱스 스캔)
  • 조인의 드라이빙 테이블에 속한 칼럼만을 이용해 그루핑 하는 경우에 GROUP BY 칼럼으로
    이미 인덱스가 있는 경우 사용 가능한 방법
• 루스 인덱스 스캔을 이용한 GROUP BY
  • 단일 테이블에 대해 수행되는 GROUP BY 처리에서만 사용 가능한 방법
  • 실행 계획에서 Using index for group-by 표시
  • 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능 향상
• 임시 테이블을 사용하는 GROUP BY
  • 인덱스를 전혀 사용하지 못할 때 사용하는 방법
  • 실행 계획에서 Using temporary 표시
  • GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복제거와 집합 함수 연산을 수행한다.
    

    ---

✔️ DISTINCT 처리

특정 칼럼의 유니크한 값 조회를 위해서, SELECT 쿼리에 사용

MIN(), MAX(), COUNT()와 같은 집합 함수와 사용되는 경우와 아닌 경우의 DISTINCT 키워드의 영향 범위가 달라진다.

• 단순 DISTINCT(집합 함수 없는 DISTINC)
  • GROUP BY와 동일한 방식으로 처리됨.(특히 MySQL 8.0부터)
  • 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다. (특정 칼럼만 유니크하게 조회하는 것 X)
• 집합 함수와 함께 사용된 DISTINCT
  • 집합 함수의 인자로 전달된 칼럼 값이 유니크한 것들을 가져온다.
  • 인덱스를 사용하지 못할 때에는 임시 테이블이 항상 필요하지만, 실행 계획의 별도의 ‘Using temporary’ 가 출력 되지는 않는다.
    
    

    ---

✔️ 내부 임시 테이블 활용

• 정의
  • MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑 할때 내부적으로 만드는 임시 테이블
• 특징
  • 처음에는 메모리에 생성되었다가, 크기가 커지면 디스크로 이동 (예외 케이스는 존재)
  • 다른 세션이나 다른 쿼리에서 보는 것과 사용하는 것이 불가
• 메모리 임시 테이블과 디스크 임시 테이블
  • 임시 테이블이 메모리 or 디스크에 생성됐는지는 MySQL 서버의 상태 변수 통해 확인
    이때 FLUSH STATUS 명령어를 통해 현재 세션의 상태 값을 초기화!
    

    SHOW SESSION STATUS LIKE 'Created_tmp%';​

  • MySQL 8.0 이전
    • 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리 엔진, 디스크에 저장될 때는 MyISAM 스토리 엔진 사용
    • MEMORY 스토리 엔진은 가변 길이 타입 지원 X → 메모리 낭비 MyISAM 스토리 엔진은 트랜잭션을 지원 X
  • MySQL 8.0 이후
    • 메모리를 사용할 때는 TempTable 스토리 엔진, 디스크에 저장될때는 InnoDB 스토리엔진 (기본값)
    • TempTable 스토리 엔진은 가변 길이 타입 지원 InnoDB 스토리 엔진은 트랜잭션을 지원
  • 임시 테이블이 디스크에 생성되는 경우
    • UNION이나 UNION ALL로 SELECT 되는 칼럼 중에서 512바이트 이상인 크기의 칼럼이 있는 경우
    • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
    • 메모리 임시 테이블의 크기가(MEMORY 스토리 엔진에서) tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리 엔진에서) temptable_max_ram 시스템 변수 값 보다 큰 경우