- full table scan: 데이터가 많아질수록 검색 속도가 느려진다
- index scan: 가나다순 색인표가 있을 경우 빠르게 찾을 수 있다. 단, 데이터 추가할 경우 관리 필요
인덱스
-
테이블과 별도의 자료구조로 관리(b-tree)
-
@Id를 붙이면 primary key 라는 인덱스가 생김
-
unique도 인덱스가 생김
-
db 외의 추가공간이 필요하게 되고, insert update 속도 저하
-
자주 갱신되는 컬럼에는 인덱스를 안 거는 게 좋고, 인덱스를 너무 많이 만들지 않는 게 좋다
-
참고 ctrl b 하면 테이블의 정보들 확인 가능(테이블 생성문)
- 인덱스 실습내용 참고(빅데이터 생성시)
create database bigdatatest;
use bigdatatest;
CREATE TABLE user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
CREATE TABLE user (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
);
// 천만건 데이터 생성
INSERT INTO user (name, age)
SELECT
CONCAT('user',
n1.a + n2.a*10 + n3.a*100 + n4.a*1000 + n5.a*10000 + n6.a*100000 + n7.a*1000000 + 1),
FLOOR(RAND() * 100)
FROM
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n1,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n2,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n3,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n4,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n5,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n6,
(SELECT 0 a UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9) n7;
SELECT COUNT(*) FROM user;
//내컴퓨터의 경우 2.8초 걸렸다
SELECT * FROM user WHERE name = 'user5000';
CREATE INDEX idx_user_name ON user(name);\
//내컴퓨터의 경우 0.4초 걸렸다
SELECT * FROM user WHERE name = 'user5000';b-tree
배열구조
Index: 0 1 2 3 4
Value: [10][20] [30][40] [50]
트리 구조
[50]
/ \
[20] [70]
/ \ / \
[10] [30] [60] [80]
- 작으면 왼쪽 크면 오른쪽
- 읽는법: 왼쪽 부모 오른쪽 부모 오른쪽 부모 오른쪽
.... 루트를 만나면 쭉내려가서 제일왼쪽, 부모 오른쪽 을 반복
인덱스 스캔
- 인덱스 자체는 정렬되어 있음
- 어떤 스캔을 할지는 데이터베이스의 옵티마이저가 알아서 정함(explain 명령어를 통해 확인 가능)
종류
- unique scan: 한개의 데이터를 찾기
- range scan: 일정구간의 데이터를 찾기(between, ><, like 등에서 발생)
- full index scan: 인덱스 전체를 읽기(order by)
- icp: index condition pushdown. 조건중 일부를 인덱스 단계에서 미리 필터링(더 효율적인 걸로)
- table full scan: 인덱스가 없는 탐색
실습
CREATE TABLE users (
id BIGINT PRIMARY KEY,
name VARCHAR(50),
age INT,
city VARCHAR(50),
INDEX idx_name (name),
INDEX idx_age (age)
);
INSERT INTO users (id, name, age, city)
VALUES
(1, 'Viva', 30, 'Seoul'),
(2, 'Ravi', 29, 'Busan'),
(3, 'Viva', 25, 'Incheon'),
(4, 'Minho', 30, 'Daegu');
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM users WHERE id = 1;
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM users WHERE age BETWEEN 20 AND 30;
EXPLAIN FORMAT=TRADITIONAL SELECT * FROM users WHERE city = 'Seoul';
- type, key, rows 를 보면 됨
- type에 const range ref인 게 좋다
| 신호 | 의미 | 비유 |
|---|---|---|
| type = ALL | 모든 데이터를 끝까지 검색 (Full Table Scan) | “사전 전체를 첫 장부터 찾는 중” |
| key = NULL | 인덱스를 사용하지 않음 | “색인표 없이 책을 찾는 중” |
| rows 숫자 큼 | 많은 행을 읽어야 함 | “학생 1명 찾는데 전교생 호출 중” |
-
unique scan의 경우 type은 const, key 어떤인덱스를쓸지(pk) 라고 나옴
-
range scan 의 경우 타입은 range, key도 아까 만든 인덱스 idx_age 를 사용한다고 나옴.
-
table full scan 의 경우 인덱스가 없으니 타입은 all, key도 없다고 나옴
복합 인덱스
- start_date, end_date 두개를 묶어서 하나의 인덱스로 만드는 것
- 순서가 중요함
- leftmost prefix rule: 왼쪽것들부터 순서대로 작동
왼쪽 것이 같을 경우 오른쪽 것으로 정렬 - start만 있는 경우 복합 인덱스 사용 가능
- start, end 둘다 있는 경우 복합 인덱스 사용 가능
- end만 있는 경우 복합 인덱스 사용 불가능(왼쪽것부터 보니까)
- 맨 왼쪽 것을 포함해야만 사용 가능
| 인덱스 컬럼 조건 | 사용 가능 여부 | 설명 |
|---|---|---|
| a | 가능 | 복합 인덱스 (a, b, c)의 선두 컬럼이므로 사용 가능 |
| a, b | 가능 | 선두부터 순서대로 사용하므로 가능 |
| a, b, c | 가능 | 인덱스 전체를 모두 활용 |
| b | 불가능 | 선두 컬럼 a를 건너뛰었기 때문 |
| b, c | 불가능 | a 없이 시작하므로 인덱스 사용 불가 |
실습
- 복합 인덱스 생성
INSERT INTO events (title, start_date, end_date)
WITH RECURSIVE seq AS (
SELECT 1 AS n
UNION ALL
SELECT n + 1 FROM seq WHERE n < 1000
)
SELECT
CONCAT('event', n),
DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 90) DAY),
DATE_ADD('2025-01-01', INTERVAL FLOOR(RAND() * 90) + 5 DAY)
FROM seq;
CREATE INDEX idx_start_end ON events(start_date, end_date);
EXPLAIN FORMAT=TRADITIONAL
SELECT * FROM events
WHERE start_date = '2025-02-01'
AND end_date = '2025-02-10';
DROP INDEX idx_start_end ON events;
CREATE INDEX idx_end_start ON events(end_date, start_date);
EXPLAIN FORMAT=TRADITIONAL
SELECT * FROM events
WHERE start_date = '2025-02-01'
AND end_date = '2025-02-10';
- 복합 인덱스가 잘 적용되었다!
- 인덱스 순서를 뒤집을 경우 잘못될 가능성이 있어서 일치시켜주는 게 좋음
=> 두 조건이 반복해서 같이 쓰이는 경우에 만들면 좋다!
스프링에 적용하기
- 쿼리 조회시 서버에 출력되는 sql문을 활용
- 내 경우 table이 안 보여서 yml을 update 로 수정한 후 진행했다
- 쿼리콘솔에 해당 내용 입력
EXPLAIN format = traditional
select
p1_0.content,
cast(count(distinct c1_0.id) as signed)
from
posts p1_0
left join
users u1_0
on p1_0.user_id=u1_0.id
left join
comments c1_0
on c1_0.post_id=p1_0.id
where
u1_0.username=?
group by
p1_0.id-
아래와 같이 나온다. post와 comment는 풀 스캔을 하고 있음
-
따라서 인덱스 추가
create index idx_posts_user_id on posts(user_id);
CREATE INDEX idx_comments_post_id ON comments(post_id);
- 참고 바인딩된 파라미터 출력
logging:
level:
root: info # 전체 로그 기본 레벨 유지 (error, warn 포함)
org.hibernate.SQL: debug # SQL 로그
org.hibernate.orm.jdbc.bind: trace # 바인딩된 파라미터 출력
도전