MySQL Prefix Index 설계: 긴 문자열 인덱싱과 선택도 측정

긴 URL, 전자우편 주소, 추적 식별자, 외부 시스템 키처럼 길이가 큰 문자열에 전체 인덱스를 만들면 인덱스 크기와 쓰기 비용이 빠르게 증가한다. 문자열의 앞부분만 저장하는 Prefix index는 이 비용을 줄이는 실용적인 수단이다. 그러나 접두부가 지나치게 짧으면 많은 행이 같은 인덱스 키에 모이고, InnoDB가 후보 행을 대량으로 읽은 뒤 원본 문자열을 다시 비교해야 한다.

따라서 Prefix index의 핵심 질문은 “몇 글자를 인덱싱할 것인가”가 아니다. 다음 세 가지를 함께 판단해야 한다.

해당 길이에서 실제 데이터가 얼마나 잘 구분되는가.
대표 쿼리가 후보 행을 몇 건까지 줄일 수 있는가.
절약한 저장 공간과 잃어버린 기능적 이점이 균형을 이루는가.

이 글은 MySQL 8.0 이상을 기준으로 Prefix index의 내부 동작, 선택도 측정법, 실행 계획 검증, 운영상 주의점, Aurora MySQL에서의 해석을 순서대로 다룬다.

1. Prefix index가 저장하는 것

다음 인덱스는 request_path 열 전체가 아니라 문자열 앞 24자만 보조 인덱스 키로 저장한다.

CREATE INDEX idx_request_path_p24 ON access_log (request_path(24));

InnoDB 보조 인덱스의 leaf record에는 대략 다음 정보가 들어 있다.

인덱스로 지정한 문자열 접두부
해당 행의 Primary Key
레코드 관리에 필요한 내부 메타데이터

WHERE request_path = '전체 문자열' 조건을 처리할 때 서버는 먼저 접두부로 보조 인덱스 범위를 찾는다. 하지만 접두부가 같은 행이 여러 건이면 인덱스만으로 전체 문자열의 일치 여부를 확정할 수 없다. InnoDB는 각 후보의 clustered index record를 읽고 원본 request_path를 비교해야 한다.

flowchart LR
    Q[전체 문자열 동등 조건] --> P[접두부 계산]
    P --> S[Prefix index 범위 탐색]
    S --> C{같은 접두부 후보 수}
    C -->|적음| R[clustered record 확인]
    C -->|많음| M[다수의 clustered record 확인]
    R --> O[최종 일치 행 반환]
    M --> O

접두부 충돌이 많을수록 다음 비용이 커진다.

보조 인덱스에서 읽는 leaf record 수
Primary Key를 통한 clustered index lookup 수
Buffer Pool page 접근과 CPU 비교 작업
cold cache 환경에서의 읽기 지연 시간

Prefix index는 “긴 문자열을 짧은 키로 완전히 대체하는 구조”가 아니라 후보 집합을 줄이는 불완전한 필터라고 이해해야 한다.

2. 전체 인덱스보다 Prefix index가 필요한 이유

2.1 인덱스 키 길이와 페이지 밀도

utf8mb4 문자열은 문자당 최대 4바이트를 사용할 수 있다. VARCHAR(1024) 전체를 인덱싱하려 하면 선언 길이만으로도 InnoDB 인덱스 키 한도를 초과할 수 있다. 실제 제한은 MySQL 버전, InnoDB row format, 페이지 크기, 복합 인덱스의 다른 열에 따라 달라지므로 DDL을 대상 환경에서 검증해야 한다.

인덱스 생성에 성공하더라도 긴 키는 공짜가 아니다. 하나의 16KiB B-tree page에 들어가는 record 수가 줄어들고, 동일한 행 수를 담기 위해 더 많은 leaf page가 필요하다. 그 결과 다음 현상이 나타날 수 있다.

B-tree 높이 또는 탐색 page 수 증가
Buffer Pool에서 인덱스가 차지하는 비율 증가
insert와 update 시 page split 및 redo 기록량 증가
backup, 복제, DDL에 필요한 처리량 증가

Prefix index는 키 크기를 줄여 page fan-out과 cache 효율을 개선할 수 있다. 다만 이 이득은 선택도가 충분할 때만 조회 성능으로 이어진다.

2.2 Prefix 길이는 문자 수다

비이진 문자열 열에서 col_name(N)의 N은 바이트 수가 아니라 문자 수다. 그러나 실제 인덱스 저장 공간과 키 한도는 문자 집합의 최대 바이트 수 영향을 받는다. 같은 VARCHAR(255)라도 ascii, utf8mb4의 최악 조건은 다르다.

설계 시에는 다음 메타데이터를 함께 확인한다.

CHARACTER_SET_NAME
COLLATION_NAME
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
복합 인덱스의 전체 열 구성

문자 단위 접두부 길이와 물리적 byte 비용을 혼동하면 DDL 실패 또는 예상보다 큰 인덱스를 만들 수 있다.

3. 선택도는 Prefix 길이의 출발점이다

선택도는 전체 행 수 중 서로 다른 키 값이 차지하는 비율로 볼 수 있다.

[ 선택도(N) = \frac{COUNT(DISTINCT\ LEFT(col, N))}{COUNT(*)} ]

값이 1에 가까울수록 해당 접두부가 행을 잘 구분한다. 그러나 선택도 비율 하나만으로 길이를 결정해서는 안 된다. 예를 들어 1억 행에서 선택도 0.01은 서로 다른 접두부가 100만 개라는 뜻이지만, 평균적으로 접두부 하나당 100개 행이 충돌한다. point lookup이 매우 빈번하다면 후보 100개도 큰 비용일 수 있다.

반대로 선택도 1.0을 반드시 목표로 할 필요도 없다. 전체 문자열 자체에 중복이 존재하거나, 쿼리가 tenant_id 같은 선행 열과 함께 사용된다면 복합 키 수준의 후보 수가 이미 충분히 작을 수 있다.

3.1 재현용 데이터와 접두부별 선택도 측정

다음 예제는 공통 경로가 긴 URL 형태의 데이터 10,000건을 만들고 12자, 24자, 40자 접두부의 선택도를 비교한다. 세 Prefix index도 함께 생성한다. 이 예제는 임시 Docker MySQL 8.0 환경에서 실행하도록 구성했다.

DROP TABLE IF EXISTS prefix_index_demo;

CREATE TABLE prefix_index_demo (
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id INT UNSIGNED NOT NULL,
    request_path VARCHAR(1024)
        CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
    response_code SMALLINT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    KEY idx_path_p12 (request_path(12)),
    KEY idx_path_p24 (request_path(24)),
    KEY idx_path_p40 (request_path(40))
) ENGINE=InnoDB;

SET SESSION cte_max_recursion_depth = 10000;

INSERT INTO prefix_index_demo (tenant_id, request_path, response_code)
WITH RECURSIVE seq(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 10000
)
SELECT MOD(n, 10) + 1,
       CONCAT('/products/category/',
              LPAD(MOD(n, 100), 3, '0'),
              '/item/', LPAD(n, 5, '0')),
       IF(MOD(n, 50) = 0, 500, 200)
FROM seq;

ANALYZE TABLE prefix_index_demo;

SELECT COUNT(*) AS total_rows,
       COUNT(DISTINCT request_path) AS full_distinct,
       COUNT(DISTINCT LEFT(request_path, 12)) AS p12_distinct,
       COUNT(DISTINCT LEFT(request_path, 24)) AS p24_distinct,
       COUNT(DISTINCT LEFT(request_path, 40)) AS p40_distinct,
       ROUND(COUNT(DISTINCT LEFT(request_path, 12)) / COUNT(*), 6)
           AS p12_selectivity,
       ROUND(COUNT(DISTINCT LEFT(request_path, 24)) / COUNT(*), 6)
           AS p24_selectivity,
       ROUND(COUNT(DISTINCT LEFT(request_path, 40)) / COUNT(*), 6)
           AS p40_selectivity
FROM prefix_index_demo;

실행 결과(MySQL 8.0.x):

다음은 검증된 실행에서 준비 DDL을 축약하고 적재 결과와 선택도 측정값을 남긴 것이다.

mysql> CREATE TABLE prefix_index_demo (...);
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION cte_max_recursion_depth = 10000;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO prefix_index_demo ...
Query OK, 10000 rows affected (0.08 sec)
Records: 10000  Duplicates: 0  Warnings: 0

mysql> ANALYZE TABLE prefix_index_demo;
+-----------------------------------+---------+----------+----------+
| Table                             | Op      | Msg_type | Msg_text |
+-----------------------------------+---------+----------+----------+
| mysql_tech_note.prefix_index_demo | analyze | status   | OK       |
+-----------------------------------+---------+----------+----------+
1 row in set (0.01 sec)

mysql> SELECT COUNT(*) AS total_rows, ...;
+------------+---------------+--------------+--------------+--------------+-----------------+-----------------+-----------------+
| total_rows | full_distinct | p12_distinct | p24_distinct | p40_distinct | p12_selectivity | p24_selectivity | p40_selectivity |
+------------+---------------+--------------+--------------+--------------+-----------------+-----------------+-----------------+
|      10000 |         10000 |            1 |          100 |        10000 |          0.0001 |          0.0100 |          1.0000 |
+------------+---------------+--------------+--------------+--------------+-----------------+-----------------+-----------------+
1 row in set (0.07 sec)

이 데이터에서 12자 접두부는 공통 경로만 담으므로 사실상 모든 행이 충돌한다. 24자는 category 구간까지 구분하고, 40자는 전체 샘플 경로를 구분한다. 실제 데이터에서는 8, 12, 16, 24, 32, 48처럼 후보 길이를 단계적으로 측정하면 된다.

운영 테이블에서 정확한 COUNT(DISTINCT ...)는 전체 스캔, 임시 테이블, 정렬 또는 큰 메모리를 요구할 수 있다. 대용량 환경에서는 다음 순서가 안전하다.

replica 또는 분석용 복제본에서 측정한다.
시간 범위나 Primary Key 범위를 나누어 표본을 추출한다.
업무 주기와 데이터 분포가 포함되도록 표본 구간을 정한다.
최종 후보 길이만 더 넓은 표본 또는 전체 데이터로 확인한다.

무작위 표본이 어렵다는 이유로 최근 연속 구간만 보면 특정 tenant나 특정 URL 패턴이 과대표집될 수 있다. 데이터 생성 규칙과 파티션 기준을 먼저 이해해야 한다.

3.2 메타데이터에서 실제 Prefix 길이 확인

information_schema.STATISTICS.SUB_PART는 Prefix index에 저장되는 문자 길이를 보여준다. 전체 열 인덱스라면 보통 NULL이다.

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

실행 결과(MySQL 8.0.x):

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

+--------------+--------------+--------------+----------+-----------+------------+
| INDEX_NAME   | SEQ_IN_INDEX | COLUMN_NAME  | SUB_PART | COLLATION | NON_UNIQUE |
+--------------+--------------+--------------+----------+-----------+------------+
| idx_path_p12 |            1 | request_path |       12 | A         |          1 |
| idx_path_p24 |            1 | request_path |       24 | A         |          1 |
| idx_path_p40 |            1 | request_path |       40 | A         |          1 |
| PRIMARY      |            1 | id           |     NULL | A         |          0 |
+--------------+--------------+--------------+----------+-----------+------------+
4 rows in set (0.00 sec)

DDL 원문만 확인하지 말고 운영 서버의 실제 메타데이터를 조회하는 이유는 schema migration 누락, 환경별 DDL 차이, 과거 수동 변경을 발견하기 위해서다.

4. 선택도 비율보다 중요한 후보 행 수

선택도 측정은 길이 후보를 좁히는 단계다. 최종 판단은 대표 쿼리의 실제 access path로 해야 한다. 다음 예제는 같은 전체 문자열 조건을 12자, 24자, 40자 Prefix index로 각각 강제해 EXPLAIN ANALYZE 결과를 비교한다.

FORCE INDEX는 세 구조의 차이를 교육적으로 분리하기 위한 것이다. 운영 SQL에 그대로 적용하라는 뜻이 아니다. 실제 운영에서는 optimizer의 기본 선택과 통계 상태를 먼저 확인해야 한다.

EXPLAIN ANALYZE
SELECT id, response_code
FROM prefix_index_demo FORCE INDEX (idx_path_p12)
WHERE request_path = '/products/category/042/item/01242';

EXPLAIN ANALYZE
SELECT id, response_code
FROM prefix_index_demo FORCE INDEX (idx_path_p24)
WHERE request_path = '/products/category/042/item/01242';

EXPLAIN ANALYZE
SELECT id, response_code
FROM prefix_index_demo FORCE INDEX (idx_path_p40)
WHERE request_path = '/products/category/042/item/01242';

실행 결과(MySQL 8.0.x):

다음은 검증된 세 실행 계획에서 핵심 iterator와 실제 행 수만 발췌한 것이다. 실행 시간은 테스트 환경의 순간 상태에 따라 달라지므로 생략했다.

mysql> EXPLAIN ANALYZE ... FORCE INDEX (idx_path_p12) ...;
-> Filter: (request_path = '...')  (actual rows=1 loops=1)
    -> Index lookup using idx_path_p12  (actual rows=10000 loops=1)

mysql> EXPLAIN ANALYZE ... FORCE INDEX (idx_path_p24) ...;
-> Filter: (request_path = '...')  (actual rows=1 loops=1)
    -> Index lookup using idx_path_p24  (actual rows=100 loops=1)

mysql> EXPLAIN ANALYZE ... FORCE INDEX (idx_path_p40) ...;
-> Filter: (request_path = '...')  (actual rows=1 loops=1)
    -> Index lookup using idx_path_p40  (actual rows=1 loops=1)

확인할 핵심은 cost 숫자 하나가 아니라 iterator가 읽은 actual rows다.

12자 접두부: 공통 접두부를 가진 대량의 후보를 검사한다.
24자 접두부: category가 같은 후보로 범위가 줄어든다.
40자 접두부: 이 샘플에서는 거의 단일 후보로 좁혀진다.

운영 데이터에서는 평균뿐 아니라 상위 빈도 접두부를 별도로 확인해야 한다. 평균 후보 수가 2건이어도 특정 인기 tenant나 공통 URL prefix에 수십만 건이 집중되어 있으면 tail latency가 악화될 수 있다.

다음과 같은 분포 확인 쿼리를 표본 데이터에 적용할 수 있다.

SELECT LEFT(request_path, 24) AS path_prefix,
       COUNT(*) AS candidate_rows
FROM prefix_index_demo
GROUP BY LEFT(request_path, 24)
ORDER BY candidate_rows DESC, path_prefix
LIMIT 10;

실행 결과(MySQL 8.0.x):

mysql> SELECT LEFT(request_path, 24) AS path_prefix,
    ->        COUNT(*) AS candidate_rows
    -> FROM prefix_index_demo
    -> GROUP BY LEFT(request_path, 24)
    -> ORDER BY candidate_rows DESC, path_prefix
    -> LIMIT 10;

+--------------------------+----------------+
| path_prefix              | candidate_rows |
+--------------------------+----------------+
| /products/category/000/i |            100 |
| /products/category/001/i |            100 |
| /products/category/002/i |            100 |
| /products/category/003/i |            100 |
| /products/category/004/i |            100 |
| /products/category/005/i |            100 |
| /products/category/006/i |            100 |
| /products/category/007/i |            100 |
| /products/category/008/i |            100 |
| /products/category/009/i |            100 |
+--------------------------+----------------+
10 rows in set (0.00 sec)

이 결과는 “24자의 평균 선택도”가 감추는 상위 충돌 접두부를 보여준다. 실제 설계에서는 대표 point lookup 값, 가장 흔한 값, 긴 꼬리 값에 대해 각각 EXPLAIN ANALYZE를 수행하는 편이 좋다.

5. 복합 인덱스에서는 업무 조건 전체를 평가한다

다중 tenant 서비스에서 다음 쿼리를 처리한다고 가정한다.

SELECT id, response_code
FROM access_log
WHERE tenant_id = ?
  AND request_path = ?;

이 경우 request_path 단독 선택도보다 (tenant_id, LEFT(request_path, N))의 결합 선택도가 중요하다. 설계 후보는 다음과 같다.

KEY idx_tenant_path (tenant_id, request_path(24))

tenant_id가 먼저 후보를 크게 줄인다면 경로 접두부를 40자까지 늘리지 않아도 충분할 수 있다. 반대로 한 tenant가 대부분의 데이터를 차지한다면 기대한 효과가 없다.

결합 선택도는 다음 관점으로 측정한다.

COUNT(DISTINCT tenant_id, LEFT(request_path, N)) / COUNT(*)

또한 쿼리 패턴을 함께 확인해야 한다.

tenant_id = ... AND request_path = ...: 복합 Prefix index와 잘 맞을 수 있다.
request_path = ...만 사용: 선행 열이 tenant_id이면 일반적인 leftmost-prefix 탐색에 제약이 있다.
tenant_id = ... ORDER BY request_path: Prefix index는 전체 문자열 정렬을 완전히 보장하지 못한다.
tenant_id IN (...): 값 수와 분포에 따라 range 수와 비용이 달라진다.

인덱스 열 순서와 문자열 Prefix 길이는 별개의 결정이 아니라 하나의 access path 설계 문제다.

6. Prefix index가 잃는 기능

6.1 전체 문자열 기준 covering index가 되기 어렵다

인덱스에 원본 문자열 전체가 없으므로, SELECT request_path ... 또는 전체 문자열 비교는 일반적으로 base row 확인이 필요하다. EXPLAIN에서 Using index가 나타나지 않는 이유를 단순히 통계 문제로 오해해서는 안 된다.

Prefix index는 키 크기를 줄이는 대신 index-only access 가능성을 포기할 수 있다. 읽기 비중이 매우 높은 쿼리라면 더 긴 Prefix index와 covering index 중 어느 쪽이 총비용이 낮은지 측정해야 한다.

6.2 전체 문자열 정렬을 보장하지 않는다

접두부가 같은 두 값의 나머지 부분은 인덱스에 없다. 따라서 ORDER BY long_text_column 전체 순서를 Prefix index만으로 완성할 수 없으며 filesort가 필요할 수 있다. GROUP BY, MIN, MAX, distinct 처리도 같은 제약을 받는다.

6.3 UNIQUE Prefix index는 전체 값이 아니라 접두부를 제한한다

다음 정의는 전체 전자우편 주소의 유일성을 보장하는 것이 아니다.

UNIQUE KEY ux_email_p16 (email(16))

앞 16자가 같은 서로 다른 주소도 중복 키로 거부한다. 이는 실제 데이터는 서로 다른데 저장이 실패하는 거짓 충돌을 만든다. 반대로 전체 문자열 유일성이 중요한 업무 규칙을 Prefix index 하나에 맡겨서는 안 된다.

전체 값의 유일성을 유지해야 하지만 문자열이 너무 길다면 다음 대안을 검토할 수 있다.

정규화된 짧은 업무 키를 별도 열로 저장
전체 값의 cryptographic hash를 generated/stored column에 저장하고 UNIQUE index 구성
hash 충돌 가능성을 고려해 원본 값도 애플리케이션 또는 트랜잭션 안에서 재검증
데이터 모델 자체를 surrogate key 중심으로 재설계

hash index 대안도 충돌 확률, 계산 비용, 변경 추적, collation 의미를 별도로 검토해야 한다.

7. Collation과 데이터 정규화가 선택도를 바꾼다

COUNT(DISTINCT LEFT(col, N))과 인덱스 비교는 해당 열의 collation 의미를 따른다. 대소문자와 악센트를 구분하지 않는 collation에서는 사람이 서로 다른 문자열로 보아도 같은 키로 비교될 수 있다.

예를 들어 URL path가 대소문자를 구분하는 업무 규칙을 가지는데 열은 case-insensitive collation이면 다음 문제가 생긴다.

선택도 측정이 기대보다 낮아진다.
동등 비교가 애플리케이션의 URL 의미와 달라진다.
UNIQUE 제약의 충돌 규칙이 업무 요구와 달라진다.

반대로 전자우편 주소나 검색어처럼 정규화 정책이 필요한 값은 입력 시점의 대소문자, Unicode normalization, 공백 처리 규칙을 명확히 해야 한다. Prefix 길이 조정으로 데이터 품질 문제를 해결하려 해서는 안 된다.

인덱스를 만들기 전에 다음 순서를 지키는 편이 안전하다.

업무상 동등성 규칙을 정의한다.
그 규칙에 맞는 character set과 collation을 선택한다.
입력 정규화 정책을 고정한다.
정규화된 실제 데이터로 Prefix 선택도를 측정한다.

8. 운영 환경에서의 검증 절차

8.1 쿼리 수요를 먼저 수집한다

Prefix index 후보를 만들기 전에 다음 항목을 확인한다.

동등 조건, 범위 조건, LIKE 'prefix%' 중 어떤 형태가 많은가
조회 projection에 원본 문자열이 포함되는가
ORDER BY와 GROUP BY가 같은 열을 사용하는가
tenant, 상태, 시간 같은 다른 조건이 항상 함께 사용되는가
쓰기량과 문자열 변경 빈도는 어느 정도인가

LIKE '%keyword%'처럼 선행 wildcard가 있는 조건은 일반적인 B-tree Prefix index로 해결되지 않는다. 이 경우 Full-Text Search, 별도 검색 시스템, 역색인 구조 등 다른 수단을 검토해야 한다.

8.2 후보 길이를 좁힌다

권장 순서는 다음과 같다.

여러 Prefix 길이의 distinct count와 충돌 분포를 측정한다.
선택도 증가가 둔화되는 지점을 찾는다.
상위 빈도 접두부의 후보 행 수를 확인한다.
복합 인덱스라면 선행 열과 결합한 분포를 다시 측정한다.
가장 짧은 실용 후보와 여유 있는 후보를 남긴다.

예를 들어 24자에서 후보가 평균 3건, 32자에서 1.1건, 48자에서 1.0건이라면 24자와 32자를 실제 쿼리로 비교할 가치가 있다. 48자는 저장 공간 증가에 비해 추가 이득이 작을 가능성이 높다.

8.3 실제 실행을 비교한다

staging 또는 production과 유사한 복제 데이터에서 다음을 비교한다.

EXPLAIN ANALYZE의 실제 읽은 행 수와 loop 수
warm cache와 cold cache 지연 시간
Buffer Pool read 변화
인덱스 page 수와 전체 크기
insert/update 처리량과 redo 발생량
DDL 소요 시간과 replica lag 위험

EXPLAIN의 추정 행 수만 보고 결정하지 않는다. Prefix 충돌 분포가 통계에 충분히 반영되지 않거나 특정 값이 평균과 크게 다를 수 있다.

8.4 배포 후 사용 여부를 확인한다

새 인덱스를 만든 뒤에는 실제 workload가 인덱스를 사용하는지 확인한다. MySQL 8.0의 Invisible index를 이용하면 기존 인덱스 제거 전에 optimizer 영향도를 검증할 수 있지만, 새 인덱스의 쓰기 비용과 구축 비용 자체는 여전히 존재한다.

운영 확인 항목은 다음과 같다.

대표 digest의 실행 계획 변화
performance_schema.table_io_waits_summary_by_index_usage의 인덱스별 접근
latency percentile과 rows examined 변화
write latency, redo, Buffer Pool 압력 변화
장시간 실행 쿼리 또는 replica lag 발생 여부

관찰 기간에는 일중·주중 주기와 batch workload가 포함되어야 한다.

9. 실패하기 쉬운 설계 패턴

9.1 관행적으로 10자 또는 20자를 선택한다

Prefix 길이는 데이터 분포에 종속된다. UUID처럼 앞부분도 잘 분산된 값과 공통 URL scheme/host/path가 긴 값은 같은 길이를 사용할 수 없다. 샘플 측정 없이 정한 숫자는 근거가 아니다.

9.2 평균 선택도만 확인한다

skew가 큰 데이터에서는 평균이 tail latency를 숨긴다. 접두부별 COUNT(*) 상위값과 대표 hot key를 반드시 확인한다.

9.3 개발 데이터로 결정한다

개발 데이터는 건수가 적고 생성 패턴이 단순하다. 운영 데이터의 tenant 편중, 지역 코드, 날짜 prefix, 공통 URL 구조가 빠져 있으면 선택도가 과대평가된다.

9.4 인덱스 크기만 줄이고 rows examined를 보지 않는다

인덱스가 작아져도 후보 lookup이 수백 배 증가하면 point query는 느려질 수 있다. 저장 공간과 읽기 증폭을 동시에 측정해야 한다.

9.5 Prefix index를 covering 또는 정렬 인덱스로 가정한다

접두부 뒤의 값이 없기 때문에 전체 문자열 반환, 비교, 정렬에는 base row 접근이나 추가 정렬이 필요할 수 있다. EXPLAIN의 Extra와 실제 iterator를 확인한다.

9.6 중복 인덱스를 방치한다

request_path(12), request_path(24), request_path(40)을 실험 후 모두 유지하면 쓰기와 저장 공간만 늘어난다. 검증이 끝나면 workload에 필요한 최소 집합만 남겨야 한다. 단, 짧은 Prefix index가 긴 Prefix index에 완전히 불필요한지는 복합 열 구성, cache 효과, 쿼리 형태까지 확인한 뒤 판단한다.

10. Aurora MySQL에서의 해석

Aurora MySQL도 MySQL 호환 B-tree 인덱스와 optimizer를 사용하므로 Prefix index의 선택도, 접두부 충돌, covering 제약은 그대로 중요하다. 분산 스토리지가 낮은 수준의 저장 구조를 관리한다고 해서 비선택적인 Prefix index의 후보 행 읽기가 사라지는 것은 아니다.

Aurora 환경에서는 다음 차이를 운영 계획에 반영한다.

DDL 기능과 algorithm 지원 범위는 사용 중인 Aurora MySQL major/minor 버전에서 확인한다.
큰 인덱스 생성은 writer의 CPU, I/O, redo 성격의 변경 전파, replica lag에 영향을 줄 수 있다.
reader에서 선택도 분석과 읽기 계획을 시험할 수 있지만, 최종 DDL 및 writer workload 영향은 별도 검증이 필요하다.
Performance Insights와 Performance Schema를 함께 사용해 digest별 latency와 rows examined 변화를 관찰한다.
failover 뒤에도 통계와 cache 상태에 따라 단기 실행 특성이 달라질 수 있으므로 배포 직후 한 시점만 측정하지 않는다.

Aurora의 저장 계층 장점을 인덱스 설계의 대체재로 보아서는 안 된다. 불필요하게 긴 인덱스는 여전히 cache와 쓰기 비용을 증가시키고, 지나치게 짧은 Prefix index는 여전히 읽기 증폭을 만든다.

11. Prefix index 설계 의사결정 흐름

flowchart TD
    A[긴 문자열 검색 요구] --> B{선행 wildcard 검색인가}
    B -->|예| C[Full-Text 또는 별도 검색 구조 검토]
    B -->|아니오| D[동등성 규칙과 collation 확인]
    D --> E[실데이터 Prefix 선택도와 충돌 분포 측정]
    E --> F[복합 조건의 결합 선택도 측정]
    F --> G[후보 길이 2~3개 선정]
    G --> H[EXPLAIN ANALYZE와 workload 시험]
    H --> I{읽기 증폭과 저장 비용이 수용 가능한가}
    I -->|아니오| J[길이·열 순서·데이터 모델 재설계]
    I -->|예| K[온라인 배포 계획과 관찰 지표 정의]
    K --> L[배포 후 사용량 검증 및 중복 인덱스 정리]

12. 운영 체크리스트

데이터 의미

문자열의 대소문자, 악센트, Unicode 정규화 규칙을 정의했는가?
열의 character set과 collation이 업무상 동등성 규칙에 맞는가?
공통 scheme, host, 고정 prefix 때문에 앞부분 선택도가 낮지 않은가?
운영 데이터의 tenant 편중과 hot prefix를 포함해 측정했는가?

선택도와 실행 계획

여러 Prefix 길이의 COUNT(DISTINCT LEFT(col, N)) 여러 Prefix 길이의 `COUNT(DISTINCT LEFT(col, N))`를 비교했는가?
평균뿐 아니라 접두부별 상위 충돌 건수를 확인했는가?
복합 인덱스라면 선행 열과 결합한 후보 수를 측정했는가?
대표값, hot value, long-tail value에 대해 EXPLAIN ANALYZE 대표값, hot value, long-tail value에 대해 `EXPLAIN ANALYZE`를 확인했는가?
추정 행 수가 아니라 실제 읽은 행 수와 loop 수도 확인했는가?

기능과 비용

전체 문자열 조회 때문에 covering 효과를 잃는지 확인했는가?
ORDER BY, GROUP BY `ORDER BY`, `GROUP BY`, distinct 처리에 전체 문자열 순서가 필요한가?
UNIQUE Prefix index가 거짓 충돌을 만들지 않는가?
인덱스 page 수, Buffer Pool 점유, 쓰기 처리량을 비교했는가?
실험용 중복 Prefix index의 제거 계획이 있는가?

배포와 운영

대상 MySQL 또는 Aurora MySQL 버전에서 DDL을 검증했는가?
인덱스 생성 시간, metadata lock, replica lag 위험을 평가했는가?
배포 전후 digest latency와 rows examined를 비교할 수 있는가?
일중·주중·batch 주기를 포함한 관찰 기간을 확보했는가?
rollback 또는 인덱스 비가시화·제거 절차를 준비했는가?

맺음말

Prefix index는 긴 문자열의 물리적 비용을 줄이는 강력한 도구이지만, 고정된 권장 길이가 존재하는 기능은 아니다. 올바른 길이는 문자열의 실제 분포, collation, 복합 조건, hot key, projection, 정렬 요구, 읽기와 쓰기의 균형으로 결정된다.

가장 안전한 접근은 선택도로 후보를 좁히고, 충돌 분포로 위험을 찾은 뒤, EXPLAIN ANALYZE와 실제 workload로 최종 길이를 결정하는 것이다. 다음 인덱스 설계 단계에서는 Prefix 길이뿐 아니라 복합 인덱스 열 순서, covering 범위, DML 비용을 함께 평가해야 한다.