Настраивайте запросы MySQL как профессионал

Оптимизация запросов — это не темное искусство; это просто инженерия.

Многие люди считают настройку запросов к базе данных неким загадочным «темным искусством» из романа о Гарри Поттере; при неправильном заклинании ваши данные превращаются из ценного ресурса в кучу каши.

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

Чтобы дать оптимизатору лучшее представление о данных, вы можете использовать индексы и гистограммы. При правильном использовании они могут значительно увеличить скорость запроса к базе данных. Если вы будете следовать рецепту, у вас получится то, что вам понравится. Но если вы добавите в этот рецепт свои собственные ингредиенты, вы можете не получить то, что хотите.

Оптимизатор затрат

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

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

Сложность запроса может помешать оптимизации. Оптимизатор хочет предоставить запрос с наименьшей стоимостью из доступных вариантов. Объединение пяти разных таблиц означает, что существует пятифакториал или 120 возможных комбинаций, которые к чему можно соединить. Эвристика встроена в код, чтобы попытаться сократить оценку всех возможных вариантов. MySQL хочет генерировать новый план запроса каждый раз, когда видит запрос, в то время как другие базы данных, такие как Oracle, могут иметь заблокированный план запроса. Вот почему жизненно важно предоставить оптимизатору подробную информацию о ваших данных. Для обеспечения стабильной производительности очень полезно иметь актуальную информацию, которую оптимизатор запросов может использовать при составлении планов запросов.

Кроме того, в оптимизатор встроены правила с предположениями, которые, вероятно, не соответствуют реальности ваших данных. Оптимизатор запросов предполагает, что все данные в столбце равномерно распределены по всем строкам, если в нем нет другой информации. И по умолчанию он выберет меньший из двух возможных индексов, если не видит альтернативы. Хотя модель, основанная на затратах, для оптимизатора может принять много хороших решений, вы можете попасть в случаи, когда не сможете получить оптимальный план запроса.

План запроса?

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

SELECT city.name as 'City',
               country.name as 'Country'
FROM city
JOIN country ON (city.countrycode = country.code)
WHERE country.code = 'GBR'
LIMIT 5;

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

select `world`.`city`.`Name` AS `City`,
         	'United Kingdom' AS `Country`
from `world`.`city`
join `world`.`country`
where (`world`.`city`.`CountryCode` = 'GBR')
limit 5;

Большие изменения заключаются в том, что country.name как «Страна» было изменено на 'Соединенное Королевство» как «Страна», а предложение WHERE было заменено переходя от таблицы страны к таблице города. Оптимизатор определил, что эти два изменения обеспечат более быстрый результат, чем исходный запрос.

Индексы

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

Механизм хранения InnoDB MySQL по умолчанию хочет, чтобы ваша таблица имела первичный ключ, и будет хранить ваши данные в дереве B+ по этому ключу. Недавно добавленная функция MySQL — это невидимые столбцы — столбцы, которые не возвращают данные, если столбец явно не указан в запросе. Например, SELECT * FROM foo; не предоставляет столбцов, обозначенных как скрытые. Эта функция позволяет добавить первичный ключ к старым таблицам без перекодирования всех запросов для включения этого нового столбца.

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

Описание различных индексов выходит за рамки этой статьи, поэтому просто думайте об индексе как о ярлыке к нужной записи или записям. Вы можете создать индекс для одного или нескольких столбцов или части этих столбцов. Система моего врача может найти мои записи по первым трем буквам моей фамилии и даты рождения. При использовании нескольких столбцов сначала необходимо использовать самое уникальное поле, затем второе по уникальности и т. д. Индекс «год-месяц-день» работает для поиска «год-месяц-день», «год-месяц» и «год», но не работает для поиска «день», «месяц-день» или «год-день». Это помогает проектировать индексы с учетом того, как вы хотите использовать свои данные.

Гистограммы

Гистограмма — это распределение ваших данных. Если бы вы располагали людей по фамилиям в алфавитном порядке, вы могли бы использовать «логическую корзину» для людей, чьи фамилии начинаются с букв от A до F, затем еще одну от G до J и так далее. Оптимизатор предполагает, что данные равномерно распределены внутри столбца, но на практике это происходит редко.

MySQL предоставляет два типа гистограмм: равной высоты, где все данные поровну разделены между сегментами, и синглтон, где в сегменте находится одно значение. Вы можете иметь до 1024 сегментов. Количество сегментов, которые следует выбрать для столбца данных, зависит от многих факторов, в том числе от того, сколько у вас различных значений, насколько искажены ваши данные и насколько высокой на самом деле должна быть ваша точность. После определенного количества ведер доходность снижается.

Эта команда создаст гистограмму из 10 сегментов в столбце c1 таблицы t:

ANALYZE TABLE t UPDATE HISTOGRAM ON c1 WITH 10 BUCKETS;

Представьте, что вы продаете носки маленького, среднего и большого размера, и для каждого размера есть своя корзина для хранения. Чтобы найти нужный вам размер, перейдите в корзину этого размера. В MySQL появились гистограммы с момента выхода MySQL 8.0 три года назад, но они не так известны, как индексы. В отличие от индексов, здесь нет дополнительных затрат на вставку, обновление или удаление записи. Чтобы обновить индекс, необходимо обновить команду ANALYZE TABLE. Это хороший подход, когда данные не сильно изменяются и частые изменения в данных снижают эффективность.

Индексы или гистограммы?

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

Прощальные мысли

Эта статья стала результатом недавней презентации на конференции Open Source 101. И эта презентация выросла из семинара на конференции PHP в Великобритании. Настройка запросов — сложная тема, и каждый раз, когда я представляю индексы и гистограммы, я нахожу способы улучшить свое представление. Но каждая презентация также показывает, что многие люди в мире программного обеспечения плохо разбираются в индексах и склонны использовать их неправильно. Гистограммы существуют недостаточно долго (я надеюсь), чтобы ими можно было злоупотреблять.