Оптимізація швидкості вибірки даних

1. Вступ

А тепер перейдемо від теорії до практики.

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

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

Адже часто, щоб відобразити одну сторінку, потрібно виконати кілька десятків запитів до бази. І якщо вони виконуються послідовно, то ліміт у тебе уже не секунда, а скажімо, 100мс на запит.

Нижче я наведу 5 основних способів, якими програмісти прискорюють роботу запитів до бази даних:

1. Додаємо індекси до таблиць у базі даних.
2. Переписуємо та оптимізуємо запити.
3. Включаємо (і налаштовуємо) кешування на боці БД.
4. Включаємо кешування на стороні клієнта.
5. Виконуємо денормалізацію бази даних.

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

2. Індекси

Ні для кого не секрет, що робота з базою даних займає більшу частину роботи практично будь-якого сайту. І саме робота з БД найчастіше є вузьким місцем вебзастосунків.

У цій статті хотілося б дати практичні поради щодо використання MySQL.

Одразу зазначу:

• ця стаття написана про MySQL, хоча загальні речі найімовірніше справедливі для будь-якої СУБД.
• все написане у статті є моєю особистою точкою зору, і не є істиною в останній інстанції.
• поради не претендують на новизну і є результатом узагальнення прочитаної літератури та особистого досвіду.
• у межах цієї статті я не торкатимусь питань конфігурування MySQL.

Проблеми під час використання MySQL можна розділити на три групи (у порядку значимості):

1. Невикористання або неправильне використання індексів.
2. Неправильна структура БД.
3. Неправильні \ неоптимальні SQL запити.

Зупинимося на кожній із цих груп докладніше.

Використання індексів

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

Поради щодо використання індексів:

• Не потрібно індексувати все поспіль. Досить часто, не розуміючи сенсу, люди просто індексують усі поля таблиці. Індекси прискорюють вибірки, але уповільнюють вставки та оновлення рядків, тому вибір кожного індексу має бути осмислений.
• Один із основних параметрів, що характеризує індекс — селективність (selectivity) — кількість різних елементів в індексі. Немає сенсу індексувати поле, в якому є два-три можливі значення. Користі від такого індексу буде мало.
• Вибір індексів має починатися з аналізу всіх запитів до цієї таблиці. Дуже часто після такого аналізу замість трьох-чотирьох індексів можна зробити один складений.
• При використанні складових індексів порядок полів в індексі має вирішальне значення.
• Не забувай про індекси, що покривають (covering). Якщо всі дані в запиті можна отримати з індексу, MySQL не буде звертатися безпосередньо до таблиці. Подібні запити виконуватимуться дуже швидко. Наприклад, для запиту SELECT name FROM user WHERE login='test' за наявності індексу (login, name) звернення до таблиці не потрібно. Іноді має сенс додати до складового індексу додаткове поле, яке зробить індекс покриваючим та прискорить запити.
• Для індексів рядків часто достатньо індексувати лише частину рядка. Це може значно зменшити розмір індексу.
• Якщо % стоїть на початку LIKE(SELECT * FROM table WHERE field LIKE '%test'), індекси не використовуватимуться.
• FULLTEXT індекс використовується лише з синтаксисом MATCH … AGAINST.

3. Структура БД

Грамотно спроектована БД — запорука швидкої та ефективної роботи з базою. З іншого боку, погано продумана БД — це головний біль для розробників.

Поради з проєктування БД:

1. Використовуй мінімально можливі типи даних. Чим більший тип даних, тим більша таблиця, тим більше звернень до дисків потрібно для отримання даних. Використовуй дуже зручну процедуру: SELECT * FROM table_name PROCEDURE ANALYSE(); для визначення мінімально можливих типів даних.
2. На етапі проєктування дотримуйся нормальних форм. Часто програмісти вдаються до денормалізації вже на цьому етапі. Однак у більшості випадків на початку проєкту не очевидно, у що це може вилитися. Денормалізувати таблицю набагато простіше, ніж страждати від неоптимально денормалізованої. Та й JOIN часом працює швидше, ніж неправильно денормалізовані таблиці.
3. Не використовуй NULL стовпці за винятком випадків, коли вони тобі свідомо потрібні.

4. SQL запити.

Так само часто виникає бажання переписати всі запити на native SQL, щоб запит був максимально швидким. Якщо ти зважишся на таке, то ось тобі кілька порад:

1. Уникай запитів у циклі. SQL — мова множин, і до написання запитів потрібно підходити не мовою функцій, а мовою множин.
2. Уникай * (зірочки) у запитах. Не полінуйтеся перерахувати ті поля, які ти обираєш. Це скоротить кількість даних, що вибираються і пересилаються. До того ж, не забувай про індекси, що покривають. Навіть якщо ти дійсно вибираєш всі поля у таблиці, краще їх перерахувати. По-перше, це підвищує читабельність коду. В разі використання зірочок неможливо дізнатися, які поля є в таблиці без заглядання в неї. По-друге, сьогодні у твоїй таблиці п'ять INT стовпців, а через місяць додалося ще одне TEXT та BLOB, а зірочка як була, так і залишилася.
3. Під час посторінкового вибору для отримання загальної кількості записів використовуй SQL_CALC_FOUND_ROWS та SELECT FOUND_ROWS(); Під час використання SQL_CALC_FOUND_ROWS MySQL кешує обрану кількість рядків (до застосування LIMIT) і при SELECT FOUND_ROWS() лише віддає це закешоване значення без необхідності повторного виконання запиту.
4. Не забувай, що INSERT має синтаксис для множинної вставки. Один запит буде виконуватися набагато швидше, ніж безліч запитів у циклі.
5. Використовуй LIMIT там, де тобі не потрібні всі дані.
6. Використовуй INSERT… ON DUPLICATE KEY UPDATE… замість вибірки та INSERT або UPDATE після неї, а також часто замість REPLACE.
7. Не забувай про чудову функцію GROUP_CONCAT. Вона може допомогти у разі складних запитів.