6.1 Вступ

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

«У теорії між теорією та практикою відмінностей немає. Насправді вони є.»

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

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

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

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

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

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

6.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 .

6.3 Структура БД

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

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

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

6.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. Вона може допомогти при складних запитах.
Вебінар JavaRush