taeknology/notes

[MySQL] PK 없는 테이블이 RBR 복제 지연을 일으키는 이유

PK가 없는 테이블에 관심을 가지는 이유

InnoDB 테이블은 클러스터형 인덱스(Clustered Index) 구조를 사용함. 사용자가 PK를 정의하지 않으면 MySQL이 자동으로 숨겨진 DB_ROW_ID 컬럼(6바이트, 단조 증가)을 만들어 GEN_CLUST_INDEX로 활용함. 어차피 내부적으로 생성되므로, 명시적 PK를 만들어 직접 제어하는 것이 나음.

운영 경험 — PK 없는 테이블에서 대량 DML이 발생하면 Row-Based Replication 환경에서 레플리카 장비의 복제 지연이 간헐적으로 발생함. 주기적으로 모니터링하여 PK 생성을 유도하고 있음.

PK 없는 테이블 찾기

SELECT a.table_schema,
       a.table_name
FROM information_schema.tables a
         LEFT JOIN (SELECT table_schema,
                           table_name
                    FROM information_schema.table_constraints
                    WHERE constraint_type = 'PRIMARY KEY') b ON (
            a.table_schema = b.table_schema
        AND a.table_name = b.table_name
    )
WHERE a.table_schema NOT IN (
                             'information_schema',
                             'db_helper',
                             'mysql',
                             'performance_schema',
                             'sys'
    )
  AND b.table_name IS NULL
  AND a.table_type = 'BASE TABLE'
ORDER BY table_schema,
         table_name;
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| howknow      | meter_aggr |
| howknow      | meter_raw  |
+--------------+------------+

PRIMARY KEY 찾기

SELECT table_schema,
       table_name
FROM information_schema.table_constraints
WHERE constraint_schema NOT IN (
                                'information_schema',
                                'db_helper',
                                'mysql',
                                'performance_schema',
                                'sys'
    )
  AND constraint_type = 'PRIMARY KEY';
+--------------+------------+
| table_schema | table_name |
+--------------+------------+
| mydb         | orders     |
| mydb         | users      |
+--------------+------------+

복제 지연이 발생하는 이유

소스에서는 DB_ROW_ID로 행을 직접 특정하지만, 이 값은 binlog에 기록되지 않음. 레플리카는 변경 대상 행을 찾을 때 다른 컬럼값으로 매칭해야 하므로 최악의 경우 풀스캔이 발생함.

소스:     DB_ROW_ID 기반 직접 접근 → O(log n)
레플리카: binlog에 DB_ROW_ID 없음 → 컬럼값 매칭 → 최악 O(n)

레플리카의 행 탐색 전략은 버전에 따라 변수명과 기본값이 달라짐.

LTS 버전변수명기본값
5.7slave_rows_search_algorithmsINDEX_SCAN,TABLE_SCAN
8.0slave_rows_search_algorithmsINDEX_SCAN,HASH_SCAN
8.4— (제거됨)—
  • 8.0.18부터 slave_rows_search_algorithms deprecated, replica_rows_search_algorithms로 대체
  • 8.4에서 변수 완전 제거 — 탐색 알고리즘이 HASH_SCAN,INDEX_SCAN으로 고정됨 (사용자 설정 불가) (MySQL 8.4 — Removed Features)

각 값의 동작 (MySQL 8.0 기준):

값동작
TABLE_SCAN풀스캔으로 대상 행 탐색
INDEX_SCAN세컨더리 인덱스 활용
HASH_SCAN이벤트 단위로 해시 맵 구성 후 탐색 — PK 없는 테이블의 대량 DML에 유리함
-- MySQL 8.0 (8.0.18 이후, 8.3.0 미만)
SHOW VARIABLES LIKE 'replica_rows_search_algorithms';
+--------------------------------+---------------------+
| Variable_name                  | Value               |
+--------------------------------+---------------------+
| replica_rows_search_algorithms | INDEX_SCAN,HASH_SCAN|
+--------------------------------+---------------------+

Generated Invisible Primary Keys (GIPK)

MySQL 8.0.30부터 sql_generate_invisible_primary_key 변수를 활성화하면, PK 없이 생성된 테이블에 MySQL이 자동으로 my_row_id (BIGINT UNSIGNED AUTO_INCREMENT, INVISIBLE) 컬럼을 PK로 추가함.

INVISIBLE 속성으로 인해 SELECT * 결과에는 나타나지 않아 기존 코드에 영향이 없으며, 실제 PK이므로 복제 지연도 해소됨. 단, 기존 테이블에는 소급 적용되지 않음.

참고

관련 글