Все подряд О чем здесь

Оптимизация MySQL-индексов

Как индексы могут влиять на производительность MySQL запросов

Если говорить простым языком, то запросы к MySQL условно можно разделить на 2 типа - операции выборки и операции обновления. К первым относятся операции SELECT, ко вторым - UPDATE, INSERT, DELETE. Довольно много информации по этому поводу есть в официальной документации, а так же в российском комьюнити. Но если в официальной документации обычно описывается как правильно использовать индексы и составлять правильные запросы, то в рамках этой статьи мне бы хотелось рассказать о личном опыте того, как можно оптимизировать "всё и разом" если более логично подойти к организации хранения своих данных и выборе типов данных.

Любой разработчик в курсе, что основным слабым звеном в доступе к данным являются дисковые операции. Именно количество дисковых операций, необходимых для выполнения запроса, в основном и влияет на скорость выполнения этого запроса. И если на непосредственные операции чтения/записи к самим данным мы повлиять не можем, то мы можем легко повлиять на операции предшествующие им - а именно операции с индексами. Именно индексы позволяют движку MySQL почти напрямую получать ячейку где хранятся требуемые данные, а не искать их используя fullscan по всему файлу с данными. Именно поэтому любой опытный программист и администратор работающий с базами данных скажет что индекс должен помещаться целиком в память, чтоб исключить лишние дисковые операции. В MySQL за размер буффера для хранения индекса отвечает системная переменная key_buffer_size. Но бесконечно его увеличивать не получится - в железе есть ограничение по максимальному количеству ОЗУ, а вот ограничения по максимальному размеру таблиц в базе данных довольно далеко выходят за рамки обычных жестких дисков, поэтому для нас они почти бесконечны. И рано или поздно возникает проблема выхода размеров индекса за рамки key_buffer_size что приводит к резкому снижению скорости выполнения запросов.

Как уменьшить размер индексов в базе данных

Уменьшить размер индексов можно по нескольким направлениям:

  1. Удалить неиспользуемые индексы
  2. Уменьшить размер целочисленных индексов
  3. Оптимизировать хранение строковых индексов
Рассмотрим каждое из направлений подробнее:

Какие индексы можно удалить

Прежде всего стоит понимать что в одной операции сравнения на одной таблице может максимально использоваться лишь только один индекс. Например в запросе

SELECT id, message FROM ticket WHERE manager_id = 3 AND title like "a%";

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

Рассмотрим еще один частовстречающуюся неверную конфигурацию:

SELECT id, email FROM user WHERE is_active = 1

В том что в случае если по индексу is_active MySQL увидит несколько тысяч строк, а таблица большая, - оптимизатор будет делать fullscan, т.к. в этом случае выигрыш от последовательного чтения всей таблицы будет больше, чем необходимость делать несколько тысяч отдельных операций случайного доступа к диску. Таким образом индексы всех тех столбцов, которые содержат в себе лишь несколько различных значений (имеют высокую "повторяемость значений"), можно смело удалять. К таковым, например, относятся различные столбцы хранящие флаги (булевы значения), или значения типа enum. Как вариант - эти столбцы можно добавить к другому составному индексу, если они часто используются как дополнительный фильтр при выборке с другими столбцами.

Переходим к составным индексам. Иногда невнимательные разработчики добавляют несколько индексов и покрывают один индекс другим. Например, если изначально для таблицы user была выставлена индексация столбца added_time, а в дальнейшем из-за частого использования запроса

SELECT id, email FROM user WHERE added_time > now() - INTERVAL 1 DAY AND is_approved = 1

был добавлен составной индекс (added_time, is_approved), то новый полностью покрывает старый - MySQL сможет комфортно использовать этот новый индекс индекс по первому столбцу. В данном случае старый можно смело удалить. Подробнее о том как работают составные индексы можно почитать на сайте документации MySQL

Как уменьшить размер целочисленных индексов

Не стоит забывать что уменьшая тип данных Primary Key небольшой таблицы, мы так же будем уменьшать и тип данных всех внешних ключей к ней. Например если в таблице status 10 строк, а столбец id имеет тип int то, казалось бы, сменив его на тип tinyint мы выиграем всего 30 байт индекса. Но ведь мы сменим и все столбцы status_id в связанных таблицах, а если в тех таблицах суммарно несколько десятков миллионов строк, то можно выиграть десятки и сотни мегабайт в объеме индексного хранилища.

Как правильно выбрать тип для индексного столбца

Для того чтоб верно выбрать тип столбца - нужно понимать сколько уникальных значений в нем будет за время полное время жизни проекта. Потенциальное количество уникальных значений должно целиком покрываться максимальным значением unsigned-варианта типа. Разумеется, если мы говорим об экономии индексного пространства, то значение auto_increment_increment не должно быть более 1.

Пространство которое можно покрыть целочисленными unsigned-типами:

Type Capacity TINYINT 255 SMALLINT 65535 MEDIUMINT 16777215 INT 4294967295 BIGINT 18446744073709551615

Tinyint идеально подойдет под небольшие списки статусов и типов каких-либо сущностей. Smallint будет хорош для списков пользователей внутренней биллинговой системы. Mediumint должно хватить для списков пользователей под проект среднего размера со свободной регистрацией. Int будет достаточным для идентификации почти любых вручную генерируемых объектов. И, наконец, bigint подойдет для идентификации самых массовых сущностей или массово генерируемых событий. Так же bigint подойдет как замена строковых индексов.

Как оптимизировать хранение строковых индексов

Когда необходимо сделать индекс по строке, например по столбцу с типом varchar, то, для сокращения размера индекса, часто индексируют дополнительный столбец char(32) содержащий md5-преобразование от строки. Тем не менее, результат md5-преобразования занимает 32 байта на каждую строку, что тоже весьма затратно. md5 - это набор из 32-х 16-ричных цифр, позволяющий нам охватить пространство в 2^256 или больше чем 10^38. Но для большинства задач хватает намного меньшего диапазона значений. Например, можно сократить char(32) до char(16) и получить экономию в два раза в размере ключа. Но в 16 байтах мы будем хранить 16 символов или 16 16-ричных цифр, которые кодируют 8 байт информации. А значит можно использовать для этого bigint, а не char(16)! Сделать это довольно просто - сначала получаем md5 от нашей строки, далее обрезаем его до 16 символов, затем выполняем преобразование из 16-ричной системы счисления в 10-тичную:

UPDATE article SET id = conv(substring(md5(url), 1, 16), 16, 10);

Кроме кратного уменьшения размера индекса мы так же индекс преобразовали в 64-битное число, с которым современным 64-битным процессорам работается быстрее чем с набором 1-байтовых символов.

Значения id, полученные в последнем примере обладают так же равномерным распределением по всему 8-байтовому пространству, что позволяет делать быстрые случайные выборки из таблицы.