Real MySQL 8.0 정리 (2) — 압축, 암호화, 인덱스, 실행계획까지

이전 글에서 MySQL 아키텍처, InnoDB 내부 구조, 트랜잭션과 잠금까지 정리했었는데요.

사실 이번 챕터들은 더 어려웠어요. 6, 7장은 압축이랑 암호화 얘기인데 실무에서 직접 건드려볼 일이 별로 없다보니 읽어도 잘 안 와 닿았고, 8장 인덱스부터는 내용이 너무 방대해서 중간에 몇 번을 접었다가 다시 펼쳤어요.

그래서 이 글은 "다 공부했다"가 아니라 "이것만큼은 이해하고 넘어가자" 는 마음으로 정리한 거예요. 분량도 많고 개념도 깊은 책이라 한 번 읽고 전부 소화하는 사람이 있으면 그게 오히려 이상한 거라고 생각하면서 읽었습니다 ㅎㅎ.

06장: 데이터 압축 — 알지만 직접 쓸 일은 드물다

이 챕터를 읽으면서 든 생각은 "존재는 알겠는데 내가 직접 쓸 상황이 올까?" 였어요. 그래도 개념 정도는 알아두는 게 맞는 것 같아서 핵심만 정리했어요.

페이지 압축 (Transparent Page Compression)

MySQL의 페이지 압축은 파일 시스템의 펀치 홀(Hole Punching) 기능을 활용한다. 데이터를 압축한 뒤 남은 빈 공간을 파일 시스템에 반환하는 방식이다.

1압축 전: [데이터 16KB]
2압축 후: [압축된 4KB] + [빈 공간 12KB] → 파일 시스템에 반환

이렇게 하면 논리적으로는 16KB 페이지지만 실제 디스크 점유는 4KB만 된다. 깔끔한 아이디어지만 문제가 있어요.

지원하는 파일 시스템이 한정적이다. 리눅스의 ext4, xfs 같은 경우는 지원하지만, 윈도우나 일부 환경에서는 작동하지 않는다. 클라우드 환경에서도 볼륨 타입에 따라 다르고요.

[!info] 페이지 압축은 거의 안 씁니다 실무에서는 페이지 압축보다 테이블 압축을 더 많이 쓴다고 해요. 파일 시스템 종속성 때문에 환경을 타는 게 단점이다.

테이블 압축

테이블 압축은 ROW_FORMAT=COMPRESSED와 KEY_BLOCK_SIZE를 지정하는 방식이다.

1CREATE TABLE logs (
2  id BIGINT PRIMARY KEY,
3  content TEXT,
4  created_at DATETIME
5-- KEY_BLOCK_SIZE: 압축 후 목표 크기 (2, 4, 8 중 하나)

디스크 사용량을 크게 줄일 수 있지만 쓰기 성능 저하가 있어요. 데이터를 쓸 때마다 압축/압축 해제가 일어나고, 압축 전용 압축 버퍼가 추가로 사용된다.

읽기 빈도가 높고 쓰기가 적은 로그성 테이블, 아카이빙 데이터에 쓰기 적합하다. 실시간 트랜잭션 테이블에는 맞지 않아요.

07장: 데이터 암호화 — TDE가 뭔지는 알아야 한다

MySQL TDE (Transparent Data Encryption)

TDE(투명한 데이터 암호화) 는 데이터 파일(.ibd) 자체를 암호화하는 방식이다. "투명한"이라는 단어가 핵심인데, 애플리케이션 코드를 전혀 바꾸지 않아도 된다는 뜻이다.

1일반 암호화:   앱 코드가 암호화/복호화를 직접 처리
2TDE:          MySQL이 디스크에 쓸 때 암호화, 읽을 때 복호화
3              → 앱 입장에서는 평문처럼 다룸

데이터 파일이 유출돼도 암호화 키 없이는 읽을 수 없는 게 TDE의 목적이에요. 컴플라이언스 요구사항(금융, 의료 등)에서 자주 요구하는 기능이다.

[!warning] TDE의 한계 TDE는 디스크에 저장된 데이터를 보호한다. 메모리(버퍼 풀)에 올라온 데이터는 평문이고, 네트워크를 통한 전송도 보호하지 않는다. 네트워크 암호화는 SSL/TLS를 따로 설정해야 한다.

keyring_file 플러그인

TDE를 사용하려면 암호화 키를 어딘가에 저장해야 한다. 가장 간단한 방법이 keyring_file 플러그인이다.

1-- my.cnf에 플러그인 설정 후
2SHOW PLUGINS;
3-- keyring_file이 ACTIVE 상태인지 확인

키를 파일로 저장하는 방식이라 가장 단순하지만, 그 파일이 유출되면 의미가 없다는 게 단점이에요. 프로덕션에서는 AWS KMS나 HashiCorp Vault 같은 전용 키 관리 시스템(KMS)을 쓰는 게 더 안전하다.

테이블 암호화

1-- 개별 테이블 암호화
2CREATE TABLE users (
3  id BIGINT PRIMARY KEY,
4  email VARCHAR(255)
5) ENCRYPTION='Y';
6
7-- 기존 테이블 암호화 적용
8ALTER TABLE users ENCRYPTION='Y';

암호화된 테이블은 성능 오버헤드가 약간 있다. 일반적으로 5~10% 정도라고 하는데, 실제로는 I/O 패턴이나 하드웨어에 따라 다르다.

언두 로그, 리두 로그, 바이너리 로그 암호화

테이블만 암호화하면 충분하다고 생각하기 쉬운데, 사실 그게 다가 아니에요.

언두 로그와 리두 로그에도 실제 데이터가 담긴다. UPDATE accounts SET balance=5000을 실행하면 이전 값(10000)이 언두 로그에 남는다. 테이블은 암호화됐는데 로그 파일은 평문이면 의미가 없다.

1-- 언두 로그 암호화
2SET GLOBAL innodb_undo_log_encrypt = ON;
3
4-- 리두 로그 암호화
5SET GLOBAL innodb_redo_log_encrypt = ON;
6
7-- 바이너리 로그 암호화 (my.cnf에 설정)
8-- binlog_encryption = ON

바이너리 로그는 복제(Replication)에 쓰이는 변경 이력이다. 레플리카 서버로 전송되는 내용이라 민감한 데이터가 담길 수 있어요.

[!tip] 컴플라이언스라면 세 군데 다 암호화 규정 대응이 목적이라면 테이블, 언두 로그, 리두 로그, 바이너리 로그 네 군데를 모두 암호화해야 한다. "테이블만 암호화했어요"는 감사에서 지적받을 수 있다.

08장: 인덱스 — 이전 글에서 많이 다뤘으니 새로운 것들 위주로

인덱스 기초(B-Tree 구조, 복합 인덱스, 카디널리티, EXPLAIN)는 이전 글에서 이미 정리했어요. 여기선 그때 다루지 못한 내용들만 골랐습니다.

디스크 읽기 방식: 랜덤 I/O vs 순차 I/O

인덱스를 왜 쓰는지의 근본 이유가 여기 있다.

1순차 I/O:  [1][2][3][4][5][6] 순서대로 읽기
2           → HDD 헤드가 움직이지 않아도 됨. 빠름.
3
4랜덤 I/O:  [5][1][8][3]... 여기저기 읽기
5           → HDD 헤드가 계속 이동. 느림.
6           → SSD도 랜덤 I/O는 순차보다 느림

Full Table Scan은 순차 I/O다. 인덱스를 타면 랜덤 I/O가 발생한다.

그러면 항상 인덱스가 더 빠를까요? 아니다. 데이터를 아주 많이(전체의 20~30% 이상) 읽어야 하는 상황에서는 랜덤 I/O 부담이 오히려 더 커서 MySQL 옵티마이저가 Full Table Scan을 선택하는 게 맞는 판단이다.

B-Tree 인덱스 키 추가/삭제의 비용

B-Tree 인덱스는 키를 추가할 때 트리 균형을 맞추는 작업이 필요하다. 쓰기 성능에 영향을 준다는 뜻이다.

1키 추가: B-Tree 탐색(균형 찾기) + 노드 삽입 + 필요 시 재분할
2키 삭제: B-Tree 탐색 + 삭제 마킹 (즉시 삭제 아닐 수 있음)
3키 변경: 삭제 + 추가 (두 번의 작업)

인덱스가 많을수록 INSERT/UPDATE/DELETE 성능이 저하된다. 이게 "인덱스는 적당히만 걸어야 한다"는 말의 이유다.

함수 기반 인덱스 (Function-based Index)

MySQL 8.0부터 지원된 기능이다. 컬럼 값에 함수를 적용한 결과를 인덱스로 만들 수 있다.

1-- 기존에는 이런 쿼리에 인덱스를 못 썼음
2WHERE YEAR(created_at) = 2024
3WHERE LOWER(email) = 'alice@example.com'
4
5-- 함수 기반 인덱스로 해결
6CREATE INDEX idx_year ON orders ((YEAR(created_at)));
7CREATE INDEX idx_email_lower ON users ((LOWER(email)));
8
9-- 이제 인덱스가 사용됨
10SELECT * FROM orders WHERE YEAR(created_at) = 2024;

이전 글에서 "함수 적용하면 인덱스가 무효화된다"고 했는데, 8.0부터는 함수를 인덱스에 직접 포함시키는 방식으로 이 문제를 해결할 수 있어요.

멀티 밸류 인덱스 (Multi-Value Index)

JSON 배열 안의 값들을 인덱싱하는 기능이다. JSON을 DB에 저장하는 경우에 유용하다.

1CREATE TABLE users (
2  id BIGINT PRIMARY KEY,
3  tags JSON
4);
5
6-- tags 배열 안의 값들을 인덱싱
7CREATE INDEX idx_tags ON users ((CAST(tags -> '$[*]' AS CHAR(50) ARRAY)));
8
9-- 배열 원소 포함 여부 검색에 인덱스 사용됨
10SELECT * FROM users WHERE JSON_CONTAINS(tags, '"backend"');

JSON 컬럼을 쓴다면 알아두면 좋은 기능인데, 실무에서는 아직 많이 쓰이는 편은 아닌 것 같아요.

클러스터링 인덱스 — 다시 한 번

이전 글에서 다뤘지만 워낙 중요해서 한 번 더 짚고 싶어요.

InnoDB의 PRIMARY KEY는 단순한 인덱스가 아니다. 데이터 자체가 PK 순서로 정렬되어 디스크에 저장된다. 이게 클러스터링 인덱스다.

1클러스터링 인덱스 (InnoDB PK):
2  [PK=1] → 실제 데이터 행 자체
3  [PK=2] → 실제 데이터 행 자체
4  [PK=3] → 실제 데이터 행 자체
5
6보조 인덱스 (Secondary Index):
7  [email값] → PK값만 저장
8              PK로 다시 클러스터링 인덱스 조회해서 행 찾기

이 구조 때문에 PK 설계가 중요하다. PK가 랜덤한 UUID면 삽입할 때마다 B-Tree 중간에 끼워 넣어야 해서 페이지 분할이 자주 일어난다. 순서가 있는 값(AUTO_INCREMENT, 시간 기반 ID)이 클러스터링 인덱스에 유리하다.

유니크 인덱스

유니크 인덱스는 중복을 허용하지 않는 인덱스다. 성능 측면에서 알아야 할 포인트가 있어요.

읽기: 유니크 인덱스로 검색하면 딱 1건만 있다는 게 보장되므로 옵티마이저가 type=const나 type=eq_ref로 최적화한다.

쓰기: 중복 체크가 필요하기 때문에 일반 인덱스보다 느리다. 이전 글에서 체인지 버퍼가 유니크 인덱스에는 적용되지 않는다고 했는데, 바로 이 이유다. 중복 확인을 위해 반드시 디스크를 읽어야 한다.

외래키 (Foreign Key)

1CREATE TABLE orders (
2  id BIGINT PRIMARY KEY,
3  user_id BIGINT,
4  FOREIGN KEY (user_id) REFERENCES users(id)
5    ON DELETE CASCADE
6    ON UPDATE RESTRICT
7);

외래키를 걸면 부모/자식 테이블 간 참조 무결성이 DB 레벨에서 보장된다. 하지만 실무에서는 외래키를 안 거는 경우도 많아요.

이유는 성능 때문이에요. 자식 테이블에 쓸 때마다 부모 테이블을 조회해야 하고, ON DELETE CASCADE가 있으면 부모 삭제 시 자식 테이블에 락이 연달아 걸린다. 대규모 시스템에서 이 락 전파가 의도치 않은 장애로 이어질 수 있다.

[!tip] 외래키 전략

• 소규모/스타트업: 외래키 걸어서 데이터 정합성 보장
• 대규모/마이크로서비스: 외래키 없이 애플리케이션 레벨에서 관리

어느 쪽이 맞다고 단정할 수 없고, 팀 상황에 맞게 결정하는 게 맞다.

09장: 옵티마이저와 힌트 — 쿼리 실행 전에 무슨 일이 일어나는가

쿼리를 날리면 MySQL이 어떻게 실행하는지 그 흐름을 이해하면, 왜 인덱스가 안 타는지, 왜 쿼리가 느린지 감이 잡히기 시작해요.

쿼리 실행 절차

1SQL 쿼리 입력
2     ↓
3[파서] SQL 문법 검사, 파스 트리 생성
4     ↓
5[전처리기] 테이블/컬럼 존재 여부, 권한 확인
6     ↓
7[옵티마이저] 최적 실행 계획 수립 ← 핵심
8     ↓
9[실행 엔진] 스토리지 엔진에 읽기/쓰기 요청
10     ↓
11[스토리지 엔진] 실제 디스크 I/O

옵티마이저가 "이 쿼리를 어떻게 실행할지" 결정한다. MySQL은 비용 기반 옵티마이저(CBO, Cost-Based Optimizer) 를 사용한다. 각 실행 방법의 비용(디스크 I/O 횟수, 처리할 행 수 등)을 계산해서 가장 저렴한 방법을 선택한다.

옵티마이저가 항상 최선의 판단을 하는 건 아니에요. 통계 정보가 오래됐거나, 비용 계산 모델이 실제와 다를 때 잘못된 실행 계획을 세우기도 한다. 그럴 때 힌트를 쓰는 거다.

풀 테이블 스캔이 선택되는 경우

인덱스가 있는데도 Full Table Scan을 하는 경우가 있어요. 옵티마이저가 의도적으로 선택하는 거다.

1-- 전체 행이 100만 건인 테이블에서
2SELECT * FROM orders WHERE status = 'paid';
3-- status='paid'가 전체의 70%라면?
4-- 인덱스 타면 70만 번 랜덤 I/O
5-- Full Scan하면 순차 I/O 한 번
6-- → 옵티마이저가 Full Scan 선택 = 맞는 판단

그래서 인덱스가 없어서 느린 건지, 아니면 인덱스가 있어도 옵티마이저가 안 쓰는 건지를 EXPLAIN으로 구분해야 한다.

ORDER BY 최적화

ORDER BY는 크게 두 가지 방식으로 처리된다.

1인덱스를 이용한 정렬:
2  인덱스가 이미 정렬되어 있으므로 추가 정렬 없음
3  → 빠름, Extra 컬럼에 아무것도 없음
4
5파일 소트(Filesort):
6  결과를 임시 버퍼에 담아서 정렬
7  → 느림, Extra 컬럼에 "Using filesort" 표시

인덱스로 정렬을 커버하려면 WHERE와 ORDER BY 컬럼이 인덱스와 맞아야 한다.

1-- (user_id, created_at) 인덱스가 있을 때
2SELECT * FROM orders
3WHERE user_id = 123
4ORDER BY created_at DESC;
5-- → 인덱스로 정렬까지 처리 가능, Filesort 없음

조인 최적화

MySQL은 Nested Loop Join(NL Join) 을 기본으로 사용한다. 외부 테이블(드라이빙 테이블)을 한 행씩 읽으면서 내부 테이블을 반복 조회한다.

1드라이빙 테이블(users) 100건 × 내부 테이블(orders) 조회
2→ orders에 user_id 인덱스가 있으면: 100번의 인덱스 조회
3→ orders에 인덱스가 없으면: 100번의 Full Scan (재앙)

JOIN을 쓴다면 조인 컬럼에 반드시 인덱스가 있어야 한다. 없으면 조인할 때마다 전체 테이블을 스캔하게 된다.

드라이빙 테이블 선택도 중요해요. 결과 행 수가 적은 쪽을 드라이빙 테이블로 써야 내부 루프가 덜 돈다. 옵티마이저가 자동으로 선택하지만, 잘못 선택하면 힌트로 강제할 수 있다.

쿼리 힌트

옵티마이저의 판단이 잘못됐을 때 힌트로 유도할 수 있다.

1-- 1. STRAIGHT_JOIN: 조인 순서 강제
2SELECT STRAIGHT_JOIN u.name, o.amount
3FROM users u
4JOIN orders o ON o.user_id = u.id;
5-- users를 드라이빙 테이블로 강제
6
7-- 2. USE INDEX: 특정 인덱스 사용 권유
8SELECT * FROM orders USE INDEX (idx_status)
9WHERE status = 'paid';
10
11-- 3. FORCE INDEX: 옵티마이저 무시하고 강제
12SELECT * FROM orders FORCE INDEX (idx_status)
13WHERE status = 'paid';
14
15-- 4. IGNORE INDEX: 특정 인덱스 무시
16SELECT * FROM orders IGNORE INDEX (idx_created_at)
17WHERE status = 'paid';

[!warning] 힌트 남용 주의 힌트는 데이터 분포가 바뀌어도 고정된다. 오늘은 맞는 힌트가 6개월 후에는 틀린 힌트가 될 수 있다. 통계를 갱신하고 EXPLAIN을 먼저 봐서 옵티마이저가 정말 틀렸는지 확인한 뒤에 힌트를 쓰는 게 맞다.

10장: 실행 계획 — EXPLAIN을 읽는 법

이게 이 책 후반부에서 가장 실용적인 챕터였어요. 느린 쿼리를 만났을 때 EXPLAIN 없이 최적화하는 건 눈 감고 운전하는 거랑 비슷하다.

통계 정보

옵티마이저가 실행 계획을 세울 때 테이블 통계 정보를 참고한다. 테이블에 몇 행이 있는지, 인덱스 카디널리티가 어느 정도인지 등이다.

1-- 테이블 통계 확인
2SHOW TABLE STATUS LIKE 'orders'\G
3
4-- 통계 수동 갱신
5ANALYZE TABLE orders;
6-- 데이터가 대량으로 바뀐 뒤 실행 계획이 이상하면 여기서 시작

통계가 오래되면 옵티마이저가 엉뚱한 판단을 한다. "갑자기 쿼리가 느려졌어요"라고 할 때 ANALYZE TABLE로 해결되는 경우가 있다.

EXPLAIN 실행

1EXPLAIN SELECT * FROM orders
2WHERE user_id = 123 AND status = 'paid'
3ORDER BY created_at DESC;

핵심 칼럼 읽는 법

EXPLAIN 결과에서 실제로 봐야 할 칼럼들만 정리했어요.

type — 가장 중요한 칼럼

접근 방식을 나타낸다. 위에서 아래로 갈수록 나쁘다.

ALL이 보이면 일단 의심해야 한다. 행 수가 수백만이면 장애 예약이다.

key — 실제로 사용한 인덱스

possible_keys는 사용 가능한 후보 인덱스 목록이고, key는 옵티마이저가 실제로 선택한 인덱스다. key가 NULL이면 인덱스를 안 쓰는 거다.

1possible_keys: idx_user_id, idx_status
2key: idx_user_id        → idx_user_id 사용 중
3key: NULL               → 인덱스 미사용 (Full Scan)

rows — 예상 처리 행 수

옵티마이저가 처리해야 할 것으로 예상하는 행 수다. 정확한 결과 행 수가 아니라 예측값이다.

1rows: 1         → 거의 최적
2rows: 100       → 괜찮음
3rows: 1,000,000 → 문제 있음

Extra — 부가 정보

Using filesort나 Using temporary가 보이면서 행 수가 많으면 쿼리 튜닝이 필요하다는 신호다.

커버링 인덱스 — Extra: Using index

1-- (user_id, status) 복합 인덱스 존재
2SELECT status FROM orders WHERE user_id = 123;
3-- SELECT하는 컬럼(status)이 인덱스에 포함됨
4-- → 실제 테이블 행을 읽을 필요 없음
5-- → Extra: Using index

커버링 인덱스는 쿼리에서 필요한 컬럼이 모두 인덱스에 포함된 경우다. 실제 데이터 파일을 읽지 않아도 되니까 매우 빠르다. EXPLAIN에서 Using index가 보이면 잘 되고 있다는 신호다.

EXPLAIN 실전 읽기

1EXPLAIN
2SELECT u.name, COUNT(o.id) as order_count
3FROM users u
4LEFT JOIN orders o ON o.user_id = u.id
5WHERE u.created_at > '2025-01-01'
6GROUP BY u.id;

1+----+-------+--------+-------+---------------+--------------+------+----------+-------------+
2| id | type  | table  | key   | possible_keys | rows         | ref  | filtered | Extra       |
3+----+-------+--------+-------+---------------+--------------+------+----------+-------------+
4|  1 | range | u      | idx_c | idx_created   | 5000         | NULL | 100.00   | Using where |
5|  1 | ref   | o      | idx_u | idx_user_id   | 10           | u.id | 100.00   |             |
6+----+-------+--------+-------+---------------+--------------+------+----------+-------------+

• u 테이블: range로 인덱스 범위 스캔 → 괜찮음
• o 테이블: ref로 인덱스 조회 → 좋음
• Using filesort나 Using temporary 없음 → 정상

만약 여기서 o 테이블의 type이 ALL이었다면 orders.user_id에 인덱스가 없다는 뜻이고, 즉시 추가해야 한다.

EXPLAIN 확인 시 주의사항

• rows는 예측값이다. 실제 행 수와 크게 다를 수 있어요. EXPLAIN ANALYZE(MySQL 8.0.18+)를 쓰면 실제 실행 통계를 볼 수 있다.
• 서브쿼리 주의. 서브쿼리가 있으면 id가 다르게 나오고, 내부적으로 임시 테이블을 만들 수 있다. Using temporary가 보이면 서브쿼리를 JOIN으로 바꾸는 걸 검토해보자.
• 통계가 오래됐을 수 있다. EXPLAIN이 이상해 보이면 ANALYZE TABLE 후에 다시 확인해본다.

1EXPLAIN ANALYZE
2SELECT * FROM orders WHERE user_id = 123;
3-- 예측값과 실제 실행 결과를 함께 보여줌
4-- actual rows=150 (predicted rows=120) 식으로 나옴

마치며

솔직히 이 책은 한 번 읽어서 전부 소화되는 책이 아니에요. 나중에 실무에서 느린 쿼리를 만나거나 장애가 생겼을 때 다시 펼쳐보게 될 것 같아요.

그래도 이번에 공부하면서 머릿속에 남은 게 있다면:

• 압축과 암호화는 필요할 때 찾아서 쓰자. 무조건 켜는 게 아니라 디스크 비용, 컴플라이언스 요구에 따라 판단해야 한다.
• 인덱스는 설계가 전부다. 많다고 좋은 게 아니고, 쓰기 비용과 읽기 성능의 트레이드오프다. PK는 순차적인 값이 유리하다.
• EXPLAIN은 반드시 읽어야 한다. type이 ALL이고 rows가 수백만이면 무조건 조치가 필요하다. Using filesort, Using temporary는 개선 신호다.

책을 다 읽은 건 아니고, 이해가 안 되는 부분도 아직 많지만 이 정도 맥락을 잡고 나면 나중에 문제를 만났을 때 어디를 봐야 하는지는 알 것 같아요.

2권도 언젠가는... ㅎㅎ