MySQL 옵티마이저 동작 원리 알아보기

이전 글: https://gojs.tistory.com/39

MySQL에서의 트랜잭션과 잠금의 개념

 

쿼리 실행 절차

SQL parsing → parse tree → query plan → physical work

옵티마이저는 위의 쿼리 실행 절차 중 parse tree를 기반으로 쿼리 실행 계획을 세우는 역할을 수행한다.

 

옵티마이저의 자체적인 동작 순서는 아래와 같다.

1. 불필요한 조건 제거

2. 복잡한 연산 단순화

3. 어떤 순서로 테이블을 읽을지 결정

4. 어떤 인덱스를 사용할 지 결정

5. 임시 테이블을 활용할지 결정

6. 실행 계획 생성

 

옵티마이저의 종류

옵티마이저는 크게 두 가지 종류로 분류된다.

• 비용 기반 최적화 (Cost-based Optimizer, CBO)
  • 여러 가지 방법을 만들고 비용과 예측통계를 기반
  • 같이 쿼리라도 비용이 최소가 되는 방식으로 실행 계획 생성
• 규칙 기반 최적화 (Rule-based Optimizer, RBO)
  • 옵티마이저에 내장된 우선순위를 기반
  • 같은 쿼리는 항상 같은 실행 계획 생성

MySQL을 포함한 대부분의 DBMS가 CBO를 채택하여 옵티마이저를 구성한다.

 

기본 데이터 처리

풀 테이블 스캔

옵티마이저는 아래와 같은 조건일 때 인덱스를 사용하지 않고 풀 테이블 스캔을 한다.

• 테이블 레코드 건수가 너무 작은 경우 (풀 테이블 스캔이 인덱스를 통해 읽는 것보다 빠른 경우)
• 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
• 인덱스 레인지 스캔을 할 수 있어도 조건 일치 레코드가 너무 많은 경우

InnoDB에서의 풀 테이블 스캔은 많은 양의 데이터를 디스크에서 가져와야하기 때문에 한 번에 여러 개의 페이지나 블록의 데이터를 가져온다.

→ 테이블의 연속된 데이터 페이지가 읽히면 리드 어헤드 작업 시작 (앞으로의 작업을 예측해서 미리 디스크 조회)

→ 특정 페이지 이후의 페이지는 백그라운드 스레드가 읽기 작업 (최대 64개)

→ 포그라운드 스레드는 버퍼에서 준비된 데이터를 가져다 사용

 

병렬 처리

MySQL 서버에서는 아무런 WHERE 조건 없이 테이블 전체 건수를 가져오는 쿼리만 병렬 처리할 수 있다.

mysql> SET SESSION innodb_parallel_read_threads=1;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.32 sec)

mysql> SET SESSION innodb_parallel_read_threads=8;
mysql> SELECT COUNT(*) FROM salaries;
1 row in set (0.13 sec)

위의 쿼리는 스레드 갯수를 늘리니 병렬 처리로 수행되어 성능이 향상되었다.

그러나 스레드 갯수를 CPU 코어의 갯수보다 더 크게 늘리는 경우에는 오히려 성능이 감소할 수도 있다.

 

ORDER BY 처리

MySQL에서 정렬 처리를 위해서는 인덱스를 이용하거나 Filesort를 이용한다.

아래와 같은 이유로 모든 쿼리를 인덱스로 처리할 수는 없다.

• 정렬 기준 마다 모두 인덱스를 생성하기는 불가능하다
• group by, distinct 등 처리 결과를 정렬해야하는 경우
• union의 결과와 같이 임시 테이블을 정렬해야하는 경우
• 랜덤하게 결과 레코드를 가져와야하는 경우

따라서 아래와 같이 각 방식의 장단점을 가지고 쿼리를 튜닝할 수 있다.

 

인덱스 이용

• 장점
  • 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어 빠르다
• 단점
  • 조회를 제외한 쿼리는 오히려 느려진다
  • 인덱스 때문에 디스크 공간이 더 많이 필요하다

 

Filesort 이용

• 장점
  • 인덱스의 단점이 없어진다
  • 정렬해야할 레코드가 많지 않으면 충분히 빠르다
• 단점
  • 대상 레코드가 많아지면 느리다

쿼리의 실행 계획의 Extra 컬럼에 Using filesort 가 있다면 filesort를 이용한 것이다.

 

소트 버퍼

MySQL은 정렬을 위한 별도 공간을 할당받는다. 이 공간을 소트 버퍼라고 한다.

(sort_buffer_size 시스템 변수로 설정 가능)

• 정렬해야할 데이터가 sort_buffer_size를 넘는 경우
  • 여러 조각으로 데이터를 나눈다
  • 소트 버퍼에서 한 조각을 정렬한다
  • 정렬된 데이터를 디스크에 임시 기록한다
  • 그다음 레코드를 가져와서 정렬하고 반복한다
  • 정렬된 레코드를 병합하면서 재정렬한다 (멀티 머지)

이 경우 성능이 저하된다.

sort_buffer_size를 늘리면 어느정도 해소되지만, 임계점을 넘으면 오히려 큰 메모리를 할당해야해서 성능이 떨어질 수도 있다.  (56K~1MB 정도가 적절)

 

정렬 알고리즘

정렬 알고리즘은 크게 두 가지로 구분된다. 옵티마이저가 어떤 방식을 사용하는지는 아래와 같이 옵티마이저 트레이스 기능으로 확인해볼 수 있다.

mysql> SET OPTIMIZER_TRACE="enabled=on", END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)

mysql> SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM MEMBER ORDER BY password limit 10000, 1;
Empty set (0.00 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

...
      "join_execution": {
        "select#": 1,
        "steps": [
          {
            "sorting_table": "MEMBER",
            "filesort_information": [
              {
                "direction": "asc",
                "expression": "`member`.`password`"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,
              "cause": "not applicable (no LIMIT)"
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {
              "memory_available": 262144,
              "key_size": 2057,
              "row_size": 5036,
              "max_rows_per_buffer": 15,
              "num_rows_estimate": 15,
              "num_rows_found": 1,
              "num_initial_chunks_spilled_to_disk": 0,
              "peak_memory_used": 32776,
              "sort_algorithm": "none",
              "sort_mode": "<varlen_sort_key, packed_additional_fields>"
...

sort_mode 섹션은 아래와 같은 내용이 입력된다.

• <sort_key, rowid> : 정렬 키와 로우 아이디만 가져와서 정렬
• <sort_key, additional_fields> : 정렬키와 레코드 전체를 가져와서 정렬 (컬럼: 고정사이즈 메모리 저장)
• <sort_key, packed_additional_fields> : 정렬키와 레코드 전체를 가져와서 정렬 (컬럼: 가변사이즈 메모리 저장)
•  

싱글 패스 정렬 방식

소트 버퍼에 select 대상이 되는 칼럼 전부를 담아서 정렬을 수행한다.

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

싱글 패스 정렬

정렬에 필요하지 않은 last_name 컬럼도 소트 버퍼에 담아 정렬을 수행하기 때문에 많은 공간이 필요하다.

정렬이 완료되는 경우 그 결과를 그대로 클라이언트에게 전달한다.

 

투 패스 정렬 방식

투 패스 정렬

정렬 대상 컬럼과 기본 키만 소트 버퍼에 담아서 정렬한다.

그리고 정렬된 순서대로 다시 SELECT하기 때문에 불합리한 동작방식이다. MySQL 8.0 버전 이전에 사용되던 방식이다.

 

정렬 처리 방법

• 인덱스 사용
  • query plan의 extra 컬럼에 별도 표기 없음
  • order by에 제일 먼저 읽는 테이블(드라이빙 테이블)의 칼럼, 인덱스가 있는 칼럼
  • b-tree 인덱스가 아니면 사용 불가

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

-- emp_no가 pk라서 order by 절을 추가하지 않더라도 자동으로 정렬이 되는 경우
-- 그럼에도 불구하고 order by를 그대로 명시하는 것이 좋다
-- 명시한다고 하더라도 두 번 작업하지 않는다
-- 쿼리 실행 계획이 바뀌게 되면 정상적으로 정렬 값이 나오지 않을 수도 있다

 

• 조인의 드라이빙 테이블만 정렬
  • query plan의 extra 컬럼에 ‘Using filesort’
  • order by절에 드라이빙 테이블의 칼럼

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

-- MySQL이 employees 테이블을 드라이빙 테이블로 선정하는 이유
-- where절 검색 조건이 e.emp_no -> pk이기 때문에 작업량 감소
-- salaries의 emp_no 칼럼에 인덱스가 있다

 

• 조인 결과를 임시 테이블로 저장 후 정렬
  • query plan의 extra 컬럼에 ‘Using temporary; Using filesort’
  • 가장 느린 정렬 방법

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

-- order by절의 컬럼이 드리븐 테이블의 컬럼이기 때문에 임시테이블 사용

 

정렬 처리 방법의 성능 비교

웹 서비스용 쿼리에서 ‘select … order by … limit …’ 과 같은 쿼리를 자주 사용하게 된다.

그러나 order by나 group by 연산은 limit 건수만으로는 처리할 수 없다. (성능 저하)

심지어 해당 연산들은 인덱스가 없는 경우에는 더욱 느리게 동작한다. (스트리밍 처리, 버퍼링 처리 때문)

 

스트리밍 처리

조건에 일치하는 레코드가 검색될 때마다 바로 클라이언트에 전송하고 클라이언트는 레코드를 먼저 받아 처리시작한다.

LIMIT 조건을 사용하면 클라이언트에 전송하는 레코드 건수가 줄어 효율적이다.

인덱스를 활용한 정렬만 스트리밍 방식을 사용하여 LIMIT 조건을 굉장히 효율적으로 사용한다.

 

버퍼링 처리

모든 레코드를 검색하고 클라이언트에 전송하기 위해 MySQL 서버에서 데이터를 전처리한다.

따라서 LIMIT 조건을 걸어도 크게 성능 개선이 이루어지지 않는다.

클라이언트의 도구에 따른 방식 차이

JDBC의 경우 자체적으로 버퍼링 정책을 가진다
(첫 번째 레코드를 받아도 모든 레코드를 받기 전까지는 기다린다)
→ 전체 처리시간이 짧고 통신 횟수가 줄어 자원 소모가 줄어들기 때문
→ 대량 데이터 조회 시 스트리밍 방식으로 변경 가능

 

예시

-- t1:100건, t2:1000건
-- t1의 1레코드가 t2의 10레코드 매칭
SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.col1 = t2.col2
ORDER BY t2.col2
LIMIT 10;

• tb_test1이 드라이빙
  • 인덱스 사용
    • 조회 레코드 수 - tb_test1:1건, tb_test2:10건
    • 조인 횟수 - 1번
    • 정렬 대상 건수 - 0개
  • 조인의 드라이빙 테이블 정렬
    • 조회 레코드 수 - tb_test1:100건, tb_test2:10건
    • 조인 횟수 - 1번
    • 정렬 대상 건수 - 100건
  • 임시테이블 사용 후 정렬
    • 조회 레코드 수 - tb_test1:100건, tb_test2:1000건
    • 조인 횟수 - 100번
    • 정렬 대상 건수 - 1000건
• tb_test2가 드라이빙
  • 인덱스 사용
    • 조회 레코드 수 - tb_test1:10건, tb_test2:10건
    • 조인 횟수 - 10번
    • 정렬 대상 건수 - 0개
  • 조인의 드라이빙 테이블 정렬
    • 조회 레코드 수 - tb_test1:10건, tb_test2:1000건
    • 조인 횟수 - 10번
    • 정렬 대상 건수 - 1000건
  • 임시테이블 사용 후 정렬
    • 조회 레코드 수 - tb_test1:100건, tb_test2:1000건
    • 조인 횟수 - 1000번
    • 정렬 대상 건수 - 1000건 
      • ⇒ 성능 영향도: 드라이빙 테이블 < 정렬 방식
      • ⇒ 최소 드라이빙 테이블만 정렬해도 되게 유도
      • ⇒ 인덱스를 사용한 정렬을 우선적으로 사용

성능 영향도

드라이빙 테이블 < 정렬 방식
인덱스를 사용한 정렬을 우선적으로 사용
드라이빙 테이블 정렬하도록 유도하기만 해도 괜찮다

 

GROUP BY 처리

GROUP BY 절은 스트리밍 처리 할 수 없는 방식 중 하나이다.

특히 HAVING 절은 튜닝할 수 없다. (GROUP BY 조건 결과는 인덱스로 처리되지 않음)

GROUP BY 절에 대한 처리는 아래의 3가지 방법으로 나뉜다.

• 인덱스 스캔을 이용
• 인덱스를 건너뛰며 스캔
• 임시 테이블 사용

 

인덱스 스캔을 이용(타이트 인덱스 스캔)

조인의 드라이빙 테이블에 속하며 인덱스가 존재하는 컬럼으로 그루핑하는 경우 인덱스를 읽으며 그루핑해서 결과를 조인한다.

(쿼리 플랜의 Extra 컬럼에 별도 코멘트 없음)

 

루스 인덱스 스캔을 이용

인덱스의 레코드를 건너뛰며 필요한 부분만 읽는 방식이다.

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

-- (emp_no, from_date) 인덱스 생성된 상태

1. 인덱스를 스캔하며 emp_no의 유일한 값을 찾아낸다 (10001이라 가정)
2. 인덱스에서 emp_no가 10001이며 from_date가 1985-03-01 인 레코드만 가져온다
3. 그다음 emp_no의 유일한 값을 찾아낸다
4. 없는 경우 처리 종료, 있는 경우 2번부터 반복

단일 테이블에 대해 수행되는 GROUP BY만 사용가능하며, 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다.

 

-- 루스 인덱스 스캔을 사용할 수 없는 케이스

-- MIN(), MAX() 이외의 집합 함수가 사용됨
SELECT col1, SUM(col2)
FROM tb_test
GROUP BY col1;

-- GROUP BY 절의 컬럼이 인덱스 컬럼의 순서와 일치하지 않음
SELECT col1, col2
FROM tb_test
GROUP BY col2, col3;

-- SELECT 절의 칼럼이 GROUP BY와 일치하지 않음
SELECT col1, col3
FROM tb_test
GROUP BY col1, col2;

 

임시 테이블을 사용

인덱스를 전혀 사용하지 못하는 경우 사용된다.

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

 

위의 쿼리를 실행하면 아래와 같이 인덱스를 가진 임시테이블을 생성한다.

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

조인의 결과를 임시 테이블에 INSERT, UPDATE 처리한다.

 

DISTINCT 처리

DISTINCT만 사용하는 경우

GROUP BY와 같은 방식으로 처리한다.

SELECT DISTINCT first_name, last_name
FROM employees;

위의 쿼리는 유니크한 first_name을 가져오는 것이 아니라 유니크한 (first_name, last_name)을 가져온다.

SELECT DISTINCT(first_name), last_name
FROM employees;

위의 쿼리의 경우에도 유니크한 (first_name, last_name)을 가져온다.

(MySQL 서버에서 괄호를 빼버린다)

 

집합 함수와 함께 사용하는 경우

집합 함수 내부에서는 조금 다른 방식으로 동작한다.

SELECT COUNT(DISTINCT s.salary)
FROM employees e, salaries s
WHERE e.emp_no = s.emp_no
AND e.emp_no BETWEEN 100001 AND 100100;

COUNT(DISTINCT s.salary)를 처리하기 위한 임시테이블을 생성한다.

employees, salaries 조인 결과 → salary를 임시테이블에 저장 (salary 유니크 인덱스 생성)

 

내부 임시 테이블 활용

사용자가 ‘CREATE TEMPORARY TABLE …’로 만든 임시 테이블과는 달리 MySQL 서버가 내부 처리를 위해 임시 테이블을 생성할 수 있다.

생성된 임시 테이블은 우선 메모리에 생성되고 크기가 커지면 디스크로 이동하게 된다.

 

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

임시 테이블이 메모리에 저장되는 경우 TempTable 스토리지 엔진을 사용한다.

임시 테이블이 디스크에 저장되는 경우 InnoDB 스토리지 엔진을 사용한다.

 

internal_tmp_mem_storage_engine 변수로 메모리용 임시 테이블 스토리지 엔진을 선택할 수도 있다.

(MEMORY/TempTable, default:TempTable)

 

메모리의 임시테이블이 temptable_max_ram 의 크기가 넘게 되면 디스크로 기록하게 된다.

이 때 MMAP 파일로 기록하거나 InnoDB 테이블로 기록하는데, temptable_use_mmap 변수로 설정할 수 있다. (default: on)

해당 시스템 변수가 on이라면 TempTable을 MMAP로 변환한다. (테이블 전환보다 오버헤드가 적음)

 

임시테이블이 필요한 쿼리

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

쿼리 실행 계획의 Extra 컬럼의 값에 Using temporary가 포함되는 경우 임시 테이블이 사용된다.

3,4,5,6번 케이스는 계획에 드러나지 않지만 임시 테이블을 사용하게 된다.

 

임시테이블이 디스크에 생성

일반적으로 임시테이블을 메모리에 먼저 생성되게되는데 특수하게 디스크에 생성되는 경우가 있다.

• UNION이나 UNION ALL에서 SELECT되는 칼럼 중 길이가 512Byte 이상인 칼럼이 있는 경우
• GROUP BY나 DISTINCT 칼럼에서 512Byte 이상인 칼럼이 있는 경우
• 메모리 임시 테이블의 크키가 tmp_table_size, max_heap_table_size, temptable_max_ram 보다 큰 경우

 

임시 테이블 관련 상태 변수

임시 테이블 사용 여부를 확인하기 위해서는 쿼리 실행 계획을 확인하는 방법이 있다.

그러나 실행 계획으로는 임시 테이블에 대한 상세 정보(메모리/디스크, 테이블 개수 등)을 확인하기는 어렵다

이 경우 SHOW SESSION STATUS LIKE 'Created_tmp%'; 를 확인해볼 수 있다.

• Craeted_tmp_tables : 쿼리 처리를 위해 만들어진 내부 임시 테이블 개수
• Created_tmp_disk_tables : 디스크에 만들어진 임시 테이블 개수

 

다음 글: https://gojs.tistory.com/41

MySQL에서 옵티마이저가 쿼리 최적화하는 방법