Індексування – не Google’ом єдиним

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

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

Веб-бази даних використовуються не тільки для простого зберігання інформації. Однією з їх можливостей є ефективне індексування записів. Індекс служить свого роду картою, що вказує точне місце розташування невеликої ділянки даних в набагато більшому обсязі. Наприклад, Google, за запитом «web development» знаходить двісті мільйонів результатів і відображає перший десяток всього за чверть секунди. Але Google не завантажує і не виконує сканування кожної з цих сторінок відразу після запиту – аналіз був проведений заздалегідь, і пошук вівся по індексованих копіях сторінок.

Чи так це важливо?

Звичайно! В одному простому випадку при відсутності індексу додаток працював від 20 до 60 разів повільніше , ніж міг би. Наприклад, візьмемо просту блогову систему. Ми створимо декілька таблиць і заповнимо їх випадково згенерованими даними:

– articles
– articles_categories
– categories
– comments
– users

Уявіть, що наш блог опублікований відносно недавно. У нього тільки один автор, десяток статей і по п’ять коментарів до кожної статті. Наша база даних містить індекси, але тільки для ключових полів (ID) зазначених таблиць.

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

SELECT * FROM articles
INNER JOIN users
ON articles.user_id = users.id
WHERE users.email = 'john.doe@example.com';

0.01 секунди

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

Розглянемо більш складний приклад. Цей запит знаходить всі коментарі до статей певного автора, включаючи інформацію про категорії, до яких належать ці статті:

SELECT * FROM articles
INNER JOIN articles_categories
ON articles_categories.article_id = articles.id
INNER JOIN categories
ON articles_categories.category_id = categories.id
INNER JOIN users
ON articles.user_id = users.id
INNER JOIN comments
ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

0.02 секунди

Знову ж, запит виконується практично миттєво. Але, насправді, він робить дуже ресурсномістку операцію – повнотабличне сканування . Швидкість відгуку обумовлена лише малими обсягами даних. Згадаймо, що ми хочемо за допомогою бази даних виконати наступне:

1. Відшукати користувача з електронною поштою john.doe@example.com.
2. Знайти кожну статтю, значення поля user_id якої збігається зі значенням id цього користувача.
3. Вибрати з категорій в таблиці articles_categories ті, у яких значення поля article_id збігається з ID кожної зі знайдених статей користувача.
4. Нарешті, знайти всі коментарі, у яких значення поля article_id також збігається з ID знайдених статей.

На жодному з цих кроків записи не вибираються за власним ID – тільки по ID інших пов’язаних записів. Оскільки індексуються тільки поля ID, базі даних доводиться перевіряти кожен рядок принаймні в кількох таблицях, щоб видати результат. Подумки перенесемося на кілька років вперед і поглянемо на наш блог: 1000 статей, 15 авторів і в середньому по 25 коментарів до кожної статті. Повторимо наш простий запит:

SELECT * FROM articles
INNER JOIN users
ON articles.user_id = users.id
WHERE users.email = 'john.doe@example.com';

0.65 секунди

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

SELECT * FROM articles
INNER JOIN articles_categories
ON articles_categories.article_id = articles.id
INNER JOIN categories
ON articles_categories.category_id = categories.id
INNER JOIN users
ON articles.user_id = users.id
INNER JOIN comments
ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

6.69 секунди

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

Ось що відбувається, коли ми додаємо індекси в наші таблиці:

Простий запит: 0.01 секунди

Складний запит: 0.32 секунди

Зауважте, що простий запит для 1000 статей обробляється стільки ж часу, скільки і для 15 статей. Складний запит не настільки швидкий, але все ж в 20 разів швидший, ніж раніше. Без індексів ми переглядали кожен рядок в деяких таблицях, і час роботи безпосередньо залежав від обсягу даних. При індексуванні полів, за якими ведеться пошук, процес стає набагато ефективнішим. Це все одно, що шукати страви з птиці в змісті кулінарної книги, а не гортаючи і позначаючи кожну її сторінку. Використання індексів економить час. І чим більші обсяги даних, тим більше часу буде зекономлено.

Що індексувати?

У загальному випадку, слід робити індексними всі зовнішні ключові поля бази даних. Це поля, що посилаються на ID (або первинні ключі) іншої таблиці і зв’язуючі тим самим записи в різних таблицях, як, наприклад, поле user_id в таблиці articles. Також, слід застосовувати індекси до всіх полів, за якими ведеться пошук серед великого числа записів. У нашому прикладі блогу ми можемо індексувати поле email таблиці users, так як нам часто доводиться ідентифікувати користувачів по електронній пошті. Знаючи, що число користувачів буде невеликим, ми не відчуємо велику різницю, однак при зростанні бази користувачів, відмінність може стати вирішальною. При кожному вході користувача ми можемо виконувати подібний запит:

SELECT password FROM users WHERE email = '$my_email';

Виконання цього запиту на таблиці з тисячами записів може бути проблематичним без індексування поля email. Для створення індексу в MySQL використовуйте наступну команду:

ALTER TABLE users ADD INDEX (email);

Більшість графічних засобів управління базами даних (такі як phpMyAdmin) мають вбудовані засоби для створення і управління індексами. Індекс також може пов’язувати кілька полів одночасно, що корисно, коли записи найчастіше ідентифікуються по комбінації атрибутів. Щоб зробити такий запит ефективнішим, можна індексувати одночасно поле email і password:

SELECT * FROM users WHERE email = '$my_email' 
AND password = '$my_password';

Вивчіть ваші засоби

Розуміння індексів особливо важливе, коли ми перекладаємо генерацію SQL-коду на фреймворки. Фреймворки багато в чому корисні, але, тим не менш, слід бути обережним. Установка Dreamweaver не скасовує необхідності розуміння XHTML і CSS, а побудова таблиць за допомогою міграційних скриптів Ruby on Rails не усуває необхідності їх індексування. І, хоча така професійна система, як, наприклад, WordPress, створює добре індексовані таблиці, що встановлюються, плагіни можуть цього не робити. З іншого боку, важливо і не перестаратися. Створення занадто великої кількості індексів може теж стати проблемою, оскільки сервер витрачає час на визначення того, які з них використовувати для поточного запиту. База даних також повинна оновлювати індекси при додаванні нових записів. Тут справедливе загальне правило дизайну:не множити сутності більше необхідного.

Наступний рівень

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

Визначте корінь проблеми

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

long_query_time = 1 log-slow-queries = /var/log/mysqld.slow.log

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

# Query_time: 7 Lock_time: 0 Rows_sent: 296 Rows_examined: 75872

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

Попросіть у сервера «пояснень»

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

EXPLAIN SELECT * FROM articles
INNER JOIN articles_categories
ON articles_categories.article_id = articles.id
INNER JOIN categories
ON articles_categories.category_id = categories.id
INNER JOIN users
ON articles.user_id = users.id
INNER JOIN comments
ON comments.article_id = articles.id
WHERE users.email = 'john.doe@example.com';

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

table type possible_keys key rows Extra
articles_categories ALL NULL NULL 1000
categories eq_ref PRIMARY PRIMARY 1
articles eq_ref PRIMARY PRIMARY 1
users eq_ref PRIMARY PRIMARY 1 Using where
comments ALL NULL NULL 25000 Using where

Тут міститься багато інформації про те, як сервер баз даних обробляє кожну таблицю в запиті. Стовпець type показує, як порівнюються рядки таблиць, що об’єднуються. Значення ALL означає, що використовується повнотабличне сканування. У стовпчиках possible_keys і key перераховані індекси, які сервер баз даних вважає потенційно можливими для використання, і які були використані при обробці запиту. Стовпець rows містить число рядків, передбачених для перегляду. Загальна кількість можливих результатів запиту – результат множення всіх чисел в цьому стовпці (а не сума, як можна було б подумати). Іншими словами, в даному запиті, сервер визначає переглянути 25 мільйонів комбінацій рядків, хоча, дійсне число залежить від реальних даних.

Стовпець Extra містить іншу інформацію про процес обробки запиту. У нашому випадку тут вказано, які набори даних обмежуються умовою WHERE. Для більш складних запитів, особливо таких, що включають операції групування або сортування, тут можуть бути вказані прапори Using filesort або Using temporary.

Для порівняння, ось результат команди EXPLAIN для таблиць з індексами:

table type possible_keys key rows Extra
users ref PRIMARY, email email 1 Using where
articles ref PRIMARY, user_id user_id 67
articles_categories ref category_id, joint_index, article_id article_id 1
categories eq_ref PRIMARY PRIMARY 1
comments ref article_id article_id 25 Using where

Зауважте, що тут не просто використовуються індекси, весь порядок операцій зовсім інший. Сервер баз даних досить розумний, щоб витягти максимум користі з наявних можливостей, тому в першому випадку була запропонована найбільш ефективна при відсутності індексів схема. Зараз індекси доступні, і підхід зовсім інший. Помноживши значення в стовпці rows, ми отримаємо всього +1675 потенційних результатів – лише невелику частку від початкової кількості.

Правильні об’єднання

Отримання даних з декількох таблиць має на увазі їх об’єднання по полях, що зв’язує записи. У прикладах, наведених вище, ми використовували внутрішнє об’єднання (INNER JOIN), щоб вибрати тільки ті рядки, які відповідають зазначеним умовам. Іноді краще використовувати інші типи об’єднань, наприклад ліве зовнішнє (LEFT JOIN), при якому результат може повертатися навіть при відсутності відповідних під умову рядків в одній з таблиць. Хоча, загалом, при другому підході база даних виконує більше роботи.

Наприклад, якби ми хотіли знайти всі статті певного автора, включаючи інформацію про категорії, ми могли б використовувати подібний запит з внутрішніми об’єднаннями:

SELECT * FROM articles
INNER JOIN users
ON articles.user_id = users.id
INNER JOIN articles_categories
ON articles_categories.article_id = articles.id
INNER JOIN categories
ON articles_categories.category_id = categories.id
WHERE users.email = 'john.doe@example.com';

Оскільки цілісність наших даних висока, цей запит буде повертати правильні результати. Але що, якщо програма не вимагає від автора вказівки категорії при публікації статті? В цьому випадку, запит може видати не всі статті заданого автора. Всі статті, яким не була присвоєна категорія, не потраплять в результат, оскільки при внутрішньому об’єднанні потрібна наявність відповідностей в обох таблицях. Можна змінити запит наступним чином:

SELECT * FROM articles
INNER JOIN users
ON articles.user_id = users.id
LEFT JOIN articles_categories
ON articles_categories.article_id = articles.id
LEFT JOIN categories
ON articles_categories.category_id = categories.id
WHERE users.email = 'john.doe@example.com';

Такий запит поверне всі статті заданого автора, підставляючи NULL замість невказаних категорій. Однак, він є більш ресурсоємним. В базі даних з наших прикладів запит з внутрішніми об’єднаннями виконується за 0.06 секунди, а запит з лівими об’єднаннями – за 0.29 секунди, тобто, майже в п’ять разів повільніше. Але, оскільки результат лівого об’єднання включає в себе результат внутрішнього, ліві об’єднання іноді використовуються там, де вони зовсім не потрібні.

Використовуйте тільки те, що необхідно

Для простоти, всі розглянуті запити використовували SELECT* для отримання даних, що означає, що ми завантажували всі поля результуючих рядків. Насправді, так ми отримували набагато більше даних, ніж потрібно. Особливо, труднощі пов’язані з великими текстовими полями, такими, як записи блогу в таблиці articles. У другому запиті ми хотіли отримати всі коментарі до статей певного автора. Запит був написаний так, що ми, насправді, завантажували весь вміст статей (разом з безліччю інших даних), і не просто для кожної статті, а для кожного коментаря. Наприклад, якщо до статті написано 300 коментарів, база даних завантажить повний текст статті 300 раз. Набагато швидше було б вибирати тільки необхідні нам поля, наприклад:

SELECT comments.* FROM articles
INNER JOIN [...]

До речі, така зміна прискорює виконання запиту в чотири рази, до розумних 0.08 секунди.

Основи – перш за все

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

Пам’ятайте, що різні сервери баз даних, наприклад, MySQL, PostgreSQL або Microsoft SQL Server, по-різному виконують запити і реалізують індекси. За деталями найкраще звертатися до документації, хоча, загальні принципи в більшості випадків залишаються незмінними. Всі наведені приклади працюють в MySQL 5.0. Варто також згадати, що існують різні види індексів з певними властивостями. Наприклад, деякі роблять всі значення унікальними або включають повнотекстовий пошук.

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