10 простих порад з оптимізації MySQL

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

Утиліти вимірювання продуктивності

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

Популярними утилітами, які вам допоможуть, будуть SuperSmack, AB (з утиліт Apache), SysBench, DBench, TioBench - вам варто на них подивитися і вибрати найбільш підходящу.

Отримання "внутрішньої" інформації про роботу сервера

За допомогою перерахованих вище утиліт ви зможете генерувати тисячі запитів до вашого сайту, створюючи для нього високе навантаження. Тепер залишається визначити вузькі місця в продуктивності. На що саме ваше додаток витрачає найбільше часу? Потужність процесора, наявність вільної оперативної пам'яті, продуктивність операцій файлового IO  - все це і багато іншого впливають на швидкість вашого скрипта, але в цій статті мова піде тільки про сервер MySQL.

Після того як будуть знайдені найповільніші запити настає черга внутрішніх утиліт MySQL, таких як команди EXPLAIN, SHOW STATUS, SHOW PROCESSLIST. Це дасть відповідь на питання - які ресурси витрачаються, де і до яких побічних ефектів це призводить. Крім того стануть в нагоді такі команди як top, procinfo, vmstat, які дозволяють отримати більш широку картину того, що відбувається на сервері.

3. Проектування бази даних

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

Пам'ятайте, що розмір таблиці визначається (приблизно) як розмір_одного_запісу * кількість_записів_в_таблиці. Намагайтеся не збільшувати розміру запису без необхідності. У тих випадках, коли дані мають фіксований розмір (серія і номер паспорта, податковий номер) краще відмовитися від типу VARCHAR.

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

4. Розділяйте таблиці на кілька частин

Часто буває що в таблиці дані з деяких колонок запитуються частіше (приклад для блогу - заголовки статей можуть відображатися на багатьох сторінках, але повний текст статті буде виводитися набагато рідше). У такому випадку буде корисним поділ:

CREATE TABLE posts (

id int UNSIGNED NOT NULL AUTO_INCREMENT,

author int UNSIGNED NOT NULL,

posted timestamp NOT NULL,

PRIMARY KEY(id)

);

 

CREATE TABLE posts_content (

post int UNSIGNED NOT NULL.

content text,

PRIMARY KEY(post)

);

У цьому прикладі дані розділені на дві частини: частину, до якої часто йдуть звернення, і менш популярна частина. Це дає можливість оптимізувати першу з таблиць для читання. Також часто змінювані дані також можуть бути винесені на окрему таблицю і це дозволить серверу MySQL більш ефективно управляти кешем даних.

5. Уникайте створення зайвих первинних ключів.

З одного боку, штучні первинні ключі корисні, тому що роблять базу даних більш стійкою. З іншого боку у більшості випадків достатньо лише звичайних ключів. подивимося на приклад таблиці, яка має відношення багато-до-багатьох: Так краще не робити!

1

2

3

4

5

6

7

CREATE TABLE posts_tags (

 relation_id int UNSIGNED NOT NULL AUTO_INCREMENT,

 post_id int UNSIGNED NOT NULL,

 tag_id int UNSIGNED NOT NULL,

 PRIMARY KEY(relation_id),

 UNIQUE INDEX(post_id, tag_id)

);

1

2

3

4

5

REATE TABLE posts_tags (

 post_id int UNSIGNED NOT NULL,

 tag_id int UNSIGNED NOT NULL,

 PRIMARY KEY(post_id, tag_id)

);