Данная статья рассматривает вопросы создания предсказуемых индексов. С точки зрения теории индекс Z является предсказуемым, если ясно, как изменится план/скорость выполнения запроса X, если в таблице Y добавить/удалить/изменить индекс Z. Задача этой статьи — научить предсказывать поведение сервера, а точнее оптимизатора запросов, при манипуляциями с индексами таблиц.
В данной статье раскрываются в основном особенности MySQL 4.0. Все примеры, которые приведены без указания версии сервера, относятся к MySQL 4.0.26. Очень жаль, но руководство на Русском языке доступно только для версий серии 4.0, но так как эта серия больше не поддерживается, то он-лайн просмотр документации не доступен. Ссылки были поправлены на английскую документацию версии 4.1.
Прочитав статью, не надо убивать часы на оптимизацию всех возможных запросов в вашей системе. Попробуем вывести ряд критериев для получения списка запросов, подлежащих оптимизации в первую очередь:
Сервер поддерживает ряд запросов, предназначеных для управления индексами и анализа эффективности их использования.
Самый главный помошник — выводит план выполнения запроса.
Cправочное руководство: EXPLAIN.
Выводит информацию о существующих индексах в конкретной таблице.
Cправочное руководство:
Создание/удаление индексов.
Cправочное руководство:
Позволяет жестко определить порядок объединения таблиц. В рабочих системах не рекомендовано к использованию, но при исследовании запросов можно проанализировать различные последовательности объединения.
Cправочное руководство: ...
Позволяет выбрать индексы, которые сервер может/должен использовать.
Cправочное руководство: ...
Справочное руководство: ANALYZE TABLE
Справочное руководство: OPTIMIZE TABLE
MySQL позволяет настроить запись запросов, время выполнения которых превысило определенный предел, в отдельный файл.
Оценки могут показаться достаточно грубыми — нельзя установить время меньше секунды, но и этого вполне достаточно для выделения самых проблемных запросов. Для более точного протоколирования запросов можно использовать патч, который увеличивает точность до микросекунд. Более подробно про патч можно прочитать здесь.
Очень важно не забывать, что считается время от начала до окончания выполнения запроса, а не время, потраченное конкретно на данный запрос, то есть общая загруженность системы влияет на заполнение этого журнала.
Справочное руководство: журнал медленных запросов
В добавок к предыдущему журналу можно также протоколировать запросы, которые не используют индексы совсем. Для этого в версии 4.0.x активируйте log-long-format, а в 4.1 и выше log-queries-not-using-indexes.
Для результатов используется файл журнала медленных запросов.
Справочное руководство: журнал медленных запросов
В крайних случаях можно воспользоваться журналом всех запросов. Сервер протоколирует все подряд запросы без учета времени выполнения.
Рекомендуется использовать только на этапе тестирования. На загруженных системах файл очень быстро разрастается, а также заметно падает производительность сервера.
Справочное руководство: общий журнал запросов
Пока только список:
Добавляйте другие скрипты на любых языках программирования, позже постараюсь освятить
Индекс — отдельно хранимая информация о значениях в колонке(ах) таблицы с целью ускорения поиска записей по указанным значениям.
Допустим, в таблице нет индексов, тогда для того, чтобы найти все строки, где определенное поле равняется заданному значению, серверу придется перебрать все записи в таблице и для каждой записи сравнить значение в колонке с указанным. При наличии же индекса сервер может получить расположение искомых строк, используя информацию, хранящуюся в индексе, на порядок быстрее.
Существуют различные алгоритмы формирования индексов(типы инедксов): BTREE, HASH, FULLTEXT и другие.
FULLTEXT(полнотекстовой) индекс принципиально отличается по возможностям и области применения от всех других вариантов. Обсуждение данного типа индексов выходит за рамки этой статьи и, возможно, заслуживает отдельной. Справочное руководство: полнотекстовой поиск.
Все остальные индексы можно объединить в одну группу, так как они обладают общими свойствами, но отличаются по скорости поиска и ограничениям. Наиболее часто в MySQL используются индексы, построенные на основе сбалансированных деревьев(BTREE), потому что являются наиболее универсальными.
Некоторые общие особенности:
Справочное руководство: использование индексов
После получения запроса сервер строит план его выполнения, который включает последовательность объединения таблиц, и для каждого объединения выбираются используемые индексы. В случае с выборкой из одной таблицы последовательность выбирать не приходится, поэтому рассмотрим для начала этот случай как более простой.
Возьмем таблицу пользователей с набором личной информации, проиндексированную по адресу электронной почты. Вот пара простых условий, которые могут получить преимущества от индексов:
EmailAddress = '[email protected]' написал равно вместо 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 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 |
+----+-------------+-------+------+-------------------------+-------------+---------+--------------------------+------+-------------------------+