частина 11

Частина 11.7: Індекси в базах даних SQLite. Індексація таблиць в SQLite3. Алгоритм B-дерева в базах даних

Звичайно, індекси в базах даних реалізовані більш складними алгоритмами, одним з найпопулярніших і часто використовуваних алгоритмів для індексів в реляційних базах даних є алгоритм роботи з B-деревами. Він отримав таке велике поширення за рахунок своєї надійності, ефективності і відносну простоту, в цьому ми з вами зможемо переконатися. Індекси в базах даних SQLite будуються на основі алгоритму B-дерева.

Для чого потрібні індекси в базах даних SQlite3

Індекси ми віднесли до теми забезпечення цілісності даних в базах даних тільки через те, що стовпець PRIMARY KEY є одночасно і обмеженням рівня таблиці. і індексом таблиці будь-якої реляційної СУБД. в тому числі і SQLite3. Індекси в базах даних SQLite є об'єктами бази даних. це означає, що ім'я індексу повинно бути унікальним у всій базі даних.

Так як індекс - цьої об'єкт бази даних, то до нього можуть бути застосовані різні SQL команди. Наприклад, команди визначення даних потрібні для створення і видалення індексів в базі даних, в SQLite до індексів можна застосувати команди маніпуляції даними. хоча ми з легкістю можемо застосовувати ці команди до таблиць, стовпці яких є індексами.

Основне призначення індексів в базі даних полягає в тому, щоб прискорити операцію вибірки даних з бази даних. Індекси дуже прискорюють виконання команд SELECT за рахунок того, що реалізовані вони у вигляді B-дерев. але про це ми поговоримо трохи нижче. Але, оскільки індекси прискорюють вибірку, вірніше тому, що вони реалізовані у вигляді B-дерева, індекси дуже сповільнюють інші операції маніпуляції даними:

Це відбувається тому, що ці операції вносять зміни в B-дерево, яке є збалансованим, щоб підтримувати B-дерево в актуальному стан SQLite проводить перерахунок вузлів після кожної зміни даних в таблицях.

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

Зробимо висновки: індекси в базах даних прискорюють запити на вибірку даних за рахунок зменшення операцій в порівнянні значень, але уповільнюють інші операції маніпуляції з даними.

Створення індексів в базах даних SQLite. CREATE INDEX

Індекси дуже сильно прискорюють вибірку даних з бази даних за допомогою команди SELECT, але в той же час індекси уповільнюють роботу інших операторів маніпуляції даними. Давайте подивимося, як створювати індекси для таблиць бази даних під управлінням SQLite. Хоча ми вже багато разів створювали індекси в базах даних, коли оголошували обмеження первинного ключа для стовпця - PRIMARY KEY (ключі і ключові атрибути).

Обмеження первинного ключа - це обмеження рівня таблиці, яке використовується для забезпечення цілісності даних в базах даних, але в той же час стовпець PRIMARY KEY-це індекс в базі даних, який помітно прискорює вибірку даних.

А тепер давайте розглянемо синтаксис створення індексів в базах даних SQlite3:

SQLite має гнучкий синтаксис створення індексів. щоб сказати, що ми хочемо створити об'єкт бази даних, потрібно використовувати команду CREATE. далі ми вказуємо, який об'єкт бази даних ми хочемо створити, в нашому випадку INDEX. Після чого йде необов'язкова перевірка на існування IF NOT EXISTS. Перевірка потрібна, якщо ви не впевнені в тому, що індекс існує.

Якщо ви вказали ключову фразу IF NOT EXISTS, то SQLite спершу перевірить ім'я індексу на унікальність і якщо в базі даних є індекс з таким ім'ям, то видасть попередження, а не помилку. Після перевірки йде ім'я індексу, замість якого можна використовувати кваліфікатор або повне ім'я індексу із зазначенням бази даних.

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

Після імені стовпця можна використовувати клаузулу WHERE, яка дозволяє задати певні умови для створення індексу.

Створення декількох індексів для однієї таблиці в базі даних SQLite3

Ми згадували про те, що SQLite дозволяє створювати кілька індексів для однієї таблиці в базі даних, давайте розберемося з синтаксисом створення декількох індексів для однієї таблиці, такі індекси ще називають композитними:

Оскільки індекс - це об'єкт, його ім'я має бути унікальним у всій базі даних, але бувають випадки, коли ви працюєте одночасно з декількома базами даних, тоді індекси можна видалити з використанням кваліфікатора або повного імені індексу. Конструкцією IF EXISTS можна перевірити існування індексу перед його видаленням, тоді, якщо індексу з таким ім'ям немає, SQLite видасть попередження, в іншому випадку буде помилка. Сам запит на видалення індексу починається з команди DROP. а потім йде ключова фраза INDEX, яка повідомляє SQLite3 про те, що ми хочемо видалити саме індекс, а не якийсь інший об'єкт бази даних.

Приклади використання індексів в базах даних SQLite

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

Давайте створимо таблицю в базі даних, скориставшись командою CREATE:

У нас є два варіанти переиндексации таблиць. якщо ми вказуємо ім'я таблиці, то SQLite зробить переіндексацію для всіх стовпців таблиці, які вказані, як індекс. Якщо ми робимо переіндексацію, вказуючи ім'я індексу, то значення послідовність буде видалена і сформована заново для конкретного індексу.

Коли не потрібно використовувати індекси в базах даних

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

Індекси зручні і гарні, але бувають ситуації, коли індекси не варто використовувати, наприклад:

Якщо у вас невелика база даних з невеликою кількістю рядків у таблицях, то індекси не варто використовувати, так як ніяких переваг від індексів ви не отримаєте.
Якщо у вас є таблиці, в яких ви часто проводите операції модифікації даних. то індекси не варто використовувати, так як вони дуже сильно уповільнювати операції оновлення даних, які виконуються командою UPDATE.
Якщо ви часто додаєте нові рядки в таблиці бази даних (часто використовуєте команду INSERT), то для таблиць, яким багато SQL запитів INSERT не варто створювати індекси, так як SQLite буде постійно переформовувати індексні таблиці, через що швидкість операції INSERT дуже зменшиться.
Якщо у вашій таблиці є стовпці, в яких багато NULL значень, то такі стовпці краще не використовувати, як індекси, значення NULL можуть позбавити вас тих переваг, що дають індекси в базах даних.
Якщо в таблиці є стовпці, з якими ви часто проводите операції маніпуляції даними, то не використовуйте їх, як індекси. При кожному зміна стовпця SQLite, як і будь-яка інша СУБД, буде перераховувати індекси.

Внутрішні індекси в базах даних SQLite. Стовпець ROWID в SQLite3. Структура B-дерева

У кожної таблиці бази даних SQLite за замовчуванням є внутрішній індекс, який називає ROWID. Іноді цей індекс збігається з первинним ключем таблиці PRIMARY KEY, іноді не збігається, але ROWID є у кожної таблиці. Якщо говорити просто і зрозуміло, то ROWID - це додатковий стовпець, який є у будь-якої таблиці SQLiteі є індексом для даної таблиці.

Крім того, що ROWID - це індекс, це ще один спосіб підтримання цілісності даних. Давайте розберемося з індексом ROWID в базах даних SQLite. ROWID-це 64-розрядне число, яке однозначно ідентифікую будь-який рядок в базі даних SQLite.

Особливістю баз даних SQLite є те, що всі рядки бази даних зберігаються у вигляді B-дерева (багато СУБД використовують алгоритм B-дерева). Якщо говорити коротко і абстрактно, то B-дерево - це збалансоване і дуже гіллясте дерево, яке дуже сильно прискорює вибірку даних.

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

Структура індексів в базі даних SQLite. Структура B-дерева

Ми бачимо, що структура B-дерева гілляста. кожна пелюстка дерева - це вузол, в базах даних SQLite і інших реляційних базах даних кожен вузол - це таблиця. Верхня таблиця на малюнку - це корінь B-дерева. B-дерево називають збалансованим, тому що довжина шляху від кореня до будь-яких двох вузлів одного рівня збігаються.

Корінь B-дерева - це таблиця довідник, в якій записуються посилання на інші довідники, довідники другого рівня можуть теж посилатися на інші довідники, а ті в свою чергу зберігають в собі посилання на якісь дані, насправді, глибина B-дерева може бути дуже великий (рівнів може бути багато).

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

B-дерева якраз-таки допомагають нам уникнути тупого перебору рядків і скорочують кількість перевірок у багато разів, все залежить від кількості рядків в базі даних.

Зверніть увагу: якщо стовпець PRIMARY KEY оголосити як-небудь по-іншому, то значення PRIMARY KEY і ROWID не збігатимуться. Значення стовпця ROWID ви можете змінювати за допомогою команди UPDATE, як і інші значення в таблиці бази даних. Але пам'ятайте про правила унікальності і вічності, які властиві для ключових атрибутів і необхідні для забезпечення цілісності даних в базі даних, так як PRIMARY KEY-це обмеження рівня таблиці, то ми маємо право вважати, що внутрішній індекс SQLite ROWID це обмеження рівня таблиці.

Таблиці WITHOUT ROWID в SQLite, таблиці без внутрішніх індексів

У SQLite можна створювати таблиці без стовпчика ROWID. тобто без внутрішнього індексу. Такий підхід несе незначні плюси: іноді це прискорює вибірку даних з бази даних і трохи зменшує обсяг бази даних. Якщо ви вирішили створити таблицю без ROWID в SQLite, то вам необхідно зробити дві речі: по-перше, у таблиці повинен бути обов'язково первинний ключ, по-друге, вам необхідно використовувати ключову фразу WITHOUT ROWID.

Давайте створимо таблицю без стовпчика ROWID, скориставшись конструкцією WITHOUT ROWID:

Тепер у таблиці не буде стовпчика ROWID. Ще однією особливістю таблиць WITHOUT ROWID є те, що обмеження рівня стовпчика AUTOINCREMENT працювати не буде. Первинний ключ таблиці WITHOUT ROWID ні за яких обставин не може мати значення NULL, якщо ви спробуєте додати NULL в стовпець PRIMARY KEY таблиці WITHOUT ROWID, то SQLite видасть помилку.

Через особливості роботи таблиць WITHOUT ROWID. розробники SQLiteрекомендуют використовувати такі таблиці в тих випадках, коли:

Ви використовуєте складові первинні ключі або коли первинний ключ не є цілим числом, а має інший тип даних.
Якщо вам потрібно підвищити швидкість роботи таблиці з первинним ключем INTEGER, створюйте таблицю без внутрішнього індексу, цим ви прискорите вибірку і зменшіть обсяг бази даних. Якщо таблиці має індекс ROWID і індекс у вигляді INTEGER PRIMARY KEY, то SQLIte робить перебір двома циклами: перший цикл йде по стовпцю ROWID, другий цикл йде по стовпцю INTEGER PRIMARY KEY, хоча значення цих стовпців збігаються.
Якщо в таблицях зберігаються рядки з малими значеннями, то таблиці без індексу (WITHOUT ROWID) будуть працювати трохи швидше, ніж зі стовпцем ROWID.

Якщо ви проектуєте базу даних і хочете її оптимізувати, то вам не варто замислюватися про те, які таблиці використовувати: ROWID або WITHOUT ROWID, на етапі розробки і проектування. Тести найкраще проводити після того, як база даних буде спроектована і реалізована. Оскільки ніяких відмінностей між таблицями з ROWID і без немає, за винятком тих, що описані вище, немає.

частина 11