InnoDB B+Tree 페이지 통계로 PRIMARY vs Secondary 인덱스 감 잡아보기 (실험 기록)

직접 테이블을 만들고 데이터를 넣어본 다음, 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 = 13873
• size = 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: 13873
• size: 13934

fk_employee_department

• n_diff_pfx01: 101 (department_id distinct 추정치)
• n_diff_pfx02: 4996714 ((department_id, employee_id) distinct 추정치)
• n_leaf_pages: 4159
• size: 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)처럼 보이는 게 자연스럽다.

(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로 갱신해도 약간 튈 수 있다는 걸 같이 기억해야 한다.