9.3 고급 최적화
- 옵티마이저는 통계 정보와 옵티마이저 옵션을 결합해서 최적의 계획을 수립한다.
- 옵티마이저 옵션
- 조인 관련 옵티마이저 옵션
- 옵티마이저 스위치
- 옵티마이저 스위치: MySQL 서버의 고급 최적화 기능들을 활성화할지를 제어하는 용도이다.
9.3.1 옵티마이저 스위치 옵션
- 변수 optimizer_switch를 이용한다.
- 옵티마이저 스위치 이름
- batched_key_access (기본값 off)
- block_nested_loop
- engine_condition_pushdown
- index_condition_pushdown
- use_index_extensions
- index_merge
- index_merge_intersection
- index_merge_sort_union
- index_merge_union
- mrr
- mrr_cost_based
- semijoin
- firstmatch
- loosescan
- materialization
- subqueryt_materialization_cost_based
- batched_key_access 를 뻬고 기본값은 모두 on이다.
- 각각의 옵티마이저 스위치 옵션은 "default", "on", "off" 중 하나를 설정 가능하다.
- 옵티마이저 스위치 옵션은 글로벌과 세션별 모두 설정할 수 있는 시스템 변수이다.
- 따라서 MySQL 서버 전체적으로 또는 현재 커넥션에 대해서만 다음과 같이 옵티마이저 스위치 설정 가능하다.
-- MySQL 서버 전체적으로 옵티마이저 스위치 지정
SET GLOBAL optimizer_switch='index_merge=on, index_merge_union=on,...';
-- 현재 커넥션의 옵티마이저 스위치만 지정
SET SESSION optimizer_switch='index_merge=on, index_merge_union=on,...';
9.3.1.1 MRR과 배치 키 엑세스(mrr & batched_key_access)
- Def) MRR(Multi-Range Read)
- 메뉴얼 상 DS-MRR(Disk Sweep Multi-Range Read)라고도 한다.
- Def) 네스티드 루프 조인(Nested Loop Join): 기존에는 MySQL서버에서 조인할 때, 드라이빙 테이블의 레코드를 한 건 읽어서 드리븐 테이블의 일치하는 레코드를 찾아서 수행했는데 이러한 방식을 말한다.
- MySQL 서버의 내부 구조상 JOIN은 MySQL 엔진이 처리하지만 실제 레코드를 읽고 검색하는 부분은 스토리지 엔진이 담당한다.
- 이 때, 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 레코드를 찾고 읽는 스토리지 엔진은 아무러 최적화를 수행할 수 없다.
- 이런 단점 보완을 위해 MySQL 서버는 서버 조인 대상 테이블 중 하나로부터 레코드를 읽어서 버퍼에 버퍼링한다.
- 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 한번에 요청한다.
- 따라서, 스토리지 엔진은 데이터 페이지에 정렬된 순서로 접근해서 디스크의 데이터 페이지 읽기를 최소화할 수 있는 것이다.
- 이런식으로 MRR을 응용해서 실행되는 조인 방식을 BKA(Batched Key Access)조인이라고 한다.
- BKA 조인의 최적화는 기본적으로 비활성화되어 있다.
- 부가적인 정렬이 발생하므로 상황에 따라 적용하는 것이 좋기 때문이다.
9.3.1.2 블록 네스티드 루프 조인(block_nested_loop)
- Def) 블록 네스티드 루프 조인(block_nested_loop): (MySQL 서버에서는 대부분 네스 티드 루프 조인을 사용하지만) 조인의 연결 조건이 되는 칼럼에 모두 인덱스가 있는 경우 사용된다.
- 네스티드 루프 조인과 블록 네스티드 루프 조인의 다른 점은?
- 1) 조인 버퍼가 사용되는지 여부와 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐이다.
- 2) 조인에서 드라이빙 테이블과 드리븐 테이블이 어떤 순서로 조인되느냐?
- 조인 알고리즘에서 "Block"이라는 단어가 사용되면 조인용으로 별도의 버퍼가 사용됐다는 것을 의미하는데 조인 쿼리의 실행 계획에서 Extra 칼럼에 "Using Join buffer"라는 문구가 표시되면 그 쿼리는 조인 버퍼를 사용한다는 뜻이다.
Ex) 네스티드 루프 조인(nested loop join)
SELECT *
FROM employees e INNER JOIN salaries s ON s.emp_no=e.emp_no
AND s.from_date <= NOW()
AND s.to_date >= NOW()
WHERE e.first_name = 'Amor';- 중첩된 반복 명령을 사용하는 것처럼 작동해서 "네스티드 루프 조인"이라고 한다.
- 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아서 반환한다.
- 조인은 드라이빙 테이블에서 일치하는 레코드의 건수만큼 드리븐 테이블을 검색하면서 처리된다.
- 드라이빙 테이블은 한 번에 쭉 읽지만 드리븐 테이블은 여러 번 읽는다.
- ex) 드라이빙 테이블에서 일치하는 레코드가 1000건인 경우, 드리븐 테이블의 조인 조건이 인덱스를 이용할 수 없었다면 드리븐 테이블에서 연결되는 레코드를 찾기위해 1000번의 풀 테이블 스캔을 해야한다.
- 따라서 드리븐 테이블을 검색할 때 인덱스를 사용할 수 없는 쿼리는 상당히 느려지고 옵티마이저는 최대한 드리븐 테이블의 검색이 인덱스를 사용할 수 있게 실행 계획을 수립한다.
- ex) 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없는 경우는?
- 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시하고나서 드리븐 테이블과 메모리의 캐시를 조인하는 형태로 처리한다.
- 이 때의 메모리 캐시를 "조인 버퍼" 라고 한다.
Ex) JOIN 조건이 없는 경우 - 카테시안 조인
SELECT *
FROM dept_emp de, employees e
WHERE de.from_date > '1995-01-01' AND e.emp_no < 109004;- 테이블에 대한 조건은 WHERE 절에 있다.
- 그런데 조인 조건이 없다.
- 따라서, from_date > '2000-01-01' 인 레코드와 employees 에서 emp_no < 109004는 카르테시안 조인을 수행한다.
- 쿼리의 실행 계획과 조인 버퍼가 사용되는 방법
- dept_emp 테이블의 ix_fromdate 인덱스를 이용해서 (from_date > '1995-01-01') 조건을 만족하는 레코드를 검색한다.
- 조인에 필요한 나머지 칼럼을 모두 dept_emp 테이블로부터 읽어서 조인 버퍼에 저장한다.
- employees 테이블의 PK를 이용해서 emp_no < 109004 조건을 만족하는 레코드를 검색한다.
- 3번에서 검색된 결과에 2번의 캐시된 조인 버퍼의 레코드(dept_emp)를 결합해서 반환한다.
- 중요 - 조인 버퍼가 사용되는 쿼리에서는 조인 순서가 거꾸로인 것처럼 실행된다.
9.3.1.3 인덱스 컨디션 푸시다운(index_condition_pushdown)
- MySQL 5.6 부터는 인덱스 컨디션 푸시 다운 도입됐다.
- 쿼리 성능을 몇 배에서 몇 십 배로 향상될 수 있는 중요한 기능이다.
Ex)
SELECT *
FROM employees
WHERE last_name = 'Acton' AND first_name LIKE '%sal';- last_name = 'Acton'조건은 ix_lastname_firstname 인덱스를 레인지 스캔으로 사용 가능하다.
- LIKE '%sal' 조건은 인덱스 레인지 스캔으로 검색할 인덱스 범위를 좁힐 수 없다.
- 따라서 다음 쿼리에서는 last_name 조건은 ix_lastname_firstname 인덱스의 특점 범위만 조회 가능한 조건이며 LIKE '%sal' 조건은 데이터를 모두 읽은 후 사용자가 원하는 결과인지 하나씩 비교해보는 조건(체크 조건 또는 필터링 조건이라고 한다.)으로만 사용된다.
- Using where: InnoDB 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 WHERE 조건에 일치하는지 검사하는 과정을 의미한다.
- 위 쿼리에서는 firstname LIKE '%sal' 검사 과정에 사용된 조건이다.
Ex) 인덱스 컨디션 푸시다운이 작동하지 않을 경우와 적용한 다음
== 사진 ==
- ix_lastname_firstname 인덱스의 first_name 칼럼을 이용하지 않고 다시 테이블의 레코드를 읽어서 처리했을까?
- 인덱스의 fisrt_name 칼럼을 이용해서 비교했다면 불필요한 2건의 레코드는 employees 테이블에서 읽지 않아도 됐을 것이다.
- 이 부분은 firstname LIKE '%sal' 조건을 누가 처리하느냐에 따라 인덱스에 포함된 first_name 칼럼을 이용할지 또는 테이블의 first_name 칼럼을 이용할지 결정된다.
- 그림의 인덱스 비교 작업은 InnoDB 엔진이 수행하지만 테이블의 레코드에서 first_name 조건을 비교하는 작업은 MySQL 엔진이 수행한다.
- MySQL 5.5 까지는 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name 조건은 MySQL 엔진이 스토리지 엔진으로 아예 전달해주지 않았다.
- 그래서 스토리지 엔진에서는 불필요한 2건의 테이블 읽기를 수행하는 것이다.
- MySQL 5.6 Q부터는 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달하도록 핸들러 API가 개선됐다.
- 다음 그림과 같이 인덱스를 이용해서 최대한 필터링까지 완료해서 꼭 필요한 레코드 하나에 대해서만 테이블 읽기를 수행한다.
== 사진 ==
- 옵티마이저 스위치에서 index_condition_pushdown 을 on으로 활성화하고 쿼리 실행 계획을 확인하면 "Using where"가 없어지고 "Using index condition" 이 출력된다.
9.3.1.4 인덱스 확장 (use_index_extensions)
- Def) use_index_extensions 옵티마이저 옵션은 InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 PK를 활용할 수 있게 할지를 결정하는 옵션이다.
- InnoDB 엔진은 PK를 클러스터링 키로 생성한다.
- 따라서, 모든 세컨데리 인덱스는 리프노드에 프라이머리 키 값을 가진다.
Ex)
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)
- 세컨더리 인덱스인 ix_fromdate는 from_date 칼럼만 포함한다.
- 세컨더리 인덱스는 데이터 레코드를 찾기 위해 PK인 dept_no, emp_no 칼럼을 순서대로 포함한다.
- 최종적으로 ix_fromdate 인덱스는(from_date, dept_no, emp_no) 조합으로 인덱스를 만든 것과 유사하게 작동할 수 있다.
Ex)
- key_len: 쿼리 실행 계획을 EXPLAIN으로 조회하면 나오는 칼럼으로 이 쿼리가 인덱스를 구성하는 칼럼 중에서 어느 부분(어느 칼럼)까지 사용했는지를 바이트 수로 보여준다.
- 19 바이트 = from_Date (3 바이트) + dept_no(16 바이트)
- DATE : 3바이트
- CHAR(4) => 4 * 4 = 16바이트
- depe_no 조건을 제거한 쿼리를 실행하면 위와 같이 key_len = 3 바이트가 출력된다.
- from_Date 만 이용했기 때문에 3 바이트가 출력된다.
참고) http://www.incodom.kr/DB_-_%EB%8D%B0%EC%9D%B4%ED%84%B0_%ED%83%80%EC%9E%85/MYSQL
9.3.1.5 인덱스 머지(index_merge)
- 인덱스를 이용해서 쿼리를 실행하는 경우, 대부분의 옵티마이저는 테이블별로 하나의 인덱스만 사용하도록 계획을 수립한다.
- 그런데 인덱스 머지 실행 계획을 사용하면 하나의 테이블에 대해 2개 이상의 인덱스를 이용해서 쿼리를 처리
- WHERE 절에 조건이 여러 개 있어도 하나의 인덱스에 포함된 칼럼에 대한 조건만으로 인덱스를 검색하고 나머지 조건을 읽어온 레코드에 대해 체크하는 형태로만 사용되는 게 일반적이다.
- 하나의 인덱스만 이용해서 작업 범위를 줄일 수 있다면 테이블별로 하나의 인덱스만 이용하는 게 효율적이다.
- 그러나, 쿼리에 사용된 각각의 조건이 서로 다른 인덱스를 사용할 수 있고 그 조건을 만족하는 레코드 건수가 많을 거라 예상되는 경우에는 MySQL 서버는 인덱스 머지 실행 계획을 선택한다.
- 인덱스 머지 실행 계획은 다음과 같이 3개의 세부 실행 계획으로 나눈다.
- 3개 모두 여러 개의 인덱스를 통해 결과를 가져온다는 점은 동일하나 각각 결과를 어떤 방식으로 병합할지에 따라 구분된다.
- index_merge 옵션은 3개의 최적화 옵션을 한 번에 모두 제어할 수 있는 옵션이다.
- index_merge_intersection
- index_merge_sort_union
- index_merge_union
9.3.1.6 인덱스 머지 - 교집합(index_merge_intersection)
Ex)
SELECT *
FROM employees
WHERE first_name = 'Georgi' AND emp_no BETWEEN 10000- AND 20000;- WHERE 절에는 2개의 조건이 있다.
- employees 테이블의 first_name 칼럼과 emp_no 칼럼 모두 각각의 인덱스(ix_firstname, PRIMARY)를 가지고 있다.
- 2 개 중에서 어떤 조건을 사용해서라도 인덱스를 사용할 수 있다.
- 옵티마이저는 ix_firstname, PRIMARY 키를 모두 사용해서 쿼리를 처리하기로 결정한다.
- "Using intersect": 쿼리가 여러 개의 인덱스를 각각 검색해서 그 결과의 교집합만 반환했다는 뜻이다. 실행 계획의 extra 컬럼에 나온다.
- 인덱스 머지 실행 계획이 아니라면 다음 방식으로 처리한다.
- 1) "first_name = 'Georgi'" 조건만 인덱스를 사용한다면 일치하는 레코드 253건을 검색한 다음 데이터 페이지에서 레코드를 찾고 emp_no 칼럼의 조건에 일치하는 레코드만 반환하는 형태로 처리돼야한다.
- 2) "emp_no BETWEEN 10000 AND 2000" 조건만 인덱스를 사용했다면 PK를 이용해서 10,000 건을 읽어와서 "first_name = 'Georgi'" 조건에 맞는 레코드만 반환하는 형태로 처리해야한다.
- 옵티마이저는 왜 두 인덱스의 교집합만 가져오는 방식으로 처리할까?
- 1), 2) 둘다 매우 비효율적인 작업이라서 옵티마이저는 각 인덱스를 검색해서 두 결과의 교집합만 찾아서 반환한다.
- 그런데 ix_firstname 인덱스는 PK인 emp_no 칼럼을 자동 포함하므로 그냥 ix_firstname인덱스만 사용하는 게 더 성능이 좋을 것으로 생각할 수도 있다.
- 그러면 index_merge_intersection 최적화를 비활성화하면 된다.
Ex) index_merge_intersection 최적화 비활성화
-- MySQL 서버 전체적으로 index_merge_intersextion 최적화 비활성화
SET GLOBAL optimizer_switch = 'index_merge_intersextion=off';
-- 현재 커넥션에 대해 index_merge_intersextion 최적화 비활성화
SET SESSION optimizer_switch = 'index_merge_intersextion=off';
-- 현재 쿼리에서만 index_merge_intersextion 최적화 비활성화
SELECT /*+ SET_VAR(optimizer_switch='index_merge_intersextion=off') */ *
FROM employees
WHERE firstn_name = 'Georgi' AND emp_no BETWEEN 10000 AND 20000;
9.3.1.7 인덱스 머지 - 합집합(index_merge_union)
- Def) Using union: WHERE 절에 사용된 2개 이상의 조건이 각각 인덱스를 사용하되 OR 연산자로 연결된 경우에 사용되는 최적화이다.
Ex)
SELECT *
FROM employees
WHERE first_name = 'Matt' OR hire_date= '1987-03-31';- 2개의 조건이 OR로 연결되어 있다.
- first_name 칼럼과 ix_firstname 인덱스 연결
- hire_date 칼럼과 ix_hiredate 인덱스 연결
- 쿼리의 실행 계획을 보면 "Using union(ix_firstname , ix_hiredate)" 최적화 사용한다.
- 인덱스 머지 최적화가 ix_firstname 인덱스의 검색 결과와 ix_hiredate 인덱스 검색 결과를 Union 알고리즘으로 병합됐다는 것을 의미한다. 즉, 합집합을 가져왔다는 뜻이다.
- MySQL 서버는 구 결과 집합을 정렬해서 중복 레코드를 제거했을 텐데 두 결과 집합에서 중복을 제거하기 위해 정렬이 필요했을 것이다.
- 그런데 실행 계획에는 정렬했다는 표시가 없다.
- 이런 중복 제거를 위해 내부적으로 어떤 작업을 수행했을까?
- 위 그림은 인덱스 머지 최적화의 'Union' 알고리즘의 작동 방식을 나타낸다.
- first_name 칼럼의 검색 결과와 hire_date 칼럼의 검색 결과에서 사원 번호가 '13163'인 사원은 양쪽에 모두 포함돼 있어서 반드시 제거해야한다.
- 그런데 MySQL 서버는 first_name 조건을 검색한 결과와 hire_date칼럼을 검색한 결과가 PK로 이미 각각 정렬돼있다는 것을 알고 있다.
- 위의 쿼리를 아래와 같이 두 개의 쿼리로 쪼갠다.
- 두 결과 집합에서 하나씩 가져와서 서로 비교하면서 PK인 emp_no 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러낼 수 있는 것이다.
- 이렇게 정렬된 두 집합의 결과를 하나씩 가져와서 중복 제거를 수행할 때, "우선순위 큐(Priority Queue)"라고 한다.
SELECT * FROM employees WHERE first_name = 'Matt';
SELECT * FROM employees WHERE hire_date = '1987-03-31';
참고) https://pridiot.tistory.com/267
cf) WHERE 절의 AND, OR 차이점
- AND: 두 조건 중 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 쿼리가 실행된다.
- OR: 둘 중 하나라도 제대로 인덱스를 쓰지 못하면 항상 풀 테이블 스캔으로 밖에 처리를 못한다.
9.3.1.8 인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)
Q 1)
- 325P
- 인덱스 목록
- 인덱스를 원래 칼럼 값과 주소를 KEY-VALUE 형태 저장하는 건데 여기 사진에서는 주소는 생략이 된건가?
출처 - 「Real MySQL 8.0 - 백은빈, 이성욱」
'컴퓨터 과학 > [Study] Real MySQL 8.0' 카테고리의 다른 글
| Chapter 10.01 실행 계획 (0) | 2023.01.26 |
|---|---|
| Chapter 09.04 쿼리 힌트 (0) | 2023.01.23 |
| Chapter 09.02 기본 데이터 처리 (0) | 2023.01.07 |
| Chapter 09.01 옵티마이저(Optimizer)와 힌트 (0) | 2023.01.07 |
| Chapter 08.08 클러스터링 인덱스(Clustering Index) (0) | 2023.01.04 |