Від автора: один мій знайомий вирішив оптимізувати свій автомобіль. Спочатку одне колесо зняв, бо дах спиляв, потім мотор… загалом, зараз він пішки ходить. Це все наслідки неправильного підходу! Тому, щоб ваша СУБД продовжувала «їздити», оптимізація MySQL повинна проходити правильно.
Коли оптимізувати і навіщо?
Зайвий раз лізти в налаштування сервера і змінювати значення параметрів (особливо, якщо не знаєте, чим це може закінчитися) не варто. Якщо розглядати дану тему з «дзвіниці» покращення продуктивності веб-ресурсів, то вона настільки велика, що їй потрібно присвячувати ціле наукове видання у 7 томах.
Але такого письменницького терпіння у мене явно немає, та й у вас читацького теж. Ми зробимо простіше, і постараємося лише трохи заглибитися в хащі оптимізації MySQL сервера і його складових. З допомогою оптимальної установки усіх параметрів СУБД можна досягти декількох цілей:
Збільшити швидкість виконання запитів.
Підвищити загальну продуктивність сервера.
Зменшити час очікування завантаження сторінок ресурсу.
Знизити споживання серверних потужностей хостингу.
Знизити обсяг займаного дискового простору.
Постараємося всю тематику оптимізації розбити на кілька пунктів, щоб було більш-менш зрозуміло, від чого «казанок» закипає 
.
Навіщо налаштовувати сервер
В MySQL оптимізацію продуктивності слід починати з сервера. Насамперед, слід прискорити його роботу та зменшити час обробки запитів. Універсальним засобом для досягнення всіх перерахованих цілей є включення кешування. Не знаєте, «what is it»? Зараз все поясню.
Якщо на вашому екземплярі сервера включено кешування, то система MySQL автоматично «запам’ятовує» введений користувачем запит. І наступного разу при його повторенні даний результат запиту на вибірку) не буде оброблено, а узятий з пам’яті системи. Виходить, що таким чином сервер «економить» час на видачу відповіді, і внаслідок чого швидкість реагування сайту підвищується. В тому числі це стосується і загальної швидкості завантаження.
В MySQL оптимізація запитів застосовна до тих движка і CMS, які працюють на основі цієї СУБД і PHP. При цьому код, написаний на мові програмування, для динамічної генерації веб-сторінки запитує деякі її структурні частини і вміст (записи, архіви та інші таксономії) з БД.
Завдяки включеному кешуванню в MySQL виконання запитів до сервера СУБД відбувається набагато швидше. За рахунок чого підвищується швидкість завантаження всього ресурсу в цілому. А це позитивно позначається і на користувальницькому досвіді, і на позиції сайту у видачі.
Включаємо і налаштовуємо кешування
Але давайте повернемося від «нудною» теорії до цікавої практиці. Подальшу оптимізацію бази MySQL продовжимо з перевірки стану кешування на вашому сервері БД. Для цього за допомогою спеціального запиту ми виведемо значення системних змінних:
SHOW VARIABLES;
Результат перевершив всі наші очікування, і видав таку кількість змінних, що на їх вивчення піде цілий тиждень.
Щоб отримати потрібні для оптимізації таблиць MySQL дані слід використовувати якийсь більш точно «націлений» запит на конкретні строки. Наприклад, такий:
SHOW VARIABLES LIKE ‘%query_cache%’;
Зовсім інша справа.
Зробимо маленький огляд отриманих значень, які знадобляться нам для оптимізації баз даних 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 постійно «вимірює» рівень застосування індексів та ключів у кожної БД. Для отримання даного значення використовуйте запит:
SHOW STATUS LIKE ‘handler_read%’
В отриманому результаті нас цікавить значення в рядку Handler_read_key. Якщо вказане там маленьке, то це говорить про те, що індекси майже не використовуються в даній базі. А це погано (як у нас ).
Також не забувайте про MySQL Explain оптимізації. За допомогою даної команди СУБД пояснює нам, як оптимальніше побудувати введений запит. Вона вказується на початку запиту на вибірку. Наприклад:
EXPLAIN SELECT * FROM `user_animal`
У стовпці possible_keys MySQL виводяться можливі варіанти індексів, які можна використовувати в даній таблиці. Наприкінці хотілося б зазначити, що «доведення» продуктивності – СУБД справа дуже тонка і скрупульозне. При цьому кожен з движків, що використовує MySQL має свій рецепт «тонкої настройки». Наприклад, принципи оптимізації MySQL в Joomla можуть бути категорично «не підходять» до іншої CMS. І це варто враховувати, інакше вийде не оптимізація, а «конемобиль» якийсь!
