Invisible index 활용: 인덱스 제거 전 안전한 영향도 검증
MySQL Invisible index로 인덱스 제거의 실행 계획과 운영 영향을 사전에 검증하고 안전하게 복구하는 절차를 정리한다.
1. 인덱스를 바로 삭제하면 되돌리는 비용이 크다
사용되지 않는 인덱스는 쓰기 비용과 저장 공간을 늘린다. INSERT, UPDATE, DELETE가 실행될 때마다 관련 B-tree도 수정해야 하며, 변경 페이지는 Buffer Pool과 redo log에 추가 부담을 준다. 백업 크기와 복구 시간, 통계 수집 대상도 함께 늘어난다. 따라서 불필요한 인덱스를 정리하는 일은 중요한 운영 최적화다.
문제는 DROP INDEX가 되돌리기 어려운 변경이라는 점이다. 삭제 직후 예상하지 못한 배치 SQL이나 월말 보고서가 느려지면 인덱스를 다시 생성해야 한다. 큰 테이블에서 인덱스 생성은 긴 시간, 추가 I/O, 임시 공간, metadata lock 대기를 유발할 수 있다.
MySQL 8.0의 Invisible index는 이 위험을 줄이는 중간 단계다. 인덱스를 물리적으로 유지하면서 기본 optimizer 후보에서만 제외한다. 운영자는 삭제 후와 유사한 실행 계획을 먼저 관찰하고, 문제가 발견되면 빠르게 VISIBLE로 되돌릴 수 있다.
핵심 목적은 “인덱스를 숨기면 비용이 사라진다”가 아니다. 삭제 여부를 판단하기 위한 가역적인 실행 계획 실험이다.
2. Invisible index의 내부 의미
Secondary index를 INVISIBLE로 바꾸면 다음 특성이 적용된다.
- 인덱스 B-tree와 기존 엔트리는 그대로 남는다.
- DML은 계속 인덱스 엔트리를 추가·수정·삭제한다.
- uniqueness와 같은 제약 의미도 계속 적용된다.
- 기본 상태의 optimizer는 해당 인덱스를 접근 경로 후보에서 제외한다.
- 세션에서
optimizer_switch='use_invisible_indexes=on'을 사용하면 비교 실험에 다시 포함할 수 있다. VISIBLE전환은 인덱스 재생성이 아니라 가시성 변경이므로, 일반적인 재생성보다 복구가 빠르다.
따라서 Invisible 상태에서는 읽기 계획 변화는 시험할 수 있지만, 인덱스를 실제로 삭제했을 때의 쓰기량·redo·저장 공간 감소는 측정할 수 없다. 이 구분을 놓치면 “숨겼는데 쓰기 성능이 개선되지 않는다”는 잘못된 결론에 도달한다.
flowchart LR
Q[SQL 최적화 시작] --> S{use_invisible_indexes}
S -->|off: 기본값| V[VISIBLE 인덱스만 후보]
S -->|on: 시험 세션| A[VISIBLE + INVISIBLE 인덱스 후보]
V --> P[비용 기반 실행 계획 선택]
A --> P
D[DML 실행] --> M[VISIBLE과 INVISIBLE 인덱스 모두 유지]
2.1 Primary key는 숨길 수 없다
MySQL의 clustered index 역할을 하는 PRIMARY KEY는 단순한 보조 접근 경로가 아니다. InnoDB 행 저장 구조의 기준이므로 Invisible index로 전환할 수 없다. 다음 문장은 지원되지 않는 작업을 설명하기 위한 예시이며 실행 대상이 아니다.
ALTER TABLE orders ALTER INDEX PRIMARY INVISIBLE;
또한 foreign key 제약을 유지하는 데 반드시 필요한 인덱스는 가시성 변경이 제한될 수 있다. 먼저 SHOW CREATE TABLE과 information_schema.STATISTICS로 제약 및 인덱스 구성을 확인해야 한다.
3. 가시성 변경이 실행 계획을 바꾸는 과정
Invisible 전환은 인덱스 페이지를 삭제하지 않는다. 대신 optimizer가 후보 접근 경로를 구성할 때 해당 인덱스를 기본 후보 집합에서 제외한다. 이후 optimizer는 남은 인덱스의 ref, range, index-order scan과 table scan 비용을 다시 비교한다.
예를 들어 (customer_id, ordered_at, amount) 인덱스가 다음 역할을 동시에 수행하고 있다고 가정한다.
customer_id동등 조건을 빠르게 찾는다.ordered_at순서로 읽어filesort를 피한다.- 조회 열을 인덱스 안에서 충족하는 covering access를 제공한다.
이 인덱스를 숨기면 단순히 lookup 비용만 늘어나는 것이 아니다. Using filesort, 추가 page read, 더 많은 examined rows가 동시에 발생할 수 있다. 따라서 영향도 검증은 인덱스 사용 여부 하나가 아니라 access type, 선택된 key, 정렬 방식, 실제 지연 시간과 처리 행 수를 함께 봐야 한다.
4. 기능과 기본 세션 상태 확인
먼저 대상이 MySQL 8.0 계열인지 확인하고, 현재 세션이 Invisible index를 강제로 사용하도록 설정되어 있지 않은지 점검한다.
SELECT VERSION() AS mysql_version,
@@session.optimizer_switch LIKE '%use_invisible_indexes=on%'
AS invisible_index_test_mode;
실행 결과(MySQL 8.0.x):
mysql> SELECT VERSION() AS mysql_version,
-> @@session.optimizer_switch LIKE '%use_invisible_indexes=on%'
-> AS invisible_index_test_mode;
+---------------+---------------------------+
| mysql_version | invisible_index_test_mode |
+---------------+---------------------------+
| 8.0.46 | 0 |
+---------------+---------------------------+
1 row in set (0.00 sec)
invisible_index_test_mode가 0이면 기본 optimizer 동작으로 Invisible index를 제외한다. 운영 진단 세션에서 이전 실험 설정이 남아 있으면 결과를 잘못 해석할 수 있으므로 매번 확인하는 편이 안전하다.
5. 재현 환경: 기준 실행 계획 만들기
다음 예제는 2,000건의 주문을 만들고 customer_id와 주문 시각을 기준으로 조회한다. idx_customer_date_amount는 조건, 정렬, projection을 함께 지원한다. 재귀 CTE의 깊이를 고려해 예제 세션에서 cte_max_recursion_depth를 조정한다.
DROP TABLE IF EXISTS invisible_idx_orders;
CREATE TABLE invisible_idx_orders (
order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
customer_id BIGINT UNSIGNED NOT NULL,
ordered_at DATETIME NOT NULL,
amount DECIMAL(10,2) NOT NULL,
status VARCHAR(20) NOT NULL,
PRIMARY KEY (order_id),
KEY idx_customer_date_amount (customer_id, ordered_at, amount)
) ENGINE=InnoDB;
SET SESSION cte_max_recursion_depth = 2500;
INSERT INTO invisible_idx_orders
(customer_id, ordered_at, amount, status)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 2000
)
SELECT MOD(n, 200),
TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n MINUTE,
MOD(n, 500) + 0.50,
CASE WHEN MOD(n, 5) = 0 THEN 'PAID' ELSE 'NEW' END
FROM seq;
ANALYZE TABLE invisible_idx_orders;
SELECT COUNT(*) AS rows_loaded
FROM invisible_idx_orders;
EXPLAIN
SELECT order_id, ordered_at, amount
FROM invisible_idx_orders
WHERE customer_id = 42
ORDER BY ordered_at;
실행 결과(MySQL 8.0.x):
아래 EXPLAIN 표는 검증된 출력에서 계획 판단에 필요한 핵심 열만 발췌했다.
mysql> CREATE TABLE invisible_idx_orders (...);
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO invisible_idx_orders (...) WITH RECURSIVE seq AS (...) SELECT ...;
Query OK, 2000 rows affected (0.01 sec)
Records: 2000 Duplicates: 0 Warnings: 0
mysql> SELECT COUNT(*) AS rows_loaded FROM invisible_idx_orders;
+-------------+
| rows_loaded |
+-------------+
| 2000 |
+-------------+
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT ... WHERE customer_id = 42 ORDER BY ordered_at;
+------+--------------------------+------+-------------+
| type | key | rows | Extra |
+------+--------------------------+------+-------------+
| ref | idx_customer_date_amount | 10 | Using index |
+------+--------------------------+------+-------------+
1 row in set (0.00 sec)
이 예제에서 확인할 안정적인 신호는 추정 rows의 정확한 숫자가 아니라 type=ref, key=idx_customer_date_amount, Using index다. 통계 샘플링과 MySQL minor version에 따라 비용 및 추정 행 수는 달라질 수 있다.
6. 인덱스를 숨기고 세 가지 계획을 비교한다
이제 인덱스를 INVISIBLE로 전환한다. information_schema.STATISTICS.IS_VISIBLE은 메타데이터 상태를 확인하는 안정적인 관측점이다. 이어서 기본 세션과 시험 세션의 EXPLAIN을 비교한다.
ALTER TABLE invisible_idx_orders
ALTER INDEX idx_customer_date_amount INVISIBLE;
SELECT DISTINCT INDEX_NAME, NON_UNIQUE, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'invisible_idx_orders'
ORDER BY INDEX_NAME;
EXPLAIN
SELECT order_id, ordered_at, amount
FROM invisible_idx_orders
WHERE customer_id = 42
ORDER BY ordered_at;
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
EXPLAIN
SELECT order_id, ordered_at, amount
FROM invisible_idx_orders
WHERE customer_id = 42
ORDER BY ordered_at;
실행 결과(MySQL 8.0.x):
두 EXPLAIN 표는 검증된 출력에서 핵심 열만 발췌했다.
mysql> ALTER TABLE invisible_idx_orders
-> ALTER INDEX idx_customer_date_amount INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT INDEX_NAME, NON_UNIQUE, IS_VISIBLE ...;
+--------------------------+------------+------------+
| INDEX_NAME | NON_UNIQUE | IS_VISIBLE |
+--------------------------+------------+------------+
| idx_customer_date_amount | 1 | NO |
| PRIMARY | 0 | YES |
+--------------------------+------------+------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT ... WHERE customer_id = 42 ORDER BY ordered_at;
+------+-------+------+-----------------------------+
| type | key | rows | Extra |
+------+-------+------+-----------------------------+
| ALL | NULL | 2000 | Using where; Using filesort |
+------+-------+------+-----------------------------+
1 row in set (0.00 sec)
mysql> SET SESSION optimizer_switch = 'use_invisible_indexes=on';
Query OK, 0 rows affected (0.00 sec)
mysql> EXPLAIN SELECT ... WHERE customer_id = 42 ORDER BY ordered_at;
+------+--------------------------+------+-------------+
| type | key | rows | Extra |
+------+--------------------------+------+-------------+
| ref | idx_customer_date_amount | 10 | Using index |
+------+--------------------------+------+-------------+
1 row in set (0.00 sec)
해석 순서는 다음과 같다.
IS_VISIBLE=NO가 실제 메타데이터 변경을 확인한다.- 기본 세션에서는 후보 인덱스가 없어
type=ALL,key=NULL,Using filesort가 나타난다. use_invisible_indexes=on인 시험 세션에서는 다시idx_customer_date_amount를 선택한다.- 두 계획의 차이가 크다면 삭제 시 회귀 가능성이 있다는 뜻이다.
use_invisible_indexes=on은 운영 전체에 적용할 우회책이 아니다. 특정 진단 세션에서 “이 인덱스가 있을 때와 없을 때”를 비교하기 위한 도구로 제한해야 한다. 애플리케이션 연결 풀의 일반 세션에 설정하면 실제 삭제 영향도를 관찰할 수 없게 된다.
7. 안전하게 원복하고 상태를 증명한다
회귀가 발견되었거나 관찰 기간을 끝내야 한다면 즉시 VISIBLE로 되돌린다. 변경 후 메타데이터를 다시 조회해 의도한 상태인지 확인한다.
SET SESSION optimizer_switch = 'use_invisible_indexes=off';
ALTER TABLE invisible_idx_orders
ALTER INDEX idx_customer_date_amount VISIBLE;
SELECT DISTINCT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'invisible_idx_orders'
ORDER BY INDEX_NAME;
EXPLAIN
SELECT order_id, ordered_at, amount
FROM invisible_idx_orders
WHERE customer_id = 42
ORDER BY ordered_at;
DROP TABLE invisible_idx_orders;
실행 결과(MySQL 8.0.x):
EXPLAIN 표는 검증된 출력에서 핵심 열만 발췌했다.
mysql> ALTER TABLE invisible_idx_orders
-> ALTER INDEX idx_customer_date_amount VISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT DISTINCT INDEX_NAME, IS_VISIBLE ...;
+--------------------------+------------+
| INDEX_NAME | IS_VISIBLE |
+--------------------------+------------+
| idx_customer_date_amount | YES |
| PRIMARY | YES |
+--------------------------+------------+
2 rows in set (0.00 sec)
mysql> EXPLAIN SELECT ... WHERE customer_id = 42 ORDER BY ordered_at;
+------+--------------------------+------+-------------+
| type | key | rows | Extra |
+------+--------------------------+------+-------------+
| ref | idx_customer_date_amount | 10 | Using index |
+------+--------------------------+------+-------------+
1 row in set (0.00 sec)
mysql> DROP TABLE invisible_idx_orders;
Query OK, 0 rows affected (0.00 sec)
실제 운영에서는 ALTER TABLE이 짧은 metadata lock을 요구할 수 있다. 장기 트랜잭션이나 미완료 DDL이 있는지 확인하고, 변경 창과 lock_wait_timeout 정책에 맞춰 실행해야 한다. 가시성 변경이 인덱스 재구축을 요구하지 않는다는 사실과 metadata lock 대기가 없다는 뜻은 서로 다르다.
8. UNIQUE Invisible index도 제약은 계속 적용한다
Invisible은 optimizer의 조회 후보 여부를 뜻한다. unique index를 숨겨도 중복 데이터 허용으로 바뀌지 않는다. 다음 예제는 Invisible unique index가 중복 이메일 입력을 계속 차단하는지 확인한다.
DROP TABLE IF EXISTS invisible_unique_demo;
CREATE TABLE invisible_unique_demo (
member_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(255) NOT NULL,
member_name VARCHAR(50) NOT NULL,
PRIMARY KEY (member_id),
UNIQUE KEY ux_email (email) INVISIBLE
) ENGINE=InnoDB;
INSERT INTO invisible_unique_demo (email, member_name)
VALUES ('dba@example.test', '첫 번째 계정');
INSERT IGNORE INTO invisible_unique_demo (email, member_name)
VALUES ('dba@example.test', '중복 계정');
SHOW WARNINGS;
SELECT COUNT(*) AS stored_rows,
MIN(member_name) AS stored_name
FROM invisible_unique_demo;
DROP TABLE invisible_unique_demo;
실행 결과(MySQL 8.0.x):
mysql> DROP TABLE IF EXISTS invisible_unique_demo;
Query OK, 0 rows affected (0.00 sec)
mysql> CREATE TABLE invisible_unique_demo (
-> member_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
-> email VARCHAR(255) NOT NULL,
-> member_name VARCHAR(50) NOT NULL,
-> PRIMARY KEY (member_id),
-> UNIQUE KEY ux_email (email) INVISIBLE
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO invisible_unique_demo (email, member_name)
-> VALUES ('dba@example.test', '첫 번째 계정');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT IGNORE INTO invisible_unique_demo (email, member_name)
-> VALUES ('dba@example.test', '중복 계정');
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> SHOW WARNINGS;
+---------+------+-----------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------------------------------------------+
| Warning | 1062 | Duplicate entry 'dba@example.test' for key 'invisible_unique_demo.ux_email' |
+---------+------+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT COUNT(*) AS stored_rows,
-> MIN(member_name) AS stored_name
-> FROM invisible_unique_demo;
+-------------+-------------------+
| stored_rows | stored_name |
+-------------+-------------------+
| 1 | 첫 번째 계정 |
+-------------+-------------------+
1 row in set (0.00 sec)
mysql> DROP TABLE invisible_unique_demo;
Query OK, 0 rows affected (0.00 sec)
중복 입력은 Duplicate entry 경고를 만들며, 저장된 행은 한 건으로 유지된다. 즉, unique index를 Invisible로 전환한 기간에도 DML 유지 비용과 무결성 제약은 그대로다. unique index 삭제를 검토할 때는 조회 회귀뿐 아니라 데이터 모델이 해당 제약에 의존하는지도 별도로 검토해야 한다.
9. 운영 검증은 단계적으로 진행한다
9.1 후보 선정
sys.schema_unused_indexes는 후보를 찾는 출발점으로 사용할 수 있지만, 곧바로 삭제 목록으로 해석하면 안 된다. 이 뷰는 Performance Schema의 관측 기간에 의존한다. 서버 재시작 직후, 월간 배치 실행 전, 트래픽이 낮은 기간에는 실제로 필요한 인덱스도 미사용으로 보일 수 있다.
후보 선정에는 다음 자료를 함께 사용한다.
- 충분한 기간의 index I/O 사용 통계
- slow query log와 digest별 쿼리 목록
- 배치·정산·리포트 일정
SHOW CREATE TABLE의 unique 및 foreign key 관계- 동일 prefix를 가진 중복 인덱스 여부
- write latency, redo 생성량, Buffer Pool pressure
9.2 변경 전 기준선
가시성을 바꾸기 전에 최소한 다음 기준선을 남긴다.
- 대표 쿼리의
EXPLAIN또는EXPLAIN ANALYZE - digest별 호출 수, 평균·상위 지연 시간, examined rows
- CPU, 스토리지 읽기 IOPS, Buffer Pool hit 상태
- lock wait와 thread concurrency
- 복제 지연 또는 Aurora replica lag
단일 SQL의 평균 지연 시간만으로는 부족하다. 인덱스가 숨겨지면 같은 행을 찾더라도 더 많은 페이지를 읽고 동시 실행 쿼리끼리 Buffer Pool을 경쟁할 수 있기 때문이다.
9.3 제한된 범위에서 Invisible 전환
- low-risk 시간대에 변경한다.
information_schema.STATISTICS로IS_VISIBLE=NO를 확인한다.- 대표 SQL의 계획을 즉시 재확인한다.
- 짧은 초기 관찰 후 일간·주간·월간 워크로드 주기를 포함하도록 관찰 기간을 늘린다.
- 회귀 기준을 넘으면
VISIBLE로 원복한다. - 충분한 기간 동안 문제가 없을 때만 별도 변경으로
DROP INDEX를 수행한다.
10. 실패 모드와 흔한 오해
10.1 “미사용 통계가 0이면 삭제해도 된다”
관측 창에 실행되지 않은 쿼리는 통계에 나타나지 않는다. 재시작으로 통계가 초기화되었거나 드문 장애 대응 SQL이 있을 수 있다. 업무 주기와 쿼리 인벤토리를 함께 확인해야 한다.
10.2 “Invisible로 바꾸면 쓰기 성능이 좋아진다”
인덱스는 계속 유지된다. 쓰기 비용, redo, 페이지 분할, 디스크 공간 감소는 실제 DROP INDEX 이후에만 기대할 수 있다. Invisible 단계의 성공 조건은 쓰기 개선이 아니라 읽기 회귀가 없는지 확인하는 것이다.
10.3 “EXPLAIN 한 번이면 영향도를 알 수 있다”
parameter 값, 통계, 데이터 분포, buffer cache 상태에 따라 선택 계획이 달라진다. 동일 digest라도 선택도가 높은 값과 낮은 값을 나눠 확인하고 실제 지연 시간 지표를 함께 봐야 한다.
10.4 “필요하면 항상 즉시 VISIBLE로 바꿀 수 있다”
가시성 원복 자체는 인덱스 재생성보다 가볍지만 metadata lock을 얻어야 한다. 장기 트랜잭션이 DDL을 막는 환경에서는 원복도 대기할 수 있다. 변경 전 blocking session 확인과 타임아웃 정책이 필요하다.
10.5 시험 세션 설정을 운영 결론에 섞는다
use_invisible_indexes=on 세션에서 성능을 측정하면 숨긴 인덱스를 계속 사용할 수 있다. 기본 애플리케이션 세션의 결과와 시험 세션 결과를 구분해 기록해야 한다.
10.6 복합 인덱스의 여러 역할을 놓친다
한 인덱스는 WHERE, JOIN, ORDER BY, GROUP BY, covering을 동시에 지원할 수 있다. 특정 lookup 쿼리에서 다른 인덱스로 대체 가능하더라도 정렬이나 조인 계획이 악화될 수 있다.
11. Aurora MySQL에서의 운영 해석
Aurora MySQL v3는 MySQL 8.0 호환 계열이므로 Invisible index 기능을 사용할 수 있지만, 실제 지원 범위는 사용 중인 engine version에서 확인해야 한다. Aurora 전용 함수는 Community MySQL 검증 컨테이너에서 실행되지 않으므로 다음 명령은 Aurora 클러스터에서만 확인한다.
SELECT AURORA_VERSION();
SELECT VERSION();
운영 판단에서는 다음 차이를 고려한다.
- 인덱스를 숨겨도 Aurora의 분산 스토리지에 있는 인덱스 구조와 DML 유지 비용은 남는다.
- reader instance마다 서로 다른 실제 쿼리 집합이 실행될 수 있으므로 writer 지표만 보면 보고서·읽기 전용 워크로드 회귀를 놓칠 수 있다.
- Performance Insights 또는 Database Insights의 DB load, wait, SQL digest 변화를 writer와 reader에서 함께 비교한다.
- replica lag, failover, connection 재분산 시 평소와 다른 SQL이 실행될 수 있으므로 정상 시간대만 관찰해서는 안 된다.
- 최종
DROP INDEX는 별도 변경으로 수행하고, 스토리지·I/O 개선은 삭제 이후의 지표로 평가한다.
Aurora의 스토리지 구조가 관리형이라고 해서 optimizer의 index 선택 위험이 사라지는 것은 아니다. 오히려 다수 reader와 연결 라우팅 때문에 관찰 범위를 명확히 정의해야 한다.
12. 실행 체크리스트
변경 전
- 대상은
PRIMARY KEY
Invisible 관찰 중
-
information_schema.STATISTICS.IS_VISIBLE='NO' - 기본 세션에
use_invisible_indexes=on -
type,key,Extra - 회귀 임계치와
VISIBLE
최종 삭제 전
-
DROP INDEX
13. 결론
Invisible index는 불필요한 인덱스를 자동으로 판별하는 기능이 아니다. 물리 구조와 제약을 유지한 채 optimizer 선택만 바꾸어, 인덱스 삭제의 읽기 영향도를 가역적으로 검증하는 안전장치다.
안전한 절차는 후보 통계만 보고 바로 삭제하는 것이 아니라 후보 선정 → 기준선 확보 → Invisible 전환 → 충분한 업무 주기 관찰 → 회귀 시 VISIBLE 원복 → 별도 DROP 변경 순서로 진행하는 것이다. 다음 단계에서는 이 관찰 결과를 EXPLAIN ANALYZE, Performance Schema digest, 실제 I/O 지표와 연결해 실행 계획 변화가 서버 전체 부하에 미치는 영향을 판단해야 한다.