SQL - SQLite Index

SQLite

 

 

✅ Index 구조(B+ Tree)

---

B+Tree
데이터베이스와 파일 시스템 등에서 널리 사용되는 트리 자료구조 중 하나로 키에 의해서 각각 식별되는 레코드의 효율적인 삽입, 검색과 삭제를 통해 정렬된 데이터를 표현하기 위한 트리 자료구조의 일종입니다.

 

SQLite와 MySQL 자체도 B+ Tree구조로 이루어져 있다.

 

데이터베이스 인덱스에서 사용하는 트리 구조로
데이터는 리프 노드(leaf)에만 저장하고, 내부 노드는 탐색만 담당하는 구조

 

 

index는 inserting, deleting, editing을 모두 느리게 만든다. (데이터베이스와 동기화를 유지해야 하기 때문)

변경이 잦은 열이 있다면 인덱스를 사용하는 것이 좋지 않을 수도 있다.

 

 

rowid

SQLite는 기본 제공, Primary key를 명시적으로 생성했을 시, 그 primary key를 rowid로 사용한다.
MySQL은 Primary key가 없고, table에 unique column이 없을 시에 생성한다.

 

unique

unique한 column을 생성하면 자동으로 해당 column에 index가 만들어진다.

 

 

✅ Multi Column Index

---

Multi-Column Indexes 또는 복합 인덱스는 데이터베이스 테이블에서 두 개 이상의 컬럼을 조합하여 만든 인덱스를 의미합니다. 일반적으로 하나의 컬럼만으로 인덱스를 생성하는 Single-Column Index와 달리, Multi-Column Index는 쿼리 조건에 여러 컬럼이 함께 사용될 때 성능을 더욱 최적화할 수 있습니다.

 

Multi-Column Index의 특징
1. 선두 컬럼 우선 규칙: Multi-Column Index는 정의된 컬럼의 순서대로 성능 최적화에 영향을 미칩니다. 예를 들어, INDEX(col1, col2, col3)라는 Multi-Column Index를 생성하면, 이 인덱스는 col1을 조건으로 사용하거나, col1과 col2, 또는 col1, col2, col3을 모두 조건으로 사용할 때만 유효하게 작동합니다. 하지만 col2, col3만을 조건으로 사용할 경우에는 인덱스가 적용되지 않습니다.

2. 쿼리 최적화: Multi-Column Index는 여러 컬럼이 조합된 조건으로 자주 검색되는 경우 성능을 크게 향상시킵니다. 예를 들어, WHERE col1 = 'A' AND col2 = 'B'와 같은 쿼리가 많다면 (col1, col2)로 인덱스를 생성하는 것이 좋습니다. 이렇게 하면 해당 쿼리를 처리할 때 빠르게 접근할 수 있습니다.

 

EXPLAIN QUERY PLAN
SELECT
	title
FROM
	movies
WHERE
	revenue > 100
	AND rating = 8
	AND release_date > 2020;

CREATE INDEX idx ON movies (rating, release_date, revenue);

DROP INDEX idx;

= 와 같은 조건이 아니라 범위 조건이 사용되면 다음 index는 사용되지 않기 때문에 자주 사용하거나 중요한 index를 먼저 생성해주는 것이 좋다.

 

AND로 전부 연결되어 있지 않고, 중간에 OR가 있으면 다중 index가 깨진다.
이런 경우 해결법은 2가지다.

 

1. 다중 index가 아닌 각각 index를 생성해준다.

CREATE INDEX idx_rating ON movies(rating);
CREATE INDEX idx_release ON movies(release_date);

 

2. 다중 index를 사용하면서 UNION으로 결과를 합쳐준다.

SELECT * FROM movies WHERE rating = 8
UNION
SELECT * FROM movies WHERE release_date > 2020;

 

 

 

 

✅ Covering Index

---

Multi Column Index처럼 드라마틱하진 않지만 훌륭한 성능 최적화를 제공한다.

Multi Column Index와 결합하여 사용하면 최고.

 

Covering Index란?

query의 요구사항을 완벽하게 만족시키는 Index

 

EXPLAIN QUERY PLAN
SELECT
	title
FROM
	movies
WHERE
	rating > 7;

CREATE INDEX idx ON movies (rating, title);

DROP INDEX idx;

이와 같은 경우에 query에서 사용하는 title과 rating 모두 index에 존재하기 때문에 index에서 main table로 점프할 필요가 없기 때문

과도한 사용은 업데이트나 삭제의 성능을 떨어트림

(index 또한 업데이트 해야 하기 때문)

 

 

✅ Index 사용 시기

---

1. where, order by, join 연산 시  자주 사용하는 column이 있을 때

2. 카디널리티가 높을 때

3. table이 클 때

4. Foreign keys가 있을 때

5. 일단 index 없이 모든 작업을 끝내고나서, 성능 향상이 필요하다고 느껴졌을 때

6. 다중열을 함께 필터링, 정렬하는 query에는 Multi Column Index를 사용

7. 자주 변경되는 column에는 사용하지 않는다

8. 영화 줄거리 같은 긴 글의 column이 있다면 기본값인 B+ Tree를 사용하지말고 full-text index를 사용해야 한다.

(SQLite는 지원 안 함)