직접 테이블을 만들고 데이터를 넣어본 다음, mysql.innodb_index_stats 같은 통계를 보면서 “B+Tree가 대충 어떤 모양일까?”를 감으로 잡아본 실험 기록입니다.
틀린 부분이나 더 좋은 해석이 있으면 편하게 알려주세요!
실험 목표
employee테이블에 500만 건을 넣고,- PRIMARY(클러스터 인덱스)와 Secondary 인덱스(
department_id)의- 리프 페이지 수(
n_leaf_pages) - 전체 페이지 수(
size) - prefix distinct 통계(
n_diff_pfxXX)
- 리프 페이지 수(
- 를 비교해서,
- “왜 PRIMARY는 리프가 많고, Secondary는 리프가 적을까?”를 숫자로 확인해보기
1) 테이블 만들기
-- 부서 테이블
CREATE TABLE department (
department_id INT PRIMARY KEY AUTO_INCREMENT,-- 부서 ID
department_name VARCHAR(100) NOT NULL-- 부서명
);
-- 사원 테이블
CREATE TABLE employee (
employee_id INT PRIMARY KEY AUTO_INCREMENT,-- 사원 ID (PRIMARY KEY = 클러스터 인덱스)
employee_name VARCHAR(100) NOT NULL,-- 사원명
department_id INT NOT NULL,-- 소속 부서 ID
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)REFERENCES department(department_id) -- FOREIGN KEY: 참조 무결성(부서가 있어야 사원 삽입 가능)
ONDELETE CASCADE -- 부모(department) 삭제 시 자식(employee) 자동 삭제
ONUPDATE CASCADE
);
employee_id가 PRIMARY KEY라서 InnoDB 클러스터 인덱스의 키가 되고,department_id는 FK라서(환경에 따라) 인덱스가 만들어지거나 직접 만들어서 조인을 빠르게 하게 된다.- 그리고 InnoDB에서 Secondary 인덱스 리프에는 “secondary key + PK”가 저장된다는 이야기를 통계로 확인해볼 예정이다.
2) 부서 100개 넣기 (WITH RECURSIVE로 숫자 생성)
INSERT INTO department (department_name)
WITH RECURSIVE seqAS ( -- WITH RECURSIVE: 재귀 CTE(숫자 시퀀스 생성 등에 사용)
SELECT 1 AS n
UNION ALL -- UNION ALL: 중복 제거 없이 결과를 누적
SELECT n+1 FROM seq WHERE n<100 -- 재귀 종료 조건(100 넘으면 멈춤)
)
SELECT CONCAT('부서 ', n)AS department_name -- CONCAT: 문자열 결합
FROM seq;
3) 사원 500만 건 넣기
부서(100개) × 사원번호(각 부서당 50,000명) = 5,000,000명을 만들었다.
SET foreign_key_checks=0; -- foreign_key_checks: FK 검사 끄기(대량 insert 속도 목적)
-- 주의: 다시 1로 켠다고 “기존 데이터 FK를 사후 검증”해주진 않음(그냥 이후 검사만 켬)
SET SESSION cte_max_recursion_depth=100000;-- cte_max_recursion_depth: 재귀 CTE 최대 반복 수(기본 1000이라 대량 생성시 필요)
INSERT INTO employee (employee_name, department_id)
WITH RECURSIVE num AS (
SELECT 1 AS n
UNION ALL
SELECT n+1 FROM num WHERE n<50000 -- 1~50,000 숫자 생성
)
SELECT CONCAT('사원_', d.department_id,'_', n)AS employee_name,
d.department_id
FROM department d
JOIN num ON 1=1
-- JOIN ... ON 1=1: 항상 참 → 카티시안 곱(부서행 × num행)
-- 여기서는 department(100) × num(50,000) = 5,000,000행 생성 목적
ORDERBY d.department_id, n;
SET foreign_key_checks=1; -- FK 검사 다시 켜기
JOIN num ON 1=1은 사실상 카티시안 곱(부서 100개 × num 50,000개)이라서 500만 건이 만들어진다.
4) 통계 갱신(ANALYZE)
InnoDB의 persistent stats(인덱스 통계)는 샘플링/갱신 타이밍에 따라 달라질 수 있어서, 일단 통계를 수집해봤다.
ANALYZE TABLE employee;
5) PRIMARY(클러스터 인덱스) 통계 보기
5-1) 리프 페이지 수 / 전체 페이지 수 확인
SELECT stat_name, stat_value
FROM mysql.innodb_index_stats
WHERE database_name= DATABASE() -- DATABASE(): 현재 사용 중인 DB 이름 반환
AND table_name = 'employee'
AND index_name='PRIMARY'
AND stat_nameIN ('n_leaf_pages','size');
내 결과는 다음과 같았다.
n_leaf_pages = 13873size = 13934
여기서 해석:
n_leaf_pages: PRIMARY 인덱스의 리프 페이지 수size: PRIMARY 인덱스의 전체 페이지 수(리프 + 비리프)
따라서
- 비리프 페이지 수 =
size - n_leaf_pages = 13934 - 13873 = 61
이 61은 “루트 + 내부 노드 전체(브랜치)”의 합이다.
그래서 나는 이렇게 추론해봤다.
- “비리프 61개면, 루트 1개 + 내부노드 약 60개 정도로 볼 수 있지 않을까?”
- 그러면 높이는 루트 → 내부 → 리프(=높이 3)일 가능성이 크다.
다만, 이건 통계 기반 추정이라서
- 루트 → 상위 내부 → 하위 내부 → 리프처럼 내부 레벨이 2겹일 가능성도 있다.
- (그래도 비리프가 61개로 매우 작아서, 높이 3 가정이 자연스럽다.)
5-2) 실제 row 수 확인
SELECT COUNT(*) FROM employee;
결과는 5,000,000.
5-3) 리프 페이지당 평균 행 수
- 평균 rows per leaf page ≈
COUNT(*) / n_leaf_pages - =
5,000,000 / 13,873 ≈ 360.41
즉, PRIMARY 리프 페이지 1장에 평균 약 360행이 들어간다는 계산이 나온다.
이게 처음엔 “한 페이지에 360행이나 들어간다고?” 싶었는데, InnoDB 페이지가 16KB고 row가 생각보다 작으면 충분히 가능한 값이라는 걸 알게 됐다.
페이지 크기도 확인해봤다:
SELECT @@innodb_page_size;
내 환경은 16384 (16KB).
6) Secondary 인덱스(fk_employee_department) 통계 보기
이번엔 FK 인덱스 fk_employee_department에 대해 통계를 봤다.
이 인덱스는 employee 테이블의 department_id 기반 Secondary 인덱스다.
6-1) employee 테이블의 전체 인덱스 통계 펼쳐보기
SELECT index_name, stat_name, stat_value, stat_description
FROM mysql.innodb_index_stats
WHERE database_name= DATABASE()
AND table_name='employee'
ORDERBY index_name, stat_name;
내 결과(통계 추정치)는 다음과 같:
PRIMARY
n_diff_pfx01: 5009540(employee_id distinct 추정치)n_leaf_pages: 13873size: 13934
fk_employee_department
n_diff_pfx01: 101(department_id distinct 추정치)n_diff_pfx02: 4996714((department_id, employee_id) distinct 추정치)n_leaf_pages: 4159size: 4198
여기서 내가 이해한 포인트:
(1) n_diff_pfx01 / n_diff_pfx02는 “distinct 개수 추정치”
n_diff_pfx01은 인덱스 첫 컬럼의 distinct 개수 추정치- department_id는 1~100이니 사실상 100이어야 자연스러운데, 통계라 101처럼 살짝 튈 수 있음
n_diff_pfx02는 (첫 컬럼 + 다음 컬럼)의 distinct 개수 추정치- Secondary 인덱스 리프에는 “secondary key + PK”가 같이 저장되므로, 통계에서도
(department_id, employee_id)처럼 보이는 게 자연스럽다.
- Secondary 인덱스 리프에는 “secondary key + PK”가 같이 저장되므로, 통계에서도
(2) Secondary 인덱스의 정렬 순서
- 이 인덱스는
department_id로 정렬되고 - 같은
department_id안에서는employee_id(PK)로 다시 정렬된 것처럼 이해하면 된다.
(통계에 prefix가 (department_id, employee_id)로 찍히는 걸 보고 “아 진짜 PK가 함께 저장되는 구조구나”를 체감했다.)
6-2) “왜 Secondary 인덱스 리프 페이지 수가 훨씬 적지?”를 숫자로 확인
둘 다 500만 행인데:
- PRIMARY:
n_leaf_pages = 13,873 - Secondary:
n_leaf_pages = 4,159
Secondary가 훨씬 적다.
여기서 내가 계산해본 것:
- Secondary 인덱스 리프 페이지당 평균 엔트리 수=
5,000,000 / 4,159 ≈ 1202 - =
COUNT(*) / n_leaf_pages
즉,
- PRIMARY 리프: 평균 약 360행/페이지
- Secondary 리프: 평균 약 1202행/페이지
차이가 큰 이유는 구조적으로 자연스럽다.
- PRIMARY(클러스터) 리프에는 행 데이터 자체가 저장된다.
- (employee_name 같은 VARCHAR 포함 + 레코드 오버헤드)
- Secondary 리프에는 (인덱스 컬럼 + PK) 중심으로 저장된다.
- (여기서는 대략
department_id + employee_id+ 오버헤드)
그래서 Secondary 엔트리가 훨씬 작고, 한 페이지에 더 많이 들어가서 리프 페이지 수도 더 적게 나온다.
7) 정리: 내가 이번 실험으로 얻은 결론
mysql.innodb_index_stats의n_leaf_pages,size를 보면- “리프가 몇 장인지, 비리프가 얼마나 적은지” 같은 감을 잡을 수 있었다.
- PRIMARY와 Secondary는 행 수는 같아도 “리프 페이지 수가 크게 다를 수 있다.”
- PRIMARY는 행 데이터가 들어가서 엔트리가 크고
- Secondary는 (secondary key + PK) 위주라 엔트리가 작다.
n_diff_pfxXX통계를 보면- Secondary 인덱스 리프에 PK가 같이 저장되는 구조를 체감할 수 있었다.
- 그리고 통계 값(
n_rows,n_diff_pfxXX)은 정확한 실측이 아니라 추정치라 ANALYZE TABLE로 갱신해도 약간 튈 수 있다는 걸 같이 기억해야 한다.