옵티마이저와 힌트 #1

minstone·2022년 4월 26일
Real MySQLmysql
0

Real MySQL 8.0

목록 보기
3/3
post-thumbnail

옵티마이저

MySQL 서버로 요청된 쿼리는 결과는 동일하지만 내부적으로 그 결과를 만들어내는 방법은 매우 다양하다.

이런 다양한 방법 중 어떤 방법이 최적이고 최소의 비용이 소모될지 결정해야 한다.

MySQL에서는 쿼리를 최적으로 실행하기 위해 각 테이블이 데이터가 어떤 분포로 저장돼 있는지 통계 정보를 참조하며, 그런 기본 데이터를 비교해 최적의 실행 계획을 수립하는 작업이 필요하다.

대부분의 DBMS에서 옵티마이저가 이러한 기능을 담당한다.

1. EXPLAIN

  • EPLAIN명령어로 쿼리의 실행 계획 확인.
  • 실행계획을 통해 튜닝하기 위해서는 최적화관련 지식이 필요.

2. 쿼리 실행 절차

쿼리는 아래와 같이 3단계로 수행된다.

👆 SQL 파싱(Parsing) - SQL 파서가 수행
요청된 SQL문장을 잘게 쪼개서 파스 트리로 분리한다.

✌ 최적화 및 실행 계획 수립 - 옵티마이저가 수행
파스 트리를 확인하며 어떤 테이블로부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다. 파스트리를 참조하며 처리하는 내용은 아래와 같다.

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

👌 실행계획 수행
2단계에서 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.


3. 옵티마이저의 종류

  • 규칙 기반 최적화 (Rule-based optimizer)

    • 옵티마이저에 내장된 우선순위에 따라 싱행 계획을 수립
    • 테이블의 레코드 건수나, 선택도등을 고려하지 않음
    • 통계 정보를 참조하지 않기에 같은 쿼리에 대해서는 항상 같은 실행계획을 보여줌
      • 초기 버전의 오라클에서 많이 사용했던 옵티마이저
      • 사용자의 데이터 분포도는 매우 다양하기에 규칙 기반 최적화는 오래전부터 사용되지 않는다.

  • 비용 기반 최적화 (Cost-based optimizer)

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

기본 데이터 처리

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

  • 풀 테이블 스캔을 선택하는 경우

    • 레코드가 건수가 너무 적어 인덱스를 통해 읽는 것보다 풀스캔이 빠른 경우
    • 레인지 스캔이 가능하더라도 결과 레코드 건수가 너무 많은 경우
    • WHERE, ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우

  • 리드 어헤드란?

    • 특정 테이블의 데이터페이지가 연속적으로 읽히면 백그라운드 스레드가 해당 테이블의 데이터를 미리 버퍼풀에 적재 하는 것
    • 때문에 풀스캔 시 처음 몇개의 페이지는 포그라운드 스레드가, 이후부터는 백그라운드 스레드가 읽기슬 수행
      *innodb_read_ahead_threshold 변수로 리드 어헤드 임계치 설정 가능

2. 병렬 처리

기존 MySQL은 여러 쿼리를 동시해 수행하는 병렬 처리가 가능했다.
여기에 MySQL8.0버전부터 한개의 쿼리를 여러 스레드가 병렬 처리하는 기능도 추가됐다.

  • innodb_parallel_read_threads 변수를 통해 몇개의 스레드로 처리할지 변경 가능
  • 스레드 수가 늘수록 처리시간이 줄어드나 CPU 코어개수보다 많은 스레드 설정 시 성능 저하에 주의

3. ORDER BY 처리

✅ 인덱스를 이용하는 방식

  • 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.
  • 인덱스 추가/삭제 시 부가작업이 필요하며, 추가 디스크 공간과 버퍼풀이 필요할 수 있음.
  • 인덱스의 개수가 늘어날수록 InnoDB 버퍼 풀을 위한 메모리가 많이 필요하다.

✅ Filesort를 이용하는 방식

  • 인덱스를 이용할 때의 단점이 Filesort 방식의 장점
  • 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

✅ 인덱스를 사용못하는 케이스

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

4. 소트 버퍼

✅ MySQL은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용한다.

  • 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만, 최대 사용 가능한 소트 버퍼의 공간은 sort_buffer_size라는 시스템 변수로 설정할 수 있다.
  • 정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다 MySQL은 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데, 임시 저장을 위해 디스크를 사용한다.
  • 각 버퍼 크기만큼 정렬된 레코드를 다시 병합하면서 정렬을 수행해야 하는데, 이 병합 과정을 멀티 머지라고 한다.

일반적으로 256KB 미만, 8MB 이상부터 소트 버퍼 사이즈에 의한 성능변화가 미미하다.
소트버퍼를 크게 설정 시 메모리풀이 발생할 수 있으니 주의.


✅ 소트 버퍼의 크기

  • 정렬을 위해 할당하는 소트 버퍼는 세션 메모리 영역에 해당한다.
  • MySQL 서버의 데이터가 많거나 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 도움이 될 수 있다. 하지만 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 MySQL 서버가 메모리 부족을 겪을 수도 있어 적절히 설정해야 한다.

5. 정렬 알고리즘

✅ OPTIMIZER TRACE

  • OPTIMIZER TRACE 기능으로 정렬방식을 확인 가능하다.
  1. <sort_key, rowid> : 정렬키와 레코드의 로우아이디만 가져와 정렬
  2. <sort_key, additional_fields> : 정렬키와 레코드전체를 가지고 정렬하며 고정사이즈 메모리 저장
  3. <sort_key, packed_additional_fields> : 정렬키와 레코드전체를 가지고 정렬하며 가변사이즈 메모리 저장

첫번쨰 방식은 투패스정렬, 둘째,셋째 방식은 싱글패스정렬이다.


✅ 싱글패스 정렬 방식

  • SELECT 대상이 되는 컬럼 전부를 담아서 (정렬이 필요하지 않은 컬럼까지 전부 읽음) 정렬을 수행하는 방식

✅ 투 패스 정렬 방식

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

✅ 싱글패스 vs 투 패스

  • 투 패스 방식
    테이블을 두 번 읽어야 하기 때문에 상당히 불합리해서, 일반적으로 싱글 패스가 사용된다.
  • 싱글 패스 정렬 방식
    더 많은 소트 버퍼 공간이 필요하기 때문에 다음과 같은 상황에선 투 패스 정렬 방식을 사용한다.
    • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
    • BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때

✅ 정렬 처리 방법

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

인덱스 활용하도록 유도. 최소한 드라이빙 테이블만 정렬해도 되는 수준으로 유도.


✅ 인덱스를 사용한 정렬

  • 반드시 ORDER BY에 명시된 컬럼이 드라이빙 테이블에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
  • 인덱스를 이용한 정렬이 처리되는 경우, 실제 인덱스 값이 정렬돼 있기 때문에 인덱스의 순서대로 읽기만 하면 된다.

6. 스트리밍 방식 vs 버퍼링 방식

✅ 스트리밍 방식

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

✅ 버퍼링 방식

  • ORDER BY나 GROUP BY 같은 처리는 쿼리의 결과가 스트리밍 되는 것을 불가능하게 한다.
  • 먼저 결과를 모아서 MySQL 서버에서 일괄 가공해야 하므로 모든 결과를 스토리지 엔진으로부터 가져올 때까지 기다려야 한다.
  • LIMIT처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 별로 도움이 되지 않는다.
    정렬 처리 방법 중 "인덱스를 사용한 방법" 이외의 나머지

7, GROUP BY 처리

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

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

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


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

  • 인덱스의 레코드를 건너뛰면서 필요한 부분만 읽어서 가져오는 것
  • 실행 계획의 Extra 컬럼에 Using index for group-by 코멘트가 표시된다.
  • 루스 인덱스 스캔으로 처리되는 쿼리에서는 별도의 임시 테이블이 필요하지 않다.
  • 인덱스 레인지 스캔에서는 유니크한 값의 수가 많을수록 성능이 향상되는 반면 루스 인덱스 스캔에서는 인덱스의 유니크한 값의 수가 적을수록 성능이 향상된다.

✅ 임시 테이블을 사용하는 GROUP BY
인덱스를 전혀 사용할 수 없는 GROUP BY로, 실행 계획의 Extra 컬럼에 Using temporary 메시지가 표기된다.

✅ ORDER BY NULL

  • MySQL 8.0 이전 버전까지는 GROUP BY가 사용된 쿼리는 그루핑되는 컬럼을 기준으로 묵시적인 정렬까지 함께 수행했다.
  • 그래서 정렬이 필요하지 않은 경우라면 ORDER BY NULL을 추가로 사용할 것을 권장했다. (불필요한 추가 정렬 작업을 수행하지 않아 성능 향상)
  • MySQL 8.0부터는 묵시적인 정렬은 더 이상 실행되지 않게 바뀌었다.

8. DISTINCT 처리

✅ SELECT DISTINCT...

SELECT DISTINCT ... FROM ...
  • 단순 SELECT 되는 레코드 중에서 유니크한 레코드만 가져오고자 하면 SELECT DISTINCT 형태의 쿼리 문장을 사용한다.
    👉 내부적으로 GROUP BY와 동일하게 처리된다.
  • 주의할 것은 DISTINCT는 SELECT 하는 레코드를 유니크하게 SELECT 하는 것이지, 특정 컬럼만 유니크하게 조회하는 것이 아니다.

🌵예제

SELECT DISTINCT first_name, last_name FROM employees;
SELECT DISTINCT (first_name), last_name FROM employees;

MySQL Syntex는 DISTICT가 함수가 아니기에 괄호를 무시처리한다.


✅ 집합 함수와 함께 사용된 DISTINCT

SELECT COUNT(DISTINCT s.salary) FROM ...
  • COUNT(), MIN(), MAX() 와 같은 집합 함수 내에서 DISTINCT가 사용되는 경우, 그 집합 함수의 인자로 전달된 컬럼 값이 유니크한 것들을 가져온다.
    • 쿼리의 실행 계획에서 DISTINCT가 인덱스를 사용하지 못할 때는 항상 임시 테이블이 필요하다.
    • 하지만 실행 계획의 Extra 컬럼에는 Using temporary 메시지가 출력되지 않는다. (버그 같음)

9. 내부 임시 테이블 활용

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

  • 내부적인 임시 테이블은 처음에는 메모리에 생성됐다가 테이블의 크기가 커지면 디스크로 옮겨진다.
  • 내부적인 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.

CREATE TEMPORARY TABLE 명령으로 만든 임시 테이블과 다르다


✅ 메모리 임시 테이블과 디스크 임시 테이블
MySQL 8.0 이전 버전까의 경우 MEMORY 스토리지 엔진을 사용하며, 디스크에 저장될 때는 MyISAM 스토리지 엔진을 사용한다.

  • MySQL 8.0 버전부터는 메모리는 기본값으로 TempTable이라는 스토리지 엔진을 사용하며, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진을 사용하도록 개선됐다.
  • MEMORY 테이블은 가변 길이 타입을 지원하지 못하는 문제점이 존재함.
    👉 TempTable 스토리지 엔진은 가변 길이 타입을 지원.
  • MyISAM 테이블은 트랜잭션을 지원하지 않음.
    👉 InnoDB 스토리지 엔진은 트랜잭션을 지원

임시 테이블의 크기가 1GB보다 커지는 경우 메모리의 임시 테이블을 디스크로 기록한다.

  • 이때 두 가지 방식 중 하나를 선택한다.
    • MMAP 파일로 디스크에 기록
    • InnoDB 테이블로 기록

임시테이블 사이즈는 temptable_max_ram 시스템 변수로 변경 가능


✅ 임시 테이블이 필요한 쿼리

  • ORDER BY, GROUP BY에 명시된 컬럼이 다른 쿼리
  • ORDER BY, GROUP BY에 명시된 컬럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
  • DISTINCT나 ORDER BY가 동시에 쿼리에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • UNION, UNION DISTINCT가 사용된 쿼리 (UNION ALL을 사용하는 쿼리는 MySQL 8.0부터는 임시 테이블을 사용하지 않도록 개선되었다)
  • 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
    어떤 쿼리의 실행 계획에서 임시 테이블을 사용하는지는 Extra

컬럼에 Using temporary라는 메시지가 표시되는지 확인하면 된다.


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

  • UNION, UNION ALL에서 SELECT 되는 컬럼 중에서 길이가 512byte 이상의 크기의 컬럼이 있는 경우
  • GROUP BY나 DISTINCT 컬럼에서 512byte 이상의 크기의 컬럼이 있는 경우
  • 메모리 임시 테이블의 크기가 지정된 시스템 변수 값보다 큰 경우
profile
minstone
🌚Stone Kid
이전 포스트

[작성중]인덱스 #2

0개의 댓글