📔설명
기본 데이터 처리, 고급 최적화, 쿼리 힌트에 대해 알아보자
🍑개요
1. 쿼리 실행 절차
- 사용자로부터 요청된 SQL문장을 잘게 쪼개서 MySQL 서버가 이해할 수 있는 수준으로
분리(파스 트리) - SQL의 파싱 정보를 확인하면서
어떤 테이블부터 읽고어떤 인덱스를 이용해 테이블을 읽을지 선택 - 결정된 테이블의 읽기 순서나 선택된 인덱스를 이용해 스토리지 엔진으로부터
데이터를 가져옴
첫 번째 단계를 SQL 파싱이라고 하며 SQL 파서가 처리
=> 문법 오류는 해당 단계에서 걸러짐
두 번째 단계는 최적화 및 실행 계획 수립 단계이며 옵티마이저가 처리
=> 완료시 실행 계획 만들어짐
세 번째 단계는 스토리지 엔진이 레코드를 읽어오도록 요청하고, MySQL 엔진은 스토리지 엔진이 받아온 레코드를 조인하거나 정렬
2. 옵티마이저의 종류
규칙 기반 최적화(RBO) : 옵티마이저에 내장된 우선순위에 따라 실행계획 수립
=> 통계 정보 조사X
비용 기반 최적화(CBO) : 비용과 통계 정보를 이용해 실행 계획별 비용을 비교해 최소로 소요되는 처리 방식 선택
=> 대부분 DBMS 채택
🍒기본 데이터 처리
1. 풀 테이블 스캔과 풀 인덱스 스캔
풀 테이블 스캔 : 인덱스를 사용하지 않고 테이블의 데이터를 처음부터 끝까지 읽어서 처리
=> 테이블 레코드 건수가 너무 작아서 인덱스를 통해 읽는 것 보다 풀 테이블 스캔이 더 빠른 경우
=> where절이나 on절에 인덱스를 이용할 수 있는 적절한 조건이 없는 경우
=> 인덱스 레인지 스캔을 사용할 수 있는 쿼리라도 조건 일치 레코드 수가 너무 많은 경우
리드 어해드(Read ahead) : 어떤 영역의 데이터가 앞으로 필요해질 것을 예측해서 요청이 오기 전 미리 디스크에서 읽어 InnoDB의 버퍼 풀에 가져다 두는 것
=> 풀 테이블 스캔 실행 시 처음 몇 개의 데이터 페이지는 포그라운드 스레드가 페이지 읽기를 실행하지만 특정 시점부터는 읽기 작업을 백그라운드 스레드로 넘겨 한 번에 4개 또는 8개씩 페이지를 읽음
=> 풀 인덱스 스캔에서도 동일하게 사용
풀 인덱스 스캔 : 인덱스를 처음부터 끝까지 스캔하는 것
2. 병렬 처리
병렬 처리 : 하나의 쿼리를 여러 스레드가 작업을 나누어 동시에 처리하는 것
=> innodb_parallel_read_threads 시스템 변수로 스레드 개수 지정 가능
set session innodb_parallel_read_threads=2;
select count(*) from salaries;
set session innodb_parallel_read_threads=8;
select count(*) from salaries;
3. ORDER BY 처리(Using filesort)
| 장점 | 단점 | |
|---|---|---|
| 인덱스 이용 | 쿼리 실행 시 이미 인덱스가 정렬돼 있어 순서대로 읽기만 하면 됨 | 부가적인 인덱스 추가/삭제 작업이 필요함 인덱스 때문에 디스크 공간이 더 필요함 인덱스 개수 늘어날수록 버퍼 풀 메모리 많이 필요 |
| Filesort 이용 | 인덱스 생성하지 않아도 돼 인덱스 이용시의 단점이 장점 정렬할 레코드 적으면 메모리에서 Filesort 처리 | 정렬 작업이 쿼리 실행시 처리돼 대상 건수 많을수록 쿼리 응답 속도 느림 |
인덱스를 이용하지 않고 별도의 정렬 처리를 수행했는지는 Extra칼럼의 Using filesort 메시지 표시 여부로 확인
소트 버퍼
소트 버퍼(Sort buffer) : MySQL이 정렬을 수행하기 위해 별도의 메모리 공간을 할당받아서 사용
=> 정렬이 필요한 경우에만 할당
정렬해야 할 레코드의 건수가 소트 버퍼로 할당된 공간보다 크다면, 정렬해야 할 레코드를 여러 조각으로 나눠서 처리하는데 이 과정에서 임시 저장을 위해 디스크를 사용
=> 메모리의 소트 버퍼에서 정렬을 수행하고, 그 결과를 임시로 디스크에 기록해 두고, 다음 레코드를 가져와서 다시 정렬해서 반복적으로 디스크에 임시 저장 후, 병합하면서 정렬 수행
=> 위의 병합 작업을 멀티 머지라고 함
소트 버퍼는 세션 메모리 영역에 해당하므로 공유해서 사용X한 공간이므로 56KB에서 1MB미만으로 설정하는 것이 적절
정렬 알고리즘
레코드 정렬시 레코드 전체를 소트 버퍼에 담을지(싱글 패스) 또는 정렬 기준 칼럼만 소트 버퍼에 담을지(투 패스)
-- 정렬 수행 쿼리가 어떤 정렬 모드 사용하는지 확인
-- 옵티마이저 트레이스 활성화
set optimizer_trace="enabled=on", end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
-- 쿼리 실행
select * from employees order by last_name limit 100000, 1;
-- 트레이스 내용
select * from information_schema.optimizer_trace \G
<sort_key, rowid>: 정렬키와 레코드의로우 아이디만 가져와서 정렬 =>투 패스<sort_key, additional_fields>: 정렬 키와 레코드전체를 가져와서 정렬, 레코드 칼럼은고정사이즈로 메모리에 저장 =>싱글 패스<sort_key, packed_additional_fields>: 정렬 키와 레코드전체를 가져와서 정렬, 레코드 칼럼은가변사이즈로 메모리에 저장 =>싱글 패스
싱글 패스 정렬 방식
소트 버퍼에 정렬 기준 칼럼을 포함해 select 대상이 되는 칼럼 전부를 담아서 정렬 수행
select emp_no, first_name, last_name
from employees
order by first_name;
정렬에 필요하지 않은 last_name까지 전부 읽어서 소트 버퍼에 담고 정렬 수행
투 패스 정렬 방식
정렬 대상 칼럼과 프라이머리 키 값만 소트 버퍼에 담아서 정렬 수행, 정렬된 순서대로 다시 프라이머리 키로 테이블 읽어서 select할 칼럼 가져오는 정렬 방식
처음에는 정렬에 필요한 first_name과 프라이머리 키인 emp_no만 읽어서 정렬을 수행하고, 그 순서대로 employees 테이블을 한 번 더 읽어서 last_name을 가져옴
싱글 패스 정렬을 사용하지 못하고투 패스 정렬 방식을 사용하는 경우
-레코드의 크기가max_length_for_sort_data시스템 변수에 설정된 값보다 클 때
-BLOB이나TEXT타입의 칼럼이select 대상에 포함될 때
정렬 처리 방법
| 정렬 처리 방법 | 실행 계획의 Extra 칼럼 내용 |
|---|---|
| 인덱스 사용 정렬 | 별도 표기 없음 |
| 조인에서 드라이빙 테이블만 정렬 | "Using filesort" 표시 |
| 조인에서 조인 결과를 임시 테이블로 저장 후 정렬 | "Using temporary; Using fileosrt" 표시 |
인덱스를 이용한 정렬
- order by절에 명시된 칼럼이 제일
먼저 읽는 테이블(드라이빙 테이블)에 속하고,order by 순서대로생성된 인덱스가 있어야 함 - where절에 첫 번째로 읽는 테이블의
칼럼에 대한 조건이 있다면 그 조건과 order by는같은 인덱스를 사용할 수 있어야 함 B-Tree에서 가능- 여러 테이블 조인 시
NL조인에서만 가능
select *
from employees e, salaries s
where s.emp_no=e.emp_no
and e.emp_no between 100002 and 100020
order by e.emp_no;
조인의 드라이빙 테이블만 정렬
조인시 레코드 건수가 많아지고 레코드 크기도 커지므로, 조인 실행 전 첫 번째 테이블의 레코드를 먼저 정렬한 다음 조인하는 것이 좋음
드라이빙 테이블의칼럼만으로order by절 작성해야 함
select *
from employees e, salaries s
where s.emp_no=e.emp_no
and e.emp_no between 100002 and 100020
order by e.last_name;where절의 검색 조건은 employees의 프라이머리 키를 이용해 작업량을 줄일 수 있으며, 드리븐 테이블 salaries의 조인 칼럼인 s.emp_no에 인덱스가 있어 employees를 드라이빙 테이블로 선택할 것
임시 테이블을 이용한 정렬
select *
from employees e, salaries s
where s.emp_no=e.emp_no
and e.emp_no between 100002 and 100020
order by s.salary;
정렬 처리 방법의 성능 비교
스트리밍 방식 : 조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트에 전송
=> OLTP 환경
=> LIMIT 조건 추가시 마지막 레코드 가져오는 시간 상당히 줄어듦
=> order by나 group by는 조건을 만족하는 모든 레코드를 가져온 후 정렬하거나 그룹핑해야 하기 때문에 스트리밍 방식 사용 불가
버퍼링 방식 : 스트리밍의 반대 표현으로, MySQL 서버가 검색하고 정렬하는 동안 클라이언트는 아무것도 하지 않고 기다림
인덱스를 사용한 정렬 방식만 스트리밍 형태의 처리이며, 나머지는 모두 버퍼링된 후 정렬됨
정렬 관련 상태 변수
-- 정렬 관련 상태 변수
flush status;
show status like 'Sort%';
Sort_merge_passes: 멀티 머지 처리 횟수Sort_range:인덱스 레인지 스캔을 통해 정렬Sort_scan:풀 테이블 스캔으로 정렬Sort_rows: 지금까지 정렬한전체 레코드 건수
4. GROUP BY 처리
인덱스 스캔을 이용하는 GROUP BY(타이트 인덱스 스캔)
조인의 드라이빙 테이블에 속한 칼럼만 이용해 그루핑할 때 group by 칼럼으로 이미 인덱스가 있는 경우
루스 인덱스 스캔을 이용하는 GROUP BY
Extra 칼럼에 "Using Index for group-by" 코멘트 나타남
-- 인덱스 : emp_no+from_date
explain
select emp_no
from salaries
where from_date='1985-03-01'
group by emp_no;
단일 테이블에 대해 수행되는gorup by 처리에만 사용 가능Min()과Max()이외의 집합 함수 사용시 불가
임시 테이블을 사용하는 GROUP BY
explain
select e.last_name, avg(s.salary)
from employees e, salaries s
where e.emp_no=s.emp_no
group by e.last_name;
group by가 사용되면 내부적으로 group by 절의 칼럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 생성해 중복 제거 및 집합 함수 연산 수행
5. DISTINCT 처리
집합 함수와 같이 distinct가 사용되는 쿼리의 실행 계획에서 distinct 처리가 인덱스 사용 불가시 항상 임시 테이블 필요
SELECT DISTINCT ...
group by와 동일한 방식으로 처리
select절에 사용된 distinct는 모든 칼럼에 영향을 미침
집합 함수와 함께 사용된 DISTINCT
집합 함수 내에서 사용된 distinct는 그 집합 함수의 인자로 전달된 칼럼값이 유니크한 것들을 가져옴
explain
select count(distinct s.salary)
from employees e, salaries s
where e.emp_no=s.emp_no
and e.emp_no between 100001 and 100100;
count(distinct s.salary) 처리를 위해 임시 테이블 사용
-- 인덱스 된 칼럼에 distinct 처리시 인덱스 풀 스캔 or 레인지 스캔해 임시 테이블 없어도 됨
explain
select count(distinct emp_no) from employees;
6. 내부 임시 테이블 활용
처음에는 메모리에 생성됐다가 테이블 크기가 커지면 디스크로 옮겨짐
=> create temporary table로 만드는 임시 테이블과 다름
=> 쿼리 완료시 자동으로 삭제
=> 다른 세션이나 다른 쿼리에서는 볼 수도 없고 사용도 불가
메모리 임시 테이블과 디스크 임시 테이블
메모리는 TempTable이라는 스토리지 엔진 사용, 디스크에 저장되는 임시 테이블은 InnoDB 스토리지 엔진 사용
임시 테이블의 크기가 커지는 경우 MySQL 서버는 메모리의 임시 테이블을 디스크로 기록
=> MMAP 파일로 디스크에 기록 (오버헤드가 더 적음)
=> InnoDB 테이블로 기록
임시 테이블이 필요한 쿼리
- order by와 group by에 명시된 칼럼이 다른 쿼리
- order by나 group by에 명시된 칼럼이 조인의 순서상 첫 번째 테이블이 아닌 쿼리
- distinct와 order by가 동시에 쿼리에 존재하는 경우 또는 distinct가 인덱스로 처리되지 못하는 쿼리
- union이나 union distinct가 사용된 쿼리
- 쿼리 실행 계획에서 select_type이 DERIVED인 쿼리
마지막 쿼리 패턴을 제외한 위 네가지는 유니크 인덱스를 가지는 내부 임시 테이블이 만들어짐
임시 테이블이 디스크에 생성되는 경우
- union이나 union all에서 select되는 칼럼 중 길이가 512바이트 이상인 크기의 칼럼이 있는 경우
- group by나 distinct 칼럼에서 512바이트 이상인 크기의 칼럼이 있는 경우
- 메모리 임시 테이블 크기가 temptable_max_ram 시스템 변수 값보다 큰 경우
임시 테이블 관련 상태 변수
-- 임시 테이블이 디스크에 생성됐는지 메모리에 생성됐는지 확인
flush status;
select first_name, last_name
from employees
group by first_name, last_name;
--확인
show session status like 'Created_tmp%';
Created_tmp_tables: 쿼리 처리를 위해 만들어진임시 테이블의개수누적Created_tmp_disk_tables:디스크에 내부 임시 파일이 만들어진개수누적
🍓고급 최적화
옵티마이저가 실행 계획을 수립할 때 통계 정보와 옵티마이저 옵션을 결합해서 최적의 실행계획 수립
=> 옵티마이저 옵션 : 조인 관련된 옵션과 옵티마이저 스위치로 구분 가능
1. 옵티마이저 스위치 옵션
옵티마이저 스위치 : MySQL 서버의 고급 최적화 기능들을 활성화할지 제어
-- 옵티마이저 스위치 설정
set global optimizer_switch='index_merge=on, index_merge_union=on,...';MRR과 배치 키 액세스(batched_key_access)
MySQL 서버에선 지금까지 NL 조인을 지원해서 처리했는데, 실제 레코드를 검색하고 읽는 부분은 스토리지 엔진이 담당한다. 드라이빙 테이블의 레코드 건별로 드리븐 테이블의 레코드를 찾으면 레코드를 찾고 읽는 스토리지 엔진에서는 아무런 최적화 수행 불가
MRR(Multi-Range Read) : MySQL 서버가 조인 대상 테이블 중 하나로부터 레코드를 읽어서 조인 버퍼에 버퍼링
=> 드라이빙 테이블의 레코드를 읽어서 바로 조인하지 않고 조인 대상을 버퍼링
=> 조인 버퍼에 레코드가 가득 차면 비로소 MySQL 엔진은 버퍼링된 레코드를 스토리지 엔진으로 요청
=> 스토리지 엔진은 읽어야 할 레코드를 데이터 페이지에 정렬된 순서로 접근해 디스크 데이터 페이지 읽기 최소화
BKA(Batched Key Access) 조인 : MRR을 응용해서 실행되는 조인 방식
블록 네스티드 루프 조인(block_nested_loop)
MySQL 8.0.20 이후는 해시 조인 알고리즘이 대체되어 사용
explain
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';
--employees가 드라이빙
NL조인과 블록 NL조인의 큰 차이
조인 버퍼 사용 여부
=>Extra칼럼에"Using Join buffer"라고 표시되면조인 버퍼사용- 조인에서
드라이빙 테이블과드리븐 테이블이어떤 순서로 조인되는지
어떤 방식으로도 드리븐 테이블의 풀 테이블 스캔이나 인덱스 풀 스캔을 피할 수 없다면, 옵티마이저는 드라이빙 테이블에서 읽은 레코드를 메모리에 캐시한 후 드리븐 테이블과 이 메모리 캐시를 조인
=> 이때 사용되는 메모리의 캐시가 조인 버퍼
--조인 조건이 없어 카티션 조인
explain
select *
from dept_emp de, employees e
where de.from_date>'1995-01-01'
and e.emp_no<109004;
위 결과에서 employees 테이블의 결과 기준으로 dept_emp 테이블의 결과를 결합하여 조인 순서가 거꾸로인 것처럼 실행됨
인덱스 컨디션 푸시다운(index_condition_pushdown)
alter table employees add index ix_lastname_firstname (last_name, first_name);
set optimizer_switch='index_condition_pushdown=off';
show variables like 'optimizer_switch' \G
explain
select *
from employees
where last_name='Acton' --범위 검색 조건
and first_name like '%sal'; -- 필터링 조건
Extra 칼럼의 "Using where" : InnoDB 스토리지 엔진이 읽어서 반환해준 레코드가 인덱스를 사용할 수 없는 where 조건에 일치하는지 검사하는 과정
위는 MySQL이 인덱스를 범위 제한 조건으로 사용하지 못하는 first_name 조건은 아예 MySQL 엔진이 스토리지 엔진으로 전달해주지 않았다.
=> InnoDB : 인덱스 비교
=> MySQL 엔진 : 테이블의 레코드에서 조건 비교하는 작업
MySQL 5.6 이후 인덱스를 범위 제한 조건으로 사용하지 못하더라도, 인덱스에 포함된 칼럼의 조건이 있다면 모두 같이 모아서 스토리지 엔진으로 전달할 수 있도록 개선
set optimizer_switch='index_condition_pushdown=on';
show variables like 'optimizer_switch'\G
explain
select *
from employees
where last_name='Acton'
and first_name like '%sal';
인덱스 확장(use_index_extensions)
InnoDB 스토리지 엔진을 사용하는 테이블에서 세컨더리 인덱스에 자동으로 추가된 프라이머리 키를 활용할 수 있게 할지 결정
create table dept_emp(
...
primary key(dept_no, emp_no),
key ix_fromdate(from_date)
) engine=innodb;위 테이블의 프라이머리 키는 dept_no+emp_no이며, 세컨더리 인덱스는 idx_fromdate인데, 세컨더리 인덱스는 데이터 레코드를 찾기 위해 프라이머리 키를 순서대로 포함
=> 즉, ix_fromdate인덱스는 from_date+dept_no+emp_no 조합으로 인덱스를 생성한 것과 흡사하게 작동 가능
=> 옵티마이저가 세컨더리 인덱스의 마지막에 자동으로 추가되는 프라이머리 키를 활용할 수 있도록 개선
explain
select count(*) from dept_emp
where from_date='1987-07-25'
and dept_no='d001';
key_len 칼럼은 인덱스를 구성하는 칼럼 중 어느 부분까지 사용했는지를 바이트 수로 보여줌
=> from_date(3바이트)+dept_no(16바이트) = 19
explain
select count(*) from dept_emp
where from_date='1987-07-25';
인덱스 머지(index_merge)
하나의 테이블에 대해 2개 이상의 인덱스를 이용해 쿼리 처리
인덱스 머지 - 교집합(index_merge_intersection)
explain
select *
from employees
where first_name='Georgi'
and emp_no between 10000 and 20000;
ix_firstname과 primary 인덱스를 모두 사용해 쿼리 처리
ix_firstname 인덱스는 프라이머리 키인 emp_no 칼럼을 가지고 있어 ix_firstname 인덱스만 사용하는 것이 더 성능이 좋을 것으로 생각된다면 비활성화하면 됨
인덱스 머지 - 합집합(index_merge_union)
where절에 or 연산자로 연결된 경우 사용
explain
select *
from employees
where first_name='Matt'
or hire_date='1987-03-31';
first_name으로 검색한 결과랑 hire_date로 검색한 결과는 모두 프라이머리 키를 기준으로 각각 정렬돼 있으므로 MySQL 서버는 두 집합에서 하나씩 가져와서 서로 비교하면서 프라이머리 키인 emp_no 칼럼의 값이 중복된 레코드들을 정렬 없이 걸러내기 가능
인덱스 머지 - 정렬 후 합집합(index_merge_sort_union)
explain
select *
from employees
where first_name='Matt'
or hire_date between '1987-03-01' and '1987-03-31';
hire_date에 대한 조건 결과는 emp_no 칼럼으로 정렬돼 있지 않음
=> 그러므로 중복 제거를 위해 각 집합을 정렬한 다음 중복 제거
세미 조인(semijoin)
세미 조인(Semi-Join) : 다른 테이블과 실제 조인 수행X, 단지 다른 테이블에서 조건에 일치하는 레코드가 있는지 없는지만 체크하는 형태의 쿼리
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.from_date='1995-01-01');
-- 최적화 전에는 57건만 읽으면 될 것을 30만 건 넘게 읽어서 한 건 한 건 서브쿼리 조건과 비교함= (subquery) 형태와 in (subquery) 형태의 세미 조인 쿼리 최적화
세미 조인최적화
-Table Pull-out
-Duplicate Weed-out
-First Match
-Loose Scan
-MaterializationIN-to-EXISTS최적화MATERIALIZATION최적화
<> (subquery) 형태와 not in (subquery) 형태의 안티 세미 조인 쿼리 최적화
IN-to-EXISTS최적화MATERIALIZATION최적화
테이블 풀-아웃(Table Pull-out)
Table pullout 최적화 : 세미 조인의 서브쿼리에 사용된 테이블을 아우터 쿼리로 끄집어낸 후 쿼리를 조인 쿼리로 재작성하는 형태
explain
select *
from employees e
where e.emp_no in (select de.emp_no
from dept_emp de
where de.dept_no='d009');
id 칼럼 값이 모두 1 이라는 것은 두 테이블이 서브쿼리 형태가 아니라 조인으로 처리됐다는 것을 의미
-- 옵티마이저가 재작성한 쿼리 살펴보기
show warnings \G
Table pullout 최적화 제한 사항 및 특징
세미 조인 서브쿼리에서만 사용 가능- 서브쿼리 부분이 unique 인덱스나 프라이머리 키 룩업으로
결과가 1건인 경우에만 사용 가능 - 기존 쿼리에서 가능했던 방법이 사용 불가능한 것은 아니므로 MySQL에서는 가능하다면 Table pullout 최적화 최대한 적용
- 서브쿼리 테이블을 아우터 쿼리로 가져와 최적화 수행하는데, 서브쿼리의 모든 테이블이 아우터 쿼리로 끄집어 낼 수 있다면 서브쿼리 자체는 없어짐
퍼스트 매치(firstmatch)
First Match 최적화 : IN(subquery) 형태의 세미 조인을 EXISTS(subquery) 형태로 튜닝한 것과 비슷
explain
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 '1995-01-01' and '1996-01-30');
In-to-EXISTS 변환에 비해 First Match 전략의 장점
- 옵티마이저가 자동으로
동등 조건을 추가하는동등 조건 전파가서브쿼리 내에서만 가능했던 것이조인 형태로 처리되면서아우터 쿼리까지전파가능 - 일부 테이블에 대해서
FirstMatch가능
FirstMatch 최적화 제한 사항 및 특성
- 서브쿼리는 그 서브쿼리가 참조하는 모든 아우터 테이블이
먼저 조회된 이후에 실행 - Extra 칼럼에
"FirstMatch(table-N)"문구 표시 상관 서브쿼리에서도 사용 가능group by나집합 함수가 사용된 서브쿼리의 최적화에는 사용 불가
루스 스캔(loosescan)
LooseScan : 인덱스를 사용하는 group by 최적화 방법에서의 "Using index for group-by"의 루스 인덱스 스캔과 비슷
explain
select * from departments d where d.dept_no in(
select de.dept_no from dept_emp de);
dept_emp 테이블의 프라이머리 키(dept_no+emp_no)를 루스 인덱스 스캔으로 유니크한 dept_no만 읽으면 서브쿼리 실행 가능
=> dept_no만으로 그루핑시 9건밖에 없기 때문
LooseScan 최적화의 특성
- 루스 인덱스 스캔으로
서브쿼리테이블을 읽고 그 다음으로아우터 테이블을드리븐으로 사용해서 조인 수행
구체화(Materialization)
Materialization : 세미조인에 사용된 서브쿼리를 통째로 구체화해서 쿼리 최적화
=> 즉 내부 임시 테이블을 생성
explain
select *
from employees e
where e.emp_no in (select de.emp_no from dept_emp de
where de.from_date='1995-01-01');
dept_emp de 테이블이 먼저 실행되어 임시 테이블<subquery2>이 만들어지고, 최종적으로 구체화된 임시 테이블과 employees 테이블을 조인해서 결과 반환
서브쿼리 내에 group by가 있어도 최적화 전략 사용 가능
Materialization 최적화 제한 사항 및 특성
IN 서브쿼리에서 서브쿼리는상관 서브쿼리가 아니어야 함- 서브쿼리는
grouip by나집합 함수들이 사용돼도 가능 내부 임시 테이블사용
중복 제거(Duplicated Weed-out)
Duplicate Weedout : 세미조인 서브쿼리를 일반적인 inner join 쿼리로 바꿔서 실행 후 마지막에 중복된 레코드 제거
=> 아래 쿼리처럼 inner join+group by 절로 바꿔서 실행하는 것과 동일한 작업으로 쿼리 처리
explain
select * from employees e
where e.emp_no in (select s.emp_no from salaries s where s.salary>150000);salaries 테이블 프라이머리 키가 emp_no+from_date로 중복된 emp_no가 발생할 수 있으므로 group by절 추가시 동일한 결과
select e.*
from employees e, salaries s
where e.emp_no=s.emp_no and s.salary>150000
group by e.emp_no;
- salaries 테이블의 인덱스 스캔 후 employees 테이블과 조인
- 조인된 결과를
임시 테이블에 저장 - 임시 테이블에 저장된 결과에서 중복 제거
- 최종 반환
Duplicate Weedout 최적화 장점 및 제약 사항
- 서브쿼리가 상관 서브쿼리라도 사용 가능
- 서브쿼리에 group by나 집합 함수가 사용된 경우 사용 X
- 서브쿼리의 테이블을 조인으로 처리해 최적화 방법 많음
컨디션 팬아웃(condition_fanout_filter)
옵티마이저는 여러 테이블이 조인되는 경우 가능하다면 일치하는 레코드 건수가 적은 순서대로 조인 실행
explain
select *
from employees e
inner join salaries s on s.emp_no=e.emp_no
where e.first_name='Matt'
and e.hire_date between '1985-11-21' and '1986-11-21';--컨디션 팬아웃 비활성화
set optimizer_switch='condition_fanout_filter=off';
- employees 테이블에서 인덱스를 이용해 Matt에 일치하는 233건을 검색
- 검색된 233건 중 hire_date 조건에 맞는 레코드만 걸러내는데,
filtered에100으로 나온 것은 옵티마이저가 233건 모두hire_date 칼럼 조건을 만족할 것으로 예측 - employees 테이블의 233건에 대해 salaries 테이블의
프라이머리 키를 이용해 레코드 읽는데, 옵티마이저는 employees 레코드 한 건당 salaries 레코드 9건이 일치할 것으로 예상
--컨디션 팬아웃 활성화
set optimizer_switch='condition_fanout_filter=on';
employees 테이블에서 60건(233*0.26)만 조건을 충족할 것이라고 예측
레코드 비율 계산 가능한 경우
- 칼럼에
인덱스가 있는 경우 - 칼럼에
히스토그램이 존재하는 경우
파생 테이블 머지(derived_merge)
파생 테이블(Derived Table) : from절에 사용된 서브쿼리
=> 임시 테이블로 조건 만족하는 테이블로 생성함
dreived_merge : 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합해서 서브쿼리 부분을 제거하는 것
explain
select * from(
select * from employees where first_name='Matt') derived_table
where derived_table.hire_date='1986-04-03';
DERIVED 라인이 사라지고 서브쿼리 없이 employees 테이블을 단순 조회하는 실행계획으로 바뀜
옵티마이저가 자동으로 서브쿼리를 외부쿼리로 병합할 수 없어 수동으로 작성해야 할 경우
- 집계함수 및 윈도우 함수 사용된 서브쿼리
- distinct 사용된 서브쿼리
- group by나 having이 사용된 서브쿼리
- limit이 사용된 서브쿼리
- union 또는 union all 포함 서브쿼리
- select 절에 사용된 서브쿼리
- 값이 변경되는 사용자 변수가 사용된 서브쿼리
인비저블 인덱스(use_invisible_indexes)
인덱스를 삭제하지 않고 해당 인덱스를 사용하지 못하게 제어하는 기능
alter table ... alter index ... [visible|invisible]스킵 스캔(skip_scan)
alter table employees add index ix_gender_birthdate (gender, birth_date);select * from employees where birth_date>='1965-02-01';인덱스 스킵 스캔을 통해 선행 칼럼이 조건절에 없더라도 후행 칼럼의 조건만으로도 인덱스 이용한 쿼리 성능 개선 가능
--힌트
/*+ skip_scan()*/
/*+ no_skip_scan()*/해시 조인(hash_join)
해시 조인은 조인 조건의 칼럼에 인덱스가 없거나 조인 대상 테이블 중 일부 레코드 건수가 매우 적은 경우에 대해서만 사용
explain
select *
from employees e ignore index (primary, ix_hiredate)
inner join dept_emp de ignore index(ix_empno_fromdate, ix_fromdate)
on de.emp_no=e.emp_no
and de.from_date=e.hire_date;
빌드 단계 : 레코드 건수가 적은 테이블을 골라 메모리에 해시 테이블 생성하는 작업
프로브 단계 : 나머지 테이블의 레코드를 읽어 해시 테이블의 일치 레코드를 찾는 과정
explain format=tree
select *
from employees e ignore index (primary, ix_hiredate)
inner join dept_emp de ignore index(ix_empno_fromdate, ix_fromdate)
on de.emp_no=e.emp_no
and de.from_date=e.hire_date;
위의 경우 de(dept_emp)가 빌드 테이블
해시 테이블 레코드 건수가 많아 조인 버퍼의 공간이 부족한 경우 청크로 빌드 테이블과 프로브 테이블을 분리한 다음 다시 동일 방식으로 해시 조인
클래식 해시 조인 알고리즘 : 메모리에서 모두 처리 가능한 경우
그레이스 해시 조인 알고리즘 : 해시 테이블이 조인 버퍼 메모리보다 큰 경우
인덱스 정렬 선호(prefer_ordering_index)
prefer_ordering_index : order by를 위한 인덱스에 너무 가중치를 부여하지 않도록 하는 옵션
=> 옵티마이저가 자주 실수시 off로 변경
2. 조인 최적화 알고리즘
조인 쿼리의 실행 계획 최적화를 위한 알고리즘이 2개 있음
select *
from t1, t2, t3, t4
where ...Exhaustive 검색 알고리즘
from절에 명시된 모든 테이블의 조합에 대해 실행 계획의 비용을 계산해 최적의 조합 1개를 찾는 방법
Greedy 검색 알고리즘
- 전체 N개 테이블 중
optimizer_search_depth변수에 정의된 개수의 테이블로 가능한 조인 조합 생성 (그림에선 2로 가정) - 1번에서 생성된 조합 중 최소 비용 실행 계획 하나 선정
- ㅈ번에서 선정된 계획의 첫 번째 테이블을
부분 실행 계획(실행 계획 완료 대상)의 첫 번째 테이블로 설정 N-1개 테이블 중 다시optimizer_search_depth변수에 정의된 개수의 테이블로 가능한 조인 조합 생성(3번에서 선택된 테이블 제외)- 4번에서 생성된 조인 조합들을 하나씩
부분 실행 계획에 대입해 실행 비용 계산 부분 실행 계획의 두번째 테이블로 선정- 남은 테이블이 모두 없어질 때까지 반복
optimizer_search_depth 설정 값이 테이블 개수보다 작다면, optimizer_searcg_depth만큼은 Exhaustive 검색을 사용하고 나머지는 Greedy 검색
테이블 개수가 더 작다면 Exhaustive 검색만 사용
optimizer_prune_level은 1로 설정시 Heuristic 알고리즘을 사용하므로 변경하지 말자
🍅쿼리 힌트
- 인덱스 힌트
- 옵티마이저 힌트
1. 인덱스 힌트
STRAIGHT_JOIN과 USE INDEX 등 인덱스 힌트
=> ANSI-SQL 표준 문법 준수 불가(SQL 문법에 맞게 사용해야 하므로)
=> SELECT와 UPDATE에서만 사용 가능
STRAIGHT_JOIN
STRAIGHT_JOIN : SELECT, UPDATE, DELETE 쿼리에서 여러 개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할
=> from절에 명시된 순서대로 조인
explain
select *
from employees e, dept_emp de, departments d
where e.emp_no=de.emp_no and d.dept_no=de.dept_no;조인 순서는 보통 칼럼들의 인덱스 여부로 결정되며, 인덱스에 문제가 없다면 레코드가 적은 테이블이 드라이빙 테이블이 된다.
select straight_join e.first_name,e.last_name,d.dept_name
from employees e, dept_emp de, departments d
where e.emp_no=de.emp_no and d.dept_no=de.dept_no;
--아래랑 동일
select /*! straight_join */ e.first_name,e.last_name,d.dept_name
from employees e, dept_emp de, departments d
where e.emp_no=de.emp_no and d.dept_no=de.dept_no;
조인 순서 조정
임시 테이블(인라인 뷰)와일반 테이블조인 : 일반적으로임시 테이블을드라이빙 테이블로 선정임시 테이블끼리조인 : 임시 테이블은 항상 인덱스가 없으므로크기가 작은 테이블을드라이빙일반 테이블끼리조인 : 양쪽 테이블 모두 인덱스가 있거나 없으면레코드가 적은테이블을드라이빙, 그 이외에조인 칼럼에인덱스가 없는 테이블을드라이빙으로 선택
STRAIGHT_JOIN과 비슷한 역할
- JOIN_FIXED_ORDER
- JOIN_ORDER
- JOIN_PREFIX
- JOIN_SUFFIX
USE INDEX / FORCE INDEX / IGNORE INDEX
사용하려는 인덱스를 가지는 테이블 뒤에 힌트 명시
키워드 뒤에 사용할 인덱스의 이름을 괄호로 묶어서 사용
USE INDEX: 옵티마이저에게 해당 인덱스를 사용하도록권장FORCE INDEX:USE INDEX보다 미치는 영향이 더 강한 힌트IGNORE INDEX: 특정 인덱스를사용하지 못하게하는 용도
인덱스 용도
USE INDEX FOR JOIN:테이블 간 조인뿐만 아니라레코드 검색을 위한 용도도 포함
=> MySQL은 하나의 테이블로부터 데이터 검색하는 작업도 JOIN으로 표현USE INDEX FOR ORDER BY:ORDER BY용도로만 사용USE INDEX FOR GROUP BY:GROUP BY용도로만 사용
select * from employees use index(primary) where emp_no=10001;SQL_CALC_FOUND_ROWS
SQL_CALC_FOUND_ROWS : LIMIT을 만족하는 수만큼의 레코드를 찾았다더라도 끝까지 검색 수행
=> LIMIT을 제외한 조건을 만족하는 레코드가 전체 몇 건이었는지 알 수 있음 (found_rows() 함수 사용)
select sql_calc_found_rows *
from employees
limit 5;
select found_rows() as total_record_count;
2. 옵티마이저 힌트
옵티아미저 힌트 종류
영향 범위에 따라
인덱스: 특정 인덱스의 이름을 사용할 수 있는 힌트테이블: 특정 테이블의 이름을 사용할 수 있는 힌트쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 힌트로, 힌트가 명시된 쿼리 블록에 대해서만 영향글로벌: 전체 쿼리에 대해서 영향을 미치는 힌트
| 힌트 이름 | 설명 | 영향 범위 |
|---|---|---|
| MAX_EXECUTION_TIME | 쿼리 실행 시간 제한 | 글로벌 |
| RESOURCE_GROUP | 쿼리 실행 리소스 그룹 설정 | 글로벌 |
| SET_VAR | 시스템 변수 제어 | 글로벌 |
| SUBQUERY | 서브쿼리 세미 조인 최적화 | 쿼리 블록 |
| BKA, NO_BKA | BKA 조인 사용 여부 | 쿼리 블록, 테이블 |
| BNL, NO_BNL | 해시 조인 사용 여부 제어 | 쿼리 블록, 테이블 |
| DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN | 외부 쿼리 조건을 서브쿼리로 옮기는 최적화 사용 여부 | 쿼리 블록, 테이블 |
| JOIN_FIXED_ORDER | FROM절에 명시된 테이블 순서대로 조인 | 쿼리 블록 |
| JOIN_ORDER | 힌트에 명시된 순서대로 조인 | 쿼리 블록 |
| JOIN_PREFIX | 힌트에 명시된 테이블을 드라이빙 테이블로 | 쿼리 블록 |
| JOIN_SUFFIX | 힌트에 명시된 테이블을 드리븐 테이블로 | 쿼리 블록 |
| QB_NAME | 쿼리 블록 이름 설정 | 쿼리 블록 |
| SEMIJOIN, NO_SEMIJOIN | 서브쿼리 세미 조인 최적화 | 쿼리 블록 |
| MERGE, NO_MERGE | FROM절의 서브쿼리나 뷰를 외부 쿼리 블록으로 병합 | 테이블 |
| INDEX_MERGE, NO_INDEX_MERGE | 인덱스 병합 실행 계획 사용 여부 | 테이블, 인덱스 |
| MRR, NO_MRR | MRR 사용 여부 | 테이블, 인덱스 |
| NO_ICP | ICP(인덱스 컨디션 푸시다운) 사용 여부 | 테이블, 인덱스 |
| NO_RANGE_OPTIMIZATION | 인덱스 레인지 액세스 비활성화 | 테이블, 액세스 |
| SKIP_SCAN, NO_SKIP_SCAN | 인덱스 스킵 스캔 제어 | 테이블, 인덱스 |
| INDEX, NO_INDEX | 인덱스 사용 여부 제어 | 인덱스 |
| GROUP_INDEX, NO_GROUP_INDEX | GROUP BY절 인덱스 사용 여부 제어 | 인덱스 |
| JOIN_INDEX, NO_JOIN_INDEX | WHERE절 처리를 위한 인덱스 사용 여부 | 인덱스 |
| ORDER_INDEX, NO_ORDER_INDEX | ORDER BY절 처리를 위한 인덱스 사용 여부 | 인덱스 |
explain
select /*+ join_order(e, s@subq1) */ count(*)
from employees e
where e.first_name='Matt'
and e.emp_no in (select /*+ qb_name(subq1) */ s.emp_no
from salaries s
where s.salary between 50000 and 50500);
MAX_EXECUTION_TIME
MAX_EXECUTION_TIME : 쿼리의 최대 실행 시간 설정하는 힌트
=> 밀리초
select /*+ max_execution_time(100) */ *
from employees
order by last_name limit 1;
-- 지정 시간 초과시 쿼리 실패SET_VAR
SET_VAR : 시스템 변수 제어
explain
select /*+ set_var(optimizer_switch='index_merge_intersection=off') */ *
from employees
where first_name='Georgi' and emp_no between 10000 and 20000;SEMIJOIN & NO_SEMIJOIN
| 최적화 전략 | 힌트 |
|---|---|
| Duplicate Weed-out | SEMIJOIN(DUPSWEEDOUT) |
| First Match | SEMIJOIN(FIRSTMATCH) |
| Loose Scan | SEMIJOIN(LOOSESCAN) |
| Materialization | SEMIJOIN(MATERIALIZATION) |
| Table Pull-out | 없음 |
explain
select *
from departments d
where d.dept_no in (select /*+ SEMIJOIN(materialization) */ de.dept_no
from dept_emp de);
-- 힌트 소문자 안됨
SUBQUERY
SUBQUERY : 세미 조인 최적화가 사용되지 못할 때 사용하는 최적화 방법
=> 안티 세미 조인에 주로 사용
| 최적화 전략 | 힌트 |
|---|---|
| IN-to-EXISTS | SUBQUERY(INTOEXISTS) |
| Materialization | SUBQUERY(MATERIALIZATION) |
BNL & NO_BNL
BNL : 해시 조인을 사용하도록 유도
explain
select /*+ BNL(e, de) */ *
from employees e
inner join dept_emp de on de.emp_no=e.emp_no;JOIN_FIXED_ORDER & JOIN_ORDER & JOIN_PREFIX & JOIN_SUFFIX
JOIN_FIXED_ORDER: FROM절에 명시된 테이블 순서대로 조인JOIN_ORDER: 힌트에 명시된 순서대로 조인JOIN_PREFIX: 힌트에 명시된 테이블을 드라이빙 테이블로JOIN_SUFFIX: 힌트에 명시된 테이블을 드리븐 테이블로
explain
select /*+ join_suffix(de, e) */ *
from employees e inner join dept_emp de on de.emp_no=e.emp_no
inner join departments d on d.dept_no=de.dept_no;
MERGE & NO_MERGE
MERGE : from절에 사용된 서브쿼리를 내부 임시 테이블로 생성했는데(파생 테이블), 자원 소모를 유발하지 않도록 외부 쿼리와 서브 쿼리를 병합하도록 함
explain
select /*+ merge(sub)*/ *
from (select * from employees where first_name='Matt') sub
LIMIT 10;
INDEX_MERGE & NO_INDEX_MERGE
INDEX_MERGE : 하나의 테이블에 대해 여러 개의 인덱스를 동시에 사용
explain
select /*+ no_index_merge(employees PRIMARY) */ *
from employees
where first_name='Georgi' and emp_no between 10000 and 20000;
-- merge시
-- index_merge(employees ix_firstname, PRIMARY)
NO_ICP
인덱스 컨디션 푸시다운(ICP, Index Condition Pushdown) : 최대한 인덱스 컨디션 푸시다운을 사용하도록 하기 때문에 ICP 힌트는 제공 X
explain
select /*+ no_icp(employees ix_lastname_firstname) */ *
from employees
where last_name='Acton' and first_name like '%sal';
SKIP_SCAN & NO_SKIP_SCAN
SKIP_SCAN : 인덱스의 선행 칼럼에 대한 조건이 없어도 인덱스 사용 가능
explain
select /*+ no_skip_scan(employees ix_gender_birthdate) */ gender, birth_date
from employees
where birth_date>='1965-02-01';
INDEX & NO_INDEX
INDEX & NO_INDEX : 인덱스 힌트를 대체하는 용도
| 인덱스 힌트 | 옵티마이저 힌트 |
|---|---|
| USE INDEX | INDEX |
| USE INDEX FOR GROUP BY | GROUP_INDEX |
| USE INDEX FOR ODER BY | ORDER_INDEX |
| IGNORE INDEX | NO_INDEX |
| IGNORE INDEX FOR GROUP BY | NO_GROUP_INDEX |
| IGNORE INDEX FOR ODER BY | NO_ORDER_INDEX |
-- 인덱스 힌트
select *
from employees use index(ix_firstname)
where first_name='Matt';
--옵티마이저 힌트
select /*+ index(employees ix_firstname) */ *
from employees
where first_name='Matt';