Mysql - 옵티마이저

쿼리 실행 절차

---

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

 

첫 번째 단계

첫 번째 단계를 "SQL 파싱" 이라고 하며, MySQL 서버의 "SQL 파서" 라는 모듈로 처리합니다. SQL 문장이 잘못되었다면 이 단계에서 걸려집니다. 또한 이 단계에서 "SQL 파스 트리"가 만들어집니다. MySQL 서버는 SQL문장 자체가 아닌 SQL 파스 트리를 이용해 쿼리를 실행합니다.

두 번째 단계

두번째 단계에서는 첫 번째 단계에서 만들어진 SQL 파스 트리를 참조하면서 아래와 같은 내용을 처리합니다. 또한 최적화 및 실행 계획 수립 단계이며, MySQL 서버의 옵티마이저에서 처리합니다. 두번째 단계가 완료되면 실행 계획이 만들어집니다.

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

세 번째 단계

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

 

 

옵티마이저의 종류

---

규칙 기반 최적화

• 기본적으로 테이블의 레코드나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식입니다.
• 이 방식에서는 통계 정보(테이블의 레코드 수나 컬럼값의 분포도)를 조사하지 않고 실행 계획이 수립되기 때문에 같은 쿼리에 대해서는 거의 항상 같은 실행 방법을 만들어 냅니다.

비용 기반 최적화

• 쿼리를 처리하기 위한 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출합니다. 이렇게 산출된 실행 방법별로 비용이 최소로 소요되는 처리 방식을 선택해 쿼리를 실행합니다.

 

기본 데이터 처리

---

풀 테이블 스캔

• 풀 테이블 스캔은 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 작업을 의미합니다.
• 그럼 어떠한 상황일 때 MySQL 옵티마이저는 풀 테이블 스캔을 사용할까요?
  1. 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔을 사용하는 것이 더 빠른 경우에 사용됩니다.
  2. WHERE절이나 ON절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우에 사용됩니다.
  3. 인덱스 레인지 스캔을 사용할 수 있는 쿼리라하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우에 사용됩니다.

---

🤭

또한 일반적으로 테이블의 전체 크기는 인덱스보다 훨씬 크기 때문에 테이블을 처음부터 끝까지 읽는 작업은 상당히 많은 디스크 읽기가 필요합니다. 그래서 대부분의 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있습니다. 하지만 MySQL에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 존재하지 않습니다. 그래서 많은 사람들이 MySQL은 풀 테이블 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어오는 것으로 생각하고 있지만 이것은 MyISAM 스토리지 엔진에는 맞는 말이지만 InnoDB 스토리지 엔진에서는 틀린 말입니다. InnoDB 스토리지 엔진은 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드가 발생합니다.

 

리드 어헤드

• 이드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기전에 미리 디스크에서 읽어 InnoDB의 버퍼풀에 가져다 두는 것을 의미합니다. 즉 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘깁니다. 백그라운드 스레드가 읽기를 넘겨받는 시점부터는 한번에 4개 또는 8개씩 페이지를 읽으면서 계속 그 수를 증가시킵니다. 이때 한번에 최대 64개의 데이터 페이지까지 읽어서 버퍼풀에 저장해둡니다. 이렇게 되면 포그라운드 스레드는 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨라 집니다.
• 리드 어헤드는 풀 테이블 스캔에서만 사용되는 것이 아닌 풀 인덱스 스캔에서도 동일하게 사용됩니다.

 

ORDER BY(Using filesort)

---

인덱스를 이용한 ORDER BY

장점

• INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어서 순서대로 읽기만 하면 되므로 빠릅니다.

단점

• INSERT, UPDATE, DELETE 작업시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느립니다.
• 인덱스로 인하여 디스크 공간이 추가적으로 필요합니다.
• 인덱스의 갯수가 늘어날수록 InnoDB의 버퍼풀을 위한 메모리가 증가합니다.

File Sort를 이용한 ORDER BY

장점

• 인덱스를 생성하지 않아도 되므로 인덱스를 이용할때의 단점이 장점이 됩니다.
• 정렬해야할 레코드가 적은 경우에는 메모리에서 file sort가 처리되므로 충분히 빠릅니다.

단점

• 정렬 작업시 쿼리 실행시 처리되므로 레코드가 많아질수록 쿼리 응답 속도가 느려집니다.

💡 File Sort를 사용하는 경우

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

 

소트 버퍼

• MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데 이 메모리 공간을 소트 버퍼라고 합니다. 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있습니다.
• 만약 정렬해야할 레코드 수가 소트 버퍼로 할당된 공간보다 크다면 어떻게 될까? 이때 MySQL은 정렬해야할 레코드를 여러 조각으로 나눠서 처리하는데 이 과정에서 임시 저장을 위한 디스크를 사용하게 됩니다.
• 메모리의 소트 버퍼에서 정렬을 수행하고 그 결과를 임시로 디스크에 기록해 둡니다. 그리고 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장합니다. 이처럼 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행하게 됩니다. 이러한 병합 과정을 멀티 머지라고 표현합니다.
• MySQL은 글로벌 메모리 영역과 세션 메모리 영역으로 나누어 생각할 수 있는데 정렬을 위해 할당되는 소트 버퍼는 세션 메모리 영역에 해당됩니다. 즉 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 역역이 아니기 때문에 커넥션이 많으면 많을수록 정렬 작업이 많으면 많을수록 소트 버퍼로 소비되는 메모리 공간이 커지게 됩니다.

 

정렬 알고리즘

💡 싱글 패스

• employess 테이블을 읽을 때 정렬에 필요하지 않은 last_name 컬럼까지 전부 읽어서 소트 버퍼에 담고 정렬을 수행합니다. 그리고 정렬이 완료되면 정렬 버퍼의 내용을 그대로 클라이언트로 넘겨줍니다.

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

 

 

💡 투 패스

• employess 테이블을 읽을 때 정렬에 필요한 first_name 컬럼과 프라이머리 키인 emp_no만 읽어서 정렬을 수행합니다. 이 정렬이 완료되면 그 결과 순서대로 employess 테이블을 한번 더 읽어서 last_name을 가져오고, 최종적으로 그 결과를 클라이언트에게 넘겨줍니다.
• 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때 사용됩니다.
• BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때 사용됩니다.

 

---

🤭

필자는 이전까지 * 을 사용하여 모든 컬럼을 가져오는 SQL문을 종종 사용하곤 했습니다. 하지만 이는 소트 버퍼를 몇 배에서 몇십 배까지 비효율적으로 사용할 가능성이 큽니다. 그래서 SELECT 쿼리에서 꼭 필요한 컬럼만 조회하도록 작성하는 것이 좋습니다.

mysql > SELECT * FROM members

 

정렬 처리 방법

쿼리에 ORDER BY가 사용되면 반드시 다음 3가지 처리 방법 중 하나로 정렬이 수행됩니다. 일반적으로 아래쪽에 있는 정렬 방법으로 갈수록 처리 속도는 느려집니다.

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

 

💡 인덱스를 사용한 정렬

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

mysql > SELECT * FROM employess e, salaries s
        WHERE s.emp_no = e.emp_no
        AND e.emp_no BETWEEN 100002 AND 100020
        ORDER BY e.emp_no;

 

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

• 일반적으로 조인이 수행되면 결과 레코드의 건수가 몇 배로 불어나고 레코드 하나하나의 크기도 늘어납니다. 그래서 조인을 실행하기 전에 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인을 실행하는 것이 정렬의 차선책이 될 것입니다. 이 방법은 조인에서 첫 번째로 읽히는 테이블의 컬럼만으로 ORDER BY절을 작성해야 합니다.
• 아래 SQL 문장을 보면 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블(employess)에 포함된 컬럼임을 알 수 있습니다. 옵티마이저는 드라이빙 테이블만 검색해서 정렬을 먼저 수행하고 그 결과와 salaries 테이블과 조인하게 됩니다.

mysql > SELECT * FROM employess e, salaries s
        WHERE s.emp_no = e.emp_no
        AND e.emp_no BETWEEN 100002 AND 100020
        ORDER BY e.last_name;

💡 임시 테이블을 이용한 정렬

• 조인의 드라이빙 테이블만 정렬은 2개 이상의 테이블이 조인되면서 정렬이 실행되지만 임시 테이블을 사용하지 않습니다. 하지만 그 외 패턴의 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거칩니다.
• 아래 SQL 문장을 보면 ORDER BY 절의 정렬 기준 컬럼이 드라이빙 테이블이 아닌 드리븐 테이블(salaries)에 있는 컬럼입니다. 즉 정렬이 수행되기 전 salaries 테이블을 읽어야 하므로 이 쿼리는 조인된 데이터를 가지고 정렬을 수행합니다.

mysql > SELECT * FROM employess e, salaries s
        WHERE s.emp_no = e.emp_no
        AND e.emp_no BETWEEN 100002 AND 100020
        ORDER BY s.salary;

정렬 처리 방법의 성능 비교

💡 스트리밍 방식

• 서버쪽에서 처리할 데이터가 얼마인지 관계없이 조건게 일치하는 레코드가 검색될때마다 바로바로 클라이언트로 전송해주는 방식입니다.
• 클라이언트는 MySQL 서버가 일치하는 레코드를 찾는 즉시 전달받기 때문에 동시에 데이터의 가공 작업을 시작할 수 있습니다,
• 스트리밍 방식으로 처리되는 쿼리에서 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 전체 실행 시간을 상당히 줄여줄 수 있습니다.

💡 버퍼링 방식

• ORDER BY나 GROUP BY같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능하게 합니다. MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려집니다.
• 버퍼링 방식으로 처리되는 쿼리는 LIMIT처럼 결과 건수룰 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않습니다. 네트워크로 전송되는 레코드의 건수를 줄일 수는 있지만 MySQL 서버가 해야하는 작업은 변화가 없기 때문입니다.
• 인덱스를 사용한 정렬 방식은 스트리밍 형태의 처리이며, 나머지는 모두 버퍼렁된 후에 정렬됩니다. 즉 인덱스를 사용한 정렬 방식은 LIMIT로 제한된 건수만큼 읽으면서 바로바로 클라이언트로 결과를 전송해줄 수 있습니다. 하지만 인덱스를 이용하지 못하는 경우의 처리는 필요한 모든 레코드를 디스크로부터 읽어서 정렬한 후에야 비로소 LIMIT로 제한된 건수 만큼 잘라서 클라이언트로 전송해줄 수 있습니다.

 

GROUP BY

---

GROUP BY 또한 ORDER BY와 같이 쿼리가 스트리밍된 처리를 할 수 없게 하는 처리중 하나입니다. GROUP BY절이 있는 쿼리에서는 HAVING 절을 사용할 수 있는데 HAVING절은 GROUP BY 결과에 대해 필터링 역할을 수행합니다. GROUP BY에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 HAVING 절은 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없습니다.

 

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

• 조인의 드라이빙 테이블에 속한 컬럼만 이용해 그루핑할 때 GROUP BY 컬럼으로 이미 인덱스가 있다면 그 인덱스를 차례대로 읽으면서 그루핑 작업을 수행하고 그 결과로 조인을 처리합니다.

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

• 인덱스의 레코드를 건너뛰면서 필요한 부분만 가져오는 것을 의미하는데, 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향샹되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상됩니다. 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않습니다.

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

• 기준 컬럼이 드라이빙 테이블에 있든 드리븐 테이블에 있든 관계없이 인덱스를 전혀 사용하지 못하는 경우 이 방식으로 처리됩니다.

 

 

DISTINCT 처리

---

특정 컬럼의 유니크한 값만 조회하려면 SELECT 쿼리에 DISTINCT를 사용합니다. DISTINCT는 MIN(), MAX() 또는 COUNT()와 같은 집합 함수와 함께 사용되는 경우와 집합 함수가 없는 경우 2가지로 구분해서 살펴보겠습니다.

 

SELECT DISTINT

• DISTINCT는 SELECT하는 레코드를 유니크하게 SELECT하는 것이지, 특정 칼럼만 유니크하게 조회하는 것이 아닙니다.

---

🤭

필자는 사실 DISTINCT를 잘 못 사용하고 있었습니다. SELECT를 할때 DISTINCT를 사용하여 특정 컬럼만 유니크하게 나타내야지 했는데 이 책을 읽고 지금까지 내가 잘 못된 방법으로 사용하고 있었구나 알게되었습니다. 지금부터 잘 못된 내용을 알아보겠습니다.

아래 SQL 문장을 보면 DISTINCT를 함수처럼 사용하였습니다. first_name만 유니크하게 조회하고 last_name은 유니크하지 않게 조회해야지라는 생각에 저런 SQL 문장을 많이 사용하곤 했습니다. MySQL 서버는 DISTINCT 뒤의 괄호를 그냥 의미 없이 사용된 괄호로 해석하고 제거해버립니다. DISTINCT는 함수가 아니므로 그 뒤의 괄호는 의미가 없는 것입니다.

# 잘못된 방법
mysql> SELECT DISTINCT(first_name), last_name FROM members;

# 정상적인 방법
mysql> SELECT DISTINCT first_name, last_name FROM members;

 

집합 함수와 함께 사용된 DISTINCT

• 집합 함수 내에서 사용된 DISTINCT는 그 집합 함수의 인자로 전달된 컬럼값이 유니크한 것들만 가져옵니다.
• 아래 SQL 문장을 살펴보면 COUNT(DISTINCT first_name)을 처리하기 위해서 임시 테이블을 사용합니다. 만약 집합 함수가 여러개 사용되는 경우에는 여러개의 임시 테이블이 사용됩니다.
• 인덱스된 컬럼에 DISTINCT 처리를 수행할 때는 인덱스를 풀 스캔하거나 레인지 스캔하면서 임시 테이블을 사용하지 않고 최적화된 처리를 할 수 있습니다.

# 임시 테이블 하나 사용
mysql> SELECT COUNT(DISTINCT first_name) FROM members;

# 임시 테이블 두개 사용
mysql> SELECT COUNT(DISTINCT first_name), COUNT(DISTINCT last_name) FROM members;

# 인덱스를 사용하여 임시 테이블을 사용하지 않고 최적화
mysql> SELECT COUNT(DISTINCT member_no) FROM members;

 

내부 임시 테이블 활용

• MySQL 엔진이 스토리지 엔진으로부터 받아온 레코드를 정렬하거나 그루핑할 때는 내부적으로 임시 테이블을 사용합니다. 내부적이라는 단어가 포함된 이유는 여기서 이야기하는 임시 테이블은 "CREATE TEMPORARY TABLE" 명령으로 임시 테이블과는 다르기 때문입니다.
• 일반적으로 MySQL 엔진이 사용하는 임시 테이블은 처음에는 메모리에 생성되었다가 테이블의 크기가 커지면 디스크로 옮겨집니다. 물론 특정 예외 케이스에는 메모리를 거치지 않고 바로 디스크에 임시 테이블이 만들어지기도 합니다.
• MySQL 엔진이 내부적인 가공을 위해 생성하는 임시 테이블은 다른 세션이나 다른 쿼리에서는 볼 수 없으며 사용하는 것도 불가능합니다. 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제가 됩니다.

메모리 임시 테이블

MySQL 8.0 이전 버전까지는 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블이 메모리를 사용할 때에는 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용했습니다. 하지만 MySQL 8.0 버전부터는 메모리는 TempTable이라는 스토리지 엔진을 사용하고 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선되었습니다.

기존 MEMORY 스토리지 엔진은 VARCHAR 같은 가변 길이 타입을 지원하지 못하기 때문에 임시 테이블이 메모리에 만들어질때 가변 길이의 최대 길이만큼 메모리를 할당하여 사용되어 왔습니다. 이는 메모리 낭비를 발생시키는 문제점이 있었습니다. 그리고 디스크에 임시 테이블이 만들어질 때 사용되는 MyISAM 스토리지 엔진은 트랜잭션을 지원하지 못한다는 문제점을 가지고 있었습니다. 그래서 MySQL 8.0 버전부터는 MEMORY 스토리지 엔진 대신 가변 길이 타입을 지원하는 TempTable 스토리지 엔진이 도입되었으며, MyISAM 스토리지 엔진을 대신해 트랜잭션 지원 가능한 InnoDB 스토리지 엔진이 사용되도록 개선되었습니다.

 

디스크 임시 테이블

MySQL 8.0버전 부터는 메모리용 임시 테이블이 TempTable로 설정되어 있는데 메모리의 공간 크키의 기본값은 1GB로 설정되어 있으며 임시 테이블의 크기가 1GB보다 커지는 경우 MySQL 서버는 메모리 임시 테이블을 디스크로 기록하게 되는데 이때 2가지 디스크 저장 방식을 사용할 수 있습니다.

MySQL 서버가 MMAP 파일로 기록할지 InnoDB 테이블로 전환할지는 시스템 변수로 설정할 수 있습니다. 기본적으로는 MMAP 파일로 전환하게 되며  그 이유는 InnoDB 테이블로 전환하는 것보다 오버헤드가 적기 때문입니다.

• MMAP 파일로 디스크에 기록
• InnoDB 테이블로 기록

임시 테이블이 필요한 쿼리

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

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

• UNION이나 UNION ALL에서 SELECT되는 컬럼 중에서 길이가 512바이트 이상인 크기의 컬럼이 있는 경우
• GROUP BY나 DISTINCT 컬럼에서 512바이트 이상인 크기의 컬럼이 있는 경우

 

 

 

---

해당 내용은 Real Mysql 8.0 책을 바탕으로 작성되었습니다.