А що, як би ці 5 секунд коштували вам 5000 доларів США? У користувачах, у вартості інфраструктури, у інструментах… Давайте знімемо стабілізатори з велосипеда і почнемо прогресувати трохи більше.
У мене є така думка, що якщо ми не розуміємо базових концепцій технологій, нам важко витягнути більше з них, намагатись знайти шляхи покращення без розуміння того, як дані зберігаються в базі даних, або який план виконання у select запиту.
Отже, сказавши це, моя ідея для цього мого першого документа, (що є практично вивантаженням того, що я маю в голові, і трішки більше) — це передати читачу частину цього знання та дати поштовх для вирішення проблем, з якими вони можуть стикатись.
Зв’язок між застосунком та базою даних
У таких фреймворках, як Laravel, зв’язок — це те, на що ми рідко звертаємо увагу. Я великий прихильник фреймворків, тим більше Laravel, який є настільки потужним, як мало хто з них на ринку, але зрозумійте, що через свою універсальність, часто нам потрібно робити дії "вручну", особливо це стосується бази даних, а ще більше — запитів SELECT. Приділяйте УВАГУ цьому і сумнівайтесь в кожному SQL, який виконується, якщо ви дозволяєте Eloquent генерувати запит.
Тепер про з’єднання з базою даних. Що відбувається:
- З’єднання встановлюється.
- MySQL перевіряє дані для підключення.
- Це з’єднання зберігається у пулі з’єднань MySQL.
- Виконуються необхідні дії.
- З’єднання завершується.
Важливо розуміти цей процес, адже за замовчуванням кожен запит, виконаний в додатку, проходить через цей процес.
Уявіть собі, що для кожного користувача, який заходить на панель, виконується 10 запитів для заповнення панелі. Цей процес відкриття з’єднання, виконання та закриття повторюється 10 разів для кожного користувача. А якщо їх 1000? Або 10000? Тут вже є місце для покращень.
Чому б не відкрити з’єднання та виконати всі 10 запитів перед його закриттям?
Звісно, залежно від обраної структури, це можна робити вручну, але сьогодні існують пакети, які створюють так звані "connection pools" (пули з’єднань), які відкривають з’єднання з базою даних і підтримують їх відкритими для виконання запитів.
Зберігання даних
Зберігання залежить від вибраного Store Engine (механізму зберігання), стандартним і найбільш поширеним є InnoDB. Він пропонує високу продуктивність і підтримує транзакції ACID.
Цей механізм використовує систему сторінок з розміром 16KB для зберігання даних. Для покращення роботи існує Buffer Pool, який зберігає найбільш запитувані дані в пам'яті для зменшення читань і записів на диск.
Одним з найбільш недооцінених, а часто й неправильно використовуваних елементів є індекси. Для початку, щодо індексів, потрібно розуміти, що існують clustered та non-clustered індекси:
- Clustered — найбільш поширені індекси, це зазвичай первинні ключі (оскільки таблиця може мати лише один clustered індекс). Вони базуються на B-Tree і оптимізують запити.
Але важливо розуміти, що при величезних обсягах даних, а ми говоримо про сотні тисяч, вони не вирішують проблему самостійно. І ось тут приходять Non-Clustered індекси. - Non-Clustered — ці індекси не зберігають таблицю в дисковому порядку, але організовують дані і частково зберігаються в пам'яті для пришвидшення доступу.
Це ті індекси, які ми створюємо за допомогою командиcreate index, можна створювати кілька індексів для однієї таблиці, але не створюйте індекси для кожної колонки і завжди думайте про створення складених індексів.
На частині запитів давайте поговоримо про це.
І дуже важливо думати про типи даних, зазвичай ми вивчаємо найбільш поширені, такі як varchar, float, int, datetime, text… Але є й інші типи, які мають схожі цілі, але використовуються в різних місцях. Наприклад, CHAR, цей тип ми знаємо, але після того, як ми дізнаємось, що коли ми визначаємо його розмір, цей простір завжди буде заповнений, а от varchar — ні, ми часто залишаємо його поза увагою. Але чи замислювались ви, чому він не був видалений?
Varchar і Char
Тип char є більш оптимізованим для продуктивності читання, оскільки він завжди має однакову кількість байт, що спрощує обчислення зміщення кожного запису на диску чи в пам'яті, завдяки чому читання стає більш ефективним, оскільки механізм вже точно знає, де починається та закінчується кожне значення. Прогнозованість розміру — одна з найбільших переваг, це також впливає на кешування та зменшення фрагментації.
Але де ж використати char? Одне з цікавих місць, де можна застосувати — це публічний ID, наприклад UUID. Цей тип ID завжди має однакову кількість символів — 36. Ви створюєте поле uuid як char (або навіть саме поле UUID, але це лише приклад), з індексом, і пошук за UUID стає дуже швидким навіть у таблицях з мільйонами записів.
Інші типи
Варто розуміти різницю між float і decimal, datetime і timestamp… Коли потрібно створювати нову таблицю, це чудова можливість для навчання.
Читання даних
Читання даних часто є найбільшим вузьким місцем бази даних, і джерелом фінансових проблем у компаніях. Оптимізація читання в базі даних — це фактично мистецтво.
Іноді підзапит (subquery) краще, ніж join, іноді варто розділити запит на два, дуже важливо тестувати різні методи і думати нестандартно.
Щоб запити повернули результат, перед цим відбуваються кілька кроків:
- Проходить через Query Parser для перевірки синтаксису та оптимізації інструкції.
- Аналізується Query Optimizer, який оцінює можливі індекси або повний скан таблиці.
Якщо використовується індекс, механізм звертається до індексу, який працює як карта, що вказує прямо на потрібні записи.
Якщо використовується повний скан таблиці, як випливає з назви, механізм буде аналізувати всю таблицю. - І, врешті-решт, виконується запит.
Індекси (Index)
Щоб індекс був "придатний" для використання, поля в клаузулі where І В ORDER BY повинні бути проіндексовані.
CREATE TABLE users (
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
password VARCHAR(255) NOT NULL,
address TEXT,
phone VARCHAR(20),
is_admin BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at TIMESTAMP NULL DEFAULT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
З такою таблицею, залежно від запитів, які виконує додаток, ми створюємо індекси, наприклад:
"У мене є звіт, який шукає користувачів-адміністраторів, що були видалені в січні 2024 року."
Ми могли б створити складений індекс між is_admin та deleted_at
CREATE INDEX idx_is_admin_deleted_at ON users (is_admin, deleted_at);
Цей індекс оптимізує продуктивність запитів, таких як:
SELECT * FROM users
WHERE is_admin = TRUE AND deleted_at BETWEEN '2024-01-01' AND '2024-01-31';
Але існує не лише структура B-Tree, є й Hash для точних пошуків, або Full Text Search для пошуків з LIKE, індекси “Spatial” для пошуків у геометричних даних, таких як пари координат, та ще кілька типів.
Інший важливий момент — це використання * в select, що може негативно вплинути на продуктивність запиту.
Як ми можемо оцінити цей вплив? Ми можемо використовувати команди EXPLAIN та EXPLAIN FORMAT=JSON перед SELECT.
Ці поля надають інформацію про використання ресурсів і план виконання запиту, що допомагає виявити проблеми та дає рекомендації для оптимізації.
EXPLAIN SELECT * FROM users
WHERE is_admin = TRUE AND deleted_at BETWEEN '2024-01-01' AND '2024-01-31';
Завершення
Оптимізація запитів — це мистецтво, тестуйте багато і аналізуйте дані, якими керуєте.
- Створення індексів: Будьте обережні при створенні індексів. Хоча вони покращують продуктивність запитів, вони також займають місце на диску і можуть негативно вплинути на продуктивність вставок і оновлень.
- Вибір типів даних: Ретельно аналізуйте типи даних, які будуть використовуватися, і вивчайте їх перед оголошенням. Це може мати великий вплив на ефективність і використання ресурсів.
- *Уникайте SELECT **: Завжди вказуйте необхідні стовпці у запитах, щоб зменшити обсяг повернутих даних і покращити продуктивність.
- Складні запити проти малих запитів: Оцінюйте ситуацію. Складний запит може бути ефективнішим, ніж виконання кількох малих запитів підряд, але це залежить від конкретного випадку і повинно бути протестовано.
- Інструменти діагностики: Використовуйте інструменти, такі як EXPLAIN, щоб зрозуміти, як MySQL обробляє запит, а також інші інструменти, наприклад, Query Insights у AWS.
- Постійний моніторинг: Впроваджуйте метрики продуктивності та постійно моніторьте вплив змін, внесених у базу даних.
Пам'ятайте, що кожен додаток має свої особливості, і те, що добре працює в одному випадку, може бути не найкращим у іншому. Тому вивчайте, експериментуйте та постійно оптимізуйте.
Перекладено з: Otimizando Persistência e Leitura de dados no MySQL e Aplicação