Коли оптимізувати і навіщо?
Зайвий раз лізти в налаштування сервера і змінювати значення параметрів (особливо, якщо не знаєте, чим це може закінчитися) не варто. Якщо розглядати дану тему з «дзвіниці» поліпшення продуктивності веб-ресурсів, то вона настільки велика, що їй потрібно присвячувати ціле наукове видання в 7 томах.
Але такого письменницького терпіння у мене явно немає, та й у вас Новомосковсктельского теж. Ми зробимо простіше, і постараємося лише злегка заглибитися в хащі оптимізації MySQL сервера і його складових. За допомогою оптимальної установки всіх параметрів СУБД можна досягти декількох цілей:
Збільшити швидкість виконання запитів.
Підвищити загальну продуктивність сервера.
Фреймворк YII2. Швидка розробка з сучасним PHP фреймворком
Дізнайся тонкощі сучасної веб-розробки за допомогою фреймворка YII2
Зменшити час очікування завантаження сторінок ресурсу.
Знизити споживання серверних потужностей хостингу.
Знизити обсяг займаного дискового простору.
Постараємося всю тематику оптимізації розбити на кілька пунктів, щоб було більш-менш зрозуміло, від чого «казанок» закипає.
Навіщо налаштовувати сервер
В MySQL оптимізацію продуктивності слід починати з сервера. Перш за все, слід прискорити його роботу і зменшити час обробки запитів. Універсальним засобом для досягнення всіх перерахованих цілей є включення кешування. Не знаєте, «what is it»? Зараз все поясню.
Якщо на вашому екземплярі сервера включено кешування, то система MySQL автоматично «запам'ятовує» введений користувачем запит. І наступного разу при його повторенні даний результат запиту (на вибірку) буде не оброблений, а взятий з пам'яті системи. Виходить, що таким чином сервер «економить» час на видачу відповіді, і внаслідок чого швидкість реагування сайту підвищується. У тому числі це стосується і загальної швидкості завантаження.
В MySQL оптимізація запитів може бути застосована до тих движкам і CMS, які працюють на основі даної СУБД і PHP. При цьому код, написаний на мові програмування, для генерації динамічної веб-сторінки запитує деякі її структурні частини і вміст (записи, архіви та інші таксономії) з БД.
Завдяки включеному кешуванню в MySQL виконання запитів до сервера СУБД відбувається набагато швидше. За рахунок чого і підвищується швидкість завантаження всього ресурсу в цілому. А це позитивно відбивається і на призначеному для користувача досвід, і на позиції сайту у видачі.
Включаємо і налаштовуємо кешування
Але давайте повернемося від «нудною» теорії до цікавої практиці. Подальшу оптимізацію бази MySQL продовжимо з перевірки стану кешування на вашому сервері БД. Для цього за допомогою спеціального запиту ми виведемо значення всіх системних змінних:
Результат виконання перевищив всі наші очікування, і видав таку кількість змінних, що на їх вивчення піде цілий тиждень.
Щоб отримати потрібні для оптимізації таблиць MySQL дані слід використовувати якийсь більш точно «націлений» запит на конкретні рядки. Наприклад, такий:
Фреймворк YII2. Швидка розробка з сучасним PHP фреймворком
Дізнайся тонкощі сучасної веб-розробки за допомогою фреймворка YII2
Зовсім інша справа.
Зробимо маленький огляд отриманих значень, які стануть в нагоді нам для оптимізації баз даних MySQL:
have_query_cache - значення показує «ВКЛ» кешування запитів чи ні.
query_cache_type - відображає активний тип кешу. Нам потрібно значення «ON». Це говорить про те, що кешування включено для всіх видів вибірки (команда SELECT). Крім тих, в яких використовується параметр SQL_NO_CACHE (забороняє збереження інформації про цей запит).
У нас все настройки правильні.
Відміряємо кеш під індекси і ключі
Тепер потрібно перевірити, скільки відведено оперативної пам'яті під індекси і ключі. Рекомендується встановлювати цей важливий для оптимізації БД MySQL параметр на 20-30% від обсягу оперативної пам'яті, доступної для сервера. Наприклад, якщо під екземпляр СУБД виділено 4 «гектара», то сміливо ставте 32 «метра». Але все залежить від особливостей певної бази і її структури (типів) таблиць.
Для установки значення параметра потрібно відредагувати вміст конфігураційного файлу my.ini, який в Денвері знаходиться за наступним шляхом: F: \ Webserver \ usr \ local \ mysql-5.5
Файл відкриваємо за допомогою Блокнота. Потім знаходимо в ньому параметр key_buffer_size і встановлюємо оптимальний для вашої системи ПК (в залежності від «гектарів» оперативки) розмір. Після цього потрібно перезапустити сервер БД.
В СУБД використовується кілька додаткових підсистем (нижнього рівня), і всі основні їх налаштування також задаються в даному файлі конфігурації. Тому, якщо потрібно провести в MySQL InnoDB оптимізацію, то ласкаво просимо сюди. Більш докладно цю тему ми вивчимо в одному з наших наступних матеріалів.
Вимірюємо рівень індексів
Використання індексів в таблицях значно підвищує швидкість обробки і формування відповіді СУБД на введений запит. MySQL постійно «вимірює» рівень застосування індексів і ключів в кожній БД. Для отримання даного значення використовуйте запит:
В отриманому результаті нас цікавить значення в рядку Handler_read_key. Якщо вказане там число маленьке, то це говорить про те, що індекси майже не використовуються в даній базі. А це погано (як у нас).
Також не забувайте про MySQL Explain оптимізації. За допомогою даної команди СУБД пояснює нам, як оптимальніше побудувати введений запит. Вона вказується в початку запиту на вибірку. наприклад:
Фреймворк YII2. Швидка розробка з сучасним PHP фреймворком
Дізнайся тонкощі сучасної веб-розробки за допомогою фреймворка YII2
Найсвіжіші новини IT і веб-розробки на нашому Telegram-каналі