Швидкодія бази даних — це фундамент стабільної роботи веб-проєкту. Саме БД найчастіше стає прихованим вузьким місцем: код застосунку може бути оптимальним, сервер — потужним, але одна неефективна вибірка або неправильно налаштований буфер здатні звести нанівець увесь потенціал інфраструктури.

У міру зростання трафіку та обсягу даних проблеми проявляються поступово: сторінки починають «підвисати», API відповідає нестабільно, збільшується навантаження на CPU і диск, з’являються блокування та таймаути з’єднань. Без системного підходу до діагностики такі симптоми складно інтерпретувати — незрозуміло, де саме виникає деградація: у запитах, індексах, параметрах сервера чи в самій архітектурі зберігання.

Ця стаття пропонує практичний і відтворюваний підхід до прискорення БД для проєктів, розміщених на VPS/VDS і виділених серверах. Ми розберемо:

• як зафіксувати початковий стан і зрозуміти, що саме потрібно покращити;
• як діагностувати ОС перед тим, як заглиблюватися в SQL;
• як аналізувати плани виконання запитів і проєктувати індекси під фактичне навантаження;
• як коректно налаштовувати буфери, кеш і з’єднання з конкретними значеннями;
• як перевірити результат і коли переходити до масштабування.

Ключовий принцип — вимірюваність. Будь-яка оптимізація має бути підтверджена метриками й мати прогнозований ефект. Замість хаотичних змін ви отримаєте структурований цикл: зафіксувати → діагностувати → усунути → перевірити результат.

Крок 1. Визначте ціль і зафіксуйте baseline

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

На практиці використовують:

• час відповіді API або сторінок у 95% і 99% запитів (p95/p99);
• QPS/TPS (кількість запитів або транзакцій за секунду) на поточному сервері;
• час виконання ключових запитів;
• завантаження CPU;
• очікування підсистеми введення/виведення (I/O);
• ефективність використання систем кешування (cache hit ratio).

Крок 2. Зафіксуйте симптоми на рівні ОС

Перш ніж аналізувати SQL-запити, необхідно перевірити стан операційної системи. Якщо сервер обмежений дисковою підсистемою або пам’яттю, зміна індексів чи параметрів БД не усуне першопричину — оптимізація запитів дасть ефект лише на здоровій інфраструктурі.

Базовий набір команд:

uptime
free -h
df -h
iostat -x 1 5
vmstat 1 5

Крок 3. Аналіз і прискорення запитів

Після перевірки інфраструктури наступним кроком стає аналіз запитів. Саме вони найчастіше формують основне навантаження. Налаштування параметрів сервера без розуміння структури запитів призводить до поверхневих покращень.

Увімкніть slow query log і визначте лідерів навантаження

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

MySQL / MariaDB

Додати до конфігураційного файлу:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 0.5
log_queries_not_using_indexes = 1

Перезавантажити MySQL:

# systemctl restart mysql

Для аналізу логів використовуйте таку команду, вона покаже 10 найповільніших запитів, відсортованих за часом виконання.

# mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

Використовуйте EXPLAIN і аналізуйте план виконання

План виконання показує, яким чином СУБД інтерпретує запит. Ключове завдання — переконатися, що використовується індекс, а не виконується повне сканування таблиці. Для швидкого аналізу використовуйте директиву EXPLAIN FORMAT=TRADITIONAL:

EXPLAIN FORMAT=TRADITIONAL
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Для детального аналізу використовуйте директиву EXPLAIN FORMAT=JSON:

EXPLAIN FORMAT=JSON
SELECT *
FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50\G

Приклад результату:

{
  "query_block": {
    "select_id": 1,
    "nested_loop": [
      {
        "table": {
          "table_name": "orders",
          "access_type": "range",
          "possible_keys": ["idx_user_status_created"],
          "key": "idx_user_status_created",
          "used_key_parts": ["user_id", "status", "created_at"],
          "key_length": "14",
          "rows_examined_per_scan": 25,
          "rows_produced_per_join": 25,
          "filtered": "100.00",
          "extra": {
            "using_index": true,
            "using_filesort": true
          }
        }
      }
    ]
  }
}

Індекси: проєктування під реальні сценарії

Індексація має ґрунтуватися на фактичних запитах. Кожен індекс збільшує навантаження на запис і споживає додатковий дисковий простір, тому надмірна індексація шкідлива так само, як і її відсутність.

Основні принципи:

• індекс має значно скорочувати вибірку;
• порядок колонок у складеному індексі відповідає фільтрам і сортуванню (правило: WHERE, потім ORDER BY);
• індекси, які не використовуються планувальником, підлягають видаленню.

Приклад складеного індексу:

CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at);

Цей індекс оптимальний для запиту:

SELECT * FROM orders
WHERE user_id = 123 AND status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Як перевірити використання індексів:

SELECT * FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE OBJECT_SCHEMA = 'your_database'
ORDER BY COUNT_STAR DESC;

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

-- Знайти індекси, які не використовувалися
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE COUNT_STAR = 0 AND INDEX_NAME != 'PRIMARY'
AND OBJECT_SCHEMA != 'mysql' AND OBJECT_SCHEMA != 'performance_schema';

ALTER TABLE orders ADD INDEX idx_user_created (user_id, created_at), ALGORITHM=INPLACE, LOCK=NONE;

Оптимізація витратних патернів

Деякі конструкції призводять до надмірного навантаження незалежно від наявності індексів.

Рекомендації:

• замість SELECT * указуйте конкретні поля;
• у SQL-запитах уникайте LIKE '%term%' без повнотекстового індексу;
• не застосовуйте OFFSET на великих обсягах;
• усувайте N+1 запити через об’єднання або батчі.

Додав короткі пояснення до кожного кейсу російською.

Приклад неоптимального запиту

Цей запит використовує LIKE із шаблоном %2024%, через що індекс за полем created_at не може бути використаний. У результаті база даних виконує повне сканування таблиці, що суттєво знижує продуктивність на великих обсягах даних.

-- Погано: повне сканування таблиці для фільтрації
SELECT * FROM orders
WHERE created_at LIKE '%2024%'
LIMIT 100;

-- Добре: точна фільтрація за діапазоном дат
SELECT id, user_id, total, created_at FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
LIMIT 100;

Проблема OFFSET на великих обсягах

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

-- Погано: MySQL прочитає 1,000,050 рядків і відкине перший мільйон
SELECT * FROM orders
OFFSET 1000000
LIMIT 50;

-- Добре: keyset pagination
SELECT id, created_at, total FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
ORDER BY created_at DESC
LIMIT 50;

Проблема N+1 запитів

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

// Погано: N+1 запити
$orders = $db->query("SELECT * FROM orders WHERE user_id = ?", [$userId]);
foreach ($orders as $order) {
    // Для кожного замовлення — окремий запит!
    $items = $db->query("SELECT * FROM order_items WHERE order_id = ?", [$order['id']]);
}

// Добре: один JOIN-запит
$data = $db->query("
    SELECT o.*, oi.*
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.user_id = ?
", [$userId]);

Крок 4. Налаштування параметрів БД і з’єднань

Налаштування конфігурації має виконуватися лише після аналізу навантаження. Параметри пам’яті та з’єднань залежать від обсягу RAM, кількості одночасних користувачів і характеру операцій.

Буфери та кеш БД

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

InnoDB Buffer Pool (MySQL)

# Основний кеш для даних та індексів InnoDB
# Рекомендація: 50-75% від вільної RAM на виділеному сервері
innodb_buffer_pool_size = 12G

# Розмір сторінок у буфері (у байтах)
innodb_page_size = 16K

# Кількість екземплярів буфера (для паралелізму на багатоядерних серверах)
innodb_buffer_pool_instances = 8

# Відсоток буфера, виділений під старі дані
innodb_old_blocks_pct = 37

# Час у мілісекундах до переміщення сторінки зі "старої" в "нову" частину
innodb_old_blocks_time = 1000

# Сервер із 4GB RAM (малі проєкти)
innodb_buffer_pool_size = 2G

# Сервер із 8GB RAM (середні проєкти)
innodb_buffer_pool_size = 6G

# Сервер із 16GB RAM (великі проєкти)
innodb_buffer_pool_size = 12G

# Сервер із 32GB RAM (високонавантажені проєкти)
innodb_buffer_pool_size = 24G

Інші важливі буфери

# Буфер для операцій сортування та хеш-таблиць (на операцію)
sort_buffer_size = 256K

# Буфер для операцій об’єднання (на операцію)
join_buffer_size = 256K

# Буфер для читання таблиць (на операцію)
read_rnd_buffer_size = 8M

# Буфер введення (на з’єднання)
read_buffer_size = 128K

# Розмір буфера логів перед записом на диск
binlog_cache_size = 32K

# Максимальний розмір пакета даних
max_allowed_packet = 64M

# Буфер для тимчасових таблиць
tmp_table_size = 32M
max_heap_table_size = 32M

Як перевірити ефективність буфера:

-- Дізнатися поточний розмір буфера
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- Перевірити статус буфера
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- Cache hit ratio має бути вищим за 95%
SELECT (Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads)) * 100 AS cache_hit_ratio;

Якщо cache_hit_ratio нижче 90% — збільште innodb_buffer_pool_size. Якщо після збільшення різко зросло споживання ОЗП або активувався SWAP — зменште назад: завищений буфер так само шкідливий, як і занижений.

Оптимізація з’єднань

Кожне відкрите з’єднання до бази даних споживає оперативну пам’ять і процесорний час на перемикання контексту. Розрахунок max_connections виконується на основі вже зафіксованого значення буферного пулу.

# Максимальна кількість одночасних з’єднань
max_connections = 200

# Таймаут для неактивного з’єднання (у секундах)
wait_timeout = 28800
interactive_timeout = 28800

# Максимальна кількість з’єднань від одного хоста
max_connections_per_hour = 1000
max_user_connections = 50

Як розрахувати max_connections

max_connections = (доступна_память - буфер_пула) / пам'яті_на_з'єднання

Але рекомендується бути консервативнішим: max_connections = 200–500 для більшості застосунків.

Інструменти для пулінгу з’єднань

• ProxySQL — це проксі-сервер між застосунком і MySQL, який керує пулом з’єднань і знижує навантаження на базу даних. Він повторно використовує вже відкриті з’єднання, що зменшує витрати на їх постійне створення та закриття. Додатково підтримує балансування навантаження, розділення read/write-запитів і гнучку маршрутизацію SQL.

• MySQL Router — офіційний інструмент маршрутизації з’єднань від Oracle Corporation. Він автоматично спрямовує запити до потрібного сервера MySQL (наприклад, до primary або replica) і використовується в кластерах, таких як MySQL InnoDB Cluster. Рішення простіше в налаштуванні, але має менше можливостей, ніж ProxySQL.

Інтерпретація

• Обмеження зверху: встановлення жорсткого ліміту (max_connections) захищає базу від падіння через нестачу пам’яті. Зайві запити очікуватимуть у черзі на рівні веб-сервера, а не «душитимуть» ядро БД.
• Робота пулера: інструмент на кшталт ProxySQL тримає, наприклад, 50 постійних з’єднань із базою, але успішно обслуговує 5000 з’єднань від веб-сервера, миттєво повторно використовуючи їх.

Інші критичні параметри продуктивності

# Механізм зберігання (завжди використовуйте InnoDB для нових проєктів)
default-storage-engine = InnoDB

# Формат рядків (Barracuda підтримує більше функцій)
innodb_file_format = Barracuda
innodb_file_per_table = ON

# Рівень ізоляції транзакцій
transaction_isolation = READ-COMMITTED

# Автоматичний коміт
autocommit = 1

# Максимальний час виконання запиту (у секундах)
max_execution_time = 300

# Рівень скидання логів на диск
innodb_flush_log_at_trx_commit = 2

# Розмір логу InnoDB
innodb_log_file_size = 512M

# Буфер логу InnoDB
innodb_log_buffer_size = 16M

# Кількість потоків для операцій введення-виведення
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# Попереднє завантаження буфера під час старту
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_at_shutdown = ON

Крок 5. Перевірка результату та порівняння з baseline

Після кожного циклу змін необхідно виміряти ефект. Будь-яка зміна — це гіпотеза: без перевірки невідомо, чи вирішено початкову проблему, чи створено нову (наприклад, прискорилися SELECT, але сповільнилися INSERT і UPDATE).

Інструменти перевірки

Порівняння базових метрик (baseline), зафіксованих на першому кроці, з поточними показниками сервера під реальним навантаженням.

Інтерпретація результату

• Чистота логів: slow query log має стати значно «чистішим», а час виконання найважчих запитів — впасти нижче вашого цільового порога.
• Розвантаження диска: у виводі iostat має бути видно явне зниження навантаження на диск (I/O wait) — це підтверджує, що нові індекси або розширені буфери спрацювали.
• Стабілізація p95/p99: графіки часу відповіді перестали показувати випадкові піки, сайт працює плавно навіть під час напливу відвідувачів.

Якщо метрики не покращилися — поверніться до кроку 2 і пройдіть цикл знову. Лише після підтвердження позитивного ефекту можна переходити до обслуговування та масштабування.

Крок 6. Обслуговування даних і профілактика деградації

Регулярне обслуговування необхідне для підтримання передбачуваності планів виконання та зниження фрагментації. Без нього продуктивність деградує з часом — незалежно від якості початкового налаштування.

Оновлення статистики

Планувальник запитів MySQL будує плани виконання на основі статистики розподілу даних. Якщо вона застаріла (після масового видалення, оновлення або імпорту), база може вибрати вкрай неоптимальний шлях — наприклад, запустити повне сканування замість використання щойно створеного індексу.

Команди для оновлення статистики

-- Оновити статистику для конкретної таблиці
ANALYZE TABLE table_name;

-- Оптимізувати таблицю (дефрагментація)
OPTIMIZE TABLE table_name;

-- Перевірити й відновити таблицю
CHECK TABLE table_name;
REPAIR TABLE table_name;

Інтерпретація та застосування

Різке уповільнення після імпорту: якщо ви залили велику порцію даних, а сайт одразу після цього почав гальмувати — перший крок завжди ручне оновлення статистики.

ANALYZE TABLE orders, order_items, customers;
FLUSH TABLE orders, order_items, customers;

Автоматизація обслуговування

Цей cron-запуск призначений для регулярного оновлення статистики таблиць MySQL, яка використовується оптимізатором запитів для вибору ефективних планів виконання.

Коли статистика застаріває (наприклад, після великої кількості INSERT, UPDATE, DELETE), оптимізатор може почати вибирати неоптимальні індекси або стратегії виконання, що знижує продуктивність. Команда ANALYZE TABLE оновлює цю статистику.

# Щотижневий аналіз статистики
0 2 * * 0 mysql -u root -p `'password'` `your_database` -e "SELECT CONCAT('ANALYZE TABLE ', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'your_database' INTO OUTFILE '/tmp/analyze.sql'; SOURCE /tmp/analyze.sql;"

Висновок

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

Хаотичні зміни без baseline — найчастіша помилка. Інженер змінює innodb_buffer_pool_size, додає кілька індексів, править max_connections — і не знає, що саме спрацювало, а що погіршило запис. Наступний інцидент відтворити й усунути вже не вдається. Саме тому кожна зміна має бути ізольованою, вимірюваною та зворотною.

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

Нарешті, продуктивність деградує сама по собі — у міру зростання даних, зміни навантаження та застарівання статистики. Разова оптимізація без регулярного обслуговування дає тимчасовий ефект. Моніторинг cache_hit_ratio, періодичний ANALYZE TABLE і контроль slow query log — це не разові дії, а частина експлуатації.

Чек-ліст оптимізації MySQL:

1. ✓ Зафіксувати baseline-метрики (час відповіді, QPS, cache hit ratio)
2. ✓ Перевірити стан ОС (CPU, I/O, пам’ять, swap)
3. ✓ Увімкнути slow query log і аналізувати повільні запити
4. ✓ Проаналізувати EXPLAIN для кожного проблемного запиту
5. ✓ Додати індекси під фактичні запити, видалити невикористовувані
6. ✓ Налаштувати innodb_buffer_pool_size (50–75% RAM)
7. ✓ Налаштувати max_connections і пулінг з’єднань
8. ✓ Повторно виміряти й порівняти з baseline
9. ✓ Налаштувати регулярне обслуговування (ANALYZE TABLE, моніторинг)

Дотримуючись цього підходу, ви значно підвищите продуктивність вашого MySQL сервера.