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

Робіть запити MySQL зручними для кешування

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

// цей запит MySQL закешувати не зможе
$res = mysql_query(“SELECT username FROM user WHERE signup_date >= CURDATE()”);
// можна зробити інакше
$today = date(“Y-m-d”);
$res = mysql_query(“SELECT username FROM user WHERE signup_date >= ‘$today'”);

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

Перегляньте як виконується ваш запит за допомогою синтаксису EXPLAIN

Подивитися, як MySQL виконує ваш запит можна за допомогою синтаксису EXPLAIN. Його використання може допомогти визначити слабкі місця в продуктивності запиту, а так само в структурі таблиць. В якості результату запиту EXPLAIN поверне дані, які покажуть, які використовуються індекси, яким чином вибираються дані з таблиць, як сортуються, і т. д. Для цього досить додати спочатку SELECT-запиту ключове слово EXPLAIN, після чого буде показана таблиця з даними.

Коли вам потрібна одна запис, виставляйте LIMIT 1

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

// запит міста з кодом Shymkent з бази
$res = mysql_query(“SELECT * FROM location WHERE city = ‘Shymkent'”);
if (mysql_num_rows($res) > 0){
}
// додаємо LIMIT 1 для оптимізації запиту
$res = mysql_query(“SELECT * FROM location WHERE city = ‘Shymkent’ LIMIT 1”);
if (mysql_num_rows($res) > 0){
}

Індексуйте поля за якими проводиться пошук

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

// тут спрацює індекс
city LIKE ‘shym%’
// тут же індекс задіяний не буде
city LIKE ‘%shymkent%’

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

Індексуйте поля за якими об’єднуються таблиці

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

Знайдіть альтернативу замість ORDER BY RAND()

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

Використовуйте вибірку конкретних полів, замість SELECT *

Не лінуйтеся вказувати конкретні потрібні поля в запиті при вибірці, замість використання «*» — вибірка всіх полів, справа в тому, що чим більше даних зчитується з таблиці, тим повільніше ставати ваш запит.

Додавайте поле ID для всіх таблиць

Кожна таблиця в гарному її виконання повинна мати поле id типу INT, яке є первинним ключем (PRIMARY_KEY), і AUTO_INCREMENT. Крім того, для поля потрібно вказати параметр UNSIGNED, який означає те, що значення завжди буде позитивним.
В MySQL є внутрішні операції, які можуть використовувати первинний ключ, це грає роль для складних конфігурацій баз даних, таких як кластери, розпаралелювання, і т. д.
Крім того, якщо є кілька таблиць, і необхідно виконати об’єднаний запит, то тут ID таблиць виявиться досить до речі.

ENUM як альтернатива VARCHAR

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

Використовуйте значення NOT NULL замість NULL

Поля NULL займають більше місця в запису, з-за того, що виникає необхідність відзначати це NULL значення. Таблиці MyISAM, поля з NULL зберігаються таким чином, що кожне поле займає 1 додатковий біт, який округляється до найближчого байта. Якщо використання NULL в полі не принципово, то рекомендується використовувати NOT NULL.

Користуйтеся Prepared Statements

Prepared Statements (підготовлені вирази, їх так само називають зв’язані змінними) — це частина функціональності SQL-баз даних, призначена для відділення даних запиту і власне виконується SQL-запиту. Його використання має переваги в плані безпеки і продуктивності. Фільтруючи значення даних, доданих до запиту, prepared statements таким чином захищає базу даних від SQL ін’єкцій. Зрозуміло, робити подібні перевірки можна і вручну, однак у цьому випадку є ймовірність допустити помилку через неуважність. Останні версії MySQL компілюють prepared statements в бінарну форму, це дозволяє підвищити ефективність його роботи. Виконуючи безліч однотипних запитів в додатку, MySQL буде розбирати запит тільки один раз. На перших етапах prepared statements не мало можливості кешуватися в MySQL, це було вагомою причиною для його ігнорування і відсутності бажання використовувати в своїх проектах. Однак починаючи з версії MySQL 5.1 ситуація кардинально змінилася.

Користуйтеся mysql_unbuffered_query

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

Ви можете зберігати IP-адреси в поле з типом INT (UNSIGNED)

Для зберігання IP-адрес в звичному вигляді багато зберігають у таблиці з полем типу VARCHAR(15), і лише не багато хто використовує цілочисельний тип для цього. Плюси в тому, тип INT займає 4 байти і має фіксований розмір поля. Поле типу INT має бути UNSIGNED, тобто цілочисельним, у запиті слід використовувати функцію INET_ATON(), яка буде конвертувати IP-адресу в число. Зворотне перетворення виконується за допомогою функції INET_NTOA().

$res = “UPDATE hosts SET ip = INET_ATON(‘{$_SERVER[‘REMOTE_ADDR’]}’) WHERE id = $host_id”;

Використовуйте статичні таблиці

Статична таблиця це звичайна таблиця в базі, за виключенням того, що кожне поле таблиці має фіксований розмір. Якщо в таблиці є колонки, не фіксованої довжини, наприклад, це можуть бути: VARCHAR, TEXT, BLOB, вона перестає бути статичною, і буде оброблятися MySQL трохи інакше. Статичні таблиці, або їх можна ще назвати таблицями фіксованого розміру працюють швидше не статичних. Записи з таких таблицях будуть проглядатися швидше, при необхідності вибору потрібної рядки MySQL швидко розрахувати її позицію. Якщо поле не має фіксований розмір, то у цьому випадку пошук проводитися за індексом. Є й інші плюси використання статичних таблиць, справа в тому, що ці таблиці простіше кешуються, а так само відновлюються після падіння бази даних.

Використовуйте вертикальне поділ

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

Розділяйте об’ємні запити INSERT і DELETE

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

while (1){
mysql_query(“DELETE FROM logs WHERE log_date <= ‘2015-07-20’ LIMIT 1000”);
if (mysql_affected_rows() == 0){
// запису видалені успішно
break;
}
usleep(50000); // робимо невеличку паузу
}

Прагніть використовувати поля невеликого розміру

Як відомо дані бази зберігаються на жорсткому диску, це найчастіше це може виявитися одним з слабких місць у веб-додатку. Справа в тому, що записи невеликого розміру є більш переважними, оскільки використання їх зменшує роботу з жорстким диском. Якщо ви впевнені, що конкретна таблиця буде зберігати мало рядків, то раціональним рішенням буде використання типів полів, з мінімальними можливими значеннями. Наприклад, якщо основний ключ має тип INT, і ви будете зберігати в таблиці лише невелику кількість даних, то краще зробити його типу MEDIUMINT, SMALLINT або навіть TINYINT.

Вибирайте тип таблиць під свої завдання

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

Додав: htmaker, 19.07.2015 р.
(2 голосів, середній: 4,50 із 5)

Завантаження…

Діліться з друзями:

См. також:


Змінюємо пароль root в MySQL 5.7
Рубрика: Linux, MySQL

Виправлення помилок у базі MySQL
Рубрика: MySQL

Відновлення пароля root в MySQL
Рубрика: MySQL

Утиліта Mytop для MySQL
Рубрика: Linux, MySQL

Кількість запитів до MySQL в режимі реального часу
Рубрика: MySQL

Як дізнатися час виконання MySQL запиту?
Рубрика: MySQL

Повнотекстовий індекс: Full text index
Рубрика: MySQL

Вставлення стовпців: ALTER TABLE ADD
Рубрика: MySQL

Відновлення таблиць MyISAM після збою
Рубрика: Linux, MySQL

Коментарі

  • Alex’S пише:
    17.03.2017 в 20:08

    Хороша і корисна стаття! Для початківців саме-то!

    Відповісти