# 슬라이드 학습 가이드 — SQL 한 문장이 InnoDB 페이지를 만날 때까지 > 발표를 듣지 않아도 MySQL 서버와 InnoDB의 경계, SQL 처리 과정, 저장 구조, 저장 프로그램 객체를 순서대로 이해할 수 있도록 정리한 가이드입니다. 설명은 MySQL 8.0 공식 문서와 MySQL 8.0.46 Source Code Documentation을 기준으로 검증했습니다. ## 읽기 전에 알아둘 것 MySQL은 SQL을 이해하고 실행하는 **서버**와 실제 테이블 데이터를 읽고 쓰는 **스토리지 엔진**을 분리합니다. InnoDB는 MySQL 8.0의 기본 스토리지 엔진이지만 MySQL 서버 전체와 같은 뜻은 아닙니다. 이 구분이 가이드 전체의 출발점입니다. 또 하나의 기준은 **페이지(Page)**입니다. InnoDB는 행을 하나씩 디스크와 메모리 사이로 옮기지 않습니다. 여러 행과 인덱스 레코드가 들어 있는 페이지를 기본 I/O·캐시 단위로 씁니다. 기본 크기는 16KB이며 다른 크기는 인스턴스를 초기화할 때 정합니다. ## 이 가이드로 공부하고 발표하는 방법 이 자료는 슬라이드의 글자를 외우는 용도가 아닙니다. 한 문장의 SQL이 어느 계층에서 어떤 상태로 바뀌는지 추적하는 교재입니다. 다음 세 번의 순서로 읽으면 발표 준비가 빨라집니다. 1. **첫 번째 읽기 — 위치를 잡습니다.** 각 장에서 다루는 주체가 Client, MySQL Server, Storage Engine, InnoDB 메모리, InnoDB 디스크 가운데 어디에 있는지 먼저 표시합니다. 2. **두 번째 읽기 — 원인과 결과를 연결합니다.** “이 단계가 실패하면 무엇이 보이는가?”, “다음 단계에는 무엇을 넘기는가?”라는 질문에 답합니다. 3. **세 번째 읽기 — 직접 설명합니다.** 슬라이드를 보지 않고 30초 안에 핵심을 말한 뒤, 아래의 실습 SQL로 확인합니다. 설명이 막히면 용어를 더 외우기보다 앞뒤 단계의 입력과 출력을 다시 봅니다. 발표자는 모든 세부 내용을 말할 필요가 없습니다. 본문에 있는 핵심 흐름을 먼저 설명하고, 청중 질문이 나왔을 때 ‘자주 받는 질문’과 ‘실습’ 부분을 꺼내 쓰면 됩니다. 32–38분 발표라면 1–4장은 6분, 5–10장은 11분, 11–16장은 12분, 17–19장은 6분, 20–21장은 3분 정도로 배분합니다. ## 공통 예제 스키마 가이드 전체에서 다음 두 테이블을 같은 예제로 사용합니다. customers는 고객, orders는 주문입니다. 주문은 고객을 참조하고, 주문 상태와 생성 시각으로 조회할 수 있습니다. ~~~sql CREATE TABLE customers ( id BIGINT NOT NULL, name VARCHAR(100) NOT NULL, PRIMARY KEY (id) ) ENGINE = InnoDB; CREATE TABLE orders ( id BIGINT NOT NULL, customer_id BIGINT NOT NULL, status VARCHAR(20) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY ix_orders_status_created (status, created_at), CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ) ENGINE = InnoDB; ~~~ 발표에서 반복해서 사용할 조회는 다음과 같습니다. ~~~sql SELECT o.id, c.name FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.status = 'PAID' ORDER BY o.created_at DESC LIMIT 20; ~~~ 이 쿼리를 기준으로 보면 status 조건은 보조 인덱스, orders.id와 customers.id는 클러스터드 인덱스, 실제 페이지 읽기는 Buffer Pool과 Tablespace 설명에 연결됩니다. ## 먼저 구분할 핵심 용어 | 용어 | 이 가이드에서의 뜻 | 헷갈리기 쉬운 표현 | | --- | --- | --- | | MySQL Server | 연결, 권한, SQL 해석, 최적화와 실행 흐름을 맡는 공통 계층 | InnoDB와 같은 말이 아님 | | Storage Engine | 테이블의 레코드·인덱스·물리 저장을 담당하는 엔진 | 모든 엔진의 기능이 같지 않음 | | Session | 한 연결에 묶인 변수, 트랜잭션, 문자 집합 등의 논리 상태 | Thread와 같은 말이 아님 | | Handler API | Executor가 Storage Engine에 행 연산을 요청하는 경계 | 네트워크 API가 아님 | | Iterator | 실행 계획의 연산 노드가 다음 행을 생산하는 실행 단위 | 디스크 페이지 자체가 아님 | | Page | InnoDB가 캐시하고 읽고 쓰는 기본 블록 | 행 하나와 일대일이 아님 | | Tablespace | Page를 담는 InnoDB의 논리 저장 공간 | 항상 OS 파일 하나와 일대일이 아님 | | Clustered Index | 리프 레코드가 행 데이터를 함께 담는 InnoDB 인덱스 | 조회 결과 순서를 보장하지 않음 | ## 1. SQL 한 문장이 InnoDB 페이지를 만날 때까지 ![Slide 1 — SQL 한 문장이 InnoDB 페이지를 만날 때까지](mysql-innodb-architecture-guide-assets/slide-01.png) ### 핵심 개념 발표는 SQL 한 문장을 끝까지 추적합니다. 애플리케이션이 보낸 `SELECT`가 곧바로 데이터 파일을 읽는 것은 아닙니다. 연결과 세션을 거쳐 서버에 들어오면 SQL 구조와 의미를 확인하고, 옵티마이저가 실행 계획을 고릅니다. 실행기는 그 계획에 맞춰 스토리지 엔진에 필요한 행을 요청합니다. InnoDB는 Buffer Pool에서 페이지를 찾고, 없으면 Tablespace에서 읽습니다. ### 흐름과 의미 이 흐름을 알면 문제를 한 덩어리로 보지 않게 됩니다. 연결이 많아 생긴 메모리 문제인지, 옵티마이저가 잘못된 계획을 선택한 것인지, InnoDB가 많은 페이지를 읽는 것인지 나눠 볼 수 있습니다. 뒤의 모든 슬라이드는 이 경로의 한 구간을 확대합니다. ### 슬라이드가 생략한 세부 사항 화면은 읽기 중심의 대표 경로만 보여 줍니다. 실제 요청에는 인증, 메타데이터 잠금, 트랜잭션 가시성, 네트워크 전송과 cleanup도 끼어듭니다. 어느 단계가 병목인지 확인하려면 실행 계획과 서버·InnoDB 지표를 함께 봅니다. ### 확인 포인트 - Parser에서 Page까지의 순서를 말로 연결해 본다. - 느린 요청을 Connection·Plan·Page 문제로 나눠 본다. ### 왜 중요한가 “MySQL이 느리다”는 말만으로는 점검을 시작하기 어렵습니다. 같은 2초 지연도 연결 대기, 잘못된 실행 계획, Buffer Pool miss, 잠금 대기처럼 원인이 전혀 다릅니다. 이 장에서는 문제를 Connection, Plan, Page라는 관찰 단위로 나눕니다. ### 내부 동작을 단계별로 클라이언트가 SQL 문자열을 보내면 서버는 먼저 어떤 세션이 보낸 요청인지 확인합니다. Parser와 Resolver/Preparation은 문자열을 실행 가능한 내부 구조로 바꿉니다. Optimizer는 후보 계획의 비용을 비교하고, Executor는 선택된 계획의 iterator를 실행합니다. 행이 필요해지면 Handler API를 거쳐 InnoDB로 내려갑니다. InnoDB는 필요한 인덱스 페이지가 Buffer Pool에 있는지 확인하고, 없으면 해당 Tablespace에서 페이지를 읽습니다. 결과 행은 이 경로를 반대로 올라가 네트워크 응답이 됩니다. 읽기와 쓰기는 중간까지 같은 경로를 지나지만 InnoDB 내부 작업은 달라집니다. 쓰기에서는 undo, redo, dirty page, flush와 commit 조건이 추가됩니다. 이번 발표가 SELECT 경로에 집중하는 이유는 공통 뼈대를 먼저 익히기 위해서입니다. ### 직접 확인해 볼 실습 ~~~sql EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE id = 42; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; ~~~ 첫 명령은 서버가 고른 계획을 보여 줍니다. 두 번째 명령의 Innodb_buffer_pool_read_requests는 논리 읽기 요청, Innodb_buffer_pool_reads는 Buffer Pool에서 해결하지 못해 디스크 읽기가 필요했던 횟수입니다. 두 값을 한 번만 보고 성능을 단정하지 말고 같은 구간의 변화량을 비교합니다. ### 자주 받는 질문 - **Q. SELECT는 언제나 디스크를 읽나요?** A. 아닙니다. 필요한 페이지가 Buffer Pool에 있으면 물리 디스크 읽기 없이 처리될 수 있습니다. - **Q. 실행 계획이 좋으면 무조건 빠른가요?** A. 좋은 출발점이지만 충분조건은 아닙니다. 캐시 상태, 동시성, 잠금, 반환 행 수와 네트워크 전송도 영향을 줍니다. ### 한 문장으로 설명하기 SQL 요청은 문자열에서 계획으로, 계획에서 엔진 호출로, 엔진 호출에서 페이지 접근으로 단계적으로 변환됩니다. ## 2. 오늘 따라갈 네 개의 경계 ![Slide 2 — 오늘 따라갈 네 개의 경계](mysql-innodb-architecture-guide-assets/slide-02.png) ### 핵심 개념 첫 번째 경계는 **Client와 Connection** 사이입니다. 외부 프로그램이 인증된 통신 채널을 만들고 Session 문맥을 얻습니다. 두 번째는 SQL을 구조화하고 계획으로 바꾸는 **SQL 처리 경계**입니다. 세 번째는 Executor가 Handler API로 Storage Engine을 호출하는 **엔진 경계**입니다. 네 번째는 Buffer Pool과 Tablespace, Page가 만나는 **메모리·디스크 경계**입니다. ### 흐름과 의미 네 경계는 설명을 돕기 위한 큰 구분입니다. 실제 MySQL 소스 코드가 정확히 네 모듈로만 나뉜다는 뜻은 아닙니다. 특히 Preprocessor는 공식 구현의 고정된 단일 컴포넌트가 아닙니다. 여기서는 Resolver, Preparation, 권한·의미 검사를 묶어 부르는 교육용 표현으로 씁니다. ### 슬라이드가 생략한 세부 사항 네 경계는 소스 코드의 디렉터리나 함수와 일대일로 맞춘 모듈도가 아닙니다. 설명을 위해 책임을 묶은 지도이며, 버전과 실행 문장 종류에 따라 세부 단계와 순서는 달라집니다. ### 확인 포인트 - 네 경계마다 책임 주체를 하나씩 적어 본다. - Preprocessor가 교육용 묶음이라는 점을 기억한다. ### 왜 중요한가 아키텍처를 외울 때는 모든 구성요소를 같은 수준에 놓기 쉽습니다. Client, Session, Optimizer, Buffer Pool은 서로 다른 수명과 책임을 가집니다. 경계를 먼저 나누면 “누가 상태를 소유하고 누가 결정을 내리는가”가 선명해집니다. ### 네 경계를 질문으로 바꾸기 연결 경계에서는 “누가 접속했고 어떤 Session 상태를 갖는가?”를 묻습니다. SQL 처리 경계에서는 “문법과 의미가 올바른가, 어떤 계획을 선택했는가?”를 묻습니다. 엔진 경계에서는 “Executor가 어떤 행 연산을 요청했는가?”를 봅니다. 저장 경계에서는 “그 연산이 어떤 인덱스와 Page를 읽고 Buffer Pool에서 해결됐는가?”를 확인합니다. 실제 소스 코드는 이 네 상자로만 구성되지 않습니다. 예를 들어 metadata locking, privilege check, prepared statement 재준비, cleanup은 문장 종류와 상태에 따라 여러 지점에 걸칩니다. 경계는 구현을 단순화한 학습 지도이지 함수 호출 목록이 아닙니다. ### 스스로 그려 보는 연습 종이에 네 칸을 그리고 다음 항목을 배치합니다: thread_cache_size, Parser, EXPLAIN, Handler API, Buffer Pool, .ibd, Session variable. 각 항목이 어느 칸에 들어가는지뿐 아니라 앞뒤로 무엇을 받는지도 한 줄로 적습니다. ### 자주 받는 질문 - **Q. Preprocessor는 정확히 어디에 있나요?** A. 교육 자료에서 편의상 쓰는 묶음입니다. 공식 구현을 설명할 때는 Resolver와 Preparation, 권한·의미 검사라고 표현하는 편이 안전합니다. - **Q. Storage Engine 경계 아래는 전부 InnoDB인가요?** A. 테이블이 InnoDB를 사용할 때 그렇습니다. 다른 엔진 테이블이면 같은 Handler 계층 아래 다른 구현이 호출됩니다. ### 한 문장으로 설명하기 네 경계는 모듈 이름을 외우기 위한 표가 아니라 요청 상태가 바뀌는 지점을 찾는 진단 지도입니다. ## 3. MySQL 서버와 InnoDB의 역할 경계 ![Slide 3 — MySQL 서버와 InnoDB의 역할 경계](mysql-innodb-architecture-guide-assets/slide-03.png) ### 핵심 개념 MySQL 서버의 공통 계층은 클라이언트 연결, 인증과 권한, SQL 문법 처리, 이름 해석, 실행 계획 최적화, 실행 흐름을 담당합니다. InnoDB는 이 공통 계층 아래에서 B-tree 인덱스, 행 레코드, Buffer Pool, Tablespace, 트랜잭션과 복구 구조를 담당합니다. MySQL 서버가 계획을 만든 뒤 실제 행 접근을 InnoDB에 위임하는 구조입니다. ### 흐름과 의미 이 분리는 진단에 직접 도움이 됩니다. `EXPLAIN`에서 조인 순서나 접근 방식이 이상하면 SQL·Optimizer 계층을 먼저 봅니다. 계획은 합리적인데 디스크 읽기가 지나치게 많다면 Buffer Pool hit/miss, 인덱스 선택도, 페이지 수를 확인합니다. [MySQL Storage Engine Architecture](https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html)는 서버가 공통 API로 여러 엔진을 감싸는 구조를 설명합니다. ### 슬라이드가 생략한 세부 사항 Optimizer는 계획을 만들고 InnoDB는 행을 읽는다는 구분은 유용하지만, 두 영역은 통계와 비용 정보, Handler API, 잠금 요청을 주고받습니다. 완전히 고립된 두 프로세스로 이해하면 안 됩니다. ### 확인 포인트 - 실행 계획은 서버, 행 접근은 엔진이라는 경계를 확인한다. - Handler API가 두 책임을 어떻게 잇는지 설명한다. ### 왜 중요한가 서버와 InnoDB를 구분하지 않으면 튜닝 대상을 잘못 잡습니다. 조인 순서나 인덱스 선택은 서버 Optimizer의 결정이고, Buffer Pool·클러스터드 인덱스·행 잠금은 InnoDB의 책임입니다. 어느 쪽이 문제인지 나눠야 설정과 지표도 맞게 고릅니다. ### 책임을 구체적으로 나누기 MySQL Server는 프로토콜, 인증, 권한, SQL 문법, 이름 해석, 비용 기반 최적화, 실행 계획 순회를 담당합니다. Storage Engine은 Handler API가 요청한 인덱스 탐색, 레코드 읽기·변경, 엔진별 잠금과 영속 저장을 구현합니다. 서버는 “어떤 순서와 방식으로 읽을지”를 결정합니다. InnoDB는 그 요청에 맞는 레코드를 찾고 보호합니다. 두 계층은 단절되어 있지 않습니다. Optimizer는 테이블·인덱스 통계와 엔진이 제공하는 비용 정보를 참고합니다. Executor는 엔진에서 받은 행을 필터링하거나 조인하고, 필요하면 다시 다음 행을 요청합니다. ### 직접 확인해 볼 실습 ~~~sql SHOW VARIABLES LIKE 'default_storage_engine'; SHOW CREATE TABLE orders\G EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE status = 'PAID'; ~~~ 첫 번째 결과는 새 테이블의 기본 엔진, 두 번째는 실제 테이블 엔진과 인덱스·제약, 세 번째는 서버가 고른 접근 계획을 보여 줍니다. 하나의 명령으로 모든 계층을 판단하지 않습니다. ### 자주 받는 질문 - **Q. 트랜잭션은 서버 기능인가요, InnoDB 기능인가요?** A. SQL 문과 트랜잭션 제어 표면은 서버가 제공하지만 실제 undo, 잠금, 격리와 복구의 중심 구현은 InnoDB에 있습니다. 엔진에 따라 지원 수준이 다릅니다. - **Q. 복제도 InnoDB 기능인가요?** A. 복제와 binary log는 주로 서버 계층 기능입니다. InnoDB redo log와 목적을 섞으면 안 됩니다. ### 한 문장으로 설명하기 MySQL Server가 SQL의 실행 방법을 지휘하고 InnoDB가 인덱스와 Page를 이용해 실제 행 접근을 수행합니다. ## 4. Plugin Storage Engine 구조 ![Slide 4 — Plugin Storage Engine 구조](mysql-innodb-architecture-guide-assets/slide-04.png) ### 핵심 개념 MySQL은 테이블마다 스토리지 엔진을 정할 수 있습니다. 서버 위쪽의 Connector와 SQL 계층은 공통이지만, 아래쪽에는 InnoDB, MyISAM, MEMORY처럼 서로 다른 엔진 구현이 놓입니다. 로드 가능한 엔진 플러그인은 `INSTALL PLUGIN`과 `UNINSTALL PLUGIN`으로 관리합니다. 실제 지원 상태는 `SHOW ENGINES`로 확인합니다. ### 흐름과 의미 플러그형 구조가 엔진 차이까지 없애 주지는 않습니다. InnoDB는 트랜잭션, 행 수준 잠금, MVCC, 외래 키를 제공하지만 다른 엔진의 기능은 다를 수 있습니다. 존재하지 않거나 쓸 수 없는 엔진을 지정했을 때 의도치 않은 대체를 막으려면 `NO_ENGINE_SUBSTITUTION` SQL mode도 확인합니다. 자세한 내용은 [Pluggable Storage Engine Architecture](https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage.html)에 있습니다. ### 슬라이드가 생략한 세부 사항 InnoDB는 트랜잭션과 행 수준 잠금, crash recovery, 외래 키를 지원합니다. MyISAM은 트랜잭션을 지원하지 않고 테이블 수준 잠금을 사용합니다. MEMORY는 데이터를 메모리에 두므로 서버 재시작 뒤 데이터가 남는 영속 테이블 용도로 선택하면 안 됩니다. 엔진 비교는 기능표 하나보다 트랜잭션·잠금 단위·영속성·인덱스 제약을 함께 읽어야 합니다. 공식 문서 도식은 사실 확인에 유용하지만 Oracle 문서의 공개·상업적 재배포 권리가 자동으로 허용되는 것은 아닙니다. 이 자료는 로컬 학습용으로 원형을 유지해 사용했습니다. 외부 배포 전에는 별도 권리 검토가 필요합니다. 엔진을 바꾸면 SQL 표면이 같아 보여도 트랜잭션, 잠금 단위, 외래 키, crash recovery, 영속성 조건이 바뀝니다. 특히 MEMORY의 데이터는 서버 재시작 뒤 유지되지 않습니다. ### 확인 포인트 - `SHOW ENGINES`로 실제 지원 상태를 확인한다. - InnoDB·MyISAM·MEMORY의 트랜잭션·잠금·영속성을 비교한다. ### 왜 중요한가 Plugin Storage Engine 구조는 MySQL이 하나의 물리 저장 방식에 고정되지 않았다는 뜻입니다. SQL 표면은 비슷해도 엔진을 바꾸면 트랜잭션, 잠금, 인덱스, 영속성, 장애 복구 조건이 달라집니다. 엔진 선택은 단순한 옵션이 아니라 데이터 안전성과 동시성 모델을 선택하는 일입니다. ### 엔진 선택을 읽는 기준 InnoDB는 MySQL 8.0의 기본 엔진이며 ACID 트랜잭션, commit·rollback, crash recovery, 행 수준 잠금, consistent nonlocking read, 외래 키와 clustered index를 제공합니다. MyISAM은 트랜잭션을 제공하지 않고 테이블 수준 잠금을 사용합니다. MEMORY는 데이터가 RAM에 있으므로 재시작 뒤 유지해야 하는 업무 데이터에 쓰면 안 됩니다. “어느 엔진이 더 빠른가”보다 데이터 보존, 동시 쓰기, 제약 조건, 복구 요구를 먼저 비교합니다. ### 직접 확인해 볼 실습 ~~~sql SHOW ENGINES; SELECT ENGINE, SUPPORT, TRANSACTIONS, XA, SAVEPOINTS FROM INFORMATION_SCHEMA.ENGINES; CREATE TABLE engine_demo (id INT PRIMARY KEY) ENGINE = MEMORY; SHOW CREATE TABLE engine_demo\G ~~~ SHOW ENGINES의 SUPPORT가 DEFAULT인 엔진이 현재 기본값입니다. YES는 사용 가능, NO는 지원되지 않음을 뜻합니다. 실습 테이블은 서버 재시작 뒤 데이터가 사라질 수 있으므로 실제 업무 데이터를 넣지 않습니다. ### 자주 틀리는 설명 - “플러그형이므로 모든 엔진 기능이 호환된다”는 설명은 틀립니다. 공통 Handler 표면 아래의 능력은 엔진마다 다릅니다. - “ENGINE을 생략하면 항상 InnoDB다”도 절대 규칙은 아닙니다. MySQL 8.0 기본 설정은 InnoDB지만 서버·세션 기본값을 바꿀 수 있습니다. - 엔진 플러그인을 제거해도 관련 데이터 파일이 자동으로 안전하게 변환되는 것은 아닙니다. ### 한 문장으로 설명하기 Plugin Storage Engine은 같은 SQL 서버 아래 여러 저장 구현을 연결하지만 데이터 안전성과 기능 차이까지 표준화하지는 않습니다. ## 5. Client → Connection → Session → Thread ![Slide 5 — Client → Connection → Session → Thread](mysql-innodb-architecture-guide-assets/slide-05.png) ### 핵심 개념 **Client**는 MySQL에 접속하는 애플리케이션이나 CLI입니다. **Connection**은 인증된 통신 채널이고, **Session**은 연결에 묶여 유지되는 논리 상태입니다. 세션 변수, 문자 집합, 현재 데이터베이스, 트랜잭션 상태가 여기에 포함됩니다. **Thread**는 요청을 처리하는 실행 단위입니다. ### 흐름과 의미 기본 `one-thread-per-connection` 모델에서는 연결마다 전용 connection thread가 요청을 처리합니다. 연결이 끝난 뒤 스레드는 `thread_cache_size` 설정에 따라 캐시에 돌아가 다음 연결에서 재사용될 수 있습니다. 스레드 재사용은 이전 세션이 살아 있다는 뜻이 아닙니다. Session 상태는 새 연결에 맞게 초기화됩니다. ### 슬라이드가 생략한 세부 사항 MySQL Enterprise Thread Pool을 쓰면 연결과 OS 스레드의 관계가 달라집니다. 여러 연결을 thread group과 실행 스레드가 나눠 처리하므로 “Connection 하나는 항상 OS thread 하나”라고 일반화하면 안 됩니다. 이 차이는 [Connection Management](https://dev.mysql.com/doc/refman/8.0/en/connection-management.html)와 Performance Schema의 `threads` 테이블에서 관찰합니다. 애플리케이션의 connection pool에 있는 논리 요청 수명은 MySQL 서버 Connection 수명과 다릅니다. Thread Cache도 Session을 보존하는 기능이 아니라 종료된 connection thread의 생성 비용을 줄이는 장치입니다. ### 확인 포인트 - Connection과 Session의 종료 조건을 구분한다. - Thread Pool 사용 시 일대일 관계 가정을 버린다. ### 왜 중요한가 Client, Connection, Session, Thread를 같은 말처럼 쓰면 연결 수와 메모리, 동시성 문제를 잘못 해석합니다. 네 용어는 서로 이어지지만 소유하는 상태와 수명이 다릅니다. ### 수명주기를 단계별로 Client가 TCP 또는 Unix socket으로 접속하면 서버가 인증을 수행하고 Connection이 성립합니다. 이 Connection에는 Session 문맥이 붙습니다. 현재 데이터베이스, 문자 집합, SQL mode, Session variable, 임시 테이블과 트랜잭션 상태가 Session에 속합니다. 기본 모델에서는 connection thread가 요청을 읽고 실행합니다. 연결이 종료되면 Session 상태는 끝나지만 thread 객체는 thread_cache_size에 따라 캐시됐다가 새 연결 처리에 재사용될 수 있습니다. 애플리케이션 connection pool도 구분해야 합니다. HTTP 요청 하나가 DB Connection 하나를 새로 만드는 대신 풀에서 빌려 쓸 수 있습니다. 따라서 애플리케이션 요청 수, 풀 크기, MySQL의 실제 연결 수는 같은 값이 아닙니다. ### 직접 확인해 볼 실습 ~~~sql SELECT CONNECTION_ID(); SHOW SESSION VARIABLES LIKE 'sql_mode'; SHOW PROCESSLIST; SELECT THREAD_ID, PROCESSLIST_ID, PROCESSLIST_USER, PROCESSLIST_HOST, PROCESSLIST_STATE FROM performance_schema.threads WHERE TYPE = 'FOREGROUND'; ~~~ 서로 다른 두 터미널에서 CONNECTION_ID()를 실행하고 한쪽 Session의 sql_mode만 바꿔 봅니다. 다른 연결에 같은 변경이 보이지 않는다면 Session 상태의 범위를 확인한 것입니다. ### 자주 받는 질문 - **Q. Connection 하나는 항상 OS thread 하나인가요?** A. 기본 one-thread-per-connection 모델의 설명입니다. Enterprise Thread Pool을 사용하면 여러 연결을 thread group과 실행 thread가 나눠 처리합니다. - **Q. Thread Cache가 Session을 재사용하나요?** A. 아닙니다. 처리 thread 생성 비용을 줄일 뿐 이전 사용자의 Session 상태를 이어받지 않습니다. ### 한 문장으로 설명하기 Connection은 통신 채널, Session은 그 채널의 논리 상태, Thread는 요청을 수행하는 실행 자원입니다. ## 6. Global Memory vs Session Memory ![Slide 6 — Global Memory vs Session Memory](mysql-innodb-architecture-guide-assets/slide-06.png) ### 핵심 개념 메모리는 수명과 공유 범위로 나눠야 합니다. InnoDB Buffer Pool은 대표적인 글로벌 공유 메모리입니다. 서버의 여러 연결이 같은 데이터·인덱스 페이지 캐시를 사용합니다. 테이블 캐시와 테이블 정의 캐시도 서버 범위에서 공유됩니다. ### 흐름과 의미 반면 connection thread에는 thread stack, network/result buffer, 현재 SQL 문자열과 세션 상태가 필요합니다. Sort, join, read buffer는 해당 작업이 생길 때 문장이나 연산 단위로 할당됩니다. MySQL 8.0.12부터 filesort 메모리는 `sort_buffer_size` 전체를 항상 먼저 잡지 않고 필요한 만큼 증가시켜 상한까지 사용합니다. ### 슬라이드가 생략한 세부 사항 메모리를 `global memory + max_connections × 모든 session buffer 최대값`으로만 계산하면 실제 사용과 어긋날 수 있습니다. 일부 버퍼는 쓰지 않으면 할당되지 않습니다. 반대로 복잡한 문장은 여러 join buffer를 동시에 요구하기도 합니다. [How MySQL Uses Memory](https://dev.mysql.com/doc/refman/8.0/en/memory-use.html)는 공유 캐시와 thread-local 메모리 풀을 구분해 설명합니다. 세션 버퍼는 모두 연결 시점에 최대치로 선할당되지 않습니다. 필요한 연산이 생길 때 커지는 버퍼가 있고, 한 문장이 여러 join buffer를 동시에 만들기도 합니다. 최댓값의 단순 곱은 상한 추정일 뿐 실제 사용량이 아닙니다. ### 확인 포인트 - 공유 메모리와 작업별 버퍼를 따로 계산한다. - 최대 연결 수에 모든 버퍼 최댓값을 곱한 수치를 실제 사용량으로 단정하지 않는다. ### 왜 중요한가 MySQL 메모리를 “Buffer Pool + 연결 수 × 모든 버퍼 최대값”으로만 계산하면 과대·과소 추정이 동시에 생깁니다. 공유 영역, 연결 기본 비용, 실제 연산이 있을 때만 생기는 작업 버퍼를 분리해야 합니다. ### 메모리 수명으로 구분하기 Global/Shared 영역에는 InnoDB Buffer Pool과 여러 서버 캐시가 있습니다. 서버 전체 요청이 같은 영역을 사용합니다. Session baseline에는 thread stack, 연결·결과 buffer, Session 상태처럼 연결이 유지되는 동안 필요한 메모리가 포함됩니다. sort, join, read buffer는 해당 연산이 필요할 때 문장 단위로 할당됩니다. 복잡한 조인은 join buffer를 하나 이상 사용할 수 있고, 정렬도 동시에 여러 Session에서 일어날 수 있습니다. sort_buffer_size나 join_buffer_size를 크게 올렸다고 Buffer Pool처럼 서버 전체가 공유하는 큰 영역 하나가 생기는 것이 아닙니다. 연결과 실행 문장 수에 따라 메모리가 증폭될 수 있으므로 전역 변경은 실제 계획과 동시 실행 수를 보고 결정합니다. ### 직접 확인해 볼 실습 ~~~sql SHOW VARIABLES WHERE Variable_name IN ( 'max_connections', 'sort_buffer_size', 'join_buffer_size', 'read_buffer_size', 'read_rnd_buffer_size', 'innodb_buffer_pool_size' ); SELECT EVENT_NAME, CURRENT_NUMBER_OF_BYTES_USED FROM performance_schema.memory_summary_global_by_event_name ORDER BY CURRENT_NUMBER_OF_BYTES_USED DESC LIMIT 20; ~~~ Performance Schema의 memory instrumentation 상태와 MySQL 마이너 버전에 따라 보이는 항목이 다를 수 있습니다. 값이 없다고 메모리를 전혀 쓰지 않는다고 결론 내리면 안 됩니다. ### 자주 받는 질문 - **Q. max_connections를 두 배로 늘리면 메모리도 정확히 두 배가 되나요?** A. 아닙니다. 연결 기본 비용은 늘지만 작업 버퍼는 실제 연산과 동시성에 따라 달라집니다. - **Q. sort_buffer_size를 크게 하면 모든 정렬이 빨라지나요?** A. 정렬 방식과 데이터 크기에 따라 다르며 동시 Session의 총메모리를 키울 수 있습니다. 먼저 실행 계획과 실제 정렬 지표를 확인합니다. ### 한 문장으로 설명하기 MySQL 메모리는 서버 공유 영역과 Session·문장별 동적 영역으로 나눠 수명과 동시 할당 가능성을 함께 계산합니다. ## 7. SQL Query 처리 흐름 ![Slide 7 — SQL Query 처리 흐름](mysql-innodb-architecture-guide-assets/slide-07.png) ### 핵심 개념 Connection thread가 SQL 요청을 받으면 Parser가 문자열을 토큰화하고 문법을 검사해 내부 트리를 만듭니다. 이어지는 Resolver/Preparation 단계는 테이블, 열, 별칭, 뷰, 서브쿼리 참조를 해석하고 권한과 의미 조건을 확인합니다. 필요한 prelocking과 table locking 뒤 Optimizer가 접근 방법과 조인 순서, 변환을 선택합니다. ### 흐름과 의미 Executor는 선택된 계획을 실행합니다. 실행기는 계획의 iterator를 순회하면서 필요한 행 연산을 Handler API에 요청합니다. Storage Engine은 인덱스와 레코드, 페이지를 다루고 결과를 돌려줍니다. 문장 처리가 끝나면 cleanup 단계가 이어집니다. ### 슬라이드가 생략한 세부 사항 MySQL 8.0.46 Source Code Documentation은 parsing 이후 DML 단계를 `Prelocking → Preparation → Locking of tables → Optimization → Execution or explain → Cleanup`으로 설명합니다. 교육용 도식과 실제 구현 용어는 구분해서 읽어야 합니다. 근거는 [SQL Query Execution](https://dev.mysql.com/doc/dev/mysql-server/8.0.46/PAGE_SQL_EXECUTION.html)에 정리돼 있습니다. 실제 DML 경로에는 prelocking, table locking, cleanup이 포함됩니다. Prepared Statement 재실행은 preparation 일부를 생략할 수 있으며, DDL이나 관리 문장은 DML과 같은 경로를 그대로 따르지 않습니다. ### 확인 포인트 - parse·resolve·optimize·execute 뒤 cleanup까지 이어 본다. - Executor가 Handler API를 호출하는 지점을 표시한다. ### 왜 중요한가 쿼리 처리 흐름을 알면 오류와 병목의 위치가 갈립니다. 문법 오류, 존재하지 않는 열, 나쁜 실행 계획, 느린 페이지 읽기는 사용자에게 모두 “쿼리 실패 또는 지연”으로 보이지만 발생 단계가 다릅니다. ### 공식 단계와 교육용 단계를 연결하기 교육용 흐름은 Parser → Resolver/Preparation → Optimizer → Executor → Storage Engine입니다. MySQL 8.0 소스 문서가 설명하는 DML 처리에는 parsing 뒤 prelocking, preparation, table locking, optimization, execution 또는 explain, cleanup이 포함됩니다. 모든 문장이 똑같은 경로를 같은 비용으로 지나지는 않습니다. Prepared Statement 재실행은 일부 준비 작업을 생략하거나 metadata 변화가 있을 때 다시 준비할 수 있습니다. Parser는 SQL을 내부 tree representation으로 바꿉니다. Resolver/Preparation은 테이블·열·별칭·뷰와 표현식의 의미를 확정합니다. Optimizer는 변환과 접근 후보를 비용으로 비교합니다. Executor는 계획의 iterator를 실행하고 Handler API로 Storage Engine에 레코드를 요청합니다. 문장이 끝나면 잠금과 문장 단위 자원을 정리합니다. ### 단계별 실패 예시 ~~~sql -- Parser: 문법 오류 SELECT * FORM orders; -- Resolver/Preparation: 존재하지 않는 열 SELECT ghost_column FROM orders; -- Optimizer/Executor 관찰 EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID'; ~~~ 마지막 명령은 실제로 쿼리를 실행합니다. 운영 데이터 변경 문장이나 비용이 큰 쿼리에 무심코 사용하지 않습니다. ### 자주 받는 질문 - **Q. 권한 검사는 Parser 단계인가요?** A. 문법 파싱과 같은 작업은 아닙니다. 객체 해석과 준비 과정에서 필요한 권한·의미 조건을 확인하며 문장 종류에 따라 세부 시점이 달라질 수 있습니다. - **Q. Query Cache는 이 흐름 어디에 있나요?** A. MySQL 8.0에서는 기존 Query Cache가 제거됐습니다. Buffer Pool을 결과 캐시처럼 설명하면 안 됩니다. ### 한 문장으로 설명하기 SQL 문자열은 구조 확인, 의미 확정, 계획 선택, 계획 실행을 거쳐야 비로소 Storage Engine의 Page 접근으로 이어집니다. ## 8. Parser와 Resolver/Preparation ![Slide 8 — Parser와 Resolver/Preparation](mysql-innodb-architecture-guide-assets/slide-08.png) ### 핵심 개념 Parser는 문장이 SQL 문법에 맞는지 확인합니다. `SELECT * FORM orders`처럼 `FROM`을 잘못 쓴 문장은 여기에서 멈춥니다. Resolver/Preparation은 문법이 맞는 문장 안의 이름과 의미를 확인합니다. 존재하지 않는 `ghost_col`을 참조하거나 권한이 없는 테이블에 접근하면 이 단계에서 오류가 납니다. ### 흐름과 의미 Resolver는 단순한 오탈자 검사기가 아닙니다. 별칭과 와일드카드, 뷰와 서브쿼리, 식의 참조를 실제 객체와 연결합니다. 이 준비가 끝나야 Optimizer가 후보 계획의 비용을 비교합니다. Prepared Statement를 다시 실행할 때는 일부 preparation을 생략할 수 있습니다. 모든 요청이 똑같은 비용으로 이 단계를 반복하지는 않습니다. ### 슬라이드가 생략한 세부 사항 책에서 쓰는 Preprocessor는 Resolver/Preparation과 권한·의미 검사를 한데 묶은 교육용 이름입니다. MySQL 8.0 소스 문서가 고정된 단일 Preprocessor 컴포넌트를 보장하는 것은 아닙니다. ### 확인 포인트 - 문법 오류와 이름·권한 오류의 실패 단계를 나눈다. - Prepared Statement 재실행에서 생략 가능한 preparation을 구분한다. ### 왜 중요한가 문법이 맞아도 실행하지 못하는 SQL이 많습니다. 테이블이나 열이 없고 별칭 범위가 틀렸거나, 데이터형이 맞지 않고 권한이 부족한 경우입니다. Parser와 Resolver/Preparation을 나누면 오류 메시지가 어느 종류인지 빠르게 판단할 수 있습니다. ### Parser가 하는 일 Parser는 문자열을 토큰으로 나누고 MySQL 문법 규칙에 맞는지 검사합니다. SELECT, 식별자, 연산자, 괄호가 어떤 구조를 만드는지 확인해 내부 parse tree를 구성합니다. 옵티마이저 힌트도 구문 안에서 인식됩니다. 이 단계에서는 orders라는 테이블이 실제로 존재하는지까지 확정하지 않습니다. ### Resolver/Preparation이 하는 일 Resolver는 테이블·열·별칭이 가리키는 실제 객체를 찾습니다. SELECT *를 실제 열 목록으로 확장하고, 표현식의 데이터형과 집계·GROUP BY 조건을 확인하며, 뷰와 서브쿼리를 준비합니다. 필요한 권한과 의미 조건도 이 범주에서 확인됩니다. Prepared Statement는 처음 준비할 때 이 작업을 하고, 참조 객체의 metadata가 바뀌면 재준비가 일어날 수 있습니다. ### 직접 확인해 볼 실습 ~~~sql -- 문법 오류 SELECT id, status FORM orders; -- 이름 해석 오류 SELECT unknown_col FROM orders; -- 모호한 열 이름 SELECT id FROM orders AS o JOIN customers AS c ON c.id = o.customer_id; ~~~ 세 번째 문장은 두 테이블에 모두 id가 있어 어느 열인지 결정할 수 없습니다. o.id처럼 소유 테이블 별칭을 붙이면 해결됩니다. ### 자주 받는 질문 - **Q. 문법이 맞는데 왜 실행 전 오류가 나나요?** A. Parser 통과는 SQL 구조가 유효하다는 뜻일 뿐 객체 존재, 권한, 데이터형과 의미 조건까지 통과했다는 뜻은 아닙니다. - **Q. Preprocessor라고 말하면 틀린가요?** A. 교육용 표현으로 사용할 수 있지만 발표에서는 MySQL 공식 구현의 단일 고정 모듈이 아니라 Resolver/Preparation 계열을 묶은 말이라고 밝혀야 합니다. ### 한 문장으로 설명하기 Parser는 SQL 문장의 모양을 확인하고 Resolver/Preparation은 그 문장이 실제 스키마에서 무엇을 뜻하는지 확정합니다. ## 9. Optimizer가 고르는 실행 계획 ![Slide 9 — Optimizer가 고르는 실행 계획](mysql-innodb-architecture-guide-assets/slide-09.png) ### 핵심 개념 Optimizer는 “인덱스를 쓸지 말지”보다 넓은 결정을 합니다. 어떤 테이블을 먼저 읽을지, 어떤 인덱스와 접근 방식을 쓸지, 서브쿼리를 변환하거나 materialize할지, 조인을 어떤 연산으로 수행할지를 비교합니다. 각 후보의 통계와 비용을 평가해 실행 계획을 선택합니다. ### 흐름과 의미 `EXPLAIN`은 Optimizer가 예상한 계획을 보여줍니다. 여기서 예상 행 수와 접근 방식, 조인 순서를 확인합니다. `EXPLAIN ANALYZE`는 MySQL 8.0.18부터 문장을 실제로 실행하고 iterator별 예상치와 실제 시간·행 수를 함께 보여줍니다. 데이터 변경 문장이나 비용이 큰 쿼리에 쓸 때는 실제 실행에 따른 부작용을 고려합니다. ### 슬라이드가 생략한 세부 사항 통계가 오래됐거나 데이터 분포가 치우치면 비용 추정이 실제와 달라집니다. 인덱스 존재 여부에서 확인을 끝내지 말고 선택된 계획과 실제 실행을 비교합니다. MySQL 8.0에는 hash join도 있습니다. 모든 조인이 늘 nested loop라고 설명하면 부정확합니다. `EXPLAIN ANALYZE`는 관찰만 하는 명령이 아니라 문장을 실제로 실행합니다. 비용이 큰 쿼리와 데이터 변경 문장에는 실행 시간, 잠금, 변경 부작용이 생길 수 있으므로 검증 환경과 트랜잭션 경계를 정합니다. ### 확인 포인트 - `EXPLAIN`의 예상 행 수와 실제 행 수를 비교한다. - `EXPLAIN ANALYZE`의 실제 실행 부작용을 확인한다. ### 왜 중요한가 Optimizer는 “인덱스가 있으면 사용한다”는 단순 규칙으로 움직이지 않습니다. 인덱스를 써도 많은 행을 읽어야 하거나 랜덤 I/O가 커지면 full scan이 더 저렴할 수 있습니다. 통계와 비용 추정이 계획 선택의 중심입니다. ### 어떤 후보를 비교하는가 단일 테이블에서는 full scan, range scan, ref, const 같은 접근 방법과 사용할 수 있는 인덱스를 비교합니다. 여러 테이블에서는 조인 순서와 조인 방식, 각 단계의 예상 행 수를 함께 봅니다. 서브쿼리·derived table을 merge할지 materialize할지, 조건을 어느 단계로 내릴지도 계획에 영향을 줍니다. MySQL 8.0에는 hash join이 있으므로 모든 조인을 nested loop라고 단정하지 않습니다. 비용은 실제 시간을 직접 예언하는 값이 아닙니다. 통계와 비용 모델로 후보를 상대 비교한 결과입니다. 데이터가 치우쳤거나 통계가 오래됐다면 예상 행 수가 실제와 크게 달라지고 뒤의 모든 비용 판단도 흔들릴 수 있습니다. ### EXPLAIN ANALYZE 읽기 ~~~sql EXPLAIN FORMAT=TREE SELECT * FROM orders WHERE status = 'PAID'; EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'PAID'; ~~~ EXPLAIN ANALYZE는 실제 문장을 실행하고 iterator별 예상 행 수, 실제 반환 행 수, 첫 행까지 걸린 시간, 전체 실행 시간, loops를 보여 줍니다. 예상 10행인데 실제 100,000행이라면 인덱스 유무보다 통계와 조건 선택도, 데이터 분포부터 의심합니다. 부모 iterator 시간에는 자식 실행 시간이 포함될 수 있으므로 각 숫자를 단순 합산하지 않습니다. ### 자주 받는 질문 - **Q. cost가 가장 낮으면 운영에서도 항상 가장 빠른가요?** A. 비용 모델의 추정치입니다. 실제 캐시 상태, 동시 부하, 디스크와 데이터 분포는 EXPLAIN ANALYZE와 운영 지표로 확인합니다. - **Q. 인덱스가 있는데 왜 full scan을 하나요?** A. 조건이 많은 행을 반환하거나 테이블이 작거나 인덱스 재탐색 비용이 크다고 판단하면 full scan이 더 저렴할 수 있습니다. ### 한 문장으로 설명하기 Optimizer는 인덱스 하나가 아니라 접근 방식·조인 순서·변환을 포함한 실행 계획 전체를 통계와 비용으로 비교합니다. ## 10. Executor와 Storage Engine의 핸드셰이크 ![Slide 10 — Executor와 Storage Engine의 핸드셰이크](mysql-innodb-architecture-guide-assets/slide-10.png) ### 핵심 개념 Executor는 실행 계획을 실제 연산 순서로 수행합니다. 스캔, 필터, 조인, 정렬, 집계 iterator가 다음 행을 요청하면서 결과를 만들어 냅니다. Executor가 `.ibd` 파일을 직접 열지는 않습니다. Handler API로 Storage Engine에 행 연산을 요청합니다. ### 흐름과 의미 InnoDB는 요청받은 인덱스 접근이나 레코드 읽기를 수행합니다. 필요한 페이지가 Buffer Pool에 있는지 확인하고, 없으면 Tablespace에서 읽습니다. 레코드를 찾은 뒤 필요한 열을 Executor로 돌려보내면 상위 연산이 이어집니다. 반복이 끝난 결과 집합은 Connection을 거쳐 Client로 돌아갑니다. ### 슬라이드가 생략한 세부 사항 이 접점이 플러그형 구조의 핵심입니다. Executor는 공통 인터페이스를 사용하되 페이지·잠금·트랜잭션의 내부 구현은 엔진마다 다릅니다. Handler API는 추상 경계입니다. 실제 호출은 index read, range scan, row fetch처럼 계획과 엔진 구현에 따라 달라집니다. Executor가 데이터 파일 형식을 직접 해석하지 않는다는 책임 분리가 핵심입니다. ### 확인 포인트 - Executor와 InnoDB가 각각 무엇을 모르는지 설명한다. - range scan과 row fetch가 계획에 따라 달라짐을 기억한다. ### 왜 중요한가 실행 계획은 읽기만 하는 문서가 아니라 실제로 움직이는 연산 트리입니다. Executor가 이 트리를 순회하면서 실제 행을 요구하고, Storage Engine은 요청받은 행 접근을 구현합니다. 이 경계를 알아야 EXPLAIN의 plan node와 InnoDB의 Page 읽기가 연결됩니다. ### Iterator 실행 모델 각 iterator는 “다음 행을 달라”는 요청에 응답하는 연산 단위입니다. 상위 iterator가 다음 결과를 요구하면 하위 iterator가 필요한 행을 생산합니다. 필터 iterator는 조건을 통과한 행만 위로 넘기고, 조인 iterator는 두 입력의 행을 결합합니다. 정렬이나 집계처럼 입력을 모아야 결과를 내는 연산도 있습니다. Executor가 .ibd 파일을 직접 열지는 않습니다. 인덱스 탐색, 다음 레코드, 조건에 맞는 범위 읽기 같은 작업을 Handler API로 요청합니다. InnoDB는 B-tree와 Buffer Pool을 이용해 레코드를 찾아 반환합니다. 반환된 행에 남은 필터를 적용하거나 다른 iterator가 조인하고 최종 결과를 클라이언트로 보냅니다. ### 실행 계획과 호출 횟수 연결하기 ~~~sql EXPLAIN ANALYZE SELECT o.id, c.name FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.status = 'PAID'; ~~~ 출력에서 각 iterator의 rows와 loops를 봅니다. 바깥 iterator가 10,000번 반복되면 안쪽 PK lookup도 10,000번 호출될 수 있습니다. 한 번의 lookup이 빨라도 반복 횟수가 크면 전체 비용이 커집니다. ### 자주 받는 질문 - **Q. 모든 WHERE 조건은 InnoDB가 처리하나요?** A. 조건과 접근 방법에 따라 엔진이 일부 조건을 활용할 수 있지만 Executor가 최종 필터를 적용하는 경우도 있습니다. 계획을 보고 판단합니다. - **Q. Handler API는 HTTP API인가요?** A. 아닙니다. MySQL 서버 내부에서 SQL 실행 계층과 Storage Engine 구현을 잇는 인터페이스입니다. ### 한 문장으로 설명하기 Executor는 iterator를 구동하고 Handler API로 행을 요청하며 InnoDB는 인덱스와 Page에서 해당 레코드를 찾아 돌려줍니다. ## 11. InnoDB Architecture ![Slide 11 — InnoDB Architecture](mysql-innodb-architecture-guide-assets/slide-11.png) ### 핵심 개념 InnoDB의 구조는 메모리 영역과 디스크 영역으로 나눠 보면 이해하기 쉽습니다. 메모리 쪽의 핵심은 Buffer Pool입니다. Change Buffer, Adaptive Hash Index, Log Buffer도 여기에 속합니다. 디스크에는 Tablespace, Redo Log, Doublewrite Buffer 같은 구조가 놓입니다. 각 요소는 읽기 성능, 쓰기 지연, 장애 복구라는 서로 다른 문제를 맡습니다. ### 흐름과 의미 다만 이 구분을 “메모리는 휘발성, 디스크는 영구 저장”이라는 한 문장으로 끝내면 부족합니다. Buffer Pool의 dirty page는 아직 디스크에 반영되지 않은 변경을 담고 있고, Redo Log는 그 간극에서 복구 가능성을 보장합니다. Change Buffer는 보조 인덱스 변경을 나중에 병합할 수 있도록 돕습니다. Adaptive Hash Index는 자주 접근하는 B-tree 경로를 해시 방식으로 빠르게 찾도록 돕지만 워크로드에 따라 효과가 달라집니다. ### 슬라이드가 생략한 세부 사항 발표에서는 SQL 한 문장의 읽기 경로와 직접 맞닿은 Buffer Pool, Tablespace, Page에 초점을 맞춥니다. 트랜잭션 격리, MVCC, Undo·Redo 복구도 같은 아키텍처에 속하지만 별도 발표가 필요할 만큼 범위가 큽니다. 전체 구성은 [InnoDB Architecture](https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html)에서 확인하십시오. Adaptive Hash Index는 전체 B-tree를 별도 해시 인덱스로 복제하지 않습니다. 반복되는 검색 패턴을 관찰해 hot page의 일부 경로를 보조합니다. 경합이 큰 워크로드에서는 이득이 줄거나 오히려 비용이 생길 수 있습니다. ### 확인 포인트 - 메모리 구조와 디스크 구조의 역할을 나눈다. - Adaptive Hash Index의 효과가 workload 의존적임을 확인한다. ### 왜 중요한가 InnoDB를 Buffer Pool 하나로만 이해하면 쓰기 안전성과 장애 복구를 설명할 수 없습니다. InnoDB는 메모리 구조, 디스크 구조, 백그라운드 작업이 함께 움직이는 Storage Engine입니다. ### 메모리 구조의 역할 Buffer Pool은 테이블과 인덱스 Page를 캐시합니다. Change Buffer는 Buffer Pool에 없는 secondary index page의 일부 변경을 나중에 merge하도록 보조합니다. Adaptive Hash Index는 반복되는 B-tree 접근 패턴을 관찰해 hash lookup 경로를 만들 수 있지만 효과와 경합은 워크로드에 따라 달라집니다. Log Buffer는 redo 기록을 디스크에 쓰기 전 메모리에 모읍니다. ### 디스크 구조의 역할 Tablespace는 데이터와 인덱스 Page를 담습니다. Undo Tablespace는 이전 버전과 rollback에 필요한 undo log를 저장합니다. Redo Log는 변경의 복구 가능성을 높이는 write-ahead log 역할을 합니다. Doublewrite 구조는 Page가 부분적으로 기록되는 torn page 위험을 완화합니다. 이 구성요소는 같은 데이터를 단순 복제한 것이 아니라 서로 다른 실패 조건을 다룹니다. ### 직접 확인해 볼 실습 ~~~sql SHOW VARIABLES WHERE Variable_name IN ( 'innodb_buffer_pool_size', 'innodb_adaptive_hash_index', 'innodb_log_buffer_size' ); SHOW ENGINE INNODB STATUS\G ~~~ SHOW ENGINE INNODB STATUS는 특정 시점의 폭넓은 상태를 한꺼번에 보여 줍니다. 한 번의 출력만으로 정상·비정상을 단정하지 말고 문제가 발생한 시간대의 반복 샘플과 다른 지표를 함께 봅니다. ### 자주 받는 질문 - **Q. Adaptive Hash Index는 사용자가 만드는 HASH 인덱스인가요?** A. 아닙니다. InnoDB가 Buffer Pool의 B-tree 접근 패턴을 보고 내부적으로 보조 경로를 관리합니다. - **Q. Redo와 Undo의 차이는 무엇인가요?** A. Redo는 장애 뒤 committed 변경을 복구하는 데, Undo는 rollback과 일관 읽기에 필요한 이전 변경 정보를 제공하는 데 중심 역할을 합니다. ### 한 문장으로 설명하기 InnoDB는 Page 캐시, 변경 기록, 복구 로그와 영속 저장 공간이 협력하는 트랜잭션 Storage Engine입니다. ## 12. Buffer Pool에서 읽고 쓰기 ![Slide 12 — Buffer Pool에서 읽고 쓰기](mysql-innodb-architecture-guide-assets/slide-12.png) ### 핵심 개념 Buffer Pool은 InnoDB가 디스크의 테이블·인덱스 페이지를 메모리에 캐시하는 공간입니다. 읽으려는 페이지가 Buffer Pool에 있으면 메모리에서 처리합니다. 없으면 해당 페이지를 Tablespace에서 읽어 빈 프레임에 올립니다. 변경된 페이지는 dirty page가 되며, 체크포인트와 백그라운드 플러시 과정에서 디스크에 기록됩니다. ### 흐름과 의미 페이지 교체에는 LRU에 가까운 목록을 쓰지만 단순한 한 줄짜리 LRU는 아닙니다. 목록을 young 영역과 old 영역으로 나누고 새 페이지를 midpoint 부근에 넣습니다. 한 번만 훑는 큰 스캔이 오래 쓰이던 hot page를 한꺼번에 밀어내는 현상을 줄이려는 설계입니다. `innodb_old_blocks_pct`와 `innodb_old_blocks_time`은 이 동작을 조절합니다. ### 슬라이드가 생략한 세부 사항 Buffer Pool은 SQL 결과 집합을 저장하는 result cache가 아닙니다. 행과 인덱스가 들어 있는 InnoDB 페이지를 캐시합니다. Buffer Pool hit가 높더라도 쿼리가 너무 많은 페이지를 훑으면 CPU와 메모리 대역폭을 많이 쓸 수 있습니다. 반대로 miss가 많다면 디스크 I/O가 병목이 될 가능성이 커집니다. [InnoDB Buffer Pool](https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html)을 참고하십시오. Buffer Pool은 쿼리 결과 캐시가 아닙니다. Page를 캐시하며 dirty page는 checkpoint와 background flush 흐름에서 디스크로 기록됩니다. hit rate가 높아도 너무 많은 Page를 훑으면 CPU와 메모리 대역폭이 병목이 됩니다. ### 확인 포인트 - cache hit과 cache miss의 I/O 경로를 그린다. - dirty page와 result cache를 혼동하지 않는다. ### 왜 중요한가 Buffer Pool은 InnoDB 읽기 성능의 중심이지만 “hit ratio가 높으면 성능이 좋다”는 한 문장으로 끝낼 수 없습니다. 어떤 Page가 들어 있고, workload가 얼마나 많은 Page를 반복 사용하며, dirty page가 어떻게 flush되는지 함께 봐야 합니다. ### Page 읽기와 쓰기 흐름 쿼리가 인덱스 Page를 요구하면 InnoDB는 Buffer Pool에서 해당 Page를 찾습니다. 있으면 logical read로 처리합니다. 없으면 Tablespace에서 읽어 free page에 올리거나 기존 Page를 교체합니다. Page를 변경하면 메모리의 Page가 dirty가 되고, background flush가 적절한 시점에 디스크로 기록합니다. commit 시 모든 dirty page를 즉시 데이터 파일에 쓰는 것은 아닙니다. redo durability와 data page flush는 목적과 시점이 다릅니다. LRU는 단순한 한 줄 목록이 아닙니다. New/Old sublist와 midpoint insertion을 사용해 큰 sequential scan이 자주 쓰는 Page를 한꺼번에 밀어내는 현상을 줄입니다. Read-ahead로 미리 읽은 Page가 실제 사용 전에 쫓겨난다면 workload와 read-ahead 효과를 다시 봅니다. ### 직접 확인해 볼 실습 ~~~sql SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_buffer_pool_read_requests', 'Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_data', 'Innodb_buffer_pool_pages_dirty', 'Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_read_ahead_evicted' ); ~~~ 논리 읽기 대비 물리 읽기 비율은 일정 시간 구간의 변화량으로 계산합니다. 서버 시작 뒤 누적값을 서로 다른 시점이나 workload와 무작정 비교하지 않습니다. hit ratio가 높아도 한 쿼리가 지나치게 많은 logical read를 만들 수 있습니다. ### 자주 받는 질문 - **Q. Buffer Pool은 SELECT 결과를 저장하나요?** A. 결과 집합이 아니라 테이블·인덱스 Page를 캐시합니다. - **Q. Buffer Pool을 크게 하면 무조건 좋은가요?** A. OS와 다른 프로세스가 쓸 메모리, NUMA와 운영 여유를 고려해야 합니다. 과도하게 잡아 swap이 생기면 오히려 나빠집니다. ### 한 문장으로 설명하기 Buffer Pool은 결과가 아니라 Page를 캐시합니다. logical read, physical read, dirty page와 eviction을 함께 관찰합니다. ## 13. Tablespace 지형도 ![Slide 13 — Tablespace 지형도](mysql-innodb-architecture-guide-assets/slide-13.png) ### 핵심 개념 Tablespace는 InnoDB의 페이지와 세그먼트가 놓이는 논리적 저장 공간입니다. 하나의 Tablespace가 반드시 하나의 파일과 일치하는 것은 아닙니다. System Tablespace처럼 여러 파일로 구성할 수 있는 공간도 있고, file-per-table Tablespace처럼 일반적으로 테이블 하나가 자체 `.ibd` 파일을 갖는 경우도 있습니다. ### 흐름과 의미 MySQL 8.0에서 자주 만나는 공간은 System Tablespace, File-per-table Tablespace, General Tablespace, Undo Tablespace, Temporary Tablespace입니다. System Tablespace에는 change buffer와 일부 시스템 데이터가 놓입니다. MySQL 8.0의 데이터 딕셔너리는 InnoDB에 통합됐지만 이를 단순히 “모든 메타데이터가 system tablespace 한 파일에 있다”고 설명하면 정확하지 않습니다. ### 슬라이드가 생략한 세부 사항 File-per-table은 테이블별 이동·회수·관리에 유리합니다. General Tablespace는 여러 테이블을 하나의 공유 공간에 둡니다. Undo Tablespace는 MVCC와 rollback에 필요한 undo record를 담고, Temporary Tablespace는 내부·사용자 임시 테이블을 저장합니다. 구조와 제약은 [InnoDB Tablespaces](https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace.html)와 [File Space Management](https://dev.mysql.com/doc/refman/8.0/en/innodb-file-space.html)에서 확인하십시오. Tablespace와 파일은 항상 일대일이 아닙니다. System Tablespace는 여러 data file로 구성할 수 있고 General Tablespace에는 여러 테이블이 들어갑니다. MySQL 8.0의 통합 Data Dictionary를 구버전 system tablespace 설명과 섞지 않습니다. ### 확인 포인트 - Tablespace와 data file의 대응 관계를 유형별로 비교한다. - Undo·Temporary Tablespace의 목적을 구분한다. ### 왜 중요한가 Tablespace와 파일을 같은 말로 외우면 공간 회수와 장애 범위를 잘못 판단합니다. Tablespace는 Page를 담는 논리 저장 공간이고 하나 이상의 파일과 연결될 수 있습니다. ### 주요 Tablespace 유형 System Tablespace는 InnoDB의 공통 내부 데이터와 구성에 따라 일부 사용자 테이블을 담을 수 있는 shared Tablespace입니다. File-per-table Tablespace는 한 InnoDB 테이블의 데이터와 인덱스를 보통 하나의 .ibd 파일에 저장하며 MySQL 8.0의 기본 테이블 배치입니다. General Tablespace는 여러 테이블을 함께 둘 수 있는 사용자 정의 shared Tablespace입니다. Undo Tablespace는 undo log, Temporary Tablespace는 내부·사용자 임시 작업을 지원합니다. File-per-table에서 테이블을 drop하거나 truncate하면 해당 .ibd 공간을 OS에 돌려주기 쉽습니다. shared Tablespace에서 확보된 공간은 Tablespace 내부 재사용 공간이 될 수 있으며 파일 크기가 바로 줄지 않을 수 있습니다. “DELETE하면 디스크 파일이 줄어든다”는 가정도 일반적으로 맞지 않습니다. ### 직접 확인해 볼 실습 ~~~sql SELECT SPACE, NAME, SPACE_TYPE, FILE_SIZE, ALLOCATED_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES ORDER BY SPACE_TYPE, NAME; SELECT SPACE, PATH FROM INFORMATION_SCHEMA.INNODB_DATAFILES ORDER BY SPACE; ~~~ 권한과 버전에 따라 보이는 열과 행이 다를 수 있습니다. Filesystem 크기와 FILE_SIZE, 실제 데이터 양을 같은 값으로 보지 않습니다. ### 자주 받는 질문 - **Q. File-per-table이면 데이터 파일과 인덱스 파일이 따로 생기나요?** A. 아닙니다. 해당 테이블의 데이터와 인덱스 Page가 같은 .ibd Tablespace 파일에 있습니다. - **Q. System Tablespace가 MySQL 8.0 Data Dictionary 전체를 저장하나요?** A. 구버전 설명을 그대로 적용하면 안 됩니다. MySQL 8.0은 통합 Data Dictionary를 사용합니다. ### 한 문장으로 설명하기 Tablespace는 Page의 논리 저장 공간이며 파일 배치와 공간 회수 방식은 Tablespace 유형에 따라 달라집니다. ## 14. Page → Extent → Segment ![Slide 14 — Page → Extent → Segment](mysql-innodb-architecture-guide-assets/slide-14.png) ### 핵심 개념 Page는 InnoDB가 디스크와 Buffer Pool 사이에서 읽고 쓰는 기본 단위입니다. 기본값은 16KB입니다. 인스턴스를 초기화할 때 `innodb_page_size`로 4KB, 8KB, 16KB, 32KB, 64KB 가운데 하나를 고릅니다. 운영 중인 인스턴스에서 가볍게 바꾸는 설정은 아닙니다. ### 흐름과 의미 기본 16KB 페이지 기준으로 연속된 64개 페이지가 1MB Extent를 이룹니다. 4KB·8KB·16KB 페이지 크기에서도 Extent는 1MB이고, 32KB 페이지에서는 2MB, 64KB 페이지에서는 4MB입니다. Segment는 한 인덱스의 leaf page나 non-leaf page처럼 같은 목적의 페이지·Extent를 묶어 관리하는 논리 단위입니다. ### 슬라이드가 생략한 세부 사항 한 Page가 곧 한 Row는 아닙니다. 일반적으로 하나의 index page 안에 여러 record가 들어갑니다. 큰 가변 길이 열은 일부 데이터를 overflow page에 둘 수 있습니다. Page Type도 index, undo log, inode, system 등 여러 종류가 있으므로 “페이지는 테이블 행을 담는 상자” 정도로만 기억하면 실제 구조를 놓치게 됩니다. 16KB는 기본 Page 크기이지 고정값이 아닙니다. 4KB·8KB·16KB Page의 Extent는 1MB지만 32KB에서는 2MB, 64KB에서는 4MB입니다. Page 크기는 인스턴스 초기화 뒤 바꿀 수 없습니다. ### 확인 포인트 - Page 크기별 Extent 크기를 다시 계산한다. - Segment가 leaf와 non-leaf 공간을 논리적으로 묶는 이유를 설명한다. ### 왜 중요한가 InnoDB는 행 하나가 아니라 Page 단위로 캐시하고 I/O합니다. 쿼리가 1행을 반환해도 해당 행을 찾기 위해 여러 인덱스 Page를 읽을 수 있습니다. Page → Extent → Segment 관계는 읽기 비용과 공간 할당을 연결하는 기본 단위입니다. ### Page, Extent, Segment Page는 InnoDB Tablespace의 기본 블록입니다. 기본값은 16KB지만 인스턴스 초기화 시 4KB, 8KB, 16KB, 32KB, 64KB 가운데 하나를 선택합니다. 한 인스턴스의 모든 InnoDB Tablespace는 같은 innodb_page_size를 사용합니다. 16KB 이하 Page에서는 64개 Page가 1MB Extent를 이룹니다. 32KB Page의 Extent는 2MB, 64KB Page의 Extent는 4MB입니다. Segment는 같은 목적의 Page와 Extent를 묶는 논리 단위입니다. B-tree index는 leaf page용 segment와 non-leaf page용 segment를 가질 수 있습니다. Segment를 단순히 OS 파일 안의 연속 구간 하나로 이해하면 안 됩니다. ### 직접 확인해 볼 실습 ~~~sql SHOW VARIABLES LIKE 'innodb_page_size'; SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; SELECT NAME, PAGE_SIZE, FILE_SIZE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES WHERE NAME LIKE '%orders%'; ~~~ innodb_page_size는 data directory를 초기화할 때 정하며 운영 중 동적으로 바꾸는 옵션이 아닙니다. 큰 VARCHAR·BLOB·TEXT 값은 row format에 따라 일부가 off-page에 저장될 수 있으므로 “한 행은 한 Page 안에 모두 있다”라고 설명하지 않습니다. ### 자주 받는 질문 - **Q. 16KB Page면 SSD도 항상 16KB씩 물리 기록하나요?** A. InnoDB의 논리 Page 단위와 저장장치 내부 block·filesystem I/O는 같은 개념이 아닙니다. - **Q. Extent는 항상 1MB인가요?** A. 4KB·8KB·16KB Page에서는 1MB지만 32KB와 64KB Page 설정에서는 더 큽니다. ### 한 문장으로 설명하기 Page가 I/O와 캐시의 기본 단위이고 Extent와 Segment가 같은 목적의 Page를 더 큰 공간 관리 단위로 묶습니다. ## 15. Primary Key Clustering · Foreign Key ![Slide 15 — Primary Key Clustering · Foreign Key](mysql-innodb-architecture-guide-assets/slide-15.png) ### 핵심 개념 InnoDB의 clustered index leaf page에는 행 데이터가 함께 저장됩니다. 테이블에 명시적인 Primary Key가 있으면 그 키를 clustered index로 씁니다. Primary Key가 없으면 모든 열이 `NOT NULL`인 첫 번째 `UNIQUE` 인덱스를 선택합니다. 그런 인덱스도 없으면 InnoDB가 6바이트 row ID를 만들고 `GEN_CLUST_INDEX`라는 숨은 clustered index를 구성합니다. ### 흐름과 의미 Primary Key로 찾은 leaf record에서 행의 나머지 열을 바로 읽는 구조입니다. 반면 Primary Key가 길면 모든 secondary index leaf record에 복사되는 값도 길어집니다. 짧고 안정적이며 증가 방향이 비교적 일정한 키가 공간과 쓰기 패턴에 유리한 경우가 많은 이유입니다. 그렇다고 모든 서비스가 단조 증가 정수만 써야 하는 것은 아닙니다. 분산 ID, 보안 요구, 업무 키의 성격을 함께 봅니다. ### 슬라이드가 생략한 세부 사항 InnoDB의 외래 키는 부모·자식 테이블의 참조 무결성을 검사하고 `CASCADE`, `SET NULL`, `RESTRICT` 같은 참조 동작을 지원합니다. 관련 열에는 인덱스가 필요합니다. `foreign_key_checks=0`은 적재 순서를 유연하게 만들 때 쓰기도 하지만 다시 `1`로 바꿔도 이미 들어간 행을 소급 검사하지 않습니다. 비활성화 구간의 데이터 품질을 별도로 검증해야 합니다. Clustered index의 물리적 배치가 조회 결과의 정렬 순서를 보장하지는 않습니다. SQL 결과 순서가 필요하면 반드시 `ORDER BY`를 사용합니다. 자세한 선택 규칙은 [Clustered and Secondary Indexes](https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html)에 정리돼 있습니다. Clustered layout은 물리 배치 특성일 뿐 SQL 결과 순서를 보장하지 않습니다. 외래 키 검사를 잠시 껐다가 다시 켜도 비활성화 구간에 들어온 기존 행은 소급 검증되지 않습니다. ### 확인 포인트 - PK 선택 규칙 세 단계를 순서대로 말한다. - 외래 키 비활성화 뒤 기존 행이 소급 검증되지 않음을 기억한다. ### 왜 중요한가 InnoDB의 Primary Key는 중복을 막는 논리 제약이면서 행이 저장되는 clustered index의 키입니다. PK의 폭과 삽입 패턴은 데이터 Page뿐 아니라 모든 secondary index 크기와 쓰기 비용에도 영향을 줍니다. ### Clustered Index 선택 규칙 명시적인 PRIMARY KEY가 있으면 InnoDB는 이를 clustered index로 사용합니다. PRIMARY KEY가 없으면 모든 열이 NOT NULL인 첫 UNIQUE index를 선택합니다. 그것도 없으면 6-byte hidden row ID를 만들고 GEN_CLUST_INDEX를 구성합니다. 숨은 키 덕분에 테이블은 동작하지만 사용자가 그 값을 조회·참조할 수 없고 secondary index도 그 내부 키를 사용하므로 명시적 PK가 권장됩니다. clustered index leaf record에는 행 데이터가 함께 있습니다. “PK 순서대로 파일에 완전히 연속 저장된다”는 뜻은 아닙니다. Page split과 재구성, 삭제와 삽입으로 물리 Page 배치는 바뀔 수 있고 SELECT 결과 순서도 보장하지 않습니다. 순서가 필요하면 ORDER BY를 사용합니다. ### Foreign Key와 함께 보기 ~~~sql SHOW CREATE TABLE orders\G SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_SCHEMA = DATABASE(); ~~~ Foreign Key는 child 값이 parent key를 참조하도록 무결성을 검사합니다. 관련 열의 데이터형과 인덱스 조건도 확인합니다. foreign_key_checks=0은 bulk load에 쓰일 수 있지만 다시 1로 설정한다고 비활성화 중 들어온 기존 행을 자동 소급 검증하지 않습니다. 운영 절차에는 별도 검증을 넣습니다. ### PK 설계 체크리스트 - 짧고 안정적이며 변경되지 않는 값인가? - secondary index가 많을 때 PK 폭이 전체 인덱스에 미치는 영향을 계산했는가? - 삽입 패턴이 한쪽 Page에 과도하게 몰리거나 무작위 Page split을 만드는가? - 업무 식별자와 물리 PK를 같은 값으로 써야 하는 이유가 분명한가? ### 자주 받는 질문 - **Q. PK가 없으면 InnoDB 테이블을 만들 수 없나요?** A. 만들 수 있지만 hidden clustered index가 생깁니다. 명시적 PK가 관찰성과 secondary index 비용 면에서 유리합니다. - **Q. clustered index면 SELECT가 PK 순서로 나오나요?** A. SQL 결과 순서는 보장되지 않습니다. 반드시 ORDER BY를 사용합니다. ### 한 문장으로 설명하기 InnoDB의 PK는 행 위치를 찾는 clustered index 키이며 그 폭과 안정성이 모든 secondary index 비용에 퍼집니다. ## 16. Secondary Index의 두 번째 탐색 ![Slide 16 — Secondary Index의 두 번째 탐색](mysql-innodb-architecture-guide-assets/slide-16.png) ### 핵심 개념 InnoDB의 secondary index leaf record에는 secondary key와 해당 행의 Primary Key가 저장됩니다. secondary index로 조건에 맞는 항목을 찾은 뒤 행의 다른 열이 필요하면 그 Primary Key로 clustered index를 다시 탐색합니다. 흔히 이를 두 번째 탐색 또는 back-to-table lookup이라고 설명합니다. ### 흐름과 의미 예를 들어 `orders(status, created_at)` 인덱스로 주문 후보를 찾았는데 결과에 `total_amount`가 필요하고 그 열이 인덱스에 없다면 clustered index에서 행을 다시 읽습니다. 후보가 많을수록 두 번째 탐색 비용도 커집니다. 쿼리에 필요한 열이 모두 secondary index에 있으면 covering index가 되어 clustered index 접근을 피합니다. ### 슬라이드가 생략한 세부 사항 Primary Key가 모든 secondary index에 포함된다는 점은 설계 비용으로 돌아옵니다. Primary Key가 길수록 secondary index가 커지고, 같은 Buffer Pool 공간에 담기는 entry 수가 줄어듭니다. 인덱스 수가 많을수록 쓰기 비용도 늘어납니다. “인덱스가 있으면 빠르다”보다 접근 경로와 저장 비용을 함께 봐야 합니다. Secondary index가 필요한 열을 모두 담는 covering index라면 clustered index 재탐색을 피합니다. 다만 열을 무작정 더 넣으면 인덱스 크기와 쓰기 비용이 늘어납니다. ### 확인 포인트 - secondary leaf의 PK가 clustered lookup에 쓰임을 설명한다. - covering index의 이득과 인덱스 비대화 비용을 함께 본다. ### 왜 중요한가 secondary index가 “행의 물리 주소”를 저장한다고 설명하면 InnoDB의 두 번째 탐색 비용을 놓칩니다. InnoDB secondary index leaf record에는 secondary key와 Primary Key 열이 들어갑니다. ### 두 B-tree 탐색 예제의 ix_orders_status_created(status, created_at)에서 status='PAID' 범위를 찾으면 leaf record에서 status, created_at과 해당 row의 PK를 얻습니다. 쿼리가 orders.id만 요구한다면 이미 secondary index에 PK가 있어 추가 탐색이 필요 없을 수 있습니다. 반면 secondary index에 없는 customer_id나 다른 열이 필요하면 PK로 clustered index를 다시 탐색해 full row를 가져옵니다. 이를 흔히 bookmark lookup 또는 table lookup이라고 설명합니다. 첫 번째 탐색의 반환 행 수가 많으면 두 번째 탐색도 많이 반복됩니다. PK가 긴 문자열이면 각 secondary index leaf record도 커지고 한 Page에 들어가는 레코드 수가 줄어 B-tree 높이와 캐시 효율에 영향을 줄 수 있습니다. ### 커버링 인덱스 실습 ~~~sql EXPLAIN SELECT id, created_at FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 20; EXPLAIN SELECT id, customer_id, created_at FROM orders WHERE status = 'PAID' ORDER BY created_at DESC LIMIT 20; ~~~ 첫 쿼리는 현재 secondary index에 있는 열과 PK만으로 해결될 가능성이 있습니다. 두 번째 쿼리의 customer_id는 인덱스 정의에 없으므로 clustered index 재조회가 필요할 수 있습니다. 실제 Extra와 TREE 계획을 보고 확인합니다. ### 자주 받는 질문 - **Q. 모든 secondary index 조회는 정확히 두 번 탐색하나요?** A. 전체 행이 필요할 때의 대표 흐름입니다. 커버링 인덱스, 범위 조건, optimizer 선택에 따라 실제 접근은 달라집니다. - **Q. 필요한 모든 열을 인덱스에 넣으면 되나요?** A. 읽기에는 유리할 수 있지만 인덱스 크기와 쓰기·유지 비용이 늘어납니다. 자주 쓰는 쿼리와 선택도를 기준으로 결정합니다. ### 한 문장으로 설명하기 InnoDB secondary index는 PK를 포인터처럼 사용하며 필요한 열이 없으면 clustered index를 다시 탐색합니다. ## 17. Stored Object 용어 지도 ![Slide 17 — Stored Object 용어 지도](mysql-innodb-architecture-guide-assets/slide-17.png) ### 핵심 개념 MySQL의 stored object는 서버에 정의를 저장하고 서버 안에서 실행하는 객체를 가리킵니다. Stored Procedure와 Stored Function을 합쳐 Stored Routine이라고 부릅니다. Trigger와 Event는 실행 계기가 다르지만 함께 Stored Object 범주에서 설명합니다. ### 흐름과 의미 Procedure는 애플리케이션이 `CALL`로 명시적으로 호출하며 `IN`, `OUT`, `INOUT` 매개변수를 씁니다. Function은 값을 반환하고 SQL 식 안에서 호출합니다. Trigger는 특정 테이블의 `INSERT`, `UPDATE`, `DELETE` 전후에 행마다 실행됩니다. Event는 Event Scheduler가 지정된 시각이나 반복 주기에 맞춰 실행합니다. ### 슬라이드가 생략한 세부 사항 이 객체들을 쓰면 비즈니스 규칙을 데이터 가까이에 둘 수 있습니다. 대신 배포·버전 관리·테스트·관찰은 애플리케이션 코드보다 까다롭습니다. 팀의 운영 방식과 장애 대응 절차까지 보고 선택합니다. 공식 개요는 [Stored Objects](https://dev.mysql.com/doc/refman/8.0/en/stored-objects.html)에 있습니다. Stored Object에는 Stored Program뿐 아니라 View도 포함됩니다. 이번 발표는 실행 주체와 시점을 비교하려고 Procedure, Function, Trigger, Event에 범위를 맞췄습니다. ### 확인 포인트 - Routine·Program·Object의 포함 관계를 그린다. - 네 객체를 호출 주체와 실행 시점으로 분류한다. ### 왜 중요한가 Stored Procedure, Function, Trigger, Event를 모두 “프로시저”라고 부르면 권한과 실행 조건, 제약을 잘못 적용합니다. 공식 용어 계층을 알면 문서와 metadata 테이블을 정확히 찾을 수 있습니다. ### 용어 계층 Stored Routine은 Procedure와 Function을 묶습니다. Stored Program은 Routine에 Trigger와 Event를 더한 범주입니다. Stored Object는 Stored Program과 View를 포함합니다. 이 계층은 단순 분류가 아니라 공통 제약이 어디까지 적용되는지 읽는 기준입니다. 예를 들어 stored function 제한 중 일부는 trigger에도 적용되고, procedure 제한은 Event의 DO 본문에 적용될 수 있습니다. ### Metadata 확인 ~~~sql SELECT ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, SECURITY_TYPE FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = DATABASE(); SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, ACTION_TIMING FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_SCHEMA = DATABASE(); SELECT EVENT_SCHEMA, EVENT_NAME, STATUS, EVENT_TYPE FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_SCHEMA = DATABASE(); ~~~ 객체 정의에는 DEFINER가 들어갈 수 있습니다. Procedure와 Function은 SQL SECURITY DEFINER 또는 INVOKER 문맥을 가질 수 있지만 Trigger와 Event는 서버가 자동 실행하므로 definer 권한을 특히 주의합니다. Definer 계정이 삭제되거나 과도한 권한을 가지면 운영 위험이 생깁니다. ### 자주 받는 질문 - **Q. View도 Stored Program인가요?** A. Stored Object에는 포함되지만 Stored Program에는 포함되지 않습니다. - **Q. Trigger를 사용자가 직접 호출할 수 있나요?** A. 직접 CALL하지 않습니다. 연결된 테이블의 지정 DML event가 발생하면 서버가 자동 실행합니다. ### 한 문장으로 설명하기 Routine, Program, Object의 포함 관계를 알면 네 객체의 공통 제약과 개별 실행 조건을 정확히 읽을 수 있습니다. ## 18. Procedure · Function · Trigger · Event ![Slide 18 — Procedure · Function · Trigger · Event](mysql-innodb-architecture-guide-assets/slide-18.png) ### 핵심 개념 Procedure는 여러 SQL 문장을 하나의 호출 단위로 묶거나 결과 집합과 출력 매개변수를 돌려줄 때 적합합니다. Function은 반드시 값을 반환하며 SQL 식 안에서 사용합니다. 데이터 변경과 외부 상태 의존이 큰 로직을 Function에 넣으면 평가 횟수와 부작용을 추적하기 어려워집니다. ### 흐름과 의미 Trigger는 테이블 이벤트에 묶여 자동 실행됩니다. MySQL Trigger는 `FOR EACH ROW` 방식이므로 한 문장이 10만 행을 바꾸면 트리거 본문도 행마다 실행됩니다. 호출자가 Trigger의 존재를 몰라도 동작합니다. 감사 열 기록이나 단순한 불변식 보강에는 유용하지만 숨은 비용과 재귀적 영향 범위를 주의합니다. 문법과 실행 조건은 [Trigger Syntax and Examples](https://dev.mysql.com/doc/refman/8.0/en/trigger-syntax.html)에서 확인하십시오. ### 슬라이드가 생략한 세부 사항 Event는 한 번 또는 반복 일정으로 실행하는 서버 내부 작업입니다. Event를 정의해도 `event_scheduler`가 `ON`이 아니면 실행되지 않습니다. 실행 주체, 실패 기록, 시간대, 중복 실행 가능성까지 운영 절차에 넣습니다. [Event Scheduler Configuration](https://dev.mysql.com/doc/refman/8.0/en/events-configuration.html)과 [Event Scheduler Overview](https://dev.mysql.com/doc/refman/8.0/en/events-overview.html)을 함께 보십시오. Trigger는 statement 단위가 아니라 `FOR EACH ROW`로 실행됩니다. Event는 정의만으로 동작하지 않고 `event_scheduler=ON`이 필요합니다. 두 객체 모두 호출 표면에서 보이지 않는 비용을 만들 수 있습니다. ### 확인 포인트 - Trigger의 `FOR EACH ROW` 비용을 변경 행 수와 연결한다. - Event 실행 전에 `event_scheduler` 상태를 확인한다. ### 왜 중요한가 네 객체의 가장 큰 차이는 문법보다 호출 주체와 실행 시점입니다. 같은 SQL 본문을 넣을 수 있어 보여도 누가 언제 실행하고 무엇을 반환하는지에 따라 운영 특성이 달라집니다. ### Procedure와 Function ~~~sql DELIMITER // CREATE PROCEDURE count_paid_orders(OUT paid_count BIGINT) BEGIN SELECT COUNT(*) INTO paid_count FROM orders WHERE status = 'PAID'; END// CREATE FUNCTION order_label(p_id BIGINT) RETURNS VARCHAR(32) DETERMINISTIC RETURN CONCAT('ORDER-', p_id)// DELIMITER ; CALL count_paid_orders(@cnt); SELECT @cnt, order_label(42); ~~~ Procedure는 CALL로 호출하고 OUT·INOUT parameter와 result set을 돌려줄 수 있습니다. Function은 표현식 안에서 scalar value를 반환합니다. Function과 Trigger는 dynamic SQL과 transaction 제어 등 Procedure보다 제약이 많습니다. ### Trigger와 Event ~~~sql CREATE TRIGGER orders_bu BEFORE UPDATE ON orders FOR EACH ROW SET NEW.status = UPPER(NEW.status); CREATE EVENT purge_old_orders ON SCHEDULE EVERY 1 DAY DO DELETE FROM orders WHERE created_at < CURRENT_DATE - INTERVAL 3 YEAR; ~~~ MySQL Trigger는 row-level입니다. 한 UPDATE가 100,000행에 영향을 주면 trigger body도 각 행에 대해 실행됩니다. Event는 event_scheduler=ON일 때 시간 일정에 따라 실행됩니다. 반복 event 실행 시간이 interval보다 길면 실행이 겹칠 수 있으므로 자동 직렬화를 가정하지 않습니다. ### 자주 틀리는 설명 - Procedure는 “아무것도 반환하지 않는다”가 아니라 함수식 return value가 없을 뿐 OUT·INOUT과 result set을 보낼 수 있습니다. - Foreign Key cascade는 Trigger를 활성화하지 않습니다. - Event object가 ENABLE 상태여도 서버의 Event Scheduler가 꺼져 있으면 실행되지 않습니다. ### 한 문장으로 설명하기 Procedure는 CALL, Function은 표현식, Trigger는 각 행의 DML, Event는 시간 일정이 실행 조건입니다. ## 19. Stored Program 선택 체크리스트 ![Slide 19 — Stored Program 선택 체크리스트](mysql-innodb-architecture-guide-assets/slide-19.png) ### 핵심 개념 선택 기준은 실행 계기부터 잡으면 됩니다. 애플리케이션이 명시적으로 시작하는 다단계 작업은 Procedure가 자연스럽습니다. SQL 식에서 재사용할 계산이 필요하고 부작용을 통제할 수 있다면 Function을 검토합니다. 특정 테이블의 행 변경에 반드시 붙어야 하는 규칙은 Trigger 후보입니다. 정해진 시각이나 주기에 실행하는 내부 작업은 Event 후보입니다. ### 흐름과 의미 그다음에는 관찰 가능성과 실패 경계를 점검합니다. 누가 언제 실행했는지, 실행 시간과 변경 행 수를 어떻게 기록할지, 오류가 났을 때 transaction이 어디까지 rollback되는지 확인합니다. 권한과 `DEFINER` 계정의 수명, 백업·복구 시 객체 포함 여부, 배포 순서도 빠뜨리기 쉽습니다. ### 슬라이드가 생략한 세부 사항 애플리케이션 job scheduler가 이미 표준이라면 Event를 더하기보다 기존 체계를 따르는 편이 운영에 유리합니다. 데이터베이스 안에서 실행해야 원자성과 데이터 근접성이 보장되는 작업도 있습니다. 어느 쪽을 고르든 로직의 존재가 숨지 않도록 소스 관리, 모니터링, 테스트 경로를 마련합니다. DB 내부 로직은 스키마 백업, 권한, `DEFINER`, 복제, 장애 복구, 배포 순서에 함께 묶입니다. 기능 적합성만 보고 선택하면 운영 중 변경과 관찰이 어려워집니다. ### 확인 포인트 - 호출 가시성·실패 경계·권한을 선택 기준에 넣는다. - 소스 관리와 모니터링 경로가 있는지 확인한다. ### 왜 중요한가 Stored Program은 로직을 데이터 가까이에 둘 수 있지만 호출이 숨겨지고 배포·권한·복제·관찰이 어려워질 수 있습니다. “DB에서 가능하다”와 “DB에 두는 것이 운영하기 좋다”를 분리해서 판단합니다. ### 선택 질문 여러 문장을 하나의 명시적 API처럼 호출하고 결과 집합이나 OUT 값을 돌려줘야 한다면 Procedure를 검토합니다. 식 안의 순수한 값 계산에는 Function이 맞습니다. 데이터 변경과 함께 지켜야 하는 짧은 불변식은 Trigger 후보입니다. DB 서버가 정해진 시간에 처리해야 하고 내부 실행이 더 명확한 작업에는 Event를 검토합니다. 다음 조건이면 애플리케이션 코드나 외부 job scheduler가 더 나을 수 있습니다. 로직 변경이 잦고 코드 리뷰·테스트·배포 추적이 중요하거나, 여러 서비스와 외부 API를 호출하거나, 재시도·알림·분산 잠금이 필요하거나, 실행량이 커서 별도 worker 확장이 필요할 때입니다. ### 운영 점검 SQL ~~~sql SHOW VARIABLES LIKE 'event_scheduler'; SHOW EVENTS FROM your_database; SHOW TRIGGERS FROM your_database; SHOW PROCEDURE STATUS WHERE Db = 'your_database'; SHOW FUNCTION STATUS WHERE Db = 'your_database'; ~~~ 정의와 함께 DEFINER 계정, privilege, binary logging·replication 형식, 실행 시간, 실패 기록, 중복 실행 방지 방법을 문서화합니다. Trigger와 Event는 사용자가 직접 호출하지 않아 application trace에서 빠지기 쉬우므로 별도 관찰 경로가 필요합니다. ### 자주 받는 질문 - **Q. Trigger로 모든 데이터 검증을 처리하면 안전한가요?** A. 중앙 강제에는 도움이 되지만 row별 비용, 숨은 부작용, 테스트와 변경 관리가 어려워집니다. CHECK·FK·애플리케이션 검증과 역할을 나눕니다. - **Q. Event Scheduler는 cron을 완전히 대체하나요?** A. 단순 DB 내부 일정에는 적합하지만 복잡한 workflow, 외부 연동, 재시도와 관찰은 외부 scheduler가 더 적합할 수 있습니다. ### 한 문장으로 설명하기 Stored Program은 실행 위치보다 호출 가시성, 권한, 실패 관찰과 변경 관리 비용을 기준으로 선택합니다. ## 20. SELECT 한 문장의 End-to-End 경로 ![Slide 20 — SELECT 한 문장의 End-to-End 경로](mysql-innodb-architecture-guide-assets/slide-20.png) ### 핵심 개념 마지막으로 `SELECT` 한 문장을 처음부터 다시 따라가 봅니다. Client가 Connection을 열고 인증을 마치면 Session 상태가 준비됩니다. 요청을 처리하는 Thread가 SQL 문자열을 받아 Parser와 Resolver/Preparation으로 넘깁니다. 문법과 객체·권한 확인이 끝나면 Optimizer가 통계와 비용을 바탕으로 실행 계획을 고릅니다. ### 흐름과 의미 Executor는 계획의 iterator를 실행하며 Handler API로 필요한 행을 요청합니다. InnoDB는 선택된 인덱스의 페이지를 Buffer Pool에서 찾습니다. cache miss가 나면 Tablespace에서 Page를 읽어 메모리에 올립니다. secondary index를 사용했고 필요한 열이 leaf record에 없다면 Primary Key로 clustered index를 다시 찾습니다. ### 슬라이드가 생략한 세부 사항 조건을 통과한 행은 Executor의 조인·필터·정렬·집계 단계를 거쳐 결과가 됩니다. 서버는 결과를 Connection으로 보내고 Client가 이를 받습니다. 느린 쿼리를 볼 때도 같은 길을 거꾸로 확인하면 됩니다. 연결 대기, 세션 메모리, 계획 오류, 많은 row 접근, Buffer Pool miss 가운데 어느 경계에서 시간이 늘었는지 찾습니다. 실제 SELECT에는 트랜잭션 가시성 확인, 잠금, 네트워크 패킷 전송이 더해집니다. 도식은 Parser부터 Page까지의 책임 경계를 선명하게 보이려고 이 세부 단계를 접었습니다. ### 확인 포인트 - Client에서 Page까지 정방향으로 한 번 설명한다. - 병목 진단 때 같은 경로를 역방향으로 추적한다. ### 왜 중요한가 지금까지의 개념을 하나의 SELECT에 다시 연결해야 지식이 조각나지 않습니다. 이 장에서는 Buffer Pool hit와 miss 두 경우를 나눠 같은 요청이 어디까지 내려가는지 추적합니다. ### End-to-End 흐름 1. Client가 인증된 Connection으로 SQL을 전송합니다. 2. Session 문맥의 현재 database, SQL mode, transaction 상태가 적용됩니다. 3. Parser가 구조를 만들고 Resolver/Preparation이 orders, customers와 열 참조를 확정합니다. 4. Optimizer가 ix_orders_status_created와 customers PK lookup을 포함한 후보를 비교합니다. 5. Executor가 선택한 iterator를 실행하며 Handler API로 다음 레코드를 요청합니다. 6. InnoDB가 secondary index Page에서 PAID 범위를 찾고 필요한 PK로 clustered index를 탐색합니다. 7. Page가 Buffer Pool에 있으면 logical read로 진행합니다. 없으면 Tablespace에서 Page를 읽어 Buffer Pool에 올립니다. 8. 행이 Executor로 돌아오고 조인·필터·LIMIT가 적용된 결과가 Client에 전송됩니다. ### Hit와 Miss 비교 Buffer Pool hit에서는 저장장치 읽기 없이도 여러 B-tree Page와 row를 논리적으로 탐색합니다. hit라고 비용이 0인 것은 아닙니다. latch, CPU, 많은 logical read가 남습니다. miss에서는 Page read 대기와 Page 교체가 더해집니다. 처음 실행은 느리고 두 번째 실행은 빨라지는 현상이 있다면 plan 변화뿐 아니라 cache warm-up도 함께 의심합니다. ### 직접 확인해 볼 실습 ~~~sql EXPLAIN ANALYZE SELECT o.id, c.name FROM orders AS o JOIN customers AS c ON c.id = o.customer_id WHERE o.status = 'PAID' ORDER BY o.created_at DESC LIMIT 20; SHOW SESSION STATUS LIKE 'Handler_read%'; SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; ~~~ Session Handler 값과 Global Buffer Pool 값은 범위가 다릅니다. 같은 분모처럼 직접 비교하지 말고 각각 어떤 계층의 누적 활동을 나타내는지 구분합니다. ### 한 문장으로 설명하기 SELECT는 Connection과 SQL Layer를 지나 Handler 호출이 되고 InnoDB의 인덱스·Buffer Pool·Tablespace Page 접근으로 완성됩니다. ## 21. 세 가지 렌즈로 MySQL을 읽는다 ![Slide 21 — 세 가지 렌즈로 MySQL을 읽는다](mysql-innodb-architecture-guide-assets/slide-21.png) ### 핵심 개념 첫 번째 렌즈는 **책임 경계**입니다. MySQL 서버는 SQL과 실행 흐름을 맡고, InnoDB는 트랜잭션을 지원하는 레코드·인덱스·페이지 저장을 맡습니다. Handler API가 두 영역을 연결합니다. ### 흐름과 의미 두 번째 렌즈는 **수명과 범위**입니다. Connection과 Session, Thread는 비슷해 보여도 수명과 소유 상태가 다릅니다. Global Memory와 Session Memory도 공유 범위와 할당 시점이 다릅니다. 설정값의 최댓값만 곱하지 말고 실제 할당 조건을 확인합니다. ### 슬라이드가 생략한 세부 사항 세 번째 렌즈는 **접근 단위**입니다. SQL은 행을 요구하지만 InnoDB는 Page를 읽고 Buffer Pool에 캐시합니다. Clustered index와 secondary index는 그 페이지를 어떤 경로로 찾을지 결정합니다. 세 렌즈를 겹치면 연결, 실행 계획, I/O 문제를 같은 그림 안에서 구분합니다. 발표 뒤에는 실제 쿼리 하나를 골라 `EXPLAIN ANALYZE`, Performance Schema, InnoDB Buffer Pool 지표를 함께 확인해 보십시오. 아키텍처 용어가 운영 지표와 연결될 때 비로소 문제 해결 도구가 됩니다. 세 렌즈는 정답을 자동으로 주는 체크리스트가 아닙니다. 같은 증상도 버전, 에디션, 설정, 데이터 분포에 따라 원인이 달라집니다. 측정값으로 가설을 확인하는 순서를 유지합니다. ### 확인 포인트 - Boundary·Plan·Page 세 질문으로 실습 쿼리를 점검한다. - 실제 버전과 workload 측정값으로 가설을 검증한다. ### 왜 중요한가 아키텍처는 구성요소 이름을 재현하려고 배우는 것이 아닙니다. 장애와 성능 문제를 조사할 질문을 얻는 공부입니다. Boundary, Plan, Page 세 렌즈는 조사 순서를 단순하게 유지합니다. ### 세 렌즈 진단 순서 **Boundary**에서는 연결 폭증, Session 상태, 서버와 InnoDB 중 어느 책임에서 대기하는지 나눕니다. **Plan**에서는 EXPLAIN과 EXPLAIN ANALYZE로 접근 방식, 예상·실제 행 수, loops를 비교합니다. **Page**에서는 선택된 인덱스가 얼마나 많은 Page를 읽는지, Buffer Pool에서 해결되는지, Tablespace 물리 읽기가 늘어나는지 봅니다. 예를 들어 “PAID 주문 20건 조회가 느리다”면 먼저 Connection 대기와 서버 전체 포화를 확인합니다. 다음으로 status와 created_at 인덱스가 선택됐는지, 예상 행 수가 실제와 맞는지 봅니다. 마지막으로 반환 20건을 위해 수십만 logical read나 많은 physical read가 발생하는지 확인합니다. 이 순서가 항상 정답은 아니지만 계층을 무작위로 건드리는 일을 줄입니다. ### 최종 실습 과제 1. 공통 예제 쿼리의 EXPLAIN FORMAT=TREE 결과를 저장합니다. 2. ix_orders_status_created가 없을 때와 있을 때 계획을 비교합니다. 3. EXPLAIN ANALYZE의 estimated rows와 actual rows 차이를 적습니다. 4. 실행 전후 Buffer Pool read status의 변화량을 기록합니다. 5. 결과를 “어느 계층의 어떤 증거가 바뀌었는가”라는 문장으로 정리합니다. ### 발표 전 자가 점검 - MySQL Server와 InnoDB를 같은 말로 쓰지 않는가? - Connection, Session, Thread의 수명 차이를 설명할 수 있는가? - Preprocessor가 교육용 묶음이라는 단서를 말하는가? - EXPLAIN ANALYZE가 실제 실행한다는 점을 경고하는가? - Page 16KB를 고정값이 아니라 기본값이라고 말하는가? - clustered index가 SELECT 결과 순서를 보장한다고 말하지 않는가? - Trigger의 FOR EACH ROW와 Event Scheduler 상태를 확인하는가? ### 한 문장으로 설명하기 문제가 생기면 먼저 책임 경계를 나누고 실행 계획을 확인한 뒤 실제 Page 접근 비용을 측정합니다. ## 전체 복습 문제 답을 보기 전에 한 문장으로 먼저 설명합니다. 1. MySQL Server와 InnoDB는 어떤 책임 경계로 나뉘는가? 2. Plugin Storage Engine 구조가 공통으로 만드는 부분과 엔진마다 달라지는 부분은 무엇인가? 3. Connection, Session, Thread가 종료되거나 재사용되는 조건은 어떻게 다른가? 4. Global Memory와 Session/Statement Memory를 나누는 기준은 무엇인가? 5. Parser가 통과한 SQL이 Resolver/Preparation에서 실패할 수 있는 이유는 무엇인가? 6. Preprocessor를 공식 구현의 고정 단일 모듈이라고 말하면 왜 위험한가? 7. Optimizer가 비교하는 것은 인덱스 외에 무엇이 있는가? 8. EXPLAIN과 EXPLAIN ANALYZE의 가장 중요한 차이는 무엇인가? 9. Executor와 Handler API, Storage Engine은 어떤 요청과 결과를 주고받는가? 10. Buffer Pool과 Query Result Cache의 차이는 무엇인가? 11. Adaptive Hash Index는 사용자가 생성하는 인덱스와 어떻게 다른가? 12. dirty page와 redo log는 같은 데이터의 같은 목적 복사본인가? 13. File-per-table Tablespace에서 데이터와 인덱스 파일은 어떻게 배치되는가? 14. Page 16KB와 Extent 1MB를 절대값이라고 말하면 왜 틀릴 수 있는가? 15. 명시적 PK가 없을 때 InnoDB가 clustered index를 선택하는 순서는 무엇인가? 16. secondary index가 긴 PK의 영향을 받는 이유는 무엇인가? 17. Stored Routine, Stored Program, Stored Object의 포함 관계는 무엇인가? 18. Procedure, Function, Trigger, Event의 실행 조건은 각각 무엇인가? 19. Trigger와 Event가 application trace에서 누락되기 쉬운 이유는 무엇인가? 20. 느린 SELECT를 Boundary, Plan, Page 순서로 조사하면 무엇을 확인하는가? ### 정답 핵심어 1. Server는 연결·SQL 처리·계획·실행 제어, InnoDB는 인덱스·레코드·Page·트랜잭션 저장을 맡습니다. 2. Handler 표면은 공통이지만 트랜잭션·잠금·영속성·인덱스 능력은 엔진별로 다릅니다. 3. Connection 종료와 함께 Session은 끝나며 Thread는 cache될 수 있지만 Session 상태는 재사용되지 않습니다. 4. 서버 전체 공유인지, 연결 수명인지, 특정 문장 연산이 필요할 때만 생기는지 구분합니다. 5. 실제 객체·열·별칭·권한·데이터형과 의미 조건은 Parsing 뒤 확정됩니다. 6. 공식 구현은 Resolver/Preparation과 여러 검사로 나뉘며 버전에 따라 함수 경계가 달라질 수 있습니다. 7. 접근 방식, 조인 순서·방법, 예상 행 수, 서브쿼리·derived table 변환과 materialization 등을 비교합니다. 8. EXPLAIN ANALYZE는 문장을 실제로 실행하고 iterator별 실제 시간·행 수·loops를 제공합니다. 9. Executor가 다음 행·범위·인덱스 접근을 요청하고 엔진이 B-tree와 Page에서 레코드를 찾아 반환합니다. 10. Buffer Pool은 테이블·인덱스 Page를 캐시하며 완성된 SELECT 결과를 캐시하지 않습니다. 11. InnoDB가 반복 B-tree 접근을 관찰해 내부적으로 관리하는 보조 hash 경로입니다. 12. dirty page는 변경된 데이터 Page이고 redo는 장애 복구용 변경 기록으로 목적과 flush 시점이 다릅니다. 13. 한 테이블의 데이터와 인덱스 Page가 보통 같은 .ibd 파일에 저장됩니다. 14. Page 크기는 4–64KB에서 초기화 시 정하며 32KB·64KB 설정의 Extent는 1MB보다 큽니다. 15. PRIMARY KEY → 첫 UNIQUE NOT NULL index → hidden 6-byte row ID 순입니다. 16. secondary index leaf record가 row를 찾기 위해 PK 열을 함께 저장합니다. 17. Routine은 Procedure+Function, Program은 Routine+Trigger+Event, Object는 Program+View입니다. 18. CALL, 표현식 평가, 테이블의 row DML event, 시간 schedule입니다. 19. 사용자가 직접 호출하지 않고 서버가 자동 실행하며 definer 권한으로 동작할 수 있습니다. 20. 연결·책임 경계, 예상·실제 계획, logical·physical Page 읽기와 cache 상태를 확인합니다. ## 발표 리허설 가이드 ### 1차 리허설 — 20분 압축 각 슬라이드의 ‘한 문장으로 설명하기’만 이어 말합니다. 앞 장의 출력이 다음 장의 입력이 되는지 확인합니다. 연결이 끊기는 지점은 슬라이드 문구를 외우지 말고 이 가이드의 단계별 흐름을 다시 읽습니다. ### 2차 리허설 — 32–38분 본 발표 슬라이드에 보이는 도형은 한 번만 짚고 원리와 예시를 설명합니다. 공식 도식이 복잡한 11–14장은 모든 라벨을 읽지 말고 Buffer Pool → Tablespace → Page 경로만 추적합니다. 17–19장은 객체 문법을 전부 보여주기보다 호출 조건과 운영 위험을 중심으로 말합니다. ### 3차 리허설 — 질문 대응 다음 다섯 질문에는 30초 안에 답할 수 있어야 합니다. - InnoDB가 MySQL 서버 전체가 아닌 이유는 무엇인가? - 인덱스가 있는데 full scan을 선택하는 이유는 무엇인가? - Buffer Pool hit인데도 느릴 수 있는 이유는 무엇인가? - PK가 secondary index 크기에 영향을 주는 이유는 무엇인가? - Trigger와 Event를 애플리케이션 코드 대신 사용할 기준은 무엇인가? ## 공식 문서 읽기 순서 발표 준비가 끝난 뒤 더 깊게 공부할 때는 다음 순서가 효율적입니다. 1. [Overview of MySQL Storage Engine Architecture](https://dev.mysql.com/doc/refman/8.0/en/pluggable-storage-overview.html) 2. [How MySQL Uses Memory](https://dev.mysql.com/doc/refman/8.0/en/memory-use.html) 3. [SQL Query Execution — MySQL 8.0 Source Code Documentation](https://dev.mysql.com/doc/dev/mysql-server/8.0.46/PAGE_SQL_EXECUTION.html) 4. [EXPLAIN Statement](https://dev.mysql.com/doc/refman/8.0/en/explain.html) 5. [InnoDB Architecture](https://dev.mysql.com/doc/refman/8.0/en/innodb-architecture.html) 6. [InnoDB Buffer Pool](https://dev.mysql.com/doc/refman/8.0/en/innodb-buffer-pool.html) 7. [InnoDB Tablespaces](https://dev.mysql.com/doc/refman/8.0/en/innodb-tablespace.html) 8. [Clustered and Secondary Indexes](https://dev.mysql.com/doc/refman/8.0/en/innodb-index-types.html) 9. [Stored Objects](https://dev.mysql.com/doc/refman/8.0/en/stored-objects.html) 10. [Restrictions on Stored Programs](https://dev.mysql.com/doc/refman/8.0/en/stored-program-restrictions.html) 공식 문서는 MySQL 8.0의 마이너 버전과 빌드 옵션에 따라 세부 내용이 달라질 수 있습니다. 실습 서버에서 SELECT VERSION()과 실제 변수·metadata를 함께 확인합니다.