[MySQL] 옵티마이저 GROUP BY 처리 방식

기존의 Real MySQL (5.0, 5.1 버전) 책을 너무 유익하게 봤는데, Real MySQL 8.0이 전면 개정판이 나와서... 설레는 마음에 보면서 개인적인 공부용으로 정리하고 있습니다.
역시 믿고 보는 Real MySQL... 👍 한번 사서 보시는 것을 강력 추천드립니다!

Group BY 처리

---

• GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍 된 처리를 할 수 없게 하는 처리 중 하나이다.
• GROUP BY 절이 있는 쿼리에서는 필터링 역할을 하는 HAVING 절을 사용할 수 있는데, GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

인덱스 스캔을 이용하는 GROUP BY (타이트 인덱스 스캔)

ORDER BY의 경우와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑 할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

• GROUP BY가 인덱스르 사용해서 처리된다 하더라도 그룹 함수 등의 그룹 값을 처리해야 해서 임시 테이블이 필요할 때도 있다.

루스 인덱스 스캔을 이용하는 GROUP BY

• 루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
• 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 칼럼에 Using index for group-by 코멘트가 표시된다.
• 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.
• 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다.

루스 인덱스 스캔을 사용할 수 없는 쿼리 패턴

SELECT co1, SUM(co2), FROM tb_test GROUP BY co1; 
# MIN(), MAX() 이외의 집합 함수가 사용되서 루스 인덱스 스캔은 사용 불가

SELECT co1, col2 FROM tb_test GROUP BY col2, col3;
# GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않기 때문에 사용 불가

SELECT co1, co3 FROM tb_test GROUP BY col1, col2;
# SELECT 절의 칼럼이 GROUP BY와 일치하지 않기 때문에 사용 불가

임시 테이블을 사용하는 GROUP BY

• 인덱스를 전혀 사용할 수 없는 GROUP BY로, 실행 계획의 Extra 칼럼에 Using temporary 메시지가 표기된다.

ORDER BY NULL

• MySQL 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 함께 수행했다.
  • 그래서 정렬이 필요하지 않은 경우라면 ORDER BY NULL을 추가로 사용할 것을 권장했다. (불필요한 추가 정렬 작업을 수행하지 않아 성능 향상)
• MySQL 8.0부터는 묵시적인 정렬은 더 이상 실행되지 않게 바뀌었다.

DISTINCT 처리

---

SELECT DISTINCT...

SELECT DISTINCT ... FROM ...

단순 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다.

• 이 경우에는 GROUP BY와 동일한 방식으로 처리된다. (내부적으로 같은 작업으로 처리된다)

주의할 것은 DISTINCT는 SELECT 하는 레코드를 유니크하게 SELECT 하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아니다.

집합 함수와 함께 사용된 DISTINCT

SELECT COUNT(DISTINCT s.salary) FROM ...

COUNT(), MIN(), MAX()와 같은 집합 함수 내에서 DISTINCT가 사용되는 경우, 그 집합 함수의 인자로 전달된 칼럼 값이 유니크한 것들을 가져온다.

• 쿼리의 실행 계획에서 DISTINCT가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다.
• 하지만 실행 계획의 Extra 칼럼에는 Using temporary 메시지가 출력되지 않는다. (버그 같음)

내부 임시 테이블 활용

---

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드들을 정렬하거나 그루핑 할 때는 내부적인 임시 테이블을 사용한다. (CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과 다르다)

• 내부적인 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다.
• 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

MySQL 8.0 이전 버전까지는 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용한다.

• MySQL 8.0 버전부터는 메모리는 기본값으로 TempTable이라는 스토리지 엔진을 사용하며, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됐다.
  • MEMORY 테이블은 VARBINARY, VARCHAR 등 가변 길이 타입을 지원하지 못해 메모리 낭비가 심해지는 문제점이 존재함. → TempTable 스토리지 엔진은 가변 길이 타입을 지원.
  • MyISAM 테이블은 트랜잭션을 지원하지 못하는 문제. ⇒ InnoDB 스토리지 엔진은 트랜잭션을 지원

임시 테이블의 크기가 1GB보다 커지는 경우 MySQL 서버는 메모리의 임시 테이블을 디스크로 기록한다. (크기는 temptable_max_ram 시스템 변수로 변경 가능)

• 이때 두 가지 방식 중 하나를 선택한다.
  • MMAP 파일로 디스크에 기록
  • InnoDB 테이블로 기록

임시 테이블이 필요한 쿼리

• ORDER BY, GROUP BY에 명시된 칼럼이 다른 쿼리
• ORDER BY, GROUP BY에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• DISTINCT나 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION, UNION DISTINCT가 사용된 쿼리 (UNION ALL을 사용하는 쿼리는 MySQL 8.0부터는 임시 테이블을 사용하지 않도록 개선되었다)
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra 칼럼에 Using temporary라는 메시지가 표시되는지 확인하면 된다.

임시 테이블이 디스크에 생성되는 경우

• UNION, UNION ALL에서 SELECT 되는 칼럼 중에서 길이가 512바이트 이상의 크기의 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상의 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 지정된 시스템 변수 값보다 큰 경우