Ось готовий урок, написаний у стилі CS50, спеціально для твого запиту.

🎓 CS50: Профілювання та аналіз SQL-запитів

(Lesson 0x05: Looking under the hood)

Привіт, друзі! Це CS50! 👋

Сьогодні ми не просто пишемо код, який працює. Ми вчимося писати код, який літає.

Уявіть ситуацію. Ви запустили свій стартап — інтернет-магазин кросівок. Спочатку у вас 100 користувачів, і все працює миттєво. Ви щасливі. Але раптом — успіх! У вас 100,000 користувачів. І що відбувається?

Клієнт вводить у пошук "Nike Air Jordan", натискає Enter... і чекає. Секунду. Дві. П'ять. Колесо крутиться. Клієнт дратується і йде до конкурента. 📉

Чому так сталося? Ваш SQL-запит правильний. Він повертає вірні дані. Але він робить це надто повільно.

Сьогодні ми відкриємо капот нашої бази даних. Ми візьмемо в руки "рентгенівський апарат" для SQL — команду EXPLAIN ANALYZE. Ми навчимося бачити, як база даних думає, і як змусити її думати швидше.

Готові? Поїхали! 🚀

1. 🔥 Вступ: Проблема голки в копиці сіна

Уявіть, що я даю вам телефонний довідник Нью-Йорка (пам'ятаєте такі товсті жовті книги?) і прошу знайти номер "Джона Сміта".

Оскільки книга відсортована за алфавітом (прізвище -> ім'я), ви відкриваєте її десь посередині, бачите літеру "M", гортаєте далі до "S" і швидко знаходите Сміта. Це займе у вас секунд 10. Це — ефективний пошук.

А тепер уявіть, що я даю вам ту саму книгу, але прошу знайти людину, чий номер телефону закінчується на "55-12".

Книга не відсортована за номерами. Що вам доведеться робити? Вам доведеться читати кожен рядок на кожній сторінці, від першої до останньої. Це займе дні!

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

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

2. 🧠 Теоретична база: Планувальник запитів

Коли ви надсилаєте запит у PostgreSQL (або MySQL), там сидить маленький, але дуже розумний "гномик" — Query Planner (Планувальник запитів).

Він дивиться на ваш запит: SELECT * FROM orders WHERE user_id = 42;

І думає: "Так, у мене є кілька шляхів отримати ці дані..." 1. Seq Scan (Sequential Scan): Піти пішки по всій таблиці, рядок за рядком. (Надійно, але повільно для великих даних). 2. Index Scan: Подивитися в спеціальний "покажчик" (індекс), знайти, де лежить user_id = 42, і стрибнути прямо туди. (Швидко, як телепорт).

Планувальник рахує Cost (Вартість) кожного варіанту. Це умовні бали зусиль (процесор + читання з диска). Він обирає шлях з найменшою вартістю.

🔑 Що треба знати (обов'язково):

EXPLAIN: Команда, яка показує план (що база даних збирається робити).
EXPLAIN ANALYZE: Команда, яка реально виконує запит і показує, скільки часу це зайняло насправді. Використовуйте обережно на DELETE або UPDATE!
Full Table Scan (Seq Scan): Ворог продуктивності на великих таблицях. Це читання "від корки до корки".

3. 🧪 Приклади: Від повзання до польоту

Давайте подивимось на це в дії.

Уявіть таблицю users з 1 мільйоном рядків.

Приклад 1: Пошук без карти

Ми шукаємо користувача за email-ом, але ми забули створити індекс для колонки email.

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'david@harvard.edu';

Що ви очікуєте побачити? Чи знає база, де цей email? Ні.

Результат (спрощено):

Seq Scan on users  (cost=0.00..18334.00 rows=1 width=35) (actual time=0.05..150.45 rows=1)
Execution Time: 150.5 ms

👀 Аналіз: * Seq Scan: База перебрала весь мільйон рядків. * Time: 150 мілісекунд. Здається швидко? А якщо таких запитів 1000 на секунду? Сервер "ляже".

Приклад 2: Додаємо магію (Індекс)

Створюємо індекс (той самий алфавітний покажчик):

CREATE INDEX idx_users_email ON users(email);

Тепер запускаємо той самий запит:

EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'david@harvard.edu';

Результат:

Index Scan using idx_users_email on users ... (actual time=0.02..0.03 rows=1)
Execution Time: 0.05 ms

🤯 Бум! * Було: 150 ms * Стало: 0.05 ms * Прискорення у 3000 разів! * Index Scan: База пішла чітко за адресою.

4. 🛠 Практична частина

Час забруднити руки! Відкрийте термінал або PgAdmin.

Завдання 1: Перший погляд

Напишіть будь-який SELECT до вашої найбільшої таблиці. Додайте перед ним слово EXPLAIN. * Подивіться на output. Знайдіть слово cost. * Не лякайтеся цифр. Просто порівняйте: велика цифра — погано, мала — добре.

Завдання 2: "Детектив"

Спробуйте знайти запит у вашому поточному проєкті, який виконується довше 100мс. Запустіть його з EXPLAIN ANALYZE. * Чи бачите ви там Seq Scan? * Якщо так — це ваш кандидат на оптимізацію.

Завдання 3: Пастка з LIKE

Спробуйте виконати запит з пошуком частини тексту:

EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%harvard%';

Питання: Чи допоможе тут наш індекс idx_users_email?
Відповідь: Швидше за все, ні! Звичайний B-Tree індекс працює як телефонна книга: він добре шукає початок слова ('harvard%'), але не може знайти середину ('%harvard%'). Тут знову буде повне сканування.

Завдання 4: Міні-кейс

У вас є таблиця замовлень orders (id, user_id, amount, created_at). Менеджер скаржиться, що звіт за вчорашній день формується дуже довго. Запит:

SELECT * FROM orders WHERE created_at > '2023-10-26';

Ваше завдання: 1. Який індекс треба створити? 2. Напишіть команду CREATE INDEX .... 3. Перевірте через EXPLAIN, чи почав він використовуватись.

5. 💡 Мислення як у розробника

Як думає новачок?

"Запит працює? Дані повертає? Чудово, комітимо!"

Як думає сеньйор-інженер?

"Цей запит працює швидко зараз, коли в таблиці 10 рядків. А як він поведе себе, коли там буде 10 мільйонів? Яка складність алгоритму пошуку?"

⚠️ Типові помилки:

Індексувати все підряд.
- Чому це погано: Кожен індекс прискорює SELECT, але уповільнює INSERT та UPDATE. Коли ви записуєте новий рядок, базі треба оновити й саму таблицю, і всі 10 індексів до неї. Дотримуйтесь балансу.
Ігнорування типів даних.
- Порівнювати рядок з числом (user_id = '123') — це може збити планувальник з пантелику і він не використає індекс.
Оптимізація без вимірювання.
- Ніколи не кажіть "мені здається, це повільно". Кажіть: "EXPLAIN показує вартість 5000, це треба фіксити".

6. 🧩 Підсумок

Отже, що ми сьогодні зрозуміли?

SQL — це не чорна скринька. Ми можемо зазирнути всередину за допомогою EXPLAIN ANALYZE.
Seq Scan — це як читати всю бібліотеку заради однієї книги. Уникайте цього на великих даних.
Індекси — це суперсила, яка перетворює годинні очікування на мілісекунди. Але з великою силою приходить велика відповідальність (не створюйте їх бездумно!).

Тепер ви не просто пишете запити. Ви їх інжинірите.

Спойлер до наступного уроку: Ми навчилися шукати дані швидко. Але що, якщо самих даних стане настільки багато, що вони не влізуть на один сервер? На наступному занятті поговоримо про Шардінг та Реплікацію. Як розділити базу на шматки, щоб вона не луснула.

А поки що — це був CS50! 🏛️

Практикуйтесь, експериментуйте і не бійтеся червоних цифр у консолі!