7.9 SQL 힌트

August 5th, 2021

SQL은 데이터베이스로부터 어떤 데이터를 가져올지 기술하는 언어이지, 어떻게 데이터를 가져올지 기술하는 언어가 아니다.
실제로 데이터를 어떻게 가져올지를 결정하는 것은 MySQL의 옵티마이저이다. 물론 옵티마이저가 최적의 경로를 찾아 효율적인 방법으로 데이터를 가져오면 좋겠지만, 그렇지 못할 때가 많다. 그 때 SQL문장에 키워드를 지정해 MySQL 옵티마이저에게 어떻게 데이터를 읽는 것이 최적인지 알려줄 수 있으며, 이러한 키워드를 SQL 힌트라고 한다.

7.9.1 힌트의 사용법

MySQL에서 옵티마이저 힌트는 종류별로 사용 위치가 정해져 있다. 그리고 힌트를 표기하는 방법은 크게 두가지가 있다.

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

첫번째 방법은 SQL문장의 일부로 작성하는 방식이며, 두 번째 예제는 주석 표기 방법으로 힌트를 사용했다.

7.9.2 STRAIGHT_JOIN

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

쿼리에 따라 어떤 테이블이 드라이빙 테이블이 될지, 어떤 테이블이 드리븐 테이블이 될지에 대해서는 옵티마이저가 결정하지만, 이 키워드를 사용하면 개발자가 직접 지정을 할 수 있게 된다.

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;

위 두가지 쿼리는 작성한 방식은 다르지만, 동일하게 동작한다. 옵티마이저가 FROM절에 명시된 테이블의 순서대로 조인을 수행하도록 유도한다. 위 케이스의 경우 employees -> dept_emp -> departments 순으로 조인이 수행된다.


MySQL의 힌트는 다른 DBMS의 힌트에 비해 미치는 영향이 크다. 즉 MySQL은 힌트를 맹신하는 경향이 있다.

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.emp_no AND d.dept_no = de.dept_no;

위와 같이 쿼리를 실행하면 어떻게 될까? 이 경우에 employees와 departments가 조인되는 컬럼이 없기 때문에 풀조인이 일어난다. 확실히 옵티마이저가 잘못된 선택을 하지 않는다면 STRAIGHT_JOIN은 사용하지 않는 것이 좋다. 주로 아래 기준에 맞게 조인 순서가 결정되지 않을 때만 STRAIGHT_JOIN로 순서를 강제화 해주는 것이 좋다.

  • 임시테이블과 일반 테이블의 조인 : 임시 테이블을 드라이빙 테이블로 지정.
  • 임시 테이블 끼리의 조인 : 임시 테이블은 인덱스가 없으므로 어느 테이블을 먼저 드라이빙 테이블로 읽어도 무관하다. 크기가 작은 것을 드라이빙 테이블로 지정해주자.
  • 일반 테이블 끼리의 조인 : 양쪽 테이블 모두 조인 컬럼에 인덱스가 있거나, 양쪽 테이블 모두 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택하는 것이 좋다. 그 외에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙 테이블로 선택하자.

주의 위에서 언급한 레코드 건수는 전체 레코드 건수가 아니라, 조건에 만족하는 레코드 건수를 말한다.

그리고 InnoDB 스토리지 엔진을 사용하는 테이블에서는 가능하다면 보조 인덱스보다는 PK를 조인에 사용하는 것이 성능ㅇ ㅔ좋다.

7.9.3 USE INDEX / FORCE INDEX / IGNORE INDEX

가끔 4개 이상의 컬럼으로 생성된 인덱스가 있다. 이 때 똑같은 컬럼을 비슷한 순서 혹은 조합으로 포함한 인덱스가 여러개 있는 테이블에서는 MySQL 옵티마이저가 최적의 인덱스를 선택하지 못할 때가 있다.(2~3개 컬럼이 포함된 인덱스는 보통 잘 찾아준다) 이 때 USE INDEX, FORCE INDEX를 이용해 옵티마이저가 다른 인덱스를 사용하도록 유도할 수 있다.

  • USE INDEX : 가장 자주 사용되는 인덱스 힌트로, MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장한다.
  • FORCE INDEX : USE INDEX보다 옵티마이저에게 더 끼치는 영향이 강한 힌트다(USE INDEX와 비교해서 기능상 차이는 없다)
  • IGNORE INDEX : 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트다. 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하고 싶을 때 IGNORE INDEX를 사용하자.

또한, 위 세종류의 인덱스 힌트 모두 어떤 용도로 인덱스를 이용할지 명시할 수 있다. 용도는 선택사항이며, 기재하지 않을경우 주어진 인덱스를 아래 세가지 용도로 전부 사용한다 :

  • USE INDEX FOR JOIN : 테이블간의 조인 뿐만 아니라 검색하는 용도로도 사용된다.
  • USE INDEX FOR ORDER BY : 명시된 인덱스를 ORDER BY 하는 용도로만 사용하도록 제한한다.
  • USE INDEX FOR GROUP BY : 명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한한다.

이제 쿼리의 사용 케이스에 대해 알아보자 :

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;
  • 첫번째 쿼리부터 세번째 쿼리까지 모두 employees 테이블의 프라이머리 키를 이용해 쿼리를 처리할 것이다.
  • 네번째 쿼리는 일부러 인덱스를 사용하지 못하도록 힌트를 추가했다.
  • 다섯 번째 예제에서는 이 쿼리와 전혀 무관한 인덱스를 강제로 사용하도록 FORCE INDEX 힌트를 사용했다. PK키는 버리고 풀테이블 스캔을 하게 된다.

7.9.4 SQL_CACHE / SQL_NO_CACHE

MySQL은 SELECT 쿼리에 의해 만들어진 결과를 재사용하기 위해 쿼리 캐시에 선택적으로 저장해둔다. SELECT 쿼리의 결과를 캐시에 담아 둘지 여부를 쿼리에서 직접 선택할 수도 있는데, 이 때 사용하는 힌트들이라고 볼 수 있다.
query_cache_type라는 시스템 변수의 설정에 의해 기본적으로 쿼리의 결과를 쿼리 캐시에 저장할지 말지가 결정된다. query_cache_type의 설정 값과 쿼리 캐시 힌트의 사용 조합에 따라 캐싱여부가 최종적으로 결정되는데, 상세 여부는 아래를 통해 확인할 수 있다 :

  • 힌트 없음 :
    • query_cache_type 설정 값이 0 또는 OFF : 캐시하지 않음
    • query_cache_type 설정 값이 1 또는 ON : 캐시함
    • query_cache_type 설정 값이 2 또는 DEMAND : 캐시하지 않음
  • SQL_CACHE :
    • query_cache_type 설정 값이 0 또는 OFF : 캐시하지 않음
    • query_cache_type 설정 값이 1 또는 ON : 캐시함
    • query_cache_type 설정 값이 2 또는 DEMAND : 캐시함
  • SQL_NO_CACHE :
    • query_cache_type 설정 값이 0 또는 OFF : 캐시하지 않음
    • query_cache_type 설정 값이 1 또는 ON : 캐시하지 않음
    • query_cache_type 설정 값이 2 또는 DEMAND : 캐시하지 않음

만일 쿼리에 따라 쿼리 캐시가 선별적으로 작동하도록 세밀하게 조정하고 싶을 때에는 query_cache_type 설정 값을 2 또는 DEMAND로 셋팅하고, 쿼리에 SQL_CACHE 힌트를 사용하면 된다. 하지만 보통 query_cache_type 는 ON으로 셋팅되어 있어 SQL_NO_CACHE 힌트를 더 많이 사용한다. SQL_NO_CACHE는 쿼리 캐시로부터 결과를 가져오지 못하게 하는 것이 아니라, 쿼리 캐시에 저장하지 않게 하는 힌트다. 이 힌트는 SELECT 쿼리 문장에서만 사용할 수 있으며, SELECT 키워드 바로 뒤에 입력해야 한다.

7.9.5 SQL_CALC_FOUND_ROWS

SELECT 쿼리에 LIMIT절이 사용될 때, 조건을 만족하는 레코드가 LIMIT 절에 명시된 수보다 많다면 LIMIT에 명시된 건수만큼만 레코드를 찾고, 즉시 쿼리 수행을 멈춘다. 하지만 SQL_CALC_FOUND_ROWS를 사용하면 LIMIT여부와 관계없이 검색 조건에 일치하는 모든 레코드를 검색해서, 전체 조건에 일치하는 레코드가 몇 건이나 되는지 계산한다. 개발자의 편의를 위해 만들어진 힌트기 때문에, 쿼리 튜닝용으로는 사용하는 것을 가급적 자제하자.

7.9.6 기타 힌트

위에서 말한 힌트 외에도, SQL_BIG_RESULT, SQL_SMALL_RESULT, SQL_BUFFER_RESULT, HIGH_PRIORITY 와 같은 힌트도 있다. 하지만 이런 힌트들은 거의 사용되지 않기 때문에 참고만 해두도록 하자.

참고자료 : Real MySQL - 7.9 SQL 힌트

oksusutea's blog

꾸준히 기록하려고 만든 블로그