[Real MySQL] 옵티마이저와 힌트 - 1

Real MySQL 8.0 전면 개정판을 읽으며 정리한 내용입니다.

쿼리 튜닝의 Base는 내가 실행한 쿼리가 어떻게 처리되는지 아는 것이다!

 

mysql에서 쿼리는 크게 3단계의 과정으로 실행된다.

1. sql 파싱
   mysql 서버가 이해할 수 있는 수준으로 sql 문장을 분리, 이 과정에서 문법 오류 체크
2. 최적화 및 실행 계획 수립
   mysql 서버의 옵티마이저가 처리 (테이블을 읽을 순서, 사용할 인덱스 결정 등)
3. 실행 계획을 바탕으로 스토리 엔진에서 데이터 확보
   

⚙️ 옵티마이저

✔️  옵티마이저란?

쿼리를 최적으로 실행하기 위해서 데이터가 어떻게 분포되어있는지 통계정보를 참조하여,
mysql에서는 EXPLAIN이라는 명령으로 쿼리의 실행계획을 확인할 수 있다.

 

✔️  옵티마이저의 종류

• 비용 기반 최적화(Cost-based optimizer, CBO)
  → 쿼리를 처리하기 위한 여러가지 방법을 만들어 비용이 최소로 소용되는 처리 방식 선택
  → 대부분의 RDBMS가 채택 중
• 규칙 기반 최적화(Rule-based optimizer, RBO)
  → 내장된 우선순위에 따라 실행 계획을 수립, 같은 쿼리에 대해서는 거의 같은 실행 방법 선택
  → 이전에 비용 계산 과정이 부담스럽게 느껴졌던 느린 CPU 시절 사용

---

💿 기본 데이터 처리 방식

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

• 풀 테이블 스캔
  • 정의
    • 인덱스를 사용하지 않고 테이블의 처음부터 끝까지 읽어서 요청된 작업을 처리하는 방식이다.
  • 조건
    • 테이블의 레코드 수가 너무 적어서, 인덱스보다 풀 테이블 스캔이 빠른 경우 건
    • where이나 on 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
    • 인덱스 레인지 스캔이가능하더라도, 옵티마이저가 판단한 조건 일치 레코드 수가 너무 많은 경우
  • 동작 방식
    • MyISAM 스토리 엔진에서는 디스크로부터 페이지를 하나씩 읽어 옴.
    • InnoDB 스토리 엔진에서는 연속된 페이지가 읽히면 백그라운드 스레드에 의해서 리드 어헤드 작업이 자동으로 실행 → 리드 어헤드란 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측, 요청이 오기 전에 미리 디스크를 읽어 InnoDB 버퍼 풀에 적재하는 방식
• 풀 인덱스 스캔
  • 정의
    • 인덱스를 처음부터 끝까지 읽어서 요청된 작업을 처리하는 방식이다.
  • 동작방식
    • 풀 테이블 스캔과 마찬가지로 리드 어헤드가 사용된다.

✔️ 병렬 처리

• MySQL 8.0 버전부터는 하나의 쿼리가 여러 스레드로 동시에 처리할 수 있다.
• innodb_parallel_read_threads 시스템 변수를 사용해 최대 몇 개의 스레드로 하나의 쿼리를 처리할지 변경 가능하다.
  → CPU 코어 개수를 넘어서는 스레드 개수는 성능을 떨어트린다.→ 단, MySQL 8.0 버전에서는 아무런 where 조건 없이 단순히 테이블 전체 건수를 가져오는 쿼리만 병렬로 처리 가능하다.

✔️ ORDER BY 처리

• 인덱스를 이용하는 방법
  • 이미 인덱스가 정렬이 된 상태라, 순서대로 읽으면 되어 빠르다.
  • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하다.
  • 인덱스를 위한 디스크 공간과 InnoDB 버퍼 풀을 위한 메모리가 필요하다.
• Filesort 이용하는 방법
  • 인덱스를 사용 방식의 단점이 장점이다.
  • 레코드가 많지 않으면, 메모리에서 Filesort가 처리되므로 빠르다.
  • 레코드 대상이 많아지면 정렬 작업이 쿼리 실행 시에 처리되므로 인해 응답이 느리다.
  • 정렬 기준이 너무 많아 인덱스 생성이 불가능한 경우, GROUP BY or DISTINCT와 같은 처리 결과를 정렬하는 경우 등의 경우에 인덱스를 사용하지 못하여, Filesort 사용
• 소트 버퍼
  • 정렬을 위해 할당받은 별도의 메모리 공간
  • 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적, sort_buffer_size 시스템 변수로 최대 사용 가능 크기 설정은 가능
  • 쿼리의 실행이 완료되면, 즉시 반납
  • 동작 방식
    • 소트 버퍼에서 정렬 수행
    • 버퍼의 크기보다 정렬할 레코드가 크다면, 레코드를 여러 조각으로 나누어 진행한다. 소트 버퍼에서 정렬하고, 임시로 디스크에 저장 한 뒤 다음 레코드 작업 진행 이 과정에서 버퍼의 크기만큼 정렬된 데이터를 다시 병합-정렬이 필요 → 멀티 머지
    • 멀티 머지 횟수는 Sort_merge_passes라는 상태 변수로 집계 확인 가능
    • 소트 버퍼는 세션 메모리에 영역으로, 클라이언트끼리 공유 X
      → 커넥션과 정렬 작업이 많다면 소트 버퍼로 소비되는 메모리 공간으로 인해 메모리 부족 현상이 발생할 수도 있다.
• 정렬 알고리즘
  정렬 시에 레코드 전체 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 2가지로 나뉜다.
  
  • 싱글 패스 방식
    • 소트 버퍼에 SELECT 대상이 되는 칼럼 전부를 담아서 정렬 수행하는 방식
    • 투 패스에 비해 더 많은 소트 버퍼 공간 필요
    • 최신 버전에서 일반적으로 사용됨
  • 투패스 정렬 방식
    • 정렬 대상과 프라이머리 키 값만 소트 버퍼에 담아서 정렬하고, 그 순서대로 다시 프라이머리 키로 테이블을 읽는 방식
    • 레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 크거나,
      BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함될 때 사용된다.
• 정렬 처리 방법
  ORDER BY는 아래 3가지 처리 방법 중 하나로 정렬이 처리된다. 처리 속도는 밑의 방법일수록 느리다. 
  • 인덱스 사용 방법
    • 실행 계획에서 별도 표기 X
    • 사용 조건
      • ORDER BY에 명시된 칼럼이 제일 먼저 읽을 테이블에 속하고, ORDER BY 순서대로 생성된 인덱스가 있어야 한다.
      • WHERE절에 첫 번째로 읽는 테이블의 칼럼에 대한 조건이 있다면,
        그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
  • 조인에서 드라이빙 테이블만 정렬하는 방법
    • 첫 번째 테이블의 레코드를 정렬한 다음, 조인을 실행하는 방법
    • 실행 계획에 Using filesort 표시
    • 사용 조건
      • 조인에서 첫 번째로 읽히는 테이블의 칼럼만으로 ORDER BY 절을 작성
  • 조인에서 조인 결과를 임시 테이블로 저장 후 정렬하는 방법
    • 실행 계획에 Using temporary; Using filesort
• 쿼리가 처리되는 방식
  • 스트리밍 방식
    • 서버 쪽에서 처리할 데이터 양의 관계없이, 조건에 일치하는 레코드 검색마다 클라이언트에게 전송
    • 응답 속도가 빨라 웹서비스 같은 환경에 적절하다.
  • 버퍼링 방식
    • 서버에서 모든 레코드를 검색하고, 클라이언트에게 전송하는 방식
    • 응답 속도가 느리다.
    • ORDER BY 또는 GROUP BY와 같은 쿼리에서 사용