10.3.12 Invisible Indexes

10.3.12 Invisible Indexes

MySQL은 옵티마이저에서 사용하지 않는 인덱스인 invisible index를 지원합니다. 이 기능은 primary key(명시적이든 암시적이든)를 제외한 인덱스에 적용됩니다.

인덱스는 기본적으로 visible입니다. 새 인덱스에 대해 visibility를 명시적으로 제어하려면, CREATE TABLE, CREATE INDEX, 또는 ALTER TABLE의 인덱스 정의에 VISIBLE 또는 INVISIBLE 키워드를 사용하십시오:

1CREATE TABLE t1 (
2  i INT,
3  j INT,
4  k INT,
5  INDEX i_idx (i) INVISIBLE
6) ENGINE = InnoDB;
7CREATE INDEX j_idx ON t1 (j) INVISIBLE;
8ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;

기존 인덱스의 visibility를 변경하려면, ALTER TABLE ... ALTER INDEX 연산에서 VISIBLE 또는 INVISIBLE 키워드를 사용하십시오:

1ALTER TABLE t1 ALTER INDEX i_idx INVISIBLE;
2ALTER TABLE t1 ALTER INDEX i_idx VISIBLE;

인덱스가 visible인지 invisible인지에 대한 정보는 Information Schema STATISTICS 테이블이나 SHOW INDEX 출력에서 확인할 수 있습니다. 예를 들면 다음과 같습니다:

1mysql> SELECT INDEX_NAME, IS_VISIBLE
2       FROM INFORMATION_SCHEMA.STATISTICS
3       WHERE TABLE_SCHEMA = 'db1' AND TABLE_NAME = 't1';
4+------------+------------+
5| INDEX_NAME | IS_VISIBLE |
6+------------+------------+
7| i_idx      | YES        |
8| j_idx      | NO         |
9| k_idx      | NO         |
10+------------+------------+

invisible index를 사용하면, 인덱스가 실제로 필요하다고 판명되어 되돌려야 할 파괴적인 변경을 하지 않고도, 쿼리 성능에 대한 인덱스 제거 효과를 테스트할 수 있습니다. 큰 테이블의 경우 인덱스를 드롭하고 다시 추가하는 것은 비용이 많이 들 수 있지만, invisible로 만들거나 visible로 만드는 작업은 빠르고 인플레이스 연산입니다.

invisible로 만든 인덱스가 실제로 필요하거나 옵티마이저에서 사용되는 경우, 해당 테이블의 쿼리에 대해 그 부재의 영향을 여러 가지 방식으로 알아차릴 수 있습니다:

• invisible index를 참조하는 인덱스 힌트를 포함하는 쿼리에서 에러가 발생합니다.

• Performance Schema 데이터에서 영향을 받는 쿼리의 워크로드 증가가 나타납니다.

• 쿼리의 EXPLAIN 실행 계획이 달라집니다.

• 이전에는 나타나지 않았던 쿼리가 slow query log에 나타납니다.

optimizer_switch 시스템 변수의 use_invisible_indexes 플래그는 옵티마이저가 쿼리 실행 계획 구성 시 invisible index를 사용할지 여부를 제어합니다. 플래그가 off(기본값)이면, 옵티마이저는 invisible index를 무시합니다(이 플래그 도입 이전과 동일한 동작). 플래그가 on이면, invisible index는 여전히 invisible이지만, 옵티마이저는 실행 계획 구성을 위해 이를 고려합니다.

SET_VAR 옵티마이저 힌트를 사용하여 optimizer_switch의 값을 일시적으로 업데이트하면, 단일 쿼리의 기간 동안만 invisible index를 활성화할 수 있습니다. 예시는 다음과 같습니다:

1mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch = 'use_invisible_indexes=on') */
2     >     i, j FROM t1 WHERE j >= 50\G
3*************************** 1. row ***************************
4           id: 1
5  select_type: SIMPLE
6        table: t1
7   partitions: NULL
8         type: range
9possible_keys: j_idx
10          key: j_idx
11      key_len: 5
12          ref: NULL
13         rows: 2
14     filtered: 100.00
15        Extra: Using index condition
16
17mysql> EXPLAIN SELECT i, j FROM t1 WHERE j >= 50\G
18*************************** 1. row ***************************
19           id: 1
20  select_type: SIMPLE
21        table: t1
22   partitions: NULL
23         type: ALL
24possible_keys: NULL
25          key: NULL
26      key_len: NULL
27          ref: NULL
28         rows: 5
29     filtered: 33.33
30        Extra: Using where

인덱스 visibility는 인덱스 유지 관리에는 영향을 미치지 않습니다. 예를 들어, 인덱스는 테이블 행 변경에 따라 계속 업데이트되고, unique index는 인덱스가 visible인지 invisible인지와 상관없이 컬럼에 대한 중복 삽입을 방지합니다.

명시적인 primary key가 없는 테이블은, NOT NULL 컬럼에 대해 아무 UNIQUE 인덱스나 가지고 있다면 여전히 효과적인 암시적 primary key를 가질 수 있습니다. 이 경우, 첫 번째 그러한 인덱스는 명시적인 primary key와 동일한 제약을 테이블 행에 부과하며, 그 인덱스는 invisible로 만들 수 없습니다. 다음 테이블 정의를 고려해 보십시오:

1CREATE TABLE t2 (
2  i INT NOT NULL,
3  j INT NOT NULL,
4  UNIQUE j_idx (j)
5) ENGINE = InnoDB;

이 정의는 명시적인 primary key를 포함하지 않지만, NOT NULL 컬럼 j에 대한 인덱스는 primary key와 동일한 제약을 행에 부과하며 invisible로 만들 수 없습니다:

1mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
2ERROR 3522 (HY000): A primary key index cannot be invisible.

이제 테이블에 명시적인 primary key가 추가된다고 가정해 보겠습니다:

1ALTER TABLE t2 ADD PRIMARY KEY (i);

명시적인 primary key는 invisible로 만들 수 없습니다. 또한, j에 대한 unique index는 더 이상 암시적 primary key 역할을 하지 않으므로, 그 결과 invisible로 만들 수 있습니다:

1mysql> ALTER TABLE t2 ALTER INDEX j_idx INVISIBLE;
2Query OK, 0 rows affected (0.03 sec)