Використання індексованих та неіндексованих полів

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

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

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

Ефективність запиту можна зрозуміти завдяки побудові плану запиту.

Цей план запиту можна побудувати в інструментальних засобах в графічному вигляді. Наприклад, в SQL Manager:

Теж саме можна досягти використанням команди EXPLAIN, але результат буде дещо менш візуально-зрозумілий.

EXPLAIN SELECT * FROM shipping_order_string WHERE 
shipping_order_stringid = ’2234’
LIMIT 1

Приклад поганого коду

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

Програміст вирішив порівняти дані таким SQL-запитом:

Для запита: SELECT * FROM shipping_order_string WHERE 
nomer=7288138 AND 
date=’02.11.2023 5:45:49’ AND 
owner_id=’4b816fe5-7932-11ee-a87d-5a734ee7812c’ AND 
code_kis=’50’ AND 
tovar_id=’231cef6d-ac51-11ed-a874-8db29f2f2606’ AND 
status_nomen_id=’978ed86c-afef-11e7-8d0f-917e7ad61670’ AND 
parties_nomenclature_id=’EmptyLink’ AND 
packing_nomenclature_id=’231cef6e-ac51-11ed-a874-8db29f2f2606’ AND 
container_number=’’ AND 
underdelivery_percentage=0 AND 
shelf_life_percentage=0 AND 
overdelivery_percentage=0 AND 
number_packages=0 AND number=0 AND 
ordering_props=50 AND price=0 AND 
vat_rates_id=’EmptyLink’ AND 
shipment_plan_id=’00000000-0000-0000-0000-000000000000’ AND 
date_expiration=’01.01.0001 0:00:00’ AND 
number_shipping_plan=0 AND 
batch_number=’’ AND 
set=’false’ AND 
price_internet=0 AND 
code_nomenklatura_kis=’777563’ AND 
delete_mark=’false’ 
LIMIT 1

Коли ми виконали план запитів, то побачили такий результат:

Як видно, витрати часу 925 416, що є дуже великою цифрою. Така цифра була отримана тому, що в таблиці, по якій відбувається пошук, багато записів. І цей пошук відбувається неіндексованим шляхом. Бо той перелік полів, по якому відбувається пошук, неможливо SQL серверу оптимізувати.

Програміст знає ID цієї позиції та може зробити запит більш ефективно:

SELECT * FROM shipping_order_string WHERE 
shipping_order_stringid = ’2234’
LIMIT 1

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

В результаті ми бачимо план запиту:

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

В першому варіанті витрати часу 925 416 і вони збільшуються параболічно з кількістю рядків. В другому запиті: 2,5.
Порахувати, у скільки разів більше, ви можете. В 370 тис. разів!
Перший варіант не тільки довше виконується, а ще й в стільки ж разів (в 370 тис. разів) сильніше навантажує сервер.

Ця цифра більш зрозуміла, коли перевести все на кількість серверів. Коли замість одного сервера потрібно поставити 370 тис. серверів. Замисліться над цією цифрою та витратами на задачу, яка не потрібна нікому.

Автор: Рудюк С.А. 2023. K2 Cloud ERP.