Типу блог 20 хороших порад по mysql

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

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

// клопотання не буде кешувати
$ R = mysql_query ( "SELECT username FROM user WHERE signup_date> = CURDATE ()");
// а так буде!
$ Today = date ( "Y-m-d");
$ R = mysql_query ( "SELECT username FROM user WHERE signup_date> = '$ today'");

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

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

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

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

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

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

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

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

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

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

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

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

// вибірки компаній в штаті користувача
$ R = mysql_query ( "SELECT company_name FROM users
LEFT JOIN companies ON (users.state = companies.state)
WHERE users.id = $ user_id ");
// обидві колонки state повинні бути проіндексованості
// вони обидві повинні мати один тип даних і кодування символів
// а інакше MySQL зробить повну вибірку з цих таблиць

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

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

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

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

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

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

// не дуже добре:
$ R = mysql_query ( «SELECT * FROM user WHERE user_id = 1»);
$ D = mysql_fetch_assoc ($ r);
echo «Welcome»;

// краще:
$ R = mysql_query ( «SELECT username FROM user WHERE user_id = 1»);
$ D = mysql_fetch_assoc ($ r);
echo «Welcome»;

// різниця більш значна при більшому наборі даних.

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 значення. Для таблиць InnoDB, кожне поле з NULL займає 1 додатковий біт, який округляється до найближчого байта ».

12. Prepared Statements

// створюємо a prepared statement
if ($ stmt = $ mysqli -> prepare ( "SELECT username FROM user WHERE state =?"))
// прив'язуємо значення
$ Stmt -> bind_param ( "s". $ State);
// виконуємо
$ Stmt -> execute ();
// прив'язуємо результат
$ Stmt -> bind_result ($ username);
// отримуємо дані
$ Stmt -> fetch ();
printf ( "% s is from% s \ n". $ username. $ state);
$ Stmt -> close ();
>

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

20. іспользутся ORM

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

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

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