DROP TABLE IF EXISTS users; # 기존 테이블 삭제
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT
);
100만 건의 랜덤 데이터 삽입
인덱스 설정 전후의 성능 차이를 비교하기 위해 랜덤 데이터를 넣어보자.
-- 높은 재귀(반복) 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성하면 된다.)
SET SESSION cte_max_recursion_depth = 1000000;
-- 더미 데이터 삽입 쿼리
INSERT INTO users (name, age)
WITH RECURSIVE cte (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT
CONCAT('User', LPAD(n, 7, '0')), -- 'User' 다음에 7자리 숫자로 구성된 이름 생성
FLOOR(1 + RAND() * 1000) AS age -- 1부터 1000 사이의 랜덤 값으로 나이 생성
FROM cte;
-- 잘 생성됐는 지 확인
SELECT COUNT(*) FROM users;
참고) 이 SQL문을 스스로 작성할 수 있을 정도까지는 공부하지 않아도 된다. 위 SQL문을 내 테이블과 컬럼에 맞게 커스텀 할 수 있을 정도까지만 이해하고 있으면 된다.
데이터 조회해보기
SELECT * FROM users
WHERE age = 23;
주의) Workbench, DBeaver와 같은 GUI 툴을 써서 위 쿼리문을 날릴 경우, 데이터의 개수가 제한돼서 조회된다. 해당 프로그램에 자체적으로 limit가 걸려있는 지 체크해보기 바란다.
DBeaver에서는 한 번에 조회할 수 있는 데이터 개수가 200개로 제한이 걸려있다. 이 제한이 걸려있으면 제대로 성능 테스트를 하기가 어렵다.
SQL문 실행 시 걸리는 소요 시간 측정하기
❗
성능 개선을 할 때 ‘빨라진 것 같아요'라는 말은 없다. 수치를 정확히 측정해서 얼마나 더 빨라지고 느려졌는 지를 정확하게 숫자로 얘기할 수 있어야 한다.
데이터 조회를 할 때 걸리는 소요 시간이 대략 200ms 정도 걸린다.
인덱스 설정하기
인덱스 생성
# 인덱스 생성
# CREATE INDEX 인덱스명 ON 테이블명 (컬럼명);
CREATE INDEX idx_age ON users(age);
인덱스 조회
# SHOW INDEX FROM 테이블명;
SHOW INDEX FROM users;
데이터 다시 조회해보고 SQL문 실행 소요 시간 측정해보기
SELECT * FROM users
WHERE age = 23;
데이터 조회를 해보니 약 40ms 정도 걸린다.
인덱스를 적용 전과 후의 성능 차이가 200ms → 40ms로 5배 정도 차이가 난다.
✅ 직관적으로 이해하기
인덱스를 직접 생성하게 되면 우리 눈에는 안 보이지만 왼쪽과 같은 표가 시스템 내부적으로 생성된다. 나이를 기준으로 정렬해놓은 표를 가지고 있기 때문에, 나이를 기준으로 데이터를 조회할 때 훨씬 빠르게 찾을 수 있었던 것이다.
