Featured image of post 11.4 쿼리 작성 및 최적화 - SELECT (2)
Real MySQL

11.4 쿼리 작성 및 최적화 - SELECT (2)

Real MySQL 8.0

GROUP BY

GROUP BY는 특정 컬럼 값으로 레코드를 그루핑하고, 그룹별로 집계된 결과를 하나의 레코드로 조회할 때 사용한다.

WITH ROLLUP

GROUP BY가 사용된 쿼리에서는 그루핑된 그룹별로 소계를 가져올 수 있는 롤업(ROLLUP) 기능을 사용할 수 있는데, ROLLUP으로 출력되는 소계는 단순히 최종 합만 가져오는 것이 아니라 사용된 컬럼의 개수에 따라 소계의 레벨이 달라진다.

MySQL의 GROUP BY ... ROLLUP 쿼리는 엑셀의 피벗 테이블과 거의 동일한 기능이며, 결과의 소계 레코드의 컬럼값은 항상 NULL로 표현된다.

컬럼 1개

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21

SELECT dept_no, COUNT(*)
FROM dept_emp
GROUP BY dept_no WITH ROLLUP
;

/*
+---------+----------+
| dept_no | COUNT(*) |
+---------+----------+
| d001    |    20211 |
| d002    |    17346 |
| d003    |    17786 |
| d004    |    73485 |
| d005    |    85707 |
| d006    |    20117 |
| d007    |    52245 |
| d008    |    21126 |
| d009    |    23580 |
| NULL    |   331603 |
+---------+----------+
*/

컬럼 1개 이상

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18

SELECT first_name, last_name, COUNT(*)
FROM employees
GROUP BY first_name, last_name WITH ROLLUP
;

/*
+------------+-----------+----------+
| first_name | last_name | COUNT(*) |
+------------+-----------+----------+
| Aamer      | Anger     |        1 |
| Aamer      | ...       |      ... |
| Aamer      | NULL      |      228 |
| Zvonko     | Aamodt    |        1 |
| Zvonko     | ...       |      ... |
| Zvonko     | NULL      |      258 |
| NULL       | NULL      |   300024 |
+------------+-----------+----------+
*/

MySQL 8.0 버전부터는 그룹 레코드에 표시되는 NULL을 GROUPING() 함수를 통해 변경할 수 있다.

레코드를 컬럼으로 변환해서 조회

GROUP BY나 집합 합수를 통해 레코드를 그루핑할 수 있지만 하나의 레코드를 여러 개의 컬럼으로 나누거나 변환하는 SQL 문법은 없다.

하지만 SUM(), COUNT() 같은 집합 함수와 CASE WHEN ... END 구분을 이용해 레코드를 컬럼으로 변환하거나 하나의 컬럼을 조건으로 구분하여 2개 이상의 컬럼으로 변환하는 것은 가능하다.

레코드르 컬럼으로 변환

GROUP BY의 결과를 SUM(CASE WHEN ...) 구분을 사용해 한 번 더 변환하면 레코드를 컬럼으로 변환할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    SUM(CASE WHEN dept_no='1001' THEN emp_count ELSE 0 END) AS count_d001
    , ...
    SUM(CASE WHEN dept_no='1001' THEN emp_count ELSE 0 END) AS count_d009
FROM (
    SELECT dept_no, COUNT(*) AS emp_count 
    FROM dept_emp 
    GROUP BY dept_no
) tb_dervied
;

이처럼 레코드를 컬럼으로 변환하는 작업을 할 때는 목적이나 용도에 맞게 집합 함수를 사용하면 된다.

이 예제는 부서 번호가 쿼리의 일부로 사용되기 때문에 변경되거나 추가되면 쿼리를 변경해야 하지만, 이런 부분은 동적 쿼리 등으로 보완할 수 있다.

하나의 컬럼을 여러 컬럼으로 분리

SUM(CASE WHEN ...) 문장은 소그룹을 특정 조건으로 나눠 수를 구하는 용도로도 사용할 수 있다.

ORDER BY

ORDER BY는 검색된 레코드를 어떤 순서로 정렬할 지 결정한다. ORDER BY 절이 사용되지 않으면 아래 규칙에 맞춰 정렬된다.

  • 인덱스를 사용한 SELECT
    • 인덱스에 정렬된 순서대로 레코드를 가져온다.
  • 풀 테이블 스캔을 실행하는 SELECT
    • MyISAM: 테이블에 저장된 순서대로 가져온다.
    • InnoDB: 프라이머리 키로 클러스터링 되어있기 때문에 프라이머리 키 순서대로 레코드를 가져온다.
  • 임시 테이블로 처리되는 SELECT
    • 조회되는 레코드의 순서를 예측하기 어렵다.

ORDER BY절이 없는 SELECT 쿼리 결과의 순서는 처리 절차에 따라 달라질 수 있으며, 어떤 DBMS도 ORDER BY 절이 명시되지 않은 쿼리에 대해 정렬을 보장하지 않는다. 따라서 정렬이 필요한 곳에서는 ORDER BY 절을 사용해야한다.

인덱스를 사용하지 못하는 ORDER BY

ORDER BY에서 인덱스를 사용하지 못할 때는 추가 정렬 작업이 수행되며, 쿼리 실행 계획에 있는 Extra 컬럼에 “Using Filesort” 코멘트가 노출된다.

  • “filesort"의 “file"은 디스크의 파일을 이용해 정렬을 수행한다는 의미가 아닌 MySQL 서버가 명시적으로 정렬 알고리즘을 수행했다는 의미이다.

정렬 대상이 많은 경우 여러 부분으로 나눠서 처리하는데, 정렬된 결과를 임시로 디스크나 메모리에 저장해 둔다.

  • 실제로 메모리만 이용해 정렬이 수행되었는지는 MySQL 서버 상태값을 확인해보면 알 수 있다.

ORDER BY 사용법 및 주의사항

ORDER BY 절은 1개 이상의 컬럼으로 정렬을 수행할 수 있으며, 정렬 순서는 컬럼별로 다르게 명시할 수 있다.

일반적으로 정렬할 대상은 컬럼명이나 표현식으로 명시하지만, SELECT되는 컬럼의 순번을 명시할 수도 있다.

  • 이러한 경우 숫자가 아닌 문자열을 사용할 경우 ORDER BY 절 자체가 무시된다.
  • 컬럼 명이라고 하더라도 문자 리터럴이 사용된다면 무시된다.

여러 방향으로 동시 정렬

MySQL 8.0 버전부터는 오름차순과 내림차순을 혼용해서 인덱스를 생성할 수 있게 개선되었다.

1

ALTER TABLE salaries ADD INDEX ix_salary_fromdate (salary DESC, from_date ASC);

응용 프로그램에서 예시 테이블을 내림차순으로만 조회하는 경우 2개의 인덱스 중 하나만 있어도 옵티마이저는 적절히 인덱스를 이용하여 정렬할 수 있게 최적화가 가능하다.

1
2
3
4
5

SELECT *
FROM salaries
ORDER BY salary
DESC LIMIT 10
;

1
2

ALTER TABLE salaries ADD INDEX ix_salary_asc (salary ASC);
ALTER TABLE salaries ADD INDEX ix_salary_desc (salary DESC);

하지만 쿼리가 내림차순으로만 레코드를 정렬해서 가져간다면 인덱스는 당연히 DESC를 생성하는 것이 좋다.

함수나 표현식을 이용한 정렬

하나 이상의 컬럼 연산 결과를 이용한 정렬도 가능하다.

MySQL 8.0 이전까지는 연산 결과를 기준으로 정렬하기 위해서 가상 컬럼(Virtual Column)을 추가하고 인덱스를 생성하는 방법을 사용해야 했지만, 함수 기반의 인덱스를 지원하기 시작했다.