32 ради по оптимізації продуктивності MySQL

База даних MySQL - легка, надійна, але, разом з тим, повнофункціональна серйозна СУБД. Простота MySQL забезпечує низький рівень входження розробників, проте згодом вони не особливо схильні змінювати стратегію роботи з СУБД. Саме для розробників з досвідом використання MySQL на практиці орієнтована наша стаття.

Велика частина матеріалу є перекладом статті 32 Tips To Speed ​​Up Your MySQL Queries.

1. Використовуйте постійне з'єднання з базою даних, щоб уникнути системних витрат.
2. Перевірте, щоб на шпальтах з високою кількістю унікальних елементів був PRIMARY KEY. Наприклад, у стовпці `gender` є всього 2 варіанти (male та female). Унікальний ID користувача, навпаки, містить велику кількість значень і підходить для того, щоб стати первинним ключем.
3. Бажано, щоб всі зв'язки між таблицями були з індексами (що має на увазі, що у них повинні бути однакові типи даних, завдяки цьому запити будуть швидше). Також перевірте, щоб поля, в яких необхідно робити пошук (часто з'являються в виразах WHERE, ORDER BY або GROUP BY) мали індекси. Але не додавайте занадто багато індексів: найгірше, що ви можете зробити, це додати індекс кожному колонку в таблиці (я не бачив більше 5 індексів навіть в таблиці з 20-30 стовпцями). Якщо ви ніколи не порівнюєте стовпець з іншими даними і не проводите по ньому пошук, нема чого ставити на ньому індекс.
4. Використовуйте якомога простіші привілеї, коли ви виконуєте команду GRAND, щоб зменшити витрати через перевірки привілеїв під час підключення до бази.
5. Використовуйте менше RAM на рядок, точно визначаючи необхідну довжину стовпців. (Наприклад, для зберігання пароля в md5 потрібно відводити рівно 32 символу, більше не має сенсу. Просто, але багато хто про це забувають.)
6. В MySQL ви можете визначити індекс відразу на декількох колонках одночасно. При цьому ви можете використовувати крайній зліва стовпець як окремий індекс, таким чином зменшивши кількість окремих індексів.
7. Якщо ваш індекс складається з декількох стовпців, чому б не зробити хеш стовпець з індексом, який буде коротким і досить унікальним? Тоді ваш запит може бути схожий на цей: SELECT * FROM table WHERE hash_column = MD5 (CONCAT (col1, col2)) AND col1 = 'aaa' AND col2 = 'bbb';
8. Передбачте запуск ANALYZE TABLE (або myisamchk --analyze з командного рядка) на таблиці після того, як ви заповнили її даними, щоб допомогти MySQL оптимізувати запити
9. Використовуйте тип CHAR, коли це можливо (замість VARCHAR, BLOB або TEXT) - коли у значень стовпця є постійна довжина: хеш MD5, код аеропорту і подібні дані. Дані в шпальтах CHAR можуть бути знайдені швидше, ніж в шпальтах з іншими типами даних.
10. Не варто ділити таблицю тільки через те, що в ній занадто багато стовпців. При доступі до рядка це не має значення
11. Стовпець повинен бути оголошений як NOT NULL, якщо в ньому дійсно немає порожніх клітинок - таким чином ви злегка прискорите прохід по таблиць
12. Якщо Ви зазвичай отримуєте рядки в одному і тому ж порядку, наприклад, expr1, expr2 ..., зробіть запит: ALTER TABLE ... ORDER BY expr1, expr2 ... щоб оптимізувати таблицю.
13. Не використовуйте цикл в PHP, створюючи безліч запитів. Замість цього спробуйте такий запит: SELECT * FROM `table` WHERE` id` IN (1,7,13,42);
14. Використовуйте значення стовпця за замовчуванням, і вставляйте тільки ті значення, які відрізняються від звичайного. Це зменшує час розбору запиту.
15. Використовуйте INSERT DELAYED або INSERT LOW_PRIORITY для MyISAM, коли MySQL використовується для ведення журналів. Крім того, якщо ви працюєте з MyISAM, ви можете додати опцію DELAY_KEY_WRITE = 1 - це дозволить швидше оновлювати індекси, так як вони не будуть записуватися на диск, поки файл не закриється.
16. Дані сесії користувачів (або будь-які інші не дуже важливі дані) в таблиці типу MEMORY - це значно скоротить час доступу до бази.
17. Якщо вам необхідно зберегти велику кількість текстових даних, зверніть увагу на тип BLOB, який підходить для зберігання стислих даних (COMPRESS () в MySQL виглядає повільним, можна подивитися на gzipping в PHP). Звичайно, попередньо потрібно перевірити продуктивність цього рішення.
18. Зображення, відео та інші файли на сайті зазвичай зберігаються в файлової системі. Тому не варто зберігати їх в базі даних (в полях BLOB), досить лише дати на нього посилання в таблиці.
19. Якщо вам часто доводиться обчислювати функцію COUNT або SUM, засновану на великій кількості рядків (оцінки статей, кількість голосів в опитуванні, кількість зареєстрованих користувачів, і тому подібне), має сенс створити окрему таблицю і оновлювати лічильник в режимі реального часу, що буде набагато швидше . Якщо вам потрібно зібрати статистику з величезних таблиць реєстрації, використовуйте зведену таблицю замість того, щоб кожен раз переглядати таблицю цілком.
20. Не використовуйте REPLACE (який на ділі є DELETE + INSERT і витрачає ID'и): використовуйте натомість INSERT ... ON DUPLICATE KEY UPDATE (тобто INSERT + UPDATE, якщо стався конфлікт). Ця ж техніка може використовуватися, коли вам спочатку потрібно зробити SELECT, щоб дізнатися, чи є вже дані в базі, і потім вибрати INSERT або UPDATE. Навіщо вирішувати самому - покладайтеся на базу даних!
21. Налаштуйте кешування MySQL: виділіть досить пам'яті для буфера (наприклад, SET GLOBAL query_cache_size = 1000000), і визначте query_cache_min_res_unit в залежності від середнього розміру повертаються даних в запиті.
22. Розділіть складні питання на кілька простіших - у них більше шансів бути закеширувалася, відповідно - більш швидкими.
23. Групуйте кілька подібних INSERT'ов в одному довгому зі списком VALUES, щоб вставити кілька рядків за один раз: запит виконається швидше через те, що час з'єднання, посилки і розбору запиту приблизно в 5-7 разів більше, ніж фактична вставка даних ( в залежності від довжини рядка). Якщо це не можливо, використовуйте START TRANSACTION і COMMIT, за умови, що ви працюєте з InnoDB. Інакше користуйтеся LOCK TABLES - це скорочує час, так як буфер індексу скидається на диск тільки один раз, після того, як всі оператори INSERT були виконані. При цьому не забувайте розблокувати таблиці приблизно через 1000 вставлених рядків, щоб дати іншим потокам доступ до таблиці.
24. Завантажуючи таблицю з текстового файлу, використовуйте LOAD DATA INFILE, це в 20-100 разів швидше.
25. Знаходьте вузькі місця в додатку і досліджуйте їх. Так ви зможете знайти запити з високим часом виконання, які не використовують індекси, а також повільні вираження, такі як OPTIMIZE TABLE і ANALYZE TABLE.
26. Налаштуйте параметри сервера бази даних. Наприклад, збільшивши розмір буфера.
27. Якщо у вашому додатку багато DELETE'ов або оновлень динамічних форматів рядків (якщо в рядку є стовпець типу VARCHAR, BLOB або TEXT, у рядки є динамічний формат), запускайте щотижня по крону OPTIMIZE TABLE. Дефрагментація сприяє підвищенню швидкості запитів. Якщо ви не використовуєте реплікацію, додайте ключове слово LOCAL, щоб дефрагментація займала менше часу.
28. Не використовуйте ORDER BY RAND (), щоб отримати кілька випадкових рядків. Отримайте 10-20 записів (останні за часом додавання або ID) і зробіть array_random () на стороні PHP. Є й інші рішення.
29. Постарайтеся уникати вираження HAVING, коли це можливо.
30. У більшості випадків вираз DISTINCT можна розглянути як особливий випадок GROUP BY; таким чином, оптимізація, застосовна до запитів GROUP BY, може бути також застосована до запитів з виразом DISTINCT. Крім того, якщо ви використовуєте DISTINCT, постарайтеся використовувати LIMIT (MySQL зупиняється, як тільки знаходить row_count унікальних рядків), і уникайте ORDER BY (у багатьох випадках він вимагає тимчасової таблиці).
31. Коли я прочитав "Building scalable web sites", я зрозумів, що іноді необхідно де-нормалізувати деякі таблиці (до речі, так робить Flickr), тобто дублювати деякі дані в декількох таблицях, щоб уникнути JOIN'ов, які можуть дорого обійтися.
32. Якщо ви хочете протестувати якусь функцію або вираз в MySQL, використовуйте для цього BENCHMARK.

Починаю її з перекладу статті 32 Tips To Speed ​​Up Your MySQL Queries .