--- title: "Covering index 전략: 랜덤 I/O 감소와 인덱스 폭 증가의 균형" description: "MySQL Covering index가 clustered lookup을 줄이는 원리와 넓은 인덱스의 쓰기·메모리 비용을 함께 평가하는 방법을 정리한다." tags: [ MySQL, 인덱스, 성능최적화, 운영 ] image: "mysql-report-bg.png" published: "2026-07-22" updated: "2026-07-22" author: "MySQL 기술 노트" source_url: "" --- Covering index는 쿼리가 요구하는 값을 secondary index leaf에서 모두 얻도록 설계한 인덱스다. 조건에 맞는 secondary index entry를 찾은 뒤 clustered index로 이동하는 과정을 줄이므로, 읽기 중심 워크로드에서 지연 시간과 페이지 접근량을 크게 낮출 수 있다. 그러나 조회 컬럼을 무작정 인덱스에 추가하면 leaf entry가 넓어지고, Buffer Pool 효율과 쓰기 처리량, DDL 비용이 악화된다. 따라서 Covering index 설계의 핵심은 “더 많은 컬럼을 넣을 것인가”가 아니다. **반복 비용이 큰 clustered lookup을 제거해 얻는 이익이 넓어진 인덱스를 유지하는 비용보다 큰가**를 쿼리 단위로 판단하는 일이다. 이 글에서는 InnoDB의 접근 경로부터 실행 계획 검증, 인덱스 폭의 비용, 운영 도입 절차까지 순서대로 살펴본다. ## 1. Covering은 인덱스의 고정 속성이 아니라 쿼리와의 관계다 다음 인덱스를 생각해 보자. ```text INDEX idx_order (tenant_id, status, created_at) ``` InnoDB secondary index의 leaf entry에는 명시한 key column과 row locator 역할의 Primary Key가 저장된다. 따라서 Primary Key가 `order_id`인 테이블에서 다음 쿼리는 `order_id`를 인덱스 정의에 직접 쓰지 않아도 covering이 될 수 있다. ```text SELECT order_id, created_at FROM orders WHERE tenant_id = ? AND status = ? AND created_at >= ?; ``` 반면 같은 조건이라도 `customer_name`, `total_amount`처럼 해당 secondary leaf에 없는 값을 조회하면 covering이 아니다. MySQL은 조건에 맞는 secondary entry마다 Primary Key를 사용해 clustered index record를 찾아야 한다. 이를 흔히 table lookup, row lookup 또는 clustered lookup이라고 부른다. 즉, `idx_order` 자체가 항상 “Covering index”인 것은 아니다. `SELECT` 목록, `WHERE`, `JOIN`, `ORDER BY`, `GROUP BY`에 필요한 컬럼을 해당 인덱스에서 모두 공급할 수 있는 특정 쿼리에 대해서만 covering이다. 애플리케이션의 projection이 바뀌면 같은 인덱스의 covering 여부도 바뀐다. ## 2. InnoDB에서 clustered lookup이 발생하는 경로 ```mermaid flowchart LR Q[조건과 projection] --> S[Secondary B+Tree 탐색] S --> L[Secondary leaf entry] L --> C{필요한 값이 leaf에 모두 있는가?} C -- 예 --> R[인덱스에서 결과 반환] C -- 아니요 --> P[Primary Key 획득] P --> B[Clustered B+Tree 탐색] B --> D[Base row에서 나머지 컬럼 읽기] D --> R ``` non-covering range scan의 비용은 단순히 “인덱스를 한 번 더 읽는다”로 끝나지 않는다. secondary index는 secondary key 순서로 정렬되어 있지만, 그 entry가 가리키는 Primary Key는 물리적으로 연속적이지 않을 수 있다. 많은 후보 row를 읽으면 서로 떨어진 clustered leaf page를 반복해서 방문하게 된다. Buffer Pool 적중률이 낮으면 이 과정은 storage page read로 이어질 수 있다. 모든 page가 메모리에 있어도 비용이 사라지는 것은 아니다. 추가 B+Tree 탐색, latch, CPU cache miss, 더 많은 Buffer Pool page 접근이 남는다. 반대로 결과가 한두 건뿐인 highly selective point lookup에서는 clustered lookup 제거로 얻는 절대 이익이 작을 수 있다. 비용을 개념적으로 표현하면 다음과 같다. ```text non-covering 비용 ≈ secondary 탐색 + 후보 entry scan + 후보 row 수 × clustered lookup 비용 covering 비용 ≈ 더 넓은 secondary 탐색 + 후보 entry scan + clustered lookup 0회 ``` 여기서 중요한 변수는 반환 row 수가 아니라 **clustered lookup 대상이 되는 후보 row 수**다. Index Condition Pushdown이 일부 조건을 secondary index 단계에서 평가하면 lookup 수를 줄일 수 있지만, projection에 필요한 컬럼이 leaf에 없으면 최종 row에 대한 lookup은 여전히 필요하다. ## 3. MySQL 8.0에서 실행 계획으로 covering 여부 확인하기 아래 재현은 비교를 위해 같은 prefix를 가진 narrow index와 wide index를 한 테이블에 함께 만든다. 실제 운영에서는 두 인덱스가 중복 유지 비용을 만들므로, 검증이 끝난 뒤 하나를 선택하거나 기존 인덱스를 대체해야 한다. 먼저 대상 버전과 기본 page 크기를 확인한다. ```sql SELECT VERSION() AS mysql_version, @@innodb_page_size AS innodb_page_size; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT VERSION() AS mysql_version, -> @@innodb_page_size AS innodb_page_size; +---------------+------------------+ | mysql_version | innodb_page_size | +---------------+------------------+ | 8.0.46 | 16384 | +---------------+------------------+ 1 row in set (0.00 sec) ``` 다음 fixture는 tenant별 지원 요청을 만들고 두 인덱스를 비교한다. `idx_queue_narrow`의 leaf에는 검색·정렬 key와 Primary Key가 들어가며, `idx_queue_cover`에는 `subject`까지 명시적으로 포함된다. ```sql DROP TABLE IF EXISTS tech_cover_demo; CREATE TABLE tech_cover_demo ( ticket_id BIGINT NOT NULL AUTO_INCREMENT, tenant_id INT NOT NULL, status VARCHAR(12) NOT NULL, created_at DATETIME NOT NULL, subject VARCHAR(120) NOT NULL, body VARCHAR(500) NOT NULL, PRIMARY KEY (ticket_id), KEY idx_queue_narrow (tenant_id, status, created_at), KEY idx_queue_cover (tenant_id, status, created_at, subject) ) ENGINE=InnoDB; SET SESSION cte_max_recursion_depth = 25000; INSERT INTO tech_cover_demo (tenant_id, status, created_at, subject, body) WITH RECURSIVE seq AS ( SELECT 1 AS n UNION ALL SELECT n + 1 FROM seq WHERE n < 20000 ) SELECT MOD(n - 1, 20) + 1, ELT(MOD(FLOOR((n - 1) / 20), 4) + 1, 'OPEN', 'PENDING', 'DONE', 'CLOSED'), TIMESTAMP('2026-01-01 00:00:00') + INTERVAL n MINUTE, RPAD(CONCAT('ticket-', n), 96, CHAR(65 + MOD(n, 26))), RPAD(CONCAT('body-', n), 300, 'x') FROM seq; ANALYZE TABLE tech_cover_demo; SELECT COUNT(*) AS inserted_rows FROM tech_cover_demo; ``` 실행 결과(MySQL 8.0.x): 준비 과정의 핵심 결과만 발췌했다. ```text mysql> CREATE TABLE tech_cover_demo (...); Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO tech_cover_demo ...; Query OK, 20000 rows affected (0.25 sec) Records: 20000 Duplicates: 0 Warnings: 0 mysql> ANALYZE TABLE tech_cover_demo; +---------------------------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------------+---------+----------+----------+ | mysql_tech_note.tech_cover_demo | analyze | status | OK | +---------------------------------+---------+----------+----------+ 1 row in set (0.00 sec) mysql> SELECT COUNT(*) AS inserted_rows FROM tech_cover_demo; +---------------+ | inserted_rows | +---------------+ | 20000 | +---------------+ 1 row in set (0.00 sec) ``` ### 3.1 Narrow index만으로 충분한 projection `ticket_id`는 InnoDB secondary index leaf에 row locator로 포함되므로, 아래 쿼리는 `idx_queue_narrow`만으로 결과를 만들 수 있다. Traditional `EXPLAIN`의 `Extra`에 표시되는 `Using index`는 이 실행 계획이 index-only access, 즉 covering임을 나타낸다. ```sql EXPLAIN SELECT ticket_id, created_at FROM tech_cover_demo FORCE INDEX (idx_queue_narrow) WHERE tenant_id = 7 AND status = 'OPEN' AND created_at >= '2026-01-01' AND created_at < '2027-01-01' ORDER BY created_at LIMIT 20; ``` 실행 결과(MySQL 8.0.x): ```text mysql> EXPLAIN -> SELECT ticket_id, created_at -> FROM tech_cover_demo FORCE INDEX (idx_queue_narrow) -> WHERE tenant_id = 7 -> AND status = 'OPEN' -> AND created_at >= '2026-01-01' -> AND created_at < '2027-01-01' -> ORDER BY created_at -> LIMIT 20; +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | tech_cover_demo | NULL | range | idx_queue_narrow | idx_queue_narrow | 59 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+--------------------------+ 1 row in set (0.00 sec) ``` `FORCE INDEX`는 교육용 비교에서 동일한 인덱스를 확실히 사용하기 위한 장치다. 실제 운영 쿼리에 hint를 고정하기 전에 통계 정확성, 다른 tenant의 데이터 분포, 파라미터 범위를 함께 검토해야 한다. ### 3.2 projection 하나가 clustered lookup을 만든다 같은 narrow index에서 `subject`를 요청하면 leaf에 값이 없으므로 clustered record를 찾아야 한다. 검색 범위는 동일하지만 `Extra`의 `Using index`가 사라지는 것이 핵심 차이다. ```sql EXPLAIN SELECT ticket_id, created_at, subject FROM tech_cover_demo FORCE INDEX (idx_queue_narrow) WHERE tenant_id = 7 AND status = 'OPEN' AND created_at >= '2026-01-01' AND created_at < '2027-01-01' ORDER BY created_at LIMIT 20; ``` 실행 결과(MySQL 8.0.x): ```text mysql> EXPLAIN -> SELECT ticket_id, created_at, subject -> FROM tech_cover_demo FORCE INDEX (idx_queue_narrow) -> WHERE tenant_id = 7 -> AND status = 'OPEN' -> AND created_at >= '2026-01-01' -> AND created_at < '2027-01-01' -> ORDER BY created_at -> LIMIT 20; +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | tech_cover_demo | NULL | range | idx_queue_narrow | idx_queue_narrow | 59 | NULL | 1 | 100.00 | Using index condition | +----+-------------+-----------------+------------+-------+------------------+------------------+---------+------+------+----------+-----------------------+ 1 row in set (0.00 sec) ``` Traditional `EXPLAIN`만으로 실제 page read 횟수를 알 수는 없다. 다만 선택된 `key`, access `type`, `key_len`, `rows`, `Extra`를 함께 보면 어떤 인덱스로 후보를 좁히고 covering 여부가 어떻게 달라졌는지 판단할 수 있다. `rows`는 통계 기반 추정치이므로 실행마다 조금 달라질 수 있다. ### 3.3 Wide index로 subject까지 covering하기 `idx_queue_cover`는 `subject`까지 leaf에 저장한다. 동일 projection에서 `Using index`가 다시 나타나며, 이 계획은 base row를 읽지 않고 결과를 반환할 수 있다. ```sql EXPLAIN SELECT ticket_id, created_at, subject FROM tech_cover_demo FORCE INDEX (idx_queue_cover) WHERE tenant_id = 7 AND status = 'OPEN' AND created_at >= '2026-01-01' AND created_at < '2027-01-01' ORDER BY created_at LIMIT 20; ``` 실행 결과(MySQL 8.0.x): ```text mysql> EXPLAIN -> SELECT ticket_id, created_at, subject -> FROM tech_cover_demo FORCE INDEX (idx_queue_cover) -> WHERE tenant_id = 7 -> AND status = 'OPEN' -> AND created_at >= '2026-01-01' -> AND created_at < '2027-01-01' -> ORDER BY created_at -> LIMIT 20; +----+-------------+-----------------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | tech_cover_demo | NULL | range | idx_queue_cover | idx_queue_cover | 59 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-----------------+------------+-------+-----------------+-----------------+---------+------+------+----------+--------------------------+ 1 row in set (0.00 sec) ``` `EXPLAIN ANALYZE`는 쿼리를 실제 실행해 iterator별 실제 row 수와 시간을 보여준다. 읽기 전용 검증 환경에서 아래처럼 두 경로를 비교할 수 있다. 실행 시간은 컨테이너 부하와 캐시 상태에 민감하므로 이 작은 fixture의 숫자를 운영 성능 향상률로 일반화하면 안 된다. 여기서는 `Index range scan`과 `Covering index range scan`이라는 접근 경로 차이를 확인하는 데 의미가 있다. ```sql EXPLAIN ANALYZE SELECT ticket_id, created_at, subject FROM tech_cover_demo FORCE INDEX (idx_queue_narrow) WHERE tenant_id = 7 AND status = 'OPEN' AND created_at >= '2026-01-01' AND created_at < '2027-01-01' ORDER BY created_at LIMIT 20; EXPLAIN ANALYZE SELECT ticket_id, created_at, subject FROM tech_cover_demo FORCE INDEX (idx_queue_cover) WHERE tenant_id = 7 AND status = 'OPEN' AND created_at >= '2026-01-01' AND created_at < '2027-01-01' ORDER BY created_at LIMIT 20; ``` 실행 결과(MySQL 8.0.x): 두 계획의 핵심 iterator만 발췌했다. ```text mysql> EXPLAIN ANALYZE SELECT ... FORCE INDEX (idx_queue_narrow) ...; -> Limit: 20 row(s) (actual time=0.0518..0.109 rows=20 loops=1) -> Index range scan on tech_cover_demo using idx_queue_narrow (actual time=0.0511..0.107 rows=20 loops=1) mysql> EXPLAIN ANALYZE SELECT ... FORCE INDEX (idx_queue_cover) ...; -> Limit: 20 row(s) (actual time=0.0404..0.0644 rows=20 loops=1) -> Filter: (...) (actual time=0.0399..0.0626 rows=20 loops=1) -> Covering index range scan on tech_cover_demo using idx_queue_cover (actual time=0.037..0.0564 rows=20 loops=1) ``` ## 4. 넓은 인덱스가 지불하는 비용 Covering을 위해 추가한 컬럼은 무료 복사본이 아니다. secondary index의 모든 leaf entry에 저장되고 트랜잭션 변경 경로에 참여한다. ### 4.1 leaf page 밀도와 B+Tree 규모 entry가 넓어지면 한 page에 들어가는 entry 수가 감소한다. 같은 row 수를 담기 위해 더 많은 leaf page가 필요하고, 데이터 규모가 충분히 커지면 branch page와 tree height에도 영향을 줄 수 있다. 결과적으로 다음 비용이 증가할 수 있다. - index scan이 방문하는 page 수 - Buffer Pool에서 해당 인덱스가 차지하는 working set - cache eviction과 storage read 가능성 - page split, merge, 재구성 비용 - backup, snapshot 복원 후 warm-up, logical dump 이후 index build 시간 아래 쿼리는 재현 인덱스의 persistent statistics를 비교한다. `size`와 `n_leaf_pages`는 page 단위의 통계·추정값이며 실시간으로 정확한 물리 page inventory를 뜻하지 않는다. 작은 테이블에서는 allocation과 sampling의 영향도 크므로, 절대값보다 wide index가 더 큰 방향을 확인하는 용도로 사용한다. ```sql SELECT index_name, MAX(CASE WHEN stat_name = 'size' THEN stat_value END) AS total_pages, MAX(CASE WHEN stat_name = 'n_leaf_pages' THEN stat_value END) AS leaf_pages FROM mysql.innodb_index_stats WHERE database_name = DATABASE() AND table_name = 'tech_cover_demo' AND index_name IN ('idx_queue_narrow', 'idx_queue_cover') AND stat_name IN ('size', 'n_leaf_pages') GROUP BY index_name ORDER BY index_name; ``` 실행 결과(MySQL 8.0.x): ```text mysql> SELECT index_name, -> MAX(CASE WHEN stat_name = 'size' THEN stat_value END) AS total_pages, -> MAX(CASE WHEN stat_name = 'n_leaf_pages' THEN stat_value END) AS leaf_pages -> FROM mysql.innodb_index_stats -> WHERE database_name = DATABASE() -> AND table_name = 'tech_cover_demo' -> AND index_name IN ('idx_queue_narrow', 'idx_queue_cover') -> AND stat_name IN ('size', 'n_leaf_pages') -> GROUP BY index_name -> ORDER BY index_name; +------------------+-------------+------------+ | index_name | total_pages | leaf_pages | +------------------+-------------+------------+ | idx_queue_cover | 291 | 232 | | idx_queue_narrow | 97 | 51 | +------------------+-------------+------------+ 2 rows in set (0.01 sec) ``` 문자열 컬럼의 영향은 선언 길이만으로 단순 계산할 수 없다. 실제 값 길이, character set, nullable bitmap, variable-length field metadata, page fill 상태, prefix compression 관련 내부 동작 등이 entry 크기에 관여한다. 특히 `utf8mb4 VARCHAR(120)`은 값마다 항상 480 byte를 소비한다는 뜻은 아니지만, index key limit와 최악 크기를 평가할 때는 최대 byte 길이를 고려해야 한다. ### 4.2 쓰기 증폭과 변경 비용 인덱스에 포함된 컬럼이 바뀌면 해당 secondary entry도 갱신해야 한다. 읽기 성능을 위해 자주 변경되는 상태값이나 큰 문자열을 추가하면 다음 영향이 생긴다. 1. `INSERT`가 더 큰 secondary entry를 기록한다. 2. indexed column `UPDATE`가 index maintenance와 redo 생성을 늘린다. 3. 더 많은 dirty page가 flush 대상이 될 수 있다. 4. page split 가능성과 purge가 처리할 version 관련 작업이 증가할 수 있다. 5. replica나 복구 경로도 증가한 변경량을 처리해야 한다. `subject`처럼 생성 후 거의 바뀌지 않는 짧은 컬럼과, 매 요청마다 갱신되는 `last_seen_at`, `retry_count`, JSON 문서는 같은 기준으로 다룰 수 없다. 변경 빈도는 cardinality만큼 중요한 index column 선택 기준이다. ### 4.3 중복 인덱스와 optimizer 선택 공간 기존 `(tenant_id, status, created_at)`에 `(tenant_id, status, created_at, subject)`를 추가하면 앞부분이 겹치는 두 B+Tree를 유지하게 된다. wide index가 narrow index의 읽기 용도를 상당 부분 대체할 수 있어도 무조건 narrow index를 삭제할 수 있는 것은 아니다. - narrow index는 더 작아서 broad scan에 유리할 수 있다. - wide index는 특정 projection을 cover하지만 cache footprint가 크다. - unique 속성, prefix 길이, 정렬 방향, visible 상태가 다를 수 있다. - optimizer가 데이터 분포에 따라 두 후보를 다르게 선택할 수 있다. 따라서 “더 긴 인덱스가 왼쪽 prefix가 같은 짧은 인덱스를 항상 대체한다”는 규칙은 안전하지 않다. 실제 workload digest와 실행 계획으로 대체 가능성을 검증한 뒤 제거해야 한다. ## 5. 무엇을 포함하고 무엇을 제외할 것인가 ### 5.1 좋은 후보 Covering 대상은 호출 빈도가 높고, 많은 clustered lookup을 만들며, 결과 projection이 작고 안정적인 쿼리여야 한다. 예를 들면 다음과 같다. - tenant와 상태로 최근 항목 20건을 읽는 queue/list API - parent key로 child의 식별자와 timestamp만 읽는 polling query - 좁은 기간을 반복 조회하는 운영 대시보드 집계의 선행 scan - join inner side에서 짧은 key와 소수 반환 컬럼만 필요한 경로 특히 `ORDER BY ... LIMIT N` 쿼리는 검색·정렬 순서를 인덱스가 만족하면서 projection까지 cover하면, filesort와 clustered lookup을 함께 줄일 가능성이 있다. ### 5.2 피해야 할 후보 다음 패턴은 먼저 쿼리나 데이터 모델을 개선해야 한다. - `SELECT *`를 cover하려고 대부분의 row를 인덱스에 복제하는 설계 - 큰 `TEXT`, `BLOB`, JSON 문서를 포함하려는 설계 - 거의 호출되지 않는 관리 화면 하나만을 위한 wide index - 낮은 선택도의 대량 scan에서 반환 컬럼까지 매우 많은 쿼리 - 업데이트 빈도가 높은 컬럼을 단지 편의를 위해 포함하는 설계 - 비슷한 wide index를 API별로 계속 추가하는 설계 MySQL에는 PostgreSQL의 `INCLUDE`와 동일하게 key ordering에서 분리된 비-key include column 문법이 없다. MySQL secondary index에 추가한 컬럼은 B+Tree key의 일부가 되며 DDL의 column order와 key length에 영향을 준다. 따라서 “정렬에는 참여하지 않고 leaf payload에만 싣는다”는 식으로 오해하면 안 된다. ## 6. 실무 측정: EXPLAIN에서 운영 지표까지 Covering index 후보를 고를 때 단일 개발 환경의 latency만 비교하면 cache 상태와 동시성의 영향을 놓치기 쉽다. 다음 순서로 근거를 쌓는 편이 안전하다. ### 6.1 쿼리 형태를 digest 단위로 고정한다 - 호출 횟수와 총 지연 시간이 큰 statement digest를 찾는다. - `rows_examined / rows_sent` 비율을 확인한다. - projection과 predicate가 배포마다 자주 바뀌는지 확인한다. - tenant별 데이터 편향과 날짜 범위 차이를 반영한 대표 파라미터를 준비한다. ### 6.2 before/after 계획을 비교한다 - `EXPLAIN FORMAT=TREE` 또는 Traditional `EXPLAIN`으로 access path를 확인한다. - 안전한 읽기 쿼리는 staging이나 격리된 replica에서 `EXPLAIN ANALYZE`로 실제 row를 확인한다. - `Using index`만 보지 말고 선택된 key, range 경계, filesort, 실제 scan row를 함께 본다. - warm cache와 cold에 가까운 조건을 분리하고 여러 번 측정한다. ### 6.3 서버 전체 비용을 함께 본다 읽기 지연이 줄어도 다음 지표가 악화되면 설계를 다시 평가해야 한다. - Buffer Pool data/read request와 physical read 변화 - checkpoint pressure, dirty page, redo 생성량 - foreground write latency와 lock wait - 인덱스별 page 규모와 전체 table size - DDL 소요 시간, replica lag 또는 Aurora cluster 부하 `performance_schema.table_io_waits_summary_by_index_usage`는 index별 read/write wait를 관찰하는 출발점이 될 수 있다. 다만 서버 재시작이나 summary reset 이후 누적 구간이 달라지고, 한 쿼리의 clustered lookup 원인을 직접 완벽하게 분리해 주는 지표는 아니다. 배포 전후를 같은 관측 구간과 workload로 비교해야 한다. ## 7. 안전한 도입 절차 ### 7.1 후보 인덱스 생성 전 1. 쿼리 digest와 대표 파라미터를 확보한다. 2. 현재 실행 계획과 실제 row 수를 저장한다. 3. 예상 entry 폭, row 수, 성장률로 추가 storage를 추정한다. 4. 포함 컬럼의 update 빈도를 확인한다. 5. 기존 중복·유사 인덱스를 목록화한다. ### 7.2 검증과 배포 MySQL 8.0에서는 후보 인덱스를 invisible 상태로 만들어 optimizer의 기본 선택에서 제외한 채 구축하고, 테스트 세션에서만 고려하게 할 수 있다. 다만 index build 자체의 CPU, I/O, redo, metadata lock 영향은 invisible이라고 사라지지 않는다. ```text ALTER TABLE orders ADD INDEX idx_candidate (tenant_id, status, created_at, subject) INVISIBLE; SET SESSION optimizer_switch = 'use_invisible_indexes=on'; EXPLAIN ANALYZE SELECT ...; ``` 위 코드는 운영 테이블과 파라미터가 필요한 배포 예시이므로 실행 검증용 SQL fence가 아니라 절차 예시로 제시했다. 실제 적용 시 `ALGORITHM`, `LOCK` 지원 여부와 충분한 disk 여유, DDL 진행 시간, replica/Aurora cluster 영향을 먼저 확인해야 한다. ### 7.3 전환 후 - 계획 회귀가 없는지 statement digest별 p95/p99를 확인한다. - read 개선과 write 비용 증가를 함께 비교한다. - 기존 인덱스를 즉시 삭제하지 말고 rollback 관측 기간을 둔다. - 제거 후보도 먼저 invisible로 전환해 plan 변화를 관찰한다. - 통계 갱신과 배포 후 데이터 분포 변화까지 재확인한다. ## 8. Aurora MySQL에서의 해석 Aurora MySQL도 MySQL 호환 SQL 계층에서 InnoDB 계열 B+Tree와 optimizer를 사용하므로 covering의 기본 원리는 같다. 그러나 비용을 해석할 때는 분산 storage와 cluster 운영 모델을 함께 봐야 한다. - non-covering lookup 감소는 Aurora에서도 buffer cache miss 시 storage 접근과 SQL layer 작업을 줄일 수 있다. - Aurora Standard와 Aurora I/O-Optimized는 I/O 비용 모델이 다르므로, 절감 효과를 요금만으로 단정하지 말고 latency·CPU·I/O를 함께 본다. - 넓은 인덱스는 cluster storage, write 처리, cache working set, online DDL 시간에 영향을 준다. - reader instance마다 cache 상태와 workload가 다를 수 있으므로 writer 한 곳의 측정만으로 읽기 fleet 전체를 판단하지 않는다. - failover 이후 cache 동작은 엔진 버전과 기능 구성에 따라 달라질 수 있다. 장애 전 warm-cache 결과만으로 복구 직후 성능을 보장하지 않는다. - Aurora에서 DDL은 공유 cluster storage와 여러 DB instance에 영향을 줄 수 있으므로, 큰 index build는 maintenance window와 backtrack·snapshot·복제 구성까지 고려한다. Performance Insights 또는 Database Insights를 사용할 수 있다면 top SQL과 wait 분포로 후보를 찾고, `EXPLAIN` 결과와 엔진 지표를 연결한다. 다만 서비스가 보여 주는 상위 SQL 순위는 인덱스 entry 크기나 write amplification을 직접 알려 주지 않으므로 schema 통계와 별도로 평가해야 한다. ## 9. 흔한 오해와 실패 패턴 ### 9.1 `Using index`가 있으면 항상 빠르다 covering이어도 낮은 선택도로 수백만 entry를 scan하면 느릴 수 있다. `Using index`는 base row 접근이 필요 없다는 신호이지, scan 범위가 작거나 최적 latency를 보장한다는 뜻이 아니다. ### 9.2 반환 row 수가 작으면 lookup도 작다 `LIMIT 20`이라도 조건과 정렬을 인덱스가 충분히 지원하지 못하면 20건을 찾기 전에 많은 후보와 base row를 읽을 수 있다. 실행 계획과 실제 scan row를 확인해야 한다. ### 9.3 Primary Key를 secondary index에 매번 추가해야 한다 InnoDB secondary leaf에는 Primary Key가 row locator로 포함된다. 단, 명시적으로 마지막 key part에 두는 것이 정렬의 tie-break나 key definition에 필요할 때는 별도 설계 의미가 있다. 단순히 projection을 cover하려는 목적만이라면 중복 명시가 불필요할 수 있다. ### 9.4 wide index 하나로 모든 API를 cover할 수 있다 하나의 매우 넓은 인덱스는 cache 효율과 쓰기 비용을 악화하고, 서로 다른 predicate/order 요구를 동시에 만족하지 못할 수 있다. 핵심 workload 몇 개를 중심으로 index portfolio를 관리해야 한다. ### 9.5 개발 DB의 빠른 결과가 운영 효과를 증명한다 작은 데이터가 모두 Buffer Pool에 들어가면 clustered lookup의 storage 비용이 드러나지 않는다. 반대로 cold test 하나는 실서비스 cache 재사용을 과소평가한다. 데이터 규모, 분포, 동시성, cache 상태가 유사한 환경에서 반복 측정해야 한다. ## 10. 운영 체크리스트 ### 후보 선정 - [ ] 호출 빈도와 총 지연 시간이 큰 쿼리인가? - [ ] clustered lookup 대상 후보 row 수가 충분히 큰가? - [ ] projection이 작고 배포 간에 안정적인가? - [ ] 검색과 정렬에 사용하는 선행 컬럼 순서가 올바른가? - [ ] 포함하려는 컬럼이 너무 크거나 자주 변경되지 않는가? ### 검증 - [ ] before/after `EXPLAIN`에서 선택 key와 `Using index` 변화를 확인했는가? - [ ] 대표적인 데이터 편향과 range 조건을 모두 시험했는가? - [ ] 안전한 환경에서 `EXPLAIN ANALYZE` actual row를 확인했는가? - [ ] warm/cold cache와 read/write latency를 분리해 측정했는가? - [ ] persistent index statistics는 추정값이라는 점을 반영했는가? ### 배포와 운영 - [ ] 추가 storage와 Buffer Pool working set 증가를 추정했는가? - [ ] online DDL의 disk, I/O, metadata lock 위험을 평가했는가? - [ ] invisible index 또는 동등한 단계적 검증 방식을 고려했는가? - [ ] 기존 중복 인덱스의 유지·제거 기준을 정했는가? - [ ] rollback 기간과 배포 후 plan 회귀 감시 기준이 있는가? - [ ] Aurora라면 writer·reader·storage·비용 모델을 함께 확인했는가? ## 11. 정리 Covering index는 secondary index에서 clustered index로 이어지는 반복 lookup을 제거하는 강력한 읽기 최적화다. 그러나 이 이익은 넓어진 leaf entry, 줄어든 page 밀도, 커진 Buffer Pool working set, 증가한 쓰기·DDL 비용과 맞바꾼 결과다. 따라서 `Using index` 한 줄만 보고 인덱스를 확장할 것이 아니라, 쿼리 빈도와 후보 row 수, projection 안정성, 컬럼 폭과 변경 빈도, 전체 index portfolio를 함께 평가해야 한다. 실무에서는 재현 가능한 before/after 계획을 만들고, 실제 workload 지표로 이득을 확인한 뒤, invisible index와 단계적 전환을 사용해 위험을 낮추는 접근이 안전하다. 다음 글에서는 index entry 폭과 cardinality, fan-out이 B+Tree의 page 수와 비용 추정에 어떤 영향을 주는지 더 구체적으로 연결해 볼 수 있다. ```sql DROP TABLE tech_cover_demo; ``` 실행 결과(MySQL 8.0.x): ```text mysql> DROP TABLE tech_cover_demo; Query OK, 0 rows affected (0.01 sec) ```