Искате ли да ускорите заявките към базата данни? Научете как да създадете индекс на база данни с помощта на 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.
- Създайте таблица с клиенти с полетата: 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()
Сега можем да започнем да питаме.
Създаване на индекс в колоната за град
Да предположим, че искате да получите информация за клиента чрез филтриране въз основа на колоната за град. Вашата 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 * ОТ клиенти
WHERE city=’New Wesley’;0.148 s0.001 sSELECT * FROM клиенти
КЪДЕ град В („Ню Уесли“, „Ню Стивън“, „Ню Карменмут“);0,247 s0,003 s
Виждаме, че времето за извличане с индекс е няколко порядъка по-бързо от тези без индекс в градската колона.
Най-добри практики за създаване и използване на индекси на бази данни
Винаги трябва да проверявате дали печалбите в производителността са по-големи от режийните разходи за създаване на индекс на база данни. Ето някои най-добри практики, които трябва да имате предвид:
- Изберете правилните колони, за да създадете индекс. Избягвайте създаването на твърде много индекси поради значителните разходи.
- Всеки път, когато се актуализира индексирана колона, съответният индекс също трябва да се актуализира. Така че създаването на индекс на база данни (въпреки че ускорява извличането) значително забавя вмъкванията и операциите за актуализиране. Следователно трябва да създадете индекси на колони, които са често запитвани, но рядко се актуализират.
Кога не трябва да създавате индекс?
Досега трябва да имате представа кога и как да създадете индекс. Но нека също да посочим кога индексът на базата данни може да не е необходим:
- Когато таблицата на базата данни е малка и не съдържа голям брой редове, сканирането на цялата таблица за извличане на данни не е толкова скъпо.
- Не създавайте индекси на колони, които рядко се използват за извличане. Когато създавате индекси на колони, които не се задават често, разходите за създаване и поддържане на индекс надхвърлят печалбите в производителността.
Обобщаване
Нека прегледаме наученото:
- Когато правите заявка към база данни за извличане на данни, може да се наложи по-често да филтрирате въз основа на определени колони. Индекс на базата данни на такива често търсени колони може да подобри производителността.
- За да създадете индекс на една колона, използвайте синтаксиса: CREATE INDEX index_name ON таблица (колона). Ако искате да създадете s многоколонен индекс, използвайте: CREATE INDEX index_name ON table (column_1, column_2,…,column_k)
- Всеки път, когато се променя индексирана колона, съответният индекс също трябва да се актуализира. Затова изберете правилните колони – често търсени и много по-рядко актуализирани – за да създадете индекс.
- Ако таблицата на базата данни е относително по-малка, разходите за създаване, поддържане и актуализиране на индекс ще бъдат по-големи от печалбите в производителността.
В повечето съвременни системи за управление на бази данни има оптимизатор на заявки, който проверява дали даден индекс на конкретна колона ще направи заявката по-бърза. След това нека научим най-добрите практики за проектиране на бази данни.