[SQLite] INDEX

- INDEX가 작동하는 방법 

- 데이터베이스가 데이터를 저장하는 방법 

- INDEX 잘 사용하기: 사용해야 할 때와 사용하지 말아야 할 때 

 

Table Scan 

: 데이터베이스가 검색을 위해 한 행씩 찾아보는 것 

 

Query Plan

: 내부적으로 어떤 방법을 사용하여 데이터를 검색하는지 

 

rowid

- SQLite에서 데이터별로(by row) 자동으로 부여하는 고유 식별자(내부 칼럼) 

- 별도로 생성하지 않아도 저절로 만들어진다 

- rowid를 사용하면 조회 속도가 빠르다 

- MySQL: 'primary key가 없거나 AND 테이블에 unique column이 없는 경우' 생성된다 

SQLite에서처럼 사용할 수는 없다(내부용으로만)

 

SQLite의 INDEX

- 검색 속도 향상을 위한 자료구조. 더 빨리 찾도록 해주는 일종의 데이터베이스의 목차(책의 '목차'처럼 생각하면 안된다)

- 실제로는 B-Tree 구조

- 데이터를 정렬된 상태로 저장 -> 모든 데이터가 아닌 일부 행(row)만 조회 -> 빠른 검색 가능 

- 테이블과 별도로 저장: 디스크 공간 차지 => 불필요하게 많이 만들지 말 것 

B-tree index

 

 

 

Multi Column Index

- 조건이 여러개인 쿼리 검색시 유용

- 범위 조건 주의, 순서 주의: 범위 조건(ex. 평점 7점 이상)이 있으면 이후에 나오는 조건은 적용 X 

- ex. 개봉일이 2022년 AND 평점이 7점 이상 

 

INDEX를 마구잡이로 사용해서는 안된다 

- INDEX를 신중하게 생성해야  

- 자주 업데이트 되는 테이블에는 적합하지 않음 

 

CREATE INDEX idx_director ON movies (director);
-- director 칼럼을 기준으로 색인을 만든다 


DROP INDEX idx_director;
-- director INDEX 삭제

 

 

CREATE INDEX idx ON movies (rating, release_date, revenue);
-- rating, release_date, revenue를 기준으로 인덱스 생성
-- 생성 시 칼럼 순서 중요: 검색 시 가장 많이 사용되는 칼럼 순으로 
-- 인덱스를 정의할 때 만들어지는 칼럼 순서가 검색순서가 된다 
-- 즉, rating 조건 검색 -> release_date 조건 검색 -> revenue 조건 검색 


EXPLAIN QUERY PLAN
SELECT title FROM movies WHERE release_date=2020 AND rating>7;
-- 위에서 인덱스 정의할 때 rating -> release_date 순으로 만들었으므로 rating조건이 가장 먼저 검색됨 
-- 그런데 rating 조건이 범위 조건이어서 여기서 검색이 중단됨

 

: INDEX 생성 및 사용 방법 

 

 

 

Source: https://builtin.com/data-science/b-tree-index