[MySQL] 인덱스 확장 사용

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)로 처리합니다.

 

옵티마이저는 해당 인덱스 사용 방법 및 사용 여부를 결정할 때 확장 보조 인덱스의 기본 키 컬럼을 고려합니다. 이로 인해 쿼리 실행 계획이보다 효율적으로 수행되고 성능이 향상될 수 있습니다.

 

옵티마이저는 ref, range 및 index_merge 인덱스 액세스, Loose 인덱스 스캔 액세스, 조인 및 정렬 최적화 및 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'

실행 계획은 확장 인덱스 사용 여부에 따라 다릅니다.

 

 

 

옵티마이저가 인덱스 확장을 고려하지 않으면 인덱스 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

두 경우 모두 키는 옵티마이저가 보조 인덱스 k_d를 사용하지만 EXPLAIN 출력은 확장 인덱스를 사용하여 다음과 같은 개선 사항을 보여줍니다.

+ key_len은 4바이트에서 8바이트로 이동하여 키 조회에서 d뿐만 아니라 열 d와 i1을 사용함을 나타냅니다.

+ 키 조회는 하나가 아닌 두 개의 키 부분을 사용하기 때문에 참조 값이 const에서 const, const로 변경됩니다.

+ 행 개수가 5에서 1로 감소하여 InnoDB가 적은 수의 행을 검사하여 결과를 생성해야 함을 나타냅니다.

+ Extra 값은 Use where; Useing index에서 Useing index로 변경됩니다. 이는 데이터 행의 열을 참조하지 않고 인덱스만 사용하여 행을 읽을 수 있음을 의미합니다.

 

확장 인덱스 사용에 대한 옵티마이저 동작의 차이점은 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 TABLES 및 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 바이트)에 대한 일반적인 제한이 적용됩니다.

 

※도움이 되셨다면 광고클릭 한번 부탁드립니다.※