[MySQL] MySQL group by 개선

개요

서비스 관련 쿼리를 개선하면서 MySQL에만 사용되는 기능인 index for group by를 적용하게 되어 작성합니다.

사용되는 쿼리와 테이블은 다음과 같은 형식입니다

테이블

CREATE TABLE `tableA` (
  `id` int NOT NULL AUTO_INCREMENT,
  `strDepth1Category` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `strDepth2Category` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `strDepth3Category` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `strServiceName` varchar(100) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `dtCreatedAt` datetime NOT NULL,
  `dtUpdatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `table_index_01` (`strServiceName`,`strDepth1Category`,`strDepth2Category`,`strDepth3Category`)
) ENGINE=InnoDB AUTO_INCREMENT=62145281 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

쿼리

 SELECT `strDepth2Category` AS `strDepth2Category`
FROM `tableA` 
WHERE `strServiceName` = 'abc' AND `strDepth1Category` IN ('abc', 'def') 
GROUP BY `strDepth2Category`;

가장 깊은 depth 의 category 유니크 값을 조회하기 위한 내용이며 상단의 조건들은 필수 값으로 입력을 받도록 설계하였습니다.

table_index_01 (`strServiceName`,`strDepth1Category`,`strDepth2Category`,`strDepth3Category`) 형태의 인덱스로 where 구문과 group by 구문에서 인덱스 사용 용도를 충족한다고 판단하였고 위 쿼리는 다음과 같은 실행계획으로 풀이됩니다.

 

쿼리 개선 이전

IN안에 하나의 조건만 들어올 경우

-> Group (no aggregates)  (cost=47.12 rows=193) (actual time=0.170..0.171 rows=1 loops=1)
    -> Covering index lookup on tableA using table_index_01(strServiceName='abc', strDepth1Category='abc')  (cost=27.82 rows=193) (actual time=0.031..0.147 rows=193 loops=1)

-> Group (no aggregates)  (cost=155307.21 rows=639014) (actual time=144.157..282.805 rows=5 loops=1)
    -> Covering index lookup on tableA using table_index_01 (strServiceName='abc', strDepth1Category='def')  
    (cost=91405.81 rows=639014) (actual time=0.028..240.382 rows=443627 loops=1)

 

IN안에 여러 조건이 들어올 경우

-> Table scan on <temporary>  (cost=0.01..7987.96 rows=638837) (actual time=0.003..0.004 rows=5 loops=1)
    -> Temporary table with deduplication  (cost=219154.33..227142.28 rows=638837) (actual time=574.515..574.516 rows=5 loops=1)
        -> Filter: ((tableA.strServiceName = 'abc') and (tableA.strDepth1Category in ('abc','def')))  (cost=155270.61 rows=638837) (actual time=0.033..419.354 rows=443810 loops=1)
            -> Covering index range scan on tableA  using table_index_01over (strServiceName = 'abc' AND strDepth1Category = 'def') OR (strServiceName = 'abc' AND strDepth1Category = 'abc')  (cost=155270.61 rows=638837) (actual time=0.031..269.346 rows=443810 loops=1)

 

개선 방안

커버링 인덱스를 사용하여 조회가 되지만 의도했던 부분은 인덱스의 정렬된 데이터에서 group by에서 사용되어야 하는 하나의 데이터를 읽으면 그 다음 데이터로 바로 넘어가는 상황을 기대했습니다. 그런 것에 비하여 rows 조회 양이 많았고 지연 시간도 늘어납니다.

관련하여 group by index 사용에 대하여 최적화를 좀 더 찾아보았을 때 관련 내용을 확인하였습니다.

https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html 해당 페이지를 확인하면 group by 를 성능 개선하는데 2가지 방안이 있습니다.

1 Loose Index scan : 특정 조건을 만족하면 데이터의 일부만 읽고 스킵하여 집계를 합니다.

2 Tight Index Scan : 쿼리 조건에 따라 인덱스 전체 스캔 또는 범위 스캔이 발생합니다. 임시 테이블 생성을 피할 수 있으며 범위 조건을 충족하는 모든 키를 읽은 후 집계를 합니다.

 

t1 테이블에 (c1,c2,c3) 의 복합 인덱스가 있다는 가정하에 

1 Loose Index scan은 다음과 같은 쿼리들에서 적용이 가능합니다.

SELECT c1, c2 FROM t1 GROUP BY c1, c2;
SELECT DISTINCT c1, c2 FROM t1;
SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
SELECT c1, c3 FROM t1 WHERE c3 = cosnt GROUP BY c1, c2;
SELECT COUNT(DISTINCT c1), SUM(DISTINCT c1) FROM t1;
SELECT COUNT(DISTINCT c1, c2), COUNT(DISTINCT c2, c1) FROM t1;

GROUP BY에 적용이 인덱스 순서에 맞게 컬럼이 작성되어 있습니다.

AVG(DISTINCT COLUMN), SUM(DISTINCT COLUMN), COUNT(DISTINCT COLUMN1, COLUMN2 ...) 도 사용이 가능하며 count는 두개 이상의 컬럼을 사용할 수 있지만 AVG, SUM은 하나의 컬럼을 사용해야 합니다.

 

2 Tight Index scan은 다음과 같은 쿼리들에서 적용이 가능합니다.

SELECT c1, c2, c3 FROM t1 WHERE c2 = 'a' GROUP BY c1, c3;
SELECT c1, c2, c3 FROM t1 WHERE c1 = 'a' GROUP BY c2, c3;

WHERE, GROUP BY 를 합쳐서 순서에 맞게 작성이 되어 있습니다.

 

개선

Loose Index Scan을 적용할 수 있는 상황으로 판단되어 쿼리를 수정하였습니다. 

SELECT distinct `strDepth2Category` AS `strDepth2Category`
FROM `tableA` 
WHERE `strServiceName` = 'abc' AND `strDepth1Category` 
IN ('def') 
GROUP BY strServiceName, strDepth1Category, `strDepth2Category`

 

위와 같은 쿼리 형태로 수정이 되었으며 개선 이후 성능이 많이 개선되는 것을 확인하였습니다. actual 기준  574.516 →  0.145 (ms)

개선된 실행 계획에는 다음과 같은 항목이 표시됩니다. using index for group by , index skip scan

쿼리 개선 이후

IN안에 하나의 조건만 들어올 경우

-> Sort with duplicate removal: tableA.strDepth2Category  (actual time=0.040..0.040 rows=0 loops=1)
    -> Table scan on <temporary>  (cost=2.51..2.51 rows=1) (actual time=0.002..0.002 rows=0 loops=1)
        -> Temporary table with deduplication  (cost=2.97..2.97 rows=1) (actual time=0.035..0.035 rows=0 loops=1)
            -> Covering index lookup on tableA using table_index_01(strServiceName='abc', strDepth1Category='abc'\'')  (cost=0.36 rows=1) (actual time=0.020..0.020 rows=0 loops=1)

 

-> Sort with duplicate removal: tableA .strDepth2Category  (actual time=0.131..0.131 rows=5 loops=1)
    -> Table scan on <temporary>  (cost=0.01..32.70 rows=2416) (actual time=0.002..0.003 rows=5 loops=1)
        -> Temporary table with deduplication  (cost=2433.91..2466.59 rows=2416) (actual time=0.113..0.114 rows=5 loops=1)
            -> Filter: (( tableA .strServiceName = 'abc') and ( tableA .strDepth1Category = 'def'))  (cost=2192.29 rows=2416) (actual time=0.041..0.086 rows=5 loops=1)
                -> Covering index skip scan for deduplication on tableA  using table_index_01over (strServiceName = 'abc' AND strDepth1Category = 'def')  (cost=2192.29 rows=2416) (actual time=0.038..0.082 rows=5 loops=1)

 

IN안에 여러 조건이 들어올 경우

-> Sort with duplicate removal: tableA .strDepth2Category  (actual time=0.145..0.145 rows=5 loops=1)
    -> Table scan on <temporary>  (cost=0.01..32.54 rows=2403) (actual time=0.002..0.003 rows=6 loops=1)
        -> Temporary table with deduplication  (cost=2422.69..2455.22 rows=2403) (actual time=0.129..0.131 rows=6 loops=1)
            -> Filter: ((tableA.strServiceName = 'abc') and ( tableA.strDepth1Category in ('abc','def')))  (cost=2182.38 rows=2403) (actual time=0.041..0.101 rows=6 loops=1)
                -> Covering index skip scan for deduplication on tableA using table_index_01 over (strServiceName = 'abc' AND strDepth1Category = 'def') OR (strServiceName = 'abc' AND strDepth1Category = 'abc')  (cost=2182.38 rows=2403) (actual time=0.039..0.097 rows=6 loops=1)