Index

💡 이 내용 이후로 모든 SQL 관련 내용은 NomadCoder 강의를 듣고 정리한 글입니다.
아래의 링크에 SQL 학습에 필요한 데이터가 존재합니다.
https://nomadcoders.co/sql-masterclass

아래의 글과 함께 보시면 좋습니다.

2025.07.16 - [Programming/DataBase] - Index

Index란?

DB에서 데이터를 빨리 찾을 수 있게 데이터의 위치를 나타내는 자료구조이다.

• Index가 있어서 속도가 빨라지는 쿼리가 있고, 아닌 경우가 있으니 생각해서 사용해야 한다.
• 오히려, DB의 성능을 저하시킬 수도 있다.
  • Index의 데이터를 따로 저장해야 하므로 디스크 용량을 차지한다
  • DB에 데이터가 변경되는 Insert, Delete, Update 같은 경우 인덱스도 항상 동기화해야 하므로 인덱스도 수정이 되면서 연산이 느려진다.
  • 따라서 데이터의 변경이 잦은 Column은 인덱스를 사용하지 않는 것이 좋다

DB별 사용하는 자료구조형

구분	자료구조
SQLite	B-Tree
MySQL	B+Tree
PostrgreSQL	B-Tree

---

Index Scan

• Index를 통해 원하는 id를 찾고 해당 id를 통해 레코드를 조회할 때 table scan이 일어나지 않는다.
• 기본키와 같은 unique key를 기준으로 B+Tree 형태로 데이터를 저장하기 때문에 처음부터 1개씩 조회하는 table scan이 필요 없다.
  • 즉, Index뿐만 아니라 DB내의 모든 데이터들도 B+Tree의 형태로 저장한다.
  • PostgreSQL의 경우 다른 방식으로 데이터를 저장한다.
• Primary Key와 같은 Unique Key를 생성하지 않았을 경우 SQLite와 MySQL의 경우 rowid라는 별도의 Unique Key를 생성하고 이를 기준으로 자료구조를 생성한다.

-- SQLite의 경우 rowid를 입력하면 테이블 생성 시 설정하지 않았음에도 출력이 된다.
-- MySQL의 경우 rowid는 내부적으로만 사용되므로 접근하거나 출력할 방법이 없다.
SELECT *, rowid FROM movies

---

Index 생성

CREATE INDEX {index_name} ON {table_name} (column_name)

인덱스의 Leaf_Node에는 director의 이름과 해당 director가 포함된 열의 기본키들도 저장되어 있다.

-- 인덱스 이름을 정할 때 idx_{column_name} 이런식으로 설정하면 편하다.
CREATE INDEX idx_director ON movies (director);

A라는 감독이 제작한 영화의 기본키가 1, 3, 5일 경우 → movie_id : 1, 3, 5 - director : A

---

Index 삭제

DROP INDEX {index_name}

DROP INDEX idx_director;

---

Multi Column Index

자주 사용하는 다중 조건이 존재하는 경우도 Multi Column Index를 사용하여 빠르게 조회할 수 있다.

SELECT
    title
FROM
    movies
WHERE
    -- release_date와 rating 두개를 기준으로 Index를 생성할 수 있다. 
    release_date = 2022
    AND rating > 7;

CREATE INDEX idx_release_rating ON movies (release_date, rating);

---

주의점

자주 사용하는 Column을 제일 앞에 두어야 한다.

CREATE INDEX {index_name} ON {table_name} (column_name A, column_name B, …)

• 범위 조건이 나올 경우 그 이후의 조건들에 대해서는 Index Scan을 하지 않는다.
• 만약 B 컬럼, C 컬럼에 대해서 조건을 설정할 경우 인덱스를 사용하지 않는다.
  • A, B, C 또는 A, B처럼 순서대로 제일 앞 Column이 포함되어야 Index Scan을 한다.
  • A 컬럼, C 컬럼에 대해 조건을 설정할 경우 A에 대한 Index Scan만 한다.

SELECT
    title
FROM
    movies
WHERE
    -- 이렇게 부등호로 조건을 정할 경우 
    -- index를 생성할 때 release_date를 rating보다 먼저 작성했으므로 
    -- release_date에 대해서만 Index Scan을 한다.
    -- rating은 Index Scan을 하지 않는다.
    release_date > 2022 
    AND rating > 7;

-- 이런 식으로 rating이 먼저 올 경우 rating에 대해서만 Index Scan을 실행한다.
CREATE INDEX idx_release_rating ON movies (rating, release_date);

---

Covering Index

Query의 요구사항을 완벽히 만족시키는 Index

SELECT
    title
FROM
    movies
WHERE
    rating > 7;

아래의 인덱스는 Covering Index가 아니다.

CREATE INDEX idx ON movies (rating);

아래의 인덱스는 Covering Index이다.

-- WHERE절의 rating과 SELECT절의 title을 아래의 index만으로 조회할 수 있다.
CREATE INDEX idx ON movies (rating, title);

---

언제 Index를 사용해야 되고 사용하면 안 될까?

• 사용하면 좋은 경우
  • WHERE, ORDER BY, JOIN에서 자주 사용하는 Column이 있을 때
  • Unique 값을 가지는 Column이 있을 경우
  • 테이블의 크기가 매우 클 경우
  • 외래키가 있을 경우
• 사용하면 안 되는 경우
  • INSERT, UPDATE, DELETE가 자주 발생하는 Column일 경우 Index를 지양할 것
    • Index는 main Table과 동기화해야 하므로 성능이 하락된다.
  • 자주 변경되는 Column인 경우
  • 작은 규모의 table인 경우
• 신중히 사용해야 하는 경우
  • Index를 많이 생성할 경우 디스크의 용량을 잡아먹는다.
  • 개발 초기부터 Index를 사용하지 말자
    • 개발 도중 조회가 느려지는 것을 확인하면 그때 사용해도 무관
  • 여러 열을 함께 필터링하거나 정렬하는 경우
  • 거대한 Index를 만들 필요가 없을 경우
    • Covering Index를 사용해 보는 것도 나쁘지 않다.
  • 매우 긴 텍스트인 경우 (영화 개요, 줄거리 등) B-Tree 보단 full-text 형식의 index 사용
    • index의 자료구조를 변경하는 옵션은 SQLite에선 미지원

---

Full text Index

영화 줄거리 같이 매우 긴 텍스트를 빠르게 탐색할 때 사용하는 Index

---

Full Text Index 생성

CREATE FULLTEXT INDEX {index_name} ON {table_name} (column_name);

CREATE FULLTEXT INDEX idx_overview ON movies.movies (overview);

---

Full Text Index를 사용한 조회

Natural Language Mode Search

MATCH AGAINST 안의 단어와 연관성이 높은 레코드들을 찾는 방법

SELECT * FROM {table_name} WHERE MATCH(column_name) AGAINST( query )

SELECT
    title,
    overview,
    -- 해당 레코드와 'the food'라는 단어 사이의 연관성을 출력
    MATCH(overview) AGAINST('the food') as score  
FROM movies
-- IN NATURAL LANGUAGE MODE는 디폴트라 입력하지 않아도 된다.
WHERE MATCH(overview) AGAINST('the food' IN NATURAL LANGUAGE MODE);

불용어

natural language로 조회할 때 무시하는 단어들

a	about	an	are
as	at	be	by
com	de	en	for
from	how	i	in
is	it	la	of
on	or	that	the
this	to	was	what
when	where	who	will
with	und	the	www

Boolean Mode Search

특정 단어의 존재 여부를 위주로 조회할 때 사용

SELECT * FROM {table_name} WHERE MATCH(column_name) AGAINST( query IN BOOLEAN MODE)

IN BOOLEAN MODE을 붙이면 Boolean Mode가 된다.

SELECT
    title,
    overview,
    MATCH(overview)
          AGAINST('+"overseas travel" food >asia <europe -violence' IN BOOLEAN MODE)
        as score
FROM
    movies
WHERE
    MATCH(overview)
          AGAINST('+"overseas travel" food >asia <europe -violence' IN BOOLEAN MODE);

옵션 설명 예시

종류	설명	예시
단어	해당 단어가 레코드에 있어도 되고 없어도 됨	food
+단어	해당 단어가 레코드에 무조건 존재해야 함	+world
-단어	해당 단어가 레코드에 존재해서는 안됨	-kill
>단어	해당 단어의 가중치(중요도)를 올림	>asia
<단어	해당 단어의 가중치(중요도)를 낮춤	<europe
“단 어”	찾고자 하는 단어에 띄어쓰기가 되어있을 때 사용	“overseas travel”
(단어 단어)	입력한 2개의 단어 중 하나는 반드시 존재해야 함	(action drama)
글자*	입력한 글자로 시작하는 단어가 존재해야 함	cool*
“단어 단어”@n	두 단어 사이에 n개 이하의 단어가 존재해야 함	“food romance”@9
~단어	해당 단어의 연관성 점수를 낮춘다	~apple

Query Expansion Mode Search

특정 단어로 조회를 한 결과와 연관성이 높은 데이터를 한번 더 조회를 한다.

SELECT
    title,
    overview,
    MATCH(overview) AGAINST('kimchi' WITH QUERY EXPANSION) as score
FROM movies
WHERE
    MATCH(overview) AGAINST('kimchi' WITH QUERY EXPANSION);