Table I/O와 index usage로 사용되지 않는 인덱스 진단하기
Performance Schema의 Table I/O 통계로 인덱스 사용 흔적을 관찰하고 안전한 검증과 제거 절차를 수립하는 방법을 정리한다.
인덱스는 읽기 성능을 높이지만 무료가 아니다. 보조 인덱스가 늘어나면 INSERT, UPDATE, DELETE 때마다 추가 B+Tree를 관리해야 하고, Buffer Pool과 백업 공간, redo/undo 처리량, 복제 적용 비용에도 영향을 준다. 그렇다고 사용 횟수가 낮아 보인다는 이유만으로 인덱스를 바로 삭제하면 월말 배치, 장애 대응 쿼리, 외래 키 검사, 드물지만 중요한 관리 작업이 갑자기 느려질 수 있다.
따라서 “사용되지 않는 인덱스” 진단은 단순 조회가 아니라 관측 기간을 설계하고, 통계의 범위를 이해하고, 스키마 의미와 실행 계획을 함께 검토한 뒤, 되돌릴 수 있는 방식으로 검증하는 과정이어야 한다. 이 글에서는 MySQL 8.0 이상을 기준으로 performance_schema.table_io_waits_summary_by_index_usage와 sys.schema_unused_indexes를 해석하고, 후보 인덱스를 안전하게 정리하는 운영 절차를 설명한다.
1. 먼저 구분해야 할 세 가지 질문
인덱스 정리 작업에서는 서로 다른 질문을 한 지표로 답하려는 실수가 자주 발생한다.
- 이 인덱스를 통해 행을 읽은 적이 있는가?
- Table I/O index usage 통계가 직접적으로 답하는 질문이다.
- 이 인덱스가 쓰기 비용과 저장 공간을 얼마나 증가시키는가?
- 인덱스 크기, DML 처리량, page split, redo 발생량 등을 함께 봐야 한다.
- 이 인덱스를 제거해도 데이터 무결성과 모든 중요 실행 계획이 안전한가?
UNIQUE, 외래 키, 중복·접두 관계, Invisible index 시험, 대표 쿼리 검증이 필요하다.
COUNT_STAR = 0은 첫 번째 질문에 대한 관측값일 뿐이다. 이것만으로 두 번째와 세 번째 질문까지 해결되지는 않는다.
2. Table I/O index usage의 내부 의미
performance_schema.table_io_waits_summary_by_index_usage는 스토리지 엔진이 테이블 행에 접근할 때 발생한 논리적 table I/O wait event를 테이블과 인덱스 단위로 집계한다. 운영체제의 디스크 read/write 바이트를 직접 보여주는 표가 아니며, Buffer Pool hit와 물리 디스크 접근을 구분하는 저장장치 지표도 아니다.
주요 열은 다음과 같이 해석한다.
| 열 | 운영 해석 |
|---|---|
OBJECT_SCHEMA, OBJECT_NAME |
관측 대상 스키마와 테이블 |
INDEX_NAME |
행 접근에 사용된 인덱스. NULL은 특정 인덱스에 귀속되지 않은 접근을 뜻함 |
COUNT_STAR |
읽기·쓰기 이벤트의 전체 횟수 |
COUNT_READ, COUNT_FETCH |
행 읽기와 fetch 활동 |
COUNT_WRITE |
insert/update/delete 계열 활동의 합계 |
SUM_TIMER_WAIT |
계측된 wait 시간의 합계 |
중요한 제한이 있다. 보조 인덱스 하나를 유지하는 데 든 모든 쓰기 비용이 그 인덱스 행의 COUNT_WRITE로 직접 나타난다고 해석하면 안 된다. 이 표는 “각 인덱스 B+Tree가 물리적으로 몇 번 기록되었는가”를 측정하는 인덱스 write amplification 표가 아니다. 사용 흔적이 없는 보조 인덱스의 비용을 평가하려면 information_schema.innodb_index_stats, 테이블 DML 비율, redo 처리량, Buffer Pool 압력 등을 별도로 결합해야 한다.
flowchart LR
Q[SQL 실행] --> O[Optimizer가 접근 경로 선택]
O --> H[Storage Engine handler 호출]
H --> P[Performance Schema 계측]
P --> T[table_io_waits_summary_by_index_usage]
T --> U[사용 흔적 후보 선별]
U --> M[UNIQUE·FK·중복 관계 확인]
M --> I[Invisible index 검증]
I --> D{성능·무결성 이상 여부}
D -->|이상 있음| R[VISIBLE로 즉시 복원]
D -->|이상 없음| X[변경 승인 후 제거]
이 흐름에서 Performance Schema 통계는 후보 선별 단계에 있다. 삭제 판단의 마지막 단계가 아니다.
3. 재현 가능한 작은 관측 실험
다음 예제는 테스트용 테이블을 만들고 일부 인덱스만 의도적으로 사용한다. 공유 운영 서버에서는 Table I/O 요약표를 임의로 TRUNCATE하지 말아야 한다. 이 예제의 초기화는 격리된 검증 인스턴스를 전제로 한다.
SET NAMES utf8mb4;
DROP TABLE IF EXISTS user_access_probe;
CREATE TABLE user_access_probe (
id BIGINT NOT NULL,
tenant_id BIGINT NOT NULL,
status VARCHAR(20) NOT NULL,
email VARCHAR(120) NOT NULL,
created_at DATETIME NOT NULL,
payload VARCHAR(200) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_email (email),
KEY idx_tenant_status (tenant_id, status),
KEY idx_created_at (created_at),
KEY idx_status (status)
) ENGINE=InnoDB;
INSERT INTO user_access_probe
(id, tenant_id, status, email, created_at, payload)
VALUES
(1, 10, 'READY', 'u01@example.test', '2026-08-01 09:00:00', 'alpha'),
(2, 10, 'RUNNING', 'u02@example.test', '2026-08-02 09:00:00', 'bravo'),
(3, 10, 'READY', 'u03@example.test', '2026-08-03 09:00:00', 'charlie'),
(4, 20, 'READY', 'u04@example.test', '2026-08-04 09:00:00', 'delta'),
(5, 20, 'DONE', 'u05@example.test', '2026-08-05 09:00:00', 'echo'),
(6, 20, 'READY', 'u06@example.test', '2026-08-06 09:00:00', 'foxtrot'),
(7, 30, 'SUSPENDED', 'u07@example.test', '2026-08-07 09:00:00', 'golf'),
(8, 30, 'DONE', 'u08@example.test', '2026-08-08 09:00:00', 'hotel');
ANALYZE TABLE user_access_probe;
TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
SELECT payload
FROM user_access_probe FORCE INDEX (idx_tenant_status)
WHERE tenant_id = 10 AND status = 'READY';
SELECT COUNT(*) AS recent_rows
FROM user_access_probe FORCE INDEX (idx_created_at)
WHERE created_at >= '2026-08-05 00:00:00';
SELECT email
FROM user_access_probe
WHERE id = 4;
실행 결과(MySQL 8.0.x):
준비 DDL/DML과 핵심 조회 결과를 발췌했다.
mysql> CREATE TABLE user_access_probe (...);
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO user_access_probe ...;
Query OK, 8 rows affected (0.00 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> ANALYZE TABLE user_access_probe;
+-----------------------------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| mysql_tech_note.user_access_probe | analyze | status | OK |
+-----------------------------------+---------+----------+----------+
1 row in set (0.00 sec)
mysql> TRUNCATE TABLE performance_schema.table_io_waits_summary_by_index_usage;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT payload FROM user_access_probe FORCE INDEX (idx_tenant_status)
-> WHERE tenant_id = 10 AND status = 'READY';
+---------+
| payload |
+---------+
| alpha |
| charlie |
+---------+
2 rows in set (0.00 sec)
mysql> SELECT COUNT(*) AS recent_rows FROM user_access_probe FORCE INDEX (idx_created_at)
-> WHERE created_at >= '2026-08-05 00:00:00';
+-------------+
| recent_rows |
+-------------+
| 4 |
+-------------+
1 row in set (0.00 sec)
mysql> SELECT email FROM user_access_probe WHERE id = 4;
+------------------+
| email |
+------------------+
| u04@example.test |
+------------------+
1 row in set (0.00 sec)
이 실험에서는 idx_tenant_status, idx_created_at, PRIMARY에 읽기 흔적을 만든다. uk_email과 idx_status는 데이터를 적재할 때 유지되었지만, 통계 초기화 이후 조회 경로로는 사용하지 않았다. 바로 이 차이가 “읽기 사용 흔적”과 “인덱스 유지 비용”을 구분해야 하는 이유다.
4. 인덱스별 사용 흔적 읽기
다음 쿼리는 특정 테이블로 범위를 제한한다. sys.format_time()은 Performance Schema timer 값을 사람이 읽기 쉬운 시간 단위로 바꾼다.
SELECT COALESCE(INDEX_NAME, '<NULL>') AS index_name,
COUNT_STAR,
COUNT_READ,
COUNT_FETCH,
COUNT_WRITE,
sys.format_time(SUM_TIMER_WAIT) AS total_wait
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = DATABASE()
AND OBJECT_NAME = 'user_access_probe'
ORDER BY INDEX_NAME IS NULL, INDEX_NAME;
실행 결과(MySQL 8.0.x):
mysql> SELECT COALESCE(INDEX_NAME, '<NULL>') AS index_name,
-> COUNT_STAR,
-> COUNT_READ,
-> COUNT_FETCH,
-> COUNT_WRITE,
-> sys.format_time(SUM_TIMER_WAIT) AS total_wait
-> FROM performance_schema.table_io_waits_summary_by_index_usage
-> WHERE OBJECT_SCHEMA = DATABASE()
-> AND OBJECT_NAME = 'user_access_probe'
-> ORDER BY INDEX_NAME IS NULL, INDEX_NAME;
+-------------------+------------+------------+-------------+-------------+------------+
| index_name | COUNT_STAR | COUNT_READ | COUNT_FETCH | COUNT_WRITE | total_wait |
+-------------------+------------+------------+-------------+-------------+------------+
| idx_created_at | 4 | 4 | 4 | 0 | 14.78 us |
| idx_status | 0 | 0 | 0 | 0 | 0 ps |
| idx_tenant_status | 2 | 2 | 2 | 0 | 11.69 us |
| PRIMARY | 1 | 1 | 1 | 0 | 2.34 us |
| uk_email | 0 | 0 | 0 | 0 | 0 ps |
| <NULL> | 0 | 0 | 0 | 0 | 0 ps |
+-------------------+------------+------------+-------------+-------------+------------+
6 rows in set (0.00 sec)
판독할 때는 다음 순서를 권장한다.
COUNT_READ또는COUNT_FETCH가 0보다 크면 관측 기간에 해당 인덱스를 통한 읽기가 있었다.- 값이 0이면 관측 기간에 흔적이 없었다고만 결론 내린다.
SUM_TIMER_WAIT이 크더라도 호출 횟수가 매우 많아서 합계가 커졌을 수 있으므로 평균 지연과 호출량을 분리한다.INDEX_NAME IS NULL행은 특정 보조 인덱스의 사용 여부와 동일시하지 않는다.- 통계 표 자체를 조회하는 작업은 대상 사용자 테이블의 인덱스 사용 횟수를 늘리지 않는다.
통계가 0으로 보이는 대표 원인
- 인스턴스가 최근 재시작되었거나 failover되었다.
- 요약표를
TRUNCATE하여 통계가 초기화되었다. - 해당 업무가 아직 실행되지 않았다. 예를 들어 월말·분기말 배치가 관측 창 밖에 있다.
- 읽기 트래픽이 다른 replica나 Aurora Reader에서 수행되었다.
- Performance Schema consumer 또는 관련 instrument 설정이 기대와 다르다.
- 쿼리 형태나 데이터 분포가 바뀌어 Optimizer가 관측 기간에 다른 인덱스를 선택했다.
따라서 최소 관측 기간은 업무 주기를 포함해야 한다. 일간 API만 있는 시스템이라도 주간 리포트, 월말 정산, 백업 검증, 장애 대응 절차가 있다면 그 주기를 모두 포함하는 것이 안전하다.
5. sys.schema_unused_indexes는 후보 목록이다
sys.schema_unused_indexes는 Table I/O 통계를 읽기 쉬운 형태로 제공한다. MySQL 8.0의 이 뷰는 PRIMARY와 UNIQUE 인덱스를 후보에서 제외하지만, 삭제 검토 과정에서는 뷰의 필터를 암묵적으로 신뢰하지 말고 실제 인덱스 정의를 다시 확인해야 한다. 다음 쿼리는 후보와 information_schema.STATISTICS의 정의를 함께 보여준다.
SELECT u.object_schema,
u.object_name,
u.index_name,
s.NON_UNIQUE,
s.IS_VISIBLE,
GROUP_CONCAT(s.COLUMN_NAME
ORDER BY s.SEQ_IN_INDEX
SEPARATOR ', ') AS key_columns
FROM sys.schema_unused_indexes AS u
JOIN information_schema.STATISTICS AS s
ON s.TABLE_SCHEMA = u.object_schema
AND s.TABLE_NAME = u.object_name
AND s.INDEX_NAME = u.index_name
WHERE u.object_schema = DATABASE()
AND u.object_name = 'user_access_probe'
GROUP BY u.object_schema,
u.object_name,
u.index_name,
s.NON_UNIQUE,
s.IS_VISIBLE
ORDER BY u.index_name;
실행 결과(MySQL 8.0.x):
mysql> SELECT u.object_schema,
-> u.object_name,
-> u.index_name,
-> s.NON_UNIQUE,
-> s.IS_VISIBLE,
-> GROUP_CONCAT(s.COLUMN_NAME
-> ORDER BY s.SEQ_IN_INDEX
-> SEPARATOR ', ') AS key_columns
-> FROM sys.schema_unused_indexes AS u
-> JOIN information_schema.STATISTICS AS s
-> ON s.TABLE_SCHEMA = u.object_schema
-> AND s.TABLE_NAME = u.object_name
-> AND s.INDEX_NAME = u.index_name
-> WHERE u.object_schema = DATABASE()
-> AND u.object_name = 'user_access_probe'
-> GROUP BY u.object_schema,
-> u.object_name,
-> u.index_name,
-> s.NON_UNIQUE,
-> s.IS_VISIBLE
-> ORDER BY u.index_name;
+-----------------+-------------------+------------+------------+------------+-------------+
| object_schema | object_name | index_name | NON_UNIQUE | IS_VISIBLE | key_columns |
+-----------------+-------------------+------------+------------+------------+-------------+
| mysql_tech_note | user_access_probe | idx_status | 1 | YES | status |
+-----------------+-------------------+------------+------------+------------+-------------+
1 row in set (0.01 sec)
검증 결과에는 읽기 횟수가 0인 idx_status만 나타나고, 같은 조건의 uk_email은 나타나지 않는다. schema_unused_indexes가 UNIQUE 인덱스를 제외하기 때문이다. 그러나 이 제외 동작을 “UNIQUE 인덱스는 검토할 필요가 없다”로 해석해서는 안 된다. 애플리케이션이 email로 조회하지 않아도 uk_email은 중복 방지라는 데이터 무결성을 집행한다. 외래 키 검사를 지원하는 인덱스와 복제·운영 도구가 의존하는 인덱스도 단순 사용 횟수만으로 제거하면 안 된다.
결과가 비어 있을 때의 의미
Empty set은 모든 인덱스가 반드시 중요하다는 뜻이 아니다. 관측 기간에 각 인덱스가 한 번 이상 사용되었거나, 뷰의 제외 조건에 해당하거나, 계측 범위가 기대와 다를 수 있다. 반대로 후보가 많다고 해서 모두 불필요한 것도 아니다. 이 뷰는 삭제 명령을 생성하는 자동화 입력보다 검토 대기열로 사용하는 편이 안전하다.
6. 삭제 전 반드시 결합할 메타데이터
후보 인덱스가 나오면 다음 항목을 한 건씩 확인한다.
6.1 UNIQUE와 무결성 의미
PRIMARY KEY와UNIQUE KEY는 접근 경로인 동시에 제약 조건이다.- 사용 통계가 0이어도 중복 방지 기능은 계속 수행된다.
- 동일 컬럼의 비고유 인덱스가 있더라도 UNIQUE 의미를 대체하지 못한다.
6.2 외래 키 의존성
InnoDB는 외래 키 검사를 위해 적절한 인덱스를 요구한다. MySQL 버전과 제약 정의에 따라 인덱스를 삭제하려 할 때 DDL이 거부될 수 있지만, “DDL이 거부되면 안전하다”는 방식으로 운영 변경을 설계하면 안 된다. 먼저 information_schema.KEY_COLUMN_USAGE와 REFERENTIAL_CONSTRAINTS에서 관계를 확인한다.
6.3 중복 인덱스와 왼쪽 접두 관계
KEY (tenant_id)와 KEY (tenant_id, status)가 함께 있으면 앞의 인덱스가 중복 후보가 될 수 있다. 그러나 다음 요소가 다르면 단순 접두 비교만으로 결론 내리기 어렵다.
- 정렬 방향과 컬럼 순서
- prefix length
- collation
- uniqueness
- functional key part
- index visibility
- covering query에 필요한 뒤쪽 컬럼
sys.schema_redundant_indexes는 유용한 출발점이지만, 대표 쿼리의 EXPLAIN ANALYZE와 실제 지연 시간도 확인해야 한다.
6.4 크기와 변경 빈도
사용 흔적이 같은 두 후보라면 크기가 크고 DML이 빈번한 테이블의 인덱스를 우선 검토할 수 있다. 다만 information_schema.innodb_index_stats의 페이지 통계는 persistent statistics이며, 정밀한 실시간 물리 I/O 계측값은 아니다. 방향성과 우선순위 결정에 사용하고 절대적인 비용으로 단정하지 않는다.
7. Invisible index를 이용한 되돌릴 수 있는 검증
MySQL 8.0의 Invisible index는 인덱스 구조를 즉시 삭제하지 않고 Optimizer의 일반 후보에서 제외한다. 이 기능은 제거 전 검증 단계에 적합하다. 단, PRIMARY KEY는 invisible로 만들 수 없으며, Invisible 상태에서도 DML 시 인덱스 유지 비용은 계속 발생한다.
다음 예제는 테스트 인덱스의 가시성을 변경하고 메타데이터로 확인한 뒤 원상 복구한다.
ALTER TABLE user_access_probe
ALTER INDEX idx_status INVISIBLE;
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'user_access_probe'
AND INDEX_NAME = 'idx_status';
SET SESSION optimizer_switch = 'use_invisible_indexes=on';
SELECT @@session.optimizer_switch LIKE '%use_invisible_indexes=on%' AS invisible_enabled;
SET SESSION optimizer_switch = 'use_invisible_indexes=off';
ALTER TABLE user_access_probe
ALTER INDEX idx_status VISIBLE;
SELECT INDEX_NAME, IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
AND TABLE_NAME = 'user_access_probe'
AND INDEX_NAME = 'idx_status';
실행 결과(MySQL 8.0.x):
mysql> ALTER TABLE user_access_probe
-> ALTER INDEX idx_status INVISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT INDEX_NAME, IS_VISIBLE
-> FROM information_schema.STATISTICS
-> WHERE TABLE_SCHEMA = DATABASE()
-> AND TABLE_NAME = 'user_access_probe'
-> AND INDEX_NAME = 'idx_status';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| idx_status | NO |
+------------+------------+
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> SELECT @@session.optimizer_switch LIKE '%use_invisible_indexes=on%' AS invisible_enabled;
+-------------------+
| invisible_enabled |
+-------------------+
| 1 |
+-------------------+
1 row in set (0.00 sec)
mysql> SET SESSION optimizer_switch = 'use_invisible_indexes=off';
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE user_access_probe
-> ALTER INDEX idx_status VISIBLE;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SELECT INDEX_NAME, IS_VISIBLE
-> FROM information_schema.STATISTICS
-> WHERE TABLE_SCHEMA = DATABASE()
-> AND TABLE_NAME = 'user_access_probe'
-> AND INDEX_NAME = 'idx_status';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| idx_status | YES |
+------------+------------+
1 row in set (0.01 sec)
운영에서는 invisible 전환 전후로 다음을 비교한다.
- p95/p99 query latency와 timeout 비율
- examined rows, rows sent, CPU 사용률
- 대표 쿼리의
EXPLAIN ANALYZE - slow query log와 Performance Schema digest 변화
- replica lag와 writer 부하
- 배치 완료 시간과 운영 도구의 성공 여부
use_invisible_indexes=on은 검증 세션에서 invisible 인덱스를 다시 고려하게 할 수 있다. 이를 이용하면 인덱스를 물리적으로 재생성하지 않고 실행 계획 차이를 비교할 수 있다. 다만 전역 설정으로 켜기보다 제한된 진단 세션에서 사용해야 한다.
8. 관측 창을 설계하는 운영 절차
단계 1: 기준 시각과 초기화 사건을 기록한다
인스턴스 시작 시각, 최근 failover, Performance Schema 초기화 여부를 기록한다. 운영 인스턴스에서 통계 초기화를 강제로 수행하기보다 현재 누적값을 기준선으로 저장하고 일정 기간 뒤 delta를 계산하는 방식이 안전하다.
단계 2: 토폴로지 전체를 관측한다
writer 한 대만 보면 read replica에서 사용되는 인덱스를 놓친다. 각 인스턴스에서 동일한 스냅샷 쿼리를 실행하고, server_uuid, role, collection timestamp를 함께 저장한다.
단계 3: 업무 주기를 포함한다
최소한 다음 사건이 한 번 이상 포함되도록 관측 창을 잡는다.
- 일간·주간·월간 배치
- 정산과 리포트 생성
- 백업 및 복구 검증
- 데이터 보정 작업
- 장애 대응용 점검 쿼리
- 계절성 또는 이벤트 트래픽
단계 4: 후보에 위험 등급을 붙인다
| 등급 | 예시 | 기본 조치 |
|---|---|---|
| 제외 | PRIMARY, UNIQUE, 외래 키 의존 | 자동 제거 금지 |
| 고위험 | 사용 0이지만 월간 업무 미관측 | 관측 연장 |
| 중위험 | 낮은 사용량, 큰 인덱스, 대표 쿼리 존재 | Invisible 시험 |
| 저위험 | 장기 미사용, 비고유, 중복 관계 명확 | 변경 승인 후 시험 |
단계 5: Invisible 상태로 충분히 검증한다
관측 기간과 동일한 업무 주기를 가능하면 다시 통과시킨다. 긴급 복구 명령은 ALTER TABLE ... ALTER INDEX ... VISIBLE 형태로 사전에 준비한다.
단계 6: 한 번에 적은 수만 제거한다
여러 인덱스를 동시에 삭제하면 회귀 원인을 분리하기 어렵다. 작은 변경 단위로 적용하고, DDL 방식과 metadata lock, replica 적용 시간, 백업 창 영향을 검토한다.
9. Aurora MySQL에서의 해석 차이
Aurora MySQL에서도 Performance Schema 기반 진단 원리는 동일하지만, 관측 단위와 장애 전환 특성을 반드시 고려해야 한다.
- 인스턴스별 통계: writer와 각 Aurora Replica의 메모리 내 Performance Schema 통계는 서로 합쳐진 전역 값이 아니다. 읽기 endpoint의 트래픽은 여러 reader에 분산될 수 있다.
- failover와 재시작: 장애 전환이나 인스턴스 재시작 뒤 누적 통계가 이어진다고 가정하면 안 된다. 수집 시각과 DB instance identifier, role을 함께 보관한다.
- 스토리지 구조: Aurora의 분산 스토리지는 Community MySQL의 로컬 InnoDB 파일 배치와 다르다. 그렇더라도 보조 인덱스 유지에는 CPU, Buffer Pool, redo 성격의 변경 처리, 복제·스토리지 전송 경로 비용이 발생한다.
- 파라미터 관리: Performance Schema와 관련 consumer 설정은 DB parameter group 및 인스턴스 크기와 함께 검토한다. 계측을 무조건 확대하면 메모리와 관측 오버헤드가 증가할 수 있다.
- 서비스 지표 결합: Performance Insights 또는 Database Insights, CloudWatch의 DB load, DML latency, replica lag와 함께 전후를 비교한다. Table I/O 집계만으로 Aurora 전체 비용 절감을 계산하지 않는다.
Aurora에서는 특히 reader별로 “미사용”이더라도 writer 또는 다른 reader에서는 사용 중일 수 있다. 제거 판단은 cluster topology 전체의 증거를 합친 뒤 내려야 한다.
10. 흔한 오해와 실패 방식
“0회이므로 삭제해도 된다”
0은 통계가 살아 있는 관측 기간에 사용 흔적이 없다는 뜻이다. 미래 사용 여부와 무결성 의미는 알려주지 않는다.
“COUNT_WRITE가 0이면 유지 비용도 없다”
Table I/O 요약표는 보조 인덱스별 물리 쓰기 비용표가 아니다. 인덱스가 존재하면 테이블 DML 과정에서 구조를 유지해야 한다.
“운영 트래픽이 많은 하루면 충분하다”
트래픽 양보다 업무 종류가 중요하다. 하루 동안 수십억 건의 API 요청이 있어도 월말 정산 쿼리는 한 번도 실행되지 않을 수 있다.
“중복 인덱스는 긴 인덱스 하나만 남기면 항상 된다”
긴 인덱스는 더 많은 공간과 cache를 사용하며, 짧은 인덱스가 특정 쿼리에서 더 효율적일 수 있다. uniqueness와 covering 여부도 다를 수 있다.
“Invisible이면 비용이 사라진다”
Invisible index는 Optimizer 후보에서 숨길 뿐, DML 유지와 저장 공간 비용을 없애지 않는다. 성능 회귀 검증용 중간 상태다.
“실행 계획만 같으면 안전하다”
예상 계획이 같아도 실제 데이터 분포, concurrency, cache 상태에서 지연 시간이 달라질 수 있다. EXPLAIN ANALYZE, digest 통계, 서비스 지표를 함께 본다.
11. 실무 점검표
관측 준비
후보 검토
-
PRIMARY,UNIQUE -
sys.schema_redundant_indexes
변경과 검증
- 즉시
VISIBLE
12. 테스트 객체 정리
앞의 재현 예제를 실행했다면 다음 명령으로 테스트 객체를 제거한다.
DROP TABLE user_access_probe;
실행 결과(MySQL 8.0.x):
mysql> DROP TABLE user_access_probe;
Query OK, 0 rows affected (0.00 sec)
결론
Table I/O와 index usage 통계는 불필요한 인덱스를 자동 판정하는 도구가 아니라, 장기간 검토할 후보를 찾는 관측 도구다. 정확한 운영 판단에는 통계의 생존 기간, 전체 토폴로지, 업무 주기, UNIQUE·외래 키 의미, 인덱스 크기와 쓰기 부하, 대표 실행 계획이 함께 필요하다.
안전한 절차는 장기 관측 → 메타데이터 검토 → Invisible index 시험 → 서비스 지표 비교 → 소규모 제거 순서다. 다음 단계에서는 Performance Schema의 statement digest와 Table I/O 통계를 연결하여, 특정 인덱스를 실제로 사용하는 쿼리 패턴과 회귀 위험을 더 구체적으로 추적하는 방법을 다룰 수 있다.