Invisible Index

MySQL 용어집. MySQL 8.0의 Invisible Index 기능은 인덱스를 삭제하지 않고 옵티마이저의 인덱스 사용 여부를 제어할 수 있어, 안전한 성능 테스트와 유연한 인덱스 관리가 가능합니다.

Overlay Image Overlay Image
« Back to Glossary Index
MySQL Invisible Index - 보이지 않는 인덱스

MySQL 8.0에서는 특정 인덱스를 삭제하지 않고도 옵티마이저가 해당 인덱스를 사용하지 않도록 설정할 수 있는 Invisible Index (보이지 않는 인덱스) 기능을 제공합니다. 이 기능을 활용하면 인덱스의 실제 필요성을 쉽게 검증할 수 있으며, 필요할 경우 언제든지 다시 가시화할 수 있습니다.

Invisible Index 란?

Invisible Index는 인덱스를 완전히 제거하지 않고 ‘보이지 않게’ 처리하여 옵티마이저가 해당 인덱스를 전혀 참조하지 않도록 만드는 기능입니다. 이를 통해 DBA나 개발자는 인덱스를 Invisible 상태로 전환한 뒤 쿼리 성능 변화를 관찰함으로써, 해당 인덱스의 유용성을 판단할 수 있습니다.

주요 특징

  1. 인덱스 보존: 인덱스를 삭제하지 않고도 인덱스 활용 여부를 제어할 수 있어, 필요시 다시 쉽게 활성화 가능합니다.
  2. 안전한 성능 테스트: 성능 변화에 대한 검증을 위해 인덱스를 Invisible 상태로 전환해보고, 불필요하다면 최종적으로 삭제하여 자원을 절약할 수 있습니다.

구현 예시

아래 예제는 your_database 스키마를 사용한다고 가정합니다.

테이블 및 인덱스 생성

USE your_database;

CREATE TABLE employees (
  emp_no INT NOT NULL,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  PRIMARY KEY (emp_no)
);

CREATE INDEX idx_emp_no ON employees(emp_no);
CREATE INDEX idx_name ON employees(last_name);

현재 employees 테이블에는 PRIMARY KEY(emp_no), idx_emp_no(emp_no), idx_name(last_name) 총 3개의 인덱스가 있습니다.

인덱스 Invisible 처리

idx_emp_no 인덱스를 Invisible 상태로 전환하여 옵티마이저가 이 인덱스를 사용하지 않도록 합니다.

ALTER TABLE employees ALTER INDEX idx_emp_no INVISIBLE;

이제 옵티마이저는 idx_emp_no 인덱스를 전혀 사용하지 않습니다.

Invisible Index 상태 확인

INFORMATION_SCHEMA.STATISTICS 뷰를 통해 인덱스의 가시성을 확인할 수 있습니다. IS_VISIBLE 컬럼이 YES면 Visible, NO면 Invisible입니다.

SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE
FROM INFORMATION_SCHEMA.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
  AND TABLE_NAME = 'employees';

MySQL CLI에서의 예시 결과:

+------------+-------------+------------+
| INDEX_NAME | COLUMN_NAME | IS_VISIBLE |
+------------+-------------+------------+
| PRIMARY    | emp_no      | YES        |
| idx_emp_no | emp_no      | NO         |
| idx_name   | last_name   | YES        |
+------------+-------------+------------+
3 rows in set (0.00 sec)

idx_emp_no의 IS_VISIBLE가 NO로 표시되어 Invisible 상태임을 확인할 수 있습니다.

EXPLAIN으로 확인하기

last_name을 조건으로 하는 쿼리 실행 계획을 EXPLAIN을 통해 확인해봅니다.

EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

인덱스가 Visible 상태였다면 key 필드에 idx_name 인덱스를 사용하고, type 필드에 ref 형태의 접근 방식이 보일 가능성이 큽니다. 하지만 Invisible 상태로 전환한 뒤에는 인덱스를 사용할 수 없으므로, 다음과 같이 전체 테이블을 스캔하는 형태로 결과가 나올 수 있습니다.

예시 결과(가상의 값):

+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000000 |    10.00 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+---------+----------+-------+

여기서 type이 ALL로 표시되어 있으며, possible_keys나 key에 아무런 인덱스도 나타나지 않습니다. 이는 옵티마이저가 해당 쿼리에서 idx_name 인덱스를 전혀 사용하지 않고, 테이블 전체를 스캔(Full Table Scan)하고 있음을 의미합니다.

다시 Visible 상태로 되돌리기

테스트 후 인덱스가 필요하다면 다시 Visible로 설정할 수 있습니다.

ALTER TABLE employees ALTER INDEX idx_emp_no VISIBLE;

다시 상태를 조회하면 IS_VISIBLE이 YES로 표시되어 옵티마이저에 의해 해당 인덱스가 다시 고려됩니다.

정리

MySQL 8.0 Invisible Index 기능을 사용하면 인덱스를 완전히 제거하지 않고도 옵티마이저의 인덱스 활용 여부를 유연하게 변경할 수 있습니다. 이를 통해 DBA는 안전하게 성능 변화를 검증하고, 필요하지 않은 인덱스를 식별 및 제거함으로써 최적화된 인덱스 구조를 유지할 수 있습니다.

« Back to Glossary Index