II. RF KMF DokuWiki

Джерело уроку: net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/ ( Дещо змінено. Відредаговано. )

Операції з базою даних дуже часто стають вузьким місцем при реалізації веб проекту. Питання оптимізації в таких випадках стосуються не тільки адміністратора бази даних. Програмістам потрібно правильно виконувати структурування таблиць, писати оптимальні запити і більш продуктивний код. Нижче наводиться невеличкий список технік оптимізації роботи з 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 вирішує не розміщувати даний запит в кеші. Все що потрібно, щоб виправити ситуацію - це додати додаткову рядок коду PHP перед запитом.

2. Використовуйте EXPLAIN для ваших запитів SELECT
Використання ключового слова EXPLAIN може допомогти скласти картину того, що робить MySQL для виконання вашого запиту. Така картина дозволяє легко виявити вузькі місця і інші проблеми в запитах або структурі таблиць.
Результат запиту EXPLAIN показує, які індекси використовуються, як таблиця сканується і сортується, і так далі.
Félkövér szövegFélkövér szöveg Візьмемо запит SELECT (переважно, щоб він був складним, з JOIN), додамо перед ним ключове слово EXPLAIN. Ви можете використовувати PhpMyAdmin для цього. Такий запит виведе результат в таблицю. Припустимо, ми забули додати індекс для стовпця, який використовується для JOIN.
Добавимо індекс для поля group_id. Тепер замість сканування 7883 рядків, будуть скануватися тільки 9 і 16 рядків з двох таблиць. Хорошим методом оцінки продуктивності є множення всіх чисел в стовпці „rows”. Результат приблизно пропорційний обсягу даних.

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

Чи існує користувач з Алабами?

  $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'"); 
  if (mysql_num_rows($r) > 0) { // ….}

Так значно швидше:

  $r = mysql_query("SELECT 1 FROM user WHERE state = 'Alabama' LIMIT 1");
  if (mysql_num_rows($r) > 0) { // …. }

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

  "last_name LIKE 'a%'". 

Коли для пошуку використовується початок рядка, MySQL може використовувати індекс стовпця, по якому проводиться пошук.
Вам також слід розібратися, для яких видів пошуку можна використовувати звичайне індексування. Наприклад, при пошуку слова ( „WHERE post_content LIKE '% apple%'”) переваги індексування будуть не доступні. У таких випадку краще використовувати повнотекстовий пошук mysql або побудова власних рішень на основі індексування.

5. Індексація та використання однакових типів для зв'язування стовпців
Якщо ваш додаток містить багато запитів з JOIN, вам потрібно індексувати стовпці, які зв'язуються в обох таблицях. Це має ефект на внутрішню оптимізацію операцій зв'язування в 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 ");

Обидва стовпці для назви штату повинні бути індексовані і обидва повинні мати однаковий тип і кодування символів інакше MySQL проведе повне сканування таблиці

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

Так робити НЕ ПОТРІБНО:

  $r = mysql_query ( "SELECT username FROM user ORDER BY RAND () LIMIT 1");

Ось так буде краще працювати:

  $r = mysql_query ( "SELECT count (*) FROM user");
  $d = mysql_fetch_row ($r);	 $rand = mt_rand (0, $d[0] - 1);
  $r = mysql_query ( "SELECT username FROM user LIMIT $rand, 1");

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

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

Погано:

 $r = mysql_query ( "SELECT * FROM user WHERE user_id = 1");
 $d = mysql_fetch_assoc ($r);
 echo "Welcome {$d [ 'username']}";

Так краще:

 $r = mysql_query ( "SELECT username FROM user WHERE user_id = 1");
 $d = mysql_fetch_assoc ($r);
echo "Welcome {$d [ 'username']}";

Різниця стає істотною для великих таблиць.

8. Намагайтеся використовувати поле id всюди
Корисною практикою є використання в кожній таблиці поля id, для якого встановлені властивості PRIMARY KEY, AUTO_INCREMENT, і воно має деякий цілочисельний тип.
Рекомендується - UNSIGNED, так як в цьому випадку значення не може бути негативним.
Навіть якщо у вашій таблиці є поле з унікальним ім'ям користувача, не робіть його основним ключем. Поля з типом VARCHAR повільно працюють в якості основних ключів. Також структура вашої бази даних буде кращою, якщо в ній використовувати посилання на записи на підставі id.
Крім того механізм MySQL використовує основні ключі для своїх внутрішніх завдань, і використання поля id створює оптимальні умови для їх вирішення.
Одним можливим винятком з цього правила є „асоціативні таблиці”, які використовуються для відносин багато-до-багатьох між двома іншими таблицями. Наприклад, таблиця „posts_tags” містить 2 колонки: post_id, tag_id. Вони використовуються для опису відносин між двома таблицями „post” і „tags”. Описана таблиця може мати основний ключ, який містить обидва поля id.

9. Використовуйте ENUM замість VARCHAR
Стовпці типу ENUM дуже компактні і швидкі. Вони зберігаються в базі даних як і TINYINT, але ще вони можуть містити рядкові значення. Такі особливості роблять їх відмінними кандидатами для реалізації певних полів.
Якщо у вас є поля, які містять тільки кілька різних видів значень, використовуйте для них ENUM замість VARCHAR.

Наприклад, може бути стовпець з ім'ям „status”, який буде містити тільки такі значення як „active”, „inactive”, „pending”, „expired” і так далі.
MySQL може запропонувати спосіб зміни структури вашої таблиці при виконанні PROCEDURE ANALYSE ().

10. Вивчіть пропозиції PROCEDURE ANALYSE ()
PROCEDURE ANALYSE () дозволяє MySQL аналізувати структуру стовпців і дійсних даних у вашій таблиці і на підставі аналізу видавати пропозиції. Це діє тільки якщо у вашій таблиці є реальні дані, так як їх наявність грає істотну роль при прийнятті рішень.
Наприклад, якщо ви створили поле типу INT для основного ключа, але в таблиці не так багато записів, то MySQL пропонує змінити тип поля на MEDIUMINT.
Або якщо ви використовуєте поле типу VARCHAR, то можете отримати пропозицію конвертувати його в ENUM, якщо в ньому міститься тільки кілька значень.
Ви також можете отримати рекомендації, якщо натиснете посилання „Propose table structure” (Аналіз структури таблиці) в PhpMyAdmin на закладці структури таблиці.
Потрібно тільки пам'ятати, що це всього лише пропозиції. І якщо ваша таблиця буде рости, то вони можуть виявитися невірними. Так що рішення про їх застосування залишається за вами.

11. Використовуйте NOT NULL, якщо це можливо
Якщо немає особливих причин використовувати значення NULL, потрібно завжди використовувати для стовпця властивість NOT NULL.
Запитайте себе, чи є різниця між символом нового рядка і значенням NULL (для полів типу INT: 0 і NULL). Якщо немає причин використовувати обидва значення, то немає необхідності мати поле NULL. (Але Oracle розглядає NULL і порожній рядок як однакові величини?)
Стовпець NULL вимагає додаткової пам’яті і може бути джерелом труднощів для виразів порівнянь. Просто уникайте використання його в міру можливості. Хоча, звичайно, є об'єктивні причини для використання значень NULL в деяких випадках.
З документації MySQL:
„Стовпець NULL вимагає додаткової пам’яті в рядку для запису про можливе значенні NULL. Для таблиць MyISAM кожен стовпець NULL використовує додатковий біт, округлення проводиться до найближчого байта.”

12. Небуферовані запити
Зазвичай, коли ви виконуєте запит з скрипта, то робота скрипта переривається до тих пір, поки запит не буде виконаний. Такий порядок дій можна змінити за допомогою небуферірованних запитів.
Важливе пояснення функції mysql_unbuffered_query () з документації PHP:
„Mysql_unbuffered_query () відправляє SQL запит на сервер MySQL без автоматичного отримання і буферірованія рядків результату, як це робить функція mysql_query (). Таким чином, зберігається певний обсяг пам'яті запитами SQL, які видають великий набір результату, і можна починати працювати з набором результату відразу ж після отримання першого рядка, не чекаючи поки запит SQL буде повністю виконаний.”
Однак існує кілька обмежень. Ви повинні або прочитати всі рядки або викликати mysql_free_result () перед тим, як виконати наступний запит. Також не можна використовувати mysql_num_rows () або mysql_data_seek() для набору результату.

13. Зберігайте IP адресу, як UNSIGNED INT
Багато програмістів створюють поле VARCHAR (15) для зберігання IP адреси, навіть не замислюючись про те, що будуть зберігати в цьому полі цілочисельне значення.
Якщо використовувати INT, то розмір поля скоротиться до 4 байт, і воно буде мати фіксовану довжину.
Потрібно використовувати тип UNSIGNED INT, так як IP адреса задіє всі 32 біта беззнакового цілого.
У запитах можна використовувати функцію INET_ATON () для конвертації IP адреси в ціле, і INET_NTOA () для зворотного процесу. Також є схожі функції PHP: ip2long () і long2ip ().

 $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

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

14. Вертикальний розподіл
Вертикальний розподіл - це дія з розділення структури таблиці по вертикалі з метою оптимізації.
Приклад 1: У вас є таблиця, яка містить домашні адреси, і вони рідко використовуються в додатку. Ви можете розділити вашу таблицю і зберігати адреси в окремій таблиці.
Таким чином основна таблиця користувачів скоротиться в розмірі. А як відомо, менша таблиця обробляється швидше.

Приклад 2: У вас в таблиці є поле „last_login”. Воно оновлюється кожного разу, коли користувач реєструється на сайті. Але кожне оновлення таблиці викликає кешування запиту, що може створити перевантаження системи.
Ви можете виділити дане поле в іншу таблицю, щоб зробити оновлення таблиці користувачів не такими частими.
Але треба бути впевненими в тому, що не буде потрібно постійного зв’язування двох таблиць, які ви тільки що розділили, так як це може призвести до погіршення продуктивності.

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

  while (1) {
    mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
     if (mysql_affected_rows() == 0) {
        // виконуєм  видалення
	break; 
     }
 }

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

17. Вибирайте правильний механізм зберігання даних
Є два основних механізми зберігання даних для MySQL: MyISAM і InnoDB. Кожен має свої переваги і недоліки.

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

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

18. Використовуйте об'єктно-реляційне відображення
Використання об'єктно-реляційного відображення (ORM - Object Relational Mapper) дає ряд переваг.
Все, що можна зробити в ORM, можна зробити вручну, але з великими зусиллями і більш високими вимогами до рівня розробника.
ORM відмінно підходить для „ледачою завантаження”. Це означає, що отримання значень можливо тоді, коли вони потрібні. Але потрібно бути акуратним, тому що можна створити багато маленьких запитів, які знизять продуктивність.
ORM може також об'єднувати ваші запити в транзакції, які виконуються значно швидше, ніж індивідуальні запити до бази даних.
Для PHP можна використовувати ORM Doctrine.

19. Будьте обережні з постійними з'єднаннями
Постійні з'єднання призначені для скорочення втрат на відновлення з'єднань до MySQL. Коли створюється постійне з'єднання, то воно залишається відкритим навіть після завершення скрипта.
Так як Apache повторно використовує дочірні процеси, то процес виконується для нового скрипта, і він використовує теж саме з'єднання з MySQL.

• mysql_pconnect ()
  

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