인덱스 - 쿼리 최적화
인덱스 - 쿼리 최적화 (Indexes - Optimize Queries)
아까 우리는 원하는 데이터가 있을 때 그 조건이 참인 행만 골라내는 법을 배웠어요. 예를 들어 히어로 이름이 "Deadpond" 인 경우처럼요.
그런데 만약 테이블과 데이터를 그냥 만들기만 하면, WHERE로 SELECT를 할 때 데이터베이스는 모든 레코드를 하나하나 훑어가면서 조건에 맞는 것들을 찾아내야 해요. 예제처럼 히어로가 3명뿐이라면 전혀 문제가 안 되죠.
하지만 데이터베이스에 수천, 수백만 개의 레코드가 있다고 상상해 보세요. 이름이 "Deadpond"인 히어로를 찾을 때마다 전부 훑어야 한다면, 너무 느려서 문제가 돼요.
이번에는 데이터베이스 인덱스(index) 로 이 문제를 해결하는 법을 보여드릴게요.
코드의 변화는 아주아주 작아요. 하지만 그 뒤에서 무슨 일이 벌어지는지 이해하는 게 유용하니까, 원리가 어떻게 동작하는지부터 차근차근 설명드릴게요.
혹시 앞선 예제를 이미 실행해서 데이터가 들어 있는 데이터베이스가 있다면, 각 예제를 실행하기 전에 데이터베이스 파일을 지워주세요. 그래야 데이터가 중복되지 않고 같은 결과를 얻을 수 있어요.
출처: 공식문서
시간이 없다면, 이렇게
이미 SQL 전문가라서 제 설명 들을 시간이 없다고요?
좋아요, 그럼 인덱스 만드는 최종 코드부터 살짝 엿보고 가셔도 돼요.
👀 전체 파일 미리 보기
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
sqlite_file_name = "database.db"
sqlite_url = f"sqlite:///{sqlite_file_name}"
engine = create_engine(sqlite_url, echo=True)
def create_db_and_tables():
SQLModel.metadata.create_all(engine)
def create_heroes():
hero_1 = Hero(name="Deadpond", secret_name="Dive Wilson")
hero_2 = Hero(name="Spider-Boy", secret_name="Pedro Parqueador")
hero_3 = Hero(name="Rusty-Man", secret_name="Tommy Sharp", age=48)
hero_4 = Hero(name="Tarantula", secret_name="Natalia Roman-on", age=32)
hero_5 = Hero(name="Black Lion", secret_name="Trevor Challa", age=35)
hero_6 = Hero(name="Dr. Weird", secret_name="Steve Weird", age=36)
hero_7 = Hero(name="Captain North America", secret_name="Esteban Rogelios", age=93)
with Session(engine) as session:
session.add(hero_1)
session.add(hero_2)
session.add(hero_3)
session.add(hero_4)
session.add(hero_5)
session.add(hero_6)
session.add(hero_7)
session.commit()
def select_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.age <= 35)
results = session.exec(statement)
for hero in results:
print(hero)
def main():
create_db_and_tables()
create_heroes()
select_heroes()
if __name__ == "__main__":
main()
...하지만 전문가가 아니라면 계속 읽어 주세요. 분명 도움이 될 거예요. 🤓
인덱스란 무엇인가
일반적으로 인덱스는 뭔가를 더 빨리 찾는 데 도움을 주는 장치예요. 보통은 사물을 정렬된 순서로 유지하는 방식으로 동작하죠. 데이터베이스나 코드를 생각하기 전에, 일상생활의 예부터 먼저 떠올려 볼게요.
인덱스와 사전
사전(dictionary) 을 상상해 보세요. 단어의 정의가 적힌 책이요. 📔 ...파이썬 dict 말고요. 😅
예를 들어 "database"라는 단어를 찾고 싶다고 해 봅시다. 사전을 들고 아무 데나, 예를 들어 중간쯤 펼쳐요. 어쩌면 m으로 시작하는 단어들(예: manual)의 정의가 보일 거예요. 그러면 지금 사전에서 m 글자 구간에 와 있다는 걸 알 수 있죠.
알파벳 순서상 database의 d는 manual의 m보다 앞에 와요.
그러니 지금 있는 지점 보다 앞쪽을 찾아야 한다는 걸 알 수 있어요. d가 사전의 정확히 어디에 있는지는 아직 모르지만, 이 지점보다 뒤는 아니라는 건 알죠. 이제 검색에서 사전의 오른쪽 절반을 버려도 돼요.
다음으로 사전을 다시 펼치는데, 이번에는 원하는 단어가 들어 있을 수 있는 절반(사전의 왼쪽 부분) 만 고려해요. 그 왼쪽 부분의 중간쯤을 펼치면 이번엔 어쩌면 f 글자에 도달할 거예요.
database의 d는 f보다 앞에 온다는 걸 알고 있으니, 그것보다 앞에 있어야 해요. 그러면서 동시에 database가 그 지점보다 뒤는 아니다라는 것도 알게 되죠. 이 지점 이후로 사전을 또 버릴 수 있어요.
이제 검색할 사전 구간이 크게 줄었어요(원하는 단어가 들어 있을 수 있는 건 사전의 1/4뿐). 그 1/4 페이지를 들고 그 구간의 중간을 펼쳐 봐요. 어쩌면 c 글자에 도달할 거예요.
database라는 단어는 그 지점보다 뒤에 있어야 하고 앞은 아니란 걸 알죠. 이 블록 페이지의 왼쪽 부분을 버릴 수 있어요.
이 과정을 몇 번 더 반복하면 마침내 d 글자에 도달해요. 그 구간 안에서 d에 대해 같은 과정을 이어가다가 결국 database라는 단어를 찾게 됩니다. 🎉
사전을 몇 번 펼쳐야 했죠. 어쩌면 5번에서 10번쯤요. 사실 원래 했어야 할 일에 비하면 엄청 적은 작업이에요.
!!! note "기술적인 세부사항"
멋진 **용어**를 좋아하세요? 좋아요! 프로그래머들은 멋진 용어를 좋아하니까요. 😅
위에서 보여드린 그 <abbr title="결과를 얻기 위한 미리 정의된 단계들의 순서·레시피">알고리즘</abbr>을 **이진 탐색(Binary Search)** 이라고 불러요.
사전(또는 정렬된 목록)을 **둘(두 조각)**("binary"는 "two"를 뜻해요)로 나눠가며 **탐색**하기 때문에 그런 이름이 붙었어요. 원하는 걸 찾을 때까지 이 과정을 여러 번 반복하는 거죠.
인덱스와 소설
이번에는 소설책을 읽고 있다고 상상해 볼게요. 누군가가 어느 지점에서 database를 언급했다고 했는데, 그 장을 찾고 싶은 거예요.
거기서 "database"라는 단어를 어떻게 찾을까요? 단어가 책의 어디쯤 있는지 알아내려면 책 전체를 읽어야 할지도 몰라요. 즉 책을 5~10번 펼치는 대신, 500페이지를 한 장씩 다 펼쳐 단어를 찾을 때까지 읽어야 하죠. 물론 재미는 있을 거예요. 😅
하지만 (SQL 데이터베이스처럼) 정보를 빨리 찾는 것에만 관심이 있다면, 500페이지를 다 읽는 건 너무 비효율적이에요. 책을 5~10군데만 펼쳐 원하는 걸 찾을 수 있는 선택지가 있는데 말이죠.
인덱스가 있는 기술 서적
이번엔 기술 서적을 읽는다고 상상해 볼게요. 프로그래밍에 관한 여러 주제가 담긴 책이요. 그리고 database에 대해 다루는 구절이 몇 군데 있어요.
이 책에는 책 인덱스(book index) 가 있을 수 있어요. 책에서 다뤄지는 주제 이름과 그 내용을 읽을 수 있는 페이지 번호가 적힌 구간이죠. 그리고 주제 이름들은 앞선 사전(단어 책)처럼 알파벳 순으로 정렬되어 있어요.
이 경우, 책의 끝(또는 시작)을 펼쳐 책 인덱스 구간을 찾으면 돼요. 겨우 몇 페이지뿐일 거예요. 그러고 나서 아까 사전 예제와 똑같은 과정을 적용하면 돼요.
인덱스를 펼쳐 5~10단계 만에 "database"라는 주제와 그 내용이 있는 페이지 번호를 찾아내요. 예를 들어 "5장 253페이지" 같은 식이죠. 이제 사전 기법으로 주제를 찾았고, 그 주제가 페이지 번호를 알려줬어요.
이제 "253페이지"를 찾아야 하는데, 닫힌 책만 봐서는 그 페이지가 어디 있는지 모르죠. 그래서 그 페이지를 찾아야 해요. 찾으려면 같은 과정을 또 쓰면 되는데, 이번엔 인덱스에서 주제를 찾는 게 아니라 책 전체에서 페이지 번호를 찾는 거예요. 5~10단계 더 거치면 5장 253페이지에 도달해요.
이렇게 하면, 이 책이 사전이 아니고 특별한 내용을 담고 있어도 몇 단계(500페이지를 다 읽는 대신 10~20단계 정도)만에 "database"를 다루는 구절을 찾을 수 있어요.
핵심은 인덱스가 정렬되어 있어서, 사전에 썼던 과정을 그대로 써서 주제를 찾을 수 있다는 거예요. 그리고 그 주제가 페이지 번호를 주는데, 페이지 번호도 정렬되어 있거든요! 😅
정렬된 목록이 있으면 똑같은 기법을 적용할 수 있어요. 이게 전부예요. 인덱스의 주제에 먼저 기법을 쓰고, 실제 장을 찾기 위해 페이지 번호에도 다시 쓰는 거죠.
와, 정말 효율적이죠? 😎
데이터베이스 인덱스란
데이터베이스 인덱스는 책 인덱스와 아주 비슷해요.
데이터베이스 인덱스는 몇몇 정보·키를 찾기 쉽고 빠르게(예: 정렬된 형태로) 저장하고, 각 키가 데이터베이스에 있는 다른 어딘가의 데이터를 가리키도록 해요.
좀 더 명확한 예를 볼게요. 데이터베이스에 이런 테이블이 있다고 해 봅시다.
| id | name | secret_name | age |
|---|---|---|---|
| 1 | Deadpond | Dive Wilson | null |
| 2 | Spider-Boy | Pedro Parqueador | null |
| 3 | Rusty-Man | Tommy Sharp | 48 |
그리고 행이 훨씬 많다고 상상해 보세요. 히어로가 아주 많아서, 아마 수천 명쯤 된다고요.
특정 이름으로 히어로를 찾으라고 SQL 데이터베이스에 말하면(SQL 쿼리의 WHERE 부분에 name을 넣는 식), 데이터베이스는 히어로를 전부 훑어 Spider-Boy라는 이름을 가진 걸 찾으려고 하나씩 검사해야 해요.
이 경우엔 한 명뿐이지만, 데이터베이스에 같은 이름의 레코드가 더 있는 걸 막을 방법은 없어요. 그래서 데이터베이스는 계속 검색하면서 레코드 하나하나를 검사해야 하고, 이는 아주 느릴 거예요.
그런데 데이터베이스가 name 열에 인덱스를 갖고 있다고 해 봅시다. 인덱스는 이런 모습일 거예요. 인덱스를 데이터베이스가 자동으로 관리하는 특별한 테이블 하나라고 상상해 볼게요.
| name | id |
|---|---|
| Deadpond | 1 |
| Rusty-Man | 3 |
| Spider-Boy | 2 |
hero 테이블의 각 name 필드가 정렬된 순서로 들어 있어요. id로 정렬된 게 아니라 name으로(문자열이니까 알파벳 순으로) 정렬된 거죠. 그래서 처음엔 Deadpond, 그다음 Rusty-Man, 마지막에 Spider-Boy가 와요. 각 히어로의 id도 함께 들어 있죠. 이게 수천 명이라 해도 마찬가지예요.
그러면 데이터베이스는 아까 사전과 책 인덱스 예제에서 썼던 아이디어를 거의 그대로 쓸 수 있어요.
어디선가(예를 들어 인덱스 중간)에서 시작할 수 있어요. 중간쯤에 도달한 히어로가 Rusty-Man이라고 해 봅시다. 인덱스는 name 필드가 정렬되어 있으니까, 데이터베이스는 이전 인덱스 행을 전부 버리고 다음 인덱스 행만 검색하면 된다는 걸 알게 돼요.
| name | id |
|---|---|
| 1 | |
| 3 | |
| Spider-Boy | 2 |
그리고 아까 사전 예제처럼, 히어로 수천 명을 읽는 대신 테이블(과 인덱스)에 수천 개 행이 있어도 데이터베이스는 몇 단계(예: 5~10단계)만에 Spider-Boy가 있는 인덱스 행에 도달할 수 있어요.
| name | id |
|---|---|
| 1 | |
| 3 | |
| ✨ Spider-Boy ✨ | 2 |
그런 다음 이 인덱스 행을 보고, hero 테이블에서 Spider-Boy의 id가 2라는 걸 알게 돼요.
그래서 그 id를 hero 테이블에서 거의 같은 기법으로 찾으면 되죠.
결국 수천 개 레코드를 읽는 대신, 데이터베이스는 몇 단계만 거쳐 원하는 히어로를 찾아내는 거예요.
인덱스 갱신
짐작하시겠지만, 이 모든 게 동작하려면 인덱스가 데이터베이스의 데이터와 최신 상태를 유지해야 해요.
만약 코드에서 수동으로 갱신해야 한다면 아주 번거롭고 오류가 잦을 거예요. 인덱스가 최신이 아니어서 잘못된 데이터를 가리키는 상태가 되기 쉬우니까요. 😱
좋은 소식이 있어요. SQL 데이터베이스에서 인덱스를 만들면, 데이터베이스가 필요할 때마다 자동으로 갱신을 처리해 줘요. 😎🎉
hero 테이블에 새 레코드를 추가하면, 데이터베이스가 인덱스를 자동으로 갱신해요. 새 인덱스 데이터가 들어갈 올바른 자리를 찾는 과정(아까 설명한 5~10단계)을 수행하고, 거기에 새 인덱스 정보를 저장하는 거죠. 데이터를 갱신하거나 삭제할 때도 마찬가지예요.
SQL 데이터베이스에서 인덱스를 정의하고 만드는 건 아주 쉬워요. 그리고 사용하는 것은 더 쉬워요... 그냥 투명하게 돌아가요. 데이터베이스가 어떤 인덱스를 쓸지 자동으로 판단하니까, SQL 쿼리는 조금도 바뀌지 않아요.
그러니까 SQL 데이터베이스에서 인덱스는 굉장해요! 그리고 쓰기도 정말 쉽고요. 그럼 뭐든 인덱스로 만들지 그러냐고요? .....왜냐하면 인덱스에도 연산과 저장 공간(디스크 공간)이라는 "비용"이 있거든요.
인덱스의 비용
인덱스에는 비용이 따라와요. 💰
인덱스가 없이 hero 테이블에 새 행을 추가하면, 데이터베이스는 테이블 끝에 새 히어로 행을 추가하는 연산 1회만 수행하면 돼요.
하지만 히어로 이름에 인덱스가 있다면, 이제 데이터베이스는 그 행을 추가하는 연산 1회에 더해, 이름이 들어갈 올바른 자리를 찾고 인덱스 레코드를 그 자리에 추가하기 위한 5~10회 추가 연산을 더 수행해야 해요.
그리고 name 인덱스, age 인덱스, secret_name 인덱스가 각각 있다면, 이제 그 행을 추가하는 연산 1회에 더해 인덱스마다 5~10회씩 × 3을 더 해야 해요. 즉 이제 행 하나를 추가하는 데 31회 정도의 연산이 필요한 셈이에요.
또 이것은 읽는 데 걸리는 시간을, 쓰는 데 걸리는 시간에다 데이터베이스의 추가 공간까지 더한 것으로 맞바꾸는 거라는 뜻이기도 해요.
각 필드로 비교하면서(예: WHERE 사용) 데이터를 꺼내는 쿼리가 있다면, 각각에 인덱스를 두는 게 완전히 말이 돼요. 데이터를 만들거나 갱신할 때 31회 연산(+ 인덱스 공간)이 드는 건, 각 필드로 비교하려고 모든 행을 읽어야 하는 500~1000회 연산보다 훨씬 낫거든요.
하지만 secret_name으로 레코드를 찾는 쿼리가 전혀 없다면(WHERE 부분에 secret_name을 쓴 적이 없다면) secret_name 필드/열에 인덱스를 두는 건 의미가 없어요. 데이터베이스에 쓰고 갱신하는 비용만 늘리니까요.
SQL로 인덱스 만들기
휴, 이론과 설명이 정말 많았죠. 😅
인덱스에서 가장 중요한 건 이해예요. 어떻게, 언제 써야 하는지를요.
이제 인덱스를 만드는 SQL 문법을 볼게요. 아주 간단해요.
CREATE INDEX ix_hero_name
ON hero (name)
얼추 이렇게 말하는 거예요:
안녕 SQL 데이터베이스 👋, 나를 위해
INDEX(인덱스)를CREATE(만들어) 줘.인덱스 이름은
ix_hero_name으로 하고 싶어.이 인덱스는
hero테이블을ON(참조)해야 해, 그 테이블을 가리키는 거야.인덱스에 쓸 열은
name이야.
SQLModel로 인덱스 선언하기
이제 SQLModel에서 인덱스를 정의하는 법을 볼게요.
코드의 변화는 정말 무색할 정도로 단순해요. 😆
아까 썼던 Hero 모델이 이랬죠:
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str
secret_name: str
age: int | None = None
이제 SQLModel에게 테이블을 만들 때 name 필드의 인덱스를 만들라고 알려주도록 바꿔 볼게요:
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = None
전처럼 똑같이 Field()를 쓰는데, index=True를 설정했죠. 그게 전부예요! 🚀
여기서 default=None 같은 인자를 설정하지 않았다는 점을 눈여겨보세요. 이는 SQLModel(사실은 Pydantic 덕분에)이 그 필드를 필수(required) 로 유지한다는 뜻이에요.
!!! note "참고"
**SQLModel**(실제로는 SQLAlchemy)이 **인덱스 이름을 자동으로 생성**해 줘요.
이 경우 생성되는 이름은 `ix_hero_name`이에요.
데이터 조회하기
이제 name 필드와 새 인덱스를 사용해서 데이터를 조회하는 건, 코드에서 특별히 다른 걸 할 필요가 없어요. 그냥 똑같은 코드예요.
SQL 데이터베이스가 자동으로 알아서 처리해 줘요. ✨
이게 좋은 이유는, 인덱스가 쓰기 아주 간단하다는 뜻이기 때문이에요. 다만 처음에는 직관에 반하는 느낌이 들 수도 있어요. 인덱스가 유용하다는 게 코드에서 명시적으로 드러나는 게 아니라, 모든 게 데이터베이스 뒤에서 일어나니까요.
def select_heroes():
with Session(engine) as session:
statement = select(Hero).where(Hero.name == "Deadpond")
results = session.exec(statement)
for hero in results:
print(hero)
이건 아까와 정확히 같은 코드지만, 이제 데이터베이스가 그 아래에서 인덱스를 사용할 거예요.
프로그램 실행하기
이제 프로그램을 실행하면 이런 출력이 보일 거예요.
$ uv run python app.py
// 부수적인 출력은 생략했어요 😉
// 테이블 만들기
CREATE TABLE hero (
id INTEGER,
name VARCHAR NOT NULL,
secret_name VARCHAR NOT NULL,
age INTEGER,
PRIMARY KEY (id)
)
// 인덱스 만들기 🤓🎉
CREATE INDEX ix_hero_name ON hero (name)
// SELECT with WHERE는 똑같아 보여요
INFO Engine SELECT hero.id, hero.name, hero.secret_name, hero.age
FROM hero
WHERE hero.name = ?
INFO Engine [no key 0.00014s] ('Deadpond',)
// 조회된 히어로
secret_name='Dive Wilson' age=None id=1 name='Deadpond'
인덱스 더 추가하기
hero 테이블에서 age 필드로도 비교해서 조회할 거라면, 그 필드에도 인덱스를 정의해 주는 게 좋아요:
from sqlmodel import Field, Session, SQLModel, create_engine, select
class Hero(SQLModel, table=True):
id: int | None = Field(default=None, primary_key=True)
name: str = Field(index=True)
secret_name: str
age: int | None = Field(default=None, index=True)
이 경우 age의 기본값이 계속 None이길 원하니까, Field()를 쓸 때 default=None을 설정했어요.
이제 SQLModel로 데이터베이스와 테이블을 만들면, hero 테이블의 이 두 열에 대한 인덱스도 함께 만들 거예요.
그래서 hero 테이블을 조회하면서 그 두 열로 어떤 데이터를 가져올지를 결정하게 되면, 데이터베이스가 이 인덱스들을 활용해서 읽기 성능을 높일 수 있어요. 🚀
기본 키와 인덱스
id 필드에는 index=True를 설정하지 않았다는 걸 눈치채셨을 거예요.
id는 이미 기본 키(primary key) 잖아요. 데이터베이스가 자동으로 내부 인덱스를 만들어 주거든요.
데이터베이스는 기본 키에 대해 항상 내부 인덱스를 자동으로 만들어요. 데이터를 정리·저장·조회하는 기본 수단이니까요. 🤓
하지만 다른 어떤 필드로 SQL 데이터베이스를 자주 조회하고 싶다면(예: WHERE 구간에 다른 필드를 쓰는 경우) 적어도 그 필드에는 인덱스를 두는 게 좋아요.
요약
인덱스는 데이터베이스를 조회할 때 읽기 성능과 속도를 높이는 데 아주 중요해요. 🏎
만들고 쓰는 것도 아주 단순하고 쉬워요. 가장 중요한 건 어떻게 동작하는지, 언제 만들어야 하는지, 어떤 열에 만들어야 하는지를 이해하는 거예요.