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 출력은 확장 인덱스를 사용하여 다음과 같은 개선이 표시됩니다.
key_len는 4 바이트에서 8 바이트가 키 조회에서 열d뿐만 아니라d와i1도 사용되는 것을 보여줍니다.키 조회에서 하나가 아닌 두 개의 키 파트가 사용되기 때문에
ref값이const에서const,const로 변경되어 있습니다.rows수는 5에서 1로 감소하고InnoDB가 결과를 생성하기 위해 조사 할 필요가있는 행수가 줄어들 것을 보여줍니다.Extra값이Using where; Using index에서Using 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 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 바이트)에 따른 이용 제한에 따릅니다.