[Explained] Како направити индекс базе података у СКЛ-у

by
Tweet
Share
Share
Pin

Желите да убрзате упите у бази података? Научите како да креирате индекс базе података користећи СКЛ и оптимизујете перформансе упита—и убрзате преузимање података.

Када преузимате податке из табеле базе података, чешће ћете морати да филтрирате на основу одређених колона.

Претпоставимо да напишете СКЛ упит за преузимање података на основу специфичних услова. Подразумевано, покретање упита покреће скенирање целе табеле док се не пронађу сви записи који задовољавају услов, а затим враћа резултате.

Ово може бити изузетно неефикасно када морате да питате велику табелу базе података са неколико милиона редова. Такве упите можете убрзати креирањем индекса базе података.

Шта је индекс базе података?

Када желите да пронађете одређени термин у књизи, да ли ћете скенирати целу књигу – једну страницу за другом – тражећи одређени термин? Па, не знаш.

Уместо тога, потражићете индекс да бисте сазнали које странице упућују на тај термин и скочићете директно на те странице. Индекс у бази података функционише слично као индекси у књизи.

Индекс базе података је скуп показивача или референци на стварне податке, али сортиран на начин који убрзава проналажење података. Интерно, индекс базе података се може имплементирати користећи структуре података као што су Б+ ​​стабла и хеш табеле. Стога, индекс базе података побољшава брзину и ефикасност операција преузимања података.

Креирање индекса базе података у СКЛ-у

Сада када знамо шта је индекс базе података и како може да убрза преузимање података, хајде да научимо како да креирамо индекс базе података у СКЛ-у.

  Значајна надоградња са Фрео-а

Када обављате операције филтрирања — навођењем услова преузимања помоћу клаузуле ВХЕРЕ — можда ћете желети да постављате упите за одређену колону чешће од других.

CREATE INDEX index_name ON table (column)

овде,

  • индек_наме је име индекса који се креира
  • табела се односи на табелу у релационој бази података
  • колона се односи на име колоне у табели базе података на којој треба да креирамо индекс.

Такође можете креирати индексе на више колона – индекс са више колона – у зависности од захтева. Ево синтаксе да то урадите:

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

Сада пређимо на практичан пример.

Разумевање повећања перформанси индекса базе података

Да бисмо разумели предност креирања индекса, потребно је да креирамо табелу базе података са великим бројем записа. Примери кода су за СКЛите. Али можете користити и друге РДБМС по свом избору, као што су ПостгреСКЛ и МиСКЛ.

Попуњавање табеле базе података записима

Такође можете користити Питхон-ов уграђени насумични модул за креирање и уметање записа у базу података. Међутим, ми ћемо користити Факер да попуни табелу базе података са милион редова.

Следећа Питхон скрипта:

  • Креира и повезује се са базом података цустомер_дб.
  • Направите табелу купаца са пољима: име, презиме, град и број наруџби.
  • Генерише синтетичке податке и убацује податке – милион записа – у табелу купаца.

Такође можете пронаћи код на ГитХуб-у.

# 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()

Сада можемо почети да постављамо упите.

  12 мало познатих Мицрософт ОнеНоте функција које ће вам се свидети

Креирање индекса на градској колони

Претпоставимо да желите да добијете информације о клијентима филтрирањем на основу колоне града. Ваш СЕЛЕЦТ упит ће изгледати овако:

SELECT column(s) FROM customers
WHERE condition;

Дакле, хајде да направимо цити_идк у колони град у табели купаца:

CREATE INDEX city_idx ON customers (city);

⚠ Креирање индекса траје незанемарљиво време и једнократна је операција. Али предности перформанси када вам је потребан велики број упита – филтрирањем по колони града – биће значајне.

Брисање индекса базе података

Да бисте избрисали индекс, можете користити наредбу ДРОП ИНДЕКС на следећи начин:

DROP INDEX index_name;

Поређење времена упита са и без индекса

Ако желите да покренете упите у оквиру Питхон скрипте, можете да користите подразумевани тајмер да бисте добили времена извршавања за упите.

Алтернативно, можете покренути упите користећи склите3 клијент командне линије. Да бисте радили са цустомер_дб.дб користећи клијент командне линије, покрените следећу команду на терминалу:

$ sqlite3 customer_db.db;

Да бисте добили приближна времена извршавања, можете користити .тимер функционалност уграђену у склите3 на следећи начин:

sqlite3 > .timer on
        > <query here>

Пошто смо направили индекс на колони град, упити који укључују филтрирање на основу колоне града у клаузули ВХЕРЕ биће много бржи.

Прво, покрените упите. Затим креирајте индекс и поново покрените упите. Забележите време извршења у оба случаја. Ево неколико примера:

КуериТиме без ИндекТиме са ИндекСЕЛЕЦТ * ФРОМ купаца
ГДЈЕ град КАО ‘Нови%’
ЛИМИТ 10;0,100 с0,001 сСЕЛЕЦТ * ФРОМ купаца
ВХЕРЕ цити=’Нев Веслеи’;0.148 с0.001 сСЕЛЕЦТ * ФРОМ купаца
ВХЕРЕ цити ИН (‘Нев Веслеи’, ‘Нев Стевен’, ‘Нев Царменмоутх’);0,247 с0,003 с

Видимо да су времена преузимања са индексом неколико редова бржа од оних без индекса у колони града.

  Како да користите Зовину обуку за зону откуцаја срца да сагорете више калорија

Најбоље праксе за креирање и коришћење индекса база података

Увек треба да проверите да ли је добит у перформансама већа од трошкова креирања индекса базе података. Ево неколико најбољих пракси које треба имати на уму:

  • Изаберите праве колоне да бисте направили индекс. Избегавајте креирање превише индекса због великих трошкова.
  • Сваки пут када се индексирана колона ажурира, одговарајући индекс такође треба да се ажурира. Дакле, креирање индекса базе података (иако убрзава преузимање) значајно успорава уметање и операције ажурирања. Због тога би требало да креирате индексе за колоне које се често питају, али се ретко ажурирају.

Када не би требало да правите индекс?

До сада би требало да имате идеју када и како да креирате индекс. Али хајде да наведемо и када индекс базе података можда неће бити потребан:

  • Када је табела базе података мала и не садржи велики број редова, скенирање целе табеле за преузимање података није тако скупо.
  • Немојте креирати индексе на колонама које се ретко користе за проналажење. Када креирате индексе на колонама које се не питају често, трошкови креирања и одржавања индекса су већи од повећања перформанси.

Сумирајући

Хајде да прегледамо шта смо научили:

  • Када постављате упите бази података да бисте преузели податке, можда ћете морати чешће да филтрирате на основу одређених колона. Индекс базе података на тако често постављаним колонама може побољшати перформансе.
  • Да бисте креирали индекс на једној колони, користите синтаксу: ЦРЕАТЕ ИНДЕКС индек_наме ОН табле (колона). Ако желите да креирате индекс са више колона, користите: ЦРЕАТЕ ИНДЕКС индек_наме ОН табеле (колона_1, колона_2,…,колона_к)
  • Кад год се индексирана колона модификује, одговарајући индекс такође треба да се ажурира. Према томе, изаберите праве колоне – које се често постављају и које се много ређе ажурирају – да бисте креирали индекс.
  • Ако је табела базе података релативно мања, трошкови креирања, одржавања и ажурирања индекса биће већи од повећања перформанси.

У већини модерних система за управљање базама података постоји оптимизатор упита који проверава да ли ће индекс на одређеној колони убрзати покретање упита. Затим, хајде да научимо најбоље праксе за дизајн базе података.