SQL-запросы. Как определить необходимые индексы (для новичков)?

Категория: Код

– Автор: Игорь (Администратор)

В рамках данной статьи для начинающих, я рассмотрю как определить необходимые индексы для увеличения скорости выполнения sql-запросов.

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

Это, конечно, действительно полезные вещи, однако в них нередко упускается один маленький нюанс - объемы данных, при которых все эти особенности действительно заметно сказываются. А цифра эта обычно измеряется в сотнях тысяч записей. Простыми словами, если у вас в таблицах находится в районе 1-30 тысяч записей и речь идет о веб-сайте (или подобном ресурсе), а не каком-то промежуточном хранилище данных для нагруженных систем, то чаще всего важнее просто построить корректные индексы. Тут важно отметить, что вам совсем не обязательно шибко сильно разбираться во всей технической части. Многие полезные индексы можно построить и при помощи простой логики.

Примечание: При этом подразумевается, что сами запросы построены более или менее оптимально, например, нет лишних полей в select, фильтрация данных в join и where происходит с минимизацией количества проверяемых строк и прочее.

Поэтому далее, я рассмотрю пару примеров и предоставлю ряд полезных советов.

Индекс для целочисленных полей идентификаторов.

Если у вас есть поле с целочисленным идентификатором (не важно, идентификатор ли это самой таблицы или же идентификатор, указывающий на строку другой таблицы), то стройте для него отдельный индекс.

Суть заключается в следующем. Если поле является идентификатором записей самой таблицы, то речь идет о первичном ключе (он так же является индексом). Пользы от такого индекса много, ведь сайты чаще всего оперируют именно идентификаторами. Если же это идентификатор строчки из таблицы справочника, то индекс также нужен. Так как если вам нужны отфильтрованные данные, то без индексов от этих справочников толку не очень много (ну только разве что, размер базы данных).

Если с первым случаем все достаточно просто и понятно, то для второго случая (со справочником) приведу простой пример.

Допустим, есть две таблицы: статьи (article - id, name, text) и комментарии (comment - id, article_id, text). В первой таблице содержится 200 записей (статей), во второй таблице содержится 2000 записей (по 10 комментариев для каждой статьи). Соответственно, когда каждый пользователь открывает любую статью, выполняется следующий запрос:

select id, text
from comment
where article_id = $id

Если же sql-запрос выполняется без индекса для поля article_id, то каждый раз будет полностью просматриваться вся таблица с комментариями (все 2000 записей). Если же индекс будет добавлен для поля article_id, то базе данных нужно будет посмотреть не более 20 записей (если быть точным, то порядка 18 в худшем случае). Расчет тут прост. Поиск по индексу в худшем случае происходит примерно со скоростью - двоичный логарифм от числа записей + количество записей с одинаковым значением поля индекса. В данном случае, 10 записей есть у каждой статьи (Их значения повторяются) + log2 от 200 (так как статей всего 200 = 2000 / 10) = 10 + 8 (округляют в большую сторону) = 18.

Конечно, каждый такой индекс, в добавок к занимаемому месту на диске, представляет собой еще и дополнительные издержки в базе данных при insert, update и delete. Ведь, кроме изменения данных самой таблицы, так же возникает необходимость перестраивать ее индексы. Но, как я уже говорил, для объемов обычных веб-сайтов - это не страшно. И даже если у вас будет создан индекс в таблице, которым вы не пользуетесь в своих sql-запросах, то каких-то заметных проблем от этого не возникнет. Кроме того, всегда возможен вариант, что поставив дополнительный модуль или же собственноручно добавив запросы, этот индекс может оказаться очень кстати.

Примечание: Тем не менее, помните, что это касается именно целочисленных индексов, а не варианта "сделаю-ка я индексы для всех возможных полей".

Простые и составные индексы для наиболее частых запросов.

Многие базы данных имеют кэш результатов для запросов. Попробуйте выполнить один и тот же запрос два раза подряд - в первом случае запрос выполнится долго, второй раз быстро. Первый раз данные будут вычисляться, во второй раз данные будут предоставлены из кэша. Однако, это не очень помогает в тех случаях, когда для запросов не строится кэш (например, когда в фильтре вычисляемые условия с использованием встроенных функций базы данных), тех случаях, когда запросы хоть и однотипные, но используются с разными параметрами, и тех случаев, когда запросов много и поэтому данные в кэше хранятся весьма недолгий промежуток времени.

Поэтому периодически может иметь смысл дополнительно строить обычные и составные индексы для часто выполняемых запросов. Рассмотрим два примера.

Простой индекс.

Допустим, у вас есть таблица - товары (product - id, code, name, text). И так сложилось, что пользователи сайта часто ищут товары по их цифробуквенным кодам (артикулам - поле code). Соответственно, запрос выглядит примерно так:

select *
from product as p
where p.code = $code

В такой ситуации, имеет смысл создать отдельный индекс для поля "code", так как с ним базе данных не придется полностью сканировать все записи таблицы. Однако, учтите, что у баз данных могут быть ограничения для типов и размеров полей. Поэтому предварительно стоит проверить, можно ли создавать индекс для таких полей.

Составной индекс.

Прежде, чем приводить пример с составным индексом, хотел бы немного пояснить один существенный момент - порядок полей в индексе имеет важное значение. Так как поиск вначале осуществляется по первому полю, а затем уже по последующему (и так далее). Поэтому если же вам известно конкретное значение только последнего поля, то такой индекс не подойдет, так как не зная конкретного значения первого поля, определить какой набор записей необходимо проверять невозможно, из-за чего базе данных придется сканировать полностью все записи таблицы. Простыми словами индекс (column_1, column_2) не равен индексу (column_2, column_1).

Теперь, допустим следующую ситуацию. Есть три таблицы: пользователь (user - id, name), категория (cat - id, name) и статья (article - id, cat_id, user_id, name, text). И вы сделали на сайте такую штуку - внизу статьи выводится полный список статей того же пользователя из данной категории. При этом пользователи оказались настолько плодовитыми, что пишут очень много статей, хоть и в разные категории (например, мелкие истории, небольшие заметки и так далее). В этом случае, запрос будет выглядеть следующим образом:

select id, name
from article
where user_id = $user_id
    and cat_id = $cat_id

Если вы сделали индексы для полей идентификаторов, то это вам хоть и поможет, но не сильно. Во-первых, тут есть два равновероятных индекса. Один для категорий, а второй для пользователей. Какой будет лучше - в общем случае неизвестно. Кроме того, это может не сильно помочь, так как у пользователей может быть 1000 статей и в категориях может быть по 1000 статей. Во-вторых, даже сократив записи по конкретному пользователю (или категории), все равно их придется просматривать по второму полю, то есть полное сканирование (хоть и для меньшего объема записей). Например, если у пользователей 1000 записей, то придется для всех 1000 записей проверить - входят они в категорию или нет.

Для большого количества записей и частого вызова - это весьма накладной sql-запрос. Поэтому в таком случае стоит сделать составной индекс, например, (user_id, cat_id), В таком случае, после поиска по пользователю, последующий поиск по категории будет происходить быстрее, так как для полученных записей будет так же иметься индекс. Соответственно, вместо проверки 1000 записей, будет проверено существенно меньше (расчет проверок так же осуществляется, как и со случаем обычного индекса - логарифм + число записей).

Как в таких ситуациях определить порядок полей? Тут все достаточно просто и аналогично тому, что я описывал в статье про фильтрацию (см. ссылку вначале). Напомню, суть в том, чтобы с каждым примененным фильтром записей становилось как можно меньше. Поэтому имеет смысл проверить, среднее количество записей приходящееся на каждое значение поля в таблице. И то поле, у которого это число меньше должно идти первым. Например, для данного sql-запроса, стоит проверить следующее:

-- Вычисляем среднее число записей для пользователей
select
    -- Среднее число записей
    avg(data.count) as avg
from
      -- Группируем все записи по идентификатору
      (
          select
             count(*) as `count`
          from article
          -- Группируем по пользователям
          group by user_id
      ) as data
;
-- Вычисляем среднее число записей для категорий
select
    -- Среднее число записей
    avg(data.count) as avg
from
      -- Группируем все записи по идентификатору
      (
          select
             count(*) as `count`
          from article
          -- Группируем по категориям
          group by cat_id
      ) as data
;

Соответственно, если среднее число по пользователям меньше, то это поле должно идти первым, так как, после первого поиска, записей для проверки будет немного. В противном случае, идентификатор категории должен идти первым.

Однако, стоит понимать, что в такой ситуации так же стоит проверить, что записи распределены более или менее равномерно. Ведь может оказаться, что 1 пользователь написал 2000 статей, а остальные всего 100. В такой ситуации, фильтр по категории может быть предпочтительнее, ведь большинство читателей будут просматривать статьи именно этого пользователя. Поэтому иногда стоит вычислить только группировку по идентификаторам (без вычисления avg) и бегло просмотреть полученные результаты.

Если же требуется составить индекс для трех и более полей, то стоит проделать то же самое, только с увеличением количества полей, для которых осуществляется группировка по идентификатору. Простыми словами, сначала проверить первое поле и определить наиболее меньшее число, затем вместо "group by column_1" указать различные варианты с оставшимися полями в виде "group by column_1, column_2", затем "group by column_1, column_3" и так далее. При этом каждый выбирать те комбинации, при которых среднее число записей становится меньше и меньше.

Существуют и другие нюансы, но даже описанных случаев чаще всего хватает для того, чтобы увеличить производительность веб-сайта.

• PHP - уровни доступа функций класса или как сделать закрытую функцию открытой
• PHP редирект - перенаправление на другую страницу