MySQL 서버로 요청된 쿼리는 결과는 동일하지만 결과를 만들어내는 방법은 다양한데, 다양한 방법 중 어떤 방법이 최적인지 실행 계획을 수립하는 작업을 담당하는 것을 “옵티마이저”라 한다.
EXPLAIN이라는 명령을 통해 쿼리의 실행 계획을 확인할 수 있다.
WHERE
절이나 ON
절에 인덱스를 이용할 수 있는 조건이 없는 경우정렬을 처리하는 방법은 인덱스를 이용하는 방법과 쿼리가 실행될 때 “Filesort”라는 별도의 처리를 이용하는 방법으로 나눌 수 있다.
장점 | 단점 | |
---|---|---|
인덱스 이용 | 이미 인덱스가 정렬되어 있어서 순서대로 읽기만 하면 되므로 매우 빠르다. | 쓰기 작업 시 부가적인 인덱스 추가/삭제 작업이 필요하므로 느리다. 디스크 공간이 더 많이 필요하다. 인덱스의 개수가 늘어날수록 버퍼 풀을 위한 메모리가 많이 필요하다. |
Filesort 이용 | 인덱스를 이용할 때의 단점이 없어진다. 정렬할 레코드가 많지 않으면 메모리에서 Filesort가 처리되므로 충분히 빠르다. | 정렬 작업이 쿼리 실행 시 처리되므로 레코드 건수가 많아질수록 쿼리의 응답 속도가 느리다. |
소트 버퍼는 정렬이 필요한 경우에만 할당되며, 버퍼의 크기는 정렬할 레코드 크기에 따라 증가한다.
만약 소트 버퍼로 할당된 공간보다 정렬할 레코드 건수가 크다면 아래와 같은 방식으로 처리한다.
1. 정렬할 레코드를 여러 조각으로 나눠서 처리하며 결과를 디스크에 임시 저장한다.
2. 모든 레코드를 정렬했으면, 임시 저장한 결과들을 병합한다. (이 작업을 멀티 머지 라고 한다.)
이 작업들은 모두 디스크 I/O를 유발하며, 레코드 건수가 많을수록 반복 횟수가 많아진다.
![image](https://github.com/JeongHunHui/TIL/assets/108508730/159b6121-97a6-4697-bbd0-145276385357)
참고로, 위 그림에서 볼 수 있듯 소트 버퍼의 사이즈가 커진다고 성능이 무조건 빨라지는 것은 아니다.
대신, 디스크 I/O는 줄일 수 있으므로 디스크 I/O 성능이 낮은 장비라면 소트 버퍼의 크기를 더 크게 설정하는 것이 좋을 수 있다.
하지만, 소트 버퍼를 너무 크게 설정하면 서버의 메모리가 부족해져서 적절한 것이 좋다.
대량 데이터 정렬이 필요한 경우 해당 세션의 소트 버퍼만 일시적으로 늘려서 쿼리를 실행하고 다시 줄이는 것도 좋다.
SELECT emp_no, first_name, last_name
FROM employees
ORDER BY first_name;
위 쿼리를 싱글 패스 방식으로 처리하는 절차는 아래와 같다.소트 버퍼 크기 만큼 SELECT절의 컬럼들(위 예시에선 emp_no
, first_name
, last_name
)을 읽어와서 정렬 후 임시 저장한다.
위 과정을 반복한 뒤 멀티 머지하여 쿼리의 결과를 반환한다.
싱글 패스 방식은 불필요한 데이터 까지 읽어오므로 많은 소트 버퍼 공간이 필요하다.
레코드의 크기가 max_length_for_sort_data 시스템 변수에 설정된 값보다 클 때
BLOB이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될 때
싱글 패스 방식은 정렬 대상 레코드의 크기나 건수가 작은 경우 빠르고, 투 패스 방식은 정렬 대상 레코드의 크기나 건수가 상당히 많은 경우 효율적이다.
→ SELECT 쿼리에서 꼭 필요한 컬럼만 조회하지 않고 모든 컬럼을 가져오도록 개발하게 되면 정렬 버퍼를 비효율적으로 사용하게된다. 또한, 임시 테이블이 필요한 쿼리에서도 영향을 미친다.
→ 불필요한 컬럼을 SELECT하지 않게 쿼리를 작성하자.
정렬 처리 방법 | 실행 계획의 Extra 내용 | 속도 |
---|---|---|
인덱스를 이용한 정렬 | 별도 표기 없음 | 빠름 |
조인에서 드라이빙 테이블만 정렬 | “Using filesort” | 보통 |
조인에서 조인 결과를 임시 테이블로 저장 후 정렬 | “Using temporary; Using filesort” | 느림 |
인덱스를 이용한 정렬
ORDER BY 절에 명시된 컬럼이 제일 먼저 읽는 테이블(조인이 사용된 경우 드라이빙 테이블)에 속하고, ORDER BY의 순서대로 생성된 인덱스가 있어야 한다.
또한, WHERE절에 첫 번째로 읽는 테이블이 컬럼에 대한 조건이 있다면 그 조건과 ORDER BY는 같은 인덱스를 사용할 수 있어야 한다.
SELECT *
FROM users
WHERE name LIKE 'a%' and age >= 20
ORDER BY name, age;
// 복합 인덱스 name_age_idx(name, age)가 있는 상태
위 쿼리는 ORDER BY 절에 명시된 컬럼이 users 테이블에 속하고, 순서대로 생성된 인덱스가 있고, WHERE 절에서도 같은 인덱스를 사용할 수 있으므로 인덱스를 이용해 정렬할 수 있다.
여러 테이블이 조인되는 경우에는 Nested-loop 방식의 조인에서만 이 방식을 사용할 수 있다.
참고로, 위 경우 ORDER BY 절을 넣지 않아도 정렬되지만, ORDER BY 절을 넣는다고 불필요한 정렬을 한 번 더 하지 않고, 실행계획이 변경되면 버그로 이어질 수 있으므로 넣는 것이 좋다.
조인에서 드라이빙 테이블만 정렬
인덱스를 이용한 정렬이 불가능하다면 조인을 실행하기 전에 첫 번째로 읽히는 테이블(드라이빙 테이블)을 먼저 정렬한 다음 조인을 실행하는 것이 차선책이 될 것이다.
이 방법으로 정렬이 처리되려면 조인의 드라이빙 테이블의 컬럼만으로 ORDER BY 절을 작성해야 한다.
SELECT *
FROM users, posts
WHERE users.id = posts.user_id
AND users.id BETWEEN 10000 AND 10010
ORDER BY users.name;
일단 위 쿼리에서는 아래 이유로 옵티마이저가 users
테이블을 드라이빙 테이블로 선택할 것이다.
WHERE
절의 검색 조건 users.id BETWEEN 10000 AND 10010
은 users
테이블의 PK를 이용하면 작업량을 줄일 수 있다.posts
)의 조인 컬럼인 users.id
에 인덱스가 있다.ORDER BY 절에 명시된 컬럼으로 생성된 인덱스가 없으므로 인덱스를 이용할 수 없다.
하지만, ORDER BY 절에 명시된 컬럼들이 드라이빙 테이블에 속해 있으므로 드라이빙 테이블만 먼저 정렬한 뒤 posts 테이블과 조인하는 식으로 처리할 수 있다.
즉, 아래와 같은 과정으로 실행된다.
users.id BETWEEN 10000 AND 10010
를 만족하는 레코드를 검색name
컬럼으로 정렬(Filesort)posts
테이블과 조인을 수행해 결과를 가져옴임시 테이블을 이용한 정렬
2번과 같은 패턴을 제외한 쿼리에서는 항상 조인의 결과를 임시 테이블에 저장하고, 그 결과를 다시 정렬하는 과정을 거친다.
이 방법은 다른 방법보다 정렬할 레코드 건수가 가장 많기 때문에 가장 느리다.
```sql
SELECT *
FROM users, posts
WHERE users.id = posts.user_id
AND users.id BETWEEN 10000 AND 10010
ORDER BY posts.created_at;
```
위 쿼리는 2번 방법의 예시 쿼리에서 ORDER BY 절의 컬럼만 바꾼 쿼리이다.
위 쿼리는 정렬 기준 컬럼이 드리븐 테이블에 있으므로 조인을 한 뒤에 정렬해야한다.
위 3가지 정렬 방법을 아래 쿼리를 기준으로 비교해보자.
SELECT *
FROM tb_test1 t1, tb_test2 t2
WHERE t1.c1=t2.c1
ORDER BY t1.c2
LIMIT 10;
// t1의 레코드는 100건, t2의 레코드는 1000건
→ 어떤 테이블이 먼저 드라이빙 되어 조인되는지, 어떤 정렬 방식으로 처리되는지에 따라서 큰 성능차이가 발생한다.
ORDER BY
나 GROUP BY
작업은 LIMIT
가 있어도 정렬이나 그루핑 작업 후에 건수를 제한할 수 있다. → ORDER BY
나 GROUP BY
을 잘못 활용하면 쿼리가 느려진다. 인덱스를 사용하지 못하는 정렬이나 그루핑 작업이 왜 느리게 작동할 수밖에 없는지 알기 위해 쿼리가 처리되는 방법을 두 가지로 나눠서 알아보자.스트리밍 방식
조건에 일치하는 레코드가 검색될 때마다 바로바로 클라이언트로 전송해주는 방식을 의미한다.
위 방식으로 처리되는 쿼리에서 LIMIT처럼 결과 건수를 제한하는 조건들은 쿼리의 실행 시간을 상당히 줄여줄 수 있다.
버퍼링 방식
ORDER BY
나 GROUP BY
같은 처리는 쿼리의 결과가 스트리밍되는 것을 불가능 하게 한다.
왜냐하면 조건에 맞는 모든 레코드를 가져온 뒤 정렬하거나 그루핑해서 보내야 하기 때문이다.
MySQL 서버에서는 모든 레코드를 검색하고 정렬 작업을 하는 동안 클라이언트는 아무것도 하지 않고 기다려야 하기 때문에 응답 속도가 느려진다.
그렇기 때문에 버퍼링 방식으로 처리되는 쿼리에선 `LIMIT` 처럼 결과 건수를 제한하는 조건이 있어도 성능 향상에 큰 도움이 되지 않는다.
![image](https://github.com/JeongHunHui/TIL/assets/108508730/39402839-de66-4e00-b898-561ee651f4bd)
인덱스를 사용하지 못하고 Filesort 작업을 거쳐야 하는 쿼리에서 LIMIT 조건을 걸면 모든 레코드를 정렬하는 것이 아니고, 정렬하다가 LIMIT 조건 만큼의 레코드가 정렬되면 바로 결과를 반환한다.
하지만 MySQL은 정렬을 위해 퀵 소트와 힙 소트 알고리즘을 사용하는데, 즉 상위 10건의 데이터를 얻기 위해 대부분의 데이터를 살펴야할 수도 있다.
→ 인덱스를 사용하지 못하는 쿼리에 LIMIT 조건을 붙혀도 쿼리가 생각보단 많이 빨라지지 않는다.
FLUSH STATUS;
SHOW STATUS LIKE 'Sort%';
GROUP BY
또한 ORDER BY
와 같이 스트리밍 처리를 할 수 없다.
참고로 GROUP BY
에 사용된 조건(HAVING
절)은 인덱스를 사용해서 처리될 수 없다.
GROUP BY
도 인덱스를 사용하거나 못할 수 있다.
GROUP BY
컬럼으로 이미 인덱스가 있다면 그 인덱스를 읽으며 그루핑 작업을 수행한다. 인덱스를 사용해서 처리하더라도 그룹 함수 등 그룹값을 처리해야 해서 임시 테이블이 필요할 때도 있다. 이러한 그루핑 방식을 사용하는 쿼리의 실행 계획의 Extra 컬럼에는 별도의 코멘트가 표시되지 않는다.// 인덱스는 (emp_no, from_date) 이렇게 생성된 상태
SELECT emp_no
FROM salaries
WHERE from_date='1985-03-01'
GROUP BY emp_no;
위 쿼리의 WHERE
절의 from_date
는 복합 인덱스의 첫 컬럼이 아니므로 인덱스를 사용할 수 없다. 하지만 위 쿼리의 실행 계획을 보면 인덱스 레인지 스캔을 사용했고, GROUP BY
처리도 인덱스를 사용했다. 위 쿼리는 아래와 같은 과정으로 실행된다.(emp_no, from_date)
인덱스를 스캔하며 emp_no
의 첫 번째 유일한 값 “10001” 을 찾는다.
인덱스에서 emp_no
가 “10001”인 것 중 from_date
값이 “1985-03-01”인 레코드를 찾는다.
다시 emp_no
의 유일한 값을 찾은 뒤 2번을 반복하고 더 이상 유일한 값이 없으면 처리를 종료한다.
즉 인덱스의 첫 번째 컬럼의 유니크한 값들 별로 WHERE
조건에 해당하는 레코드를 검색하는 것이다.
이 방식에선 인덱스의 유니크한 값이 적을수록 성능이 향상된다.
GROUP BY
의 기준 컬럼이 인덱스를 전혀 사용하지 못할 때는 이 방식으로 처리된다. MySQL 8.0에선 GROUP BY
가 필요한 경우 내부적으로 GROUP BY
절의 컬럼들로 구성된 유니크 인덱스를 가진 임시 테이블을 만들어서 중복 제거와 집합 함수 연산을 수행한다.특정 컬럼의 유니크한 값만 조회하기 위해 SELECT
쿼리에 DISTINCT
를 사용한다.
SELECT DISTINCT first_name, last_name FROM employees;
즉, 위 쿼리는 성이 같지만 이름이 다른 경우를 유니크한 값으로 판단하여 결과에 포함한다.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(DISRINCT s.salary)
를 처리하기 위해 임시 테이블을 사용한다. 이때 임시 테이블의 salary
컬럼엔 유니크 인덱스가 생성되기 때문에 레코드 수가 많아지면 느릴 수 있다. 하지만 인덱스된 컬럼에 대해 DISTINCT
처리를 할 땐 인덱스를 이용하며 임시 테이블 없이 처리할 수 있다.MySQL 엔진이 스토리지 엔진으로부터 받은 레코드를 정렬, 그루핑할 때는 내부적인 임시 테이블을 사용한다.
이는 CREATE TEMPORARY TABLE
명령으로 만든 임시 테이블과는 다르다.
내부 임시 테이블은 쿼리의 처리가 완료되면 자동으로 삭제된다.
Using temporary
가 표시되는지 확인하면 된다. 대표적으로 아래와 같은 경우에 내부 임시 테이블을 생성한다.ORDER BY
와 GROUP BY
에 명시된 컬럼이 다른 쿼리
ORDER BY
와 GROUP BY
에 명시된 컬럼이 드라이빙 테이블이 아닌 쿼리
DISTINCT
와 ORDER BY
가 동시에 존재하거나 DISTINCT
가 인덱스로 처리되지 못하는 쿼리
UNION
이나 UNION DISTINCT
가 사용된 쿼리
쿼리의 실행 계획에서 select_type
이 DERIVED
인 쿼리
위 경우 중 마지막 쿼리 패턴을 제외하고 전부 유니크 인덱스를 가지는 내부 임시 테이블이 만들어진다.
일반적으로 유니크 인덱스가 있는 내부 임시 테이블은 처리 성능이 느린편이다.
옵티마이저는 최적의 실행 계획을 수립하기 위해 통계 정보와 옵티마이저 옵션을 결합하여 이용한다.
옵티마이저 옵션은 크게 조인 관련 옵티마이저 옵션과 옵티마이저 스위치로 구분된다.
옵티마이저 스위치 옵션은 optimizer_switch
시스템 변수를 이용하여 제어하는데, 여러 옵션을 세트로 묶어 설정하는 방식으로 사용한다.
옵티마이저 스위치 옵션은 optimizer_switch
시스템 변수를 이용하여 제어하는데 옵션들은 아래와 같다.
옵티마이저 스위치 이름 | 기본값 | 설명 |
---|---|---|
batched_key_access | off | BKA 조인 알고리즘 사용 여부 |
block_nested_loop | on | Block Nested Loop 조인 알고리즘 사용 여부 |
engine_condition_pushdown | on | Engine Condition Pushdown 기능 사용 여부 |
index_condition_pushdown | on | Index Condition Pushdown 기능 사용 여부 |
use_index_extensions | on | Index Extension 최적화 사용 여부 |
index_merge | on | Index Merge 최적화 사용 여부 |
index_merge_intersection | on | Index Merge Intersection 최적화 사용 여부 |
index_merge_sort_union | on | Index Merge Sort Union 최적화 사용 여부 |
index_merge_union | on | Index Merge Union 최적화 사용 여부 |
mrr | on | MRR 최적화 사용 여부 |
mrr_cost_based | on | 비용 기반 MRR 최적화 사용 여부 |
semijoin | on | 세미 조인 최적화 사용 여부 |
firstmatch | on | FirstMatch 세미 조인 최적화 사용 여부 |
loosescan | on | LooseScan 세미 조인 최적화 사용 여부 |
materialization | on | Materialization 최적화 사용 여부 |
subquery_materialization_cost_based | on | 비용 기반 Materialization 최적화 사용 여부 |
위 옵션은 글로벌, 현재 커넥션, 현재 쿼리에 대해서 등 다양한 범위로 설정할 수 있다.
SELECT *
FROM employees e
INNER JOIN salaries s
ON s.emp_no=e.emp_no;
위 쿼리는 employees
테이블이 드라이빙 테이블이 되어 순서대로 레코드를 한 건 읽고, 드리븐 테이블인 salaries 에서 조건에 만족하는 레코드를 찾아서 바로 반환한다. 이를 의사 코드로 표현하면 아래와 같다.for(row1 IN employees) {
for(row2 IN salaries) {
if(row1.emp_no == row2.emp_no) return (row1, row2);
}
}
위 코드에서 알 수 있듯이 레코드를 읽어서 다른 버퍼 공간에 저장하지 않고 즉시 드리븐 테이블의 레코드를 찾아 반환한다.WHERE
절의 조건 중 일부를 스토리지 엔진 레벨에서 평가하도록 "푸시 다운"하여, 필요하지 않은 레코드를 더 빠르게 걸러내는데 도움을 준다.장점
단점
- 특정 쿼리나 데이터셋에서는 성능 저하의 원인이 될 수 있다.
- InnoDB, MEMORY 스토리지 엔진에서만 사용된다.
→ 대부분의 경우에선 활성화 하는 것이 좋다.
use_index_extensions
옵션은 세컨더리 인덱스에 추가된 PK를 활용여부를 결정하는 옵션이다.AND
연산자로 연결할 경우 실행된다. 실행 계획의 Extra 컬럼에 Using intersect(idx_1, PRIMARY)
라는 메시지가 있다면 idx_1
와 PK로 검색한 두 결과를 교집합했다는 뜻이다. 만약 1개의 인덱스를 사용하는 것이 더 효율적이면 index_merge_intersection
최적화를 비활성화 하자.OR
연산자로 연결할 경우 실행된다.// idx_1(first_name), idx_2(hire_date) 두 세컨더리 인덱스가 생성되어 있다.
SELECT * FROM employees WHERE first_name='Matt' OR hire_date='1987-03-31';
위와 같은 쿼리의 실행 계획에 Using union(idx_1, idx_2
이러한 메시지가 나왔다면 두 인덱스로 검색한 값을 합집합 했다는 것이다. 이때, 합집합 연산을 진행하며 중복되는 데이터가 있을 수 있는데, first_name='Matt'
인 레코드와 hire_date='1987-03-31'
인 레코드는 인덱스 스캔을 하게되면 PK로 정렬되어 있으므로 두 결과를 우선 순위 큐 알고리즘을 통해 합치면서 중복을 제거한다.AND
로 연결된 경우에는 두 조건 중 하나라도 인덱스를 사용할 수 있으면 인덱스 레인지 스캔으로 쿼리가 실행되지만 OR
로 연결된 경우에는 둘 중 하나라도 인덱스를 사용하지 못하면 풀 테이블 스캔으로 처리된다.BETWEEN
같은 경우는 정렬이 필요하다. 이렇게 합집합 연산 전에 정렬을 해야하는 경우 Extra 컬럼에 Using sort_union
문구가 표시된다.SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT de.emp_no FROM dept_emp de WHERE de.from_date='1995-01-01');
SELECT *
FROM employees e
WHERE e.emp_no IN
(SELECT de.emp_no FROM dept_emp de WHERE de.dept_no='d009');
위 쿼리에 테이블 풀 아웃 최적화를 적용하면 아래와 같은 쿼리로 바뀐다.SELECT e.*
FROM dept_emp de
JOIN employees e
WHERE e.emp_no=de.emp_no AND de.dept_no='d009';
즉, 기존 서브쿼리를 조인으로 풀어서 사용한 형태이다. 테이블 풀 아웃 최적화는 서브쿼리 부분에서 유니크 인덱스나 PK로 검색하여 결과가 1건인 경우에만 사용가능하다. 또한, 테이블 풀 아웃은 다른 최적화와 함께 적용될 수 있으므로 가능하면 최대한 적용한다.IN(subquery)
형태의 세미 조인을 EXISTS(subquery)
형태로 튜닝한 것과 비슷한 방법으로 실행된다.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 '1995-01-30'
);
위 쿼리는 first_name='Matt'
인 레코드들을 찾고 그 결과와 titles
테이블을 조인하는 식으로 실행된다. 조인하는 과정에서 e.emp_no
가 1이고, e.first_name
이 ‘Matt’인 레코드와 e.emp_no
가 3이고, e.first_name
이 ‘Matt’인 레코드를 찾은 상태라고 하자. t.emp_no
가 1인 레코드들 중 t.from_date BETWEEN '1995-01-01' AND '1995-01-30'
인 레코드를 하나만 발견하면 더이상 emp_no
가 1인 경우에 대해서는 탐색하지 않는다. 그런 뒤 emp_no
가 2인 레코드에 대해서도 동일한 작업을 시행한다.// departments 테이블의 레코드는 9건, dept_emp 테이블의 레코드는 33만건
SELECT *
FROM departments d
WHERE d.dept_no IN (
SELECT de.dept_no FROM dept_emp de);
위 쿼리는 dept_emp 테이블에 존재하는 모든 부서 번호에 대해 부서 정보를 읽어 오는 쿼리다. dept_emp
테이블은 dept_no
를 기준으로 그루핑하면 9건 밖에 없다. → dept_emp
테이블을 루스 인덱스 스캔으로 유니크한 dept_no
만 읽으면 효율적 실행이 가능하다.GROUP BY
나 집합 함수가 사용되어도 사용 가능하다.INNER JOIN
쿼리로 바꿔서 실행하고 중복된 레코드를 제거하는 방법으로 처리되는 최적화 알고리즘이다. 보통 이 방법으로 최적화되는 경우는 다른 최적화 방법이 많이 있다.condition_fanout_filter
최적화를 활성화하면 옵티마이저가 조건을 충족하는 레코드 수를 더 정확히 예측할 수 있다. 조인 시 테이블의 순서는 쿼리의 성능에 매우 큰 영향을 미치는데, 예상 레코드 수가 정확해 지면 옵티마이저가 더 효율적인 순서로 조인이 실행되도록 계획을 세울 수 있다. 대신 condition_fanout_filter
최적화를 활성화하면 계산을 위해 더 많은 자원을 사용한다. 그러므로 쿼리의 실행 계획이 잘못된적이 별로 없다면 별로 도움이 되지 않는다.FROM
절에 사용된 서브쿼리는 먼저 실행해서 임시 테이블로 만드는 식으로 처리했다. 이 실행 계획은 임시 테이블을 생성하고 조건에 맞는 레코드를 읽어서 임시 테이블에 INSERT
한다. 그리고 다시 임시 테이블을 읽으므로 오버헤드가 추가된다. 만약 임시 테이블의 크기가 메모리에 못 들어갈 정도로 커지면 성능은 많이 느려질 것이다. derived_merge
최적화 옵션은 파생 테이블로 만들어지는 서브쿼리를 외부 쿼리와 병합하는 식으로 최적화한다.use_invisible_indexes
옵티마이저 옵션을 이용하면 INVISIBLE
로 설정된 인덱스도 사용할 수 있다.빌드 단계: 조인 대상 테이블 중 레코드 건수가 적은 테이블을 메모리에 해시 테이블을 생성한다.
프로브 단계: 나머지 테이블의 레코드를 읽어서 해시 테이블의 일치 레코드를 찾는다.
보통 네스티드 루프 조인을 사용할 수 없는 경우 해시 조인이 사용된다.
SELECT *
FROM employees
WHERE hire_date BETWEEN '1985-01-01' AND '1985-02-01'
ORDER BY emp_no;
위 쿼리는 대표적으로 아래 2가지 실행 계획을 선택할 수 있다.hire_date
컬럼으로 생성된 인덱스를 이용해 조건에 맞는 레코드를 찾고 emp_no
로 정렬
PK를 정순으로 읽으며 조건에 맞는 레코드를 찾아서 반환
일반적으로는 hire_date
컬럼의 조건에 부합되는 레코드 건수가 많지 않다면 1번이 효율적일 것이다.
하지만, 옵티마이저가 ORDER BY
절의 인덱스에 가중치를 너무 부여하여 2번이 선택될 수 있다.
이렇게 자주 실수를 한다면 prefer_ordering_index
옵션을 OFF로 변경하자.
Exhaustive 검색 알고리즘, Greedy 검색 알고리즘 두 가지가 있다.
조인 테이블 개수가 많아지면 실행 계획을 수립하는 데만 많은 시간이 걸린다.
MySQL에서 사용 가능한 쿼리 힌트는 인덱스 힌트, 옵티마이저 힌트 두 가지로 구분할 수 있다.
쿼리 힌트는 옵티마이저에게 올바른 방향으로 실행 계획을 수립할 수 있도록 알려주는 역할을 한다.
인덱스 힌트는 SELECT
, UPDATE
문에서만 사용할 수 있고, 가능하면 옵티마이저 힌트를 사용할 것을 추천한다.
STRAIGHT_JOIN
은 SELECT STRAIGHT_JOIN ~
이러한 형태로 쓰이고, FROM
절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다.SELECT *
FROM table_1 USE INDEX [FOR ORDER BY/GROUP BY/JOIN](idx_1)
~ INDEX
힌트를 사용하기 위해선 위 쿼리와 같이 인덱스를 가지는 테이블 뒤에 힌트를 명시해야한다. FOR ORDER BY/GROUP BY/JOIN
를 붙혀 인덱스의 용도를 제한할 수 있다. USE
는 인덱스 사용 권장, FORCE
는 더 강하게 사용 권장(잘 안씀), IGNORE
는 인덱스를 못 사용하게 한다.옵티마이저 힌트는 영향 범위에 따라 4가지로 나눌 수 있다.
참고로 모든 인덱스 수준의 힌트는 아래와 같이 테이블명이 선행되어야 한다.
SELECT /*+ INDEX(employees ix_firstname) */ *
FROM employees
WHERE first_name='Matt';
SEMIJOIN(세미_조인_최적화_전략_이름)
힌트는 어떤 세미 조인 최적화 전략을 사용할지를 제어할 수 있다. 만약 세미 조인을 사용하고 싶지 않다면 NO_SEMIJOIN
힌트를 사용하면된다.INTOEXISTS
와 MATERIALIZATION
두 가지 방법이 있다.BNL(테이블1, 테이블2)
이렇게 힌트를 작성하고, 사용하지 않게 한다면 NO_BNL
힌트를 사용하면 된다.JOIN_FIXED_ORDER()
: FROM
절의 테이블 순서대로 조인을 실행JOIN_ORDER(tb1,tb2, ...)
: 힌트에 명시된 테이블 순서대로 조인을 실행JOIN_PREFIX(tb1)
: 드라이빙 테이블을 설정JOIN_SUFFIX(tb1, tb2, …)
: 드리븐 테이블을 설정MERGE(서브쿼리_이름)
: 임시 테이블을 사용하지 않게 서브 쿼리를 외부 쿼리와 병합 NO_MERGE(서브쿼리_이름)
: 임시 테이블을 사용하도록 강제NO_ICP
힌트로 ICP를 비활성화할 수 있다.