MySQL 버전이 업그레이드되고 통계 정보나 옵티마이저의 최적화 방법들이 더 아양해지면서 쿼리의 실행 계획 최적화가 많이 성숙하고 있지만, 여전히 서비스 로직을 이하지는 못하기 때문에 개발자나 DBA보다 MySQL 서버가 부족한 실행 계획을 수립할 때가 있을 수 있다.

이런 경우에는 옵티마이저에게 쿼리의 실행 계획을 어떻게 수립해야 할지 알려줄 수 있는 방법이 필요하다. 일반적인 DBMS는 이러한 목적으로 힌트가 제공되며, MySQL에서도 다양한 옵티마이저 힌트를 제공한다.

• 인덱스 힌트
  • 이전 부터 사용되어오던 USE INDEX 같은 힌트
• 옵티마이저 힌트
  • MySQL 5.6 버전부터 새롭게 시작한 힌트 및 STRAIGHT_JOIN 등

인덱스 힌트

STRAIGHT_JOIN, USE_INDEX 등 을 포함한 인덱스 힌트들은 SELECT, UPDATE 같은 SQL 문법에 맞게 사용해야 하기 때문에 사용 시 ANSI-SQL 표준 문법을 준수하지 못하게 된다. 따라서 주석 처럼 사용되고, 다른 RDBMS 에서 주석으로 해석하게 되는 옵티마이저 힌트를 사용할 것을 추천한다.

STRAIGHT_JOIN

STRAIGHT_JOIN은 옵티마이저 힌트인 동시에 조인 키워드이기도 하다. STRAIGHT_JOIN은 SELECT, UPDATE, DELETE 쿼리에서 여러개의 테이블이 조인되는 경우 조인 순서를 고정하는 역할을 한다.

1
2
3
4
5
6

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

위 쿼리는 3개의 테이블을 조인하지만 옵티마이저의 판단에 의해 조인 순서가 결정된다. 일반적으로 조인을 하기 위한 컬럼들의 인덱스 여부로 조인의 순서가 결정되며, 조인 컬럼의 인덱스에 아무런 문제가 없는 경우에는 (WHERE 조건이 있는 경우 조건을 만족하는) 레코드가 적은 테이블을 드라이빙 테이블로 선택한다.

이러한 쿼리의 조인 순서를 변경하기 위해 STRAIGHT_JOIN 힌트를 사용할 수 있다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19

EXPLAIN
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
;

EXPLAIN
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
;

STRAIGHT_JOIN 힌트는 옵티마이저가 FROM 절에 면시된 테이블의 순서대로 조인을 수행하도록 유도한다. 주로 다음 기준에 맞게 조인 순서가 결정되지 않느 ㄴ경우에만 STRAIGHT_JOIN 힌트로 조인 순서를 조정하는 것이 좋다.

• 임시테이블과 일반 테이블의 조인
  • 이러한 경우는 일반적으로 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋다.
  • 일반 테이블의 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 작은 족을 먼저 읽도록 드라이빙으로 선택하는 것이 좋은데, 대부분 옵티마이저가 실행 계획을 제대로 수립하지 못해서 심각한 성능 저하가 있는 경우에는 힌트를 사용하면 된다.
• 임시 테이블끼리 조인
  • 임시 테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관하므로 크기가 작은 테이블을 드라이빙으로 선택해주는 것이 좋다.
• 일반 테이블끼리 조인
  • 양쪽 테이블 모두 조인 컬럼에 인덱스가 있거나 양쪽 테이블 모두 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택해주는 것이 좋다.
  • 그 이외의 경우에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙으로 선택하는 것이 좋다.

STRAIGHT_JOIN 힌트와 비슷한 역할을 하는 옵티마이저 힌트로는 다음과 같은 것들이 있다.

• JOIN_FIXED_ORDER
• JOIN_ORDER
• JOIN_PREFIX
• JOIN_SUFFIX

STRAIGTH_JOIN와 JOIN_FIXED_ORDER 힌트는 한번 사용되면 FROM절의 모든 테이블에 대해 조인 순서가 결정되며, 나머지 3개는 일부 테이블의 조인 순서에 대해서만 제안하는 힌트이다.

USE_INDEX, FORCE IDNEX, IGNORE INDEX

조인의 순서를 변경하는 것 다음으로 자시 사용되는 것이 인덱스 힌트인데, STRAIGHT_JOIN 힌트와는 달리 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시한다.

MySQL 옵티마이저는 어떤 인덱스를 사용해야 할지를 무난하게 잘 선택하는 편이지만, 3~4개 이상의 컬럼을 포함하는 비슷한 인덱스가 여러 개 존재하는 경우에는 실수를 하는데, 이런 경우에는 강제로 특정 인덱스를 사용하도록 힌트를 추가한다.

• USE_INDEX
  • 가장 자주 사용되는 힌트로, 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장한다.
  • 대부분 힌트를 채택하지만, 항상 사용하진 않는다.
• FORCE IDNEX
  • USE_INDEX와 기능적으로는 같지만 조근 더 강한 우선도를 부여한다.
  • USE_INDEX 힌트도 충분히 우선도가 높고, USE_INDEX를 사용했음에도 인덱스를 활용하지 않는다면 FORCE IDNEX를 사용해도 활용하지 않는 경우가 많다.
• IGNORE INDEX
  • 특정 인덱스를 사용하지 못하기 하는 힌트이다.
  • 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하기 위해 사용할 수 있다.

대체로 GROUP BY, ORDER BY 작업에서 인덱스를 사용할 수 있다면 나은 성능을 보장하며, 옵티마이저가 대부분 최적으로 선택하기 때문에 크게 고려하지 않아도 괜찮다.

1
2
3
4
5
6

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees USE INDEX(primary) WHERE emp_no=10001;

SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no=10001;
SELECT * FROM employees FORCE INDEX(ix_firstname) WHERE emp_no=10001;

1~3 쿼리는 인덱스 힌트가 주어지지 않아도 WHERE 조건을 통해 프라이머리 키를 이용하여 실행하는 것이 최적임을 옵티마이저가 판단하기 때문에 동일한 실행 계획으로 처리된다.

옵티마이저느 ㄴ프라이머리 키나 전문 검색 인덱스와 같은 인덱스에 대해서 선택 시 가중치를 두고 실행 계획을 수립하기 때문에 전문 검색(Full Text search) 인덱스가 있는 경우에는 다른 일반 보조 인덱스를 사용할 수 있는 상황이라고 하더라도 전문 검색 인덱스를 선택하는 경우가 많다.

MySQL의 옵티마이저가 쉬운 최적화는 매우 빠르게 처리하기 때문에 인덱스의 사용법이나 좋은 실행 계획이 어떤 것인지 판단하기 힘든 상황이라면 힌트를 사용해 강제로 옵티마이저의 실행 계획에 영향을 미치는 것은 피하는 것이 좋다.

최적의 실행 계획은 데이터의 성격에 따라서 시시각각 변하므로, 현재 프라이머리 키를 사용하는 것이 좋은 계획이었다고 하더라도 이후 바뀔 수 있으므로 옵티마이저가 당시 통계 정보를 가지고 선택하게 하는 것이 좋다.

가장 훌륭한 최적화는 그 쿼리를 서비스에서 없애버리거나 튜닝할 필요가 없게 데이터를 최소화하는 것이며, 그것이 어렵다면 데이터 모델의 단순화를 통해 쿼리를 간결하게 만들고 힌트가 필요하지 않게 만드는 것이다. 어떤 방법도 없다면 그 다음으로 힌트를 선택하는 것인데, 일반적으로 실무에서는 앞쪽의 작업들에 상당한 시간과 작업 능력이 필요하기 때문에 이런 힌트에 의존하는 경우가 많다.

SQL_CALC_FOUND_ROWS

MySQL의 LIMIT를 사용하는 경우, 조건을 만족하는 레코드가 명시된 수 보다 더 많다고 하더라도 지정한 수 만큼 찾으면 검색 작업을 멈추지만, 사용자에게는 LIMIT에 제한된 수만큼의 레코드를 반환됨에도 불구하고 끝까지 검색을 수행한다.

1
2
3
4
5

SELECT SQL_CALC_FOUND_ROWS * 
FROM employees 
LIMIT 5
;
SELECT FOUND_ROWS() AS total_record_count;

아마도 이 기능을 웹 프로그램의 페이징 기능에 적용하기 위해 검토했거나 이미 사용하고 있을지 모르지만 이 쿼리 힌트는 사용하지 않는 것이 좋다.

1
2
3
4
5
6
7

SELECT SQL_CALC_FOUND_ROWS * 
FROM employees
WHERE first_name='Georgi' 
LIMIT 0, 20
;

SELECT FOUND_ROWS() AS total_record_count;

이 경우에는 한 번의 쿼리 실행으로 필요한 정보 2가지를 모두 가져오는 것 처럼 보이지만 FOUND_ROWS() 함수의 실행을 위해 또 한번의 쿼리가 필요하기 때문에 쿼리를 2번 실행해야 한다.

위 쿼리는 first_name='Georgi' 조건을 처리하기 위해 employees 테이블의 ix_firstname 인덱스를 레인지 스캔으로 실제 값을 읽어온다. LIMIT를 통해 처음 20건만 가져오도록 했지만 SQL_CALC_FOUND_ROWS 힌트로 인해 실제 이 조건을 만족하는 레코드는 전체 253건을 전부 읽어야하며, 이로인해 ix_firstname 인덱스를 통해 실제 데이터 레코드를 찾아가는 작업으로 인해 랜덤 I/O가 253번 발생한다.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT COUNT(*) 
FROM employees
WHERE first_name='Georgi' 
;

SELECT SQL_CALC_FOUND_ROWS * 
FROM employees
WHERE first_name='Georgi' 
LIMIT 0, 20
;

위 방법 또한 쿼리를 2번 실행해야 하지만, 실제 레코드 데이터가 필요한 것이 아니라 건수만 가져오면 되는 커버링 인덱스 쿼리 이므로 실제로 데이터 레코드를 찾기 위한 랜덤 I/O는 발생하지 않는다. 두번째 쿼리는 LIMIT 제한으로 인해 랜덤 I/O가 20번만 발생하게 된다.

쿼리가 적절히 튜닝되지 않았거나 WHERE 조건에 대해서 적절한 인덱스가 준비되지 않았을 경우 SQL_CALC_FOUND_ROWS로 처리하는 것이 더 빠를 수 있지만 이러한 경우에는 쿼리나 인덱스를 튜닝하여 뒤에 설명한 방식으로 처리하는 것이 대체로 더 빠르다. 인덱스나 쿼리의 튜닝이 제대로 됐다면 뒤에 설명한 방식이 더 빠르게 실행될 것이므로 SQL_CALC_FOUND_ROWS 힌트는 사용하지 않는 것이 좋다.

또한 SELECT 문장이 UNION, UNION DISTINT으로 연결 된 경우에는 SQL_CALC_FOUND_ROWS 힌트를 사용해도 FOUND_ROWS 함수로 정확한 레코드 건수를 가져올 수 없다.

옵티마이저 힌트

MySQL 8.0에서 사용 가능한 힌트는 종류가 매우 다양하고 힌트가 미치는 영향 범위도 매우 다양하다.

옵티마이저 힌트 종류

옵티마이저 힌트는 영향 범위에 따라 4개 그룹으로 나눌 수 있다.

• 인덱스: 특정 인덱스의 이름을 사용할 수 있는 옵티마이저 힌트
• 테이블: 특정 테이블의 이름을 사용할 수 있는 옵티마이저 힌트
• 쿼리 블록: 특정 쿼리 블록에 사용할 수 있는 옵티마이저 힌트로서, 특정 쿼리 블록의 이름을 명시하는 것이 아닌 힌트가 명시된 쿼리 블록에 대해서만 영향을 미치는 옵티마이저 힌트
• 글로벌(쿼리 전체): 전체 쿼리에 영향을 미치는 힌트

이 구분으로 인해 힌트의 사용 위치가 달라지는 것은 아니며, 힌트에 인덱스 이름이 명시될 수 있는 경우 인덱스 수준의 힌트로 구분하고, 테이블 이름까지만 명시될 수 있는 경우를 테이블 수준의 힌트로 구분한다.

또한 특정 힌트는 테이블과 인덱스의 이름을 모두 명시할 수도 있지만 인덱스의 이름을 명시하지 않고 테이블 이름만 명시하는 경우 인덱스와 테이블 수준의 힌트가 된다.