10.3.10 Use of Index Extensions

10.3.10 Use of Index Extensions

InnoDB는 각 세컨더리 인덱스에 프라이머리 키 컬럼들을 덧붙여 자동으로 확장합니다. 다음과 같은 테이블 정의를 고려해 보십시오:

1CREATE TABLE t1 (
2  i1 INT NOT NULL DEFAULT 0,
3  i2 INT NOT NULL DEFAULT 0,
4  d DATE DEFAULT NULL,
5  PRIMARY KEY (i1, i2),
6  INDEX k_d (d)
7) ENGINE = InnoDB;

이 테이블은 (i1, i2) 컬럼들에 대해 프라이머리 키를 정의합니다. 또한 (d) 컬럼에 대해 세컨더리 인덱스 k_d를 정의하지만, 내부적으로 InnoDB는 이 인덱스를 확장하여 컬럼들을 (d, i1, i2)로 취급합니다.

optimizer는 그 인덱스를 어떻게 사용할지, 그리고 사용할지 여부를 결정할 때 확장된 세컨더리 인덱스의 프라이머리 키 컬럼들을 고려합니다. 이로 인해 더 효율적인 쿼리 실행 계획과 더 나은 성능이 나올 수 있습니다.

optimizer는 ref, range, 그리고 index_merge 인덱스 액세스, 느슨한 인덱스 스캔 액세스, 조인 및 정렬 최적화, 그리고 MIN()/ MAX() 최적화에 대해 확장된 세컨더리 인덱스를 사용할 수 있습니다.

다음 예시는 optimizer가 확장된 세컨더리 인덱스를 사용하는지 여부에 따라 실행 계획이 어떻게 달라지는지 보여줍니다. t1 테이블이 다음과 같은 행들로 채워져 있다고 가정합니다:

1INSERT INTO t1 VALUES
2(1, 1, '1998-01-01'), (1, 2, '1999-01-01'),
3(1, 3, '2000-01-01'), (1, 4, '2001-01-01'),
4(1, 5, '2002-01-01'), (2, 1, '1998-01-01'),
5(2, 2, '1999-01-01'), (2, 3, '2000-01-01'),
6(2, 4, '2001-01-01'), (2, 5, '2002-01-01'),
7(3, 1, '1998-01-01'), (3, 2, '1999-01-01'),
8(3, 3, '2000-01-01'), (3, 4, '2001-01-01'),
9(3, 5, '2002-01-01'), (4, 1, '1998-01-01'),
10(4, 2, '1999-01-01'), (4, 3, '2000-01-01'),
11(4, 4, '2001-01-01'), (4, 5, '2002-01-01'),
12(5, 1, '1998-01-01'), (5, 2, '1999-01-01'),
13(5, 3, '2000-01-01'), (5, 4, '2001-01-01'),
14(5, 5, '2002-01-01');

이제 다음 쿼리를 고려해 보십시오:

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

실행 계획은 확장된 인덱스가 사용되는지 여부에 따라 달라집니다.

optimizer가 인덱스 확장을 고려하지 않을 때는 인덱스 k_d를 (d)만으로 취급합니다. 이 쿼리에 대한 EXPLAIN 결과는 다음과 같습니다:

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

optimizer가 인덱스 확장을 고려할 때는 k_d를 (d, i1, i2)로 취급합니다. 이 경우, 왼쪽 부분 인덱스 접두어 (d, i1)를 사용하여 더 나은 실행 계획을 만들 수 있습니다:

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

두 경우 모두 key는 optimizer가 세컨더리 인덱스 k_d를 사용한다는 것을 나타내지만, EXPLAIN 출력은 확장된 인덱스 사용으로 인한 다음과 같은 향상을 보여줍니다:

• key_len이 4 바이트에서 8 바이트로 증가하는데, 이는 키 조회가 d만이 아니라 d와 i1 컬럼을 사용함을 나타냅니다.

• ref 값이 const에서 const,const로 변하는데, 이는 키 조회가 하나가 아닌 두 개의 키 파트를 사용함을 의미합니다.

• rows 수가 5에서 1로 감소하는데, 이는 InnoDB가 결과를 생성하기 위해 검사해야 하는 행 수가 더 적어야 함을 나타냅니다.

• Extra 값이 Using where; Using index에서 Using index로 변합니다. 이는 데이터 행의 컬럼을 참조하지 않고, 인덱스만으로 행을 읽을 수 있음을 의미합니다.

확장된 인덱스 사용에 대한 optimizer 동작의 차이는 SHOW STATUS에서도 확인할 수 있습니다:

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

위 스테이트먼트들에는 테이블 캐시를 플러시하고 상태 카운터를 초기화하기 위한 FLUSH TABLES와 FLUSH STATUS가 포함되어 있습니다.

인덱스 확장 없이 SHOW STATUS는 다음과 같은 결과를 생성합니다:

1+-----------------------+-------+
2| Variable_name         | Value |
3+-----------------------+-------+
4| Handler_read_first    | 0     |
5| Handler_read_key      | 1     |
6| Handler_read_last     | 0     |
7| Handler_read_next     | 5     |
8| Handler_read_prev     | 0     |
9| Handler_read_rnd      | 0     |
10| Handler_read_rnd_next | 0     |
11+-----------------------+-------+

인덱스 확장을 사용하는 경우, SHOW STATUS는 다음과 같은 결과를 생성합니다. Handler_read_next 값이 5에서 1로 감소하는데, 이는 인덱스를 더 효율적으로 사용했음을 나타냅니다:

1+-----------------------+-------+
2| Variable_name         | Value |
3+-----------------------+-------+
4| Handler_read_first    | 0     |
5| Handler_read_key      | 1     |
6| Handler_read_last     | 0     |
7| Handler_read_next     | 1     |
8| Handler_read_prev     | 0     |
9| Handler_read_rnd      | 0     |
10| Handler_read_rnd_next | 0     |
11+-----------------------+-------+

optimizer_switch 시스템 변수의 use_index_extensions 플래그는 optimizer가 InnoDB 테이블의 세컨더리 인덱스를 어떻게 사용할지 결정할 때 프라이머리 키 컬럼들을 고려할지 여부를 제어할 수 있게 해 줍니다. 기본적으로 use_index_extensions는 활성화 상태입니다. 인덱스 확장 사용을 비활성화하면 성능이 향상될 수 있는지 확인하려면 다음 스테이트먼트를 사용하십시오:

1SET optimizer_switch = 'use_index_extensions=off';

optimizer에 의한 인덱스 확장 사용은 인덱스에서 키 파트 수(16)와 최대 키 길이(3072 바이트)에 대한 일반적인 제한의 적용을 받습니다.