10 Кращих прийомів для оптимізації роботи з mysql, mysql

1. LIMIT 1 Коли потрібно витягти з таблиці унікальну рядок

Іноді, формуючи запит, ви вже знаєте, вам потрібна тільки одна унікальна рядок в таблиці. Ви можете сформувати вибірку за унікальною запису. Або ви можете просто запустити перевірку на існування будь-якої кількості записів, які задовольняють вашому умові.

У таких випадках, використання методу LIMIT 1 може істотно збільшити продуктивність:

2. Оптимізація роботи з базою за допомогою обробки кешу запитів

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

Коли один і той же запит виконується кілька разів, то, результат буде отримано з кешу. Без необхідності обробляти знову все таблиці. Це значно прискорює процес.

3. Індексація полів пошуку

Індекси призначені не тільки для присвоєння первинного або унікальному ключам. Якщо в таблиці є стовпці, по яким ви проводите пошук, їх практично в обов'язковому порядку слід індексувати.

Як ви розумієте, це правило також поширюється на частину рядка пошуку: таку як "l ast_name LIKE '%'". Коли пошук здійснюється по початку рядка, MySQL може використовувати для цього стовпця індексацію.

Ви також повинні розуміти, які види запитів не можуть використовувати звичайні індекси. Наприклад, при пошуку слова (наприклад, "WHERE post_content LIKE '% tomato%'"), застосування звичайного індексу вам нічого не дасть. В такому випадку краще буде використовувати пошук MySQL на повну відповідність або створити свій власний індекс.

4. Індексація та використання стовпців однакового типу при об'єднанні

Якщо ваш додаток містить багато запитів на об'єднання, необхідно переконатися, що стовпчики в обох таблицях, які ви поєднуєте, проіндексовані. Це впливає на оптимізацію внутрішніх операцій MySQL по об'єднанню.

Крім того, стовпці, які об'єднуються, повинні бути однакового типу. Наприклад, якщо ви поєднуєте стовпець типу DECIMAL з однієї таблиці і стовпець типу INT з іншого, MySQL не зможе використовувати принаймні один з індексів.

Навіть кодування символів повинна бути того ж типу для відповідних рядків об'єднуються стовпців.

5. По можливості не використовуйте запити типу SELECT *

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

6. Будь ласка, не використовуйте метод сортування ORDER BY RAND ()

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

Якщо вам дійсно потрібно впорядкувати деякі рядки в результатах пошуку, тобто набагато ефективніші способи зробити це. Припустимо, що вам потрібно додати додатковий код до запиту, але через даної пастки ви не зможете цього зробити, що призведе до зменшення ефективності обробки даних, в міру того, як база буде розростатися в розмірах.

Проблема в тому, що MySQL буде виконувати операцію RAND () (яка використовує обчислювальні ресурси сервера) перед сортуванням для кожного рядка в таблиці. При цьому вибиратися буде всього один рядок.

Таким чином, ви виберете меншу кількість результатів пошуку, після чого зможете застосувати метод LIMIT, описаний в пункті 1.

7. Використовуйте стовпці типу ENUM замість VARCHAR

Якщо у вас є деяке поле, яке містить кілька різних значень одного виду, то замість стовпців типу VARCHAR краще використовувати ENUM. Наприклад, це може бути стовпець «Статус», який містить тільки такі значення, як «активно», «неактивно», «очікування», «термін дії закінчився» і т.д.

Існує навіть можливість задати сценарій, при якому MySQL буде «пропонувати» змінити структуру таблиці. Коли у вас є поле типу VARCHAR, система може автоматично рекомендувати змінити формат стовпця на ENUM. Це можна зробити за допомогою виклику функції PROCEDURE ANALYSE ().

8. Вертикальне секціонування (поділ)

Вертикальне секціонування представляє собою процес, коли структура таблиці розділяється по вертикалі з міркувань оптимізації роботи з базою даних.

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

Приклад 2: У вас в таблиці є поле «last_login» (останній логін). Нове воно кожного разу, коли користувач входить в систему під своїм ім'ям користувача. Але кожна зміна таблиці записується в кеш запитів до цієї таблиці, який зберігається на диску. Ви можете перемістити це поле в іншу таблицю, щоб зменшити кількість звернень до вашої основної таблиці користувачів.

Однак ви повинні бути впевнені в тому, що обидві таблиці, які вийшли після секціонування, не будуть у подальшому використовуватися однаково часто. В іншому випадку це суттєво знизить продуктивність.

9. Менші стовпці - швидше

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

В MySQL Docs прописано низку вимог до зберігання різних типів даних. Якщо очікується, що таблиця нічого очікувати утримувати занадто велика кількість записів, то немає причин зберігати первинний ключ в полях типу INT, MEDIUMINT, SMALLINT. а в окремих випадках навіть TINYINT. Якщо в форматі дати вам не потрібні складові часу (годинник. Хвилини), то використовуйте поля типу DATE замість DATETIME.

Однак все ж переконайтеся, що на перспективу ви залишили собі досить простору для розвитку. Інакше в якийсь момент може статися щось типу обвалу.

10. Правильно вибирайте движок

Існує два основних движка баз даних MySQL: MyISAM і InnoDB. У кожного є свої переваги і недоліки.
MyISAM хороший для роботи з «важкими» додатками, але гірше справляється з обробкою даних, якщо в базі велика кількість записів.

Навіть якщо ви оновлюєте всього одне поле однієї з рядків, вся таблиця може бути заблокована, і ніякий інший процес не зможе отримати до неї доступ, поки не завершиться цей запит. У той же час MyISAM дуже швидкий в обробці запитів типу SELECT COUNT (*).

InnoDB - це більш складний механізм і може повільніше, ніж MyISAM працювати з більшістю додатків. Але він підтримує функції, які дозволяють більш ефективно працювати з базами великих розмірів.

Переклад статті «10 MySQL Best Practices for Optimization» був підготовлений дружною командою проекту Сайтобудування від А до Я.