4.4 옵티마이저 힌트

힌트의 사용법

MariaDB에서 옵티마이저 힌트는 종류별로 그 사용 위치가 정해져 있는데, 두가지 방법이 있습니다. 이 두가지 방법모두 잘못 사용할 경우 오류를 발생시키게 됩니다. 오라클 처럼 힌트가 주석의 일부로 해석되는 것이 아니라 SQL의 일부로 해석(설령 힌트가 주석안에 표기되었다 하더라도)되기 때문입니다.

사용법1

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

 

사용법2

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

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

 

옵티마이저 힌트와는 조금 거리가 있을 수도 있는데, 아래와 같은 표기법도 있습니다.

CREATE /*!32302 TEMPORARY */ TABLE temp_emp_stat (hire_year INT NOT NULL,emp_count INT,PRIMARY KEY(hire_date));

 

CREATE TEMPORARY  TABLE temp_emp_stat (hire_year INT NOT NULL,emp_count INT,PRIMARY KEY(hire_date));

위 문장은 옵티마이저 힌트와 동일한 표기법으로 작성되었습니다. 사실 여기에 사용된 TEMPORARY 키워드는 힌트가 아니라 SQL의 일부이며 주석시작 마크 뒤어 붙여진 숫자는 MariaDB의 버전을 의미합니다 32302는 MariaDB3.23.02를 의미합니다. 그래서 이 쿼리가 실행되는 MySQL의 버전이 3.23.02 이상인 경우에는 위의 두 예제 쿼리는 동일한 기능을 수행하지만 그 미만의 버전에서는 첫번째 예제 쿼리는 임시테이블이 아닌 일반 테이블로 생성하게 되고 두 번째 예제는 임시 테이블로 생성하게 됩니다. 3.23.02 버전부터 임시 테이블 기능이 추가되었기 때문에 두번째 쿼리는 오류를 발생하게 됩니다. 즉, SQL 문장에 특정 기능을 사용하려고 하는데 이 기능이 특정 버전 이상에서만 작동한다면 위와 같이 주석으로 감싸고 최소 사용가능한 MariaDB버전을 명시해 주면 되는 것입니다.

 

STRAIGHT_JOIN

여러 개의 테이블이 조인되는 경우 조인의 순서를 고정시키는 역할을 합니다. 옵티마이저가 FROM절에 명시된 테이블의 순서대로 조인을 수행하도록 유도합니다.

힌트 사용전에 아래 쿼리에서는 d -> de -> e 순으로 조인을 수행했습니다. 힌트 사용 후에는 FROM절에 명시된 테이블 순서대로 e -> de ->d 순으로 조인을 수행하는 것을 볼 수 있습니다.

여기서 주의해야 할 사항은 MariaDB의 힌트는 다른 DBMS의 힌트에 비해서 옵티마이저에 미치는 영향이 크다는 점입니다. 조금 과장하면 힌트가 있으면 옵티마이저는 힌트를 맹신하고 그 힌트에 맞게 쿼리를 실행시킨다는 것입니다.

주로 다음 기준에 맞게 조인 순서가 결정되지 않는 경우에만 STRAIGHT_JOIN힌트로 조인 순서를 강제해주는 것이 좋습니다.

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

이 경우에는 거의 임시 테이블을 드라이빙 테이블로 선정하는 것이 좋음.

- 임시 테이블끼리의 조인

임시테이블은 항상 인덱스가 없기 때문에 어느 테이블을 먼저 드라이빙으로 읽어도 무관(크기가 작은 것이 좋음)

- 일반 테이블끼리의 조인

양쪽 테이블 모두 조인 컬럼에 인덱스가 있거나 양쪽 모두 조인 컬럼에 인덱스가 없는 경우에는 레코드 건수가 적은 테이블을 드라이빙으로 선택해주는 것이 좋으며 그외의 경우에는 조인 컬럼에 인덱스가 없는 테이블을 드라이빙으로 선정하는 것이 좋음

 

힌트 사용 전
MariaDB [employees]> explain
    -> select * from employees e,dept_emp de,departments d
    -> where e.emp_no=de.emp_no and d.dept_no=de.dept_no;
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+
| id   | select_type | table | type   | possible_keys             | key         | key_len | ref                 | rows  | Extra       |
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+
|    1 | SIMPLE      | d     | index  | PRIMARY                   | ux_deptname | 122     | NULL                | 9     | Using index |
|    1 | SIMPLE      | de    | ref    | PRIMARY,ix_empno_fromdate | PRIMARY     | 12      | employees.d.dept_no | 20879 |             |
|    1 | SIMPLE      | e     | eq_ref | PRIMARY                   | PRIMARY     | 4       | employees.de.emp_no | 1     |             |
+------+-------------+-------+--------+---------------------------+-------------+---------+---------------------+-------+-------------+
3 rows in set (0.003 sec)

힌트 사용 후
MariaDB [employees]> explain
    -> select /*! STRAIGHT_JOIN */ * from employees e,dept_emp de,departments d
    -> where e.emp_no=de.emp_no and d.dept_no=de.dept_no;
+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------+--------+-------+
| id   | select_type | table | type   | possible_keys             | key               | key_len | ref                  | rows   | Extra |
+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------+--------+-------+
|    1 | SIMPLE      | e     | ALL    | PRIMARY                   | NULL              | NULL    | NULL                 | 298892 |       |
|    1 | SIMPLE      | de    | ref    | PRIMARY,ix_empno_fromdate | ix_empno_fromdate | 4       | employees.e.emp_no   | 1      |       |
|    1 | SIMPLE      | d     | eq_ref | PRIMARY                   | PRIMARY           | 12      | employees.de.dept_no | 1      |       |
+------+-------------+-------+--------+---------------------------+-------------------+---------+----------------------+--------+-------+
3 rows in set (0.000 sec)

 

USE INDEX / FORCE INDEX / IGNORE INDEX

조인의 순서를 변경하는 것 다음으로 자주 사용되는 것이 인덱스 힌트입니다. 인덱스 힌트는 사용하려는 인덱스를 가지는 테이블 뒤에 힌트를 명시해 주어야 합니다. 대체적으로 MariaDB 옵티마이저는 어떤 인덱스를 사용해야 할지를 무난하게 잘 선택하는 것으로 보입니다. 하지만 비슷한 인덱스가 여러 개존재하는 경우에는 MariaDB 옵티마이저가 실수를 하는데 이런 경우에는 강제로 특정 인덱스를 사용하도록 힌트를 주는 것이 좋습니다.

 

USE INDEX

가장 자주 사용되는 인덱스 힌트로 MariaDB 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장하는 힌트정도로 생각하면 됩니다. 대부분의 경우 인덱스 힌트가 주어지면 옵티마이저는 사용자의 힌트를 채택하지만 항상 그 인덱스를 사용하는 것은 아닙니다.

 

FORCE INDEX

USE INDEX와 비교해서 다른 점은 없으며 USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트로 생각하면 됩니다. 비슷하기 때문에 거의 사용하지 않습니다.

 

IGNORE INDEX

특정 인덱스를 사용하지 못하도록 하는 용도로 사용하는 힌트입니다. MariaDB 5.1부터는 풀 테이블 스캔을 유도하기 위해서는 IGNORE INDEX를 사용할 수 있습니다.

 

방금 소개된 3종류의 인덱스 힌트 모두 용도를 명시해줄 수 있습니다. 용도는 선택사항이며 특별히 인덱스 힌트에 용도가 명시되지 않으면(사용 가능한 경우) 주어진 인덱스를 3가지 용도로 사용하게 됩니다.

 

USE INDEX FOR JOIN

테이블간의 조인뿐만 아니라 레코드를 검색하기 위한 용도까지 포함하는 용어입니다. MariaDB에서는 하나의 테이블로부터 데이터를 검색하는 작업도 JOIN이라고 표현되기 때문에 여기에서 FOR JOIN이라는 이름이 붙은 것으로 보입니다

 

USE INDEX FOR ORDER BY

명시된 인덱스를 ORDER BY용도로만 사용할 수 있게 제한

 

USE INDEX FOR GROUP BY

명시된 인덱스를 GROUP BY 용도로만 사용할 수 있게 제한

 

힌트 사용 예제

IGNORE INDEX 사용 시 인덱스 사용을 하지않고 FULL TABLE SCAN을 수행하는 것을 확인할 수 있습니다.

MariaDB [employees]> EXPLAIN
    -> SELECT * FROM employees WHERE emp_no=10001;
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | employees | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.001 sec)

MariaDB [employees]> EXPLAIN
    -> SELECT * FROM employees USE INDEX(primary) WHERE emp_no=10001;
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table     | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
|    1 | SIMPLE      | employees | const | PRIMARY       | PRIMARY | 4       | const | 1    |       |
+------+-------------+-----------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.001 sec)

MariaDB [employees]> EXPLAIN
    -> SELECT * FROM employees IGNORE INDEX(primary) WHERE emp_no=10001;EXPLAIN
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|    1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 298892 | Using where |
+------+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
1 row in set (0.001 sec)

 

SQL_CACHE / SQL_NO_CACHE

 

이 쿼리 캐시에 일시적으로 저장됩니다. SELECT 쿼리가 만들어낸 결과를 캐시에 담아 둘지 말지를 쿼리에서 직접 선택할 수 있는데 이때 사용하는 힌트가 SQL_CACHE /SQL_NO_CACHE입니다. query_cache_type이라는 시스템 변수에 의해서 기본적으로 쿼리의 결과를 쿼리 캐시에 저장할지 결정됩니다.

 

SQL_CALC_FOUND_ROWS

LIMIT를 사용하는 경우 조건을 만족하는 레코드가 LIMIT에 명시된 수보다 더 많다 하더라도 LIMIT에 명시된 수만큼 만족하는 레코드를 찾게 되면 즉시 검색 작업을 멈추게 됩니다. 하지만 해당 힌트가 포함된 쿼리의 경우에는 LIMIT를 만족하는 수만큼의 레코드를 찾았다 하더라도 끝까지 검색을 수행합니다.

 

MariaDB [employees]> SELECT SQL_CALC_FOUND_ROWS * FROM employees LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.164 sec)

MariaDB [employees]> select FOUND_ROWS() AS total_record_count;
+--------------------+
| total_record_count |
+--------------------+
|             300024 |
+--------------------+
1 row in set (0.003 sec)

해당 힌트는 성능향상을 위해서 만들어진 힌트가 아니라 개발자의 편의를 위해서 만들어진 힌트라고 생각하면 됩니다. 성능은 느릴 수 있습니다. (SELECT COUNT(*) FROM 절로 COUNT를 구하는 것보다)

 

기타힌트는 더 있지만 거의 사용되지 않기 때문에 매뉴얼을 통해 추가적으로 학습하면 될 것 같습니다.

 

출처 : Real MariaDB