[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 для генерації та вставки записів до бази даних. Але ми використаємо бібліотеку Faker для заповнення таблиці бази даних мільйоном рядків.

Наступний сценарій Python:

Створює та встановлює з’єднання з базою даних customer_db.
Створює таблицю 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()

Тепер можна починати виконувати запити.

Створення індексу для стовпця міста

Припустімо, ви хочете отримати інформацію про клієнта, фільтруючи її за стовпцем міста. Ваш SELECT-запит буде виглядати наступним чином:

SELECT column(s) FROM customers
WHERE condition;

Тому, давайте створимо індекс city_idx для стовпця city таблиці customers:

CREATE INDEX city_idx ON customers (city);

⚠ Створення індексу може зайняти певний час, але це одноразова операція. Покращення продуктивності будуть значними, особливо при виконанні великої кількості запитів з фільтрацією за стовпцем міста.

Видалення індексу бази даних

Для видалення індексу можна скористатися оператором DROP INDEX наступним чином:

DROP INDEX index_name;

Порівняння часу виконання запитів з індексом та без нього

Якщо ви бажаєте запускати запити зі сценарію Python, ви можете використовувати стандартний таймер для вимірювання часу виконання запитів.

Крім того, ви можете запускати запити, використовуючи командний рядок sqlite3. Для роботи з customer_db.db через командний рядок, виконайте наступну команду в терміналі:

$ sqlite3 customer_db.db;

Щоб отримати приблизний час виконання, ви можете скористатися функцією .timer, вбудованою в sqlite3, наступним чином:

sqlite3 > .timer on
        > <query here>

Оскільки ми створили індекс для стовпця міста, запити, що включають фільтрацію за цим стовпцем у WHERE-умові, будуть виконуватися значно швидше.

Спочатку запустіть запити. Потім створіть індекс і повторно запустіть запити. Зафіксуйте час виконання в обох випадках. Ось декілька прикладів:

Запит	Час без індексу	Час з індексом
SELECT * FROM клієнтів ДЕ місто ЯК “Новий%” LIMIT 10;	0,100 с	0,001 с
SELECT * FROM клієнтів WHERE city=’New Wesley’;	0,148 с	0,001 с
SELECT * FROM клієнтів WHERE city IN (“Нью-Уеслі”, “Нью-Стівен”, “Нью-Карменмут”);	0,247 с	0,003 с

Як бачимо, час пошуку з індексом на кілька порядків менший, ніж без індексу для стовпця міста.

Коли не варто створювати індекс?

Тепер ви знаєте, як і коли створювати індекс. Але давайте розглянемо випадки, коли індекс бази даних може бути недоцільним:

Коли таблиця бази даних невелика і не містить великої кількості рядків, сканування всієї таблиці для пошуку даних не є затратним.
Не створюйте індекси для стовпців, які рідко використовуються для пошуку. Коли ви створюєте індекси для стовпців, до яких звертаються рідко, вартість створення та підтримки індексу може перевищити потенційне підвищення продуктивності.

Підсумки

Давайте підсумуємо основні моменти:

При запиті до бази даних для отримання даних вам може знадобитися частіше фільтрувати їх за певними стовпцями. Індекс бази даних для таких часто запитуваних стовпців може значно підвищити продуктивність.
Щоб створити індекс для одного стовпця, використовуйте синтаксис: CREATE INDEX ім’я_індексу ON таблиці (стовпця). Якщо ви хочете створити багатостовпцевий індекс, використовуйте: CREATE INDEX index_name ON table (column_1, column_2,…,column_k).
Кожен раз, коли індексований стовпець змінюється, відповідний індекс також необхідно оновити. Тому обирайте правильні стовпці для індексування – ті, які часто запитуються, але набагато рідше оновлюються.
Якщо таблиця бази даних відносно мала, витрати на створення, підтримку та оновлення індексу можуть перевищити підвищення продуктивності.

Більшість сучасних систем керування базами даних мають оптимізатор запитів, який аналізує, чи прискорить виконання запиту індекс у певному стовпці. Далі, розглянемо найкращі практики розробки баз даних.