10.9.4 Index Hints

10.9.4 Index Hints

Index hints는 쿼리 처리 중 옵티마이저가 인덱스를 선택하는 방법에 대한 정보를 제공합니다. 여기에서 설명하는 Index hints는 Section 10.9.3, “Optimizer Hints”에 설명된 optimizer hints와는 다릅니다. Index hints와 optimizer hints는 각각 별도로 또는 함께 사용할 수 있습니다.

Index hints는 SELECT 및 UPDATE 문에 적용됩니다. 또한 멀티 테이블 DELETE 문에서는 동작하지만, 이 섹션 뒤쪽에 나온 것처럼 단일 테이블 DELETE에서는 동작하지 않습니다.

Index hints는 테이블 이름 뒤에 지정합니다. (SELECT 문에서 테이블을 지정하는 일반적인 구문은 Section 15.2.13.2, “JOIN Clause”를 참조하십시오.) Index hints를 포함하여 개별 테이블을 참조하는 구문은 다음과 같습니다:

1tbl_name [[AS] alias] [index_hint_list]
2
3index_hint_list:
4    index_hint [index_hint] ...
5
6index_hint:
7    USE {INDEX|KEY}
8      [FOR {JOIN|ORDER BY|GROUP BY}] ([index_list])
9  | {IGNORE|FORCE} {INDEX|KEY}
10      [FOR {JOIN|ORDER BY|GROUP BY}] (index_list)
11
12index_list:
13    index_name [, index_name] ...

USE INDEX (index_list) 힌트는 MySQL이 테이블의 행을 찾을 때 지정된 인덱스 중 하나만 사용하도록 지시합니다. 대체 구문인 IGNORE INDEX (index_list)는 MySQL이 특정 인덱스 또는 여러 인덱스를 사용하지 않도록 지시합니다. 이러한 힌트는 EXPLAIN에서 MySQL이 가능한 인덱스 목록 중 잘못된 인덱스를 사용하고 있음을 보여줄 때 유용합니다.

FORCE INDEX 힌트는 USE INDEX (index_list)처럼 동작하지만, 테이블 스캔이 매우 비용이 큰 것으로 간주된다는 점이 추가됩니다. 다시 말해, 테이블 스캔은 지정된 인덱스 중 하나를 사용하여 테이블의 행을 찾을 방법이 전혀 없을 때만 사용됩니다.

참고

MySQL 9.5는 인덱스 수준 옵티마이저 힌트인 JOIN_INDEX, GROUP_INDEX, ORDER_INDEX, 및 INDEX를 지원하는데, 이들은 FORCE INDEX index hints와 동등하며 이를 대체하기 위한 것입니다. 또한 NO_JOIN_INDEX, NO_GROUP_INDEX, NO_ORDER_INDEX, 및 NO_INDEX optimizer hints도 지원하는데, 이들은 IGNORE INDEX index hints와 동등하며 이를 대체하기 위한 것입니다. 따라서, USE INDEX, FORCE INDEX, 그리고 IGNORE INDEX는 향후 MySQL 릴리스에서 deprecated될 것으로 예상해야 하며, 그 이후 어느 시점에는 완전히 제거될 것입니다.

이러한 인덱스 수준 옵티마이저 힌트는 단일 테이블 및 멀티 테이블 DELETE 문 모두에서 지원됩니다.

자세한 내용은 Index-Level Optimizer Hints를 참조하십시오.

각 힌트에는 컬럼 이름이 아니라 인덱스 이름이 필요합니다. 프라이머리 키를 참조하려면 이름 PRIMARY를 사용하십시오. 테이블의 인덱스 이름을 보려면 SHOW INDEX 문 또는 Information Schema STATISTICS 테이블을 사용하십시오.

index_name 값은 전체 인덱스 이름일 필요는 없습니다. 인덱스 이름의 모호하지 않은 프리픽스일 수 있습니다. 프리픽스가 모호한 경우 오류가 발생합니다.

예시는 다음과 같습니다:

1SELECT * FROM table1 USE INDEX (col1_index,col2_index)
2  WHERE col1=1 AND col2=2 AND col3=3;
3
4SELECT * FROM table1 IGNORE INDEX (col3_index)
5  WHERE col1=1 AND col2=2 AND col3=3;

Index hints 구문에는 다음과 같은 특성이 있습니다:

• USE INDEX의 경우 _index_list_를 생략하는 것은 구문상 유효한데, 이는 “어떠한 인덱스도 사용하지 않는다”는 의미입니다.
  FORCE INDEX 또는 IGNORE INDEX에서 _index_list_를 생략하는 것은 구문 오류입니다.

• 힌트에 FOR 절을 추가하여 인덱스 힌트의 스코프를 지정할 수 있습니다. 이것은 쿼리 처리의 여러 단계에서 옵티마이저가 실행 계획을 선택하는 방식에 대해 보다 세밀한 제어를 제공합니다. MySQL이 테이블에서 행을 찾고 조인을 처리하는 방법에 사용되는 인덱스에만 영향을 주려면 FOR JOIN을 사용하십시오. 행을 정렬하거나 그룹핑할 때의 인덱스 사용에 영향을 주려면 FOR ORDER BY 또는 FOR GROUP BY를 사용하십시오.

• 여러 인덱스 힌트를 지정할 수 있습니다:

1SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;

같은 인덱스를 여러 힌트에서 (심지어 동일한 힌트 내에서) 이름으로 지정해도 오류가 아닙니다:

1SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);

그러나 동일한 테이블에 대해 USE INDEX와 FORCE INDEX를 혼용하는 것은 오류입니다:

1SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);

Index hint에 FOR 절이 포함되지 않으면 힌트의 스코프는 문장의 모든 부분에 적용되는 것입니다. 예를 들어 다음 힌트:

1IGNORE INDEX (i1)

는 다음 힌트 조합과 동일합니다:

1IGNORE INDEX FOR JOIN (i1)
2IGNORE INDEX FOR ORDER BY (i1)
3IGNORE INDEX FOR GROUP BY (i1)

Index hints가 처리될 때, 이들은 타입(USE, FORCE, IGNORE)과 스코프(FOR JOIN, FOR ORDER BY, FOR GROUP BY)별로 단일 목록으로 수집됩니다. 예를 들면 다음과 같습니다:

1SELECT * FROM t1
2  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);

이는 다음과 동일합니다:

1SELECT * FROM t1
2   USE INDEX (i1,i2) IGNORE INDEX (i2);

그런 다음 Index hints는 각 스코프에 대해 다음 순서로 적용됩니다:

1. {USE|FORCE} INDEX가 존재하면 이를 적용합니다. (존재하지 않으면 옵티마이저가 결정한 인덱스 집합이 사용됩니다.)

2. IGNORE INDEX는 이전 단계의 결과에 대해 적용됩니다. 예를 들어 다음 두 쿼리는 동일합니다:

1SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2);
2
3SELECT * FROM t1 USE INDEX (i1);

FULLTEXT 검색의 경우 Index hints는 다음과 같이 동작합니다:

• 내추럴 랭귀지 모드 검색에서는 Index hints가 묵시적으로 무시됩니다. 예를 들어 IGNORE INDEX(i1)는 경고 없이 무시되며 인덱스는 여전히 사용됩니다.

• 불리언 모드 검색에서는 FOR ORDER BY 또는 FOR GROUP BY가 있는 Index hints는 묵시적으로 무시됩니다. FOR JOIN이 있는 Index hints 또는 FOR 수정자가 없는 Index hints는 적용됩니다. FULLTEXT가 아닌 검색에서 힌트가 적용되는 방식과는 달리, 이 경우 힌트는 쿼리 실행의 모든 단계(행 찾기 및 검색, 그룹핑, 정렬)에 사용됩니다. 이는 힌트가 비-FULLTEXT 인덱스에 대해 주어진 경우에도 마찬가지입니다.

예를 들어, 다음 두 쿼리는 동일합니다:

1SELECT * FROM t
2    USE INDEX (index1)
3    IGNORE INDEX FOR ORDER BY (index1)
4    IGNORE INDEX FOR GROUP BY (index1)
5    WHERE ... IN BOOLEAN MODE ... ;
6
7SELECT * FROM t
8    USE INDEX (index1)
9    WHERE ... IN BOOLEAN MODE ... ;

Index hints는 DELETE 문에서도 동작하지만, 다음과 같이 멀티 테이블 DELETE 구문을 사용하는 경우에만 동작합니다:

1mysql> EXPLAIN DELETE FROM t1 USE INDEX(col2)
2    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
3ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
4corresponds to your MySQL server version for the right syntax to use near 'use
5index(col2) where col1 between 1 and 100 and col2 between 1 and 100' at line 1
6
7mysql> EXPLAIN DELETE t1.* FROM t1 USE INDEX(col2)
8    -> WHERE col1 BETWEEN 1 AND 100 AND COL2 BETWEEN 1 AND 100\G
9*************************** 1. row ***************************
10           id: 1
11  select_type: DELETE
12        table: t1
13   partitions: NULL
14         type: range
15possible_keys: col2
16          key: col2
17      key_len: 5
18          ref: NULL
19         rows: 72
20     filtered: 11.11
21        Extra: Using where
221 row in set, 1 warning (0.00 sec)