MySQL을 사용할 때 “MySQL이 데이터를 읽는다”라고 말하기 쉽지만, 내부에서는 역할이 나뉩니다. SQL을 해석하고 실행 계획을 고르는 계층과, 실제 페이지를 읽고 쓰는 계층은 다릅니다. 이 경계를 모르면 실행 계획, 인덱스, 잠금, 버퍼 풀 문제를 한 덩어리로 보게 됩니다.
MySQL이 느리다고 할 때 바로 인덱스 하나를 더 붙이는 방식으로는 원인을 좁히기 어렵습니다. 옵티마이저가 어떤 실행 계획을 골랐는지와 InnoDB가 그 계획을 실제 row 접근, buffer pool, lock, redo·undo 비용으로 어떻게 실행했는지는 다른 층위의 문제입니다. 아래에서는 그 경계를 나눠 보고, EXPLAIN 결과 뒤에 숨어 있는 실제 비용을 같이 읽는 방법을 정리합니다.
MySQL은 하나처럼 보이지만 내부에서는 두 층으로 움직입니다
클라이언트가 SQL을 보내면 먼저 MySQL 엔진이 요청을 받습니다. MySQL 엔진은 SQL을 파싱하고, 권한을 확인하고, 옵티마이저를 통해 실행 계획을 선택합니다. 하지만 실제 데이터를 저장한 페이지를 읽고 쓰는 일은 스토리지 엔진이 맡습니다. InnoDB는 MySQL에서 가장 널리 쓰이는 트랜잭션 스토리지 엔진입니다.
| 계층 | 주요 역할 | 대표 관심사 |
|---|---|---|
| MySQL 엔진 | SQL parsing, optimizer, execution plan, handler 호출 | join order, index 선택, filesort, temporary table |
| InnoDB | page read/write, B+Tree 탐색, MVCC, lock, redo/undo | clustered index, buffer pool, lock wait, transaction recovery |
이 둘을 이어주는 경계가 handler API입니다. MySQL 엔진은 “이 조건으로 row를 읽어 달라”고 요청하고, InnoDB는 실제 인덱스와 페이지를 탐색해 row를 반환합니다.
이 경계를 나누는 이유는 분명합니다. 실행 계획에서 인덱스가 선택됐다는 사실과 실제 I/O가 적었다는 사실은 같지 않습니다. MySQL 엔진은 접근 방법을 고르고, InnoDB는 그 선택을 페이지 읽기, 버퍼 풀 조회, MVCC 버전 확인, 락 대기 같은 비용으로 실행합니다.
쿼리는 위에서 결정되고 비용은 아래에서 지불됩니다
SQL 실행 흐름을 단순화하면 다음과 같습니다.
1. 클라이언트가 SQL을 보낸다.
2. MySQL 엔진이 SQL을 파싱하고 권한을 확인한다.
3. 옵티마이저가 가능한 실행 계획 중 하나를 선택한다.
4. 실행 엔진이 handler API로 row 접근을 요청한다.
5. InnoDB가 인덱스와 page를 탐색한다.
6. 필요한 경우 MVCC와 lock 규칙을 적용한다.
7. 결과 row가 다시 MySQL 엔진으로 올라간다.실행 계획에서 type=range나 type=ref가 보이면 좋아 보일 수 있습니다. 하지만 그 아래에서 InnoDB가 secondary index를 따라간 뒤 clustered index를 반복해서 다시 읽고 있다면, 실제 비용은 생각보다 커질 수 있습니다.
반대로 실행 계획만 보면 단순해 보여도, 필요한 page가 buffer pool에 잘 올라와 있으면 반복 조회는 훨씬 가볍게 끝납니다. 그래서 느린 쿼리를 볼 때는 실행 계획과 InnoDB 상태를 함께 봐야 합니다.
운영에서 이 둘을 구분할 때는 지표도 나눠 보는 편이 안전합니다.
| 확인 층위 | 먼저 볼 신호 | 해석 기준 |
|---|---|---|
| MySQL 엔진 | EXPLAIN, rows, Extra, handler read 계열 지표 | 옵티마이저가 어떤 접근 경로를 선택했고, 그 선택이 예상 row 수와 맞는지 확인 |
| InnoDB | buffer pool read, lock wait, rows read, redo/undo 상태 | 선택된 경로를 수행하는 동안 실제 page 접근과 트랜잭션 비용이 어디서 커졌는지 확인 |
InnoDB의 핵심은 clustered index입니다
InnoDB를 이해할 때 가장 먼저 봐야 할 구조는 clustered index입니다. InnoDB 테이블은 primary key 순서로 레코드를 저장합니다. primary key 자체가 clustered index이며, leaf node에 실제 row 데이터가 들어 있습니다.
이 구조 때문에 primary key 선택은 단순한 식별자 선택이 아닙니다. 저장 순서, range scan 효율, secondary index 크기까지 함께 결정합니다.
| 구조 | 저장되는 것 | 조회 시 비용 |
|---|---|---|
| Clustered index | primary key와 실제 row 데이터 | PK로 찾으면 바로 row 접근 |
| Secondary index | secondary key와 primary key 값 | secondary index 탐색 후 PK로 row 재조회 가능 |
secondary index의 leaf node에는 실제 row 위치가 아니라 primary key 값이 들어 있습니다. 따라서 secondary index만으로 필요한 컬럼을 모두 해결하지 못하면, InnoDB는 primary key를 들고 clustered index를 다시 찾아갑니다. 이를 보통 bookmark lookup 또는 back to table이라고 부릅니다.
이 구조는 다음 결론으로 이어집니다.
- primary key가 길면 secondary index도 함께 커질 수 있습니다.
- secondary index 조회가 많고 필요한 컬럼이 많으면 PK lookup 비용이 누적될 수 있습니다.
- covering index가 효과적인 이유는 clustered index 재접근을 줄일 수 있기 때문입니다.
- primary key range scan은 저장 순서와 맞기 때문에 효율적일 수 있습니다.
- covering index를 만들기 위해 컬럼을 무작정 늘리면 쓰기 비용과 인덱스 크기도 함께 커집니다. 읽기 최적화가 필요한 쿼리인지, 쓰기 부하가 더 중요한 테이블인지 먼저 나눠야 합니다.
handler 경계를 알면 EXPLAIN을 더 현실적으로 읽게 됩니다
EXPLAIN은 옵티마이저가 어떤 전략을 선택했는지 보여줍니다. 하지만 EXPLAIN만으로 InnoDB 내부의 모든 page 접근 비용을 직접 보여주지는 않습니다.
예를 들어 아래 쿼리를 생각해볼 수 있습니다.
SELECT id, name, created_at
FROM users
WHERE organization_id = 10
ORDER BY created_at DESC
LIMIT 20;옵티마이저가 (organization_id, created_at) 인덱스를 선택했다면 정렬 비용을 줄일 수 있습니다. 하지만 select 목록에 인덱스에 없는 컬럼이 많다면 InnoDB는 secondary index에서 PK를 얻은 뒤 clustered index를 다시 읽어야 합니다.
| EXPLAIN에서 볼 것 | InnoDB 관점에서 이어서 볼 것 |
|---|---|
key | 선택된 인덱스가 실제 접근 경로와 맞는지 |
rows | 예상 row 수가 실제 스캔량과 크게 다르지 않은지 |
Extra | Using filesort, Using temporary, Using index 여부 |
| access type | range/ref라도 PK lookup이 반복되는지 |
Using index는 필요한 컬럼을 인덱스만으로 읽는 covering index 상황을 의미할 수 있습니다. 이때는 clustered index 재접근을 줄일 수 있어 효과가 큽니다. 반대로 Using filesort가 보인다면 정렬을 인덱스 순서로 해결하지 못하고 별도 정렬 비용이 발생할 수 있습니다.
가능하면 추정만 보지 않고 실제 실행 결과도 같이 봐야 합니다. EXPLAIN의 rows는 옵티마이저가 계산한 추정치이고, 실제 스캔량과 지연은 데이터 분포, 통계 신선도, 버퍼 풀 상태, 락 대기에 따라 달라질 수 있습니다. MySQL 8 환경이라면 EXPLAIN ANALYZE로 추정과 실제 실행 흐름의 차이를 확인하는 것도 도움이 됩니다.
InnoDB는 단순 저장소가 아니라 트랜잭션 엔진입니다
InnoDB는 page를 읽고 쓰는 역할만 하지 않습니다. 트랜잭션을 지원하기 위해 redo log, undo log, lock, MVCC를 함께 관리합니다.
쓰기 경로를 단순화하면 다음과 같습니다.
1. SQL 실행 요청이 내려온다.
2. InnoDB가 필요한 page를 buffer pool에서 찾거나 디스크에서 읽는다.
3. 변경 전 버전을 undo log에 남긴다.
4. 변경 내용을 redo log에 기록한다.
5. buffer pool의 page를 수정하고 dirty page로 표시한다.
6. commit 정책에 따라 redo log flush가 일어난다.
7. dirty page는 이후 checkpoint 과정에서 디스크로 반영된다.UPDATE 하나도 내부적으로는 여러 비용을 동반합니다.
- rollback과 consistent read를 위한 undo log
- 장애 복구를 위한 redo log
- buffer pool의 dirty page 증가
- 관련 secondary index 갱신
- 격리 수준과 조건에 따른 row lock 또는 next-key lock
이 구조 덕분에 InnoDB는 장애 후에도 committed transaction을 복구할 수 있고, 트랜잭션 중 과거 버전을 읽을 수 있습니다. 대신 쓰기 부하가 커지면 redo flush, dirty page, purge 지연, lock wait가 함께 문제로 올라올 수 있습니다.
MVCC와 lock은 같은 저장 구조 위에서 동작합니다
InnoDB의 MVCC는 undo log를 기반으로 과거 버전을 읽을 수 있게 합니다. 덕분에 일반적인 consistent read는 다른 트랜잭션의 쓰기와 충돌하지 않고 읽을 수 있습니다. 하지만 모든 읽기가 lock 없이 끝나는 것은 아닙니다.
SELECT * FROM orders WHERE user_id = 10;
SELECT * FROM orders WHERE user_id = 10 FOR UPDATE;첫 번째 쿼리는 일반적인 consistent read로 처리될 수 있습니다. 두 번째 쿼리는 수정 의도를 가진 locking read입니다. 이 경우 InnoDB는 조건에 맞는 레코드나 인덱스 범위를 잠글 수 있습니다.
lock도 인덱스 경로와 연결됩니다. 적절한 인덱스가 없으면 InnoDB는 더 넓은 범위를 탐색하고, 그 과정에서 잠금 범위도 불필요하게 커질 수 있습니다.
Buffer Pool은 반복 조회의 체감 성능을 바꿉니다
InnoDB는 데이터를 page 단위로 읽고, 자주 접근하는 page를 buffer pool에 둡니다. 같은 쿼리라도 필요한 page가 buffer pool에 있으면 디스크 I/O를 줄일 수 있고, 없으면 디스크에서 읽어야 합니다.
그래서 쿼리 성능을 볼 때는 “인덱스를 탔는가”와 함께 “읽은 page가 메모리에 있었는가”도 중요합니다.
운영에서 자주 보는 지표는 다음과 같습니다.
| 지표 | 의미 |
|---|---|
| buffer pool hit ratio | 요청한 page를 메모리에서 찾은 비율 |
| dirty page 비율 | 메모리에는 변경됐지만 아직 디스크에 반영되지 않은 page 비율 |
| read IOPS | 디스크에서 page를 읽는 빈도 |
| redo log fsync | commit과 durability 비용 |
| history list length | purge가 밀려 undo가 오래 남는지 |
| lock wait | 트랜잭션이 lock을 기다리는 시간 |
실행 계획은 좋아 보이는데 느리다면 buffer pool miss나 lock wait를 같이 확인해야 합니다. 반대로 실행 계획상 rows가 많아 보여도 반복 조회가 대부분 buffer pool에서 처리된다면 체감은 다를 수 있습니다.
문제를 볼 때는 엔진 층위를 먼저 나눕니다
느린 쿼리가 있을 때 바로 인덱스를 추가하면 문제를 악화시킬 수도 있습니다. 인덱스는 읽기를 빠르게 할 수 있지만 쓰기 비용과 저장 공간을 늘립니다. 먼저 문제가 어느 층위인지 나누는 편이 안전합니다.
| 증상 | 먼저 볼 곳 | 가능한 원인 |
|---|---|---|
| 예상보다 많은 row scan | MySQL 엔진 / optimizer | 통계 부정확, 잘못된 index 선택, 조건식 형태 |
| index를 탔는데도 느림 | InnoDB 읽기 경로 | PK lookup 반복, buffer pool miss, covering index 부재 |
| UPDATE 지연 | InnoDB 쓰기 경로 | redo flush, dirty page 증가, secondary index 갱신 |
| 트랜잭션 대기 | InnoDB lock / MVCC | lock wait, next-key lock, 긴 트랜잭션 |
| purge 지연 | InnoDB undo | 오래 열린 트랜잭션, history list 증가 |
이렇게 나누면 “쿼리가 느리다”라는 문장을 더 구체적인 질문으로 바꿀 수 있습니다.
- 옵티마이저가 잘못된 전략을 골랐는가?
- InnoDB가 선택된 전략을 수행하는 과정에서 page를 많이 읽는가?
- secondary index에서 clustered index로 되돌아가는 비용이 큰가?
- lock wait 때문에 실행 시간이 늘어났는가?
- 쓰기 로그와 dirty page flush가 병목인가?
EXPLAIN 이후에 확인할 비용
MySQL 엔진과 InnoDB는 같은 시스템 안에 있지만 역할이 다릅니다. MySQL 엔진은 SQL을 해석하고 실행 전략을 고릅니다. InnoDB는 그 전략을 실제 page read/write, B+Tree 탐색, MVCC, lock, redo/undo로 수행합니다.
운영에서 남는 기준은 “MySQL이 느리다”에서 멈추지 않는 것입니다. 옵티마이저가 고른 계획의 문제인지, InnoDB가 실제 row를 찾는 과정에서 비용이 커진 문제인지, 쓰기 경로에서 redo·undo·dirty page가 쌓인 문제인지 먼저 층위를 나눠야 합니다.
- 실행 계획은 MySQL 엔진의 선택이고, 실제 읽기 비용은 InnoDB 접근 경로에서 결정됩니다.
- InnoDB의 clustered index 구조 때문에 primary key 선택은 조회와 secondary index 크기에 함께 영향을 줍니다.
- secondary index는 필요한 컬럼을 다 담지 못하면 primary key lookup을 다시 수행할 수 있습니다.
- 쓰기 작업은 row 변경뿐 아니라 redo, undo, dirty page, secondary index 갱신 비용을 동반합니다.
- MVCC와 lock은 인덱스 접근 경로와 연결되어 있어, 인덱스 설계가 잠금 범위에도 영향을 줍니다.
- 운영에서는
EXPLAIN과 InnoDB 지표를 함께 봐야 병목의 층위를 분리할 수 있습니다.
이 경계를 나누면 다음 조치도 달라집니다. 옵티마이저가 잘못된 경로를 골랐다면 통계, 조건식, 인덱스 후보를 확인해야 하고, InnoDB에서 비용이 커졌다면 PK lookup 반복, buffer pool miss, lock wait, redo/undo 압박을 봐야 합니다. EXPLAIN은 출발점이지 끝이 아닙니다. Using index, Using where, rows 같은 표시가 보이더라도 실제 비용은 buffer pool hit, handler read, 디스크 I/O, 잠금 대기에서 다시 확인해야 합니다. 느린 쿼리를 볼 때는 실행 계획과 스토리지 엔진 지표를 같이 놓아야 비용이 어디서 생겼는지 좁힐 수 있습니다.