- 쿼리 실행 구조 ⭐⭐
- 옵티마이저의 종류 ⭐⭐
- 기본 데이터 처리 ⭐⭐⭐
- ORDER BY 처리(Using filesort) ⭐⭐⭐⭐
- 정렬 처리 방법 ⭐⭐⭐⭐⭐
- GROUP BY 처리 ⭐⭐⭐⭐⭐
- 인덱스를 이용하지 못하고 GROUP BY 실행 ⭐⭐⭐⭐⭐
- DISTINCT 처리 ⭐⭐⭐⭐⭐
- 내부 임시 테이블 활용 ⭐⭐
- 고급 최적화 ⭐⭐⭐
MySQL에서 쿼리를 최적으로 실행하기 위해 각 테이블의 데이터가 어떤 분포로 저장되어 있는지 통계 정보를 참조하여, 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요합니다.
MySQL 서버를 포함한 대부분의 DBMS에서는 옵티마이저가 이러한 기능을 담당합니다.
쿼리 파서- 쿼리 파서는 사용자 요청으로 들어온 쿼리 문장을 토큰(MySQL이 인식할 수 있는 최소 단위의 어휘나 기호)으로 분리해 트리 형태의 구조로 만들어 내는 작업을 의미한다.
- 쿼리 문장의 기본 문법 오류는 이 과정에서 발견되고 사용자에게 오류 메세지를 전달하게 된다.
전처리기- 파서 과정에서 만들어진 파서 트리를 기반으로 쿼리 문장에 구조적인 문제점이 있는지 확인한다.
- 각 토큰을 테이블 이름이나 컬럼 이름, 또는 내장 함수와 같은 개체를 매핑해 해당 객체의 존재 여부와 객체 접근 권한 등을 확인한 과정을 수행한다.
옵티마이저⭐⭐⭐- 사용자의 요청으로 들어온 쿼리 문장을 가장 빠르게 처리할 수 있는 방법을 결정하는 역할을 담당하며, DBMS의 두뇌에 해당합니다.
실행 엔진- 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.
옵티마이저: 회사 경영진⭐⭐- 실행 엔진: 중간 관리자
- 핸들러: 각 업무의 실무자
- 옵티마이저가 두뇌라면 실행 엔진과 핸들러는 손과 발에 비유할 수 있다.
- 옵티마이저가 GROUP BY를 처리하기 위해 임시 테이블을 사용하기로 결정한 상황에 대한 예시
- 실행 엔진이 핸들러에게 임시 테이블을 만들라고 요청
- 다시 실행 엔진은 WHERE 절에 일치하는 레코드를 읽어오라고 핸들러에게 요청
- 읽어온 레코드들을 1번에서 준비한 임시 테이블로 저장하라고 다시 핸들러에게 요청
- 데이터가 준비된 임시 테이블에서 필요한 방식으로 데이터를 읽어 오라고 핸들러에게 다시 요청
- 최종적으로 실행 엔진은 결과를 사용자나 다른 모듈로 넘김
옵티마이저는 데이터베이스 서버에서 두뇌와 같은 역할을 담당합니다.
규칙 기반 최적화: 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다.비용 기반 최적화: 쿼리를 처리하기 위한 여러 가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출한다.
현재는 MySQL을 포함한 대부분의 RDBMS가 비용 기반의 옵티마이저를 채택하고 있습니다.
MySQL 서버를 포함한 모든 RDBMS는 데이터를 정렬하거나 그루핑하는 등의 기본 데이터 가공 기능을 가지고 있는데, 내부적으로 어떤 과정을 거쳐서 데이터를 처리하는지 알아보겠습니다.
풀 테이블 스캔: 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미
MySQL 옵티마이저는 아래와 같은 조건에서 주로 풀 테이블 스캔을 선택합니다.
- 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 하는 편이 더 빠른 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
- WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
- 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 (인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요합니다.
그래서 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블로이나 페이지를 읽어오는 기능을 내장하고 있습니다.
MySQL 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어오는 것으로 생각하는데, 이것은 MyISAM 스토리지 엔진에는 맞는 이야기지만 InnoDB 에서는 틀린 말입니다. ⭐⭐
- InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면
백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다. - 리드 어헤드(Read ahead)란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미함
- 테이블 풀 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
- 리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아니라 풀 인덱스 스캔에서도 동일하게 사용된다.
SELECT COUNT(*) FROM employees;- 전체 개수를 조회하는 쿼리이기 때문에 풀 테이블 스캔을 할 것처럼 보이지만,
실제 실행 계획은 풀 테이블 스캔보다는 풀 인덱스 스캔을 하게 될 가능성이 높다.(단순히 레코드의 건수만 필요로 하는 쿼리라면 용량이 적은 인덱스를 선택하는 것이 디스크 읽기 횟수를 줄일 수 있기 때문) - 일반적으로 인덱스는 테이블의 2 ~ 3개 컬럼만으로 구성되기 때문에 테이블 자체보다는 용량이 작아서 훨씬 빠른 처리가 가능하다.
SELECT * FROM employees;하지만 위와 같이 레코드에만 있는 컬럼이 필요한 쿼리의 경우에는 풀 인덱스 스캔을 활용하지 못하고 풀 테이블 스캔을 합니다.
| -- | 장점 | 단점 |
|---|---|---|
| 인덱스 이용 | INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어서 이 순서대로 읽기만 하면 되어서 매우 빠르다. | INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 디스크 공간 및 버퍼 풀 메모리가 필요하다. |
| Filesort 이용 | 인덱스를 생성하지 않아도 되므로 인덱스의 단점이 장점이다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. | 정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다. |
- 인덱스를 이용한 정렬을 위해서는 반드시 ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. ⭐⭐
- WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다. ⭐⭐
MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트 버퍼(Sort Buffer) 라고 합니다.
소트 버퍼의 공간은 sort_buffer_size 라는 시스템 변수로 설정할 수 있고, 소트 버퍼를 위한 메모리 공간은 쿼리의 실행이 완료되면 즉시 시스템으로 반납됩니다.
show variables where Variable_Name like '%sort_buffer%';위의 명령어로 sort_buffer 사이즈를 획인할 수 있습니다.
- 메모리의 sort buffer에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록합니다.
- 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장합니다.
- 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하는데, 이 작업을
멀티 머지(Multi-merge)라고 합니다.
이러한 작업들이 모두 디스크의 쓰기와 읽기를 유발하기 때문에, 레코드 건수가 많을수록 이 반복 작업의 횟수가 많아져서 좋지 않습니다.
추가로 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐을 의미해서, 소트 버퍼의 크기를 10MB 이상으로 설정하면 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시에 실행되면서 운영체제는 메모리 부족 현상을 겪을 수도 있다고 한다. (Real MySQL - 1 P290 참고)
소트 버퍼에 정렬 기준 컬럼을 포함해 SELECT 대상이 되는 컬럼 전부를 담아서 정렬을 수행하는 정렬 방식입니다.
mysql> SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;
정렬에 필요하지 않은 last_name 컬럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행합니다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨주는 과정을 볼 수 있습니다.
정렬 대상 컬럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리 키로 테이블을 읽어서 SELECT 컬럼을 가져오는 정렬 방식으로, 싱글 패스 정렬 방식이 도입되기 이전부터 사용하던 방식입니다. (하지만 MySQL 8.0에서도 여전히 특정 조건에서는 투 패스(Two-Pass) 정렬 방식을 사용한다.)
디스크에서 정렬에 필요한 first_name 컬럼과 프라이머리 키인 emp_no만 읽어서 정렬을 수행했음을 알 수 있습니다.
정렬이 완료되면 그 결과 순서대로 employees 테이블을 한번 더 읽어서 last_name을 가져오고, 최종적으로 그 결과를 클라이언트 쪽으로 반환합니다.
MySQL 예전 정렬 방식인 투 패스 방식은 테이블을 두 번 읽어야 하기 때문에 비효율적이라 할 수 있는데, 새로운 정렬 방식인 싱글 패스는 테이블을 한번만 읽기 때문에 효율적이라 할 수 있습니다. ⭐⭐
하지만! 싱글 패스 정렬 방식은 더 많은 소트 버퍼 공간이 필요합니다. 즉, 소트 버퍼 공간의 크기와 레코드 크기에 의존적이게 됩니다. ⭐⭐
최신 버전에서는 일반적으로 싱글 패스 정렬 방식을 주료 사용하지만, 최신 버전의 MySQL 서버라고 해서 항상 싱글 패스 정렬 방식을 사용하는 것은 아닙니다.
- 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
- BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때
싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠른 성능을 보이며, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이라 할 수 있습니다. ⭐⭐
SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고, 모든 컬럼(*)을 가져오도록 개발할 때가 많다. 하지만 이는 정렬 버퍼를 몇 배에서 몇십 배 까지 비효율적으로 사용할 가능성이 크다. SELECT 쿼리에서 꼭 필요한 컬럼만 조회하도록 쿼리를 작성하는 것이 좋다고 권장하는 것은 바로 위와 같은 이유 때문이다. 특히 정렬이 필요한 SELECT는 불필요한 컬럼을 SELECT 하지 않게 쿼리를 작성하는 것이 효율적이다. 정렬 버퍼에만 영향을 미치는 것이 아니라 임시 테이블이 필요한 쿼리에서도 영향을 미친다. ⭐⭐⭐
| 정렬 처리 방법 | 실행 계획의 Extra 컬럼 내용 |
|---|---|
| 인덱스를 사용한 정렬 | 별도 표기 없음 |
| 조인에서 드라이빙 테이블만 정렬 | "Using filesort" 메세지가 표시됨 |
| 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 | "Using temporary; Using filesort" 메세지가 표시됨 |
쿼리에 ORDER BY가 사용되면 반드시 3가지 처리 방법 중 하나로 정렬이 처리됩니다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 떨어집니다.
- ORDER BY에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 함
- WHERE 절에 첫 번째로 읽는 테이블의 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
인덱스를 이용한 정렬을 위해서는 반드시 위의 2가지 조건이 필요합니다.
mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_mo=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020
ORDER BY e.emp_no;// emp_no 컬럼으로 정렬이 필요한데, 인덱스를 사용하면서 자동으로 정렬이 된다고 ORDER BY 절을 제거하는 것은 좋지 않은 선택이다.
mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_mo=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100020인덱스를 이용해 정렬이 처리되는 경우에는 실제 인덱스의 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 됩니다. (B-Tree 인덱스가 키 값으로 정렬되어 있기 때문에 가능하다.)
ORDER BY 절을 포함하면 MySQL 서버가 별도로 정렬 작업을 한 번 더 할까봐 걱정스러워서 안넣는 경우가 있는데, MySQL 서버는 정렬을 인덱스로 처리할 수 있는 경우 부가적으로 불필요한 정렬 작업을 수행하지 않는다.
mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY e.last_name;조인이 수행되면 결과 레코드의 건수가 몇 배로 들어나기 때문에 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬에 효율적일 것입니다.
이러한 방법이 수행되려면 조인에서 첫 번째로 읽히는 테이블(드라이빙 테이블)의 컬럼만으로 ORDER BY 절을 작성해야 합니다.
ORDER BY에 존재하는 컬럼은 드라이빙 테이블의 컬럼이긴 한데 인덱스가 존재하는 컬럼이 아니기 때문에 인덱스를 이용해서 정렬하는 것은 불가능합니다.
- 인덱스를 이용해
e.emp_no BETWEEN 100002 AND 100010 조건을 만족하는 9건을 검색 - 검색 결과를 last_name 컬럼으로 정렬을 수행(Filesort)
- 정렬된 결과를 순서대로 읽으면서 salaries 테이블과 조인을 수행해 최종 결과를 가져옴
mysql> SELECT *
FROM employees e, salaries s
WHERE s.emp_no=e.emp_no
AND e.emp_no BETWEEN 100002 AND 100010
ORDER BY s.salary;임시 테이블을 이용한 정렬이 3가지 방법 중에 가장 느린 방법입니다. 이번 쿼리에서는 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블이 아니라 드리븐 테이블(salaries)에 있는 컬럼입니다.
즉, 정렬이 수행되기 전에 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬할 수 밖에 없습니다. ⭐⭐
쿼리의 실행 계획을 보면 Extra 컬럼에 "Using temporary; Using filesort" 라는 커멘트가 표시됩니다. 이는 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬 처리했음을 의미합니다.
- ORDER BY나 GROUP BY 같은 작업은 WHERE 조건을 만족하는 레코드를 LIMIT 건수 만큼만 가져와서는 처리할 수 없다.
- 우선 조건을 만족하는 레코드를 모두 가져와서 정렬을 수행하거나 그룹 작업을 실행해야만 비로소 LIMIT 으로 건수를 제한할 수 있다.
- WHERE 조건이 아무리 인덱스를 잘 활용하도록 튜닝해도 잘못된 ORDER BY나 GROUP BY 때문에 쿼리가 느려지는 경우가 자주 발생한다.
쿼리에서 인덱스를 사용하지 못하는 정렬이나 그룹 작업이 왜 느리게 작동할 수 밖에 없는지 알아보겠습니다.
서버 쪽에서 처리할 데이터가 얼마인지에 관계 없이 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 것을 의미합니다.
스트리밍 방식으로 처리되는 쿼리는 쿼리가 얼마나 많은 레코드를 조회하냐에 상관없이 빠른 응답 속도를 보장해줍니다.
대표적으로 스트리밍 방식은 인덱스를 사용한 정렬 방식인데, 여기에 LIMIT 쿼리까지 같이 사용한다면 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있습니다.
ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 합니다. WHERE 조건에 일치하는 모든 레코드를 가져온 후, 정렬하거나 그룹핑해서 차례대로 보내야 하기 때문입니다.
MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무 것도 하지 않고 기다려야 하기 때문에 응답 속도는 느려집니다.
- 인덱스를 사용한 정렬
- 조인에서 드라이빙 테이블만 정렬 (
Using filesort) - 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 (
Using temporary; Using filesort)
ORDER BY의 3가지 처리 방법 가운데 인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후의 정렬됩니다.
위에서 설명한 스트리밍 방식의 예시인 인덱스를 사용한 정렬 방식은 LIMIT으로 제한된 건수만큼만 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있습니다. ⭐⭐
하지만 인덱스를 사용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT 으로 제한된 건수만큼 잘라서 클라이언트로 전송해줄 수 있음을 의미합니다. 이러한 이유 떄문에 ORDER BY, GROUP BY가 인덱스를 활용하지 못했을 때 느린 것입니다. ⭐⭐
mysql> SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1=t2.col1
ORDER BY t1.col2
LIMIT 10;tb_test1: 레코드 100건tb_test2: 레코드 1,000건 (tb_test1의 레코드 1건당 tb_test2의 레코드가 10건씩 존재한다 가정)- 두 테이블의 조인 결과 전체 1,000건
| 정렬 방법 | 읽어야 할 건수 | 조인 횟수 | 정렬해야 할 대상 건수 | 상황 |
|---|---|---|---|---|
| 인덱스 사용 | tb_test1: 1건 tb_test2: 10건 |
1번 | 0건 | ORDER BY col2 인덱스 컬럼일 때 |
| 조인의 드라이빙 테이블만 정렬 | tb_test1: 100건 tb_test2: 10건 |
1번 | 100건 (tb_test1 테이블의 레코드 건수만큼 정렬 필요) | ORDER BY col2 인덱스 컬럼이 아닐 때 |
| 임시 테이블 사용 후 정렬 | tb_test1: 100건, tb_test2: 1000건 | 100번 (tb_test1 테이블의 레코드 건수만큼 조인 발생) | 1,000건 (조인된 결과 레코드 건수를 전부 정렬해야 함) | ORDER BY col2 드라이빙 테이블(tb_test1)이 아닌 드리븐 (tb_test2) 컬럼일 때 |
- tb_test1이 드라이빙 되는 경우
| 정렬 방법 | 읽어야 할 건수 | 조인 횟수 | 정렬해야 할 대상 건수 | 상황 |
|---|---|---|---|---|
| 인덱스 사용 | tb_test1: 10건 tb_test2: 10건 |
10번 | 0건 | ORDER BY col2 인덱스 컬럼일 때 |
| 조인의 드라이빙 테이블만 정렬 | tb_test1: 100건 tb_test2: 10건 |
10번 | 100건 (tb_test2 테이블의 레코드 건수만큼 정렬 필요) | ORDER BY col2 인덱스 컬럼이 아닐 때 |
| 임시 테이블 사용 후 정렬 | tb_test1: 1000건, tb_test2: 100건 | 1,000번 (tb_test2 테이블의 레코드 건수만큼 조인 발생) | 1,000건 (조인된 결과 레코드 건수를 전부 정렬해야 함) | ORDER BY col2 드라이빙 테이블(tb_test2)이 아닌 드리븐 (tb_test1) 컬럼일 때 |
- tb_test2이 드라이빙 되는 경우
어느 테이블이 드라이빙 되어 조인 되는지도 중요하지만 어떤 정렬 방식으로 처리되는지는 더 큰 성능 차이를 만듭니다. 가능하다면 인덱스를 사용하여 정렬하도록 유도하고, 그렇지 못하다면 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도하는 것도 좋은 튜닝 방법입니다. ⭐⭐
- WHERE 절이 인덱스 사용할 수 있는가?
- GROUP BY 절이 인덱스 사용할 수 있는가?
- GROUP 절과 ORDER BY 절이 동시에 인덱스 사용할 수 있는가?
위의 3가지 질문에 대해서 WHERE, GROUP BY, ORDER BY 인덱스 조건에 대한 Flow Chart를 보면 위와 같습니다.
GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리 중 하나입니다.
참고로 GROUP BY 절이 있는 쿼리에서는 HAVING 절을 사용하여 GROUP BY 결과를 필터링 역할을 하는데, GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절을 튜닝하려고 인덱스를 생성할 필요는 없습니다.
- 인덱스를 이용하여 GROUP BY 실행
- 인덱스 스캔 방법 (인덱스를 차례대로 읽음)
- 루스 인덱스 스캔 (인덱스를 건너뛰면서 읽음)
- 인덱스를 이용하지 못하고 GROUP BY 실행
- 임시 테이블 생성
GROUP BY를 사용했을 때 인덱스를 사용했을 때 사용하지 못했을 때 2가지 경우로 볼 수 있습니다.
인덱스 스캔 (인덱스를 차례대로 읽음)- 쿼리 조건에 따라 전체 인덱스 스캔 또는 범위 인덱스 스캔 중 하나임
- 실행 계획 Extra 컬럼에
Using index for group-by표시
루스 인덱스 스캔 (인덱스를 건너뛰면서 읽음)- 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어오는 방식
- 실행 계획 Extra 컬럼에
Using index for group-by표시 - 단일 테이블에 대해 수행되는 GROUP BY 처리에만 사용할 수 있음
GROUP BY 인덱스를 이용할 때는 위처럼 2가지 상황이 존재할 수 있습니다.(참고 Link)
index idx(c1, c2, c3) on table t1(c1, c2, c3, c4)테이블에 c1, c2, c3 순서로 생성되어 있는 인덱스가 있다고 가정하고 예시를 보면서 알아보겠습니다.
SELECT * FROM t1 WHERE c1 = 'c1' GROUP BY c2, c3 # 인덱스 사용 가능
SELECT * FROM t1 WHERE c1 = 'c1' AND c2 = 'c2' GROUP BY c3 # 인덱스 사용 가능- WHERE 조건 인덱스 사용 가능
- GROUP BY 인덱스 사용 가능
위의 두 쿼리 예시를 보면 Flow Chart에서 볼 수 있듯이 WHERE, GROUP BY 에서 사용하는 컬럼이 인덱스에서 생성된 순서대로 컬럼을 사용하고 있어서 인덱스를 모두 이용할 수 있습니다.
그리고 WHERE 조건은 인덱스 첫 번째 칼럼으로 한 번 걸려졌기 때문에 GROUP BY 절에는 인덱스 두 번째 컬럼부터 사용해도 인덱스를 사용할 수 있다는 특징을 가지고 있습니다.
SELECT * FROM t1 GROUP BY c1 # 인덱스 사용 가능
SELECT * FROM t1 GROUP BY c1, c2 # 인덱스 사용 가능
SELECT * FROM t1 GROUP BY c1, c2, c3 # 인덱스 사용 가능GROUP BY 조건만 사용할 때도 마찬가지로 인덱스 생성 순서대로 컬럼을 사용하고 있기 때문에 모두 인덱스 사용이 가능합니다.
SELECT * FROM t1 GROUP BY c2, c1 # 순서 불일치, 인덱스 사용 불가능
SELECT * FROM t1 GROUP BY c1, c3, c2 # 순서 불일치, 인덱스 사용 불가능
SELECT * FROM t1 GROUP BY c1, c3 # 순서는 일치하나, c2가 누락되서 인덱스 사용 불가능
SELECT * FROM t1 GROUP BY c1, c2, c3, c4 # c4는 인덱스에 들어있지 않아서 인덱스 사용 불가능위와 같은 특징에 대해서도 참고하면 좋을 것 같습니다.
GROUP BY 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못할 때 임시 테이블 방식이 사용됩니다.
임시 테이블을 사용할 때 쿼리의 실행 계획을 보면 Extra 컬럼에 Using temporary;와 같이 나오는 것을 확인할 수 있습니다.
그런데 제가 테스트 해보았던 쿼리의 실행 계획에서는 Using temporary; Using filesort 와 같이 filesort도 나타난 것을 보았는데요.
GROUP BY가 인덱스를 사용하지 못해서 임시 테이블(Using temporary)을 생성한 것은 알겠는데, ORDER BY를 사용하지 않았는데 Filesort 정렬이 왜 일어나는 것일까 라는 생각이 들었는데요.
MySQL의 GROUP BY는 ORDER BY 칼럼에 대한 정렬까지 함께 수행하는 것이 기본 작동 방식이므로 GROUP BY와 ORDER BY 칼럼이 내용과 순서가 같은 쿼리에서는 ORDER BY 절을 생략해도 같은 결과를 얻게 된다.
MySQL 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그룹핑 되는 컬럼을 기준으로 묵시적인 정렬까지 수행되었지만, MySQL 8.0 버전 부터는 이 값은 묵시적인 정렬은 더 이상 수행되지 않는다.
관련하여 좀 더 찾아보니 MySQL에서 GROUP BY는 위와 같은 특징을 가지고 있어서 ORDER BY가 디폴트로 실행된 것 같습니다. (테스트 한 MySQL 버전은 5.7.33)
특정 컬럼의 유니크한 값만 조회할 때 SELECT 쿼리에 DISTINCT를 사용합니다.
- 집합 함수가 없는 경우
- MIN(), MAX(), COUNT() 집합 함수와 함께 사용되는 경우
DISTINCT 키워드가 2가지 상황에 따라 다르게 동작하기 때문에 각각 상황에 대해서 정리해보겠습니다.
그리고 집합 함수와 같이 DISTINCT가 사용되는 쿼리의 실행 계획에서 DISTINCT 처리가 인덱스를 사용하지 못할 때는 항상 임시 테이블 생성이 필요합니다. 하지만 실행 계획 Extra 컬럼에서 Using temporary 메세지가 출력되지 않습니다.
SELECT 쿼리에서 유니크한 레코드만 가져오고자 할 때 사용합니다.
SELECT DISTINCT emp_no FROM salaries;SELECT emp_no FROM salaries GROUP BY emp_no;MySQL 8.0 버전 부터는 GROUP BY를 수행하는 쿼리에 ORDER BY 절이 없으면 정렬을 사용하지 않기 때문에 위의 두 쿼리는 내부적으로 같은 작업을 수행합니다.
SELECT DISTINCT first_name, last_name FROM salaries;SELECT DISTINCT(first_name), last_name FROM salaries;DISTINCT는 SELECT 하는 레코드를 유니크하게 가져오는 것이지, 특정 컬럼만 유니크하게 조회하는 것이 아닙니다.
즉, 두 번째 쿼리도 first_name만 유니크하게 가져오는 것이 아니라 (first_name, last_name)가 유니크한 컬럼을 가져오는 것입니다.
EXPLAIN SELECT COUNT(DISTINCT salary) FROM salaries;위의 쿼리는 COUNT(DISTINCT salary)를 처리하기 위해 임시 테이블을 사용합니다. 임시 테이블의 salary 컬럼에는 유니크 인덱스가 생성되기 때문에 레코드 건수가 많아진다면 상당히 느려질 수 있는 형태의 쿼리입니다.
EXPLAIN SELECT COUNT(DISTINCT salary), COUNT(DISTINCT last_name) FROM salaries;COUNT() 함수가 두 번 사용된 쿼리는 2개의 임시 테이블을 사용합니다.
EXPLAIN SELECT COUNT(DISTINCT emp_no) FROM employees;DISTINCT를 사용할 때 인덱스를 이용할 수 없으면 임시 테이블을 사용하지만, 인덱스 컬럼에 대해서 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블 없이 최적화 처리를 하게 됩니다.
MySQL 엔진이 스토리이지 엔진으로부터 받아온 레코드를 정렬하거나 그룹핑할 때는 내부적인 임시 테이블(Internal temporary table)을 사용합니다.
일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크키가 커지면 디스크로 옮겨집니다. 그리고 쿼리 처리가 완료되면 임시 테이블은 자동으로 삭제됩니다.
MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 이용합니다.
하지만 MySQL 8.0 버전 부터는 TempTable 이라는 스토리지 엔진을 사용하고, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선 되었습니다.
- ORDER BY와 GROUP BY에 명시된 컬럼이 다른 쿼리
- ORDER BY나 GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리 ⭐⭐
- DISTINCT와 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
- UNION 이나 UNION DISTINCT가 사용된 쿼리(select type 컬림이 UNION RESULT인 경우)
- 쿼리의 실행 계획에서 select type이 DERIVED인 쿼리
위의 경우는 실행 계획 Extra 컬럼에 Using temporary 라는 메세지가 표시되는 것을 볼 수 있습니다. 하지만 Using temporary가 표시되지 않아도 임시 테이블을 사용할 수 있는데, 4 ~ 6번의 경우가 이러한 예시 입니다.
1 ~ 3번의 경우 유니크 인덱스를 가지는 내부적인 임시 테이블 생성3 ~ 6번의 경우 실행 계획 Extra 컬럼에 Using temporary가 표시되지 않지만 임시 테이블을 생성하여 사용- 6번의 쿼리 패턴은 유니크 인덱스가 없는 내부 임시 테이블이 생성
- 일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 그렇지 않은 쿼리보다 성능이 상당히 느림
- UNION 이나 UNION ALL에서 SELECT 되는 컬럼 중에서 길이가 512바이트 이상인 크기의 컬럼이 있는 경우
- GROUP BY나 DISTINCT 컬럼에서 512 바이트 이상인 크기의 컬럼이 있는 경우
- 메모리 임시 테이블의 크기가 (MEMORY 스토리지 엔진에서) temp_table_size 또는 max_heap_table_size 시스템 변수보다 크거나 (TempTable 스토리지 엔진에서) temptable_max_ram 시스템 변수 값보다 큰 경우
내부 임시 테이블은 기본적으로 메모리상에 만들어지지만 위의 조건을 만족하면 메모리 임시 테이블을 사용할 수 없게 되고 디스크 기반의 임시 테이블을 사용하게 됩니다.
MySQL 8.0.13 이전 버전까지는 BLOB 이나 TEXT 컬럼을 가진 경우, 임시 테이블을 메모리에 생성하지 못하고 디스크에 생성했다. 하지만 MySQL 8.0.13 버전부터는 BLOB이나 TEXT 컬럼을 가진 임시 테이블에 대해서도 메모리에 임시 테이블을 생성할 수 있게 개선됐다. 하지만 메모리 임시 테이블이 Temptable 스토리지 엔진이 아니라 MEMORY 스토리지 엔진을 사용하는 경우에는 여전히 디스크 임시 테이블을 사용합니다.
MRR은 Multi-Range-Read를 라고 합니다.
MySQL 서버에서 지금까지 지원하던 조인 방식은 드라이빙 테이블(조인에서 제일 먼저 읽는 테이블)의 레코드를 한 건 읽어서 드리븐 테이블(조인되는 테이블에서 드라이빙이 아닌 테이블들)의 일치하는 레코드를 찾아서 조인했습니다. 이를 네스티드 루프 조인(Nested Loop Join) 이라고 합니다.
MySQL 엔진: 조인 처리스토리지 엔진: 실제 레코드를 검색하고 읽는 부분
이 때 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 수행할 수 없었습니다.
이러한 단점을 보완하기 위해 MySQL 서버는 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링합니다. 즉, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링하는 것입니다. ⭐
조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청하여, 스토리지 엔진은 읽어야 할 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화할 수 있는 것입니다. ⭐
MySQL 서버에서 사용되는 다부분의 조인은 네스티드 루프 조인(Nested Loop Join) 인데, 조인의 연결 조건이 되는 컬럼에 모두 인덱스가 있는 경우 사용되는 조인 방식입니다.
- 조인 버퍼(join_buffer_size 시스템 설정으로 조정되는 조인을 위한 버퍼)가 사용되는지 여부
- 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되는지 여부
네스티드 루프 조인과 블록 네스티드 루프 조인(Block Nested Loop Join)의 가장 큰 차이는 위의 2가지 입니다.
Join 쿼리의 실행 계획에서 Extra 컬럼에 "Using Join buffer" 라는 문구가 표시되면 조인 버퍼를 사용한다는 것을 의미합니다.
조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리됩니다. 즉, 드라이빙 테이블은 한 번에 쭉 읽지만, 드리븐 테이블은 여러 번 읽는다는 것을 의미합니다. ⭐⭐
예를들어, 드라이빙 테이블에서 일치하는 레코드가 1,000건 이었는데, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기 위해 1,000번의 풀 테이블 스캔을 해야 합니다. ⭐⭐
어떤 방식으로든 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인하는 형태로 처리한다. 이 때 사용되는 메모리의 캐시를 조인 버퍼(Join buffer) 라고 한다. ⭐
일반적으로 조인이 수행된 후 가져오는 결과는 드라이빙 테이블의 순서에 의해 결정되지만, 조인 버퍼가 사용되는 조인에서는 결과의 정렬 순서가 흐트러질 수 있음을 기억해야 합니다.
MySQL 8.0.18 버전부터는 해시 조인 알고리즘이 도입됐으며, MySQL 8.0.20 버전부터는 블록 네스티드 루프 조인은 더 이상 사용되지 않고 해시 조인 알고리즘이 대체되어 사용된다.
MySQL 5.6 버전부터는 인덱스 컨디션 푸시다운(Index Condition Pushdown) 이라는 기능이 도입 되었는데요. 어떤 내용인지 알아보겠습니다.
- secondary index에만 사용됩니다.
- 전체 row 읽기의 수를 줄여 I/O 작업을 줄이는 것.
- 인덱스를 범위 제한 조건으로 사용하지 못하는 쿼리에 한해서 발생하는 것 같음 (
LIKE %name%,like > 10같이 인덱스를 이용하지 못하는 범위 검색을 의미하는 것 같음) - InnoDB Clustered index의 경우, 전체 레코드가 InnoDB 버퍼에 존재하기 때문에, 인덱스 컨디션 푸시다운을 사용해도 I/O가 감소하지 않음
MySQL 엔진: 스토리지 엔진에서 받은 데이터 가공 처리 역할 (쿼리의 최적화된 실행을 위한 옵티마이저가 중심)스토리지 엔진: 실제 데이터를 디스크 스토리지에 저장하거나 디스크 스토리지로부터 데이터를 읽어오는 부분 담당 (대표적으로 인덱스를 비교하는 담당)
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------------------+
| 1 | SIMPLE | c | NULL | index | idx_last_name_address | idx_last_name_address | 10 | NULL | 10 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+-----------------------+-----------------------+---------+------+------+----------+-------------------------+
인덱스 푸시다운은 쿼리의 실행 계획 extra 컬럼에서 Using index condition로 표시되는데요. 어떤 상황에서 발생하는지 확인 해보겠습니다.
SET optimizer_switch='index_condition_pushdown=on';
ALTER TABLE people ADD INDEX idx_last_name_address (zipcode, lastname, address)SELECT * FROM people
WHERE zipcode='95054'
AND lastname LIKE '%sal'
AND address LIKE '%Main Street%';만약에 인덱스 컨디션 푸시다운이 없을 때 위의 쿼리가 실행된다면 어떻게 실행 될까요?
zipcode=95054에 해당하는 값들을 스토리지 엔진을 통해서 읽어옴 (idx_last_name_address인덱스를 통해서 데이터 파일에 접근하여 데이터를 읽어옴)lastname LIKE '%sal'에 해당하는 조건을 MySQL 엔진에서 필터링 함- last_name이
Marsja,Masaki같이 굳이 테이블을 읽지 않아도 되는 데이터도 데이터 파일에 접근하여 불필요한 Disk I/O 발생 만약에 10만건 읽고 필터링 되어서 1건 남았다면 99,999건의 불필요한 Disk I/O 발생
- last_name이
여기서 2번 과정을 보면 비효율적이라는게 보이는데요.
비효율적인 이유가 무엇이냐면 이미 1번 과정(zipcode=95054)에서 인덱스 파일을 읽어서 데이터 파일에 접근하여 데이터를 가져온 것을 볼 수 있습니다. (스토리지 엔진에서 인덱스 사용하여 디스크 파일에 접근하여 데이터를 MySQL 엔진으로 반환했음)
MySQL 엔진에서는 굳이 3건 모두 디스크 파일에 접근하지 않더라도 스토리지 엔진에서 반환한 값을 보면 lastname LIKE '%sal' 해당하는 결과가 1건이라는 것을 알 수 있습니다.
즉, 1건만 디스크에 접근해서 가져오면 되고 굳이 필터링 되어 버려진 값들에 대해서 모두 불필요하게 Disk 접근을 할 필요가 없다는 뜻입니다.
하지만 MySQL 5.5 버전까지는 인덱스를 범위 제한 조건으로 사용하지 못하는 lastname 조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달하지 못한다고 합니다.
MySQL 엔진 (핸들러 API 사용)-> 스토리지 엔진 -> 디스크 파일
정리하면 MySQL 엔진에서 lastname 조건 처럼 인덱스를 사용하지 못하는 범위 검색의 경우 핸들러 API에서 스토리지 엔진으로 넘겨주지 않기 때문에 스토리지 엔진의 입장에서는 모두 디스크 파일에 접근하여 I/O를 발생시킬 수 밖에 없었던 것입니다.
그래서 MySQL 5.6 버전부터는 인덱스를 사용하지 못하는 범위 검색이라 하더라도 모두 같이 스토리지 엔진으로 전달할 수 있게 핸들러 API가 개선되어, 위처럼 스토리지 엔진에서 MySQL 엔진으로 부터 받은 필터링 조건으로 불필요한 Disk I/O를 줄이는 것을 볼 수 있습니다.
핸들러 API: MySQL 엔진의 쿼리 실행기에서 데이터를 쓰거나 읽어야 할 때는 각 스토리지 엔진에 쓰기 또는 읽기를 요청 API
SET optimizer_switch='index_condition_pushdown=off';
SET optimizer_switch='index_condition_pushdown=on';위의 명령어로 인덱스 푸시 다운 설정을 on/off 할 수 있습니다.
MySQL 8.0.18 버전부터는 해시 조인이 추가로 지원되기 시작했습니다.
A 지점: 쿼리가 실행되면서 MySQL 서버가 첫 번째 레코드를 찾아낸 시점B 지점: MySQL 서버가 마지막 레코드를 찾아낸 시점
해시 조인은 첫 번째 레코드를 찾는 데는 시간이 많지 걸리지만, 최종 레코드를 찾는 데까지는 시간이 많이 걸리지 않음을 알 수 있습니다. ⭐
네스티드 루프 조인은 마지막 레코드를 찾는 데까지는 시간이 많이 걸리지만 첫 번째 레코드를 찾는 것은 상대적으로 훨씬 빠르다는 것을 알 수 있습니다. ⭐
일반적으로 해시 조인은 빌드 단계(Build-phase)와 프로브 단계(Probe-phase)로 나뉘어 처리됩니다.
빌드 단계: 조인 대상 테이블 중에서 레코드 건수가 적어서 해시 테이블로 만들기에 용이한 테이블을 골라서 메모리에 해시 테이블을 생성(빌드)하는 작업을 수행한다. (빌드 단계에서 해시 테이블을 만들 때 사용되는 원본 테이블을 빌드 테이블이라고도 한다.)프로브 단계: 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는 과정을 진행한다. (읽는 나머지 테이블을 프로브 테이블 이라고도 한다.)
해시 조인이 모두 메모리에서 처리가 가능하다면 위와 같이 진행됩니다.
해시 테이블을 메모리에 저장할 때 MySQL 서버는 join_buffer_size 시스템 변수로 크기를 제어할 수 있는 조인 버퍼를 사용합니다.
조인 버퍼의 기본 크기는 256KB인데, 해시 테이블의 레코드 건수가 많아서 조인 버퍼의 공간이 부족할 수도 있습니다. 이러한 경우 MySQL 서버는 빌드 테이블과 프로브 테이블을 하나의 청크가 조인 버퍼보다 작도로 청크로 분리한 다음, 해시 조인을 처리합니다. ⭐
위와 같이 진행하면 1차 조인 결과를 생성합니다.
1차 조인이 완료되면 MySQL 서버는 디스크에 저장된 빌드 테이블 청크에서 첫 번째 청크를 읽어서 다시 메모리 해시 테이블을 구축합니다.
그리고 새로 구축된 메모리 해시 테이블과 프로브 테이블과 조인을 수행해서 2차 결과를 가져오고, 청크의 개수만큼 이 과정을 반복하여 처리합니다.
인덱스를 이용한 정렬을 사용하기 위해서는 반드시 ORDER BY에 명시된 컬럼이 나중에 읽는 테이블(드리븐 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다. (O / X)
GROUP BY 쿼리에서 인덱스를 사용하지 못하면 임시 테이블을 사용한다. (O / X)
SELECT DISTINCT first_name, last_name FROM employees;- 위의 쿼리는 first_name 컬럼만 중복 제거하여 결과를 가져온다. (O / X)
- Real MySQL - 1
- https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_sort_buffer_size
- https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html
- https://dev.mysql.com/doc/refman/8.0/en/group-by-optimization.html
- https://dev.mysql.com/doc/refman/8.0/en/distinct-optimization.html
- https://jojoldu.tistory.com/474