카테고리 : MySQL/기술노트

InnoDB secondary index: 보조 인덱스가 Primary Key를 참조하는 방식과 비용

InnoDB secondary index가 leaf record에 Primary Key를 함께 저장하는 이유와, covering read·clustered lookup·인덱스 크기 증가 비용을 운영 관점에서 정리한다.

저자: MySQL 기술 노트 작성: 2026.05.28 약 9분 5,136자
다운로드
#MySQL#InnoDB#인덱스#아키텍처#성능최적화

1. 왜 secondary index의 내부 구조를 알아야 하는가

운영 현장에서 인덱스 문제는 흔히 “인덱스가 있느냐 없느냐” 수준에서 논의된다. 그러나 InnoDB에서는 같은 secondary index라도 Primary Key 설계에 따라 비용 구조가 크게 달라진다. 이유는 secondary index가 독립적인 포인터 집합이 아니라, leaf record 안에 secondary key 값과 Primary Key 값을 함께 저장하는 구조이기 때문이다.

이 차이는 다음과 같은 실무 질문으로 이어진다.

  • 왜 secondary index만 읽는 쿼리는 빠른데, 비슷한 조건인데도 어떤 쿼리는 갑자기 느려지는가
  • 왜 Primary Key가 길어지면 테이블의 모든 secondary index까지 함께 비대해지는가
  • 왜 secondary index가 많은 OLTP 테이블에서 UUID 기반 PK가 buffer pool 효율과 쓰기 비용을 악화시키는가
  • 왜 Aurora MySQL처럼 스토리지 계층이 분리된 환경에서도 InnoDB 인덱스 구조 이해가 여전히 중요한가

이 글은 secondary index가 clustered index와 어떻게 연결되는지, 그리고 그 구조가 조회·메모리·쓰기 비용으로 어떻게 이어지는지 설명한다.

2. 핵심 개념: secondary index는 행 포인터가 아니라 Primary Key를 저장한다

일부 다른 스토리지 엔진에서는 secondary index leaf가 “행의 물리 주소”를 저장한다고 이해해도 큰 무리가 없다. 하지만 InnoDB는 그렇게 동작하지 않는다. InnoDB에서 실제 행 데이터는 clustered index leaf page에 저장되며, secondary index leaf에는 대략 다음 정보가 들어간다.

  1. secondary index key 컬럼 값
  2. 해당 행의 Primary Key 값

즉, secondary index는 “바로 행으로 가는 포인터”가 아니라 Primary Key를 통해 clustered index를 다시 찾을 수 있게 하는 논리적 참조를 저장한다.

이 설계는 InnoDB의 MVCC, 페이지 재배치, split/merge, row 이동 가능성, crash recovery 복원력을 생각하면 매우 합리적이다. 행의 물리 위치가 바뀌더라도 Primary Key 기준 논리 참조는 유지되기 때문이다.

다만 이 설계에는 대가가 있다.

  • Primary Key가 길수록 모든 secondary index record가 커진다.
  • secondary index만으로 필요한 컬럼을 다 얻지 못하면 clustered index 재탐색이 필요하다.
  • 결과적으로 fan-out, cache 효율, random lookup 비용이 함께 영향을 받는다.

3. 조회 경로의 큰 그림

secondary index 조회는 보통 다음 흐름으로 이해하면 된다.

flowchart TD
  Q[쿼리 실행] --> S{조건 컬럼이 secondary index를 타는가}
  S -->|예| IX[secondary index B+Tree 탐색]
  S -->|아니오| PK[clustered index 또는 full scan]
  IX --> PKVAL[leaf record에서 Primary Key 값 획득]
  PKVAL --> C{필요한 컬럼이 secondary index에 모두 있는가}
  C -->|예| COVER[covering read로 종료]
  C -->|아니오| PKLOOKUP[clustered index 재탐색]
  PKLOOKUP --> ROW[실제 행 읽기]

여기서 핵심 분기점은 하나다.

  • covering read: secondary index leaf 안에 필요한 컬럼 정보가 모두 있으면 clustered index를 다시 읽지 않는다.
  • clustered lookup: 필요한 컬럼이 leaf 안에 없으면 Primary Key를 이용해 clustered index를 재탐색한다.

따라서 secondary index 성능은 단순히 “인덱스를 탔다”로 끝나지 않는다. 몇 건을 찾았는가, 그 뒤에 clustered lookup이 몇 번 발생하는가, Primary Key 폭이 얼마인가를 함께 봐야 한다.

4. covering read가 가능한 경우: 왜 Primary Key가 따라붙는가

다음 예제는 secondary index가 status, created_at만 정의되어 있어도, order_id를 함께 읽을 수 있음을 보여준다. 이유는 order_id가 Primary Key이며 secondary index leaf record에 함께 저장되기 때문이다.

DROP TABLE IF EXISTS secondary_covering_demo;

CREATE TABLE secondary_covering_demo (
  order_id BIGINT NOT NULL,
  status VARCHAR(10) NOT NULL,
  created_at DATETIME NOT NULL,
  amount INT NOT NULL,
  note VARCHAR(100) NOT NULL,
  PRIMARY KEY (order_id),
  KEY ix_status_created (status, created_at)
) ENGINE=InnoDB;

INSERT INTO secondary_covering_demo
VALUES
  (1001, 'PAID',    '2026-05-28 09:00:00', 12000, '결제 완료'),
  (1002, 'PAID',    '2026-05-28 09:01:00', 18000, '묶음 배송'),
  (1003, 'PENDING', '2026-05-28 09:02:00',  9000, '입금 대기'),
  (1004, 'PAID',    '2026-05-28 09:03:00', 15000, '즉시 출고');

EXPLAIN SELECT order_id, status, created_at
FROM secondary_covering_demo
WHERE status = 'PAID'
ORDER BY created_at;

SELECT order_id, status, created_at
FROM secondary_covering_demo
WHERE status = 'PAID'
ORDER BY created_at;

DROP TABLE secondary_covering_demo;

실행 결과(MySQL 8.0.46):

mysql> INSERT INTO secondary_covering_demo
    -> VALUES
    ->   (1001, 'PAID',    '2026-05-28 09:00:00', 12000, '결제 완료'),
    ->   (1002, 'PAID',    '2026-05-28 09:01:00', 18000, '묶음 배송'),
    ->   (1003, 'PENDING', '2026-05-28 09:02:00',  9000, '입금 대기'),
    ->   (1004, 'PAID',    '2026-05-28 09:03:00', 15000, '즉시 출고');

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

mysql> EXPLAIN SELECT order_id, status, created_at
    -> FROM secondary_covering_demo
    -> WHERE status = 'PAID'
    -> ORDER BY created_at;

+----+-------------+-------------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
| id | select_type | table                   | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | secondary_covering_demo | NULL       | ref  | ix_status_created | ix_status_created | 42      | const |    3 |   100.00 | Using index |
+----+-------------+-------------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------------+
1 row in set (0.00 sec)

mysql> SELECT order_id, status, created_at
    -> FROM secondary_covering_demo
    -> WHERE status = 'PAID'
    -> ORDER BY created_at;

+----------+--------+---------------------+
| order_id | status | created_at          |
+----------+--------+---------------------+
|     1001 | PAID   | 2026-05-28 09:00:00 |
|     1002 | PAID   | 2026-05-28 09:01:00 |
|     1004 | PAID   | 2026-05-28 09:03:00 |
+----------+--------+---------------------+
3 rows in set (0.00 sec)

이 예제에서 기대하는 포인트는 EXPLAIN의 Extra에 Using index가 보이는지 여부다. 이는 서버가 secondary index leaf만 읽고 결과를 만들 수 있음을 의미한다. order_id가 별도 secondary key 컬럼 정의에 없는데도 읽을 수 있는 이유가 바로 secondary index record 뒤에 붙어 있는 Primary Key 값이다.

실무적으로는 다음 의미가 있다.

  • 자주 조회하는 컬럼 조합이 secondary index + PK만으로 해결되면 clustered lookup을 줄일 수 있다.
  • “PK를 자동으로 덧붙여 주는 구조”를 이해하면 covering index 설계를 더 정확히 할 수 있다.
  • 다만 PK 외의 일반 컬럼은 자동 포함되지 않으므로, 무턱대고 covering을 기대하면 안 된다.

5. clustered lookup 비용: secondary index를 타도 행 본문이 필요하면 다시 간다

secondary index가 조건 탐색을 도와주더라도, 실제로 읽고 싶은 컬럼이 leaf 안에 없으면 InnoDB는 Primary Key를 이용해 clustered index를 다시 찾아간다. 이 동작이 소량 조회에서는 큰 문제가 아니지만, 매칭 행 수가 많아지면 random lookup 비용이 빠르게 커진다.

DROP TABLE IF EXISTS secondary_lookup_demo;

CREATE TABLE secondary_lookup_demo (
  order_id BIGINT NOT NULL,
  status VARCHAR(10) NOT NULL,
  created_at DATETIME NOT NULL,
  amount INT NOT NULL,
  note VARCHAR(100) NOT NULL,
  PRIMARY KEY (order_id),
  KEY ix_status_created (status, created_at)
) ENGINE=InnoDB;

INSERT INTO secondary_lookup_demo
VALUES
  (2001, 'PAID',    '2026-05-28 10:00:00', 21000, '정기 결제'),
  (2002, 'PAID',    '2026-05-28 10:01:00', 31000, '쿠폰 적용'),
  (2003, 'PAID',    '2026-05-28 10:02:00', 17000, '부분 취소 예정'),
  (2004, 'PENDING', '2026-05-28 10:03:00',  8000, '입금 확인 중');

EXPLAIN SELECT note
FROM secondary_lookup_demo
WHERE status = 'PAID'
ORDER BY created_at;

SELECT order_id, note
FROM secondary_lookup_demo
WHERE status = 'PAID'
ORDER BY created_at;

DROP TABLE secondary_lookup_demo;

실행 결과(MySQL 8.0.46):

mysql> INSERT INTO secondary_lookup_demo
    -> VALUES
    ->   (2001, 'PAID',    '2026-05-28 10:00:00', 21000, '정기 결제'),
    ->   (2002, 'PAID',    '2026-05-28 10:01:00', 31000, '쿠폰 적용'),
    ->   (2003, 'PAID',    '2026-05-28 10:02:00', 17000, '부분 취소 예정'),
    ->   (2004, 'PENDING', '2026-05-28 10:03:00',  8000, '입금 확인 중');

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

mysql> EXPLAIN SELECT note
    -> FROM secondary_lookup_demo
    -> WHERE status = 'PAID'
    -> ORDER BY created_at;

+----+-------------+-----------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
| id | select_type | table                 | partitions | type | possible_keys     | key               | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | secondary_lookup_demo | NULL       | ref  | ix_status_created | ix_status_created | 42      | const |    3 |   100.00 | NULL  |
+----+-------------+-----------------------+------------+------+-------------------+-------------------+---------+-------+------+----------+-------+
1 row in set (0.00 sec)

mysql> SELECT order_id, note
    -> FROM secondary_lookup_demo
    -> WHERE status = 'PAID'
    -> ORDER BY created_at;

+----------+----------------------+
| order_id | note                 |
+----------+----------------------+
|     2001 | 정기 결제        |
|     2002 | 쿠폰 적용        |
|     2003 | 부분 취소 예정 |
+----------+----------------------+
3 rows in set (0.00 sec)

note는 secondary index에 포함되지 않으므로, 실행 계획에서 Using index가 사라지는 것을 확인할 수 있다. 이때 InnoDB는 대략 다음 과정을 수행한다.

  1. ix_status_created에서 status='PAID' 조건으로 후보 행을 찾는다.
  2. leaf record에 들어 있는 order_id를 읽는다.
  3. order_id로 clustered index를 다시 탐색한다.
  4. leaf page에서 실제 note 컬럼을 읽는다.

매칭 건수가 적고 buffer pool hit ratio가 높으면 큰 문제가 없어 보일 수 있다. 그러나 다음 조건이 겹치면 급격히 비싸진다.

  • 조건 selectivity가 낮아 후보 행이 많을 때
  • 행 본문이 크고 page locality가 떨어질 때
  • Primary Key가 랜덤해서 clustered page 접근이 흩어질 때
  • 동시에 여러 세션이 비슷한 lookup 패턴을 만들 때

즉, “인덱스를 탔는데도 느리다”는 현상은 secondary index가 쓸모없어서가 아니라, secondary index 이후의 clustered lookup 비용이 지배적이기 때문인 경우가 많다.

6. 왜 긴 Primary Key가 모든 secondary index를 비대하게 만드는가

secondary index record 안에는 Primary Key 값이 함께 저장된다. 따라서 같은 secondary key를 쓰더라도, Primary Key 폭이 커지면 leaf page에 담을 수 있는 record 수가 줄고 인덱스 크기가 커진다. 이것은 단순 저장공간 문제에 그치지 않는다.

  • 같은 buffer pool 메모리로 캐시할 수 있는 secondary leaf record 수가 줄어든다.
  • 더 많은 page read가 필요해질 수 있다.
  • secondary index split 비용도 커진다.
  • secondary index가 많은 테이블에서는 PK 설계의 부작용이 배수로 확대된다.

다음 예제는 BIGINT PK와 36자 문자열 PK가 같은 secondary index를 가질 때 mysql.innodb_index_stats에서 인덱스 페이지 수가 어떻게 달라질 수 있는지 보여준다.

DROP TABLE IF EXISTS narrow_pk_cost_demo;
DROP TABLE IF EXISTS wide_pk_cost_demo;

CREATE TABLE narrow_pk_cost_demo (
  id BIGINT NOT NULL,
  status CHAR(1) NOT NULL,
  created_at DATETIME NOT NULL,
  filler VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  KEY ix_status (status)
) ENGINE=InnoDB;

CREATE TABLE wide_pk_cost_demo (
  id CHAR(36) NOT NULL,
  status CHAR(1) NOT NULL,
  created_at DATETIME NOT NULL,
  filler VARCHAR(100) NOT NULL,
  PRIMARY KEY (id),
  KEY ix_status (status)
) ENGINE=InnoDB;

INSERT INTO narrow_pk_cost_demo (id, status, created_at, filler)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT n,
       CASE WHEN MOD(n, 2) = 0 THEN 'P' ELSE 'O' END,
       TIMESTAMP('2026-05-28 00:00:00') + INTERVAL n SECOND,
       RPAD('x', 100, 'x')
FROM seq;

INSERT INTO wide_pk_cost_demo (id, status, created_at, filler)
WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT CONCAT(LPAD(HEX(n), 8, '0'), '-0000-0000-0000-', LPAD(HEX(n), 12, '0')),
       CASE WHEN MOD(n, 2) = 0 THEN 'P' ELSE 'O' END,
       TIMESTAMP('2026-05-28 00:00:00') + INTERVAL n SECOND,
       RPAD('x', 100, 'x')
FROM seq;

ANALYZE TABLE narrow_pk_cost_demo, wide_pk_cost_demo;

SELECT table_name,
       stat_name,
       stat_value
FROM mysql.innodb_index_stats
WHERE database_name = DATABASE()
  AND table_name IN ('narrow_pk_cost_demo', 'wide_pk_cost_demo')
  AND index_name = 'ix_status'
  AND stat_name IN ('size', 'n_leaf_pages')
ORDER BY table_name, stat_name;

DROP TABLE narrow_pk_cost_demo;
DROP TABLE wide_pk_cost_demo;

실행 결과(MySQL 8.0.46):

mysql> INSERT INTO narrow_pk_cost_demo (id, status, created_at, filler)
    -> WITH RECURSIVE seq AS (
    ->   SELECT 1 AS n
    ->   UNION ALL
    ->   SELECT n + 1 FROM seq WHERE n < 1000
    -> )
    -> SELECT n,
    ->        CASE WHEN MOD(n, 2) = 0 THEN 'P' ELSE 'O' END,
    ->        TIMESTAMP('2026-05-28 00:00:00') + INTERVAL n SECOND,
    ->        RPAD('x', 100, 'x')
    -> FROM seq;

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

mysql> INSERT INTO wide_pk_cost_demo (id, status, created_at, filler)
    -> WITH RECURSIVE seq AS (
    ->   SELECT 1 AS n
    ->   UNION ALL
    ->   SELECT n + 1 FROM seq WHERE n < 1000
    -> )
    -> SELECT CONCAT(LPAD(HEX(n), 8, '0'), '-0000-0000-0000-', LPAD(HEX(n), 12, '0')),
    ->        CASE WHEN MOD(n, 2) = 0 THEN 'P' ELSE 'O' END,
    ->        TIMESTAMP('2026-05-28 00:00:00') + INTERVAL n SECOND,
    ->        RPAD('x', 100, 'x')
    -> FROM seq;

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

mysql> SELECT table_name,
    ->        stat_name,
    ->        stat_value
    -> FROM mysql.innodb_index_stats
    -> WHERE database_name = DATABASE()
    ->   AND table_name IN ('narrow_pk_cost_demo', 'wide_pk_cost_demo')
    ->   AND index_name = 'ix_status'
    ->   AND stat_name IN ('size', 'n_leaf_pages')
    -> ORDER BY table_name, stat_name;

+---------------------+--------------+------------+
| table_name          | stat_name    | stat_value |
+---------------------+--------------+------------+
| narrow_pk_cost_demo | n_leaf_pages |          1 |
| narrow_pk_cost_demo | size         |          1 |
| wide_pk_cost_demo   | n_leaf_pages |          5 |
| wide_pk_cost_demo   | size         |          6 |
+---------------------+--------------+------------+
4 rows in set (0.00 sec)

stat_value는 데이터 분포, page fill 상태, MySQL minor version에 따라 달라질 수 있지만, 일반적으로 문자열 PK 쪽의 secondary index가 더 많은 page를 차지하는 경향을 확인할 수 있다. 이것이 바로 “Primary Key는 하나지만 비용은 모든 secondary index에 전파된다”는 말의 구체적인 의미다.

7. Aurora MySQL에서는 무엇이 같고 무엇이 다른가

Aurora MySQL은 스토리지 계층이 분리되어 있고 redo/replica 전파 방식도 Community MySQL과 다르다. 그러나 SQL 실행기와 InnoDB 논리 인덱스 구조를 이해하는 관점에서는 다음 사실이 여전히 중요하다.

  • secondary index leaf가 PK를 함께 저장한다는 점은 동일하다.
  • covering read와 clustered lookup의 비용 차이도 동일한 방향으로 나타난다.
  • 긴 PK가 secondary index 메모리 효율을 떨어뜨린다는 점도 동일하다.

다만 Aurora에서는 페이지 접근 비용이 로컬 디스크 기반 MySQL과 완전히 같지 않을 수 있고, replica lag·reader endpoint 분산·스토리지 지연 특성이 진단 체감에 영향을 줄 수 있다. 따라서 Aurora 운영에서는 단순히 “스토리지가 분리되어 있으니 PK 폭은 중요하지 않다”고 결론 내리면 안 된다. 오히려 buffer pool 효율과 읽기 증폭(read amplification) 관점에서 더 신중하게 봐야 한다.

8. 자주 생기는 오해와 장애 패턴

8.1 secondary index가 있으면 테이블 본문을 안 읽는다고 생각하는 오해

secondary index를 탔다고 해서 항상 leaf만 읽고 끝나는 것은 아니다. SELECT 목록에 포함된 컬럼이 secondary index에 없으면 clustered lookup이 발생한다. 실행 계획에서 Using index 유무를 반드시 확인해야 한다.

8.2 UUID PK를 써도 secondary index에는 영향이 없다고 생각하는 오해

영향이 매우 크다. UUID PK는 clustered index 삽입 분산뿐 아니라, 모든 secondary index record 폭 증가로 이어진다. secondary index가 8개인 테이블이라면 PK 설계 부작용이 8배 방향으로 확대될 수 있다.

8.3 PK를 자연키로 길게 잡아도 secondary index 조건만 좋으면 괜찮다는 오해

조건 탐색은 괜찮을 수 있다. 그러나 leaf page 밀도 감소, 더 큰 인덱스, 더 많은 cache miss, 더 잦은 split이라는 형태로 비용이 축적된다. 특히 write-heavy OLTP에서는 작은 차이가 장기적으로 매우 크게 벌어진다.

8.4 EXPLAIN의 rows만 보고 비용을 판단하는 오해

rows는 접근 후보 수 추정치일 뿐이다. 같은 rows=1000이라도 covering read인지, 1000번의 clustered lookup이 필요한지에 따라 실제 비용은 크게 달라진다.

9. 운영 체크리스트와 설계 기준

다음 항목은 secondary index 비용을 점검할 때 실무적으로 자주 쓰는 기준이다.

  • EXPLAIN에서 Using index 유무와 key

설계 원칙을 요약하면 다음과 같다.

  1. 가능하면 짧고 안정적이며 단조 증가 특성을 가진 Primary Key를 우선 고려한다.
  2. secondary index가 많은 핵심 OLTP 테이블일수록 PK 폭을 엄격히 관리한다.
  3. 자주 호출되는 쿼리는 covering read 가능성을 먼저 검토한다.
  4. covering이 어렵다면 매칭 행 수를 줄이거나 row 본문 접근을 최소화하는 방향으로 쿼리를 재구성한다.
  5. “인덱스 추가”보다 “PK 구조와 secondary lookup 비용”을 먼저 점검한다.

10. 결론

InnoDB secondary index를 이해할 때 가장 중요한 문장은 다음 하나로 요약할 수 있다.

secondary index는 행 주소를 저장하는 것이 아니라, secondary key와 Primary Key를 함께 저장하고 필요 시 clustered index를 다시 찾는다.

이 구조 덕분에 InnoDB는 논리 일관성과 복구 안정성을 얻지만, 동시에 Primary Key 폭이 모든 secondary index 비용으로 전파되는 구조적 대가를 치른다. 따라서 PK 설계는 단순 식별자 선택이 아니라, secondary index fan-out·covering 가능성·buffer pool 효율·쓰기 비용까지 함께 결정하는 핵심 아키텍처 선택이다.

다음 글에서는 이 흐름을 이어서 covering index 설계와 bookmark lookup 감소 전략을 더 구체적으로 다룰 수 있다.