개발공부 기록지

When and How You Should Denormalize a Relational Database 정리한 내용

웹사이트의 속도가 빠를수록 검색 엔진에서 높은 순위를 차지하며 사용자 경험도 개선됩니다.

데이터베이스 최적화는 웹사이트 성능 향상에 필수적인 과정입니다. 일반적으로 개발자들은 데이터 중복을 줄이고 무결성을 향상시키기 위해 관계형 데이터베이스를 정규화합니다. 즉, 데이터베이스를 재구성하는 것입니다.

그러나 가끔은 정규화만으로는 충분하지 않을 때가 있습니다. 이런 경우, 성능 향상을 위해 개발자들은 데이터베이스를 비정규화하기도 합니다.

데이터베이스를 언제 비정규화해야 할까

정규화는 데이터베이스 설계의 첫 단계로, 데이터를 적절한 위치에 배치하여 무결성을 보장하고 업데이트를 용이하게 합니다. 이 과정은 중복을 제거하여 데이터 관리를 효율적으로 만듭니다. 그러나, 데이터가 여러 테이블에 분산되어 있으면, 쿼리가 복잡해지고 데이터 검색 속도가 느려질 수 있습니다.

반면, 비정규화는 데이터 검색 속도를 높이기 위해 동일한 데이터를 의도적으로 여러 위치에 저장하는 방법입니다. 이 방식은 데이터베이스의 응답 시간을 단축시키지만, 업데이트 과정에서 일관성 유지에 더 많은 노력을 요구할 수 있습니다.

따라서, 비정규화는 검색 성능을 중시하는 특정 상황에서 선택적으로 사용되어야 합니다. 데이터의 중복을 허용함으로써 빠른 검색을 제공하지만, 데이터 관리의 복잡성과 오류 가능성을 증가시킬 수 있기 때문입니다.

1. 쿼리 성능 향상

정규화된 데이터베이스는 데이터의 무결성을 유지하기 위해 여러 테이블로 데이터를 분리합니다. 하지만, 이 구조에서는 쿼리가 여러 테이블에 걸쳐 조인을 수행해야 하기 때문에 쿼리 성능이 저하될 수 있습니다. 이 문제를 해결하기 위해, 자주 사용되는 데이터를 부모 테이블에 복사하여 중복을 허용함으로써 조인을 줄일 수 있습니다. 이 방법은 데이터 검색 속도를 향상시키지만, 데이터 동기화에 주의가 필요합니다.

2. 운영 편의성 증가를 위한 데이터 베이스의 비정규화

정규화 과정에서는 계산된 값들을 따로 저장하지 않고, 필요할 때마다 실시간으로 계산합니다. 이는 데이터베이스의 쿼리 실행 속도를 늦출 수 있습니다. 비정규화를 통해 계산된 값을 사전에 테이블에 저장함으로써, 개발자들이 복잡한 계산 로직을 이해하지 않고도 보고서나 쿼리를 쉽게 작성할 수 있도록 도움을 줍니다.

3. 리포팅 가속화와 용이성 향상

애플리케이션은 종종 다양한 분석 및 통계 정보를 제공해야 할 필요가 있습니다. 정규화된 데이터베이스 구조에서는 보고서 작성 시 많은 계산과 데이터 집계가 필요하여, 이 과정이 시스템의 성능을 저하시킬 수 있습니다. 비정규화를 통해 예를 들어, 사용자 테이블에 연간 누적 매출 요약 같은 중요한 정보를 미리 계산하여 저장함으로써, 리포팅을 빠르고 쉽게 만들 수 있습니다. 이러한 접근 방식은 데이터 처리 시간을 단축시켜 성능 저하를 최소화하는 데 도움이 됩니다.

데이터베이스 비정규화 기법 요약

1. 유도 가능한 데이터 저장

자주 계산해야 하는 값을 미리 저장하여 조회 속도를 높인다.

• 장점: 매번 계산할 필요 없음, 조회 성능 향상
• 단점: 원본 데이터 변경 시 재계산 필요, 데이터 불일치 가능성
• 예시: 이메일 메시지 시스템에서 users_received_count를 추가하여 수신자가 모두 삭제되면 메시지를 자동 삭제하도록 함.

2. 사전 조인된 테이블 사용

조인을 최소화하기 위해 비즈니스적으로 중요한 컬럼이 아닌 값을 테이블에 추가한다.

• 장점: 조인 없이 빠른 조회 가능
• 단점: 업데이트 시 추가적인 DML((Data Manipulation Language) 필요, 추가 저장 공간 사용
• 예시: User_messages 테이블에 category_name 컬럼 추가 → 만약 Categories 테이블에서 category_name이 변경되면, User_messages 테이블의 category_name도 일괄적으로 업데이트되어야 한다.

3. 하드코딩된 값 사용

변하지 않는 값을 참조 테이블 대신 애플리케이션에서 하드코딩하여 조회 속도를 향상시킨다.

• 장점: 참조 테이블 조인 불필요
• 단점: 값 변경 시 코드 수정 필요
• 예시: User_kinds 테이블 대신 사용자 유형을 하드코딩하고, 체크 제약 조건을 설정.

4. 마스터 테이블에 상세 정보 포함

상세 테이블의 레코드 개수가 적거나 마스터 테이블과 함께 조회될 경우, 상세 데이터를 마스터 테이블에 포함한다.

• 장점: 조인 불필요, 저장 공간 절약 가능
• 단점: DML 복잡성 증가
• 예시: Users 테이블에 message_space_allocated, message_space_available 등의 저장 공간 제한 정보를 추가.

5.  특정 세부 정보 반복 저장

특정한 단일 레코드를 자주 조회하는 경우, 해당 값을 별도 외래키 컬럼에 저장하여 조인을 최소화한다.

• 장점: 단일 레코드 조회 시 조인 필요 없음
• 단점: 데이터 불일치 가능성 존재
• 예시: Messages 테이블에 first_attachment_name을 추가하여 첫 번째 첨부파일 정보를 빠르게 조회.

6. 단축 키(short-circuit key) 추가

마스터-디테일 관계가 3단계 이상일 때, 최상위 레벨과 최하위 레벨을 직접 연결하는 단축 키를 추가한다.

• 장점: 조인 개수 감소
• 단점: 외래 키 증가, 값 일관성 유지 필요
• 예시: Messages 테이블에 Users 테이블의 기본 키를 직접 추가하여 Categories 테이블을 거치지 않고 조회 가능하게 함.

비정규화의 단점 및 고려사항

비정규화는 성능을 향상시키지만 여러 단점이 존재함.

• 추가 저장 공간 필요: 데이터 중복으로 인해 저장 공간 증가
• 문서화 필요: 비정규화된 데이터 구조는 철저히 문서화해야 유지보수 가능
• 데이터 이상 발생 가능: 중복된 데이터의 동기화 필요
• 추가 코드 필요: 비정규화로 인해 더 많은 업데이트 로직 요구
• 쓰기 성능 저하: 조회 속도는 빨라지지만, 데이터 수정 및 삽입이 느려질 수 있음

비정규화 적용시 주의할 점

• 전체 데이터베이스가 아닌 특정 성능 저하 부분만 비정규화할 것
• 애플리케이션의 논리적 설계를 철저히 분석한 후 적용
• 데이터 변경 빈도를 고려하여 유지보수가 가능한지 검토
• 쿼리 최적화를 통해 성능 문제를 해결할 수 있는지 먼저 확인
• 필요할 경우 가장 적절한 데이터 저장 기법을 선택하여 적용

분명 인덱스를 걸었음에도 풀 테이블 스캔으로 데이터를 조회하는 경우가 있다.

경우 1 : 넓은 범위의 데이터를 조회하는 경우

옵티마이저가 넓은 범위의 데이터를 조회할 때는 인덱스를 활용하는 것이 비효율적이라고 판단한다. 왜냐면, 굳이 인덱스를 거쳤다가 각 원래 테이블의 데이터를 일일이 하나씩 찾아내는 것보다, 바로 원래 테이블에 접근해서 모든 데이터를 통째로 가져와서 정렬하는게 효율적이라고 판단하기 때문이다.

(예시)

SELECT * FROM users 
ORDER BY name DESC;

  ▶ 이런 경우 인덱스를 활용하지 않고 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적이라고 판단

넓은 범위의 데이터를 조회하는 경우, 인덱스를 사용해서 조회하는 것보다 풀 테이블 스캔을 이용하는 것이 효과적.

* 최적화를 위하여 넓은 범위의 데이터를 조회하는지 잘 파악이 안된다면, 인덱스를 적용한 후에 실행 계획 조회해 보자.

그림에 보이듯이 인덱스를 했음에도 불구하고 trype이 ALL 인 것을 볼 수 있다. 

  → 풀 테이블 스캔으로 데이터를 찾을 때 훨씬 효율적

CREATE INDEX idx_name ON users (name);

EXPLAIN SELECT * FROM users 
ORDER BY name DESC;

경우 2 : 인덱스 컬럼을 가공(함수 적용, 산술 연산, 문자열 조작 등 )을 한 경우

id, name, salary, created_at 콜롬들을 가진 users 테이블에 100만 건의 랜덤 데이터 삽입

인덱스 생성

CREATE INDEX idx_name ON users (name);
CREATE INDEX idx_salary ON users (salary);

실행 계획 조회해보기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE SUBSTRING(name, 1, 10) = 'User000000';

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary * 2 < 1000
ORDER BY salary;

첨부된 결과 사진에 있는 타입에 보이듯이 ALL 이다. 즉, 풀테이블 스캔을 한 것을 볼 수 있다.

인덱스 컬럼을 가공해서 사용하지 않게 SQL문 수정하기

# User000000으로 시작하는 이름을 가진 유저 조회
EXPLAIN SELECT * FROM users
WHERE name LIKE 'User000000%'; # % 와일드 카드: 0개 이상의 어떤 문자든지 매칭될 수 있음

# 2달치 급여(salary)가 1000 이하인 유저 조회
EXPLAIN SELECT * FROM users
WHERE salary < 1000 / 2
ORDER BY salary;

위 두 결과 그림에서 type 을 보면 인덱스가 제대로 활용 된 것을 볼 수 있다.

인덱스를 활용하기 위해서는 인덱스 컬럼 자체를 최대한 가공하지 않아야 한다.

최근 3일 이내에 가입한 유저 조회하기

데이터 조회

SELECT * FROM users
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

인덱스를 추가하기전

인덱스 추가 후

CREATE INDEX idx_created_at ON users (created_at);

created_at 에 인덱스를 추가함으로써 검색 타입이 ALL 에서 range 로 바뀌고 소요시간이 약 0.35 초 에서 0.056 초로 감소

Sales 부서이면서 최근 3일 이내에 가입한 유저 조회하기

데이터 생성

DROP TABLE IF EXISTS users; 

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

100만건 데이터 생성 후 데이터 조회해서 성능 측정

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY)

실행계획 확인

EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

#세부내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

-> Filter: ((users.department = 'Sales') and (users.created_at >= <cache>((now() - interval 3 day))))  (cost=93877 rows=33224) (actual time=3.34..243 rows=115 loops=1)
    -> Table scan on users  (cost=93877 rows=996810) (actual time=0.148..191 rows=1e+6 loops=1)

성능개선 할 수 있는 방법들

1. created_at 컬럼을 기준으로 인덱스 추가

2. department 컬럼을 기준으로  인덱스 추가

3. department 와 created_at 두 컬럼에  인덱스 추가

4. 멀티 컬럼 인덱스 = (created_at, department) ? (department, created_at) ?

< created_at 컬럼을 기준으로 인덱스 추가 > 

CREATE INDEX idx_created_at ON users (created_at);

#성능평가
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

0.24초 에서 0.033 초로 소요시간 단축

type = range , 모든 테이블을 검색하지 않고 idx_created_at 인덱스 레인지 스캔을 함 데이터 접근 개수(rows)도 줄어들었다.

< department 컬럼을 기준으로 인덱스 추가 > 

ALTER TABLE users DROP INDEX idx_created_at; # 기존 created_at 인덱스 삭제
CREATE INDEX idx_department ON users (department);

# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

약 0.13초 소요 created_at 컬럼을 기준으로 인덱스 추가 한 경우가 소요시간이 훨씬 짧음

ref 타입, = 비고유 인덱스로 조회를 했다 rows = 191,314 는 인덱스를 하기전 보다 좋아 졌지만 created_at 컬럼을 기준으로 인덱스 추가  한 경우보다 많은 데이터에 접근

<department 와 created_at 두 컬럼에  인덱스 추가>

# CREATE INDEX idx_department ON users (department); # 위에서 이미 추가함
CREATE INDEX idx_created_at ON users (created_at); # created_at 인덱스 추가

# 성능 측정
SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획
EXPLAIN SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

# 실행 계획 세부 내용
EXPLAIN ANALYZE SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

소요 시간은 idx_created_at 과 비슷

idx_created_at 과 똑같이 range 타입으로 일정 범위만 검색

-> Filter: (users.department = 'Sales')  (cost=498 rows=212) (actual time=0.0929..11.6 rows=114 loops=1)
    -> Index range scan on users using idx_created_at over ('2024-12-27 16:39:14' <= created_at), with index condition: (users.created_at >= <cache>((now() - interval 3 day)))  (cost=498 rows=1106) (actual time=0.062..11.3 rows=1106 loops=1)

디테일을 확인해 보면 idx_created_at 만 사용

결론적으로 idx_department 을 사용하지 않았다는 것은 department 컬럼에 한 인덱스가 필요가 없고 판단하여 idx_department는 의미 성능평가에서도 비슷하므로 created_at 컬럼에만 인덱스를 하는 것이 가장 효율적인 최적화 방법으로 볼수 있다.

[이것만은 꼭 기억해두자!] 

데이터 액세스(rows)를 크게 줄일 수 있는 컬럼은 중복 정도가 낮은 컬럼이다.

따라서 중복 정도가 낮은 컬럼을 골라서 인덱스를 생성해라.

< 멀티 컬럼 인덱스 >

공통 : 성능테스트

SELECT * FROM users
WHERE department = 'Sales'
AND created_at >= DATE_SUB(NOW(), INTERVAL 3 DAY);

순서 (created_at, department) 

ALTER TABLE users DROP INDEX idx_created_at;
ALTER TABLE users DROP INDEX idx_department;
CREATE INDEX idx_created_at_department ON users (created_at, department);

성능 테스트 결과 :  소요시간 약 0.0035 초

순서 (department, created_at)

ALTER TABLE users DROP INDEX idx_created_at_department;
CREATE INDEX idx_department_created_at ON users (department, created_at);

성능 테스트 결과 : 소요시간 약 0.0033초

멀티 컬럼 인덱스를 사용했지만 created_at 만 인덱스를 설정했을 때의 성능과 크게 차이가 나지 않는다.

 ’단일 컬럼에 설정하는 일반 인덱스’를 설정했을 때와 ‘멀티 컬럼 인덱스를 설정했을 때’의 성능 차이가 별로 나지 않는다면 ?

▶ 멀티 컬럼 인덱스를 사용하지 말고 일반 인덱스를 활용하자.

비교한 인덱스 방식의 성능 테스트 비교 표

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전

실제 페이스북, 인스타그램의 서비스를 보더라도 한 번에 모든 게시글의 데이터를 불러오지 않는다. 

→ 페이지네이션을 이용하여 일부 데이터만 조회

예를 들어 100만 건의 데이터가 있을 때 하나의 데이터를 찾기 위해 모든 데이터를 불러오면 성능에 영향을 끼친다.

→ 조회하는 데이터의 개수가 성능에 많은 영향

조회하는데 걸리는 시간 측정 (100만 유저가 있는 데이터베이스)

전부조회

SELECT * FROM users;

제한된 조회 10000 건

SELECT * FROM users LIMIT 10000;

제한된 조회 100 건

조회하는 데이터의 개수가 많을 수록 소요시간이 많아진다.

시스템 성능 저하를 방지하기위해 해야할 것

1. 데이터를 조회할 때 한 번에 너무 많은 데이터를 조회하는 건 아닌 지 체크

2. LIMIT, WHERE 문 등을 활용해서 한 번에 조회하는 데이터의 수를 줄이는 방법을 고려

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전

실행 계획(EXPLAIN)

옵티마이저가 SQL문을 어떤 방식으로 어떻게 처리할 지를 계획한 걸 의미

 이 실행 계획을 보고 비효율적으로 처리하는 방식이 있는 지 점검하고, 비효율적인 부분이 있다면 더 효율적인 방법으로 SQL문을 실행하게끔 튜닝을 하는 게 목표

사용하는 방법

# 실행 계획 조회하기
EXPLAIN [SQL문]

# 실행 계획에 대한 자세한 정보 조회하기
EXPLAIN ANALYZE [SQL문]

사용예시

   가정 :  콜롬이 name, age 로 구성된 users 테이블에 7명의 데이터가 저장되어 있다.

EXPLAIN [SQL문]

EXPLAIN SELECT * FROM users
WHERE age = 23;

결과

• id : 실행 순서
• select_type : (처음에는 몰라도 됨)
• table : 조회한 테이블 명
• partitions : (처음에는 몰라도 됨)
• type : 테이블의 데이터를 어떤 방식으로 조회하는 지 
• possible keys : 사용할 수 있는 인덱스 목록을 출력 
• key : 데이터 조회할 때 실제로 사용한 인덱스 값 
• key_len : (처음에는 몰라도 됨)
• ref : 테이블 조인 상황에서 어떤 값을 기준으로 데이터를 조회했는 지
• rows : SQL문 수행을 위해 접근하는 데이터의 모든 행의 수 (= 데이터 액세스 수)  → 이 값을 줄이는 게 SQL 튜닝의 핵심
• filtered : 필터 조건에 따라 어느 정도의 비율로 데이터를 제거했는 지 의미
  → filtered 비율이 낮을 수록 쓸데없는 데이터를 많이 불러온 것.
  → filtered의 값이 30이라면 100개의 데이터를 불러온 뒤 30개의 데이터만 실제로 응답하는데 사용했음을 의미한다.
• Extra : 부가적인 정보를 제공
  → ex. Using where, Using index

(주의점) rows, filtered의 값은 정확한 수치가 아닌 추정값이기 때문에 오차가 있을 수 있다.

실행 계획에서 type 의미 분석하기

All : 풀 테이블 스캔

인덱스를 활용하지 않고 테이블을 처음부터 끝까지 전부다 뒤져서 데이터를 찾는 방식 → 비효율적이므로 최적화 필요

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    age INT
);

INSERT INTO users (name, age) VALUES 
('Alice', 30),
('Bob', 23),
('Charlie', 35);

# 실행 계획 조회하기
EXPLAIN SELECT * FROM users WHERE age = 23;

type 에 ALL 이 나온 이유

• users 테이블의 데이터는 age를 기준으로 정렬되어 있지 않고 id를 기준으로 정렬되어 있다.
• age = 23의 값을 가진 데이터를 찾으려면 테이블의 처음부터 끝까지 다 뒤져봐야 한다.
  • 그래서 실행 계획의 type이 ALL로 나온 것 

index : 풀 인덱스 스캔

인덱스 테이블을 처음부터 끝까지 다 뒤져서 데이터를 찾는 방식 →  인덱스의 테이블은 실제 테이블보다 크기가 작기 때문에, 풀 테이블 스캔(Full Table Scan)보다 효율적

# 높은 재귀(반복) 횟수를 허용하도록 설정
# 생성할 더미 데이터의 개수와 맞춰서 작성
SET SESSION cte_max_recursion_depth = 1000000; 

# 더미 데이터 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;


# 인덱스 생성
CREATE INDEX idx_name ON users (name);

# 실행 계획 조회
EXPLAIN SELECT * FROM users 
ORDER BY name 
LIMIT 10;

1. name을 기준으로 정렬해서 데이터를 가져와야 하기 때문에, name을 기준으로 정렬되어 있는 인덱스를 조회
   • 덩치가 큰 users 테이블의 데이터를 하나씩 찾아보면서 정리를 하는 것보다, 이미 name을 기준으로 정렬되어 있는 인덱스를 참고하는 게 효율적이라고 판단
2. 모든 인덱스의 값을 다 불러온 뒤에 최상단 10개의 인덱스만 추출
3. 10개의 인덱스에 해당하는 데이터를 users 테이블에서 조회

const  : 1건의 데이터를 바로 찾을 수 있는 경우

고유 인덱스 또는 기본 키를 사용해서 1건의 데이터만 조회한 경우에 type 에 const가 출력된다.

예시

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    account VARCHAR(100) UNIQUE
);

INSERT INTO users (account) VALUES 
('user1@example.com'),
('user2@example.com'),
('user3@example.com');


EXPLAIN SELECT * FROM users WHERE id = 3;
EXPLAIN SELECT * FROM users WHERE account = 'user3@example.com';

UNIQUE 로 설정한 콜롬은 인덱스가 자동으로 생성이 되고 고유한 값이므로 한번의 검색으로 해결이 된다.

range : 인덱스 레인지 스캔(Index Range Scan)

인덱스를 활용해 범위 형태의 데이터를 조회한 경우를 의미

• 범위 형태란 BETWEEN, 부등호(<, >, ≤, ≥), IN, LIKE를 활용한 데이터 조회
• 데이터를 조회하는 범위를 신경써야 한다.

예시

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    age INT
);

-- 높은 반복 횟수를 허용하도록 설정
-- (아래에서 생성할 더미 데이터의 개수와 맞춰서 작성)
SET SESSION cte_max_recursion_depth = 1000000;  

-- 더미 데이터 삽입 쿼리
INSERT INTO users (age)
WITH RECURSIVE cte (n) AS
(
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM cte WHERE n < 1000000 -- 생성하고 싶은 더미 데이터의 개수
)
SELECT 
    FLOOR(1 + RAND() * 1000) AS age    -- 1부터 1000 사이의 난수로 나이 생성
FROM cte;

-- 인덱스 생성
CREATE INDEX idx_age ON users(age);

실행계획 조회

EXPLAIN SELECT * FROM users
WHERE age BETWEEN 10 and 20;

EXPLAIN SELECT * FROM users
WHERE age IN (10, 20, 30);

EXPLAIN SELECT * FROM users
WHERE age < 20;

ref : 풀 인덱스 스캔

UNIQUE가 아닌 컬럼의 인덱스를 사용한 경우 type에 ref가 출력

예시

DROP TABLE IF EXISTS users; # 기존 테이블 삭제

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100)
);

INSERT INTO users (name) VALUES 
('박재성'),
('홍길동'),
('임정우');

CREATE INDEX idx_name ON users(name);

EXPLAIN SELECT * FROM users WHERE name = '임정우';

참고 강의

(인프런) 비전공자도 이해할 수 있는 MySQL 성능 최적화 입문/실전