Оптимізація MySql запитів, Корисні фрукти

Робота з базою даних найчастіше найслабше місце у продуктивності багатьох web додатків. І про це мають дбати не лише адміністратори баз даних. Програмісти повинні вибирати правильну структуру таблиць, писати оптимізовані запити та хороший код. Далі перераховані методи оптимізації роботи з MySQL для програмістів.

1. Оптимізуйте запити для кешу запитів

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

Причина в тому, що перший запит використовується функція CURDATE(). Це стосується всіх функцій, подібних NOW(), RAND() та інших, результат яких недетермінований. Якщо результат функції може змінитися, MySQL не кешує такий запит. У цьому прикладі це можна запобігти обчисленню дати до виконання запиту.

2. Використовуйте EXPLAIN для ваших запитів SELECT

Використовуючи EXPLAIN, можна подивитися, як саме MySQL виконує ваш запит. Це може допомогти вам позбавитися від слабких місць продуктивності та інших проблем у вашому запиті або структурі таблиць. Результат EXPLAIN покаже вам, які використовуються індекси, як вибираються і сортуються таблиці і т.д. він може бути складним, з об'єднаннями) і додайте на початок ключове слово EXPLAIN. Для цього можна використовувати phpmyadmin. В результаті ви отримаєте цікаву таблицю. Наприклад, нехай я забув додати індекс втаблицю, яка бере участь у об'єднанні:

запитів

Після додавання індексу до поля group_id:

фрукти

Тепер замість 7883 рядків, вибираються лише 9 та 16 рядків з двох таблиць. Перемноження всіх чисел у стовпці rows дасть число прямо пропорційне продуктивності запиту.

3. LIMIT 1, коли потрібен єдиний рядок

Іноді, звертаючись до таблиці, ви точно знаєте, що вам потрібний лише один конкретний рядок. Наприклад, потрібно отримати один унікальний рядок або просто перевірити існування записів, що задовольняють запит WHERE. У цьому випадку, додавання LIMIT 1 у ваш запит буде оптимальнішим. Таким чином, база даних зупинить вибірку записів, після знаходження першої ж замість того, щоб вибрати всю таблицю або індекс.

4. Індексуйте поля, якими шукайте

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

mysql

Як ви можете помітити, це правило також застосовується для частини рядків, наприклад - "last_name LIKE 'a%'". При пошуку з початку рядка, MySQL використовує індекс цього стовпця. Ви також повинні розуміти, що це не спрацює для регулярних виразів. Наприклад, коли ви шукаєте слово (тобто "WHERE post_content LIKE '%apple%'"), то від звичайного індексу не буде ніякого користі. Найкраще буде використовувати повнотекстовий пошук або створити власну систему індексації.

5. Індексуйте поля для об'єднання та використовуйте для них однакові типи стовпців

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

6. Не використовуйте ORDER BY RAND()

(Мається на увазі вибірка єдиного рядка. Примітка перекладача)

Це одна з тих речей, які виглядають дуже добре на перший погляд, але багато програмістів-початківців попалися на цю вудку. Ви навіть не уявляєте, яке слабке місце у продуктивності виникне, якщо будете використовувати це в запитах. Якщо вам дійсно потрібен випадковий порядок рядків у запиті, тобто найкращі способи зробити це. Звичайно, це призведе до додаткового коду, але дозволить позбутися слабкого місця у продуктивності, яке звужуватиметься експоненційно при збільшенні даних. Проблема в тому, що MySQL виконуватиме RAND() (а це навантаження на процесор) для кожного рядка при сортуванні, видаючи лише один рядок.

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

7. Уникайте SELECT *

Що більше даних зчитується з таблиці, то повільніше запит. Це збільшує час роботи зі сховищем даних. Також, коли сервер бази даних встановлений окремо від web-сервера, буде велика затримка при передачі даних через мережу. Прописувати, які саме стовпці із запиту вам потрібні — гарна звичка.

8. Намагайтеся завжди створити поле ID

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

ENUM – ​​дуже швидкий та компактний тип поля. Значення в ньому зберігаються так само, як TINYINT, але відображаються як у рядковому полі. Це робить його незамінним у деяких випадках. Якщо у вас є поле, в якомубуде цілком певний набір значень, використовуйте ENUM замість VARCHAR. Наприклад, якщо є поле «status», його значення можуть бути «active», «inactive», «pending», «expired» тощо. Можна навіть отримати від MySQL «пораду» про те, як перебудувати таблицю . Якщо ви маєте поле VARCHAR, MySQL може запропонувати замінити його на ENUM. Для цього використовується PROCEDURE ANALYSE(), наведена нижче.

10. Використовуйте підказки від PROCEDURE ANALYSE()

корисні

Врахуйте, що це лише поради. Якщо ви додасте ще записи, вони можуть стати не актуальними. Зрештою, вам вирішувати — використовувати їх чи ні.

11. Використовуйте NOT NULL, якщо це можливо

Якщо є особливі причини використовувати NULL, використовуйте його. Але перед цим запитайте себе, чи є різниця між порожнім рядком і NULL (для INT - 0 або NULL). Якщо таких причин немає, використовуйте NOT NULL. NULL займає більше місця і до того ж ускладнює порівняння з таким полем. Уникайте його, якщо це можливо. Тим не менш, бувають вагомі причини використовувати NULL, це не завжди погано. З документації MySQL: «Стовпці NULL займають більше місця в записі, через необхідність відзначати, що це значення NULL. Для таблиць MyISAM кожне поле з NULL займає 1 додатковий біт, який округляється до найближчого байта».

12. Prepared Statements

13. Небуферизовані запити

Зазвичай, роблячи запит, скрипт зупиняється і чекає на результат його виконання. Ви можете змінити це, використовуючи небуферизовані запити. Гарний опис є у документації функції mysql_unbuffered_query():

mysql_unbuffered_query() відправляє SQL-запит у MySQL, не виймаючи і не автоматично буферизуючи результуючі ряди, як це робить mysql_query(). З одного боку, це зберігає значнекількість пам'яті для SQL-запитів, які дають великі результуючі набори. З іншого боку, ви можете розпочати роботу з результуючим набором зрізу після отримання першого ряду: вам не потрібно очікувати на виконання повного SQL-запиту»

Проте є певні обмеження. Вам доведеться зчитувати всі записи або викликати mysql_free_result(), перш ніж ви зможете виконати інший запит. Також ви не можете використовувати mysql_num_rows() або mysql_data_seek() для результату функції.

14. Зберігайте IP у UNSIGNED INT

15. Таблиці фіксованого розміру (статичні) - швидше

Якщо кожна колонка в таблиці має фіксований розмір, така таблиця називається "статичною" або "фіксованого розміру". Приклад стовпчиків не фіксованої довжини: VARCHAR, TEXT, BLOB. Якщо включити в таблицю таке поле, вона перестане бути фіксованою і оброблятиметься MySQL по-іншому. Використання таких таблиці збільшить ефективність, т.к. MySQL може переглядати записи у них швидше. Коли потрібно вибрати потрібний рядок таблиці, MySQL може дуже швидко визначити її позицію. Якщо розмір запису не фіксований, її пошук відбувається за індексом. Так само ці таблиці простіше кешувати та відновлювати після падіння бази. Наприклад, якщо перевести VARCHAR(20) в CHAR(20), запис буде займати 20 байтів, незалежно від її реального змісту.

16. Вертикальний поділ

17. Розділяйте великі запити DELETE та INSERT

Якщо вам потрібно зробити великий запит на видалення або вставку даних, потрібно бути обережним, щоб не порушити роботу програми. Виконання великого запиту може заблокувати таблицю та призвести до неправильноїроботі всієї програми. Apache може виконувати кілька паралельних процесів одночасно. Тому він працює більш ефективно, якщо скрипти виконуються якнайшвидше. Якщо ви блокуєте таблиці на тривалий термін (наприклад, на 30 секунд або довше), то при великій відвідуваності сайту, може виникнути велика черга процесів та запитів, що може привести до повільної роботи сайту або навіть падіння сервера. Якщо у вас є такі запити, використовуйте LIMIT, щоб виконувати їх невеликими серіями.

18. Маленькі стовпці швидше

Для бази даних робота з жорстким диском, можливо, є найслабшим місцем. Маленькі та компактні записи зазвичай краще з погляду продуктивності, т.к. зменшують роботу з диском. У документації до MySQL є список вимог до сховищ даних для всіх типів даних. Якщо ваша таблиця зберігатиме мало рядків, то немає сенсу робити основний ключ типом INT, можливо краще буде зробити його MEDIUMINT , SMALLINT і навіть TINYINT. Якщо вам не потрібно зберігати час, використовуйте DATE замість DATETIME. Проте будьте обережні, що б не вийшло як з Slashdot.

19. Вибирайте правильний тип таблиці

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

20. Використовуйте ORM

Використовуючи ORM, можна отримати певну оптимізацію роботи. Все, що можна зробити за допомогою ORM, можна зробити вручну. Але це вимагає додаткової роботи та вищого рівня знань. ORM чудовий для «лінивої» завантаження даних. Це означає вибірку даних у міру потреби. Але необхідно бути обережним, тому що це може призвести до появи безлічі маленьких запитів, що призведе до зниження продуктивності. . Я вже писав статтю про встановлення Doctrine у ​​CodeIgniter.

21. Будьте обережні з постійними з'єднаннями

Постійні з'єднання призначені зменшення витрат на встановлення зв'язку з MySQL. Коли з'єднання створюється, воно залишається відкритим після завершення роботи скрипту. Наступного разу цей скрипт скористається тим самим з'єднанням. mysql_pconnect() в PHP Але це звучить добре тільки в теорії. З мого особистого досвіду (і досвіду інших) використання цієї можливості не виправдовується. У вас будуть серйозні проблеми з обмеженням за кількістю підключень, пам'яттю і так далі. Apache створює багато паралельних потоків. Це основна причина, чому постійні з'єднання не працюю так добре, як хотілося б. Перед використанням mysql_pconnect() порадьтеся з сисадміном.