Оптимізація запитів до MySQL: як поліпшити продуктивність?

Є певний момент у житті кожного проєкту, коли все працювало нормально — і раптом перестало. Сторінки завантажуються повільно, адмінка гальмує, користувачі скаржаться. Перевіряєте сервер — CPU в нормі, пам'яті достатньо. А потім відкриваєте логи повільних запитів MySQL і бачите там запити, які виконуються по три, п'ять, десять секунд.

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

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

З чого починати: інструменти діагностики

Оптимізація без вимірювань — це стрільба наосліп. Перш ніж щось виправляти, треба зрозуміти, що саме є проблемою.

Slow Query Log. Перший інструмент, який варто увімкнути на будь-якому production-сервері. MySQL веде журнал запитів, що виконуються довше за встановлений поріг. Зазвичай поріг встановлюють у одну секунду, але для навантажених систем може мати сенс знизити до 500 або навіть 200 мілісекунд. Лог показує сам запит, час виконання і кількість розглянутих рядків — це вже дає чітке уявлення про те, де шукати проблему.

EXPLAIN і EXPLAIN ANALYZE. Головний інструмент розуміння того, як MySQL виконує конкретний запит. Додайте слово EXPLAIN перед будь-яким SELECT — і замість результату отримаєте план виконання: які таблиці сканує MySQL, які індекси використовує (або не використовує), скільки рядків оцінює переглянути, який тип з'єднання застосовує.

Ключові колонки в результаті EXPLAIN, на які треба звертати увагу: тип доступу (type), використані індекси (key), оцінка кількості рядків (rows) і додаткова інформація (Extra). Тип доступу ALL означає повне сканування таблиці — у більшості випадків це сигнал тривоги. Типи ref, eq_ref і const є ознакою ефективного використання індексів.

EXPLAIN ANALYZE (доступний з MySQL 8.0) іде ще далі — він не просто будує план, а реально виконує запит і показує фактичний час на кожному кроці. Це особливо корисно, коли оцінки оптимізатора розходяться з реальністю.

Performance Schema і sys schema. Вбудований інструмент моніторингу MySQL, який збирає детальну статистику по запитах, з'єднаннях, очікуваннях і використанню ресурсів. Через sys schema до цих даних можна дістатися зручними представленнями — наприклад, побачити топ-10 найповільніших запитів за весь час роботи сервера або які таблиці найчастіше скануються повністю.

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

Індекси: основа продуктивності

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

Як працює індекс. Уявіть таблицю без індексів як стопку листів у довільному порядку. Щоб знайти лист від конкретного відправника — доведеться переглянути кожен. Індекс — це окрема відсортована структура (у MySQL за замовчуванням це B-tree), яка дозволяє знайти потрібний рядок без перегляду всіх інших. Якщо знаєте, де шукати в алфавітному покажчику — знайдете швидко, хоч би скільки листів у стопці.

Коли додавати індекс. Основне правило: індекс додається на стовпці, які часто використовуються в умовах WHERE, JOIN ON, ORDER BY і GROUP BY. Якщо ви регулярно фільтруєте замовлення за статусом і датою — індекс на цих стовпцях суттєво прискорить вибірку.

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

Складові індекси. Індекс може покривати кілька стовпців. Якщо запит фільтрує за двома умовами одночасно, складовий індекс на обох стовпцях буде ефективнішим, ніж два окремих. Але тут є нюанс: MySQL використовує складовий індекс лише з лівого краю. Якщо у вас індекс на стовпцях A, B і C, запит із фільтром лише по B і C цим індексом не скористається — потрібен фільтр по A (або A і B, або A, B і C). Цей принцип називається «правилом лівого префіксу» і є однією з найчастіших причин, чому індекс є, але не використовується.

Покривні індекси. Особливо ефективний підхід: індекс містить усі стовпці, які потрібні запиту — і для фільтрації, і для повернення в результаті. У цьому випадку MySQL взагалі не звертається до основної таблиці, а отримує всі дані прямо з індексу. В EXPLAIN це відображається як Using index — ознака максимально ефективної вибірки.

Що вбиває індекси. Є конструкції, які змушують MySQL ігнорувати індекс навіть якщо він є. Застосування функцій до проіндексованого стовпця в умові WHERE — наприклад, перетворення дати функцією або обчислення з числом — робить індекс непридатним для цього запиту. Використання оператора LIKE із шаблоном, що починається з символу підстановки, — теж. Неявне приведення типів, коли ви порівнюєте рядок із числом — аналогічно. Усі ці ситуації призводять до повного сканування таблиці навіть за наявності індексу.

Аналіз і переписування запитів

Іноді проблема не в індексах, а в тому, як написаний сам запит.

SELECT * як антипатерн. Конструкція «вибрати всі стовпці» зручна при написанні, але шкідлива для продуктивності. По-перше, MySQL передає більше даних, ніж реально потрібно — по мережі і в пам'ять застосунку. По-друге, якщо у таблиці є стовпці з великими текстами або BLOB-даними, вони потрапляють у вибірку навіть коли не потрібні. По-третє, покривні індекси перестають працювати, бо запит вимагає стовпці, яких в індексі немає. Правило просте: завжди вказувати лише ті стовпці, які дійсно потрібні.

Підзапити проти JOIN. Підзапити у WHERE зручно читати, але часто MySQL виконує їх неефективно — особливо корельовані підзапити, де внутрішній запит виконується для кожного рядка зовнішнього. У більшості випадків JOIN є більш ефективним: MySQL може оптимізувати план виконання і обрати порядок з'єднання таблиць. Якщо бачите підзапит у WHERE — варто спробувати переписати через JOIN і порівняти плани виконання.

Оператор IN з великими списками. Конструкція IN зручна для фільтрації за набором значень. Але якщо список значень налічує тисячі елементів — це проблема. MySQL будує тимчасову структуру для перевірки кожного рядка, і при великих списках це може бути повільнішим, ніж здається. Альтернатива: завантажити ідентифікатори у тимчасову таблицю з індексом і зробити JOIN.

LIMIT і пагінація. Класична пагінація через LIMIT з великим OFFSET — прихована пастка. Запит з OFFSET 50000 і LIMIT 20 змушує MySQL відсканувати і відкинути 50 000 рядків, щоб повернути 20 наступних. Чим більша сторінка — тим повільніший запит. Рішення — так звана «keyset pagination» або «cursor-based pagination»: замість офсету використовується умова WHERE по ідентифікатору або даті останнього переглянутого рядка. Такий запит виконується однаково швидко незалежно від того, яка це сторінка.

Агрегатні функції і GROUP BY. Запити з GROUP BY і агрегатами (COUNT, SUM, AVG) можуть бути дорогими, якщо MySQL змушений обробляти весь результат у тимчасовій таблиці. Допомагають індекси на стовпцях GROUP BY і обмеження набору рядків через WHERE до агрегації — фільтруйте раніше, групуйте пізніше.

Структура таблиць і типи даних

Оптимізація запитів починається ще на етапі проєктування схеми.

Правильні типи даних. Кожен зайвий байт у рядку множиться на мільйони рядків. Якщо значення поля не перевищує 255 — використовуйте TINYINT, а не INT. Якщо статус має кілька фіксованих значень — ENUM займає менше місця, ніж VARCHAR. Для зберігання IP-адрес правильніше використовувати числовий тип, а не рядок — і місця менше, і порівняння швидше.

VARCHAR vs TEXT. VARCHAR зберігається в рядку таблиці (до певного ліміту), TEXT — поза рядком, з окремим покажчиком. Для коротких рядків, за якими фільтруватимуть або сортуватимуть, VARCHAR є кращим вибором — він може бути покритий індексом. TEXT-поля індексуються лише частково (prefix index) і не можуть бути частиною покривного індексу.

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

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

Кешування як рівень оптимізації

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

Кешування на рівні застосунку. Redis або Memcached зберігають результати запитів у пам'яті. Перший запит іде в базу, результат зберігається в кеші з заданим TTL. Усі наступні запити отримують дані з пам'яті — в тисячі разів швидше. Ключове питання — інвалідація кешу: коли дані змінилися, кеш треба або оновити, або видалити. Некоректна інвалідація — одна з найпоширеніших причин багів у системах із кешуванням.

Query Cache у MySQL. У старих версіях MySQL (до 8.0) був вбудований query cache. Він зберігав результати запитів і повертав їх без звернення до диска. Але в MySQL 8.0 його прибрали — бо при активному записі в таблицю кеш постійно інвалідується і стає не допомогою, а накладними витратами. Сучасний підхід — кешування на рівні застосунку з явним контролем інвалідації.

Матеріалізовані представлення. MySQL не підтримує матеріалізовані представлення нативно (на відміну від PostgreSQL), але подібний ефект досягається через таблиці-агрегати, що оновлюються за розкладом або тригерами. Якщо щогодинний звіт з агрегованими даними будується щоразу заново по мільйонах рядків — краще будувати його раз і зберігати в окремій таблиці.

Конфігурація MySQL: що варто налаштувати

Правильні запити і правильна схема — необхідні умови. Але і конфігурація сервера впливає на продуктивність суттєво.

InnoDB Buffer Pool. Найважливіший параметр. Це область пам'яті, де MySQL кешує сторінки даних і індексів. Чим більший буфер пулу, тим більше даних зберігається в пам'яті і тим рідше MySQL звертається до диска. Загальна рекомендація: виділяти під buffer pool 70–80% доступної RAM на виділеному сервері. На сервері з 16 ГБ пам'яті це означає буфер у 12–13 ГБ.

innodb_log_file_size. Розмір журналу транзакцій впливає на продуктивність операцій запису. Занадто маленький журнал — часті checkpoint-операції і запис на диск. Занадто великий — довше відновлення після краша. Оптимальне значення залежить від обсягу операцій запису, але загальна рекомендація — від 256 МБ до 1 ГБ для активних систем.

max_connections. Максимальна кількість одночасних з'єднань. Занадто мало — застосунок отримує помилки «too many connections». Занадто багато — кожне з'єднання споживає пам'ять, і при одночасному активному використанні сервер може вичерпати ресурси. Правильне значення — мінімально необхідне для вашого навантаження, не «з запасом». Пул з'єднань на рівні застосунку дозволяє обслуговувати більше паралельних запитів при меншій кількості фактичних з'єднань з базою.

Повільний лог і профілювання. slow_query_log варто тримати увімкненим постійно на production. Це незначні накладні витрати порівняно з цінністю інформації. long_query_time встановлюйте відповідно до вимог до часу відповіді вашої системи.

Типові антипатерни: що робити не варто

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

N+1 запит. Класична проблема ORM. Завантажуєте список зі 100 статей — один запит. Потім для кожної статті окремим запитом завантажуєте автора — ще 100 запитів. Разом 101 запит замість одного з JOIN. ORM вирішують цю проблему через eager loading або батчинг — потрібно просто знати про неї і використовувати відповідний механізм.

Запити всередині циклів. Логіка «для кожного елемента зробити запит до бази» — майже завжди погана ідея. Краще зібрати всі ідентифікатори, зробити один запит із IN і зіставити результати в пам'яті застосунку.

Відсутність LIMIT там, де він потрібен. Запит без LIMIT на таблиці з мільйонами рядків поверне мільйони рядків. Якщо застосунок очікує кілька записів, але не обмежує вибірку — у кращому випадку це марне навантаження, у гіршому — падіння через вичерпання пам'яті.

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

Неіндексовані зовнішні ключі. Зовнішній ключ сам по собі не створює індекс на стовпці-посиланні в MySQL. Якщо ви часто робите JOIN або фільтруєте по стовпцю зовнішнього ключа — переконайтеся, що на ньому є індекс.

Моніторинг як постійна практика

Оптимізація — це не одноразова дія, а процес. База даних змінюється: додаються нові запити, зростає обсяг даних, змінюється розподіл навантаження. Те, що було ефективним рік тому, може стати вузьким місцем сьогодні.

Хороша практика: регулярно переглядати slow query log, стежити за метриками з Performance Schema, моніторити час відповіді і кількість рядків, що переглядаються в ключових запитах. Інструменти на зразок Percona Monitoring and Management або Grafana з MySQL-exporter для Prometheus надають зручні дашборди і алертинг — замість ручного перегляду логів.

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

Підсумок

Оптимізація MySQL — це не магія і не одна срібна куля. Це послідовна робота: виміряти, зрозуміти, виправити, знову виміряти.

Починайте з діагностики — slow query log і EXPLAIN дадуть 80% картини. Індекси вирішують більшість проблем продуктивності, але лише якщо розставлені правильно і дійсно використовуються запитами. Погано написані запити не виправить жоден індекс — іноді треба переписувати логіку. Конфігурація сервера і кешування — наступні рівні, коли базова оптимізація вже зроблена.