MySQL прискорення SQL запитів

З часом інформації на сайті стає більше, оскільки сайт постійно розвивається і збільшується в обсягах, особливо це стосується інтернет магазинів, де постійно додаються нові товари з описом та характеристиками.Раз зростає сайт - збільшується база даних, та й як наслідок час виконання SELECT запитів.При цьому відчувається гальмування швидкості роботи сайту або інтернет магазину.

За досвідом скажу, що досить частопри розробці інтернет-магазинуне враховувалися методи оптимізації бази даних, не проставлені індекси, не оптимізовано SQL запити, велика надмірність.

Розглянемо основні методи оптимізації запитів у MySQL

• Використовуйте постійне з'єднання з базою даних, щоб уникнути витрат часу на перез'єднання.
• Перевірте, щоб на стовпцях (в основному унікальних ідентифікаторах ID) було вказано первинний ключ PRIMARY KEY.
• Всі зв'язки між таблицями LEFT (RIGHT) JOIN, INNER JOIN були з індексами + вони повинні мати однакові типи даних.Також індекси необхідно поставити на поля, за якими проводиться відбір або пошук, у виразах WHERE, ORDER BY або GROUP BY.На поля, які не відчувають у зв'язках чи пошуку - індекс ставити НЕ ПОТРІБНО!
• Використовуйте менше пам'яті, що виділяється на полі.Наприклад, для зберігання прізвища достатньо 70 символів varchar(70), не потрібно ставити 255
• У MySQL ви можете визначити складовий індекс одразу на кількох полях одночасно.Це робиться при одночасному пошуку по 2 і більше полях.
• Також можна скористатися хеш стовпцем з індексом, наприклад, якщо ваш індекс складається з декількох полів.Приклад такого запиту: SELECT * FROM mytable WHERE hash_col = MD5 (CONCAT (colum1, colum2)) AND colum1 = 'aaa' AND colum2 = 'bbb';
• Оптимізувати запити допоможе запуск ANALYZE TABLE (або myisamchk --analyze із командного рядка) на таблиці після того, як ви заповнили її даними.
• Якщо поле порожніх осередків воно має бути оголошено як NOT NULL — таким чином ви трохи прискорите обхід таблиці.
• Для відбору відразу кількох значень з масиву наприклад використовуйте такий запит: SELECT * FROM `mytable` WHERE `id` IN (1,3,7,22);
• При частому використанні функції COUNT або SUM у таблицях з великою кількістю рядків можна створити окрему таблицю та оновлювати лічильник у ній.
• Налаштуйте кешування MySQL: виділіть достатньо пам'яті для буфера (наприклад, SET GLOBAL query_cache_size = 31М), і визначте query_cache_min_res_unit в залежності від середнього розміру даних, що повертаються в запиті.Не слід ставити велике значення query_cache_size, оскільки буде швидко витрачатися оперативна пам'ять.
• Розділіть складні питання на дещо простіших, частину обробки можна перенести на рівень PHP та закешувати результати.
• Групуйте кілька подібних INSERT'ів в одному довгому зі списком VALUES, щоб вставити кілька рядків за один раз: запит виконається швидше через те, що час з'єднання, посилки та розбору запиту приблизно в 5-7 разів більший, ніж фактична вставка даних ( залежно від довжини рядка).Якщо це неможливо, використовуйте START TRANSACTION і COMMIT, за умови, що ви працюєте з InnoDB.Інакше користуйтеся LOCK TABLES - це скорочує час, оскільки буфер індексу скидається на диск лише один раз, після того як всі оператори INSERT були виконані.При цьому не забувайте розблокувати таблиці приблизно через 1000 рядків, щоб дати іншим потокам доступ до таблиці.
• Знаходьте вузькі місця в додатку та досліджуйте їх.Так ви зможете знайти запити з високим часом виконання, які не використовують індекси, а також повільні вирази, такі як OPTIMIZE TABLE та ANALYZE TABLE.