SQL 힌트(1)

SQL 힌트

SQL은 데이터베이스로부터 어떤 데이터를 가져올지 기술하는 언어이지, 어떻게 데이터를 가져올지를 기술하는 언어가 아니다. 실제 데이터를 어떻게 가져올지를 결정하는 것은 MySQL 서버의 옵티마이저다. 하지만 MySQL 서버의 옵티마이저는 아직 많은 한계점을 지니고 있으므로 최적의 방법으로 데이터를 읽지 못할 때가 많다. 이때 SQL 문장에 특별한 키워드를 지정해 MySQL 옵티마이저에게 어떻게 데이터를 읽는 것이 최적인지 알려줄 수 있다. 이러한 키워드를 SQL 힌트라고 한다.

아직 MySQL에서 사용할 수 있는 SQL 힌트는 그다지 많지 않은데, 그나마 실제로 쿼리의 성능 개선을 위해 자주 사용하는 것은 4~5개가 전부다. 하지만 이 힌트만으로 쿼리의 성능을 상당히 개선할 수 있다. 또한 MySQL에서는 힌트가 옵티마이저에 미치는 영향력이 다른 DBMS보다는 훨씬 크므로 힌트를 잘못 사용하면 오히려 성능이 더 떨어지기도 한다. 이번에는 중요하고 자주 사용되는 힌트 위주로 자세히 살펴보겠다.

1. 힌트의 사용법

MySQL에서 옵티마이저 힌트는 종류별로 사용 위치가 정해져 있다. 그리고 힌트를 표기하는 방법은 크게 두 가지 방법이 있는데, 이 두 가지 방법 모두 잘못 사용하면 오류가 발생한다. 오라클처럼 힌트가 주석의 일부로 해석되는 것이 아니라 SQL의 일부로 해석되기 때문이다. 힌트가 SQL 주석으로 사용됐더라도 마찬가지로 오류가 발생할 수 있다.

SELECT * FROM employees USE INDEX (PRIMARY) WHERE emp_no=10001;
SELECT * FROM employees /*! USE INDEX (PRIMARY) */ WHERE emp_no=10001;

첫 번째 예제는 별도의 주석 표시 없이 SQL 문장의 일부로 작성하는 방식이며, 두 번째 예제는 주석 표기 방법으로 힌트를 사용했다. 주석 표기 방식은 주석 시작 표시(/*) 뒤에 공백 없이 "!"를 사용해 SQL 힌트가 기술될 것임을 MySQL 서버에게 알려준다. 다른 DBMS에서 이 쿼리를 실행하면 힌트를 주석으로 처리하겠지만 MySQL에서는 여전히 SQL의 일부로 해석하며, 잘못 사용한 경우에는 에러가 발생하고 쿼리 실행이 종료된다.

2. STRAIGHT_JOIN

STRAIGHT_JOIN은 옵티마이저 힌트이기도 한 동시에 (JOIN UPDATE나 JOIN DELETE에서 본 것처럼) 조인 키워드이기도 하다. STRAIGHT_JOIN은 SELECT나 UPDATE, DELETE 쿼리에서 여러 개의 테이블에 조인될 때 조인의 순서를 고정하는 역할을 한다. 다음의 쿼리는 3개의 테이블을 조인하지만 어느 테이블이 드라이빙 테이블이 되고, 어느 테이블이 드리븐 테이블이 될지 알 수 없다. 옵티마이저가 그때그때 각 테이블의 통계 정보와 쿼리의 조건을 토대로 가장 최적이라고 판단되는 순서로 조인하게 된다.

SELECT *
FROM employees e, dept_emp de, departments d
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

이 쿼리의 실행 계획을 확인해 보면 다음과 같다. 먼저 departments 테이블을 드라이빙 테이블로 선택했고, 두 번째로 dept_emp 테이블을 읽고, 마지막으로 employees 테이블을 읽었음을 알 수 있다.

일반적으로 조인을 하기 위한 칼럼의 인덱스 여부로 조인의 순서가 결정되며, 조인 칼럼의 인덱스에 아무런 문제가 없을 때는 레코드가 적은 테이블을 드라이빙으로 선택한다. 이 쿼리는 departments 테이블이 레코드 건수가 가장 적어서 드라이빙으로 선택된 것이다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	d	index	dept_name	122		9	Using index
1	SIMPLE	de	ref	PRIMARY	12	d.dept_no	18436
1	SIMPLE	de	ref	PIRMARY	12	d.dept_no	18436
1	SIMPLE	e	eq_ref	PRIMARY	4	de.emp_no	1

그러면 이제 STRAIGHT_JOIN 힌트를 이용해 쿼리의 조인 순서를 변경해보자. 다음 두 쿼리는 힌트의 표기법만 조금 다르게 한 것일뿐 같은 쿼리다.

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 힌트는 옵티마이저가 FROM 절에 명시된 테이블의 조인은 순서대로 조인을 수행하도록 유도한다. 이 쿼리의 실행 계획을 보면 FROM 절에 명시된 employees -> dept_emp -> departments 테이블 순서로 조인이 수행됐다는 것을 확인할 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	e	ALL				300439
1	SIMPLE	d	eq_ref	PRIMARY	12	de.dept_no	1

여기서 FROM 절이란 INNER JOIN이나 LEFT JOIN까지 모두 포함하는 것이다. 즉, 다음 예제와 같이 SQL 표준 문법으로 조인을 수행하는 쿼리에서 STRAIGHT_JOIN 힌트는 위의 쿼리와 동일하게 employees -> dept_emp -> departments 순서로 조인을 실행하도록 유도한다.

SELECT /*! STRAIGHT_JOIN */ e.first_name, e.last_name, d.dept_name
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;

MySQL의 힌트는 다른 DBMS의 힌트에 비해 옵티마이저에 미치는 영향이 큰 편이다. 조금 과장하면 힌트가 있으면 옵티마이저는 힌트를 맹신하고 (힌트가 쿼리를 실행 불가능하도록 유도하지 않는다면) 그 힌트에 맞게 쿼리를 실행한다는 것이다. 다음 쿼리를 한번 살펴보자.

EXPLAIN
SELECT /*! STRAIGHT_JOIN */
  e.first_name, e.last_name, d.dept_name
FROM employees d, departments d, dept_emp de
WHERE e.emp_no=de.emp_no AND d.dept_no=de.dept_no;

이 쿼리는 employees 테이블을 드라이빙으로 선택하고 departments 테이블과 조인한 후 dept_emp 테이블을 조인한다. 그런데 employees 테이블과 departments 테이블은 직접적인 조인 조건이 없는데도 다음과 같이 주어진 힌트대로 쿼리를 실행하려고 한다는 것을 알 수 있다.

id	select_type	table	type	key	key_len	ref	rows	Extra
1	SIMPLE	e	ALL				300439
1	SIMPLE	d	Index	dept_name	122		9	Using index
1	SIMPLE	de	eq_ref	PRIMARY	16	d.dept_no, e.emp_no	1

이 예제의 실행 계획과 그 위의 실행 계획의 rows 칼럼에 표시된 값을 이용해 조인 순서가 변경됨으로써 처리해야 하는 레코드 건수가 얼마나 차이나는지 한번 비교해보자(이 예제는 단순히 테이블 3개를 조인만 하기 때문에 실행 계획의 rows 칼럼만 곱해 보면 된다.)

• employees -> dept_emp -> departments 순서대로 조인하는 경우는 300439(300439 * 1 *)건의 레코드를 처리한다.
• employees -> departments -> dept_emp 테이블 순서대로 조인하는 경우는 270395(300439 * 9 * 1)건의 레코드를 처리해야 한다.

힌트를 잘못 사용한다면 훨씬 더 느려지게 만들 수도 있다. 실제로 쿼리를 실행해 보면 대략 5~8배 정도의 성능 차이가 발생한다는 사실을 알 수 있다. 즉, 이 예제에서 STRAIGHT_JOIN 힌트는 훨씬 더 많은 레코드를 처리하게 하는 힌트이지만 MySQL 서버는 힌트의 순서대로 조인을 수행했다.

즉, 확실히 옵티마이저가 잘못된 선택을 하지 않는다면 STRAIGHT_JOIN 힌트는 사용하지 않는 것이 좋다. 주로 다음 기준에 맞게 조인 순서가 결정되지 않을 때만 STRAIGHT_JOIN 힌트로 조인 순서를 강제해 주는 것이 좋다.

임시 테이블(인라인 뷰 또는 파생된 테이블)과 일반 테이블의 조인

이때는 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다. 일반 테이블의 조인 칼럼에 인덱스가 없는 경우에는 레코드 건수가 적은 쪽을 드라이빙으로 선택해서 먼저 읽게 하는 것이 좋다. 대부분 MySQL 옵티마이저가 적절한 조인 순서를 결정하기 때문에 옵티마이저가 반대로 실행 계획을 수립하는 경우에만 힌트를 사용하자.

임시 테이블끼리의 조인

임시 테이블(서브 쿼리로 파생된 테이블)은 인덱스가 없으므로 어느 테이블을 먼저 드라이빙으로 읽어도 무관하다. 일반적으로 크기가 작은 테이블을 드라이빙으로 선택하는 것이 좋다.

일반 테이블끼리의 조인

양쪽 테이블 모두 조인 칼럼에 인덱스가 있거나 양쪽 테이블 모두 조인 칼럼에 인덱스가 없는 경우에도 레코드 건수가 적은 테이블을 드라이빙으로 선택하는 것이 좋다. 그 밖의 경우에는 조인 칼럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.

여기서 언급한 레코드 건수라는 것은 조건을 만족하는 레코드 건수를 의미하는 것이지, 무조건 테이블 전체의 레코드 건수를 의미하지는 않는다. 다음 예제는 employees 테이블의 건수가 훨씬 많지만 조건을 만족하는 employees 테이블의 레코드 건수가 적기 때문에 employees가 드라이빙 테이블이 되는 것이 좋다.

SELECT /*! STRAIGHT_JOIN */
  e.first_name, e.last_name, d.dept_name
FROM employees e, departments d, dept_emp de
WHERE e.emp_no=de.demp_no AND d.dept_no=de.dept_no AND e.emp_no=10001;

InnoDB 스토리지 엔진을 사용하는 테이블에서는 가능하다면 보조 인덱스보다는 프라이머리 키(프라이머리 키는 클러스터링 키이므로)를 조인에 사용할 수 있게 해준다면 훨씬 더 빠른 수행 결과를 가져올 수 있다.

테이블의 데이터는 계속 변화하기 때문에 어제의 최적의 실행 계획이 오늘도 최적이라고 보장할 수는 없다. 그러므로 위의 3가지 중에서도 첫 번째를 제외한 나머지 케이스에서는 실행 계획이 조금 부적절하게 수립되는 쿼리라 하더라도 조인 순서를 옵티마이저가 결정하게 해주는 것이 좋다.

참고

• Real MySQL