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

by

데이터베이스 쿼리 속도를 높이고 싶습니까? SQL을 사용하여 데이터베이스 인덱스를 만들고 쿼리 성능을 최적화하고 데이터 검색 속도를 높이는 방법을 알아보세요.

데이터베이스 테이블에서 데이터를 검색할 때 특정 열을 기준으로 더 자주 필터링해야 합니다.

특정 조건에 따라 데이터를 검색하는 SQL 쿼리를 작성한다고 가정합니다. 기본적으로 쿼리를 실행하면 조건을 만족하는 모든 레코드가 발견될 때까지 전체 테이블 스캔을 실행한 다음 결과를 반환합니다.

이는 수백만 개의 행이 있는 대규모 데이터베이스 테이블을 쿼리해야 하는 경우 매우 비효율적일 수 있습니다. 데이터베이스 인덱스를 생성하여 이러한 쿼리의 속도를 높일 수 있습니다.

데이터베이스 색인이란 무엇입니까?

책에서 특정 용어를 찾고 싶을 때 책 전체를 한 페이지씩 스캔하여 특정 용어를 찾으시겠습니까? 글쎄요.

대신 색인을 조회하여 용어를 참조하는 페이지를 찾고 해당 페이지로 바로 이동합니다. 데이터베이스의 색인은 책의 색인과 매우 유사하게 작동합니다.

데이터베이스 인덱스는 실제 데이터에 대한 포인터 또는 참조 집합이지만 데이터 검색 속도를 높이는 방식으로 정렬됩니다. 내부적으로 B+ 트리 및 해시 테이블과 같은 데이터 구조를 사용하여 데이터베이스 인덱스를 구현할 수 있습니다. 따라서 데이터베이스 인덱스는 데이터 검색 작업의 속도와 효율성을 향상시킵니다.

SQL에서 데이터베이스 인덱스 생성

이제 데이터베이스 인덱스가 무엇이며 데이터 검색 속도를 높일 수 있는 방법을 알았으므로 SQL에서 데이터베이스 인덱스를 만드는 방법을 알아보겠습니다.

WHERE 절을 사용하여 검색 조건을 지정하여 필터링 작업을 수행할 때 특정 열을 다른 열보다 더 자주 쿼리할 수 있습니다.

CREATE INDEX index_name ON table (column)

여기,

  • index_name은 생성할 인덱스의 이름입니다.
  • 테이블은 관계형 데이터베이스의 테이블을 참조합니다.
  • column은 인덱스를 생성해야 하는 데이터베이스 테이블의 열 이름을 나타냅니다.

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

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

이제 실용적인 예를 살펴보겠습니다.

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

인덱스 생성의 이점을 이해하려면 레코드 수가 많은 데이터베이스 테이블을 생성해야 합니다. 코드 예제는 SQLite. 그러나 PostgreSQL 및 MySQL.

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

또한 Python의 내장 random 모듈을 사용하여 레코드를 생성하고 데이터베이스에 삽입할 수 있습니다. 그러나 우리는 위조자 백만 개의 행으로 데이터베이스 테이블을 채웁니다.

다음 Python 스크립트:

  • customer_db 데이터베이스를 생성하고 연결합니다.
  • first_name, last_name, city 및 num_orders 필드가 있는 고객 테이블을 만듭니다.
  • 합성 데이터를 생성하고 고객 테이블에 데이터(백만 개의 레코드)를 삽입합니다.

당신은 또한 코드를 찾을 수 있습니다 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 열에 인덱스 생성

도시 열을 기준으로 필터링하여 고객 정보를 얻고 싶다고 가정합니다. SELECT 쿼리는 다음과 같습니다.

SELECT column(s) FROM customers
WHERE condition;

따라서 고객 테이블의 도시 열에 city_idx를 생성해 보겠습니다.

CREATE INDEX city_idx ON customers (city);

⚠ 인덱스 생성에는 무시할 수 없는 시간이 소요되며 일회성 작업입니다. 그러나 많은 수의 쿼리가 필요할 때 도시 열을 필터링하여 성능상의 이점이 상당할 것입니다.

데이터베이스 색인 삭제

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

DROP INDEX index_name;

인덱스가 있는 경우와 없는 경우의 쿼리 시간 비교

Python 스크립트 내에서 쿼리를 실행하려는 경우 기본 타이머를 사용하여 쿼리 실행 시간을 얻을 수 있습니다.

또는 sqlite3 명령줄 클라이언트를 사용하여 쿼리를 실행할 수 있습니다. 명령줄 클라이언트를 사용하여 customer_db.db로 작업하려면 터미널에서 다음 명령을 실행합니다.

$ sqlite3 customer_db.db;

대략적인 실행 시간을 얻으려면 다음과 같이 sqlite3에 내장된 .timer 기능을 사용할 수 있습니다.

sqlite3 > .timer on
        > <query here>

도시 열에 인덱스를 만들었기 때문에 WHERE 절의 도시 열을 기반으로 필터링하는 쿼리가 훨씬 빠릅니다.

먼저 쿼리를 실행합니다. 그런 다음 인덱스를 만들고 쿼리를 다시 실행합니다. 두 경우 모두 실행 시간을 기록해 둡니다. 여기 몇 가지 예가 있어요.

IndexSELECT가 있는 IndexTime이 없는 QueryTime * FROM 고객
WHERE city LIKE ‘신규%’
LIMIT 10;0.100 s0.001 sSELECT * 고객으로부터
WHERE city=’뉴 웨슬리’;0.148 s0.001 sSELECT * 고객으로부터
WHERE city IN (‘New Wesley’, ‘New Steven’, ‘New Carmenmouth’);0.247 s0.003 s

인덱스가 있는 검색 시간이 도시 열에 인덱스가 없는 것보다 몇 배 더 빠르다는 것을 알 수 있습니다.

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

성능 향상이 데이터베이스 인덱스 생성 오버헤드보다 큰지 항상 확인해야 합니다. 다음은 염두에 두어야 할 몇 가지 모범 사례입니다.

  • 인덱스를 생성하려면 올바른 열을 선택하십시오. 상당한 오버헤드 때문에 인덱스를 너무 많이 생성하지 마십시오.
  • 인덱스 열이 업데이트될 때마다 해당 인덱스도 업데이트되어야 합니다. 따라서 데이터베이스 인덱스를 생성하면(검색 속도는 빨라지지만) 삽입 및 업데이트 작업 속도가 상당히 느려집니다. 따라서 자주 쿼리되지만 거의 업데이트되지 않는 열에 인덱스를 만들어야 합니다.

인덱스를 생성하면 안 되는 경우는 언제입니까?

지금쯤이면 색인을 생성하는 시기와 방법에 대한 아이디어가 있어야 합니다. 그러나 데이터베이스 인덱스가 필요하지 않은 경우도 설명하겠습니다.

  • 데이터베이스 테이블이 작고 많은 수의 행을 포함하지 않는 경우 데이터를 검색하기 위한 전체 테이블 스캔은 비용이 많이 들지 않습니다.
  • 검색에 거의 사용되지 않는 열에 인덱스를 생성하지 마십시오. 자주 쿼리되지 않는 열에 인덱스를 만들면 인덱스를 만들고 유지 관리하는 비용이 성능 향상보다 큽니다.

합산

배운 내용을 복습해 보겠습니다.

  • 데이터를 검색하기 위해 데이터베이스를 쿼리할 때 특정 열을 기반으로 더 자주 필터링해야 할 수 있습니다. 이러한 자주 쿼리되는 열에 대한 데이터베이스 인덱스는 성능을 향상시킬 수 있습니다.
  • 단일 열에 인덱스를 만들려면 CREATE INDEX index_name ON table(column) 구문을 사용합니다. 다중 열 인덱스를 생성하려면 다음을 사용하십시오. CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
  • 인덱스 컬럼이 수정될 때마다 해당 인덱스도 업데이트 되어야 합니다. 따라서 자주 쿼리되고 업데이트 빈도가 훨씬 낮은 올바른 열을 선택하여 인덱스를 생성하십시오.
  • 데이터베이스 테이블이 상대적으로 작으면 인덱스 생성, 유지 관리 및 업데이트 비용이 성능 향상보다 커집니다.

대부분의 최신 데이터베이스 관리 시스템에는 특정 열의 인덱스가 쿼리 실행 속도를 높이는지 확인하는 쿼리 최적화 프로그램이 있습니다. 다음으로 데이터베이스 설계에 대한 모범 사례를 알아보겠습니다.