[Explained] SQL에서 데이터베이스 인덱스를 만드는 방법

작성자:

데이터베이스 쿼리 속도 향상 비법: SQL 인덱스 활용

데이터베이스 쿼리 속도를 극적으로 향상시키고 싶으신가요? 그렇다면 SQL을 이용한 데이터베이스 인덱스 생성과 쿼리 성능 최적화에 주목하세요. 이 글을 통해 데이터 검색 속도를 혁신적으로 높이는 방법을 알려드립니다.

데이터베이스 테이블에서 특정 열을 기준으로 데이터를 자주 필터링해야 할 때가 있습니다. 이 경우, 쿼리 실행 시 조건에 맞는 레코드를 찾기 위해 데이터베이스는 테이블 전체를 스캔하게 됩니다.

이러한 전체 테이블 스캔 방식은 데이터베이스가 거대해질수록, 즉 수백만 행 이상의 데이터를 처리해야 할 때 매우 비효율적입니다. 하지만 데이터베이스 인덱스를 활용하면 쿼리 처리 속도를 크게 향상시킬 수 있습니다.

데이터베이스 인덱스란 무엇일까요?

만약 책에서 특정 용어를 찾고 싶을 때, 책 전체를 한 페이지씩 샅샅이 뒤지시겠습니까? 물론 아닐 겁니다.

대신 책 뒤편의 색인을 참고하여 해당 용어가 나오는 페이지를 바로 찾아갈 것입니다. 데이터베이스 인덱스도 이와 유사하게 작동합니다. 즉, 실제 데이터에 대한 일종의 ‘지름길’을 제공합니다.

데이터베이스 인덱스는 실제 데이터를 가리키는 포인터들의 집합으로, 데이터 검색 속도를 높이는 방식으로 정렬되어 있습니다. 내부적으로는 B+ 트리나 해시 테이블 같은 자료구조를 활용하여 구현되며, 데이터 검색 작업의 속도와 효율성을 극대화합니다.

SQL을 사용한 데이터베이스 인덱스 생성 방법

이제 데이터베이스 인덱스의 개념과 데이터 검색 속도를 높이는 원리를 이해했으니, SQL을 사용해 직접 인덱스를 만들어보겠습니다.

데이터를 필터링할 때, WHERE 절에 사용되는 특정 열을 다른 열보다 더 자주 쿼리하게 됩니다. 이러한 열에 인덱스를 생성하는 것이 쿼리 성능 향상의 핵심입니다.

CREATE INDEX index_name ON table (column)

위 구문에서,

  • index_name: 생성할 인덱스의 이름입니다.
  • table: 인덱스를 생성할 테이블의 이름입니다.
  • column: 인덱스를 생성할 테이블의 열 이름입니다.

요구사항에 따라 여러 열에 대한 인덱스(다중 열 인덱스)를 생성할 수도 있습니다. 그 구문은 다음과 같습니다.

CREATE INDEX index_name ON table (column_1, column_2,...,column_k)

이제 실제 예시를 통해 인덱스 생성 과정을 좀 더 자세히 알아보겠습니다.

데이터베이스 인덱스의 성능 향상 효과 이해

인덱스 생성의 이점을 명확히 이해하기 위해, 레코드 수가 많은 데이터베이스 테이블을 만들어 보겠습니다. 아래 코드 예시는 SQLite를 기준으로 작성되었지만, PostgreSQL 및 MySQL에서도 동일한 원리가 적용됩니다.

레코드로 데이터베이스 테이블 채우기

Python의 내장 random 모듈을 사용하여 레코드를 생성할 수도 있지만, 여기서는 Faker 라이브러리를 활용하여 백만 개의 행을 가진 테이블을 생성합니다.

다음 Python 스크립트는 다음과 같은 작업을 수행합니다.

  • customer_db 데이터베이스를 생성하고 연결합니다.
  • first_name, last_name, city, num_orders 필드를 가진 customers 테이블을 생성합니다.
  • 가상 데이터를 생성하여 customers 테이블에 백만 개의 레코드를 삽입합니다.

전체 코드는 GitHub에서 확인할 수 있습니다.

# main.py
# imports
import sqlite3
from faker import Faker
import random

# connect to the db
db_conn = sqlite3.connect('customer_db.db')
db_cursor = db_conn.cursor()

# create table
db_cursor.execute('''CREATE TABLE customers (
                  id INTEGER PRIMARY KEY,
                  first_name TEXT,
                  last_name TEXT,
                  city TEXT,
                  num_orders INTEGER)''')

# create a Faker object
fake = Faker()
Faker.seed(27)

# create and insert 1 million records
num_records = 1_000_000

for _ in range(num_records):
    first_name = fake.first_name()
    last_name = fake.last_name()
    city = fake.city()
    num_orders = random.randint(0,100)
    db_cursor.execute('INSERT INTO customers (first_name, last_name, city, num_orders) VALUES (?,?,?,?)', (first_name, last_name, city, num_orders))

# commit the transaction and close the cursor and connection
db_conn.commit()
db_cursor.close()
db_conn.close()

이제 쿼리를 실행할 준비가 되었습니다.

city 열에 인덱스 생성

만약 city 열을 기준으로 고객 정보를 필터링해야 한다면, 다음과 같은 SELECT 쿼리를 사용할 수 있습니다.

SELECT column(s) FROM customers
WHERE condition;

이제 customers 테이블의 city 열에 city_idx라는 이름의 인덱스를 생성해 보겠습니다.

CREATE INDEX city_idx ON customers (city);

⚠️ 인덱스 생성 작업은 시간이 다소 소요될 수 있으며, 한 번만 수행하면 됩니다. 하지만 쿼리가 빈번하게 실행될 때, city 열을 필터링하는 쿼리의 성능 향상 효과는 매우 클 것입니다.

데이터베이스 인덱스 삭제

인덱스를 삭제하려면 다음 DROP INDEX 구문을 사용할 수 있습니다.

DROP INDEX index_name;

인덱스 유무에 따른 쿼리 시간 비교

Python 스크립트 내에서 쿼리를 실행하여 실행 시간을 측정할 수도 있지만, 여기서는 sqlite3 명령줄 클라이언트를 사용하여 쿼리를 실행하는 방법을 보여드리겠습니다. customer_db.db 데이터베이스를 사용하려면 터미널에서 다음 명령을 실행하세요.

$ sqlite3 customer_db.db;

대략적인 실행 시간을 측정하기 위해 sqlite3에 내장된 .timer 기능을 활용할 수 있습니다.

sqlite3 > .timer on
        > <query here>

city 열에 인덱스를 생성했으므로, WHERE 절에서 city 열을 기준으로 필터링하는 쿼리 실행 속도가 크게 향상됩니다.

우선 인덱스가 없는 상태에서 쿼리를 실행하고, 그 다음 인덱스를 생성한 후 다시 쿼리를 실행하여 실행 시간을 비교해 보겠습니다. 다음은 몇 가지 예시입니다.

쿼리 인덱스 없음 인덱스 있음
SELECT * FROM customers WHERE city LIKE ‘신규%’ LIMIT 10; 0.100 s 0.001 s
SELECT * FROM customers WHERE city=’뉴 웨슬리’; 0.148 s 0.001 s
SELECT * FROM customers WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’); 0.247 s 0.003 s

인덱스를 사용했을 때 검색 시간이 인덱스가 없을 때보다 훨씬 빠르다는 것을 확인할 수 있습니다.

데이터베이스 인덱스 생성 및 사용 모범 사례

인덱스를 생성함으로써 얻는 성능 향상 효과가 인덱스 생성 및 관리 비용보다 큰지 항상 고려해야 합니다. 다음은 인덱스 사용 시 유의해야 할 몇 가지 모범 사례입니다.

  • 인덱스를 생성할 때 올바른 열을 선택해야 합니다. 너무 많은 인덱스를 생성하면 오히려 성능 저하를 초래할 수 있습니다.
  • 인덱스된 열이 업데이트될 때마다 해당 인덱스도 함께 업데이트되어야 합니다. 따라서 인덱스를 생성하면 검색 속도는 향상되지만, 데이터 삽입 및 업데이트 작업의 속도는 느려집니다. 빈번하게 쿼리되지만 업데이트는 거의 일어나지 않는 열에 인덱스를 생성하는 것이 좋습니다.

인덱스를 생성하지 않아야 할 경우는 언제일까요?

이제 인덱스를 생성해야 할 시점과 방법에 대해 어느 정도 감을 잡으셨을 것입니다. 하지만, 데이터베이스 인덱스가 불필요한 경우도 있습니다.

  • 데이터베이스 테이블이 작고 많은 행을 포함하고 있지 않은 경우, 전체 테이블 스캔 방식도 큰 비용을 초래하지 않습니다.
  • 검색에 거의 사용되지 않는 열에는 인덱스를 생성하지 마세요. 자주 쿼리되지 않는 열에 인덱스를 만들면, 인덱스를 유지 관리하는 비용이 성능 향상 효과보다 클 수 있습니다.

요약

오늘 배운 내용을 정리해 보겠습니다.

  • 데이터베이스 쿼리 시 특정 열을 기준으로 데이터를 자주 필터링해야 할 때가 있습니다. 이러한 열에 대한 데이터베이스 인덱스는 성능 향상에 도움이 됩니다.
  • 단일 열에 대한 인덱스를 만들려면 CREATE INDEX index_name ON table(column) 구문을 사용합니다. 다중 열 인덱스를 만들려면 CREATE INDEX index_name ON table (column_1, column_2,…,column_k)를 사용합니다.
  • 인덱스된 열이 수정될 때마다 해당 인덱스도 업데이트되어야 합니다. 따라서 자주 쿼리되지만 업데이트는 거의 일어나지 않는 열에 인덱스를 생성하는 것이 효과적입니다.
  • 데이터베이스 테이블이 상대적으로 작으면 인덱스 생성, 유지 관리, 업데이트 비용이 성능 향상 효과보다 클 수 있습니다.

대부분의 최신 데이터베이스 관리 시스템에는 특정 열에 인덱스를 추가하는 것이 쿼리 실행 속도를 높이는 데 도움이 되는지 판단하는 쿼리 최적화 기능이 내장되어 있습니다. 다음번에는 데이터베이스 설계 모범 사례에 대해 알아보겠습니다.