9.3 고급 최적화

고급 최적화

MYSQL 옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행 계획을 수립하게 된다.
옵티마이저 옵션은 크게 조인 관련된 옵티마이저 옵션과 옵티마이저 스위치로 구분되며 고급 최적화 기능들을 활성화할지를 제어하는 용도로 사용된다.

옵티마이저 스위치 옵션

옵티마이저 스위치 옵션은 optimizer_switch 시스템 변수를 이용해서 제어하는데, 여러 개의 옵션을 세트로 묶어서 설정하는 방식으로 사용한다.

각각의 옵션은 default와 on, off중에서 하나를 설정할 수 있다.
옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이므로 다음과 같이 설정 가능하다.

// mysql 서버 전체적으로 설정
mysql> SET GLOBAL optimizer_switch='index_merge=on, index_merge_union=on  ...';  

// 현재 커넥션의 옵티마이저 스위치만 설정
mysql> SET SESSION optimizer_switch='index_merge=on, index_merge_union=on  ...';  

// 힌트를 사용하여 현재 쿼리에만 설정 
mysql> SELECT /*+ SET_VAR(optimizer_switch='condition_fanout_filter=off') ... FROM ...;

Nested Loop Join

MYSQL 서버에서 사용되는 대부분의 조인 방식.

for(row1 in employees){   
    for(row2 in salaries){      
        if(condition_matched) 
            return (row1, row2);   
    } 
}

이러한 형태로 중첩된 반복문을 사용한거처럼 작동한다.
레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아서 반환한다.

MRR과 배치 키 액세스

MYSQL 서버의 내부 구조상 조인 처리는 MYSQL 엔진이 처리하지만, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당한다.
이때 드라입이 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화를 수행할 수 없다.

이 단점을 보완하기 위해, 드라이빙 테이블의 레코드를 읽어서 드리븐 테이블과의 조인을 즉시 실행하지 않고 조인 대상을 버퍼링한다.
조인 버퍼에 레코드가 가득 차면 비로소 MYSQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한 번에 요청한다.
이렇게 함으로서 스토리지 엔진은 읽어야 될 레코드들을 데이터 페이지에 정렬된 순서로 접근해서 디스크 읽기를 최소화 할 수 있다.
이 방식을 MRR(Multi-Range Reade)라고 하고 MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access)조인이라고 한다.
BKA를 사용하게 되면 부가적인 정렬작업이 필요해지면서 오히려 성능에 안좋은 영향을 미치는 단점이 있어 기본적으로 비활성화 되어있다고 한다.

Block Nest Loop

nested loop join과 가장 큰 차이점은 조인 버퍼가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐이다.
조인 쿼리의 실행 계획에서 Extra 칼럼에 Using Join buffer라는 문구가 표시되면 그 실행 계획은 조인 버퍼를 사용한다는 것을 의미한다.
==> MYSQL 8.018 버전부터 해시 조인 알고리즘이 도입되며 블록 네스티드 루프 조인은 더 이상 사용되지 않고 해시 조인 알고리즘이 사용된다.

Hash Join

• 해시 조인은 해시 테이블을 사용하여 두 입력간에 일치하는 행을 찾는 조인을 실행하는 방법
• 특히 입력 중 하나가 메모리에 들어갈 수있는 경우 대용량의 처리에서는 일반적으로 중첩 루프 조인보다 효율적
• MySQL 서버는 주로 조인 조건의 컬럼이 인덱스가 없거나 조인 대상 테이블중 일부의 레코드 건수가 매우 적은 경우 등에 대해서만 Hash Join 알고리즘을 사용

-> Hash Join은 Nested Loop Join이 사용되기에 적합하지 않은 경우를 위한 차선책

• 8.0.20 부터는 INNER JOIN뿐 아니라 OUTER JOIN 에서도 Hash Join이 사용됨
• mysql doc
  ```
  // MySQL은 각 조인에 동등 조인 조건이 있고 다음과 같은 조인 조건에 적용할 수 있는 인덱스가 없는 모든 쿼리에 대해 해시 조인을 사용합니다.
  SELECT *
  FROM t1
  JOIN t2
  ON t1.c1=t2.c1;

// MySQL 8.0.20부터 블록 중첩 루프에 대한 지원이 제거되고 서버는 이전에 블록 중첩 루프가 사용되었던 모든 곳에서 해시 조인을 사용

 

 SELECT given\_name, country\_name   
 FROM persons JOIN countries   
 ON persons.country\_id = countries.country\_id;

in memory hash

• 빌드 단계와 프로브 단계
• The build phase
  
  
  
  빌드 단계 에서는 서버는 조인 속성을 해시 테이블 키로 사용하여 입력 중 하나의 행이 저장되는 인 메모리 해시 테이블을 빌드한다.
  이 입력은 빌드 입력 이라고도 하며 이상적으로는 MySQL 서버는 두 입력 중(두 테이블 중) 더 작은 것을 빌드 입력으로 선택한다. (행 수가 아니라 바이트로 측정 됨)
  countries 테이블이 빌드 입력으로 지정 되었다고 가정, 'countries.country_id'이 빌드 Input에 속한 조인 조건이며 해시 테이블의 키로서 이용된다.
  모든 행이 해시 테이블에 저장되면 빌드 단계가 완료됨.
• The probe phase
  
  
  
  프로브 단계 동안 서버는 프로브 입력 (이 예제에서는 persons 테이블) 에서 행 읽기를 시작.
  각 행에 대해 서버는 persons.country_id 의 값을 조회 키로 사용하여 행과 일치하는 해시 테이블을 조사.
  각 일치에 대해 결합 된 행이 클라이언트로 전송.

Spill to disk

위의 상황은 MySQL서버가 전체 빌드 입력을 메모리에 저장할 수 있다는 점을 감안할 때 매우 잘 작동한다.
사용 가능한 메모리 양은 시스템 변수 join_buffer_size 에 의해 제어되며 런타임에 조정할 수 있다.
그러나 빌드 입력이 사용 가능한 메모리보다 크면 디스크로 기록되게 된다.

• The build phase
  
  
  
  빌드 단계에서 설정된 버퍼 메모리가 가득 차면 서버는 나머지 빌드 입력을 디스크의 여러 청크 파일에 기록한다.
• The probe phase
  
  
  
  probe 단계에서도 해시 테이블에 일치하는 행을 조사한다.

Block Nested Join과의 성능 차이

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

쿼리 처리에 비효율이 발생하는 부분이 있는데, 대표적인 예가 Index에서 필터링을 수행하지 못하는 것이다.
예를 들어 아래와 같은 조건의 인덱스와 쿼리가 있다고 가정했을 때,
상식적으로는 인덱스에서 2개의 조건을 만족하는 데이터만 조회해서, 클라이언트에 결과를 돌려주면 된다.

 # 인덱스 : zipcode + lastname  SELECT \* FROM people WHERE zipcode='95054' AND lastname LIKE '%ho%' 

하지만 MySQL에서는 전혀 다르게 동작을 한다.
위 쿼리는 ref 타입의 실행 계획으로 처리되며, MySQL 엔진(옵티마이저)은 스토리지 엔진에게 zipcode=95043이라는 조건밖에 전달하지 못한다.
스토리지 엔진은 lastname LIKE ‘%ho%’ 라는 조건을 알 수 없기에, zipcode가 95054인 데이터 모두를 MySQL 엔진으로 전달할 수 밖에 없다.
그리고 쿼리의 조건에 맞지 않는 데이터는 MySQL 엔진에서 필터링 처리되어 버려지게 된다.
즉, 읽지 않아도 될 데이터를 읽어서 전달한 셈이다.

MySQL 5.6 버전에서는 Index Condition Pushdown(ICP)라는 기능이 추가된다.
이 기능은 WHERE 필터 조건(Condition)을 스토리지 엔진으로 밀어넣을(Pushdown) 수 있도록 해주는 기능이다.

 SET optimizer\_switch = 
 'index\_condition\_pushdown=off';   
 
 SET optimizer\_switch = 'index\_condition\_pushdown=on';

기본적으로 활성화 되어 있고, 쿼리의 성능이 몇 배에서 몇십 배로 향상될 수도 있는 중요한 기능이다.

인덱스 확장(use_index_extensions)

use_index_extensions 옵티마이저 옵션은 INNODB스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지를 결정하는 옵션이다.

mysql> CREATE TABLE dept\_emp (  
  emp\_no INT NOT NULL,   
  dept\_no CHAR(4) NOT NULL,  
  from\_date DATE NOT NULL,  
  to\_date DATE NOT NULL,   
  PRIMARY KEY (dept\_no, emp\_no),   
  KEY ix\_fromdate (from\_Date)   
) ENGINE=InnoDB;

위의 테이블에서 PK는 (dept_no, emp_no)이고, 인덱스는 (from_date)칼럼만 포함된다.
그러나 세컨더리 인덱스는 데이터 레코드를 찾아가기 위해서 프라이머리 키인 dept_no, emp_no칼럼을 순서대로 포함한다.
그래서 최종적으로 ix_fromdate 인덱스는 (from_Date, dept_no, emp_no)조합으로 인덱스를 생성한 것과 흡사하게 작동할 수 있게 된다.

 

정렬 작업도 Extra 칼럼에 'Using Filesort'가 표시되지 않았다는 것은 별도의 정렬없이 인덱스 순서대로 레코드를 읽기만 했다는 것이다.

인덱스 머지(index_merge)

쿼리에서 한 테이블에 대한 WHERE 조건이 여러 개 있더라도 하나의 인덱스에 포함된 칼럼에 대한 조건만으로 인덱스를 검색하고
나머지 조건은 읽어온 레코드에 대해서 체크하는 형태로만 사용되는 것이 일반적이다.
이처럼 하나의 인덱스만 사용해서 작업 범위를 충분히 줄일 수 있는 경우라면 테이블 별로 하나이 인덱스만 활용하는 것이 효율적이다.
그러나 쿼리에 사용된 각 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 것으로 예상될 때
MYSQL 서버는 인덱스 머지 실행 계획을 선택한다.
인덱스 머지 실행 계획은 3개의 세부 실행 계획으로 나누어 볼 수 있다.

• index_merge_intersection (교집합)
• index_merge_sort_union (정렬 후 합집합)
• index_merge_union (합집합)
  index_merge 옵티마이저 옵션은 3개의 최적화 옵션을 한 번에 모두 제어할 수 있는 옵션이다.

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

위 쿼리는 first_name 칼럼과 emp_no 칼럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있다.
2개의 인덱스 중에서 어떤 조건을 사용하더라도 인덱스를 사용할 수 있다.
따라서 옵티마이저는 두 인덱스를 모두 사용해서 쿼리를 사용하기로 결정한다.
Extra 칼럼에 'Using intersect'라고 표시된 것은 이 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 것을 의미한다.
first_name과 emp_no 칼럼의 조건 중 하나라도 충분히 효율적으로 쿼리를 처리할 수 있었다면 옵티마이저는 2개의 인덱스를 모두 사용하는 실행 계획을 사용하지 않았을 것이다.

 SELECT count(\*) 
 FROM employees  
 WHERE first\_name='Georgi';   \= 253  
 
 SELECT count(\*) FROM employees  
 WHERE emp\_no BETWEEN 10000 AND 20000;   \= 10000

인덱스 머지 실행계획이 아니었다면 다음 두 가지 방식으로 처리를 해야될 것.

• first_name = 'Georgi' 인덱스를 사용해서 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드를 반환한다.
• emp_no 인덱스를 사용해서 10000건을 읽어온뒤 first_name조건에 일치하는 레코드를 반환한다.

SELECT count(\*) 
FROM employees   
WHERE first\_name='Georgi' A
ND emp\_no BETWEEN 10000 AND 20000;   \= 14

옵티마이저는 인덱스를 하나만 사용하는 것이 아니라 두조건을 만족하는 레코드가 더 효율적이라는 것을 알아서 인덱스 머지 실행계획을 세운것이다.

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

WHERE절에 사용된 2개 이상의 조건이 각각의 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용된다.

• first_name='Matt' and hire_date='1987-03-31'와 같이 두 조건을 만족하는 레코드가 1건이 되는데 이 쿼리의 결과에서는 그 사원의 정보가 중복으로 출력되지 않는다. 그럼 두 결과 집합을 정렬해서 중복 레코드를 제거한 것인데, 실행계획에서는 정렬했다는 표시가 없다. 어떤 일이 일어난 것인가??

• 인덱스 검색 결과는 이미 키로 정렬되어 있다.
• 정렬된 결과를 한건씩 비교하면서 중복 제거하는 깔데기 우선순위 큐(priority Queue)

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

인덱스 머지 작업을 하는 도중에 결과의 정렬이 필요한 경우에 Sort Union 알고리즘을 사용한다.

위 워리를 두개의 쿼리로 분리해보자.

 

위 쿼리는 emp_no 로 정렬되어 있지만 밑에 쿼리는 emp_no로 정렬되어 있지 않다.
그래서 MYSQL 서버는 두 집합의 결과에서 중복을 제거하기 위해 각 집합을 emp_no 칼럼으로 정렬한 다음 중복 제거를 수행한다.

merge union 에서 봤던 쿼리는 정렬이 잘 되어있음을 확인.(왠지 아시는분..)

세미 조인(semijoin)

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

과거의 MYSQL은 employees 테이블을 풀 스캔하며 한건 한건 서브 쿼리의 조건에 일치하는지 비교 했었다고 한다.
따라서 57건만 읽으면 되는 쿼리를 30만 건 넘게 읽어서 처리가 되었었다.
현재는 서브 쿼리부분이 먼저 실행되고 그 다음 employees테이블에서 일치하는 레코드만 검색한다.