[RealMysql 8.0] 옵티마지어와 힌트 9 ~ 9.2

• 옵티마이저는 기본 데이터를 비교해 최적의 실행 걔획을 수립하는 작업을 한다.
• 개요
  • mysql 서버에서 쿼리가 실행되는 과정은 크게 세 단계로 나눌 수 있다
    1. 사용자로부터  요청된 SQL 문장을 잘게 쪼개서 mysql 서버가 이해할 수 있는 수준으로 분리(파스 트리)한다.
       • sql 파싱 이라고 하며 mysql 서버의 sql 파서 라는 모듈로 처리한다. sql 문장이 문법적으로 잘못됐다면 이 단계에서 걸러진다.
       • sql 파스 트리가 만들어 진다.mysql 서버는 sql 문장 그 자체가 아니라 sql 파스 트리를 이용해 쿼리를 실행한다.
    2. SQL의 파싱 정보(파스 트리)를 확인하면서 어떤 테이블부터 읽고 어떤 인덱스를 이용해 테이블을 읽을지 선택한다.
       • 첫 번째 단계에서 만들어진 sql 파스 트리를 참조하면서 다음과 같은 내용을 처리 한다.
         • 불필요한 조건 제거 및 복잡한 연산의 단순화
         • 여러 테이블의 조인이 있는 경우 어떤 순서로 테이블을 읽을지 결정
         • 각 테이블에 사용된 조건과 인덱스 통계 정보를 이용해 사용할 인덱스를 결정
         • 가져온 레코드들을 임시 테이블에 넣고 다시 한번 가공해야 하는지 결정
       • 수많은 처리를 하지만 대표적인 작업은 위와 같으며, 
       • 두 번째 단계는 최적화 및 실행 계획 수립 단계이먀, mysql 서버의 옵티마이저 에서 처리된다.
       • 두 번째 단계가 완료되면 쿼리의 "실행 계획"이 만들어 진다.
    3. 두 번째 단계에서 결졍된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터 데이터를 가져온다.
       • mysql 엔진에서는 스토리지 엔진으로부터 받은 레코드를 조인하거나 정렬하는 작업을 수행한다.
    4. 첫 번째 단계와 두 번째 단계는 거의 mysql 엔진에서 처리하며, 세 번째 단계는 mysql 엔진과 스토리지 엔진이 동시에 참여하여 처리한다.
• 옵티마이저의 종류
  • 예전 초기 버전의 오라클 DBMS에서 많이 사용했던 규칙 기반 최적화 방법(Rule-based optimizer, ROB) 으로 나뉜다.
    
    • 기본적으로 대상 테이블의 레코드 건수나 선택도 등을 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행 계획을 수립하는 방식을 의미한다.
    • 거의 항상 같은 쿼리에 대해서는 실행방법을 동일하게 만들어 낸다.
  • 옵티마이저는 현재 대부분의 DBMS가 선택하고 있는 비용 기반 최적화(Cost-based optimizer, CBO) 방법과
    •  쿼리를 처리하기 위한 여러 가지 방법을 만들고
    • 각 단위 작업의 비용(부하) 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출하여, 최소로 소요되는 처리 방식을 선택한다.
• 기본 데이터 처리
• 풀 테이블 스캔과 풀 인덱스 스캔
  • mysql 옵티마이저는 다음과 같은 조건이 일치할 때 주로 풀 테이블 스캔을 선택한다
    • 테이블의 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것보다 풀 테이블 스캔 하는 편이 더 빠른 경우(일반적으로 테이블이 페이지 1개로 구성된 경우)
    • where 절이나 on 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
    • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라고 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은경우(인덱스의 B-Tree를 샘플링해서 조사한 통계 정보 기준)
  • 대부분 DBMS는 풀 테이블 스캔을 실행할 때 한꺼번에 여러 개의 블록이나 페이지를 읽어오는 기능을 내장하고 있다.
    • 하지만 mysql에는 풀 테이블 스캔을 실행할 때 한꺼번에 몇 개씩 페이지를 읽어올지 설정하는 시스템 변수는 없다.
  • MyISAM 스토리지 엔진에서는 풀 스캔을 실행할 때 디스크로부터 페이지를 하나씩 읽어오고,
  • InnoDB 기준으로는 특정 테이블의 연속된 데이터 페이지가 읽히면 백그라운드 스레드에 의해 리드 어헤드(Read ahead) 작업이 자동으로 시작된다.
    • 리드 어헤드? 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측해서 요청이 오기 전에 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것을 의미한다.
    • 즉 풀 테이블 스캔이 실행되면 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘긴다.
    • 백그라운드 스레드는 페이지 읽기를 넘겨받는 시점부터는 한 번에 4개 또는 8개의 페이지를 읽으면서 계속 그 수를 증가시킨다.
    • 이때 한번에 64개의 데이터 페이지까지 읽어서 버퍼 풀에 저장해 둔다.
    • 포그라운ㄷ드 스레드는 미리 버퍼 풀에 준비된 데이터를 가져다 사용하기만 하면 되므로 쿼리가 상당히 빨리 처리가 된다.
    • 시스템 변수를 이용해 리드 어헤드를 언제 시작할지 임계값을 설정할 수 있다.
• 병렬 처리
  • mysql 8.0 버전부터는 용도가 한정돼 있긴 하지만 처음으로 mysql 서버에서도 쿼리의 병렬 처리가 가능해졌다.
  • mysql 8.0에서는 innodb_parallel_read_threads 라는 시스템 변수를 이용해 하나의 쿼리를 최대 몇개의 스레드를 이용해서 처리할지를 변경할 수 있다.
  • 아무런 where 조건없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬로 처리할 수 있다.
• ORDER BY 처리(Using filesort)
  • 인덱스를 이용하는 방법과, 쿼리가 실행 될 때 "Filesort" 라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.

	장점	단점
인덱스 이용	insert, update, delete 쿼리가 실행될 때 이미 인덱스가 정렬돼 있어서 순서대로 읽기만 하면 되므로 매우 빠르다.	insert, update, delete 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 인덱스 때문에 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 InnoDB의 버퍼 풀을 위한 메모리가 많이 필요하다.
Filesort 이용	인덱스를 생성하지 않아도 되므로 인덱스를 이용할 때의 단점이 장점으로 바뀐다. 정렬해야 할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다.	정렬 작업이 쿼리 실행 시 처리되므로 레코드 대상 건수가 많아질수록 쿼리의 응답 속도가 느리다.

• -
  • mysql 서버에서 인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 싱행계획의 Extra 컬럼에 "Using filesort" 메세지가 표시되는 여부로 판단할 수 있다.
• 소트 버퍼
  • mysql은 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용하는데, 이 메모리 공간을 소트버퍼 라고 한다.
  • 소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬해야 할 레코드의 크기에 따라 가변적으로 증가하지만 최대 사용 공간은 시스템 변수로 설정할 수 있다.
  • 쿼리의 실행이 완료되면 즉시 시스템으로 반납된다.
  • 무조건 용량을 늘린다고 빠르지 않고, 소트버퍼는 세션 메모리(로컬메모리)영역에 해당된다. 즉 소트 버퍼는 여러 클라이언트가 공유해서 사용할 수 있는 영역이 아니다.
    • 소트 버퍼를 10mb 이상으로 설정하면 대량의 레코드를 정렬하는 쿼리가 여러 커넥션에서 동시에 실행되면서 운영체제는 메모리 부족 현상을 겪을 수도 있다.
• 정렬 알고리즘
  • 레코드를 정렬할 때 레코드 전체를 소트 버퍼에 담을지 또는 정렬 기준 칼럼만 소트 버퍼에 담을지에 따라 싱글패스, 투 패스 2가지 정렬 모드로 나눌 수 있다.
  • 따로 공부 예정
• GROUP BY 처리
  • group by에 사용된 조건은 인덱스를 사용해서 처리될 수 없으므로 having 절을 튜닝하려고 인덱스를 생성하거나 다른 방법을 고민할 필요는 없다.
  • 따로 공부 예정
• pass