Хочете пришвидшити запити до бази даних? Дізнайтеся, як створити індекс бази даних за допомогою 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. Але ви також можете використовувати інші RDBMS на ваш вибір, такі як PostgreSQL і MySQL.
Заповнення таблиці бази даних записами
Ви також можете використовувати вбудований довільний модуль Python для створення та вставлення записів у базу даних. Однак ми скористаємося Факер щоб заповнити таблицю бази даних мільйоном рядків.
Наступний сценарій Python:
- Створює та підключається до бази даних customer_db.
- Створіть таблицю клієнтів із полями: ім’я, прізвище, місто та кількість замовлень.
- Генерує синтетичні дані та вставляє дані — один мільйон записів — у таблицю клієнтів.
Ви також можете знайти код на 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 в таблиці клієнтів:
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, будуть набагато швидшими.
Спочатку запустіть запити. Потім створіть індекс і повторно запустіть запити. Запишіть час виконання в обох випадках. Ось кілька прикладів:
QueryTime без IndexTime з IndexSELECT * FROM клієнтів
ДЕ місто ЯК “Новий%”
LIMIT 10;0,100 s0,001 sSELECT * FROM клієнтів
WHERE city=’New Wesley’;0,148 s0,001 sSELECT * FROM клієнтів
WHERE city IN (“Нью-Уеслі”, “Нью-Стівен”, “Нью-Карменмут”);0,247 с0,003 с
Ми бачимо, що час пошуку з індексом на кілька порядків швидший, ніж без індексу в стовпці міста.
Найкращі методи створення та використання індексів бази даних
Завжди слід перевіряти, чи приріст продуктивності перевищує накладні витрати на створення індексу бази даних. Ось кілька практичних порад, про які варто пам’ятати.
- Виберіть потрібні стовпці, щоб створити індекс. Уникайте створення занадто великої кількості індексів через значні накладні витрати.
- Щоразу, коли оновлюється індексований стовпець, відповідний індекс також має оновлюватися. Таким чином, створення індексу бази даних (хоча пришвидшує пошук) значно сповільнює операції вставки та оновлення. Тому ви повинні створити індекси для стовпців, які часто запитуються, але рідко оновлюються.
Коли не варто створювати індекс?
Тепер ви маєте знати, коли і як створити індекс. Але давайте також вкажемо, коли індекс бази даних може бути непотрібним:
- Коли таблиця бази даних невелика і не містить великої кількості рядків, сканування повної таблиці для отримання даних не таке дороге.
- Не створюйте індекси для стовпців, які рідко використовуються для пошуку. Коли ви створюєте індекси для стовпців, до яких не часто звертаються, вартість створення та підтримки індексу переважує підвищення продуктивності.
Підводячи підсумки
Давайте повторимо, що ми навчилися:
- Під час запиту до бази даних для отримання даних вам може знадобитися частіше фільтрувати на основі певних стовпців. Індекс бази даних для таких часто запитуваних стовпців може покращити продуктивність.
- Щоб створити індекс для одного стовпця, використовуйте синтаксис: CREATE INDEX ім’я_індексу ON таблиці (стовпця). Якщо ви хочете створити багатостовпцевий індекс, використовуйте: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
- Кожного разу, коли індексований стовпець змінюється, відповідний індекс також слід оновлювати. Тому виберіть правильні стовпці — часто запитувані та набагато рідше оновлювані — щоб створити індекс.
- Якщо таблиця бази даних відносно менша, вартість створення, підтримки та оновлення індексу буде більшою, ніж підвищення продуктивності.
У більшості сучасних систем керування базами даних є оптимізатор запитів, який перевіряє, чи прискорить виконання запиту індекс у певному стовпці. Далі, давайте дізнаємося про найкращі методи розробки бази даних.