CS/DB

[Real MySQL 8.0]9장 - 옵티마이저

빙응이 2024. 12. 15. 21:19

 

MySQL 서버로 요청된 쿼리는 결과가 동일하지만 내부적으로 결과를 만들어내는 방법은 다양하다.
쿼리를 최적 실행을 위한 옵티마이저에 대해 알아보자

 

📝9.1 개요

어떤 DBMS든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분이다. 하지만 실행 계획을 이해할 수 잇어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

 

🧷9.1.1 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다.

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

첫 번째 단계를 SQL 파싱이라 하며 SQL 파서에서 진행된다.

SQL 문장이 문접적으로 잘못됐다면 이 단계에서 걸러진다. 또한 이 단계에서 SQL 파스 트리가 만들어진다.

 

2. 옵티마이저

두 번째 단계는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리한다.

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

대표적인 작업은 이것들이다. 두 번째 단계는 최적화 및 실행 계획 수립 단계이며, MySQL 서버의 옵티마이저가 처리한다.

 

3.  MySQL 엔진 & 스토리지 엔진

세 번째 단계는 수립된 실행 계획을 토대로 스토리지 엔진에 레코드를 읽어오고 MySQL 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.

 

🧷9.1.2 옵티마이저의 종류

옵티마이저의 종류는 현재 대부분의 DBMS가 선택하는 비용 기반 최적화(Cost-based optimizer, CBO)와 초기 버전 오라클 DBMS에서 사용하던 규칙 기반 최적화(Rule-based optimizer, RBO)가 있다. 

  • 규칙 기반 최적화는 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저의 내장된 우선순위에 따라 수립하는 방식이다. 하지만 데이터를 규칙만으로 정의할 수 없기에 지금은 사용하지 않는다.
  • 비용 기반 최적화는 쿼리를 처리하기 위한 여러 가지 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용하여 실행 계획을 산출한다.

 

📝9.2 기본 데이터 처리 

🧷9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

인덱스 처리에 대한 자세한 내용은 [Real MySQL 8.0]8장 을 거쳐왔기 때문에 간단하게만 알아보자 

  • 풀 테이블 스캔
    • 정의 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미
    • MySQL 옵티마의저의 선택 조건
      1. 테이블의 레코드 건수가 너무 작아서 인덱스를 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우
      2. WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
      3. 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
리드 어헤드

일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요하다.

InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지를 읽히면 백그라운드 스레드에 의해 리드 어헤드 작업이 자동으로 시작된다.

  • 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에 읽어 버퍼 풀에 가져다 두는 것을 의미

즉, 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 클라이언트 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다. 백그라운드 스레드가 읽기를 넘겨받은 시점부터는 한번에 4개 또는 8개씩 페이지를 읽으면서 그 수를 증가시키며 이때 한번에 최대 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장한다.

이렇게 클라이언트 스레드는 버퍼풀에 있는 데이터를 가져다 사용하기만 하면 되므로 쿼리가 빨라진다.

 

🧷9.2.2 병렬 처리

여기서 설명하는 병렬 처리는 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리한다는 것을 의미한다.

실제로 MySQL 8.0에서는 시스템 변슈를 이용해 하나의 쿼리에 몇개의 스레드가 사용될 지 결정할 수 있다. 늘어날수록 속도는 빨라지지만 그만큼 스레드를 사용하는 것이기에 오히려 성능이 떨어질 수도 있다.

 

🧷9.2.3 ORDER BY 처리

대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다.

정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 Filesort라는 별도의 이용 방법으로 나눌 수 있다.

  장점 단점
인덱스  INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어 순서대로 읽기만 하면 되므로 매우 빠름 INSERT, UPDATE, DELETE 작업 시 부가 인덱스 추가/삭제 작업이 필요하여 느림
인덱스 때문에 디스크 공간이 더 많이 필요
인덱스 개수가 늘어날 수록 버퍼 풀을 위한 메모리도 증가
Filesort 인덱스를 생성하지 않아도 되므로 인덱스의 단점이 장점
정렬해야 할 레코드가 많지 않으면 메모리에서 처리되므로 충분히 빠름		 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 느림

 

물론 모든 정렬을 항상 Filesort를 사용하는 것은 아니며 인덱스 또한 모든 정렬에 대해 튜닝하기란 불가능하다.

Filesort를 사용하는 상황을 가정해보자

  • 정렬 기준이 너무 많아서 요건별로 모두 인덱스 생성이 불가능
  • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같은 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우

어떤것으로 정렬을 했는지는 실행 계획의 Extra 열로 확인이 가능하다 해당 사진은 id를 통해 조회했기에 클러스터링 인덱스를 사용하여 정렬한 것을 알 수 있다.

 

📌9.2.3.1 소트 버퍼

MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용한다. 이 메모리 공간을 소트 버퍼라고 한다.

소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 설정된다.

소트버퍼는 실행 완료되면 바로 시스템에 반납한다.

 

여기까지는 문제가 없지만 정렬이 문제가 되는 이유를 알아보자..

정렬해야할 레코드가 소량이면 상관없지만 소트 버퍼의 크기를 넘는 대량이면 문제가 발생한다.

이때 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 이 과정에서 임시 저장을 위해 디스크를 사용한다.

 

메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록한다. 이 과정을 병합하면서 정렬을 수행한다. 

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

 

이것의 문제점은 모두 디스크의 쓰기와 읽기를 유발하며 레코드 건수가 많을 수록 더 많이 반복된다. 소트 버퍼 크기를 키워도 나아지지 않는다.

 

해결 방법을 간단하게 적어보자면..

  • 적절한 소트 버퍼 크기 조정
  • 인덱스 활용
  • 리미트 설정으로 정렬 대상 축소
  • 필요없는 정렬 작업 제거 
  • 쿼리 분할
  • 병렬 처리

📌9.2.3.2 정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을 지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 

싱글 패스와 투 패스로 나뉜다. 어떤 모드인지는 옵티마이저를 통해 확인이 가능하다.

 

해당 예시로 알아보자 

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

 

싱글 패스 정렬 방식

소트 버퍼에 정렬 기준 칼럼을 포함해 SELECT 대상이 되는 칼럼 전부를 담아서 정렬을 수행하는 방식이다.

 

예시 쿼리처럼 first_name으로 정렬해서 emp_no, first_name, last_name을 SELECT하는 쿼리를 싱글 패스 정렬 방식으로 처리한 그림이다.

 

처음 employees 테이블을 읽을 때 정렬에 필요하지 않은 칼럼까지 읽어 소트 버퍼에 담고 정렬한다.

그리고 정렬이 완료되면 그대로 클라이언트에 넘겨주는 작업이다.

 

투 패스 정렬 방식

정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 칼럼을 가져오는 정렬 방식이다.

 

MySQL에서 예전 정렬 방식인 투 패스 방식은 테이블을 두 번 읽어 불합리하지만, 싱글 패스는 불합리가 없다. 

그러나 싱글 패스는 소트 버퍼 공간이 더 많이 필요하다. 

 

그래도 최신 버전에서는 싱글 패스를 주로 사용하며 무조건적인 것은 아니다.

밑은 투 패스를 사용하는 경우이다.

  • 레코드 크기가 소트 버퍼보다 클 때
  • BLOB이나 TEXT 타입이 칼럼에 포함될 때

싱글 패스는 레코드 크기나 건수가 적을 경우 빠르며 투 패스는 건수나 레코드 크기가 크면 효율적이다.

 

 

📌9.2.3.3 정렬 처리 방법

쿼리에 ORDER BY 가 사용되면 반드시 3가지 처리 방법 중 하나로 정렬된다.

  • 인덱스를 사용한 정렬
  • 조인에서 드라이빙 테이블만 정렬: "Using filesort" 메시지가 표시됨
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬: "Using temporary; Using filesort" 메시지가 표시됨

ORDER BY의 처리는 먼저 옵티마이저가 인덱스를 사용할 수 있는지 검토할 것이다. 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하여 정렬(filesort)을 처리할 것이다. 

 

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

  • 조인의 드라이빙 테이블만 정리 후 조인(드라이빙 테이블이란 조인에서 먼저 선택되는 테이블이다.)
  • 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬
인덱스를 이용한 정렬

인덱스를 이용한 정렬을 위해선 다음과 같은 조건이 있어야 한다.

  • 반드시 ORDER BY에 명시된 칼럼이 제일 먼저 읽는 테이블에 속해야함(조인도 드라이빙 테이블에 속해야함)
  • ORDER BY의 순서대로 생성된 인덱스가 있어야함,
  • WHERE 조건이 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야함
  • B-Tree 인덱스여야함
  • 여러 테이블이 조인되는 경우에는 네스티드-루프 방식으로 조인해야만 사용 가능

인덱스 사용한 정렬은 이미 정렬되어 있기에 그대로 읽기만 하면 된다. 

 

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department_id INT,
    salary INT,
    INDEX idx_department_salary (department_id, salary)
);

SELECT * 
FROM employees
WHERE department_id = 2 // WHERE 절이 첫번째 인덱스 조건
ORDER BY salary; // ORDER BY는 인덱스에서 사용할 수 있는 조건이어야함

 

조인의 드라이빙 테이블만 정렬

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇배로 불어나며 레코드의 크기가 늘어난다.

그래서 조인을 실행하기 전에 첫 번째 테이블을 먼저 정렬한다음 조인을 실행하는 것이 좋다.

이 경우 첫번째로 읽히는 테이블(드라이빙 테이블)의 칼럼만으로 ORDER BY 절을 작성해야한다.

SELECT e.name, d.department_name
FROM employees e // 드라이빙 테이블
JOIN departments d ON e.department_id = d.id
ORDER BY e.department_id; // 드라이빙 테이블을 조건으로 ORDER BY

 

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해서 정렬하는 경우라면 임시 테이블이 필요하지 않다.

하지만 2개 이상의 테이블을 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다.

 

앞에서 살펴본 조인의 드라이빙 테이블만 정렬은 2개 이상의 테이블이 조인되면 정렬이 실행되지만 임시 테이블을 사용하지 않는다. 하지만 그 외의 모든 경우 항상 조인 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.

 

이 방법은 정렬의 3가지 방법 가운데 정렬애햐 할 레코드 건수가 가장 많기 때문에 가장 느린 정렬 방법이다.

 

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.id
ORDER BY d.id;  -- 드리븐 테이블인 'departments' 테이블을 기준으로 정렬

해당 쿼리는 드라이빙 테이블이 정렬 조건이 아닌 드리븐 테이블이 정렬 조건이다 즉 조인 전까지 정렬을 할수 없는 것이다. 그렇기에 조인을 하고 많은 쿼리들을 정렬해야 한다.

 

 

쿼리 실행 계획을 보면 Extra 칼럼에 Using temporary; Using filesort 라는 코멘트가 표시된다.

이는 조인 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미한다.

 

 

📌정렬 처리 방법의 성능 비교

주로 웹 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT가 거의 필수로 사용되는 경향이 있다. 일반적으로 LIMIT는 테이블이나 처리 결과의 일부만 가져오기에 MySQL 서버가 처리할 작업량을 줄이는 역할을 한다. 그런데 ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수만큼 가져와서 처리할 수는 없다.

우선 만족하는 레코드를 모두 가져와서 정렬을 수행한 후 LIMIT를 하기에 아무리 인덱스를 잘 활용하고 튜닝을 해도 잘못된 ORDER BY와 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.

 

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느린지 살펴보자 

이를 위해 쿼리가 처리되는 방법을 2가지 방법으로 구분해보자

 

    • 스트리밍 방식
      • 서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식. 이 방식에 LIMIT 조건을 추가하면 시간을 줄일 수 있다.
    • 버퍼링 방식
      • ORDER BY, GROUP BY 같은 쿼리는 스트리밍 방식을 사용할 수 없다. 조건을 만족하는 레코드를 가져와 정렬, 그루핑이 완료된 후 전송한다. LIMIT 조건을 추가해도 성능에 도움이 되지 않는다.

 

참고로 3가지 방법 중 인덱스는 LIMIT로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송할 수 있다.

나머지는 버퍼링 방식으로 동작한다.

 

🧷9.2.4 GROUP BY 처리 

GROUP BY 또한 ORDER BY와 같이 쿼리가 스트링된 처리를 할 수 없게 하는 처리 중 하나이다. 

GROUP BY도 인덱스를 사용하는 경우와 그렇지 못한 경우로 나뉜다.

 

📌인덱스 스캔

타이트 인덱스 스캔

타이트 인덱스 스캔은 GROUP BY를 처리할 때 인덱스가 존재하면 그 인덱스를 차례대로 읽어서 그룹핑 작업을 처리한다.

즉. ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 GROUP BY 칼럼으로 이미 인덱스가 있다면 처리한다.

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

 

인덱스를 사용하는 경우는 읽기만하면 되는 것이므로 추가적인 정렬 작업이나 임시 테이블은 필요하지 않다.

 

루스 인덱스 스캔

루스 인덱스 스캔 방식은 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것을 의미한다. 루스 인덱스 스캔을 사용할 때는 실행 계획의 Extra 컬럼에 “Using index for group-by” 라는 코멘트가 표시된다.

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

 

(emp_no, from_date)로 인덱스가 생성되어 있다고 하자. 이 경우 WHERE 절에 있는 조건은 인덱스 레인지 스캔을 사용할 수 없다. 하지만 이 쿼리의 실행 계획은 인덱스 레인지 스캔을 사용했으며, GROUP BY 처리까지 인덱스를 사용했다.

  • (emp_no, from_date) 인덱스를 스캔:
    • (emp_no, from_date) 인덱스를 차례대로 스캔하면서, emp_no의 첫 번째 유일한 값을 찾습니다.
    • 이 때, emp_no가 유일하므로 첫 번째 값(예: 10001)을 찾으면 그에 해당하는 데이터를 검색합니다.
  • emp_no=10001 조건 적용:
    • (emp_no, from_date) 인덱스에서 emp_no = 10001인 레코드를 찾습니다.
    • 이 때, 추가 조건인 from_date = '1985-03-01'을 만족하는 레코드도 동시에 찾을 수 있습니다.
    • 이 과정은 사실상 AND 조건을 통해 인덱스에서 검색하는 방식과 유사합니다.
  • 다음 emp_no 처리:
    • 첫 번째 emp_no(10001)에 대한 조건을 처리한 후, 인덱스를 계속 스캔하여 다음 emp_no 값을 찾습니다.
    • 이렇게 해서 각 emp_no에 대해 해당하는 from_date 값을 검색하는 방식이 반복됩니다.

 

루스 인덱스 스캔을 사용할 수 없는 예도 알아보자

  • 집합 함수 사용(MIN, MAX 이외의 집합 함수)
  • GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치하지 않음
  • SELECT 절의 칼럼이 GROUP BY와 일치하지 않음

📌임시 테이블 사용

GROUP BY는 칼럼이 조인의 드라이빙에 있든 드리븐에 있든 상관없이 인덱스를 사용 못하면 임시 테이블을 사용한다. 또한 GROUP BY에 정렬이 필요할 때도 사용한다. 

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

해당 쿼리는 MIN, MAX 이외의 집합 함수를 사용했기에 루스 인덱스 스캔을 사용하지 못한다.  또한 (emp_no, from_date) 인덱스를 기준으로 인덱스 사용도 못하기에 인덱스 스캔 자체가 안되는 케이스입니다. 

 

이 경우 해당처럼 임시 테이블이 생성됩니다.

CREATE TEMPORARY TABLE ... (
	last_name VARCHAR(16),
	salary INT,
	UNIQUE INDEX ux_lastname (last_name)
);

 

그리고 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT, UPDATE를 실행합니다. 즉 별도의 정렬 작업 없이 GROUP BY가 처리됩니다.

 

 

원래는 해당 과정에서 GROUP BY의 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 함께 수행했습니다. 그래서 기본적으로 정렬이 되어 들어왔습니다. 하지만 MySQL 8.0부터는 이 같은 묵시적인 정렬은 더 이상 실행되지 않습니다.

그렇기에 필요한 경우 ORDER BY를 추가하는 것이 좋습니다.

 

더보기

추가

 

1.

EXPLAIN의 실행 계획에서 임시 테이블이 사용될 때는 "Using temporary"라는 코멘트가 추가됩니다.

 

2.

만약 GROUP BY가 자주 사용되는 경우, 인덱스 활용과 임시 테이블 사용을 잘 분석하여 성능 최적화를 고민해야 합니다. 인덱스를 활용할 수 없을 때 임시 테이블을 사용하게 되면 성능에 영향을 줄 수 있기 때문에, 인덱스가 잘 활용될 수 있는 설계가 필요합니다.

🧷9.2.5 DISTINCT 처리

특정 칼럼의 유니크한 값만 조회하려면 DISTINCT를 사용한다. DISTINCT는 MIN(), MAX(), COUNT() 같은 집합 함수와 같이 사용되는 경우와 집합 함수가 없는 경우 2가지로 구분된다. 

 

📌9.2.5.1 SELECT DISTINCT 

단순히 SELECT 되는 레코드 중에서 레코드만 가져오는 경우 GROUP BY와 동일한 방식으로 처리된다.

 

그렇기에 아래 두 코드는 동일한 방식으로 처리한다.

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

 

📌9.2.5.12 집합 함수와 함께 사용된 DISTINCT

MIN(), MAX(), COUNT() 같은 집합 함수내에서 DISTINCT 키워드는 일반적으로 다르게 동작한다. 집합 함수가 없는 DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다. 하지만 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼 값이 유니크한 것들을 가져온다.

EXPLAIN
	SELECT COUNT(DISTINCT s.salary)
	from employees e, salaries s
	WHERE e.emp_no=s.emp_no
	and e.emp_no BETWEEN 101 AND 110;

 

이 쿼리는 결과 값을 처리하기 위해 임시 테이블을 사용합니다. 하지만 실행 계획에서는 임시 테이블을 사용하는 메시지는 표시하지 않습니다. 또한 이 임시테이블은 salary 칼럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려집니다.

 

하지만 인덱스된 칼럼에 대해 DISTINCT를 수행하면 인덱스를 풀 스캔하거나 레인지 스캔하기에 임시 테이블없이 최적화가 가능합니다. 

 

🧷9.2.6 내부 임시 테이블 활용

MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적인 임시 테이블을 사용합니다.

이 내부적 임시 테이블은 임시 테이블을 만드는 CREATE TEMPORARY TABLE 명령과는 차이점이 있습니다.

 

일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨집니다.

MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능합니다. 사용자가 생성한 임시 테이블과 달리 내부적인 임시 테이블은 쿼리의 처리 완료시 자동으로 삭제됩니다.

  • 메모리 임시 테이블과 디스크 임시 테이블
    • 시스템 변수를 이용해 임시 테이블이 최대로 사용 가능한 메모리 공간의 크기를 정할 수 있습니다. 메모리에 저장되는 임시 테이블은 TempTable을, 디스크에 저장되는 임시 테이블은 InnoDB를 사용합니다.
  • 임시 테이블이 필요한 쿼리
    • ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
    • ORDER BY와 GROUP BY에 명시된 컬럼이 드라이빙 테이블이 아닌 쿼리
    • DISTINCT와 ORDER BY가 동시에 존재하거나 DISTINCT가 인덱스로 처리되지 못하는 쿼리
    • UNION이나 UNION DISTINCT가 사용된 쿼리
    • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
  • 위 경우 중 마지막 쿼리 패턴을 제외하고 전부 유니크 인덱스를 가지는 내부 임시 테이블이 만들어집니다.
  • 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 처리 성능이 느린편입니다.

 

 

🔥정리

9.1 쿼리 실행 절차

MySQL 서버에서 쿼리가 실행되는 과정은 크게 3단계로 나뉩니다:

  1. SQL 파싱 & 전처리: SQL 문장이 문법적으로 올바른지 검사하고, 파스 트리를 생성합니다.
  2. 옵티마이저: 파스 트리를 기반으로 최적화 작업을 수행합니다. 여기서 인덱스를 선택하고, 불필요한 조건을 제거하며, 조인의 순서를 결정합니다.
  3. MySQL 엔진 & 스토리지 엔진: 최적화된 계획에 따라 데이터를 읽고 조인 및 정렬 작업을 수행합니다.

9.1.2 옵티마이저의 종류

옵티마이저는 크게 **규칙 기반 최적화(RBO)**와 **비용 기반 최적화(CBO)**로 나뉩니다.

  • 규칙 기반 최적화(RBO): 데이터 양을 고려하지 않고, 내장된 우선순위에 따라 실행 계획을 수립합니다. 현재는 거의 사용되지 않습니다.
  • 비용 기반 최적화(CBO): 여러 가지 실행 방법을 만들고, 각 작업의 비용을 계산하여 실행 계획을 수립합니다.

9.2 기본 데이터 처리

9.2.1 풀 테이블 스캔 vs 인덱스 스캔

  • 풀 테이블 스캔: 인덱스를 사용하지 않고 테이블을 처음부터 끝까지 읽는 방식입니다. 레코드가 적거나 조건에 맞는 인덱스가 없을 경우 사용됩니다.
  • 리드 어헤드: 풀 테이블 스캔 시, InnoDB는 데이터를 미리 읽어들여 쿼리 성능을 향상시킵니다.

9.2.2 병렬 처리

  • 병렬 처리: MySQL 8.0에서는 하나의 쿼리를 여러 스레드가 나누어 동시에 처리할 수 있습니다. 적절한 스레드 수를 설정해야 최적화가 가능합니다.

9.2.3 ORDER BY 처리

  • 인덱스 사용: 인덱스가 정렬 순서와 일치하면 빠르게 처리됩니다.
  • Filesort: 인덱스를 사용할 수 없거나 정렬 기준이 복잡할 경우, Filesort가 사용됩니다.

소트 버퍼는 정렬을 위해 메모리에서 할당되는 공간으로, 버퍼 크기를 조정하거나 인덱스를 활용하여 성능을 최적화할 수 있습니다.

9.2.3.1 소트 버퍼와 정렬

  • 정렬이 메모리 내에서 처리될 때 소트 버퍼에 데이터를 담고, 메모리가 부족하면 디스크를 사용해 정렬합니다. 이를 "멀티 머지"라고 하며, 디스크 I/O가 많을수록 성능 저하가 발생할 수 있습니다.

9.2.3.2 정렬 알고리즘

  • 싱글 패스: 모든 정렬 기준을 소트 버퍼에 담아 한 번에 정렬하는 방식입니다.
  • 투 패스: 먼저 정렬 기준 칼럼을 정렬하고, 프라이머리 키로 다시 테이블을 읽어 필요한 칼럼을 가져오는 방식입니다.

싱글 패스는 소트 버퍼가 충분할 때 빠르지만, 버퍼가 부족하면 성능이 떨어질 수 있습니다. 반면, 투 패스는 레코드 크기가 클 때 더 효율적입니다.

9.2.3.3 정렬 처리 방법

  • 인덱스 사용: 인덱스를 이용해 정렬하면 성능이 빠릅니다.
  • 조인 처리: 조인 후 결과를 임시 테이블로 저장하고 정렬하는 경우 Using temporary; Using filesort 메시지가 표시됩니다.