실험 목적
대용량 데이터셋(100만 건)에서 인덱스 유무에 따른 성능 차이를 비교하여,
인덱스가 실제 쿼리 성능에 미치는 영향을 측정한다.
실험 환경
| 항목 | 내용 |
|---|---|
| DBMS | MySQL 8.x (InnoDB) |
| 테이블 | tasks_noidx, tasks_idx |
| 데이터 건수 | 각 테이블당 1,000,000건 |
| 비교 조건 | status = 'DONE' AND is_deleted = false |
| 쿼리 캐시 제거 | SQL_NO_CACHE 사용 |
| 측정 도구 | macOS 터미널 + time 명령어 |
테이블 구조 요약
CREATE TABLE tasks_noidx (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255),
...
status VARCHAR(20),
is_deleted BOOLEAN NOT NULL,
...
);
-- tasks_idx는 아래 인덱스만 추가됨
CREATE INDEX idx_status_deadline_deleted ON tasks_idx(status, deadline, is_deleted);
사용 쿼리
SELECT SQL_NO_CACHE COUNT(*)
FROM tasks_xxx
WHERE status = 'DONE' AND is_deleted = false;
xxx는tasks_noidx또는tasks_idx- 동일한 조건으로 10회 반복 실행하여 평균·중앙값·최대값 비교
측정 결과
tasks_idx (인덱스 있음)
| 회차 | 실행 시간 (sec) |
|---|---|
| 1 | 2.699 |
| 2 | 1.549 |
| 3 | 1.368 |
| 4 | 1.247 |
| 5 | 1.275 |
| 6 | 1.192 |
| 7 | 1.199 |
| 8 | 1.233 |
| 9 | 1.151 |
| 10 | 1.158 |
- 평균: 1.407초
- 중앙값: 1.233초
- 최대값: 2.699초
tasks_noidx (인덱스 없음)
| 회차 | 실행 시간 (sec) |
|---|---|
| 1 | 2.704 |
| 2 | 1.150 |
| 3 | 1.124 |
| 4 | 1.773 |
| 5 | 1.131 |
| 6 | 1.121 |
| 7 | 1.122 |
| 8 | 1.777 |
| 9 | 1.399 |
| 10 | 1.309 |
- 평균: 1.461초
- 중앙값: 1.240초
- 최대값: 2.704초
결론 요약
| 항목 | tasks_noidx | tasks_idx |
|---|---|---|
| 평균 | 1.461초 | 1.407초 |
| 중앙값 | 1.240초 | 1.233초 |
| 최대값 | 2.704초 | 2.699초 |
해석:
COUNT(*) + WHERE단순 조건에서는 인덱스 효과가 제한적- 둘 다 InnoDB Buffer Pool에 올라간 상태라 메모리 기반 처리
- 정렬, 조인, LIMIT 등 복합 조건에서 인덱스 효과가 훨씬 크게 나타날 수 있음
다음 실험 아이디어
ORDER BY deadline DESC LIMIT 100조건 추가 실험JOIN,UPDATE,DELETE성능 비교- 복합 인덱스 vs 단일 인덱스 비교
참고 명령어
반복 측정용 bash
for i in {1..10}; do
time mysql -u root -p -D taskflow -e "SELECT SQL_NO_CACHE COUNT(*) FROM tasks_idx WHERE status = 'DONE' AND is_deleted = false;";
done
SQL_NO_CACHE를 사용해도 InnoDB Buffer Pool 등의 내부 캐시 덕분에 큰 차이가 안 날 수 있다.
InnoDB Buffer Pool이란?
InnoDB 스토리지 엔진이 디스크 I/O를 줄이기 위해 사용하는 메모리 공간
즉,
- 디스크에 있는 데이터를 미리 읽어와 메모리에 저장해두고
- 나중에 같은 데이터를 다시 사용할 때는 디스크 대신 메모리에서 바로 가져오는 구조
왜 필요한가?
디스크 I/O는 매우 느림.
- 쿼리를 날릴 때마다 디스크를 읽는다면 → 성능 저하
- 그래서 자주 조회되는 테이블, 인덱스를 **메모리(Buffer Pool)**에 올려놓음
어떤 데이터가 캐싱되나?
InnoDB Buffer Pool에는 주로 다음이 올라옴:
| 종류 | 설명 |
|---|---|
| 데이터 페이지 | 테이블의 실제 행 데이터 |
| 인덱스 페이지 | B+Tree 인덱스 노드 |
| Undo Log | 트랜잭션 롤백을 위한 변경 이력 |
| Insert Buffer | 보조 인덱스에 대한 변경사항 |
| Adaptive Hash Index | 자주 사용되는 인덱스의 해시 |
작동 흐름
- 쿼리 실행 시 필요한 페이지를 디스크에서 읽어옴
- 이 페이지를 Buffer Pool에 저장
- 이후 같은 쿼리가 오면 디스크가 아닌 메모리에서 바로 읽기 ← 성능 향상
- LRU(Least Recently Used) 방식으로 오래 안 쓰인 페이지는 버림
예: SQL_NO_CACHE와 Buffer Pool
SQL_NO_CACHE는 쿼리 결과를 **쿼리 캐시(Query Cache)**에 저장하지 말라는 옵션 (MySQL 8.0에서는 제거됨)
그러나 Buffer Pool은 여전히 동작
즉, SQL_NO_CACHE를 써도 InnoDB가 데이터 페이지 자체를 메모리에 유지하기 때문에,
똑같은 쿼리를 반복해도 성능이 일정하게 나오는 것처럼 보일 수 있음
Buffer Pool 사이즈 확인 및 설정
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
- 일반적으로 전체 시스템 메모리의 70–80%까지 할당 (DB 전용 서버라면)
- 설정 파일에서 조정: my.cnf 또는 my.ini
[mysqld]
innodb_buffer_pool_size = 1G요약
| 항목 | 설명 |
|---|---|
| 역할 | 디스크 I/O 줄이고 성능 향상 |
| 저장하는 것 | 데이터·인덱스 페이지 등 |
| 위치 | 메인 메모리(RAM) |
| 사용 조건 | 자동 사용됨 (쿼리와 무관) |
| SQL_NO_CACHE 영향 | 없음 (별도 메커니즘) |
참고
Buffer Pool은 DB 쿼리 결과가 아니라, "데이터 자체"를 메모리에 유지
SQL_NO_CACHE는 Query Cache를 무시할 뿐, InnoDB Buffer Pool은 계속 작동
쿼리 성능 실험에서 Buffer Pool이 영향을 줄 수 있으므로, 완전한 비교를 위해선 innodb_buffer_pool_size=0으로 설정하거나 서버 재시작이 필요 (현실적이지는 않음)
