8.2.1.7 인덱스 확장의 사용

InnoDB 는 자동으로 각 보조 인덱스에 프라이 머리 키 컬럼을 추가하고 그것을 확장합니다. 이 테이블 정의에 대해 생각합니다.

 CREATE TABLE t1 (
   i1 INT NOT NULL DEFAULT 0
   i2 INT NOT NULL DEFAULT 0
   d DATE DEFAULT NULL,
   PRIMARY KEY (i1, i2)
   INDEX k_d (d)
 ) ENGINE = InnoDB;

이 테이블에서는, 컬럼 (i1, i2) 에 기본 키를 정의하고 있습니다. 또한 컬럼 (d) 에 보조 인덱스 k_d 를 정의하고 있습니다 만, 내부에서 InnoDB 는이 인덱스를 확장하고 그것을 컬럼 (d, i1, i2) 로 처리합니다.

MySQL 5.6.9 이전에서는 최적화 확장 보조 인덱스의 사용 방법과 사용 여부를 판단 할 때 그 프라이 머리 키 컬럼을 고려하지 않습니다. 5.6.9 이후 최적화는 프라이 머리 키 컬럼을 고려하게 되었기 때문에,보다 효율적인 쿼리 실행 계획 및 성능 향상으로 이어질 수 있습니다.

최적화는 ref , range 및 index_merge 인덱스 액세스 루스 인덱스 스캔 조인과 정렬 최적화 및 MIN() / MAX() 최적화에 확장 보조 인덱스를 사용할 수 있습니다.

다음 예제에서는 최적화가 확장 보조 인덱스를 사용하는지 여부에 따라 실행 계획에 어떤 영향을 미치는지 보여줍니다. 이러한 행 t1 이 이입되어 있다고합니다.

 INSERT INTO t1 VALUES
 (1, 1, '1998-01-01'), (1, 2, '1999-01-01')
 (1, 3, '2000-01-01'), (1, 4, '2001-01-01')
 (1, 5, '2002-01-01'), (2, 1, '1998-01-01')
 (2, 2 '1999-01-01'), (2, 3, '2000-01-01')
 (2, 4, '2001-01-01'), (2, 5, '2002-01-01')
 (3, 1 '1998-01-01'), (3, 2, '1999-01-01')
 (3, 3, '2000-01-01'), (3, 4, '2001-01-01')
 (3, 5, '2002-01-01'), (4, 1, '1998-01-01')
 (4, 2, '1999-01-01'), (4, 3, '2000-01-01')
 (4, 4 '2001-01-01'), (4, 5, '2002-01-01')
 (5, 1, '1998-01-01'), (5, 2, '1999-01-01')
 (5, 3, '2000-01-01'), (5, 4, '2001-01-01')
 (5, 5 '2002-01-01');

여기에서 다음 쿼리를 고려합니다.

 EXPLAIN SELECT COUNT (*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'

이 예에서는 기본 키 컬럼 (i1, i2) 으로 구성되며, 쿼리에 i2 를 참조하지 않기 때문에 최적화는 기본 키를 사용할 수 없습니다. 대신 최적화 (d) 의 보조 인덱스 k_d 를 사용할 수 있으며 실행 계획은 확장 인덱스를 사용하는지 여부에 따라 다릅니다.

최적화가 인덱스 확장을 고려하지 않으면 그것은 인덱스 k_d 을 (d) 만을 취급합니다. 쿼리 EXPLAIN 은 다음과 같은 결과가 생성됩니다.

 mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : t1
          type : ref
 possible_keys : PRIMARY, k_d
           key : k_d
       key_len : 4
           ref : const
          rows : 5
         Extra : Using where; Using index

최적화가 인덱스 확장을 고려할 때 그것은 인덱스 k_d 을 (d, i1, i2) 로 처리합니다. 이 경우, 그것은 왼쪽의 인덱스 프리픽스 (d, i1) 을 사용하여 더 나은 실행 계획을 생성 할 수 있습니다.

 mysql> EXPLAIN SELECT COUNT(*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01'\G
 *************************** 1. row ******************** *******
            id : 1
   select_type : SIMPLE
         table : t1
          type : ref
 possible_keys : PRIMARY, k_d
           key : k_d
       key_len : 8
           ref : const, const
          rows : 1
         Extra : Using index

두 경우 모두 key 는 최적화가 보조 인덱스 k_d 을 사용하는 것을 나타냅니다 만, EXPLAIN 출력은 확장 인덱스를 사용하여 다음과 같은 개선이 표시됩니다.

확장 인덱스의 사용의 최적화 동작의 차이는 SHOW STATUS 에서도 확인할 수 있습니다.

 FLUSH TABLE t1;
 FLUSH STATUS;
 SELECT COUNT (*) FROM t1 WHERE i1 = 3 AND d = '2000-01-01';
 SHOW STATUS LIKE 'handler_read %'

이전 문에는 FLUSH TABLE 과 FLUSH STATUS 가 포함 테이블 캐시를 플러시 상태 카운터를 지 웁니다.

인덱스 확장을 사용하지 않으면 SHOW STATUS 는 다음의 결과를 생성합니다.

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 5     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

인덱스 확장을 사용하면 SHOW STATUS 는 다음의 결과를 생성합니다. Handler_read_next 값이 5에서 1로 감소하고 인덱스를보다 효율적으로 사용하는 것을 보여줍니다.

+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 0     |
| Handler_read_key      | 1     |
| Handler_read_last     | 0     |
| Handler_read_next     | 1     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 0     |
+-----------------------+-------+

optimizer_switch 시스템 변수 use_index_extensions 플래그는 InnoDB 테이블의 보조 인덱스의 사용 방법을 결정하는 데 최적화가 프라이 머리 키 컬럼을 고려할지 여부를 제어 할 수 있습니다. 기본적으로 use_index_extensions 은 유효합니다. 인덱스 확장의 사용을 해제하면 성능이 향상 될 수 있는지 여부를 확인하려면 다음 문을 사용합니다.

 SET optimizer_switch = 'use_index_extensions = off';

최적화 프로그램이 인덱스 확장의 사용은 인덱스 (16)의 키 파트의 수와 최대 키 길이 (3072 바이트)에 따른 이용 제한에 따릅니다.