[MySQL] GROUP BY Optimization

[MySQL] GROUP BY Optimization 

MySQL은 GROUP BY 사용 시 대개는 전체 대상 테이블을 스캔한 후 새로운 임시 테이블을 만든 후 사용을 하도록 되어 있다. 하지만 인덱스 접근이 가능하다면 임시 테이블 생성을 피할 수 있다.

GROUP BY에 가장 중요한 인덱스 사용 조건은 컬럼들이 호출한 쿼리 순서대로 저장하고 있는지 이다. 인덱스 사용에 성공한다면, 두가지의 방법이 있다.

• Loose Index Scan: 모든 범위 조건과 함께 그룹화하는 방법.
• Tight Index Scan: 범위 스캔 후 결과를 그룹화하는 방법.

Loose Index Scan

테이블 t1(컬럼 a, b, c, d, e)이 인덱스로 (a, b, c) 를 가지고 있다고 가정해 보겠다.

인덱스 사용 가능

• GROUP BY a, b
• MIN(), MAX() 같은 SELECT 집계 함수 대상 컬럼이 인덱스 있는 경우 
  •  AVG와 SUM은 Single argument 경우, COUNT는 하나 이상 지원한다.

SELECT a, b FROM t1 GROUP BY a, b;
SELECT DISTINCT a, b FROM t1;

SELECT a, MIN(b) FROM t1 GROUP BY a;
SELECT MAX(c), MIN(c), a, b FROM t1 WHERE b > const GROUP BY a, b;

SELECT b FROM t1 WHERE a < const GROUP BY a, b;
SELECT a, b FROM t1 WHERE a < const GROUP BY a, b;
SELECT a, b FROM t1 WHERE c = const GROUP BY a, b;

인덱스 사용 불가능

• GROUP BY b, c
• GROUP BY a, b, d

# MIN(), MAX() 집계 함수가 아니라 
SELECT a, SUM(b) FROM t1 GROUP BY a;

# 가장 처음 시작하는 컬럼으로 GROUP BY를 하지 않음 (애초에 오류 발생 가능한 쿼리임)
SELECT a, b FROM t1 GROUP BY b, c;

# SELECT 컬럼과 GROUP BY 컬럼이 일치하지 않음 (애초에 오류 발생 가능한 쿼리임)
SELECT c1, c3 FROM t1 GROUP BY c1, c2;

Tight Index Scan

Tight Index Scan은 쿼리 조건에 따라서 full index scan 혹은 range index scan으로 처리될 수 있다.

쿼리가 Loose Index Scan으로 아직 처리 되지 않았을 때 아직 임시 테이블을 피할 수 있는 경우가 있다. 
만약, Where 절에 range 조건이 있다면 조건에 만족하는 키들만 읽어올 것이고 그렇지 않다면 index scan을 수행할 수 있다.
인덱스를 사용하지 않는 경우의 이유는 이 방법은 Where 절에 범위를 확인하기 위해 모든 키를 읽어야 하기 때문이다.

인덱스 사용 가능

# GROUP BY에 컬럼이 누락 되었지만, CONDITION으로 b가 있어서 충족이 된다.
SELECT a, b, c FROM t1 WHERE b = 'xxx' GROUP BY a, b;

인덱스 사용 

# 이 경우는 인덱스 첫 컬럼으로 시작하지 않기에 조건에 충족하지 못한다.
SELECT a, b, c FROM t1 WHERE a = 'xxx' GROUP BY b, c;

Ref

https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html

MySQL :: MySQL 8.0 Reference Manual :: 8.2.1.17 GROUP BY Optimization