Xpoint
   [напомнить пароль]

Оптимизация запросов с помощью индексов

Оглавление

Введение

Данная статья рассматривает вопросы создания предсказуемых индексов. С точки зрения теории индекс Z является предсказуемым, если ясно, как изменится план/скорость выполнения запроса X, если в таблице Y добавить/удалить/изменить индекс Z. Задача этой статьи — научить предсказывать поведение сервера, а точнее оптимизатора запросов, при манипуляциями с индексами таблиц.

Версия MySQL

В данной статье раскрываются в основном особенности MySQL 4.0. Все примеры, которые приведены без указания версии сервера, относятся к MySQL 4.0.26. Очень жаль, но руководство на Русском языке доступно только для версий серии 4.0, но так как эта серия больше не поддерживается, то он-лайн просмотр документации не доступен. Ссылки были поправлены на английскую документацию версии 4.1.

Когда вам понадобятся эти знания

Прочитав статью, не надо убивать часы на оптимизацию всех возможных запросов в вашей системе. Попробуем вывести ряд критериев для получения списка запросов, подлежащих оптимизации в первую очередь:

  • время выполнения запроса — данный критерий актуален в любое время, собрать статистику можно с помощью журнала регистрации медленных запросов;
  • во многих случаях объемы данных определяют план выполнения запроса и выбор используемых индексов, даже не столько сами объемы, сколько соотношение объемов отдельных таблиц, так что данные должны быть актуальны, то есть вы должны знать (предполагать), сколько записей в одной таблице приходится на одну запись в другой таблице: чаще ноль, всегда одна, в среднем десять, миллион и более;
  • идеальных решений не бывает, добавляя новый индекс вы увеличиваете время изменения записей в таблице, так что не оптимизируйте все подряд;
  • проектируя новую БД, воспользуйтесь стандартным набором решений по созданию индексов #SslyilkaNaGlavu.

Инструментарий

Запросы

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

EXPLAIN SELECT

Самый главный помошник — выводит план выполнения запроса.

Cправочное руководство: EXPLAIN.

SHOW INDEX

Выводит информацию о существующих индексах в конкретной таблице.

Cправочное руководство:

(CREATE|DROP) INDEX

Создание/удаление индексов.

Cправочное руководство:

SELECT STRAIGHT_JOIN

Позволяет жестко определить порядок объединения таблиц. В рабочих системах не рекомендовано к использованию, но при исследовании запросов можно проанализировать различные последовательности объединения.

Cправочное руководство: ...

SELECT ... (USE|FORCE) INDEX

Позволяет выбрать индексы, которые сервер может/должен использовать.

Cправочное руководство: ...

ANALYZE TABLE

Справочное руководство: ANALYZE TABLE

OPTIMIZE TABLE

Справочное руководство: OPTIMIZE TABLE

Статистика

Журнал регистрации(log) медленных запросов

MySQL позволяет настроить запись запросов, время выполнения которых превысило определенный предел, в отдельный файл.

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

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

Справочное руководство: журнал медленных запросов

Журнал регистрации запросов не использующих индексы

В добавок к предыдущему журналу можно также протоколировать запросы, которые не используют индексы совсем. Для этого в версии 4.0.x активируйте log-long-format, а в 4.1 и выше log-queries-not-using-indexes.

Для результатов используется файл журнала медленных запросов.

Справочное руководство: журнал медленных запросов

Журнал регистрации всех запросов

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

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

Справочное руководство: общий журнал запросов

Утилиты для анализа журналов

Пока только список:

Добавляйте другие скрипты на любых языках программирования, позже постараюсь освятить

Индексы

Индекс — отдельно хранимая информация о значениях в колонке(ах) таблицы с целью ускорения поиска записей по указанным значениям.

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

Существуют различные алгоритмы формирования индексов(типы инедксов): BTREE, HASH, FULLTEXT и другие.

FULLTEXT(полнотекстовой) индекс принципиально отличается по возможностям и области применения от всех других вариантов. Обсуждение данного типа индексов выходит за рамки этой статьи и, возможно, заслуживает отдельной. Справочное руководство: полнотекстовой поиск.

Все остальные индексы можно объединить в одну группу, так как они обладают общими свойствами, но отличаются по скорости поиска и ограничениям. Наиболее часто в MySQL используются индексы, построенные на основе сбалансированных деревьев(BTREE), потому что являются наиболее универсальными.
Некоторые общие особенности:

  • длина индексируемых данных ограничена;
  • данные индексируются слева направо, то есть индекс не может быть использован с середины, при поиске должна быть известна крайняя левая часть.

Справочное руководство: использование индексов

План выполнения запросов

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

Выборки из одной таблицы

Возьмем таблицу пользователей с набором личной информации, проиндексированную по адресу электронной почты. Вот пара простых условий, которые могут получить преимущества от индексов:

  • EmailAddress = 'vasya.pupkin@mail.ru' написал равно вместо LIKE так как сам удивился результатам EXPLAIN'а, нужны дополнительные исследования - точно определенное значение всегда имеет возможность использовать индекс, сервер пройдется по дереву и получит точное указание на запись или несколько записей в таблице. Сразу нужно отметить, что, определяя колонку уникальной, автоматически создается индекс по ней. Зачем? Иначе было бы накладно перед каждой вставкой или изменением в таблице проверять все значения поля на соответствие условию уникальности, а так можно быстро проверить существование записи.
  • EmailAddress LIKE 'vacya.pupkin@%' - частичное использование индекса. Повторю, что должна быть определена крайняя левая часть и только она может быть использована для поиска по индексам. Например, для условия EmailAddress LIKE 'vasya.%@mail.ru' тоже будет использован индекс, но в поиске в структуре индекса будет участвовать только vasya., а остальная часть будет проверена последовательным сканированием строк, найденных с помощью индекса. К этому примеру мы вернемся при рассмотрении особенностей составных индексов.

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

Вот несколько практически равнозначных вариантов:

mysql> EXPLAIN SELECT * FROM Users WHERE EmailAddress LIKE 'a%';
+-------+-------+---------------------+--------+---------+------+------+-------------+
| table | type  | possible_keys       | key    | key_len | ref  | rows | Extra       |
+-------+-------+---------------------+--------+---------+------+------+-------------+
| Users | range | Users4              | Users4 |     121 | NULL |  261 | Using where |
+-------+-------+---------------------+--------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM Users WHERE EmailAddress BETWEEN 'a' AND 'b';
+-------+-------+---------------------+--------+---------+------+------+-------------+
| table | type  | possible_keys       | key    | key_len | ref  | rows | Extra       |
+-------+-------+---------------------+--------+---------+------+------+-------------+
| Users | range | Users4              | Users4 |     121 | NULL |  261 | Using where |
+-------+-------+---------------------+--------+---------+------+------+-------------+

mysql> EXPLAIN SELECT * FROM Users WHERE EmailAddress >= 'a' AND EmailAddress < 'b';
+-------+-------+---------------------+--------+---------+------+------+-------------+
| table | type  | possible_keys       | key    | key_len | ref  | rows | Extra       |
+-------+-------+---------------------+--------+---------+------+------+-------------+
| Users | range | Users4              | Users4 |     121 | NULL |  260 | Using where |
+-------+-------+---------------------+--------+---------+------+------+-------------+

Степень уникальности индекса

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

Классический примером является поле disabled типа правда/ложь. Стоит ли его индексировать отдельно? Самое частое условие поиска скорее всего будет disabled = 0, так что данный индекс будет чаще всего простаивать, если у вас только несколько процентов записей деактивированы, но поможет найти несколько деактивированых записей в миллионах активных. Существуют и обратные ситуации, когда только несколько записей активно, а остальные составляют историю, то индекс будет использоваться на полную катушку. Достаточно наглядный пример зависимости от хранимых данных, мы еще вернемся к нему при рассмотрении составных индексов.

Примеры: Странное неиспользование индекса, Как добиться использования индексов? (конкретный пример)

В итоге выигрывают индексы, которые более уникальны и, соответственно, используя индекс в таблице, придется просматривать меньше строк. MySQL использует два параметра при приеме решений. Cardinality — свойство индекса, которое высчитывается, исходя из уникальности значений индексируемых данных. Используя cardinality при построении плана, высчитывается предполагаемое количество строк, выбранных с помощью индекса. Обе оценки приближенные и иногда могут быть далеки от реальности.

Рассмотрим это на примере с полем Disabled. Оказалось, что в таблице нет деактивированых записей:

mysql> SELECT Disabled, COUNT(1) FROM Principals GROUP BY Disabled;
+----------+----------+
| Disabled | COUNT(1) |
+----------+----------+
|        0 |    35942 |
+----------+----------+

Ниже приведены частичные выводы EXPLAIN'ов двух запросов:

mysql> EXPLAIN SELECT * FROM Principals WHERE Disabled = 0;
+-------+
|  rows |
+-------+
| 18055 |
+-------+
mysql> EXPLAIN SELECT * FROM Principals WHERE Disabled = 1;
+-------+
|  rows |
+-------+
|     1 |
+-------+

Пример демонстрирует небольшое несоответствие предположений анализатора запросов, но, в общем-то, порядки сохранены, и на эти данные можно полагаться при выборе оптимальных вариантов.

Значение свойства cardinality можно посмотреть в информации по индексам:

mysql> SHOW KEYS FROM Principals;
+------------+---------------+-------------+
| Table      | Column_name   | Cardinality |
+------------+---------------+-------------+
| Principals | Disabled      |           2 |
+------------+---------------+-------------+

Составные индексы

Зачем они вообще понадобились? Все достаточно просто. Допустим, есть поля имя и фамилия, которые проиндексированы по отдельности, и нам нужно найти все записи о Васях Пупкиных. Допустим, сначала по индексу фамилии были найдены все Пупкины, но, чтобы найти в них всех Вась, нельзя использовать индекс имени, потому что данные в индексах не связаны. Некоторые БД делают две выборки по индексам с последующим нахождением пересечения результатов, но в большинстве случаев можно использовать составные индексы, когда индекс содержит информацию о нескольких колонках таблицы сразу. Создать такой индекс можно с помощью команды:

CREATE INDEX MyCompoundIndex ON Users(LastName, FirstName);

Команда SHOW KEYS ... показывает части индекса как отдельные строки, указывая порядок следования частей в колонке Seq_in_index.

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

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

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

Выборки из нескольких таблиц

Вы, возможно, заметили, что на одну таблицу при выборе может быть использован только один индекс. Это связано с тем, что после получения временных результатов по одному индексу данные остальных индексов становятся бесполезны, так как актуальны только для всей таблицы. Другие СУБД могут использовать более одного индекса. Например, чтобы выбрать людей по городу и фамилии, можно отдельно выбрать по индексу города и отдельно по индексу фамилии, а потом найти пересечение записей двух множеств, но в случае /MySQL/Optimizatsiya/MySQL? такое невозможно и поэтому на передний план выходят Составные индексы. Это же правило распространяется и на запросы из нескольких таблиц, а именно одно объединение - один индекс. Тут нужно понять, что mysql изначально пустое множество поледовательно дополняет результатами из таблиц, фильтруя их "константными критериями" поиска и критериями объединения таблиц, получив первый набор только по "константным критериям" сервер для каждой полученной записи ищет соответствие в следующей таблице по условию объединения и критериям поиска для нее. Поясню на примере(версия mysql 4.1):

EXPLAIN SELECT a.Name
FROM Users u, Attributes a
WHERE
  u.EmailAddress LIKE 'root%'
  AND a.ObjectType = 'User'
  AND a.ObjectId = u.id;

+-------+-------+-------------+--------------------------+
| table | type  | key         | ref                      |
+-------+-------+-------------+--------------------------+
| u     | range | Users4      | NULL                     |
| a     | ref   | Attributes2 | const,rt3regression.u.id |
+-------+-------+-------------+--------------------------+

Сначала сервер получает данные по условию u.EmailAddress LIKE 'root%' из таблицы пользователей(адрес эл.почты проиндексирован), а потом используя константу 'User' и найденные идентификаторы пользователей получает записи в таблице аттрибутов с помощью составного индекса по полям ObjectType и ObjectId. Итого два индекса: ON Users(EmailAddress) и ON Attrbutes(ObjectType, ObjectId).

Возвращаясь к вопросу о последовательности включения полей в составные индексы, нужно отметить, что в данном запросе она не важна (условия захватывают определяют колонки полностью), но нужно понимать, что запрос "выбрать все атрибуты пользователей без объединения с таблице пользователей" определяет только поле ObjectType и индекс ON Attrbutes(ObjectId, ObjectType) будет бесполезен.

Порядок объединения

Поменяем немного предыдущий пример (увеличим охват пользователей):

EXPLAIN SELECT a.Name
FROM Users u, Attributes a
WHERE
  u.EmailAddress LIKE 'r%' <--- здесь
  AND a.ObjectType = 'User'
  AND a.ObjectId = u.id;

+-------+--------+-------------+--------------------------+------+
| table | type   | key         | ref                      | rows |
+-------+--------+-------------+--------------------------+------+
| a     | ref    | Attributes2 | const                    |    5 |
| u     | eq_ref | PRIMARY     | rt3regression.a.ObjectId |    1 |
+-------+--------+-------------+--------------------------+------+

Получили совсем другой результат. Так как у нас практическое руководство, то не будем теоретизировать много, а просто проверим почему изменился порядок объединения, для этого используем модификатор STRAIGHT_JOIN:

EXPLAIN SELECT STRAIGHT_JOIN ...
+-------+-------+-------------+--------------------------+------+
| table | type  | key         | ref                      | rows |
+-------+-------+-------------+--------------------------+------+
| u     | range | Users4      | NULL                     |    6 |
| a     | ref   | Attributes2 | const,rt3regression.u.id |    1 |
+-------+-------+-------------+--------------------------+------+

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

Стоит также обратить внимание на то, что новый план (без STRAIGHT_JOIN) использует все тот же индекс таблицы аттрибутов, но только частично (ObjectType), а для таблицы пользователей выбран индекс по первичному ключу.

Порядок при использовании LEFT или RIGHT объединений

Левые и правые объединения (LEFT JOIN и RIGHT JOIN) являются зависимыми и порядок в котором СУБД производит выборки фиксируется. В первую очередь выбирается таблица с которой происходит объединение, в слчае левого - левая, правого - правая.

Для примера выберем пользователей, для у которых нет определенного атрибута:

EXPLAIN SELECT u.*
FROM Users u LEFT JOIN Attributes a
    ON a.ObjectType = 'User'
        AND a.ObjectId = u.id
        AND a.Name = 'SavedSearch'
WHERE a.id IS NULL;
+----+-------------+-------+------+-------------------------+-------------+---------+--------------------------+------+-------------------------+
| id | select_type | table | type | possible_keys           | key         | key_len | ref                      | rows | Extra                   |
+----+-------------+-------+------+-------------------------+-------------+---------+--------------------------+------+-------------------------+
|  1 | SIMPLE      | u     | ALL  | NULL                    | NULL        |    NULL | NULL                     |   64 |                         |
|  1 | SIMPLE      | a     | ref  | Attributes1,Attributes2 | Attributes2 |     198 | const,rt3regression.u.id |    1 | Using where; Not exists |
+----+-------------+-------+------+-------------------------+-------------+---------+--------------------------+------+-------------------------+

продолжение следует...

TODO =

темы из архива форума:

Если вы встретили на форуме обсуждение вопросов применения индексов в /MySQL/Optimizatsiya/MySQL?, то добавьте ссылку

Полезные ссылки

Комментарии

2008-11-19 13:12:40 [обр] taunt[досье]
интересная и полезная статья, спасибо
а гед ссылка на продолжение (если оно есть) ?
спустя 4 месяца 5 дней [обр] Антон Клесс[досье]
довольно путано =(
Powered by POEM™ Engine Copyright © 2002-2005