데이터베이스

[MySQL] Order By, Group By, Distinct

heyh0 2024. 1. 30. 16:29

MySQL 서버에 요청된 쿼리는 결과는 동일하지만 과정은 매우 다양하다.

여러 방법 중에서 최적의 방법은 옵티마이저가 담당한다.

대부분의 DBMS에서는 옵티마이저가 최적의 실행 계획을 수립하는 작업을 한다.

1. 쿼리 실행 절차와 옵티마이저의 종류

1-1. 쿼리 실행 절차

  1. 요청된 SQL 문장을 잘개 쪼개서 MySQL 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
  2. 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 어떤 인덱스로 테이블을 읽을지 선택한다.
  3. 두 번째 단계에서 결정된 읽기 순서나 인덱스를 활용해서 스토리지로부터 데이터를 가져온다.

첫 번째 단계를 SQL 파싱이라고 하며, SQL 파서라는 모듈로 처리한다.

문법이 잘못됐다면 여기서 걸러지고 아니라면 SQL 파스 트리를 생성한다.

MySQL 서버는 SQL 문장이 아닌 SQL 파스 트리로 쿼리를 실행한다.

 

두 번째 단계는 최적화 및 실행 계획 수립 단계이며, 아래와 같은 내용을 처리한다.

  • 불필요한 조건 제거 및 복잡한 연산 단순화
  • 여러 테이블의 조인이 있는 경우, 어떤 순서로 읽을지 결정
  • 각 테이블의 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스 결정
  • 가져온 레코드를 임시 테이블에 넣고 다시 가공해야 할지 결정

두 번째 단계는 옵티마이저가 처리하고 완료되면 세 번째 단계인 실행 계획이 만들어진다.

세 번째 단계는 실행 계획대로 스토리지 엔진에서 레코드를 읽어오고, 받은 레코드를 조인하거나 정렬한다.

1-2. 옵티마이저의 종류

옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 한다.

규칙 기반 최적화(Rule-Based Optimizer)
옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립한다.
그래서 같은 쿼리에 대해서는 거의 같은 실행 방법을 만들어 낸다.

비용 기반 최적화(Cost-Based Optimizer)
여러 가지 가능한 방법을 만들고 실행 계획별 비용을 산출한다.
최소 비용 처리 방식을 선택해서 쿼리를 실행한다.

RBO는 각 테이블이나 인덱스 통계 정보가 거의 없고 상대적으로 느린 CPU 연산 탓에 쓰이던 방식이다.

현재는 대부분의 RDBMS가 CBO를 채택해서 사용하고 있다.

2. 기본 데이터 처리

다음과 같은 경우, 옵티마이저는 풀 테이블 스캔을 선택한다.

  • 테이블의 레코드 건수가 너무 적은 경우
  • WHERE절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
  • 인덱스 레인지 스캔을 하더라도 읽어야 할 레코드 건수가 너무 많은 경우

풀 테이블 스캔을 하면 한 페이지씩 읽어 오는 것으로 생각한다.

MyISAM에서는 맞지만 InnoDB에서는 틀린 말이다.

스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드가 리드 어헤드 작업을 한다.

리드 어헤드
어떤 영역의 데이터가 앞으로 필요할 것이라고 예측하고 미리 InnoDB 버퍼 풀에 가져다 둔다.

즉, InnoDB에서는 풀테이블 스캔을 시작하면 처음에는 포그라운드 스레드로 페이지를 읽는다.

특정 시점 부터는 백그라운드 스레드가 리드 어헤드로 동작한다.

포그라운드 스레드는 버퍼 풀에 준비된 데이터를 읽기만 해서 쿼리 작업이 빨라진다.

 

리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에도 적용된다.

-- 풀 인덱스 스캔을 할 가능성이 높음
SELECT COUNT(*) FROM employees;
-- 풀 테이블 스캔
SELECT * FROM employees;

첫 번째 쿼리는 인덱스 조건이 없기 때문에 풀 테이블 스캔을 될 것 같지만 풀 인덱스 스캔을 할 확률이 높다.

레코드의 건수만 필요하다면 용량이 작은 인덱스를 선택하는 것이 디스크 I/O 횟수가 적기 때문이다.

두 번째 쿼리는 레코드에만 있는 칼럼이 필요하기 때문에 풀 테이블 스캔을 한다.

병렬 처리
innodb_parallel_read_threads라는 시스템 변수로 하나의 쿼리를 최대 몇 개의 스레드로 처리할지를 변경할 수 있다.

 

3. Order By

여러 개의 레코드를 조회하는 쿼리는 정렬을 필수적으로 사용한다.

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 방법을 사용한다.

아래와 같은 이유로 모든 정렬을 인덱스로 튜닝하는 것은 불가능하다.

  • 정렬 기준이 많아서 요건별로 모든 인덱스를 생성하는 것이 불가능한 경우
  • Group By의 결과 또는 Distinct 같은 처리의 결과를 정렬해야 하는 경우
  • Union의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우
  장점 단점
인덱스  인덱스가 미리 정렬돼 있어서 순서대로 읽기만 하면 되므로 빠르다. 추가, 수정, 삭제 작업 시 부가적인 인덱스 추가/삭제가 필요해서 느리다.
인덱스 때문에 디스크와 메모리가 더 필요하다.
Filesort 인덱스의 단점이 장점으로 바뀐다.
정렬할 레코드가 적으면 충분히 빠르다.		 레코드 정렬 대상이 많아질수록 쿼리의 응답 속도가 느리다.

 

3-1. 소트 버퍼

MySQL은 정렬을 수행할 때 별도의 메모리 공간을 할당받아서 사용하는데 그 공간이 소트 버퍼다.

버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 sort_buffer_size로 설정할 수 있다.

정렬해야 할 레코드가 소트 버퍼보다 크다면 여러 조각으로 나눠서 처리하고, 임시 저장을 위해 디스크를 사용한다.

각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 한다.

이 병합 작업을 멀티 머지라고 한다.

 

이 작업들은 모두 디스크 I/O를 유발하고, 레코드 건수가 많아질수록 반복 작업 횟수가 늘어난다.

소트 버퍼를 크게 설정하면 디스크를 작게 사용해서 더 빨리질 것 같지만 큰 차이는 없다.

오히려 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수도 있으니 주의하자.

3-2. 정렬 알고리즘

레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 정렬 모드가 나눠진다.

각각 투 패스 정렬 방식과 싱글 패스 정렬 방식이다.

<sort_key, rowid> : 정렬 키와 레코드의 row id만 가져와서 정렬하는 방식

<sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드 칼럼들은 고정 사이즈

<sort_key, packed_additional_fields> : 방식은 위와 동일, 레코드 칼럼들은 가변 사이즈

첫 번째 방식이 투 패스 정렬 방식이고 두 번째, 세 번째는 싱글 패스 정렬 방식이다.

SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;

Real MySQL 8.0 P.293

위 쿼리를 수행할 때 싱글 패스 방식은 필요하지 않은 last_name 칼럼까지 읽어서 정렬을 수행한다.

Real MySQL 8.0 P.294

투 패스 방식은 정렬 대상 칼럼과 PK인 emp_no, first_name 칼럼만 읽어서 정렬을 수행한다.

정렬이 완료되면 last_name 칼럼을 추가해서 결과를 반환한다.

투 패스 정렬 방식은 테이블을 두 번 읽어야 하고, 싱글 패스 정렬 방식은 소트 버퍼 공간이 많이 필요하다.

싱글 패스 정렬 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보인다.

투 패스 정렬 방식은 정렬 대상 레코드의 크기나 건수가 많은 경우 효율적이다.

SELECT (*) ... 방식의 쿼리는 정렬 버퍼를 비효율적으로 사용할 가능성이 높다.

꼭 필요한 칼럼만 조회하는 쿼리를 작성하자.

3-3. 인덱스를 사용한 정렬

정렬 처리 방법 실행 계획의 Extra 칼럼 내용
인덱스를 사용한 정렬 별도 표기 없음
조인에서 드라이빙 테이블만 정렬  Using filesort 메시지가 표시됨
조인에서 조인 결과를 임시 테이블로 저장 후 정렬 Using temporary; Using filesort 메시지가 표시됨

옵티마이저는 정렬 처리를 위해 인덱스를 사용할 수 있는지 확인한다.

인덱스를 사용할 수 있다면 별도의 Filesort 과정 없이 인덱스를 순서대로 읽어서 반환한다.

하지만 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 처리(Filesort)한다.

이때 옵티마이저는 정렬 대상 레코드를 최소화하기 위해 2가지 방법중 하나를 선택한다.

  • 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
  • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행
-- 인덱스를 이용한 정렬
SELECT * 
FROM employees, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.emp_no;

위 쿼리는 인덱스를 이용한 정렬을 수행한다.

아래 사항들을 충족해야 한다.

  • ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 포함돼야 한다.
  • ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
  • WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용해야 한다.

MySQL은 부가적으로 불필요한 정렬 작업을 수행하지 않으므로 ORDER BY는 명시하는 것이 좋다.

3-4. 조인에서 드라이빙 테이블만 정렬

-- 조인의 드라이빙 테이블만 정렬
SELECT * 
FROM employees, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.last_name;

위 쿼리는 조인에서 드라이빙 테이블만 검색해서 정렬하고, 결과를 다른 테이블과 조인한다.

ORDER BY 절에 명시된 칼럼은 employees 테이블의 PK와 전혀 연관이 없으므로 인덱스를 이용한 정렬은 불가능하다.

옵티마이저는 employees 테이블을 드라이빙 테이블로 선택한다.

인덱스를 이용해 레코드를 검색한다. → 검색 결과를 last_name 칼럼으로 정렬 수행 → salaries 테이블과 조인 수행

3-5. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬

-- 임시 테이블을 이용한 정렬
SELECT * 
FROM employees, salaries s
WHERE s.emp_no = e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY s.salary;

위 쿼리는 임시 테이블을 이용한 정렬이다.

2개 이상의 테이블을 조인해서 정렬한다면 임시 테이블이 필요할 수도 있다.

ORDER BY 절의 정렬 기준 칼럼이 드라이빙 테이블이 아니라 드리븐 테이블에 있는 칼럼이다.

즉 정렬을 수행하기 전에 salaries 테이블을 읽어야 하므로 조인된 데이터를 가지고 정렬을 수행해야 한다.

스트리밍 방식과 버퍼링 방식
스트리밍 방식은 일치하는 레코드가 검색될 때마다 바로 클라이언트에 전송한다.
버퍼링 방식은 결과를 모아서 일괄 가공한 후에 제공되기 때문에 기다려야 한다.
ORDER BY와 GROUP BY는 버퍼링 방식으로 동작하기 때문에 LIMIT를 함께 사용해도 성능 차이가 거의 없다.

4. Group By

Group By 절이 있는 쿼리에서는 Having 절을 사용할 수 있다.

Having은 Group By 결과에 대해 필터링 역할을 수행한다.

Group By 작업에서 인덱스를 사용하는 경우는 인덱스 스캔 방법과 루스 인덱스 스캔 방법으로 나뉜다.

인덱스를 사용하지 못하면 임시 테이블을 사용한다.

4-1. 인덱스 스캔을 이용

Order By와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 인덱스를 읽는다.

읽은 인덱스로 그루핑 작업을 수행하고 그 결과로 조인을 처리한다.

4-2. 루스 인덱스 스캔을 이용

SELECT emp_no 
FROM salaries
WHERE from_date = '1985-03-01'
GROUP BY emp_no;

salaries의 인덱스는 (emp_no, from_date)로 생성돼 있다.

위의 쿼리 문장에서 WHERE 조건은 인덱스 레인지 스캔 방식으로 접근이 불가능하다.

쿼리가 처리되는 과정을 살펴보면 

  1. 인덱스를 스캔하면서 유니크한 첫 번째 키 값을 찾아낸다.
  2. from_date가 '1985-03-01'인 레코드만 가져온다.
  3. 1, 2번 과정을 반복하다가 유니크한 키 값이 더이상 없으면 결과를 반환한다.

루스 인덱스 스캔은 단일 테이블을 Group By로 처리할 때만 사용할 수 있다.

유니크한 값의 수가 많으면 인덱스 레인지 스캔 적으면 루스 인덱스 스캔의 성능이 좋다.

즉, 루스 인덱스 스캔은 분포도가 좋지 않은 인덱스일수록 더 빠른 결과를 만들어낸다.

4-3. 임시 테이블을 이용

SELECT e.last_name, AVG(s.salary)
FROM employees e, salaries s
WHERE s.emp_no = e.emp_no
GROUP BY e.last_name;

 

위 쿼리처럼 인덱스를 사용하지 못하면 임시 테이블을 사용해서 처리된다.

조인의 결과를 한 건씩 가져오고 중복 체크를 하면서 임시 테이블을 생성할 때, 별도의 정렬 작업 없이 처리된다.

Order By를 추가된다면 정렬 작업을 하지만 Group By만 사용한다면 별도의 Filesort가 일어나지 않는다.

5. Distinct

특정 칼럼의 유니크한 값을 조회하려면 Select 절에 Distinct를 사용한다.

Distinct는 Min(), Max(), Count() 같은 집합 함수와 함께 사용되는 경우와 사용하지 않는 경우로 나뉜다.

Distinct도 인덱스를 통해 처리하지 못하면 임시 테이블을 사용한다.

5-1. Select Distinct ...

유니크한 레코드만 가져오고자 하면 Select Distinct ... 형태의 쿼리 문장을 사용한다.

이 쿼리 문장은 Group By와 동일한 방식으로 처리된다.

SELECT DISTINCT emp_no FROM salaries;
SELECT emp_no FROM salaries GROUP BY emp_no;

-- (first_name, last_name)의 유니크한 쌍을 조회한다.
SELECT DISTINCT first_name, last_name FROM employees;

5-2. 집합 함수와 함께 사용된 Distinct

-- 인덱스를 사용하지 못함
SELECT COUNT(DISTINCT s.salary),
	COUNT(DISTINCT e.last_name)
FROM employees e, salaries s
WHERE e.emp_no = s.emp.no
AND e.emp_no BETWEEN 100001 AND 100100;

-- 인덱스를 사용함
SELECT COUNT(DISTINCT emp_no) FROM employees;
SELECT COUNT(DISTINCT emp_no) FROM dept_no GROUP BY dept_no;

첫 번째 쿼리는 COUNT() 함수를 두 번 사용하고 인덱스를 이용하지 못해서 2개의 임시 테이블을 생성한다.

나머지 쿼리는 인덱스 풀 스캔이나 레인지 스캔을 해서 임시 테이블 없이 최적화된 처리를 수행한다.

-- 유니크한 이름, 성 쌍을 가져옴
SELECT DISTINCT first_name, last_name
FROM employees;

-- 유니크한 이름, 성 각각의 개수를 가져옴
SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name)
FROM employees;

-- 유니크한 이름, 성 쌍의 개수를 가져옴
SELECT COUNT(DISTINCT first_name last_name)
FROM employees;