Индексирование — не 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. Стоит также упомянуть, что существуют различные виды индексов с определёнными свойствами, например, делающие все значения уникальными или включающие полнотекстовый поиск.

Очень просто обвинить в низкой производительности аппаратное или программное обеспечение, хотя, часто проблема скрывается на уровне базы данных. Несколько небольших, своевременно сделанных шагов могут серьёзно улучшить удобство использования вашего приложения.