개요

어떤 DBMS든지 쿼리의 실행 계획을 수립하는 옵티마이저는 가장 복잡한 부분으로 알려져 있으며, 옵티마이저가 만들어 애는 실행 계획을 이해하는 것 또한 상당히 어려운 부분이다.

하지만 실행 계획을 이해할 수 있어야만 실행 계획의 불합리한 부분을 찾아내고, 더 최적화된 방법으로 실행 계획을 수립하도록 유도할 수 있다.

쿼리 실행 절차

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

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

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

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

MySQL은 SQL 문장 그 자체가 아니라 SQL 파스 트리를 이용해 쿼리를 실행한다.

두 번째 단계는 최적화 및 실행 계획 수립 단계이며, 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 다음과 같은 내용을 처리한다.

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

이 밖에도 많은 처리를 하지만, 대표적으로 위와 같은 작업을 수행한다.

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

첫 번째 단계와 두 번째 단계는 거의 MySQL 엔진에서 처리하며, 세 번째 단계는 MySQL 엔진과 스토리지 엔진이 동시에 참여해서 처리한다.

옵티마이저의 종류

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

옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer) 방법과 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer)으로 크게 나눌 수 있다.

---

규칙 기반 최적화

대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식
사용자의 데이터는 분포도가 매우 다양하기에 규칙 기반의 최적화는 많은 DBMS에서 사용되지 않음

비용 기반 최적화

쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출

---

현재 대부분의 RDBMS는 비용 기반의 옵티마이저를 채택하고 있으며, MySQL 또한 마찬가지이다.

기본 데이터 처리

MySQL을 포함한 RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있다.

하지만 결과물은 동일하더라도 RDBMS 별로 그 결과를 만들어 내는 과정은 천차만별이다.

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

MySQL 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다.

• 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우(테이블이 페이지 1개로 구성된 경우)
• WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우

1번째 경우는 드물고 대부분 2, 3번째 상황에 풀 테이블 스캔이 사용되지 싶다.

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

그래서 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.

하지만 MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.

하지만 InnoDB를 사용하면 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 시작되어 한 번에 많은 페이지를 읽어서 빠르게 처리할 수 있다.

처음 몇 개의 데이터 페이지는 포그라운드로 처리하다, 특정 시점부터 백그라운드 스레드로 작업을 넘기는데, 해당 시점은 innodb_read_ahead_threshold 시스템 변수이다. (8.0 기준 기본 값은 56이고 최대 값은 64이다.)
동작은 예상컨데, 포그라운드 스레드는 wait로 대기하고, 백그라운드 스레드가 읽은 뒤 notify 하여 multiplexing 방식으로 구현되어 사용할 것 같다.
공식 문서에서도 비동기로 처리한다고 나와있다.

리드 어헤드는 풀 테이블 스캔에서 사용되는 것이 아니라, 풀 인덱스 스캔에서도 동일하게 사용된다.

병렬 처리

MySQL 8.0에서는 innodb_parallel_read_threads 시스템 변수를 통해 하나의 쿼리를 최대 몇 개의 스레드를 이용해서 처리할지를 변경할 수 있다.

아직 MySQL 서버에서는 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵션으 없고, 아무런 WHERE 조건 없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.

ORDER BY 처리(Using filesort)

레코드 1~2건을 가져오는 쿼리를 제외하면 대부분의 SELECT 쿼리에서 정렬은 필수적으로 사용된다.

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

---

인덱스 이용

장점: 인덱스로 정렬이 되어 있기에, 순서대로 읽기만 하면 되므로 매우 빠르다.
단점: INSERT, UPDATE, DELETE 작업 시 인덱스 추가/삭제 과정이 필요하다. 또한 인덱스를 위한 디스크 공간이 사용되어 버퍼 풀을 위한 메모리가 많이 필요하다.

Filesort 이용

장점: 인덱스를 생성하지 않아도 되므로, 디스크 공간이 절약되고 INSERT, UPDATE, DELETE 작업이 빠르다.
단점: 레코드 건수가 많아질수록 쿼리의 응답 속도가 느리다.

---

조회 성능을 위한다면 인덱스를 이용하는 것이 가장 좋겠지만, 모든 정렬을 인덱스를 이용하는 것은 불가능하다.

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

MySQL에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지 확인하려면 실행 계획의 Extra 컬럼에 Using filesort 메시지가 표시되는지 여부로 판단할 수 있다.

소트 버퍼

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

소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 innodb_sort_buffer_size 시스템 변수로 설정할 수 있다.

기본 값은 1048576 바이트(1024KB)이고, 최대 값은 67108864 바이트(65536KB)이다.

소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.

위의 상황은 가장 이상적인 상황이고, 정렬해야할 레코드가 매우 소량이라 메모리에 할당된 소트 버퍼만으로 정렬할 수 있다면 아주 빠르게 처리될 것 이지만, 정렬해야할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠 임시 저장을 위해 디스크를 사용한다.

따라서 디스크 I/O를 유발하고 레코드 건수가 많을수록 디스크 I/O 작업은 많아진다.

또한 소트 버퍼를 크게 설정하더라도 성능이 더 높아지지 않는다.

그리고 리눅스 계열의 운영체제에서 너무 큰 innodb_sort_buffer_size를 설정하면 큰 메모리 공간 할당 때문에 성능이 훨씬 떨어질 수 있다.

4장 아키텍쳐 부분에서 언급했지만, 리눅스 계열에서는 256KB 또는 2MB 메모리 할당을 초과하면 malloc() 대신 mmap()을 사용하기 때문이라고 한다. (관련 링크)

또한 소트 버퍼는 글로벌 메모리 영역이 아닌 세션 메모리 영역에 할당되므로, 소트 버퍼의 크기를 너무 크게 설정하면 메모리 부족 현상이 발생할 수 있고, 최악의 상황으로 운영체제가 메모리를 확보하기 위해 MySQL 서버가 강제로 종료될 수 있다.

정렬 알고리즘

레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글 패스 또는 투 패스 2가지 정렬 모드로 나눌 수 있다.

싱글 패스 정렬 방식

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

투 패스 정렬 방식

정렬 대상 컬럼과 프라미어리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT할 컬럼을 가져오는 정렬 방식으로, 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식이다.

투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 상당히 불합리하지만, 새로운 정렬 방식인 싱글 패스는 이러한 불합리가 없다.

하지만 싱글 패스 방식은 더 많은 소트 버퍼가 필요하다.

다음 같은 경우 싱글 패스 정렬 방식을 사용하지 못하고 투 패스 정렬 방식을 사용한다.

• 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

정렬 처리 방법

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

일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어진다.

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

옵티마이저는 정렬 처리를 위해 인덱스를 이용할 수 있을지 검토하고, 인덱스를 이용할 수 있다면 별도의 Filesort 과정 없이 인덱스를 순서대로 읽어서 결과를 반환한다.

하지만 인덱스를 사용할 수 없다면 WHERE 조건에 일치하는 레코드를 검색해 정렬 버퍼에 저장하면서 정렬을 처리할 것이다.

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

• 조인의 드라이빙 테이블만 정렬한 다음 조인을 수행
• 조인이 끝나고 일치하는 레코드를 모두 가져온 후 정렬을 수행

일반적으로 조인이 수행되면서 레코드의 건수와 레코드의 크기는 거의 배수로 불어나기 때문에 가능하다면 드라이빙 테이블만 정렬한 다음 조인을 수행하는 방법이 효율적이다.

따라서 두 번째 방법보다 첫 번째 방법이 효율적으로 처리된다.

인덱스를 이용한 정렬

인덱스를 이용한 정렬은 위해서는 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.

또한 WHERE 절에 첫 번째로 읽은 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.

그리고 B-Tree 계열의 인덱스가 아닌 해시 인덱스나 전문 검색 인덱스 등에서는 인덱스를 이용한 정렬을 사용할 수 없다.

예외적으로 R-Tree도 B-Tree 계열이지만, 특성상 이 방식을 사용할 수 없다.

여러 테이블이 조인되는 경우에도 Nested-loop 방식의 조인에서만 이 방식을 사용할 수 있다.

인덱스를 사용한 정렬 시 ORDER BY를 명시하지 않아도 정렬이 수행되기에 ORDER BY를 생략하는 경우가 있다.
하지만 쿼리의 결과가 반드시 정렬을 사용해야 하는 조건이라면 ORDER BY를 명시하는 것이 혼란과 버그를 방지할 수 있을 것이다.

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

일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고, 레코드 하나하나의 크기도 늘어난다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것이다.

이 방법으로 정렬이 처리되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 절을 작성해야 한다.

임시 테이블을 이용한 정렬

쿼리가 여러 테이블을 조인하지 않고, 하나의 테이블로부터 SELECT해서 정렬해야 하는 경우라면 임시 테이블이 필요하지 않다. 하지만 2개 이상의 테이블로 조인해서 그 결과를 정렬해야 한다면 임시 테이블이 필요할 수도 있다.

이전의 조인의 드라이빙 테이블만 정렬은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않는다. 하지만 그 외의 패턴의 쿼리에는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.

이 방법은 정렬의 3가지 방법 중 가장 느린 정렬 방법이다.

해당 방법을 사용하면 실행 계획의 Extra 컬럼에 Using temporary; Using filesort로 나타난다.

정렬 처리 방법의 성능 비교

웹 서비스용 쿼리에서는 ORDER BY와 함께 LIMIT이 거의 필수적으로 사용된다.

일반적으로 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 MySQL이 처리해야 할 작업량을 줄이는 역할을 한다.

그런데 ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없다.

우선 조건을 만족하는 모든 레코드를 가져와서 정렬 또는 그루핑 작업을 실행해야 한다.

따라서 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.

쿼리에서 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수 밖에 없는지 두 가지 방법을 통해 알아보자

---

1. 스트리밍 방식

서버 쪽에서 처리할 데이터가 얼마인지에 관계없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다.

이 방식으로 쿼리를 처리할 경우 클라이언트는 쿼리를 요청하고 곧바로 원했던 첫 번째 레코드를 전달받는다.

가장 마지막 레코드는 언제 받을 지 알 수 없지만, 이는 그다지 중요하지 않다.

이는 비동기로 처리된다는 말이다. (결과에 신경을 쓰지 않는다)

쿼리가 스트리밍 방식으로 처리된다면 클라이언트는 MySQL이 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있다.

웹 서비스 같은 OLTP 환경에서는 쿼리의 요청에서부터 첫 번째 레코드를 전달받게 되기까지의 응답 시간이 중요하다.

스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하느냐에 상관없이 빠른 응답 시간을 보장해준다.

2. 버퍼링 방식

ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 한다.

우선 WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그루핑해서 차례대로 보내야 하기 때문이다.

MySQL이 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 한다.

참고에도 나와있지만, JDBC를 사용하는 경우 JDBC 라이브러리에서 자체적으로 버퍼링을 수행하기 때문에 스트리밍 방식이라도 버퍼링 방식처럼 동작한다.
나의 추측으로 아마 이러한 문제 때문에 R2DBC를 사용하는게 아닌가 한다.
DB에서 가져온 데이터를 가공하는 작업의 비용이 큰 경우(네트워크, 디스크 I/O) 스트리밍 방식이 효과적일 것 같은데, 메모리, CPU I/O와 같이 작업의 비용이 적거나 바로 응답을 받아야 한다면 버퍼링 방식이 효과적이지 않을까

---

조인과 함께 ORDER BY와 LIMIT이 사용된 경우 조인 횟수와 정렬해야 할 건수는 다음과 같다.

(table_a 레코드 100건, table_b 레코드 1000건, table_a 레코드 1건 당 table_b의 레코드가 10건씩 존재한다고 가정)

select * from table_a ta table_b tb where ta.col1 = tb.col1 order by ta.col2 limit 10

table_a가 드라이빙 되는 경우

정렬 방법	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	table_a: 1 table_b: 10	1	0
조인의 드라이빙 테이블만 정렬	table_a: 100 table_b: 10	1	100
임시 테이블 사용 후 정렬	table_a: 100 table_b: 1000	100	1000

table_b가 드라이빙 되는 경우

정렬 방법	읽어야 할 건수	조인 횟수	정렬해야 할 대상 건수
인덱스 사용	table_b: 10 table_a: 10	1	0
조인의 드라이빙 테이블만 정렬	table_b: 1000 table_a: 10	10	1000
임시 테이블 사용 후 정렬	table_b: 1000 table_a: 100	1000	1000

정렬 관련 상태 변수

MySQL 서버는 처리하는 주요 작업에 대해서는 해당 적업의 실행 횟수를 상태 변수로 저장한다.

정렬과 관련해서도 지금까지 몇 건의 레코드나 정렬 처리를 수행했는지, 소트 버퍼 간의 병합 작업은 몇 번이나 발생했는지 등을 다음과 같은 명령으로 확인해 볼 수 있다.

SHOW STATUS LIKE 'Sort%'

Sort_merge_passes: 멀티 머지 처리 횟수

Sort_range: 인덱스 레인지 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수

Sort_scan: 풀 테이블 스캔을 통해 검색된 결과에 대한 정렬 작업 횟수

Sort_rows: 지금까지 정렬한 전체 레코드 건수

GROUP BY 처리

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

GROUP BY 절이 있는 쿼리에서 HAVING 절을 사용할 수 있는데, HAVING 절은 GROUP BY 결과에 대해 필터링 역할을 수행한다.

GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.

GROUP BY 작업도 인덱스를 사용하는 경우와 사용하지 못하는 경우로 나눌 수 있다.

인덱스를 사용한 방법은 인덱스를 차례대로 읽는 인덱스 스캔과 인덱스를 건너뛰면서 읽는 루스 인덱스 스캔 방법으로 나뉜다.

그리고 인덱스를 사용하지 못하는 쿼리에서 GROUP BY 작업은 임시 테이블을 사용한다.

인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)

ORDER BY와 마찬가지로 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑을 할 때, GROUP BY 칼럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 조인을 수행한다.

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

GROUP BY가 인덱스를 통해 처리되는 쿼리는 이미 정렬된 인덱스를 읽는 것이므로 쿼리 실행 시점에 추가적인 정렬 작업이나 내부 임시 테이블은 필요하지 않다.

이러한 그루핑 방식을 사용하는 실행 계획의 Extra 컬럼에는 별도로 GROUP BY 커멘트가 표시되지 않는다.

루스 인덱스 스캔을 이용하는 GROUP BY

해당 내용은 인덱스 파트의 루스 인덱스 스캔에 자세한 설명이 되어 있으므로 생략한다.

루스 인덱스 스캔을 사용하면 별도의 임시 테이블이 필요하지 않다.

임시 테이블을 사용하는 GROUP BY

GROUP BY의 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다.

MySQL 8.0 이전에는 GROUP BY가 사용된 쿼리는 그루핑되는 칼럼을 기준으로 묵시적인 정렬까지 수행됐지만 8.0 이후에는 묵시적인 정렬은 수행되지 않는다.

MySQL 8.0에서는 GROUP BY가 필요한 경우 내부적으로 GROUP BY 절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.

그리고 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복 체크를 하면서 INSERT 또는 UPDATE를 실행한다.

즉 별도의 정렬 작업 없이 GROUP BY가 처리된다.

추가로 명시적으로 ORDER BY가 쿼리에 사용되면 정렬을 수행한다.

DISTINCT 처리

특정 칼럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용한다.

DISTINCT는 MIN(), MAX(), COUNT()와 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우의 2가지로 구분한다.

SELECT DISTINCT ...

레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다.

이 경우에는 GROUP BY와 동일한 방식으로 처리된다.

MySQL 8.0 이후부터 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 다음 쿼리는 같은 작업을 수행한다.

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

집합 함수와 함께 사용된 DISTINCT

MIN(), MAX(), COUNT()와 같은 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있는데, 이 경우에는 일반적으로 SELECT DISTINCT와 다른 형태로 해석된다.

집합 함수가 없는 SELECT 쿼리에서 DISTINCT는 조회하는 모든 칼럼의 조합이 유니크한 것들만 가져온다.

하지만 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져온다.

SELECT COUNT(DISTINCT s.salary)
FROM employee e, salaries s
...

위와 같이 집합 함수 내에서 DISTINCT를 사용하면 내부적으로 임시 테이블을 사용한다.

하지만 쿼리의 실행 계획에서는 임시 테이블을 사용한다고 표시되지 않는다.

임시 테이블이 필요한 이유는 조인한 결과에서 유니크 인덱스를 가진 salary 컬럼이 필요하기 때문이다.

만약 다음과 같은 쿼리라면 2개의 임시 테이블이 필요하다.

SELECT COUNT(DISTINCT s.salary), COUNT(DISTINCT e.last_name)
FROM employee e, salaries s
...

하지만 인덱스를 사용할 수 있는 컬럼이라면 인덱스 풀 스캔 또는 레인지 스캔으로 임시 테이블 없이 처리가 가능하다.

SELECT COUNT(DISTINCT emp_no) FROM employees;

내부 임시 테이블 활용

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

내부적이라는 단어가 포함된 이유는 CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과는 다르기 때문이다.

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

하지만 메모리를 거치지 않고 바로 디스크에 임시 테이블을 만드는 예외 케이스도 있으니 주의해야 한다.

MySQL 엔진이 생성하는 임시 테이블은 다른 세션이나 쿼리에서 조회, 사용할 수 없다.

또한 명령으로 만든 임시 테이블과 다르게 내부적인 임시 테이블은 쿼리가 완료되면 자동으로 삭제된다.

메모리 임시 테이블과 디스크 임시 테이블

MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용한다.

MEMORY 스토리지 엔진은 VARBINARY, VARCHAR와 같은 가변 길이 타입을 지원하지 못하기 때문에 임시 테이블이 메모리에 만들어지면 가변 길이 타입의 경우 최대 길이만큼 메모리를 할당해서 사용했기에 메모리 낭비가 심해지는 문제가 있었다.

또한 디스크에 임시 테이블이 만들어질때 사용되는 MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못하는 문제가 있었다.

따라서 이후 MySQL 8.0 부터는 MEMORY 대신 TempTable, InnoDB 스토리지 엔진을 사용하도록 개선됐다.

메모리에 저장되는 TempTable의 경우 temptable_max_ram 시스템 변수를 통해 최대 용량을 지정할 수 있으며, 최대 용량이 넘어가면 MMAP 파일 또는 InnoDB 테이블로 디스크에 기록된다.

또한 MMAP 또는 InnoDB 테이블로 전환할지는 temptable_use_mmap 시스템 변수로 설정할 수 있고, 기본값은 ON이다.

이유는 MMAP 파일이 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문이다.

임시 테이블의 필요한 쿼리

다음과 같은 패턴의 쿼리는 MySQL 엔진에서 별도의 데이터 가공 작업을 필요하므로 내부 임시 테이블을 생성한다.

• ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
• ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
• DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
• UNION이나 UNION DISTINCT가 사용된 쿼리(select_type 칼럼이 UNION RESULT인 경우)
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리

임시 테이블이 디스크에 생성되는 경우

내부 임시 테이블은 기본적으로 메모리상에서 만들어지지만 다음 조건을 만족하면 메모리 임시 테이블을 사용할 수 없기 때문에 디스크 기반의 임시 테이블을 사용한다.

• UNION이나 UNION ALL에서 SELECT되는 컬럼 중에서 길이가 512바이트 이상인 크기의 컬럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
• 메모리 임시 테이블의 크기가 tmp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 temptable_max_ram 시스템 변수 값보다 큰 경우

임시 테이블 관련 상태 변수

실행 계획에서 Using temporary가 표시되면 임시 테이블을 사용했다는 사실을 알 수 있지만, 임시 테이블이 메모리에서 처리됐는지 디스크에서 처리됐는지 알 수 없으며, 몇 개의 임시 테이블이 사용됐는지도 알 수 없다.

임시 테이블이 생성된 정보를 확인하려면 MySQL의 상태 변수(SHOW SESSION STATUS LIKE 'Created_tmp%')를 확인해 보면 된다.

쿼리 힌트

MySQL의 버전이 업그레이드되고 통계 정보나 옵티마이저의 최적화 방법들이 더 다양해지며 쿼리의 실행 계획 최적화가 많이 성숙하고 있다.

하지만 여전히 MySQL은 우리가 서비스 하는 비즈니스를 100% 이해하지 못한다.

따라서 서비스 개발자나 DBA보다 MySQL 서버가 부족한 실행 계획을 수립할 때가 있을 수 있다.

이런 경우 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요한데, 일반적인 RDBMS에서는 이런 목적으로 힌트가 제공된다.

MySQL에서 사용가 가능한 쿼리 힌트는 다음과 같이 2가지로 구분할 수 있다.

• 인덱스 힌트
• 옵티마이저 힌트

인덱스 힌트는 예전 버전의 MySQL에서 사용되어 오던 USE INDEX 같은 힌트를 의미한다.

옵티마이저 힌트는 MySQL 5.6 버전부터 새롭게 추가되기 시작한 힌트들을 지칭한다.

하지만 옵티마이저 힌트에 포함되지 않는 STRAIGHT_JOIN과 같은 힌트들도 있다.

이러한 힌트는 인덱스 힌트로 분류한다.

인덱스 힌트

STRAIGHT_JOIN과 USE_INDEX 등을 포함한 인덱스 힌트들은 모두 MySQL 서버에 옵티마이저 힌트가 도입되기 전에 사용되던 기능들이다.

이는 모두 SQL 문법에 맞게 사용해야 하기 때문에 ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점이 있다.

MySQL 5.6 부터 추가되기 시작한 옵티마이저 힌트들은 모두 MySQL을 제외한 RDBMS에서는 주석으로 해석하기 때문에 ANSI-SQL 표준을 준수한다고 볼 수 있다.

그래서 가능하다면 인덱스 힌트보다는 옵티마이저 힌트를 사용할 것을 추천한다.

또한 인덱스 힌트는 SELECT, UPDATE 명령에서만 사용할 수 있다.

STRAIGHT_JOIN

STRAIGHT_JOIN은 인덱스 힌트인 동시에 조인 키워드이기도 하다.

책에서는 옵티마이저 힌트라고 나와있는데, 오타인 것 같다.

STRAIGHT_JOIN은 여러 테이블이 조인되는 경우 옵티마이저가 자동으로 드라이빙 테이블과 드리븐 테이블을 지정하는 것을 수동으로 지정할 수 있도록 해준다.

다음 같은 경우에 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

• 임시 테이블과 일반 테이블의 조인: 임시 테이블을 드라이빙 테이블로 지정
• 임시 테이블끼리 조인: 크기가 작은 테이블을 드라이빙 테이블로 지정
• 일반 테이블끼리 조인: 양쪽 모두 인덱스가 있거나 인덱스가 없는 경우 레코드 건수가 적은 테이블을 드라이빙, 그 외의 경우 조인 컬럼에 인덱스가 없는 테이블을 드라이빙 테이블로 지정

STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 있다.

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

JOIN_FIXED_ORDER의 경우 STRAIGHT_JOIN 힌트와 동일한 효과를 낸다.

하지만 나머지 힌트들은 일부 테이블의 조인 순서에 대해서만 제안하는 힌트이다.

USE INDEX / FORCE INDEX / IGNORE INDEX

조인의 순서를 변경하는 것 다음으로 자주 사용되는 것이 인덱스 힌트이다.

STRAIGHT_JOIN 힌트와 달리 인덱스 힌트는 인덱스를 가지는 테이블 뒤에 힌트를 명시해야 한다.

MySQL의 경우 옵티마이저는 어떤 인덱스를 선택해야 할지를 무난하게 잘 선택하는 편이지만 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우 옵티마이저가 잘못된 인덱스를 선택할 수 있기에 강제로 특정 인덱스를 사용하도록 힌트를 추가해야 한다.

인덱스 힌트는 크게 3종류가 있다.

USE INDEX

• 가장 자주 사용되는 인덱스 힌트
• MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트
• 해당 힌트를 사용하면 항상 해당 인덱스를 사용하지는 않는다.

FORCE INDEX

• USE INDEX와 비교하여 다른 점은 없으며, USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트
• USE INDEX 힌트로 그 인덱스를 사용하지 않은 경우에도 FORCE INDEX 힌트를 사용해도 그 인덱스를 사용하지 않았다함.

IGNORE INDEX

• 위와 반대로 특정 인덱스를 사용하지 못하게 하는 용도
• 때로 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용

위 3가지 힌트 모두 용도를 명시할 수 있는데, 용도는 선택 사항이다.

USE INDEX FOR JOIN

• JOIN이라는 키워드는 테이블 간 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어
• MySQL에서는 하나의 테이블로부터 데이터를 검색하는 작업 또한 JOIN이라고 표현한다.

USE INDEX FOR ORDER BY

• 명시된 인덱스를 ORDER BY 용도로만 사용하도록 제한한다.

USE INDEX FOR GROUP BY

• 명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한한다.

하지만 용도는 옵티마이저가 대부분 최적으로 선택하기에 인덱스의 용도까지는 크게 고려하지 않아도 된다.

가장 훌륭한 최적화는 힌트가 필요한 쿼리를 서비스에서 없애거나 튜닝할 필요가 없게 데이터를 최소화하는 것이며, 그것이 어렵다면 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요하지 않게 하는 것이다.

SQL_CALC_FOUND_ROWS

MySQL의 LIMIT을 사용하는 경우, 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 많다고 하더라도 LIMIT에 명시된 수를 만족하는 레코드를 찾으면 즉시 검색 작업을 멈춘다.

하지만 SQL_CALC_FOUND_ROWS가 포함된 쿼리의 경우에는 LIMIT을 만족하는 수만큼의 레코드를 찾았다고 하더라도 끝까지 검색을 수행한다.

SQL_CALC_FOUND_ROWS 힌트가 사용된 경우에는 FOUND_ROWS()라는 함수를 이용해 LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지를 알아 낼 수 있다.

이 기능을 페이징에 응용할 수 있겠지만, FOUND_ROWS() 함수의 실행은 한 번의 쿼리를 추가로 더 필요로 한다.

게다가 데이터를 조회하기 위해 데이터 레코드를 읽어야 하므로 풀 테이블 스캔과 다를바가 없어진다.

COUNT(*) 쿼리를 사용한 경우, 인덱스 레코드만 읽으면 되므로 더 효율적이다.

옵티마이저 힌트

그 외 옵티마이저 힌트는 다음과 같다.

옵티마이저 힌트 종류

MAX_EXECUTION_TIME

SET_VAR

SEMIJOIN & NO_SEMIJOIN

SUBQUERY

BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

MERGE & NO_MERGE

INDEX_MERGE & NO_INDEX_MERGE

NO_ICP

SKIP_SCAN & NO_SKIP_SCAN

INDEX & NO_INDEX

책에서 더 자세히 설명이 되어 있으니 따로 적지는 않는다.