이 글은 'Fundamentals of Database Engineering'에 관한 아래 유데미 강의를 보고, 공부한 내용을 정리하였습니다.
https://www.udemy.com/course/database-engines-crash-course
인덱싱 시작하기
- explain analyze로 실행계획(쿼리계획)을 분석할 수 있음
explain analyze select * from temp;Index only scan : Heap에서 fetch할 필요 없어 제일 빠르고 좋은 경우
Parallel seq scan : 전체를 다 훑어야해서 최악의 경우.
- create index로 인덱스 생성 가능
create index temp_t on temp(t);
- where 문으로 특정하였을 때 효과있지만, LIKE 문과 같이 특정하지 못하면 index 쓰는 의미가 없음
SQL 쿼리 플래너
# ex1
explain select * from temp;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on temp (cost=0.00..14425.01 rows=1000001 width=4)# ex2
explain select * from temp order by t;
QUERY PLAN
----------------------------------------------------------------------------------
Index Only Scan using temp_t on temp (cost=0.42..18420.44 rows=1000001 width=4)
Seq Scan: 테이블 전체 순차 탐색
0.00: 첫 페이지를 가져오는데 걸리는 시간 ms
14425.01: DB가 예상하는 총 ms
rows: 가져올 행의 수 (추정)
width: 모든 열의 바이트 합계
- 실행 계획(추정)일 뿐 실제 수행 시간 아님
비트맵 인덱스 스캔 vs 인덱스 스캔 vs 테이블 스캔
- Postgres는 where 문을 범위로 잡더라도 테이블 스캔을 할지 인덱스 스캔을 할지 자가판단 가능
explain select t from temp where t >10;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on temp (cost=0.00..16925.01 rows=895509 width=4)
Filter: (t > 10)
explain select t from temp where t <10;
QUERY PLAN
-------------------------------------------------------------------------------
Index Only Scan using temp_t on temp (cost=0.42..1866.06 rows=89008 width=4)
Index Cond: (t < 10)
- Postgres는 특정 상황에서 Bitmap Index Scan을 할 수 있음. Index 스캔을 해서 페이지를 파악하고, bitmap을 작성해서 한 번에 힙으로 이동하여 해당하는 모든 페이지를 가져올 수 있음.
식별자 vs 비식별자 열 데이터베이스 인덱싱
여러개의 column이 있을 때, index에 비식별자를 추가할 수 있고, 추가된 비식별자 col을 조회했을 때는 Heap을 읽을 필요 없기에 빠르게 조회할 수 있다.
create index g_idx on students(g) include(id);- 이런 방식으로 조회할 column 자체를 index에 포함해두면, 조회 시에 Heap fetch를 하지 않고 index only scan을 해서 cost를 줄일 수 있다.
+ 인덱스 크기가 크면 Disk IO가 생길 수 있는데, cost가 늘어날 수 있음.
Index scan vs Index only scan
- Index only scan은 테이블을 조회하지 않아도 되는 것... Non-Key column을 포함하는 인덱싱을 잘 활용할 것
성능 개선을 위한 DB 인덱스 결합
create table temp(a int, b int, c int);
insert into temp(a) select random()*100 from generate_series(0, 1000000);
insert into temp(b) select random()*100 from generate_series(0, 1000000);
insert into temp(c) select random()*100 from generate_series(0, 1000000);
이런 형태의 테이블이 있을 때...
1. a, b 각각에 대해서 index를 생성
explain analyze select c from temp where a=70; -> Index Scan
explain analyze select c from temp where b=70; -> Index Scan
explain analyze select c from temp where a=100 and b=70; -> 각각에 대해서 Index Scan
explain analyze select c from temp where a=100 or b=70; -> 각각에 대해서 Index Scan
2. a, b 에 대해 복합 인덱스 생성
create index on temp (a, b); 로 복합 인덱스 생성 가능
explain analyze select c from temp where a=70; -> Index Scan
explain analyze select c from temp where b=70; -> Parallel Seq Scan
explain analyze select c from temp where a=100 and b=70; -> Index Scan
explain analyze select c from temp where a=100 or b=70; -> Parallel Seq Scan인덱스 생성시에 left side인 a에 대해서만 Index Scan이 돌아감. 단, a and b에 대해서는 우수한 성능
3. a,b 복합 인덱스 + b에 대해 index 생성
최상의 시나리오
DB 옵티마이저가 인덱스 사용 여부 결정하는 방법
- 행의 수가 적다면 인덱스를 쓰는 게 cost 손해일 수 있음.
- DB는 테이블의 통계를 유지함(대략 몇 개의 행이 있고 값이 있는지 등), 이런 통계 정보를 활용해서 사용여부를 결정
- 통계를 업데이트 하지 않거나, 통계를 벗어나는 대량의 값이 삽입된 직후에 쿼리가 실행되었다면, 최적이 아닌 방법으로 실행될 수 있음
ㄴ 이럴 때는 데이터베이스 Hint를 통해 해결할 수 있음. DB의 통계보다 hint를 반영하게 하는 것
동시에 인덱스 생성
- 인덱스를 생성할 때 기본적으로 읽기는 가능하지만 쓰기는 block 됨
- postgres에서는 동시 인덱스(concurrently) 사용할 수 있음
create index concurrently idx_a on temp(a);- 이렇게 하면 여러 번의 스캔을 수행하고 index 작업 완료 전 write 트랜잭션을 기다리게 됨
Bloom filters
- A가 DB에 있는지 확인이 필요할 때, DB에 바로 요청하면 시간이 많이 걸리고, Redis와 같은 외부 인메모리 DB를 쓰면 cost가 낭비됨
- '블룸 필터'는 인메모리의 표현을 이용하는 것
- 예를 들어 64bit 메모리에 Hash(A) % 64 값을 기록하고, A 유무에 대한 질의가 왔을 때 해당 bit에 기록되어있지 않다면, 쿼리를 하지 않고도 DB에도 없는 것을 빠르게 확정 지을 수 있음 (불필요한 쿼리 방지)
1 Billion Row table
대용량 데이터를 다루는 방법
1. 테이블을 처리하거나 작업하는 과정에서 무차별 대입 방식 사용 - 테이블을 조각으로 나누고 병렬 검색
2. 모든 테이블을 처리하지 않고 subset만 처리하는 방법 - 인덱싱, 파티셔닝, 샤딩
3. 큰 테이블을 가지지 않도록 DB 디자인 단계에서 아예 피하는 것 - ex) JOIN 대신 팔로워 자체를 JSON column으로 만들어버리는 방법
