SQL 힌트(2)

SQL 힌트(2)

3. USE INDEX / FORCE INDEX / IGNORE INDEX

가끔 4개 이상의 칼럼으로 생성된 인덱스도 있다. 이때 똑같은 칼럼을 비슷한 순서나 조합으로 포함한 인덱스가 여러 개 있는 테이블에 대해 MySQL 옵티마이저가 최적의 인덱스를 선택하지 못할 때가 가끔 있다. 하지만 2~3개의 칼럼이 포함된 인덱스는 MySQL 옵티마이저가 최적의 인덱스를 충분히 선택할 수 있을 정도로 똑똑하기 때문에 너무 걱정하지 않아도 된다.

복잡한 인덱스에 대해 MySQL 옵티마이저가 적합한 인덱스를 선택하지 못할 때는 USE INDEX나 FORCE INDEX 힌트로 옵티마이저가 다른 인덱스를 사용하도록 유도할 수 있다. STRAIGHT_JOIN 힌트와는 달리, 인덱스 힌트는 사용하려는 인덱스가 포함된 테이블 뒤에 힌트를 명시해야 한다. 이 3가지 인덱스 힌트 모두 키워드 뒤에 인덱스 이름을 괄호로 묶어서 사용하며, 괄호 안에 아무것도 없거나 존재하지 않는 인덱스 이름을 사용할 때는 문법 오류가 나면서 종료된다. 또한, 별도로 사용자가 이름을 부여할 수 없는 프라이머리 키는 "PRIMARY"라는 키워드를 사용하면 된다.

USE INDEX

가장 자주 사용되는 인덱스 힌트로, MySQL 옵티마이저에게 특정 테이블의 인덱스를 사용하도록 권장한다.

FORCE INDEX

USE INDEX와 비교해서 다른 점은 없으며, USE INDEX보다 옵티마이저에게 미치는 영향이 더 강한 힌트다. USE INDEX 힌트가 사용되면 MySQL 옵티마이저는 최대한 그 인덱스를 이용해 쿼리를 실행하려고 노력하기 때문에 USE INDEX보다 더 영력이 큰 FORCE INDEX 힌트는 거의 사용할 일이 없다. 지금까지의 경험으로 보면 대체적으로 USE INDEX 힌트에도 불구하고 그 인덱스를 사용하지 않는다면 FORCE INDEX 힌트를 사용해도 해당 인덱스를 사용하지 않을 때가 더 많았다. MySQL 5.0 이하 버전에서는 "FORCE INDEX ()"와 같이 괄호에 아무런 인덱스 이름을 사용하지 않는 힌트는 옵티마이저에게 아무 인덱스도 사용하지 말라는 의미로 사용됐다. 하지만 MySQL 5.1 이상 버전부터는 인자(인덱스 명)가 없는 인덱스 힌트는 문법 오류로 처리되므로 업그레이드할 때 주의해야 한다.

IGNORE INDEX

USE INDEX나 FORCE INDEX와는 반대로 특정 인덱스를 사용하지 못하게 하는 용도로 사용하는 힌트다. 옵티마이저가 풀 테이블 스캔을 사용하도록 유도하고 싶다면 IGNORE INDEX를 사용하면 된다.

또한, 3종류의 인덱스 힌트 모두 어떤 용도로 인덱스를 이용할지 명시할 수 있다. 용도는 선택사항이며, 특별히 인덱스 힌트에 용도를 명시하지 않으면 (사용 가능한 경우) 주어진 인덱스를 다음의 3가지 용도로 전부 사용한다.

USE INDEX FOR JOIN

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

USE INDEX FOR ORDER BY

명시된 인덱스를 ORDER BY 용도로만 사용하도록 제한한다.

USE INDEX FOR GROUP BY

명시된 인덱스를 GROUP BY 용도로만 사용하도록 제한한다.

이렇게 용도를 3가지로 나누긴 했지만 일반적으로 ORDER BY나 GROUP BY 작업에서 인덱스를 사용할 수 있다면 더 나은 성능을 보장할 수 있다. 인덱스를 레코드 검색에만 사용하도록 제한할 필요는 거의 없다. 그래서 인덱스 힌트를 사용할 때 이렇게 별도로 용도까지 명시하는 경우는 거의 없다. 예제로 인덱스 힌트의 사용법을 살펴보자. 다음 예제의 인덱스 힌트는 모두 성능과는 관계없이 사용법을 소개하기 위한 것이다.

SELECT * FROM employees WHERE emp_no=10001;
SELECT * FROM employees FOR 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 테이블의 프라이머리 키를 이용해 쿼리를 처리할 것이다. 기본적으로 인덱스 힌트를 지정하지 않아도 "emp_no=10001" 조건이 있기 때문에 프라이머리 키를 사용하는 것이 최적이라는 것을 옵티마이저도 인식하기 때문이다. 여기서 나온 예제는 전부 예시를 위한 것이므로 기본적으로 옵티마이저가 최적의 실행 계획을 선택하는 경우에는 힌트를 부여하지 않는 것이 좋다.
• 네 번째 쿼리는 일부러 인덱스를 사용하지 못하도록 힌트를 추가했다. 이 쿼리는 프라이머리 키를 통해 아주 빠르게 조회할 수 있는데도 MySQL 5.0과 5.1, 그리고 5.5 모두 풀 테이블 스캔으로 실행 계획이 표시됐다.
• 다섯 번째 예제에서는 이 쿼리와 전혀 무관한 인덱스를 강제로 사용하도록 FORCE INDEX 힌트를 사용했다. 결과는 프라이머리 키는 버리고 풀 테이블 스캔을 하는 형태로 실행 계획이 출력됐다.

예제에서는 살펴보지 못했지만 전문 검색(Fulltext) 인덱스가 지정된 테이블에서 MySQL 옵티마이저는 다른 일반 보조 인덱스(B-Tree 인덱스)를 사용할 수 있는 상황이라 하더라도 전문 검색 인덱스를 선택할 때가 많다. 이는 MySQL 옵티마이저가 다른 보조 인덱스보다 프라이머리 키나 전문 검색 인덱스에 대해 더 높은 가중치를 부여하기 때문이다.

인덱스 사용법을 모르거나 좋은 실행 계획이 어떤 것인지 판단하기 어렵다면 힌트를 사용하지 말고 옵티마이저가 최적의 실행 계획을 선택할 수 있게 해주는 것이 좋다. MySQL 옵티마이저도 인덱스나 조인을 처리할 때 상당히 신뢰할 만한 수준의 최적화를 수행하기 때문이다. 최적의 실행 계획이란 데이터의 성격이나 양에 따라 시시각각 변하기 때문에 지금 프라이머리 키를 사용하는 것이 좋은 계획이었다 하더라도 내일은 아닐 수 있다. 가능하다면 그때그때 옵티마이저가 실행 시점의 통계 정보를 가지고 실행 계획을 선택하게 해주는 것이 가장 좋다.

4. SQL_CACHE / SQL_NO_CACHE

MySQL은 SELECT 쿼리에 의해 만들어진 결과를 재사용하기 위해 쿼리 캐시에 선택적으로 저장해 둔다. SELECT 쿼리의 결과를 쿼리 캐시에 담아 둘지 여부를 쿼리에서 직접 선택할 수도 있는데, 이때 사용하는 힌트가 SQL_CACHE / SQL_NO_CACHE다. query_cache_type이라는 시스템 변수의 설정에 의해 기본적으로 쿼리의 결과를 쿼리 캐시에 저장할지 말지가 결정된다. query_cache_type 변수의 설정 값과 쿼리 캐시 힌트의 사용 조합에 따라 다음 표와 같이 결정된다. 물론, 이 밖의 여러 가지 복잡한 조건에 의해서도 영향을 받지만 여기서 다른 조건들은 모두 배제했다.

		query_cache_type 시스템 변수의 설정 값
	0 또는 OFF	1 또는 ON	2 또는 DEMAND
힌트 없음	캐시하지 않음	캐시함	캐시하지 않음
SQL_CACHE	캐시하지 않음	캐시함	캐시함
SQL_NO_CACHE	캐시하지 않음	캐시하지 않음	캐시하지 않음

만약 쿼리에 따라 쿼리 캐시가 선별적으로 작동하도록 세밀하게 조정하고자 할 때는 query_cache_type 시스템 변수의 값을 DEMAND로 설정하고 각 쿼리에 SQL_CACHE 힌트를 사용하면 된다. 하지만 일반적으로 쿼리 캐시가 사용 가능한 상태(query_cache_type=ON)로 운영하기 때문에 SQL_CACHE 힌트를 사용해야 할 경우는 거의 없다. 그래서 SQL_CACHE 힌트보다는 SQL_NO_CACHE 힌트가 자주 사용된다. SQL_NO_CACHE 힌트는 쿼리 캐시로부터 결과를 가져오지 못하게 하는 것이 아니라, 쿼리의 실행 결과를 쿼리 캐시에 저장하지 않게 하는 힌트다. 즉, SQL_NO_CACHE 힌트를 사용하더라도 쿼리가 실행될 때 쿼리 캐시를 검색하는 작업이 없어지지 않는다는 것을 의미한다.

SQL_NO_CACHE는 쿼리의 성능을 비교하거나 성능을 분석하는 데 자주 사용된다. 예를 들어 다음 예제 쿼리를 직접 실행해보고 대략 성능을 확인해보자.

SELECT COUNT(*)
FROM employees WHERE last_name='Facello';

위의 예제 쿼리는 인덱스를 사용하지 못하기 때문에 풀 테이블 스캔으로 실행될 것이다. 그래서 직접 쿼리를 실행해보면 처리 시간이 상당히 오래 걸릴 것이다. 하지만 다시 한번 동일한 쿼리를 실행해보면 쿼리를 실행한 순간 바로 결과가 나오는 것을 알 수 있다. 다시 한번 더 실행해봐도 결과가 빠르게 화면에 표시될 것이다. 도대체 어떤 결과가 이 쿼리의 진정한 성능일까? 처음 쿼리를 실행했을 때는 이 쿼리의 결과가 쿼리 캐시에 없었기에 MySQL 서버가 실제 employees 테이블을 풀 스캔하면서 결과를 만드느라 느리게 처리된 것이다. 하지만 두 번째 실행부터는 단순히 쿼리 캐시에 저장된 결과를 검색해서 바로 응답을 준 것이다.

항상 모든 쿼리가 쿼리 캐시를 사용할 수 있는 것은 아니다. 특이한 경우를 제외하면 쿼리 캐시를 사용하는 성능 향상은 크게 고려하지 않고 성능 튜닝을 하는 것이 좋다. 그래서 쿼리를 튜닝하고 성능을 분석하기 위해 테스트할 때는 쿼리에 SQL_NO_CACHE 힌트를 사용하는 것이 좋다. 그러면 쿼리 캐시 결과를 사용하지 않고, MySQL 서버가 쿼리를 실행해서 그 결과를 가져오는 전체 과정에서 소요된 시간을 확인할 수 있다.

SQL_NO_CACHE 힌트는 SELECT 쿼리 문장에서만 사용할 수 있으며, SELECT 키워드 바로 뒤에 입력해야 한다. 다른 힌트와 마찬가지로 힌트용 주석을 사용할 수도 있고, 그렇지 않고 SQL의 일부로 바로 SQL_NO_CACHE 키워드를 사용해도 된다.

SELECT SQL_NO_CACHE COUNT(*) FROM employees WHERE last_name='Facello';
SELECT /*! SQL_NO_CACHE */ COUNT(*) FROM employees WHERE last_name='Facello';

쿼리의 성능 테스트를 할 때는 쿼리 캐시뿐 아니라 InnoDB의 버퍼 풀이나 MyISAM의 키 캐시에 대한 부분도 함께 고려해야 하며, 정밀한 성능을 측정하기 위해서는 그 밖에도 많은 고려 사항이 있다.

5. SQL_CALC_FOUND_ROWS

SELECT 쿼리에 LIMIT 절이 사용될 때 조건을 만족하는 레코드가 LIMIT 절에 명시된 수보다 많다면 LIMIT에 명시된 건수만큼만 레코드를 찾고, 즉시 쿼리 수행을 멈춘다. 하지만 SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리에서는 LIMIT 절과 관계없이 검색 조건에 일치하는 모든 레코드를 검색해서 전체 조건에 일치하는 레코드가 몇 건이나 되는지 계산한다. 그렇지만 사용자에게는 LIMIT에 제한된 건수만큼의 레코드만 반환된다.

SQL_CALC_FOUND_ROWS 힌트가 사용된 쿼리를 실행한 다음에는 FOUND_ROWS()라는 함수를 이용해 LIMIT 절과 관계없이 조건에 일치하는 전체 레코드가 몇 건이었는지 가져올 수 있다. SQL_CALC_FOUND_ROWS 힌트의 간단한 예제를 한번 살펴보자.

mysql > 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-08-26  |
| 10002    | 1964-06-02   | Bezalel     | Simmel     | F       | 1985-11-21  |
... 
+----------+--------------+-------------+------------+---------+-------------+

mysql > SELECT FOUND_ROWS() AS total_record_count;
+----------------------+
| total_record_count   |
+----------------------+
| 3000204              |
+----------------------+

첫 번째 쿼리에서는 조건에 만족하는 5건의 레코드만 화면에 표시될 것이다. 하지만 두 번째 쿼리에서는 "LIMIT 5"와 관계없이 SELECT 쿼리를 만족했던 모든 레코드의 건수를 화면에 출력한다. 두 번째 쿼리는 다시 한번 employees 테이블을 검색한 것이 아니라, 첫 번째 쿼리가 실행되면서 현재 커넥션의 세션 변수에 저장해둔 값을 가져와서 보여주기만 하는 것이다.

FOUND_ROWS() 함수는 'SQL_CALC_FOUND_ROWS' 힌트를 사용한 쿼리를 실행한 이후에만 사용할 수 있는 것은 아니다. 만약 'SQL_CALC_FOUND_ROWS' 힌트를 사용하지 않는 SELECT 쿼리를 실행하고 그 이후에 'SELECT FOUND_ROWS()' 쿼리를 실행하면 이전 SELECT 쿼리에서 조회됐던 레코드 건수를 반환한다. 이 기능은 가끔 셸 스크립트로 MySQL 서버에 접속해 어떤 작업을 처리해야 할 때 유용하게 활용할 수도 있다.

웹 프로그램에서 페이징 기능을 개발해본 사용자라면 이 기능이 상당히 효율적이고 편리해 보일 것이다. 하지만 편리할 수는 있지만 효율적인 경우는 별로 없다. 여기서 설명하려는 것은 이 힌트의 장점이 아니라 이 힌트를 사용하면 안 되는 경우다. 우선 SQL_CALC_FOUND_ROWS를 사용한 페이징 처리 예제와 레코드 건수를 구하기 위한 COUNT(*) 쿼리와 실제 표시해 줄 데이터를 별도로 SELECT하는 예제를 한번 비교해 보자.

SQL_CALC_FOUND_ROWS를 사용하는 방법

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

SELECT FOUND_ROWS() AS total_record_count;

한 번의 쿼리 실행으로 필요한 정보 두 가지를 모두 가져오는 것처럼 보이지만 FOUND_ROWS() 함수의 실행을 위해 또 한 번의 쿼리가 필요하기 때문에 쿼리 실행 횟수는 2번이다. employees 테이블의 ix_firstname 인덱스를 레인지 스캔 방식을 사용해서 결과를 가져온다. 실제 employees 테이블에서 이 조건을 만족하는 레코드는 전체 253건이다. LIMIT 조건 때문에 처음 20건만 가져오지만, SQL_CALC_FOUND_ROWS 힌트 때문에 조건을 만족하는 레코드 전부를 읽어야만 한다("SHOW SESSION STATUS LIKE 'Innodb_rows_read%'" 명령으로 InnoDB가 쿼리를 실행할 때 레코드를 얼마나 읽었는지 확인해 볼 수 있다). 253번의 읽기 작업은 인덱스 레인지 스캔 후 나머지 칼럼을 읽기 위해 데이터 페이지를 읽는 랜덤 I/O까지 포함한 것이다.

기존 2개의 쿼리로 쪼개서 실행하는 방법

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

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

이 방식 또한 마찬가지로 쿼리를 2번 실행하기 때문에 네트워크 통신은 2번이 발생한다. 우선 전체 조건을 만족하는 건수를 조회하기 위한 첫 번째 쿼리를 살펴보자. 이 쿼리 또한 employees 테이블의 ix_firstname 인덱스를 레인지 스캔하지만, 나머지 칼럼은 읽을 필요가 없기 때문에 커버링 인덱스로 처리된다. 즉 인덱스를 쭉 스캔하면서 253건의 레코드를 읽었지만 데이터 레코드를 가져오기 위한 랜덤 I/O는 발생하지 않는다. 실제 데이터를 가져오기 위한 두 번째 쿼리도 employees 테이블의 ix_firstname 인덱스를 레인지 스캔으로 접근한 후, 나머지 칼럼을 읽기 위해 랜덤 I/O가 발생한다. 하지만 이 쿼리에서는 LIMIT 0, 20이라는 제한이 있기 때문에 랜덤 I/O를 253번 실행하는 것이 아니라 20번만 실행하게 된다.

결론적으로, SQL_CALC_FOUND_ROWS 힌트를 사용한 쿼리는 한번의 인덱스 레인지 스캔과 253번의 랜덤 I/O가 필요하지만 힌트를 사용하지 않은 쿼리는 두 번의 인덱스 레인지 스캔이 필요하지만 20번만 랜덤 I/O로 처리된다. 인덱스 레인지 스캔은 랜덤 I/O 2~4번으로 완료되는 상대적으로 가벼운 작업이다. 결국, 랜덤 I/O 작업 230번 정도의 차이로 힌트가 사용되지 않은 쿼리가 빠르게 처리될 것이다.

SQL_CALC_FOUND_ROWS 힌트는 UNION(DISTINCT)을 사용한 쿼리에서 사용할 수 없다는 점도 주의해야 한다. 쿼리의 특성이나 데이터의 특성에 따른 많은 변수가 있겠지만 힌트를 사용하지 않는 쿼리가 힌트를 사용한 쿼리보다 느리게 실행될 때는 거의 쿼리의 인덱스 사용이 제대로 튜닝되지 않을 때일 것이다. 그러므로 가능하다면 SQL_CALC_FOUND_ROWS 힌트를 사용하기보다는 쿼리를 튜닝하는데 더 집중하자. 사실 SQL_CALC_FOUND_ROWS는 성능 향상을 목적으로 만들어진 힌트가 아니라 개발자의 편의를 위해 만들어진 힌트라는 점을 생각하면 당연한 결과다.

6. 기타 힌트

주요하게 언급되지는 않았지만 SQL_BIG_RESULT, SQL_SMALL_RESULT, SQL_BUFFER_RESULT, HIGH_PRIORITY 등의 힌트도 있다. 하지만 이러한 힌트는 거의 사용되지 않기 때문에 매뉴얼을 토대로 천천히 공부해 보면 된다.

참고

• Real MySQL