Блог компанії 3v-Hosting
14 хв.
Продуктивність сайту - це один з ключових факторів, що впливають не тільки на користувальницький досвід, але і на бізнес-показники, такі як конверсія, утримання аудиторії і навіть позиції в пошукових системах. Сучасні користувачі не готові чекати завантаження сторінки довше однієї секунди, а пошукові системи все частіше враховують реальні метрики швидкості при ранжуванні сайтів у пошуковій видачі. В останні кілька років Google прямо вказує в своїх документах, що чим швидше працює сайт, тим вище він ранжується, і навпаки - якщо сайт надзвичайно повільний, то він і зовсім може не потрапити в пошукову видачу навіть за найрелевантнішим запитом.
При цьому поширеною помилкою є вважати, що вся проблема криється виключно в потужності сервера або ширині каналу зв'язку. На практиці навіть потужний сервер з SSD і великим об'ємом оперативної пам'яті не врятує сайт, якщо база даних працює неефективно. Повільні і неоптимізовані SQL-запити здатні «вбити» продуктивність додатка, викликати лавиноподібне зростання навантаження і привести в результаті до повного падіння сервісу.
У цій статті ми розберемо покроковий і безпечний підхід до пошуку, аналізу та оптимізації повільних MySQL запитів, який підходить для реальних production-проектів і актуальний для сучасних версій MySQL і MariaDB.
Перш ніж приступати до оптимізації продуктивності бази даних, необхідно точно розуміти, яка версія СУБД використовується на сервері і які можливості вона надає. Від версії MySQL або MariaDB безпосередньо залежить набір доступних інструментів діагностики, поведінка оптимізатора запитів, підтримка тих чи інших механізмів кешування, а також коректність і безпека конфігураційних параметрів. Рекомендації, актуальні для старих версій, в сучасних релізах можуть бути неефективними або зовсім відсутніми, тому врахування версії СУБД є обов'язковим кроком перед початком будь-яких робіт з аналізу та оптимізації.
У таблиці нижче, в наочному вигляді, ми зібрали основні компоненти СУБД, що використовуються для пошуку і оптимізації повільних запитів, та їх підтримку різними версіями СУБД.
Таблиця - підтримка компонентів різними версіями СУБД
| Компонент | MySQL 5.7 | MySQL 8.0 | MariaDB 10.x |
|---|---|---|---|
| Журнал повільних запитів | Так | Так | Так |
| Performance Schema | Так | Так | Частково |
| sys schema | Так | Так | Частково |
| Query Cache | Застаріває | Видалено | Доступний (не рекомендовано) |
Наприклад, починаючи з MySQL 8.0, Query Cache повністю видалений, оскільки він погано масштабувався і створював блокування. Тому будь-які рекомендації щодо його включення в сучасних системах є застарілими і потенційно шкідливими.
Перший і обов'язковий крок у пошуку проблем з продуктивністю БД - це включення журналу повільних запитів або slow query log. Це механізм MySQL, який фіксує SQL-запити, що виконуються довше заданого часу. Саме він дозволяє перейти від суб'єктивних відчуттів («сайт гальмує») до конкретних фактів.
Для включення цього логу відкрийте конфігураційний файл вашого екземпляра MySQL. Найчастіше це або /etc/mysql/mysql.conf.d/mysqld.cnf або /etc/my.cnf. Додайте в кінці цього файлу наступні рядки:
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
Додаткові параметри, які можуть бути корисні:
log_queries_not_using_indexes = 0
min_examined_row_limit = 1000
Пояснення:
long_query_time - це поріг часу в секундах. Запити, швидші за це значення, не будуть реєструватися;log_queries_not_using_indexes - включає запис у журнал тільки тих SQL-запитів, які виконуються без використання індексів, тобто виконують повне сканування таблиці. Тому цей параметр варто включати тільки тимчасово, інакше лог швидко перетвориться на сміття;min_examined_row_limit - дозволяє ігнорувати запити, що працюють з малою кількістю рядків.
Після змін перезапустіть MySQL за допомогою наступної команди:
systemctl restart mysql
На активних і високозавантажених сайтах файл журналу повільних запитів може дуже швидко збільшуватися, особливо при низькому порозі long_query_time або великій кількості однотипних запитів. Без налаштованої ротації slow log здатний за лічені дні зайняти гігабайти дискового простору, що призведе не тільки до проблем з логуванням, але і до набагато серйозніших наслідків, таких як переповнення файлової системи, неможливості запису нових даних і, як наслідок, аварійної зупинки MySQL або всього сервісу в цілому.
Саме тому slow log слід розглядати як тимчасовий діагностичний інструмент, тобто його необхідно регулярно ротувати, контролювати розмір файлів і своєчасно очищати застарілі дані, особливо в production-середовищі з постійним користувацьким трафіком.
Тому рекомендується:
Важливо розуміти, що slow log - це інструмент діагностики, а не архів назавжди.
Також важливо розуміти, що slow query log - це не SQL-файл, його не можна «скинути» MySQL-клієнту. Для аналізу використовуються спеціальні утиліти, які вміють агрегувати та інтерпретувати зібрані дані. Ось ці утиліти:
mysqldumpslow - це вбудований інструмент MySQL, який підходить для швидкої первинної оцінки повільних запитів. Наприклад, виконавши команду:
mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log
Ви отримаєте:
Це дозволяє швидко зрозуміти, які запити варто аналізувати в першу чергу.
Коли загальна картина стає зрозумілою - потрібно провести детальний глибокий аналіз по конкретному запиту. Для цього використовується pt-query-digest. Він показує:
Це особливо важливо, коли запит виконується швидко, але дуже часто, наприклад тисячі разів на секунду.
Коли проблемний запит вже знайдено і підтверджено даними з slow query log або Performance Schema, закономірно виникає питання - чому саме цей запит виконується повільно. Сам по собі факт довгого виконання ще не дає розуміння причини, так як це може бути відсутність індексів, невдалий порядок з'єднання таблиць, сортування поза індексом, робота з великим обсягом даних або неоптимальна структура запиту.
Відповідь на це питання дає оператор EXPLAIN, який дозволяє заглянути всередину механізму виконання запиту і побачити, який план дій вибирає оптимізатор MySQL. З його допомогою можна зрозуміти, які таблиці і в якому порядку обробляються, чи використовуються індекси, скільки рядків сервер очікує просканувати і які додаткові операції виконуються в процесі. Саме аналіз плану виконання є ключовим етапом при оптимізації повільних запитів, оскільки він дозволяє приймати рішення на основі фактів, а не припущень.
Подивимося на такий приклад:
EXPLAIN SELECT * FROM customers WHERE age > 30;
При аналізі плану виконання запиту особливу увагу слід приділяти декільком полям виводу EXPLAIN. Саме вони дають найбільш точне уявлення про те, як саме MySQL отримує і обробляє дані, і де виникають вузькі місця.
type - спосіб доступу до даних
Поле type показує, яким чином MySQL читає дані з таблиці. Це один з найважливіших індикаторів продуктивності запиту. Чим «розумніший» спосіб доступу, тим менше даних серверу доводиться обробляти.
Найгіршим варіантом вважається значення ALL, яке означає повний перебір таблиці. У цьому випадку MySQL змушений прочитати кожен рядок, незалежно від умов запиту. При великих обсягах даних це призводить до різкого зростання часу виконання і навантаження на диск і CPU. Більш бажані значення - ref, range, const, так як вони вказують на використання індексів і роботу з обмеженим набором рядків.
key - використовуваний індекс
Поле key показує, який саме індекс був обраний оптимізатором для виконання запиту. Якщо значення NULL, це означає, що жоден індекс не був використаний, навіть якщо він існує. Причини можуть бути різними: невідповідна структура запиту, розбіжність типів даних, використання функцій в WHERE або неправильний порядок умов.
Відсутність індексу майже завжди означає збільшення часу виконання запиту, особливо на великих таблицях. Якщо індекс є, але не використовується, це явний сигнал до перегляду запиту або схеми індексації.
rows - оцінка кількості оброблюваних рядків
Поле rows показує, скільки рядків MySQL планує просканувати для отримання результату. Це оцінка, а не точне значення, але вона дозволяє зрозуміти масштаб операції. Великі значення rows вказують на те, що серверу доводиться обробляти значний обсяг даних, що безпосередньо впливає на продуктивність.
Навіть при наявності індексу високий показник rows може свідчити про низьку селективність умов або невдалий вибір індексу. У таких випадках оптимізація запиту або створення більш відповідного індексу може істотно знизити навантаження.
Extra - додаткові операції
Поле Extra містить додаткову інформацію про дії, які MySQL виконує в процесі обробки запиту. Саме тут часто ховаються найнеприємніші сюрпризи.
Записи на кшталт Using filesort означають, що сортування виконується поза індексом, що може призвести до використання тимчасових файлів і уповільнення запиту. Значення Using temporary вказує на створення тимчасових таблиць, які особливо небезпечні при роботі з великими обсягами даних і складними GROUP BY або ORDER BY. Такі операції збільшують навантаження на пам'ять і диск і часто стають причиною різкого падіння продуктивності.
При аналізі EXPLAIN існують ознаки, які майже завжди вказують на потенційні проблеми і вимагають подальшого розгляду:
Виявлення одного або декількох таких ознак не завжди означає катастрофу, але майже завжди служить сигналом до того, що запит вимагає оптимізації або перегляду структури даних.
Індекси - це потужний, але не універсальний інструмент. Помилкова індексація може погіршити продуктивність вашої бази даних.
Основні рекомендації щодо побудови індексів:
WHERE, JOIN, ORDER BY;EXPLAIN.Важливо! Після додавання індексу, настійно рекомендуємо вам знову повторити тести.
Performance Schema призначена для глибокого і детального аналізу поведінки бази даних на рівні внутрішніх механізмів MySQL. На відміну від slow query log, який фіксує тільки окремі повільні запити постфактум, Performance Schema працює в реальному часі і постійно збирає статистику про виконання запитів, використання ресурсів, очікування блокувань та інші ключові показники.
Особливість Performance Schema полягає в тому, що вона агрегує дані за типами і шаблонами запитів, а не за конкретними значеннями параметрів. Це дозволяє побачити не поодинокі проблеми, а системні патерни навантаження, наприклад, які класи запитів створюють основне навантаження на сервер, які операції найчастіше споживають CPU або час очікування, і які ділянки додатка вимагають оптимізації в першу чергу. Такий підхід особливо корисний на високозавантажених проектах, де безліч однакових запитів окремо виглядають нешкідливо, але в сумі мають серйозний негативний вплив на продуктивність бази даних.
Приклад запиту до Performance Schema:
SELECT * FROM performance_schema.events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;
Цей запит показує:
Важливо також пам'ятати, що дані в Performance Schema агрегуються за шаблонами запитів, а не за конкретними значеннями.
У сучасних версіях MySQL підвищення продуктивності досягається не за рахунок вбудованого Query Cache, який застарів і був видалений з MySQL 8.0, а за рахунок багаторівневого кешування на різних рівнях архітектури додатка. Такий підхід дозволяє значно знизити навантаження на базу даних і прискорити відгук сайту без ускладнення SQL-запитів.
На рівні додатка найчастіше використовується кешування за допомогою Redis або Memcached. Ці системи дозволяють зберігати результати часто виконуваних запитів або обчислень в пам'яті і повторно віддавати їх без звернення до бази даних. Особливо ефективно це працює для даних, які рідко змінюються, але часто запитуються, наприклад списків, налаштувань або довідкової інформації.
На рівні веб-сервера застосовується кешування динамічного контенту, наприклад за допомогою fastcgi_cache в Nginx. Такий підхід дозволяє віддавати готові HTML-сторінки без запуску PHP або іншого бекенд-додатка, що істотно знижує навантаження як на веб-сервер, так і на MySQL. Це особливо корисно для сторінок з високою відвідуваністю і однаковим вмістом для більшості користувачів.
З боку самої бази даних ключову роль відіграє InnoDB Buffer Pool - область пам'яті, в якій MySQL зберігає часто використовувані дані та індекси. При достатньому розмірі buffer pool більшість операцій читання виконується з оперативної пам'яті, а не з диска, що багаторазово прискорює роботу. Грамотне налаштування цього параметра часто дає більший приріст продуктивності, ніж будь-які точкові оптимізації запитів.
Нарешті, на рівні коду додатка важливо використовувати підготовлені вирази (prepared statements). Вони дозволяють повторно використовувати плани виконання запитів, зменшуючи накладні витрати на їх розбір і оптимізацію, а також підвищують безпеку додатка.
У сукупності такий багаторівневий підхід до кешування в більшості випадків дає більш відчутний і стабільний ефект, ніж спроби прискорити базу даних виключно за рахунок переписування складних SQL-запитів.
Будь-яка оптимізація бази даних має сенс тільки тоді, коли її результат можна чітко зафіксувати і порівняти. Зміни в індексах, запитах або конфігурації MySQL повинні приводити до вимірюваного ефекту, інакше неможливо зрозуміти, чи були дії дійсно корисними або зміни не дали очікуваного результату.
Саме тому після кожної оптимізації необхідно пройти стандартний цикл перевірки, який дозволяє об'єктивно оцінити вплив внесених змін.
Після внесення змін рекомендується виконати наступний набір дій:
Оновити статистику таблиць
Виконання команди ANALYZE TABLE змушує MySQL перерахувати статистику розподілу даних. Це особливо важливо після додавання або зміни індексів, оскільки без актуальної статистики оптимізатор може продовжувати використовувати неоптимальний план виконання запитів.
Порівняти журнал повільних запитів
Необхідно зіставити slow query log до і після оптимізації. Важливо аналізувати не тільки максимальний час виконання запитів, але і:
Повторно проаналізувати EXPLAIN
Для оптимізованих запитів слід повторно виконати EXPLAIN і переконатися, що:
rows);Using temporary і Using filesort, якщо вони були раніше.
Виміряти реальний час відгуку сайту
Навіть якщо показники бази даних покращилися, важливо перевірити, як це позначилося на реальному досвіді користувачів. Для цього можна використовувати:
Для наочності результати оптимізації зручно зводити в порівняльну таблицю за прикладом цієї:
| Показник | До оптимізації | Після оптимізації | Коментар |
|---|---|---|---|
| Кількість повільних запитів | |||
| Максимальний час виконання запиту | |||
| Середній час виконання | |||
| Використання індексів | Ні / Частково | Так | |
| Наявність temporary/filesort | Так / Ні | Так / Ні | |
| Середній час відповіді сторінки |
Такий підхід дозволяє не тільки зафіксувати результат, але і документувати проведені зміни, що особливо важливо для командної роботи, підтримки проекту в майбутньому і повторних оптимізацій.
Без структурованої перевірки «до і після» оптимізація бази даних легко перетворюється на набір випадкових дій, що не дають стабільного і відтворюваного ефекту.
Так, журнал повільних запитів можна і потрібно використовувати на production-серверах, якщо він налаштований коректно. Важливо задати розумний поріг long_query_time, включити ротацію логів і контролювати розмір файлів. За таких умов slow log практично не впливає на продуктивність і дозволяє виявляти реальні проблеми без ризику для стабільності системи.
long_query_time краще використовувати?Для більшості сайтів оптимальним значенням є діапазон 1–2 секунди. На високозавантажених проектах поріг можна знизити, але робити це варто тимчасово і під контролем, інакше журнал буде переповнений малозначущими запитами.
Наявність індексу не гарантує його використання. MySQL може ігнорувати індекс через невідповідну структуру запиту, використання функцій в умовах, розбіжності типів даних, низьку селективність індексу або застарілу статистику. У таких випадках необхідно аналізувати EXPLAIN і при необхідності оновлювати статистику або переглядати сам запит.
Не завжди. Іноді додавання індексу дійсно прискорює запит, але в інших випадках проблема криється в логіці запиту, зайвих з'єднаннях таблиць або надмірній вибірці даних. Крім того, кожен новий індекс збільшує навантаження на операції запису, тому індексацію слід застосовувати свідомо.
Ці інструменти вирішують різні завдання і найкраще працюють разом. Slow query log підходить для аналізу конкретних повільних запитів і історії проблем, а Performance Schema дозволяє бачити загальну картину навантаження і виявляти системні патерни в реальному часі.
Частково так, але ефективність буде нижчою. Без реальних даних про навантаження, частоту запитів і поведінку користувачів складно зробити точні висновки. Для якісної оптимізації бажано мати доступ до slow log або статистики Performance Schema саме з production-середовища.
Ознаками успішної оптимізації є скорочення кількості повільних запитів, зниження середнього і максимального часу виконання, поліпшення планів виконання EXPLAIN, а також зменшення часу відгуку сайту або додатка для кінцевих користувачів.
Пошук і оптимізація повільних запитів MySQL - це не разова задача, а скоріше постійний процес, який повинен стати частиною регулярного обслуговування будь-якого сайту або додатка. Навіть прості і базові кроки, такі як включення slow query log, аналіз планів виконання запитів і коректна індексація, дозволяють виявити і усунути більшість причин, що призводять до уповільнення роботи сайту і зростання навантаження на сервер.
Важливо пам'ятати, що будь-які зміни в структурі бази даних або конфігурації MySQL вимагають усвідомленого підходу і обов'язкової перевірки результату. Помилкові дії, виконані без розуміння наслідків, можуть не тільки не дати приросту продуктивності, але і привести до серйозних проблем, наприклад до деградації швидкості, втрати даних або навіть повної недоступності сервісу.
Грамотна оптимізація будується на фактах і вимірах, акуратних змінах і регулярному контролі стану бази даних. Саме такий методичний підхід дозволяє забезпечити стабільну, передбачувану і швидку роботу MySQL в довгостроковій перспективі.
Налаштування мережевого підключення на сервері або домашньому комп'ютері є базовим завданням, без якого не обійтися, якщо ви хочете підключити свій комп'ютер до...
При адмініструванні Linux-серверів може виникнути потреба швидкого перемонтування однієї або всіх файлових систем без перезавантаження сервера. Як це зробити ми...
Одним з перших завдань, які повинен виконати системний адміністратор при налаштуванні ОС на новому сервері, поряд з налаштуванням користувачів та їх дозволів - ...
Практичне введення в grep для Linux: як працює команда, які прапори дійсно потрібні, типові помилки та реальні сценарії використання grep в адмініструванні, роз...
Детально про те, як працюють IP-адреси, що таке IPv4 і IPv6, публічні та приватні IP, DNS, маршрутизація, безпека та використання в серверній інфраструктурі.
Прискорення WordPress на рівні Nginx: правильні налаштування PHP-FPM, try_files, статика, кешування, Brotli, захист wp-login і безпечні заголовки для стабільної...
