9.1 개요

옵티마이저 : 쿼리를 최적으로 실행하기 위한 실행 계획 수립 담당

9.1.1 쿼리 실행 절차

1. SQL 파싱
   1. 처리 기관 : SQL 파서 모듈 (MySQL 엔진)
   2. SQL 문장을 쪼개 MySQL 서버가 이해할 수 있는 수준으로 분리 (파스 트리)
   3. 만들어진 결과를 SQL 파스 트리라고 함
2. 실행 계획을 수립
   1. 처리 기관 : 옵티마이져 (MySQL 엔진)
   2. SQL 파스 트리를 참조하며 실행 계획을 수립
   3. 불필요한 조건 제거 및 연산 단순화
   4. 여러 테이블의 조인이 있는 경우 읽을 순서 결정
   5. 각 테이블의 조건과 인덱스 통계 정보를 통해 사용할 인덱스 결정
   6. 가져온 레코드들을 임시 테이블에 넣고 다시 가공해야하는지 결정
3. 실행 계획을 통해 데이터를 가져옴
   1. 처리 기관 : MySQL 엔진, 스토리지 엔진
   2. 실행 계획대로 스토리지 엔진에 레코드를 읽어오도록 요청
   3. MySQL 엔진에서는 받은 레코드를 조인하거나 정렬

9.1.2 옵티마이저의 종류

• 규칙 기반 최적화 방식
  • 초기 버전의 오라클 DBMS에서 사용
  • 대상 테이블의 레코드 건수를 고려하지 않고 옵티마이저에 내장된 우선순위에 따라 실행계획을 수립
  • 같은 쿼리에 대해선 거의 항상 같은 실행 방법을 만듬
  • 거의 사용되지 않음
  • 예전 각 테이블이나 인덱스의 통계 정보가 없고 느린 CPU 연산 때문에 비용 계산 과정을 생략하기 위해 채택되어 사용됨
• 비용 기반 최적화
  • 대부분의 DBMS가 사용
  • 여러가지 가능한 방법을 만들고, 각 단위 작업의 비용 정보와 대상 테이블의 예측된 통계 정보를 이용해 실행 계획별 비용을 산출
  • 그 중 비용이 최소가 되는 방식을 선택

9.2 기본 데이터 처리

9.2.1 풀 테이블 스캔과 풀 인덱스 스캔

• 풀 테이블 스캔
  • 데이터를 처음부터 끝까지 읽어서 작업을 처리
  • 다음 조건에서 풀 테이블 스캔을 선택
    • 테이블의 레코드 건수가 작아 인덱스 스캔보다 풀 테이블 스캔이 더 빠른 경우 (일반적으로 테이블이 페이지 1개로 구성된 경우)
      
    • WHERE 절이나 ON 절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
    • 인덱스 레인지 스캔을 사용할 수 있는 쿼리라 하더라도 옵티마이저가 판단한 조건 일치 레코드 건수가 너무 많은 경우 ( B-Tree를 샘플링해서 조사한 통계 정보 기준)
  • MyISAM의 경우 디스크로 부터 한 페이지씩 읽어옴
  • InnoDB의 경우 리드 어헤드 작업을 통해 특정 테이블의 연속된 페이지를 읽게 됨
    • 리드 어헤드 : 어떤 영역의 데이터가 앞으로 필요해지리라는 것을 예측하여 요청이 오기 전에 미리 디스크를 읽어 InnoDB 풀에 미리 가져다 둠
      • 풀 테이블 스캔이 시작될 시 처음 몇 개(임계값)의 페이지를 포그라운드가 읽음
      • 임계값 이후 백그라운가 4개 또는 8개씩의 페이지를 읽으며 최대 64개의 페이지를 읽어 버퍼 풀에 저장
      • 포그라운드 스레드는 버퍼 풀의 데이터를 사용
      • innodb_read_ahead_threshold를 통해 임계값을 설정
        • 일반적으로 디폴트 값으로 충분하지만 데이터 웨어하우스용 MySQL을 사용한다면 더 낮은 값으로 리드 어헤드를 더 빨리 유도하는 것이 좋은 방법
• 풀 인덱스 스캔

  • 인덱스를 처음부터 끝까지 읽음

    mysql> SELECT COUNT(*) FROM employees;

  • 디스크를 읽을 필요없이 인덱스에서 해결할 수 있는 쿼리의 경우 인덱스만으로 스캔하는 것이 효율적

    • 인덱스의 용량이 테이블보다 훨씬 적기 때문

  • 레코드에만 있는 칼럼이 필요한 경우 불가능 → 풀 테이블 스캔

9.2.2 병렬 처리

• MySQL 8.0부터 하나의 쿼리를 여러 스레드가 나누어 동시에 처리 → 이전에는 각각의 쿼리를 동시에 여러 스레드가 처리하는 것은 가능했지만 동시에 한 쿼리를 처리하는 병렬 처리는 없었음
  
• innodb_parallel_read_threads를 통해 하나의 쿼리에 대해 최대 몇 개의 스레드를 처리할지 결정
• 이전 버전에서는 쿼리를 여러 개의 스레드를 이용해 병렬로 처리하게 하는 힌트나 옵셥은 없음 (?????)
• WHERE 조건없이 단순히 테이블의 전체 건수를 가져오는 쿼리만 병렬 처리
• thread가 늘어날 수록 처리 속도가 빨라짐
  • 하지만 CPU의 코어 개수를 넘어서는 경우 성능이 떨어짐 → 컨텍스트 스위치 비용?
    

9.2.3 ORDER BY 처리(Using filesort)

정렬을 처리하는 방법

• 인덱스 이용
  • 장점 :
    • INSERT, UPDATE, DELETE 쿼리가 실행될 때 이미 인덱스가 정렬되어 있어 순서대로 읽기만 하면 되므로 매우 빠름
  • 단점 :
    • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느림
    • 인덱스의 디스크 공간이 필요
    • 인덱스의 개수가 늘어날 수록 InnoDB 버퍼 풀 메모리가 필요
• filesort 이용
  • 장점 :
    • INSERT, UPDATE, DELETE 작업 시 부가적인 인덱스 추가/삭제 작업이 불필요
    • 인덱스의 디스크 공간이 불필
    • 인덱스의 개수가 늘어날 수록 InnoDB 버퍼 풀 메모리가 불필요
  • 단점 :
    • 쿼리 실행 시 정렬 작업이 처리되므로 대상 건수가 많아질수록 쿼리의 응답 속도가 느림
• 다음과 같은 경우 인덱스 정렬이 불가능
  • 정렬 기준이 너무 많아 요건별로 모두 인덱스를 생성하는 것이 불가능한 경우
  • GROUP BY의 결과 또는 DISTINCT 같은 처리의 결과를 정렬해야 하는 경우
  • UNION의 결과와 같이 임시 테이블의 결과를 다시 정렬해야 하는 경우
  • 랜덤하게 결과 레코드를 가져와야 하는 경우

9.2.3.1 소트 버퍼

• 소트 버퍼 : 정렬을 수행하기 위한 별도의 메모리 공간
• sort_buffer_size 시스템 변수를 통해 크기 설정
• 정렬되어야 할 레코드 건수가 소트 버퍼보다 클 경우
  • 레코드를 여러 조각으로 나누어서 처리, 임시로 디스크에 저장
  • 여러 조각의 레코드의 정렬을 수행하면서 디스크의 쓰기 읽기 작업이 반복적으로 발생
  • 멀티 머지 : 각 버퍼의 크기만큼 정렬된 레코드를 다시 병합하면서 정렬
  • 소트 버퍼의 크기가 커진다고 유의미한 속도 증가는 없음
    • 어떤 데이터를 정렬하는지, 서버의 메모리나 디스크의 특성에 따라 결과가 달라짐 → 변수가 많음
      
    • 리눅스 계열의 운영체제에서는 큰 메모리 공간 할당 때문에 성능이 떨어질 수 있음
    • 일반적인 트랜잭션 처리용 MySQL 서버의 소트 버퍼 크기는 56KB~1MB가 적절
  • 소트 버퍼는 세션(로컬) 메모리 영역에 해당
    • 소트 버퍼는 여러 클라이언트가 공유해서 사용하지 못함
    • 커넥션이 많을수록, 정렬 작업이 많을수록 소트 버퍼로 소비되는 메모리 공간이 커짐
    • 메모리 부족 현상이 나올 수 있음 → 운영체제의 OOM-Killer가 메모리를 가장 많이 사용하는 MySQL을 강제 종료시킴
      

9.2.3.2 정렬 알고리즘

🤦 공식명칭은 아님

MySQL 서버의 정렬 방식

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

• 9.2.3.2.1 싱글 패스 정렬 방식
  • <sort_key, rowid> : 정렬 키와 레코드의 ROWID만 가져와서 정렬
    
  • 테이블을 읽을 때 정렬에 불필요한 칼럼까지 같이 소트 버퍼에 담고 정렬을 수행
  • 완료될 시 그대로 클라이언트에게 넘겨줌
• 9.2.3.2.2 투 패스 정렬 방식
  • <sort_key, additional_fields> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드의 칼럼들은 고정 사이즈로 메모리 저장
  • <sort_key, packed_additional_fileds> : 정렬 키와 레코드 전체를 가져와서 정렬하는 방식, 레코드의 칼럼들은 가변 사이즈로 메모리 저장→ 메모리를 효율적으로 사용하기 위해 도입됨
    
  • 정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬하고, 정렬된 순서대로 다시 프라이머리키로 테이블을 읽어 가져옴
  • 투 패스 정렬 방식 이전에 사용되던 방식
  • 테이블을 두번 읽어야함 → 싱글 패스에 비해 소트 버퍼의 메모리를 덜 잡아 먹음 → 싱글 패스의 경우 소트 버퍼에 정렬에 불필요한 칼럼들이 담기게 됨
    
  • 일반적으로 싱글 패스 정렬 방식을 사용
  • 다음과 같은 경우 투 패스 정렬 방식을 채택
    • 레코드의 크기가 max_length_for_sort_data 시스템 변수 보다 클때
    • BLOB이나 TEXT 타입의 칼럼이 SELECT 대상에 포함할 때
• 정렬 대상의 레코드의 크기나 건수가 작은 경우 → 싱글패스
• 정렬 대상의 레코드의 크기나 건수가 많을 경우 → 투 패스
• 이러한 이유로 정렬이 필요한 SELECT의 경우 불필요한 칼럼을 SELECT하지 않게 쿼리를 작성 (*조심)

9.2.3.3 정렬 처리 방법

ORDER BY가 사용될 시 3가지 방법이 실행됨

1. 인덱스를 사용한 정렬 (표시없음) → 인덱스 O

2. 조인에서 드라이빙 테이블만 정렬 (”Using filesort”) → 인덱스 X

   일반적으로 조인이 수행되면 레코드 건수와 레코드의 크기는 거의 배수로 증가

   → 가능하다면 드라이빙 테이블만 정렬

3. 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 (”Using temporary; Using filesort”) → 인덱스 X

   → 드라이빙 테이블만 정렬이 불가능할 경우 선택

   → 2.에 비해서는 레코드 건수와 크기가 커져 느려짐

이 순서대로 번호가 높아질 수 록 일반적으로 처리 속도가 떨어짐

인덱스를 사용할 수 없을 경우 filesort 진행

이때 정렬 대상 레코드를 최소화 하기 위해 2, 3의 방법을 선택

• 9.2.3.3.1 인덱스를 이용한 정렬
  • 인덱스를 이용한 정렬 조건

    • ORDER BY에 명시된 칼럼은 제일 먼저 읽는 테이블(JOIN의 경우 드라이빙 테이블)

    • ORDER BY 순서대로 생성된 인덱스가 존재

    • WHERE 절에 제일 먼저 읽는 테이블의 칼럼에 대한 조건이 있을 시 그 조건과 ORDER BY는 같은 인덱스를 사용해야함

      SELECT *
      FROM A, B
      WHERE A.emp_no = B.emp_no
      ORDER BY a.emp_no;

    • B-Tree 계열 외에 다른 테이블(해시 인덱스, 전문 검색 인덱스, R-Tree 등)은 불가능

    • 조인되는 경우 네스티드-루프 방식 조인만 가능

      • 네스티드 루프 조인(중첩 루프 조인) : 2개 이상의 테이블에서 하나의 집합(드라이빙)을 기준으로 순차적으로 상대방(드라이븐) ROW를 결합하여 조인 → 이중포문처럼 https://coding-factory.tistory.com/756
        
  • ORDER BY가 있든 없든 순서대로 나오지만 ORDER BY절을 명시하는 것을 추천
    • ORDER BY가 있어도 옵티마이저에서 인덱스 정렬에서는 정렬 과정을 생략하기 때문에 추가 작업이 없지만 어떤 이유로 쿼리 실행 계획이 변경된다면 인덱스 정렬의 결과로 나오지 않을 수 있기 때문
  • 조인 버퍼가 사용된 경우 순서가 바뀔 수 있어 주의
• 9.2.3.3.2 조인의 드라이빙 테이블만 정렬 조인이 수행되면 레코드의 건수와 크기가 커지짐 → 조인을 실행하기 전 드라이빙 테이블 만으로 정렬하여 다음 조인을 실행
  • 드라이빙 테이블만으로 정렬 조건

    • ORDER BY 절에서 드라이빙 테이블의 칼럼만으로 작성되어 있어야 함

      SELECT *
      FROM A, B
      WHERE A.emp_no = B.emp_no
      ORDER BY a.col;

• 9.2.3.3.3 임시 테이블을 이용한 정렬
  • 위 조건을 다 만족 시키지 못한 나머지 쿼리의 경우 이 방법을 채택
  • 항상 조인의 결과를 임시 테이블에 저장하고 그 결과를 다시 정렬하는 과정을 거침
  • 정렬해야할 레코드 건수가 가장 많기 때문에 가장 느린 방법

    SELECT *
    FROM A, B
    WHERE A.emp_no = B.emp_no
    ORDER BY B.col;

• 9.2.3.3.4 정렬 처리 방법의 성능 비교 ORDER BY와 LIMIT은 거의 필수로 함께 사용됨 LIMIT은 테이블이나 처리 결과의 일부만 가져오기 때문에 작업량이 줄어듬 하지만 ORDER BY나 GROUP BY의 결과를 LIMIT 건수 만으로 가져와 작업을 처리할 수 없음 → 만족하는 레코드를 모두 가져와서 정렬을 하거나 그루핑을 해야만 이후에 LIMIT건수 제한이 가능 이를 알아보기 위해 쿼리가 처리되는 두가지 방식을 알아야 함

  • 9.2.3.3.4.1 스트리밍 방식

    • 서버 쪽에서 처리할 데이터의 양에 상관ㄴ없이 조건에 일치하는 레코드가 검색 될 때마다 바로 클라이언트에게 전송 → 첫번째 레코드를 곧바로 받기 때문에 빠른 응답 시간을 보장해줌
      
    • LIMIT과 같이 결과 건수를 제한하는 조건들은 스트리밍 방식의 쿼리 실행 시간을 상당히 줄여줌
      • 서버에서 쿼리가 진행중이더라도 첫번째 쿼리는 매우 빠르게 응답
      • LIMIT에 의해 전체적인 레코드 건수가 줄어들 경우 마지막 레코드를 가져오는 시간이 상당히 줄어들게됨

  • 9.2.3.3.4.2 버퍼링 방식

    • ORDER BY나 GROUP BY 같은 처리는 스트리밍 방식이 불가능
    • 조건에 일치한 레코드 건수들을 가져온후 정렬이나 그루핑을 해야 하기 때문
    • MySQL 서버가 모든 레코드를 검색하고 작업을 해야하는 만큼 응답 속도가 느려짐
    • LIMIT 조건으로 레코드 건수를 낮추어도 모든 작업이 MySQL에서 먼저 진행되기 때문에 성능 향상에 도움이 그다지 없음

  • JDBC 라이브러리
    - JDBC 라이브러리를 사용할 경우 MySQL에서 스트리밍으로 처리해도 자체적으로 버퍼를 걸어 모든 레코드가 받아진 후에 작업을 실행
    - 전체 처리 속도가 짧고 MySQL 서버와의 통신 횟수가 적어 자원 소모가 줄어들기 때문
    - 대량의 데이터를 가져오며 스트리밍의 효과를 보고 싶다면 설정을 통해 바꿀 수 있음

    인덱스를 사용한 정렬에서만 스트리밍 방식으로 쿼리를 처리

    어느 테이블이 드라이빙 될 것인가와 어떤 정렬 방식으로 처리할 것인지는 큰 성능 차이를 만든다.

    가능하다면 인덱스 정렬을 유도, 차선책으로 드라이빙 테이블만으로 정렬을 하도록 유도

    ! 인덱싱을 사용한 정렬이 아닌 경우 LIMIT의 조건을 만족하는 상위 건수들을 정렬하기 위해 더 많은 작업을 수행할 수 있다 (??? 뭔말?)

9.2.3.4 정렬 관련 상태 변수

FLUSH STATUS;
SHOW STATUS LIKE 'Sort%';

해당 명령어를 통해 지금까지 처리한 정렬 작업을 확인 할 수 있음

Sort_merge_passes : 멀티 머지 처리 횟수

Sort_range : 인덱스 레인지 스캔 결과 정렬 작업 횟수

Sort_scan : 풀 테이블 스캔 정렬 작업 횟수

Sort_rows : 정렬한 전체 레코드 건수

9.2.4 GROUP BY 처리

GROUP BY는 스트리밍 처리를 할 수 없음

GROUP BY 의 HAVING 은 인덱스를 사용할 수없음

GORUP BY는 다음 3가지의 방법으로 나뉨

• 인덱스 스캔
• 루스 인덱스 스캔
• 임시 테이블

9.2.4.1 인덱스 스캔을 이용하는 GRUOP BY(타이트 인덱스 스캔)

• 조건
  • 조인의 드라이빙 테이블에 속한 칼럼만 그루핑
  • GROUP BY 칼럼에 인덱스가 존재
• 인덱스를 사용하여 GROUP BY를 처리하더라도 그룹 함수 등의 그룹값을 처리하여야 할땐 임시 테이블 필요
• GROUP BY가 이미 정렬된 인덱스를 읽으므로 쿼리 실행 시점에서는 추가적인 정렬 작업이나 내부 임시 테이블 불필요
• 쿼리의 실행 계획에서 별도로 관련 코멘트가 표시되지 않음

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

• 루스 인덱스 스캔이 사용될 수 있을지 없을지 판단하는 기준은 명확하게 만들기 어려움
  • MIN()과 MAX() 이외의 집합 함수 사용시 불가능
  • GROUP BY에 사용된 칼럼이 인덱스 구성 칼럼의 왼쪽부터 일치해야함
  • SELECT 절의 칼럼이 GROUP BY와 일치해야함
  • 단일 테이블에 수행되는 GROUP BY에서만 가능
  • 프리픽스 인덱스(칼럼 앞쪽 일부만으로 생성된 인덱스)는 불가능
• 루스 인덱스 스캔의 GROUP BY 과정
  1. 인덱스를 차례로 스캔하며 첫번째 유일값(그룹키)를 찾음
  2. 찾아낸 유일값에서 쿼리의 조건에 맞는 레코드를 읽음
  3. 그 다음 유니크한 값을 가져옴
  4. 3.의 결과가 없으면 종료, 있다면 2.을 반복
• 유니크 한 값이 적을 수록(분포도가 좋지 않을 수록) 더 빠른 결과를 만들어냄
  • 인덱스 레인지 스캔의 경우 유니크 한 값이 많을 수록 성능이 좋음
  • 인덱스 스킵 스캔의 또한 루스 인덱스 스캔 방식으로 최적화 됨 → 선행 칼럼의 유니크한 값이 많으면 쿼리 처리 성능이 떨어짐 → 선행 칼럼의 유니크한 값이 많아지면 인덱스 스킵 스캔 최적화를 진행하지 않음
    
• 임시 테이블을 사용하지 않음

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

GROUP BY 기준 칼럼이 드라이빙 테이블 있든 드리븐 테이블 있든 상관없이 인덱스를 전혀 사용하지 못할때 처리됨

• MySQL 8.0 이전
  • 그루핑되는 칼럼 기준으로 묵시적인 정렬 수행
  • 정렬이 불필요 한 경우 “ORDER BY NULL”을 사용
    • 크진 않지만 성능향상은 볼 수 있음
• MySQL 8.0
  • 내부적으로 GROUP BY 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만듬
  • 과정
    1. 임시 테이블을 생성
    2. 조인의 결과를 한 건씩 가져와 임시 테이블에서 중복체크를 하며 INSERT 또는 UPDAT 실행
  • 별도의 정렬 작업이 없음
  • ORDER BY와 함께 사용할 경우 명시적으로 정렬 작업 실행
    • 쿼리의 실행계획에 “Using temportary”와 “Using filesort” 표시

9.2.5 DISTINCT 처리

• 집합함수의 경우 DISTINCT가 영향을 미치는 범위가 달라짐
  • 집합함수를 사용할 경우
    • DISTINCT 처리에 인덱스를 사용하지 못할 경우 임시 테이블이 필요
    • 실행 계획에는 “Using temporary”가 출력되지 않음

9.2.5.1 SELECT DISTINCT ….

• SELECT 되는 레코드 중 유니크한 레코드만 가져오고자 할때 사용
• GROUP BY와 동일한 방식으로 처리
  • MySQL 8.0이후부터는 ORDER BY절이 없을 경우 정렬과정이 없어 내부적으로도 동일
• 특정 칼럼만 유니크하게 조회하는 것이 아닌 조합 전체가 유니크한 레코드를 가져움

  SELECT DISTINCT a, b FROM t;
  SELECT DISTINCT (a),b FROM t;
  // 이 둘은 동일

• DISTINCT 뒤의 괄호는 MySQL이 의미없다 해석하고 제거하고 실행
  • DISTINCT는 함수가 아님
• SELECT 절에 사용된 DISTINCT 키워드는 조회되는 모든 칼럼에 영향을 미침

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

• 집합 함수 내에서 DISTINCT 키워드가 사용될 수 있음
• 집합 함수의 인자로 전달된 칼럼값이 유니크 한값들만 가져옴

  SELECT COUNT(DISTINCT t_b.b)
  FROM t_a, t_b;

• 이 경우 DISTINCT t_b.b 를 처리하기 위해 임시테이블을 사용
• t_a와 t_b를 조인한 결과에서 t_b의 칼럼의 값만 저장하기 위한 임시 테이블이 필요
  • 이때 t_b에 대한 유니크 인덱스가 생성되기 때문에 건수가 많아지면 상당히 느려짐
• 또다른 인덱스를 사용할 수 없는 COUNT 가 추가될 경우 그에 대한 임시 테이블이 필요
• 인덱스된 칼럼에 대해 DISTINCT 처리를 할 경우 임시 테이블이 불필요

  SELECT COUNT(DISTINCT a) FROM t_a;
  SELECT COUNT(DISTINCT a) FROM t_a GROUP BY b;

9.2.6 내부 임시 테이블 활용

• 정렬 (ORDER BY)나 그루핑 (GROUP BY) 작업을 할때 내부적으로 임시 테이블을 생성
• “CREATE TEMPORARY TABLE”과는 다름
• 일반적으로 메모리에 생성되었다 크기가 커질경우 디스크로 옮겨짐 → 특정 예외케이스의 경우 바로 디스크에 생성되기도 함
  
• 내부적 가공을 위한 임시 테이블의 경우 쿼리의 처리이후 자동으로 삭제
  • 이 경우 다른 세션이나 다른 쿼리에서는 보거나 사용이 불가능

9.2.6.1 메모리 임시 테이블과 디스크 임시 테이블

• MySQL 8.0 이전
  • 원본 테이블의 스토리지 엔진과 관계없이 임시 테이블은 MEMORY 스토리지 엔진 사용
  • 디스크에 저장될 때는 MyISAM 스토리지 엔진 사용
• MySQL 8.0 이후
  • 메모리는 TempTable 사용
    • internal_tmp_mem_storage_engine 시스템 변수를 통해 설정 가능
      • MEMORY
        • tmp_table_size, max_heap_table_size 두 변 수를 통해 테이블 크기 조절
      • TempTable (기본값)
        • temptable_max_ram을 통해 테이블 크기 조절 가능 (기본 1GB)
  • 디스크 MMAP
    • temptable_use_mmap (기본값 ON)
      • MMAP (기본값)
        • 기본값인 이유는 MMAP가 오버헤드가 적기 때문
      • InnoDB
    • 디스크에 생성되는 임시 테이블은 tmpdir 시스템 변수에 정의된 디렉토리에 저장됨
      • MySQL 서버는 임시테이블 생성시
        • 파일 오픈후 바로 삭제
        • 데이터 저장시 임시 테이블 사용
      • 서버 종료나 쿼리 종료시 임시 테이블은 바로 삭제
        • 다른 스레드에서 혹은 외부 사용자가 확인 불가능
  • 메모리를 거치지 않고 바로 디스크에 생성될 시 InnoDB
    • internal_tmp_dist_storage_engine 시스템 변수 사용
      • InnoDB 기본값

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

MySQL 엔진에서 별도의 데이터 가공 작업이 필요한 경우

• ORDER BY와 GROUP BY에 명시된 칼럼이 다른 쿼리
  • “Using temporary” 표시
  • 유니크 인덱스가 있는 내부 임시 테이블 생성
  • 유니크 인덱스가 있는 내부 임시 테이블 생성
• ORDER BY나 GROUP BY에 명시된 칼럼이 조인의 순서상 첫번째 테이블이 아닌 쿼리
  • “Using temporary” 표시
  • 유니크 인덱스가 있는 내부 임시 테이블 생성
• DISTINCT와 ORDER BY가 동시에 존재하는 경우 또는 DISTINCT가 인덱스로 처리되지 못하는 쿼리
  • “Using temporary” 표시
  • 유니크 인덱스가 있는 내부 임시 테이블 생성
• UNION이나 UNION DISTINCT가 사요ㅕㅇ된 쿼리(select_type 칼럼이 UNINON RESERT인 경우)
  • “Using temporary” 표시 x
  • 유니크 인덱스가 있는 내부 임시 테이블 생성
  • MySQL 8.0 이전
    • UNION ALL 도 임시 테이블 생성
  • MySQL 8.0
    • UNION ALL은 임시 테이블을 생성하지 않음
  • MySQL에서 UNION은 DISTINCT를 생략한것으로 판단 → UNION DISTINCT와 UNION 동일
  • 여러 결과 집합에서 중복을 제거하는 작업이 필요하기 때문에 임시 테이블을 이용한 중복 제거 작업 필수
• 쿼리의 실행 계획에서 select_type이 DERIVED인 쿼리
  • “Using temporary” 표시 x
  • 유니크 인덱스가 없는 내부 임시 테이블 생성

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

• 조건
  • UNION이나 UNION ALL에서 SELECT되는 칼럼 중에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • GROUP BY나 DISTINCT 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
  • 메모리 임시 테이블의 크기가
    • MEMORY 스토리지 엔진에서 설정한 값보다 큰 경우
    • TempTable 스토리지 엔진에서 설정한 값보다 큰 경우
  • MySQL 8.0.13 이전 BLOB이나 TEXT 칼럼을 가진 경우
  • MySQL 8.0.13 이후 BLOB이나 TEXT 칼럼을 가져도 MEMORY 스토리지 엔진을 쓸 경우

9.2.6.4 임시 테이블 관련 상태 변수

FLUSH STATUS; // 해당 명령어를 통해 현재 세션값 초기화
쿼리 실행
SHOW SESSION STATUS LIKE 'Created_tmp%';

를 통해 상태 변수 확인 가능

• Created_tmp_tables : 생성된 내부 임시 테이블 개수를 누적하는 값 → 디스크인지 메모리인지 구분 x
• Created_tmp_dist_tables : 디스크에 생성된 내부 임시 테이블 누적 값

9.3 고급 최적화

옵티마이저의 옵션

• 조인과 관련된 옵티마이져 옵션
• 옵티마이져 스위치
  • MySQL 5.5부터 지원
  • MySQL 서버의 고급 최적화 기능들을 활성화 할지를 제어

9.3.1 옵티마이저 스위치 옵션

스위치 이름	기본값	스위치 이름	기본값	스위치 이름	기본값
batched_key_access	off	index_merge_intersection	on	firstmatch	on
block_nested_loop	on	index_merge_sort_union	on	loosescan	on
engine_condition_pushdown	on	index_merge_union	on	materialization	on
index_condition_pushdown	on	mrr	on	subquery_materialization_cost_based	on
use_index_extensions	on	mmr_cost_based	on
index_merge		semijoin	on

• 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정 가능

// 글로벌 설정
SET GLOBAL optimizer_switch='index_merge=on,.....'
// 현재 커넥션 만 설정
SET SESSION optimizer_switch='index_merge=on,...'

• “SET_VAR”를 통해 옵티마이저 힌트를 이용해 현재 쿼리에서만 적용 가능

SELECT /*+ SET_VAR(optimizer_switch='ccondition_fanout_filter_off') */
.....

9.3.1.1 MRR과 배치 키 액세스 (mmr & batched_key_access)

• Multi-Range Read의 약자
• 원래 기본 조인 방식은 네스티드 루프 조인(9.2.3.3.1 참고)을 사용
  • 실제 레코드를 검색하고 읽는 것은 스토리지 엔진
  • 스토리지 엔진에서는 최적화가 불가능
• MRR은 조인 버퍼에 조인 대상의 테이블 중 하나의 레코드를 읽어 버퍼링함
  • 드라이빙 테이블 레코드를 읽은 후 드리븐 테이블의 레코드와 즉시 조인을 실행하지 않음
  • 조인 버퍼가 가득 차면 레코드를 다시 스토리지 엔진에게 요청
  • 레코드들을 정렬된 순서로 접근하여 디스크 데이터 페이지 읽기를 최소화 → 데이터 페이지가 메모리(InnoDB 버퍼 풀)에 있어도 버퍼 풀 접근을 최소화
    
• BKA(Batched Key Access)라고도 함
• 기본값 비활성 (off)
  • 단점 : 부가적인 정렬 작업이 필요해지면 오히려 성능에 안좋은 영향을 미침

9.3.1.2 블록 네스티드 루프 조인 (block_nested_loop)

조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용

• 네스티드 루프 조인과 블록 네스티드 루프 조인의 가장 큰 차이점
  • 조인 버퍼가 사용되는지 여부
    • Block이라는 단어가 별도의 버퍼를 사용했다는 의미
    • 조인의 쿼리 실행 계획에서 “Using Join buffer”가 표시
  • 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되는지
• 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드린 테이블을 검색

  for (Record drivingRecord : drivingTable){
  	for (Record drivenRecord : drivenTable){
  			......
  	}
  }

  • 드라이빙 테이블은 한번 읽지만 드리븐은 여러번 읽음
• 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립
• 드리븐 테이블의 풀 테이블 스캔 혹은 인덱스 풀 스캔을 피할 수 없다면
  • 읽은 드라이빙의 테이블 레코드를 메모리에 캐쉬(조인 버퍼)
  • 드리븐 테이블과 메모리 캐시(조인 버퍼)를 조인
• 조인 버퍼
  • join_buffer_size 시스템 변수로 크기 제한
  • 조인 완료 시 바로 해결
• 과정

  // 카테시안 조인 (조인 조건절이 없는 조인)
  SELECT *
  FROM driving_table t_a, driven_table t_b
  WHERE t_a.a > 1 AND t_b.b < 1;

  1. 드라이빙 테이블의 인덱스를 통해 만족하는 레코드를 검색
  2. 조인에 필요한 나머지 칼럼을 모두 읽어 조인 버퍼에 저장
  3. 드리븐 테이블의 프라이머리 키를 이용해 만족하는 레코드를 검색
  4. 3에서 검색된 결과에 2번에 캐시된 레코드를 결합
• 이때 마지막 4번에서 드라이빙의 캐시된 레코드가 드리븐에 결합되므로 정렬의 순서가 흐트러질 수 있음(드라이빙의 순서로 정렬되지 않음)
• MySQL 8.0.18 이후
  • 해시 조인 알고리즘 도입
• MySQL 8.0.20 이후
  • 블록 네스티드 루프를 더이상 사용하지않음 → 버퍼가 사용안된다는 뜻인가?????

9.3.1.3 인덱스 컨디션 푸시다운 (index_condition_pushdown)

ALTER TABLE test ADD INEXT ix_test (col_a,col_b);
SET optimize_switch='index_condition_pushdown=off';
SELECT * FROM test WHERE col_b = 't' ANd col_a LIKE '%t';

• col_a =’t’는 인덱스 레인지 스캔 가능
• col_b LIKE ‘%t’는 인덱스의 범위를 좁힐 수 없음 (뒷 문장 일치라) → 풀스캔 필요
  • 쿼리 실행 계획을 볼경우 “Using where”이 표시
  • WHERE 조건 일치 검사 과정
• 이때 인덱스 컨디션 푸시다운이 비활성화 되어 있을 경우
  • 과정
    1. col_a = ‘t’ 를 수행하여 해당하는 인덱스를 통해 디스크에서 레코드를 가져옴
    2. 그 중에서 col_b LIKE ‘%t’에 해당하는 레코드를 구분함
  • 이때 1.과정에서 불필요한 레코드를 일게됨
• 인덱스 컨디션 푸시다운이 활성화 될 경우
  • 과정
    1. 우선 col_a = ‘t’ 를 수행하여 해당하는 인덱스를 구별
    2. 그 중에서 col_b LIKE ‘%t’에 해당하는 인덱스를 구별하고 레코드에서 가져옴
  • 불필요한 디스크 읽기가 줄어듬
  • “Using where” 대신 “Using index condition”이 출력
• engine_condition_pushdown 옵티마이져 스위치
  • 내부 작동 방식은 index_condition_pushdown과 흡사
  • NDB(MySQL Cluster)에서만 사용 가능
    • DB 복제의 일종으로 Master-slave의 수직적 구조에서 수평적 구조로 바뀐 것

9.3.1.4 인덱스 확장(use_index_extensions)

InnoDB 스토리지 엔진에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션

• 모든 세컨더리 인덱스는 리프 노드에 프라이머리 키 값을 가짐

CREATE TABLE test(
	test_no INT NOT NULL,
	pk_a CHAR NOT NULL,
	pk_b INT NOT NULL,
	pk_c INT NOT NULL,
	PRIMARY KEY (pk_a,pk_b),
	KEY ix_pk_c (pk_c)
)ENGINE=InnoDB;

• 프라이머리 키는 칼럼의 순서대로 pk_a, pk_b가 되며 세컨더리 인덱스는 pk_c가 됨
• 이때 InnoDB이므로 리프노드에 항상 (pk_a, pk_b, pk_c)가 저장
  • 옵티마이저는 숨겨진 pk를 인지하고 이를 활용하며 실행 계획을 수립
• 정렬작업시에도 인덱스를 활용하여 처리가 됨
  • “Using filesort”가 표시되지 않는 것으로 확인 가능

9.3.1.5 인덱스 머지 (index_merge)

인덱스를 이용하여 쿼리를 실행할 시 테이블별로 하나의 인덱스만 사용하도록 실행 계획 수립

인덱스 머지 : 하나의 테이블에 대해 2개 이상의 인덱스를 이용

조건 :

• 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 이용할 수 있음
• 조건을 만족하는 레코드 건수가 많을 것으로 예상

병합 방법에 따라 3가지로 구분

• index_merge_intersection
• index_merge_union
• index_merge_sort_union

9.3.1.6 인덱스 머지 - 교집합 (index_merge_intersection)

// first_name과 emp_no은 각각의 인덱스를 가지고 있다
SELECT *
FROM employees
WHERE first_name='Georgi' and emp_no BETWEEN 10000 AND 20000;

• 이 경우 우선 옵티마이저는 각각의 조건(first_name = ‘Georgi’ 와 emp_no BETWEEN 10000 AND 20000)의 레코드 건수를 예측
• 이때 각각의 조건이 상대적으로 많은 레코드를 가져와야 할 경우

  SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
  253
  SELECT COUNT(*) FROM employees WHERE BETWEEN 10000 AND 20000;
  10000

• 두 인덱스의 교집합을 통해 레코드를 가져옴

  SELECT COUNT(*) FROM employees WHERE first_name='Georgi' and emp_no BETWEEN 10000 AND 20000;
  14

• 위 두 상황을 비교할때
  • first_name 인덱스 만 사용할 때 : 253건을 읽고서 14건을 찾으므로 나머지 239건은 의미없는 작업
  • emp_no 인덱스만 사용할 때 : 10000건을 읽고서 14건을 찾으므로 나머지 9986은 의미없는 작업
  • 인덱스 머지를 사용한 경우 : 14건만 읽으므로 읽기 작업에서는 효율적
• firstname 인덱스에 emp_no이 프라이머리키로 포함되어 있을 경우 firstname인덱스만 사용하는 것이 효율적 → index_merge_intersection 최적화를 비활성화

9.3.1.7 인덱스 머지 - 합집합 (index_merge_union)

• WHERE 절의 OR 연산자에 최적화

SELECT *
FROM employees
WHERE first_name = 'matt' OR hire_date='1987-03-31';

• Union 알고리즘 정렬 알고리즘 두 조건의 인덱스를 합병하는 과정에서 first_name = 'matt' 와 hire_date='1987-03-31' 에서는 중복된 데이터가 존재하게 됨 두 결과 결과 집합을 정렬해서 중복 레코드를 제거해야함 과정
  • 조건에 해당하는 각각의 인덱스에서 결과 집합을 가져옴
  • 해당 집합들이 같은 프라이머리키로 정렬이 되어있음
  • 각각의 집합에서 하나씩 가져와 값을비교하면서 프라이머리 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러냄
  • 이때 사용되는 알고리즘은 우선순위 큐를 사용
• AND 연산자와 OR 연산자는 성능상 큰 차이를 보임
  • 2개의 조건으로 연결된 AND의 경우 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔 실행
  • 2개의 조건으로 연결된 OR의 경우 하나라도 인덱스를 사용할 수 없으면 풀 테이블 스캔을 사용해야함

9.3.1.8 인덱스 머지 - 정렬 후 합집합 (index_merge_sort_union)

• UNION 알고리즘은 정렬이 필요한 경우 인덱스를 통해 가져온 칼럼 집합들이 같은 프라이머리 키로 정렬이 되어있지 않은 경우 중복을 제거하기 위해 정렬을 하기 위해 각 집합을 동일한 칼럼으로 정렬한 다음 중복을 제거 → ?? 제대로 이해한것인지는 모르겠는데 그러면 각 인덱스에 세컨더리든 뭐든 동일한 칼럼이 존재해야하는 것 아닌가? 그리고 정렬 기준이 되는 칼럼을 선정하는 방법은? 그 후 정렬 후에는 똑같이 우선순위 큐로 정렬로 중복제거를 하나?
  

9.3.1.9 세미 조인 (semijoin)

SELECT *
FROM master_table master
WHRE master.m_no In
	(SELECT sub.sub_no FROM sub_table sub WHERE sub.col = 1);

다른 테이블과 실제 조인을 수행하지 않고 단지 다른 테이블에서 조건(서브쿼리)에 일치하는 레코드가 있는지 체크만 하는 형태의 쿼리

• MySQL 5.7
  • 테이블 풀 스캔을 하면서 서브쿼리의 조건에 일치하는지 비교하면서 실행
• MySQL 8.0
  • 세미 조인과 안티 세미 조인으로 최적화 방법이 나뉨
    • 세미 조인 = (subquery) 와 IN (subquery)
      • 세미 조인 최적화
      • IN-to-EXISTS 최적화
      • MATERIALIZATION 최적화
    • 안티 세미 조인 <> (subquery)와 NOT IN (subquery)
      • IN-to-EXISTS 최적화
      • MATERIALIZATION 최적화
  • 서브쿼리 최적화중 세미 조인 최적화가 가장 최근에 도입됨 쿼리에 사용되는 테이블과 조인 조건의 특성에 따라 옵티마이저가 전략을 선별적으로 사용 최적화 전략 :
    • Table Pull-out
      • 사용 가능할 경우 항상 세미 조인보다 좋은 성능을 보이기 때문에 제어 옵션이 없음
    • Duplicate Weed-out
    • First Match
    • Loose Scan
    • Materialization

9.3.1.10 테이블 풀-아웃 (Table Pull-out) (세미조인)

세미 조인의 서브 쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후 쿼리를 조인 쿼리로 재작성

• 서브쿼리 최적화 도입전 수동으로 쿼리를 튜닝하던 대표적 방법
• 실행 계획의 해당 테이블들의 id 칼럼 값이 같음 → extra에 따로 표시되지 않음
  • SHOW WARNINGS 명령을 통해 재작성 되었는지 확인 가능
• 제한 사항 및 특징
  • 세미 조인 서브 쿼리에서만 사용 가능
  • 서브쿼리 부분이 UNIQUE 인덱스나 프라이머리 키 룩업으로 결과가 1건인 경우에만 사용 가능
  • 기존 쿼리에서 가능했던 최적화 방법이 사용 가능
    → MySQL에서는 가능하면 최대한 Table pull out을 적용
  • 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어짐
    → 당연한 말 아닌가???
  • 최대한 서브쿼리를 조인으로 풀어서 사용해라라는 튜닝 가이드를 따름
    → 자동으로 해주므로 더이상 서브쿼리를 튜닝가이드 대로 풀 필요가없음

9.3.1.11 퍼스트 매치 (firstmatch) (세미조인)

IN (subquery)를 EXISTS (subquery) 형태로 튜닝한 것과 유사한 방법으로 실행

• 실행 계획
  • id값이 전부 동일한 값 (서브쿼리 패턴이 아닌 조인으로 처리)
  • “FistMatch”출력

SELECT *
FROM employees e WHERE e.first_name='matt'
AND e.emp_no IN (
	SELECT t.emp_no FROM titles t
	WHERE t.from_date BETWEEN ~~~~
)

• 과정
  • FROM employees e WHERE e.first_name='matt' 을 통해 fist_name=’matt’인 레코드에 접근
  • 이때 서브쿼리 조건에 일치하는 조건을 처음 발견하면 결과를 반환하고 다음 first_name=’matt’에 해당하는 레코드에 접근
    • 처음 발견한 결과만 반환하고 나머지는 무시
• IN-to-EXISTS와 거의 비슷하지만 다음과 같은 장점이 있음
  • 여러 테이블이 조인되는 경우 원래 쿼리에 없던 동등 조건을 추가(동등 조건 전파)
    • IN-to-EXISTS는 서브쿼리 내에서만 가능 → firstmatch는 아우터 쿼리까지 전파
  • IN-to-EXISTS 변환 최적화에서는 조건없이 변환이 가능한 경우에만 최적화 수행
    • firstMatch 최적화에서는 서브쿼리의 모든 테이블에 대해 FirstMatch를 수행할지 일부 테이블에 대해서만 수행할지 취사 선택 가능
• 제한사항 및 특징
  • 서브쿼리가 참조하는 모든 아우터 테이블이 먼저 조회된 이후에 실행
  • 실행 계획에 ‘FirstMatch(table-n)’이 표시
  • 상관 서브쿼리에서도 사용될 수 있음
    • 상관 서브쿼리 : 아우터 쿼리에서 값을 받아쓰는 경우, 아우터 쿼리가 없으면 단독으로 쓰일 수 없음
  • GROUP BY나 집합 합수가 사용된 서브쿼리에는 사용 불가

9.3.1.12 루스 스캔 (loosescan) (세미조인)

GROUP BY 최적화의 루스 인덱스 스캔과 비슷한 읽기 방식 (9.2.4.2)

• 실행 계획
  • LostScan 표시
  • id 값들이 동일 (내부적으로 조인처리됨)
• 특성
  • 드라이빙 테이블로 서브 쿼리 테이블, 드리븐으로 아우터 테이블을 사용하여 조인
  • 서브쿼리 부분이 루스 인덱스 스캔을 수행할 수 있는 조건들이어야함(9.2.4.2)

    SELECT ... FROM ... WHERE expr IN (SELECT keypart1 FROM tab WHERE ...)
    SELECT ... FROM ... WHERE expr IN (SELECT keypart2 FROM tab WHERE keypart1='상수'....))

9.3.1.13 구체화 (Materialization) (세미조인)

• 아우터 쿼리의 테이블에 서브쿼리외에 아무 조건이 없어 테이블 풀 스캔을 해야할 때 firstMatch를 사용할 수 없음 → 서브쿼리에 대해 임시 테이블을 생성, 아우터 쿼리와 조인을 하여 결과를 반환
  
• 제한 사항 및 특성
  • IN (subquery)에서의 서브쿼리는 상관 서브쿼리가 아니어야함 (단독 서브쿼리)
  • 서브쿼리내 GROUP BY나 집합 함수를 사용 가능
  • 내부 임시 테이블 사용

9.3.1.14 중복 제거 (Duplicated Weed-out) (세미조인)

서브쿼리를 INNER JOIN + GROUP BY 쿼리로 바궈 실행 후 중복 레코드 제거하는 방법

• 과정
  • 서브쿼리의 결과 집합과 아우터 테이블을 INNER JOIN 하여 임시 테이블에 저장
  • 임시 테이블에 저장된 결과를 중복제거 실행
  • 결과 반환
• 실행 계획
  • 별도 표시 없음
  • ‘start temporary’ ‘end temporary’ 표시 (임시 테이블로 저장하는 작업이 반복적 실행)
• 제약 사항 및 특성
  • 상관 서브쿼리여도 사용 가능
  • 서브쿼리에 GROUP BY 및 집합 함수 사용 불가
  • 조인으로 처리되기 때문에 최적화 방법이 많음
• Duplicated Weedout의 쿼리를 생성하기 힘듬
  • Materialization, FirstMatch, LooseScan의 스위치를 끄고 테스트 가능

9.3.1.15 컨디션 팬아웃 (condition_fanout_filter)

• 다음과 같은 조건을 만족하는 칼럼들에 대해 미리 레코드의 비율을 예측하고 실행계획을 수립
  • WHERE 조건절에 사용된 칼럼에 대해 인덱스가 있는 경우
  • WHERE 조건절에 사용된 칼럼에 대해 히스토그램이 존재하는 경우
• 쿼리의 실행 계획 수립에 더 많은 시간과 컴퓨팅 자원을 사용
  • 쿼리가 간단하고, MySQL 8.0이전 버전에서의 실행 계획이 잘못된 경우가 별로 없을 경우 성능향상에 별 도움이 없을 수 있음
  • 쿼리의 빈도가 높을 경우 실행 계획 수립의 오버헤드가 추가될 수 있으므로 적용전 성능테스트를 권장
• MySQL 옵티마이저의 실행계획 수립 방식 채택 순서
  1. 레인지 옵티마이저 예측
     • 가장 우선순위가 높음
     • 쿼리가 실행되기전 수립단계에서 소량의 데이터를 읽어봄
     • 인덱스를 이용한 쿼리만 가능
  2. 히스토그램
  3. 인덱스 통계
  4. 추측에 기반한 예측

9.3.1.16 파생 테이블 머지 (derived_merge)

SELECT ... FROM (subquery) ....

From절에 서브쿼리가 있는 경우

• MySQL 5.7 이전
  • 임시테이블을 통해 서브 쿼리 처리후 아우터 쿼리 해결
  • 파생 테이블 (Derived Table) : From 절에 사용된 서브쿼리의 임시 테이블
  • 실행 계획의 select_type 칼럼에 DERIVED로 표시
  • 임시 테이블에 서브쿼리 데이터를 insert하고 다시 읽으므로 레코드를 복사하고 읽는 작업이 추가
    • 레코드 수가 많아질 경우 메모리가 아닌 임시테이블을 디스크에 복사하면서 쿼리의 성능이 떨어짐
• MySQL 5.7 이후
  • derived_merge 최적화 옵션을 통해 파생 테이블 서브쿼리를 아우터 쿼리와 병합
  • 임시테이블 생성 없이 단순 실행 계획으로 변환
  • 다음과 같은 경우 쿼리 병합을 할 수 없음
    • 집계 함수와 윈도우 함수가 사용된 서브쿼리
    • DISTINCT가 사용된 서브쿼리
    • GROUP BY나 HAVING이 사용된 서브쿼리
    • LIMIT이 사용된 서브쿼리
    • UNION또는 UNION ALL이 사용된 서브쿼리
    • SELECT 절에 사용된 서브쿼리
      • 이상한거 → select절의 아우터 쿼리를 의미하나???? → 근데 예제에는 SELECT절을 사용했는데???? → 이 예제들이 된다는 거임 안된다는 거임??? 근데 책에는 자동으로 외부 쿼리를 병합할수 없다라고 써있는데???
        
    • 값이 변경되는 사용자 변수가 사용된 서브쿼리

9.3.1.17 인비저블 인덱스 (use_invisible_indexes)

• MySQL 8.0 이전
  • 실행 계획 수립 시 항상 해당 인덱스를 검토하고 사용
• MySQL 8.0 이후
  • 인덱스를 INVISIBLE 상태로 변경하여 옵티마이저가 검토하지 못하게 가능

    // 인덱스 사용 못하게 설정
    ALTER TABLE ... ALTER INDEX idx_name INVISIBLE;
    
    // 인덱스 사용하게 설정
    ALTER TABLE ... ALTER INDEX idx_name VISIBLE;

  • 해당 스위치 설정을 통해 INVISIBLE 상태의 테이블도 사용 가능

    SET optimizer_switch='use_invisible_indexes=on';

9.3.1.18 스킵 스캔 (skip_scan)

인덱스 스킵 스캔은 제한적이지만 선행 칼럼이 사용되지 않은 조건절의 제약 사항을 무시하는 기법

ALTER TABLE test_table ADD INDEX idx_a_col_b_col (a_col,b_col);
// 인덱스를 사용하지 못하는 경우
SELECT * FROM test_table WHERE b_col >= 1;
// 인덱스를 사용할 수 있는 경우
SELECT * FROM test_table WHERE a_col = 1 AND b_col>=1;

인덱스 스킵 스캔을 활성화 할 경우

• 선행칼럼 (a_col)의 모든 유니크한 값을 검색
• 그 중 후 조건에 맞는 후행 컬럼을 탐색하여 찾음

때문에 선행 칼럼의 유니크 한 값이 많아 질 수 록 비효율적

→ 해당 기능을 비활성화 시켜 성능을 향상 시킬 수도 있음

9.3.1.19 해시 조인 (hash_join)

• 네스티드 루프 조인
  • 첫번째 레코드를 찾는 속도가 빠름 → 최고 응답 속도 전략에 적합 (ex 웹서비스)
    
• 해시 조인
  • 최종 레코드를 찾는 데가지 시간이 빠름 → 최고 스루풋 전략에 적합 (ex 분석과 같은 서비스)
    
• MySQL은 온라인 트랙잭션 처리를 위한 범용 RDBMS → 해시 조인보다 네스티드 루프 조인에 최적화 되어있음
  • 해시 조인을 사용하는 경우
    • 조인 조건의 칼럼에 인덱스가 없을 경우
    • 조인 대상 테이블 중 일부의 레코드 건수가 매우 적은 경우
  • 해시조인 역사
    • MySQL 8.0.17 이전
      • 블록 네스티드 루프 조인 사용(9.3.1.2 참고)
    • MySQL 8.0.18, 8.0.19
      • 동등 조인(Equi-join)을 위한 해시 조인 사용
    • MySQL 8.0.20
      • 블록 네스트드 루프 조인을 사용하지 않고 해시 조인만 사용
  • 블록 네스티드 조인이 전부 해시조인으로 대체 → optimizer_switch의 블록 네스티드와 관련된 스위치(BNL, NO_BNL)는 해시조인 유도를 위해 사용
    
  • 실행 계획
    • hash join 표시
  • 해시 조인의 최적화 단계(클래식 해시 조인) 1) 빌드 단계 (Build-phase)

    • 조인 대상 테이블 중 레코드 건수가 적은 테이블(빌드 테이블)을 통해 메모리에 해시 테이블 생성

      2) 프로브 단계 (Probe-phase)

    • 빌드 테이블을 제외한 나머지 테이블(프로브 테이블)을 읽어서 해시 테이블 일치 레코드를 찾음

  • 조인 버퍼 (그레이스 해시 조인)

    • 해시 테이블을 메모리에 저장할 때 사용

    • join_buffer_size 를 통해 크기 제어 (256KB가 기본값)

    • 레코드 건수가 많을시

      1) 해시 테이블을 준비(빌드 과정)하다가 해시 테이블이 너무 커짐
      
      2) 빌드 테이블의 나머지 레코드를 일정한 크기 (청크)로 나누어 저장 (빌드 테이블 청크)
           프로브 테이블 또한 청크로 나누어 저장 (프로브 테이블 청크)
      
      3) 해시 테이블과 프로브 테이블을 통해 1차 조인을 실행하고 결과를 반환
      
      4) 빌드 테이블 청크의 1번 청크를 읽고 다시 메모리 해시 테이블 구축
      
      5) 다시 구축한 테이블을 통해 프로브 테이블을 통해 2차 조인 실행
      
      6) ???? 빌드 테이블의 청크만큼 (4~5)반복????
      
      → 청크의 개수 만큼 조인 결과 생

      

      

• 해시 조인에 사용되는 해시 키는 xxHash64를 사용

9.3.1.20 인덱스 정렬 선호(prefer_ordering_index)

MySQL은 ORDER BY또는 GROUP BY가 인덱스 처리가 가능한 경우 해당 인덱스 사용에 좀더 가중치를 둠

→ 하지만 상황에 따라 ORDER BY 또는 GROUP BY의 인덱스를 사용한 경우가 비효율적일 수 있음

prefer_ordering_index를 통해 이를 통제할 수 있음

9.3.2 조인 최적화 알고리즘

9.3.2.1 Exhaustive 검색 알고리즘

• MySQL 5.0과 그 이전에 사용되던 조인 최적화 알고리즘
• 전체 탐색, 모든 테이블 조합에 대해 실행 계획의 비용을 계산해서 최적의 조합을 찾는 방법
• 테이블 개수가 n 이라면 n!의 경우의 수 (O(n!))

9.3.2.2 Greedy 검색 알고리즘

• MySQL 5.0 부터 도입
• 과정
  1. 전체 N개의 테이블 중 설정 변수(optimizer_search_depth)에 정의된 개수로 가능한 조인 조합을 생성
  2. 1번에서 생성된 조합중 최소 비용 선정
  3. 2번에서 채택된 조합의 첫번째 테이블을 1번 테이블로 설정
  4. 3.에서 설정한 테이블을 제외한 N-1을 통해 다음 조인 조합을 생성
  5. 2~4를 반복하며 최소 조인 순서 탐색
• 최적화를 위한 시스템 변수
  • optimizer_search_depth
    • 조인 검색 테이블 개수
    • 조인에 사용된 테이블의 개수가 설정값보다 클경우 설정값만큼은 Exhaustive 검색이 되고 나머지는 Greedy
    • 0~62의 정수값 설정 가능 (기본값 62)
    • 0일 경우 옵티마이저가 자동으로 설정
  • optimizer_prune_level
    • Heuristic 검색이 작동하는 방식을 제어
      • Exhustive든 그리디든 상당히 많은 조인 경로를 비교
      • 이미 계산했던 조인 순서의 비용보다 큰 경우 중지하는 방법
      • 1 일 경우 알고리즘 사용 0일 경우 사용하지 않음 (기본값 1)
  • MySQL 8.0 에서는
    • optimizer_search_depth 변수에는 크게 영향 받지 않음
    • optimizer_prune_level의 경우 0으로 설정시 optimizer_search_depth의 변화에 따른 소요시간이 급증 → 휴리스틱을 비활성화할 필요가 거의 없어짐

9.4 쿼리 힌트

옵티마이저에게 어떻게 쿼리의 실행 계획을 수립할지 정해줌

2가지 쿼리 힌트가 존재

• 인덱스 힌트
• 옵티마이저 힌트

9.4.1 인덱스 힌트

“STRAIGHT_JOIN”과 “USE_INDEX”는 옵티마이저 힌트가 도입되기 전 사용되던 기능

→ ANSI-SQL 표준 문법을 준수하지 못하게 되는 단점

• SELECT와 UPDATE 명령에서만 사용가능

9.4.1.1 STRAIGHT_JOIN

여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할

// 1.
SELECT a.a_col, b.b_col, c.c_col FROM table_a a, table_b b, table_c WHERE a.id = b.id AND b.id = c.id;
// 2. 두 쿼리는 같은 쿼리
SELECT STRAIGHT_JOIN a.a_col, b.b_col, c.c_col FROM table_a a, table_b b, table_c WHERE a.id = b.id AND b.id = c.id;
SELECT /*! STRAIGHT_JOIN */ a.a_col, b.b_col, c.c_col FROM table_a a, table_b b, table_c WHERE a.id = b.id AND b.id = c.id;

1. 일반적으로 인덱스의 여부로 조인의 순서가 결정되며, 조인 칼럼의 인덱스에 문제가 없는 경우 레코드가 적은 테이블을 드라이빙으로 선택
2. FROM 절에 며시된 테이블의 순서대로 조인을 수행

• STRAIGTHT_JOIN 키워드는 SELECT 바로 뒤에 사용되어야 한다
• 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에 STRAIGHT_JOIN 힌트로 조인순서를 변경하는 것이 좋음
  • 임시 테이블과 일반 테이블의 조인 :
    • 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋음
    • 대부분 옵티마이저가 알아서 해줌
    • 심각한 성능저하가 예상될 경우에만 사용
  • 임시 테이블끼리 조인 :
    • 서브 쿼리로 파생된 임시 테이블은 인덱스가 없기 때문에 크기가 작은 테이블을 드라이빙으로 선택
  • 일반 테이블 끼리의 조인 :
    • 양쪽 테이블 모두 조인 칼럼에 인덱스가 있거나 양쪽 모두 없는 경우
      → 레코드 건수가 적은 테이블을 드라이빙으로
    • 그 외의 경우 인덱스가 없는 테이블을 드라이빙으로 설정
• STRAIGHT_JOIN과 비슷한 역할을 하는 옵티마이저 힌트
  • JOIN_FIXED_ORDER : STRAIGHT_JOIN과 동일한 효과 → FROM절의 모든 테이블에 대한 조인 순서 결정
  • JOIN_ORDER : 일부 테이블 조인 순서만 제안
  • JOIN_PREFIX : 일부 테이블 조인 순서만 제안
  • JOIN_SUFFIX : 일부 테이블 조인 순서만 제안

9.4.1.2 USE INDEX / FORCE INDEX / IGNORE INDEX

• 강제로 특정 인덱스를 사용하도록 하는 힌트
• 3~4개 이상의 칼럼을 포함하는 비슷한 인덱스가 존재하는 경우 옵티마이저가 제대로 선택하기 힘듬
• 사용하려는 인덱스를 가지는 테이블 뒤에 명시
• 인덱스 힌트 키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용 별도로 사용자가 부여한 이름이 없는 프라이머리 키는 “PRIMARY”라고 명시

  SELECT * FROM ... USE INDEX(primary) WHERE ...;
  SELECT * FROM ... USE INDEX(idx_name) WHERE ...;

  • 종류
    • USE INDEX
      • 특정 테이블의 인덱스를 사용하도록 권장
      • 옵티마이저는 대부분 사용자의 힌트를 채택하지만 항상 그 인덱스를 사용하는 것은 아님
      • 가장 자주 사용되는 인덱스
    • FORCE INDEX
      • 특정 테이블의 인덱스를 사용하도록 권장
      • USE INDEX보다 옵티마이저에게 미치는 영향이 더 큼
      • USE INDEX만으로도 충분하기 때문에 사용할 필요는 없음
    • IGNORE INDEX
      • 특정 인덱스를 사용하지 못하게 함
      • 풀 테이블 스캔을 유도하도록 사용할 수 있음
  • 용도 명시 용도는 선택 사항이며, 명시되어 있지 않을 경우 사용 가능한 것의 용도로 사용
    • USE INDEX FOR JOIN
      • 테이블 간의 조인뿐만이 아닌 레코드를 검색하기 위한 용도까지 포함
    • USE INDEX FOR ORDER BY
      • 명시된 인덱스를 ORDER BY 용도로만 쓰이게 제한
    • USE INDEX FOR GROUP BY
      • 명시된 인덱스를 GROUP BY 용도로만 쓰이게 제한
• 전문 검색 인덱스가 있는 경우 다른 보조 인덱스를 사용할 수 있는 상황이라도 전문 검색 인덱스를 선택하는 경우가 많음 → 옵티마이저가 전문 검색 인덱스나 프라이머리키에 좀더 많은 가중치를 두기 때문
  
• 최적의 실행 계획은 데이터의 성격에 따라 시시각각 변화
  • 옵티마이저가 당시 통계 정보를 가지고 선택하는 것이 가장 좋음 → 가장 좋은 최적화는 그 쿼리를 서비스에서 없애 버리거나 튜닝할 필요가 없게 데이터르 최소화 하는 것
    
  • 모델의 단순화를 통해 쿼리를 간결하게 만들어 힌트를 필요치 않게 하는 것
  • 최후순위로 힌트를 선택 → 앞쪽의 작업들은 시간이 많이 들기 때문에 힌트에 의존하는 경우가 많음

9.4.1.3 SQL_CALC_FOUND_ROWS

• LIMIT 사용된 쿼리에서 LIMIT 수만큼 찾은 후에도 끝까지 검색을 수행하게 함
  • FOUND_ROWS()를 통해 조건에 만족하는 레코드가 전체 몇건이었는지 알아 낼 수 있음
• 웹의 페이징 기능을 위해 사용
• COUNT와 비교

  // SQL_CALC_FOUND_ROWS
  SELECT SQL_CALC_FOUND_ROWS * FROM employees WHERE first_name='Georgi' LIMIT 0,20;
  SELECT FOUND_ROWS() AS total_record_count;
  
  // COUNT()
  SELECT COUNT(*) FROM employees WHERE first_name='Georgi';
  SELECT * FROM employees WHERE first_name='Georgi' LIMIT 0,20;
  

  • SQL_CALC_FOUND_ROWS의 경우
    • 쿼리가 2번 실행됨 (LIMIT 절을 위해 1번, FOUND_ROWS() 함수를 실행하기위해 1번)
    • 이때 두 쿼리 다 만족하는 모든 레코드를 읽어야함 → 랜덤 I/O가 일어남
  • COUNT의 경우
    • 처음 쿼리에서만 만족하는 모든 레코드를 읽고
    • 두번째 쿼리에서는 20개만 읽게 됨
  • SQL_CALC_FOUND_ROWS가 더 많은 작업을 하게되므로 비효율적
    • 성능면에서 나온 기능이 아닌 개발자의 편의를 위해 나온 기능
    • 사용하지 않을 것을 추천

9.4.2 옵티마이저 힌트

9.4.2.1 옵티마이저 힌트 종류

크게 4개의 그룹으로 나뉨

• 인덱스 : 특정 인덱스의 이름을 사용할 수 있는 힌트
  • 모든 인덱스 수준의 힌트는 반드시 테이블명이 선행되어야함 (그 인덱스를 가진 테이블을 먼저 명시)
• 테이블 : 특정 테이블의 이름을 사용할 수 있는 힌트
• 쿼리 블록 : 특정 쿼리 블록에 사용할수 있는 옵티마이저 힌트, 명시된 쿼리블록에만 영향을 미침
  • SELECT키워드를 여러번 쓸때 각 SELECT 키워드로 시작하는 서브쿼리 영역을 쿼리블록이라 함
  • 외부 쿼리 블록에서 특정 서브 쿼리 블록을 사용하려면 “QB_NAME()” 힌트를 통해 해당 쿼리 블록에 이름을 지정해 주어야함
• 글로벌(쿼리 전체) : 전체 쿼리에 대해 영향을 미치는 힌트

9.4.2.2 MAX_EXECUTION_TIME

• 쿼리의 실행 시간 제한, 글로벌
• 옵티마이저 힌트 중 유일하게 쿼리의 실행 계획에 영향을 미치치 않음

9.4.2.3 SET_VAR

• 쿼리의 실행을 위한 시스템 변수 제어, 글로벌

SELECT /*+ SET_VAR(optimizer_switch='...') */ ...;

• 다양한 형태의 시스템 변수를 각 쿼리에서 조정 가능 → 모든 시스템 변수를 조정할 수 있는 것은 아님

9.4.2.4 SEMIJOIN & NO_SEMIJOIN

• 서브쿼리의 세미 조인 최적화 전략 제어, 쿼리 블록
• Table Pull-out 최적화 전략은 힌트로 사용할 수 없음 → 항상 더 나은 성능을 보장하기 때문

• NO_SEMIJOIN을 통해 특정 전략을 우회하도록 유도 할 수 있음

9.4.2.5 SUBQUERY

• 서브쿼리의 세미 조인 최적화 전략 제어, 쿼리 블록
  

9.4.2.6 BNL & NO_BNL & HASHJOIN & NO_HASHJOIN

• 블록 네스티드 루프 조인 사용여부(MySQL 8.0.20 부터는 해시 조인 사용 여부 제어), 쿼리 블록 테이블

9.4.2.7 JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX

• 쿼리 블록
• JOIN_FIXED_ORDER : FROM절의 모든 테이블에 대한 조인 순서 결정
• JOIN_ORDER : 힌트에 명시된 테이블의 순서대로 조인을 실행하는 힌트
• JOIN_PREFIX : 조인에서 드라이빙 테이블만 강제하는 힌트
• JOIN_SUFFIX : 조인에서 드리븐 (가장 마지막에 조인돼야 할 테이블들)만 강제하는 힌트

9.4.2.8 MERGE & NO_MERGE

• FROM 절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합하는 최적화를 수행할지 여부 제어, 테이블
• 파생 테이블이 불필요한 자원 소모를 유발하므로 이를 제어하기 위한 힌트

9.4.2.9 INDEX_MERGE & NO_INDEX_MERGE

• 인덱스 병합 실행 계획 사용 여부, 테이블 인덱스
• 9.3.1.5 참고

9.4.2.10 NO_ICP

• 인덱스 컨디션 푸시 다운 최적화 전략 사용 여부 제어, 테이블 인덱스
• 9.3.1.3 참고

9.4.2.11 SKIP_SCAN & NO_SKIP_SCAN

• 인덱스 스킵 스캔 사용 여부 제어, 테이블 인덱스
• 조건이 누락된 선행 칼럼이 가지는 유니크한 값이 많을 경우 인덱스 스킵 스캔의 성능은 떨어짐 → 이를 방지하기 위한 키워드
  

9.4.2.12 INDEX & NO_INDEX

• GROUP BY, ORDER BY, WHERE 절의 처리를 위한 인덱스 사용 여부 제어, 인덱스