MySQL 인덱스 중복과 낭비 진단: redundant index 식별과 안전한 제거

인덱스는 읽기 성능을 높이지만 공짜가 아니다. 인덱스가 하나 늘 때마다 INSERT, UPDATE, DELETE는 추가 B+Tree를 수정해야 하고, Buffer Pool과 저장 공간에는 새로운 페이지가 필요하다. 통계 수집, 백업, 복구, 복제 적용에도 간접 비용이 생긴다. 특히 스키마가 오래 운영되면 과거 장애 대응이나 쿼리 튜닝 과정에서 만든 인덱스가 서로 겹치면서, 현재는 효용보다 유지 비용이 큰 상태가 되기 쉽다.

그러나 “사용 횟수가 0이다” 또는 “앞쪽 컬럼이 같다”는 사실만으로 인덱스를 즉시 삭제해서는 안 된다. 관측 기간, 통계 초기화 시점, 제약 조건, 정렬 방향, prefix 길이, 쿼리 형태를 함께 검토해야 한다. 이 글은 MySQL 8.0 이상을 기준으로 중복 인덱스의 구조적 판정 원리와 안전한 제거 절차를 설명한다.

1. 중복 인덱스는 두 종류로 나누어 본다

운영에서 흔히 “중복 인덱스”라고 부르는 대상은 크게 완전 중복과 포함 관계 중복으로 나뉜다.

1.1 완전 중복

다음 두 인덱스처럼 컬럼, 순서, prefix 길이, 정렬 방향이 같은 경우다.

INDEX idx_a (customer_id, created_at)
INDEX idx_b (customer_id, created_at)

두 인덱스의 가시성, 인덱스 종류, 유일성 같은 속성까지 같다면 일반적으로 하나는 불필요하다. 다만 MySQL 버전과 DDL 경로에 따라 완전히 동일한 인덱스 생성이 경고 또는 오류의 대상이 될 수 있으므로, 새 배포 파이프라인에서는 애초에 생성되지 않도록 스키마 검사를 두는 편이 좋다.

1.2 포함 관계 중복

복합 B+Tree 인덱스는 왼쪽부터 정렬되므로 다음 구성에서는 idx_customer_created가 customer_id 단독 탐색에도 사용될 수 있다.

INDEX idx_customer (customer_id)
INDEX idx_customer_created (customer_id, created_at)

이때 idx_customer는 더 긴 인덱스의 leftmost prefix다. 구조만 보면 제거 후보지만, 곧바로 “두 인덱스의 성능이 같다”고 결론 내릴 수는 없다. 짧은 인덱스는 페이지당 더 많은 레코드를 담아 더 얕거나 작은 B+Tree가 될 수 있고, Buffer Pool 점유와 point lookup 비용에서 유리할 가능성이 있다. 반대로 긴 인덱스가 filtering, ordering, covering까지 제공하면 짧은 인덱스를 유지할 이유가 약해진다.

2. 왜 불필요한 인덱스가 운영 비용을 만든다

인덱스 유지 비용은 디스크 용량에만 머물지 않는다.

flowchart LR
    W[INSERT / UPDATE / DELETE] --> C[클러스터 인덱스 변경]
    C --> S1[보조 인덱스 1 변경]
    C --> S2[보조 인덱스 2 변경]
    C --> SN[보조 인덱스 N 변경]
    S1 --> L[Redo / Undo 및 페이지 변경]
    S2 --> L
    SN --> L
    L --> F[Flush·복제·백업 부담 증가]

주요 비용은 다음과 같다.

쓰기 증폭: 변경된 인덱스 페이지에 대한 Redo 기록과 dirty page가 늘어난다.
Buffer Pool 경쟁: 사용 가치가 낮은 인덱스 페이지가 자주 쓰이면 데이터 페이지나 핵심 인덱스 페이지를 밀어낼 수 있다.
페이지 분할과 단편화: 무작위 키가 포함된 인덱스는 insert 과정에서 분할과 낮은 page fill을 유발할 수 있다.
DDL·백업·복구 시간: 테이블 복사나 인덱스 재구성이 필요한 작업의 데이터량이 커진다.
Optimizer 선택지 증가: 후보가 지나치게 많으면 최적화 시간이 늘고, 비슷한 인덱스 사이에서 불안정한 계획 변경이 일어날 여지가 생긴다.
복제 적용 비용: Row-Based Replication에서도 Replica가 각 보조 인덱스를 유지해야 한다.

따라서 인덱스 정리는 저장 공간 회수 작업이 아니라 쓰기 경로와 운영 복잡성을 줄이는 스키마 관리 작업이다.

3. 구조적 중복을 판정할 때 확인할 속성

단순히 컬럼 이름만 비교하면 잘못된 판정을 내릴 수 있다. 최소한 다음 속성을 함께 본다.

확인 항목	잘못 판단하기 쉬운 이유
컬럼 순서	`(a, b)`와 `(b, a)`는 서로 대체하지 못한다.
유일성	`UNIQUE(a)`를 비유일 인덱스로 대체하면 데이터 무결성이 사라진다.
prefix 길이	`name(20)`과 `name(100)`은 선택도와 covering 범위가 다르다.
정렬 방향	MySQL 8.0 descending index의 `ASC`/`DESC` 조합은 정렬 최적화에 영향을 준다.
functional key part	표현식 인덱스는 원본 컬럼 인덱스와 동일하지 않다.
인덱스 종류	B+Tree, FULLTEXT, SPATIAL은 용도와 탐색 방식이 다르다.
가시성	Invisible index는 기본 Optimizer 후보에서 제외된다.
제약 조건	`PRIMARY KEY`, `UNIQUE`, Foreign Key 지원 인덱스는 무결성과 연관된다.

또한 (a, b, c)가 존재한다고 해서 (a, c)가 자동으로 중복인 것은 아니다. b 조건 없이 a = ? AND c = ?를 조회하면 긴 인덱스에서 c는 첫 범위 경계 이후이거나 탐색 범위를 줄이지 못할 수 있다. leftmost prefix는 “앞에서부터 연속된 키 부분”에 관한 규칙이다.

4. `sys.schema_redundant_indexes`로 1차 후보 찾기

MySQL 8.0의 sys schema에는 구조적으로 중복 가능성이 있는 인덱스를 찾는 schema_redundant_indexes view가 있다. 다음 재현 예제는 단일 컬럼 인덱스가 더 긴 복합 인덱스에 포함되는 상황을 만든다.

DROP TABLE IF EXISTS redundant_index_demo;

CREATE TABLE redundant_index_demo (
    id BIGINT NOT NULL AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL,
    customer_id BIGINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(12,2) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_order_no (order_no),
    KEY idx_customer (customer_id),
    KEY idx_customer_created (customer_id, created_at)
) ENGINE = InnoDB;

INSERT INTO redundant_index_demo
    (order_no, customer_id, created_at, amount)
VALUES
    ('ORD-001', 101, '2026-07-28 09:00:00', 12000.00),
    ('ORD-002', 101, '2026-07-28 09:05:00', 18000.00),
    ('ORD-003', 202, '2026-07-28 09:10:00', 9000.00);

SHOW INDEX FROM redundant_index_demo;

실행 결과(MySQL 8.0.x):

다음은 검증된 실행 결과에서 SHOW INDEX의 핵심 열만 발췌한 것이다.

mysql> CREATE TABLE redundant_index_demo (...);
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO redundant_index_demo ...;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM redundant_index_demo;
+----------------------+--------------+-------------+---------+
| Key_name             | Seq_in_index | Column_name | Visible |
+----------------------+--------------+-------------+---------+
| PRIMARY              |            1 | id          | YES     |
| uk_order_no          |            1 | order_no    | YES     |
| idx_customer         |            1 | customer_id | YES     |
| idx_customer_created |            1 | customer_id | YES     |
| idx_customer_created |            2 | created_at  | YES     |
+----------------------+--------------+-------------+---------+
5 rows in set (0.00 sec)

위 예제에서 idx_customer는 (customer_id, created_at)의 왼쪽 prefix에 해당한다. 실제 후보 목록은 다음처럼 조회한다.

SELECT table_schema,
       table_name,
       redundant_index_name,
       redundant_index_columns,
       dominant_index_name,
       dominant_index_columns,
       sql_drop_index
FROM sys.schema_redundant_indexes
WHERE table_schema = DATABASE()
  AND table_name = 'redundant_index_demo';

실행 결과(MySQL 8.0.x):

mysql> SELECT table_schema,
    ->        table_name,
    ->        redundant_index_name,
    ->        redundant_index_columns,
    ->        dominant_index_name,
    ->        dominant_index_columns,
    ->        sql_drop_index
    -> FROM sys.schema_redundant_indexes
    -> WHERE table_schema = DATABASE()
    ->   AND table_name = 'redundant_index_demo';

+-----------------+----------------------+----------------------+-------------------------+----------------------+------------------------+--------------------------------------------------------------------------------+
| table_schema    | table_name           | redundant_index_name | redundant_index_columns | dominant_index_name  | dominant_index_columns | sql_drop_index                                                                 |
+-----------------+----------------------+----------------------+-------------------------+----------------------+------------------------+--------------------------------------------------------------------------------+
| mysql_tech_note | redundant_index_demo | idx_customer         | customer_id             | idx_customer_created | customer_id,created_at | ALTER TABLE `mysql_tech_note`.`redundant_index_demo` DROP INDEX `idx_customer` |
+-----------------+----------------------+----------------------+-------------------------+----------------------+------------------------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

sql_drop_index는 편리한 DDL 초안이지만 자동 실행 명령으로 취급해서는 안 된다. 이 view는 구조를 비교할 뿐, 최근 배치가 그 인덱스를 쓰는지, 특정 쿼리의 latency가 얼마나 달라지는지, 삭제 후 plan regression이 생기는지까지 판단하지 않는다.

4.1 직접 메타데이터를 확인하는 쿼리

자동 view의 판정 근거를 검토하거나 schema diff 도구를 만들 때는 information_schema.STATISTICS에서 key part 순서와 속성을 직접 확인한다. CARDINALITY는 샘플링에 따라 달라질 수 있으므로 구조 비교 결과에 포함하지 않는다.

SELECT INDEX_NAME,
       NON_UNIQUE,
       SEQ_IN_INDEX,
       COLUMN_NAME,
       SUB_PART,
       COLLATION,
       INDEX_TYPE,
       IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'redundant_index_demo'
ORDER BY INDEX_NAME, SEQ_IN_INDEX;

실행 결과(MySQL 8.0.x):

mysql> SELECT INDEX_NAME,
    ->        NON_UNIQUE,
    ->        SEQ_IN_INDEX,
    ->        COLUMN_NAME,
    ->        SUB_PART,
    ->        COLLATION,
    ->        INDEX_TYPE,
    ->        IS_VISIBLE
    -> FROM information_schema.STATISTICS
    -> WHERE TABLE_SCHEMA = DATABASE()
    ->   AND TABLE_NAME = 'redundant_index_demo'
    -> ORDER BY INDEX_NAME, SEQ_IN_INDEX;

+----------------------+------------+--------------+-------------+----------+-----------+------------+------------+
| INDEX_NAME           | NON_UNIQUE | SEQ_IN_INDEX | COLUMN_NAME | SUB_PART | COLLATION | INDEX_TYPE | IS_VISIBLE |
+----------------------+------------+--------------+-------------+----------+-----------+------------+------------+
| idx_customer         |          1 |            1 | customer_id |     NULL | A         | BTREE      | YES        |
| idx_customer_created |          1 |            1 | customer_id |     NULL | A         | BTREE      | YES        |
| idx_customer_created |          1 |            2 | created_at  |     NULL | A         | BTREE      | YES        |
| PRIMARY              |          0 |            1 | id          |     NULL | A         | BTREE      | YES        |
| uk_order_no          |          0 |            1 | order_no    |     NULL | A         | BTREE      | YES        |
+----------------------+------------+--------------+-------------+----------+-----------+------------+------------+
5 rows in set (0.00 sec)

이 결과는 “어떤 인덱스가 다른 인덱스의 연속된 왼쪽 prefix인지”를 검토하는 기초 자료다. 자동화할 때는 컬럼 목록을 문자열로만 합쳐 비교하지 말고 SEQ_IN_INDEX, SUB_PART, COLLATION, EXPRESSION, NON_UNIQUE를 포함한 구조화된 비교를 권장한다.

5. 사용되지 않는 인덱스 통계는 보조 증거다

sys.schema_unused_indexes는 Performance Schema의 인덱스 사용 통계를 바탕으로 사용 흔적이 없는 인덱스를 보여준다. 그러나 미사용은 영구적 사실이 아니라 관측 구간의 결과다.

서버 재시작 후 통계가 초기화되었을 수 있다.
월말 정산, 분기 배치, 장애 복구 쿼리는 짧은 기간에 나타나지 않는다.
읽기 Replica에서만 실행되는 쿼리를 Writer의 통계로 판단하면 안 된다.
prepared statement나 쿼리 라우팅 변경으로 워크로드가 이동했을 수 있다.
새 인덱스 생성 직후에는 당연히 사용 횟수가 낮다.

후보 조회는 다음과 같이 할 수 있다. 결과가 비어 있어도 “모든 인덱스가 필요하다”는 의미는 아니며, 반대로 행이 반환되어도 “즉시 삭제 가능”을 뜻하지 않는다.

SELECT object_schema,
       object_name,
       index_name
FROM sys.schema_unused_indexes
WHERE object_schema = DATABASE()
  AND object_name = 'redundant_index_demo';

실행 결과(MySQL 8.0.x):

mysql> SELECT object_schema,
    ->        object_name,
    ->        index_name
    -> FROM sys.schema_unused_indexes
    -> WHERE object_schema = DATABASE()
    ->   AND object_name = 'redundant_index_demo';

+-----------------+----------------------+----------------------+
| object_schema   | object_name          | index_name           |
+-----------------+----------------------+----------------------+
| mysql_tech_note | redundant_index_demo | idx_customer         |
| mysql_tech_note | redundant_index_demo | idx_customer_created |
+-----------------+----------------------+----------------------+
2 rows in set (0.01 sec)

실무에서는 최소한 대표 업무 주기를 포함하는 기간을 관측한다. 일간·주간·월간 배치가 있다면 그 주기를 모두 포함해야 한다. 계절성 워크로드나 재해 복구 절차에만 쓰이는 인덱스는 별도 스키마 문서와 쿼리 카탈로그로 보호해야 한다.

6. 제거 전에 실행 계획과 쿼리 의존성을 확인한다

구조적 중복 후보를 찾은 다음에는 실제 워크로드에서 다음을 확인한다.

후보 인덱스를 명시한 USE INDEX, FORCE INDEX, IGNORE INDEX hint가 애플리케이션이나 운영 SQL에 있는가?
Query Digest 기준으로 해당 테이블의 핵심 쿼리는 무엇인가?
후보 인덱스가 point lookup, range scan, ordering, grouping, covering 중 어떤 역할을 하는가?
대체 인덱스 사용 시 examined rows, 실제 수행 시간, Buffer Pool 읽기가 악화되는가?
Foreign Key 검증에 필요한 인덱스인가?
Replica, 보고서 노드, Blue/Green 환경에서 다른 워크로드가 실행되는가?

EXPLAIN ANALYZE는 실제 실행을 수반하므로 운영에서 비용이 큰 쿼리에 무조건 적용하지 않는다. 먼저 일반 EXPLAIN과 staging 재현을 사용하고, 운영에서는 충분히 제한된 조건과 영향 범위를 확인한 뒤 사용한다.

7. Invisible index를 이용한 안전한 검증

MySQL 8.0의 Invisible index는 인덱스 데이터를 유지하면서 기본 Optimizer 후보에서 제외한다. 삭제 전 영향 검증에 유용하다. 단, 인덱스 유지 비용은 그대로 발생하므로 Invisible 상태를 장기 보관하는 것은 정리 완료가 아니다.

다음은 idx_customer를 Invisible로 바꾸고 메타데이터를 확인한 뒤 제거하는 절차다.

ALTER TABLE redundant_index_demo
    ALTER INDEX idx_customer INVISIBLE;

SELECT INDEX_NAME,
       IS_VISIBLE
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'redundant_index_demo'
  AND INDEX_NAME IN ('idx_customer', 'idx_customer_created')
GROUP BY INDEX_NAME, IS_VISIBLE
ORDER BY INDEX_NAME;

ALTER TABLE redundant_index_demo
    ALTER INDEX idx_customer VISIBLE;

실행 결과(MySQL 8.0.x):

mysql> ALTER TABLE redundant_index_demo
    ->     ALTER INDEX idx_customer INVISIBLE;

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT INDEX_NAME,
    ->        IS_VISIBLE
    -> FROM information_schema.STATISTICS
    -> WHERE TABLE_SCHEMA = DATABASE()
    ->   AND TABLE_NAME = 'redundant_index_demo'
    ->   AND INDEX_NAME IN ('idx_customer', 'idx_customer_created')
    -> GROUP BY INDEX_NAME, IS_VISIBLE
    -> ORDER BY INDEX_NAME;

+----------------------+------------+
| INDEX_NAME           | IS_VISIBLE |
+----------------------+------------+
| idx_customer         | NO         |
| idx_customer_created | YES        |
+----------------------+------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE redundant_index_demo
    ->     ALTER INDEX idx_customer VISIBLE;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

Invisible 전환 뒤에는 대표 쿼리의 plan과 latency를 관측한다. 문제가 생기면 위 예제처럼 VISIBLE로 되돌릴 수 있다. 검증 기간을 통과했다면 최종 제거를 수행한다.

ALTER TABLE redundant_index_demo
    ALTER INDEX idx_customer INVISIBLE;

ALTER TABLE redundant_index_demo
    DROP INDEX idx_customer;

SELECT INDEX_NAME,
       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns,
       MIN(IS_VISIBLE) AS is_visible
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'redundant_index_demo'
GROUP BY INDEX_NAME
ORDER BY INDEX_NAME;

DROP TABLE redundant_index_demo;

실행 결과(MySQL 8.0.x):

mysql> ALTER TABLE redundant_index_demo
    ->     ALTER INDEX idx_customer INVISIBLE;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE redundant_index_demo
    ->     DROP INDEX idx_customer;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT INDEX_NAME,
    ->        GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS index_columns,
    ->        MIN(IS_VISIBLE) AS is_visible
    -> FROM information_schema.STATISTICS
    -> WHERE TABLE_SCHEMA = DATABASE()
    ->   AND TABLE_NAME = 'redundant_index_demo'
    -> GROUP BY INDEX_NAME
    -> ORDER BY INDEX_NAME;

+----------------------+------------------------+------------+
| INDEX_NAME           | index_columns          | is_visible |
+----------------------+------------------------+------------+
| idx_customer_created | customer_id,created_at | YES        |
| PRIMARY              | id                     | YES        |
| uk_order_no          | order_no               | YES        |
+----------------------+------------------------+------------+
3 rows in set (0.01 sec)

mysql> DROP TABLE redundant_index_demo;

Query OK, 0 rows affected (0.00 sec)

ALTER INDEX ... INVISIBLE은 일반적으로 메타데이터 변경에 가깝지만, 실제 DDL의 lock과 algorithm 지원 여부는 대상 MySQL minor version, 테이블 상태, 동시 DDL에 따라 확인해야 한다. DROP INDEX도 Online DDL이라고 해서 무영향인 것은 아니다. metadata lock 대기, I/O, Redo, Replica lag, Aurora cluster의 writer 부하를 변경 창에서 함께 감시한다.

8. 권장 제거 Runbook

다음 흐름은 후보 탐색과 실제 삭제를 분리해 사고 가능성을 낮춘다.

flowchart TD
    A[구조적 중복 후보 수집] --> B[제약·hint·DDL 의존성 확인]
    B --> C[대표 업무 주기 동안 사용 통계 관측]
    C --> D[핵심 쿼리 plan·latency 기준선 저장]
    D --> E[후보 인덱스를 Invisible로 전환]
    E --> F{회귀 또는 오류 발생?}
    F -- 예 --> G[즉시 Visible 복구 및 원인 분석]
    F -- 아니오 --> H[관측 기간 유지]
    H --> I[변경 창에서 DROP INDEX]
    I --> J[Replica lag·지연·오류·공간 확인]

단계 1: 후보 목록과 근거를 기록한다

sys.schema_redundant_indexes 결과
information_schema.STATISTICS 구조
sys.schema_unused_indexes의 관측 기간과 서버 uptime
대체할 dominant index
연관된 제약 조건과 index hint

단계 2: 기준선을 저장한다

후보 인덱스와 관련된 Query Digest의 실행 횟수, 평균·상위 지연 시간, rows examined, 임시 테이블, sort 지표를 변경 전후로 비교할 수 있게 저장한다. 단순 평균만 보지 말고 p95/p99와 오류율도 확인한다.

단계 3: Invisible로 전환한다

한 번에 많은 인덱스를 숨기지 않는다. 후보와 영향 쿼리의 대응 관계를 추적할 수 있도록 작은 batch로 진행한다. SET SESSION optimizer_switch='use_invisible_indexes=on'은 검증 세션에서 Invisible index를 다시 후보로 포함해 전후 plan을 비교할 때 사용할 수 있지만, 애플리케이션 전역 우회책으로 사용해서는 안 된다.

단계 4: 대표 주기를 관측한다

온라인 트래픽뿐 아니라 배치, 보고서, 유지보수, 장애 점검 SQL까지 실행되는 기간을 포함한다. 회귀가 발견되면 ALTER INDEX ... VISIBLE로 빠르게 되돌리고 이유를 분석한다.

단계 5: 최종 삭제와 사후 확인

삭제 DDL 직전에 재생성문을 확보한다. 변경 후에는 Query Digest, error log, metadata lock, Replica lag, CPU/I/O, Buffer Pool miss를 확인한다. 공간 회수 효과는 인덱스 페이지 감소와 테이블스페이스 파일 축소가 같은 의미가 아님을 유의한다. file-per-table의 파일 크기는 인덱스를 삭제해도 운영체제에 즉시 반환되지 않을 수 있다.

9. 자주 발생하는 오판과 장애 요인

9.1 “긴 복합 인덱스가 짧은 인덱스를 항상 대체한다”

논리적으로 탐색 가능하다는 사실과 비용이 같다는 사실은 다르다. 긴 키는 더 많은 페이지와 Buffer Pool을 사용한다. 매우 빈번한 point lookup에서는 짧은 인덱스가 실질적인 이점을 가질 수 있으므로 실제 workload로 확인한다.

9.2 “미사용 view에 나오면 삭제해도 된다”

Performance Schema 통계는 초기화 이후의 관측값이다. 월간 배치나 failover 후에만 실행되는 쿼리를 놓칠 수 있다. uptime과 관측 기간을 함께 기록하지 않은 미사용 판정은 근거가 약하다.

9.3 UNIQUE와 비유일 인덱스를 같은 것으로 본다

UNIQUE(a, b)는 검색 수단이면서 제약 조건이다. 비유일 인덱스가 같은 컬럼을 포함해도 무결성 의미를 대체하지 못한다. 반대로 unique index가 비유일 prefix 인덱스를 구조적으로 포함하더라도 NULL 처리와 쿼리 비용을 검토해야 한다.

9.4 Foreign Key 지원 인덱스를 제거한다

InnoDB는 Foreign Key 검증에 필요한 인덱스를 요구한다. 다른 인덱스가 같은 왼쪽 prefix를 제공한다면 제거가 가능할 수 있지만, DDL 실행 전 부모·자식 양쪽의 제약과 인덱스 구조를 확인해야 한다.

9.5 Invisible 전환을 곧바로 삭제와 동일시한다

Invisible index는 기본 실행 계획에서 제외될 뿐 계속 갱신된다. 쓰기 비용과 저장 공간은 사라지지 않는다. 검증용 유예 상태이며, 유지 또는 삭제 결정을 끝내야 한다.

9.6 DDL이 Online이면 무중단이라고 생각한다

Online DDL도 시작·종료 구간의 metadata lock, 장기 트랜잭션과의 충돌, 추가 I/O, Replica 적용 지연을 만들 수 있다. lock_wait_timeout과 변경 창을 준비하고, 대기 중인 DDL을 방치하지 않는다.

10. Aurora MySQL에서의 운영 해석

Aurora MySQL도 MySQL 호환 Optimizer와 인덱스 구조를 제공하므로 구조적 중복 판정과 Invisible index 절차의 기본 원칙은 같다. 다만 다음 차이를 운영 계획에 반영한다.

Aurora의 분산 스토리지 구조가 인덱스 유지 비용을 없애는 것은 아니다. Writer는 모든 보조 인덱스 변경을 처리해야 한다.
Reader 인스턴스에 보고서·분석 쿼리가 분산되어 있다면 Writer 한 곳의 사용 통계만으로 삭제를 판단하지 않는다.
DDL 중 부하와 Replica lag, failover 가능성을 CloudWatch와 Performance Insights 등 사용 가능한 관측 도구에서 함께 본다.
cluster parameter group과 DB engine minor version에 따라 기능 지원 및 동작 세부가 달라질 수 있으므로 production 전 동일 계열 staging cluster에서 확인한다.
백업 저장량 감소와 실제 청구 비용 변화는 즉시 일치하지 않을 수 있다. 인덱스 삭제의 우선 목적은 쓰기 경로 단순화와 유효 데이터 구조 개선으로 두는 편이 안전하다.

11. 운영 체크리스트

후보 판정

컬럼 순서, prefix 길이, 정렬 방향, 표현식, 인덱스 종류를 비교했다.
PRIMARY KEY와 UNIQUE `PRIMARY KEY`와 `UNIQUE`의 무결성 의미를 확인했다.
Foreign Key 지원 인덱스인지 확인했다.
애플리케이션과 운영 SQL의 index hint를 검색했다.
dominant index가 실제로 leftmost prefix를 제공하는지 확인했다.

관측과 검증

서버 재시작·통계 초기화 이후 충분한 기간을 관측했다.
일간·주간·월간 배치와 장애 대응 쿼리를 포함했다.
Writer, Replica, 보고서 노드의 워크로드 차이를 검토했다.
핵심 쿼리의 변경 전 plan과 latency 기준선을 저장했다.
Invisible 전환 후 오류율, rows examined, tail latency를 비교했다.

변경과 복구

인덱스 재생성 DDL을 확보했다.
metadata lock과 장기 트랜잭션을 사전에 점검했다.
한 번에 제거할 인덱스 수를 작게 제한했다.
회귀 시 VISIBLE 회귀 시 `VISIBLE` 복구 또는 재생성할 담당자와 절차가 준비되어 있다.
변경 후 Replica lag, CPU, I/O, Query Digest, error log를 확인했다.

맺음말

중복 인덱스 제거는 DROP INDEX 한 줄보다 판정 근거와 관측 절차가 중요하다. sys.schema_redundant_indexes는 구조적 후보를 빠르게 찾는 출발점이고, schema_unused_indexes는 관측 구간의 보조 증거다. 최종 결정은 제약 조건, 실제 쿼리, 대표 업무 주기, Invisible index 검증, 복구 준비를 결합해 내려야 한다.

다음 단계에서는 인덱스 사용량과 쓰기 비용을 Performance Schema, Query Digest, InnoDB 통계와 연결해 우선순위를 정하는 방법을 살펴볼 수 있다. 이를 통해 단순한 인덱스 개수 감축이 아니라 워크로드에 맞는 인덱스 포트폴리오를 지속적으로 관리할 수 있다.

1. 중복 인덱스는 두 종류로 나누어 본다

1.1 완전 중복

1.2 포함 관계 중복

2. 왜 불필요한 인덱스가 운영 비용을 만든다

3. 구조적 중복을 판정할 때 확인할 속성

4. sys.schema_redundant_indexes로 1차 후보 찾기

4.1 직접 메타데이터를 확인하는 쿼리

5. 사용되지 않는 인덱스 통계는 보조 증거다

6. 제거 전에 실행 계획과 쿼리 의존성을 확인한다

7. Invisible index를 이용한 안전한 검증

8. 권장 제거 Runbook

단계 1: 후보 목록과 근거를 기록한다

단계 2: 기준선을 저장한다

단계 3: Invisible로 전환한다

단계 4: 대표 주기를 관측한다

단계 5: 최종 삭제와 사후 확인

9. 자주 발생하는 오판과 장애 요인

9.1 “긴 복합 인덱스가 짧은 인덱스를 항상 대체한다”

9.2 “미사용 view에 나오면 삭제해도 된다”

9.3 UNIQUE와 비유일 인덱스를 같은 것으로 본다

9.4 Foreign Key 지원 인덱스를 제거한다

9.5 Invisible 전환을 곧바로 삭제와 동일시한다

9.6 DDL이 Online이면 무중단이라고 생각한다

10. Aurora MySQL에서의 운영 해석

11. 운영 체크리스트

후보 판정

관측과 검증

변경과 복구

맺음말

4. `sys.schema_redundant_indexes`로 1차 후보 찾기