Innodb

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

Небольшое оглавление:

• Вводная информация

• Что представляет из себя индекс в MySql

• Скорость чтения из индекса

• Отличия в индексах MyISAM и InnoDb

• Кластерный индекс

• Первичные и "вторичные" индексы в чем отличия

• Покрывающие индексы

Вводная информация

В MySql существует несколько типов индексов и каждый из них хорош для выполнения своих специализированных задач. Например Hash индекс хорош для хранения данных в виде ключ - значение в оперативной памяти, FULLTEXT индекс для поиска по текстовым документам, SPATIAL для хранения информации о гео-объектах, UNIQUE для уникальных значений. Но все же в подавляющем большинстве случаев мы используем индекс на основе B-дерева (BTREE) или Balanced-Tree. Сбалансированное оно потому что высота каждого поддерева с общим корневым элементом может отличаться, но всегда не более чем на константную величину. Далее в статье речь пойдет именно про BTREE индексы, по умолчанию под индексами буду подразумевать именно их.

Что представляет из себя индекс в MySql

На рисунке изобразил схематично как устроен индекс. Имеются узловые элементы (квадраты) и листья (круги). Предположим у нас есть таблица с колонками "Val" и "ID" как на рисунке. В этой таблице индекс построен по числовому полю "ID". Тогда получается что в узловых элементах находятся значения индекса и ссылки на другой более нижний узел или лист. В листовых же элементах точно так же лежат значения индекса которые уже ссылаются непосредственно на данные из таблицы.

Процесс поиска происходит примерно следующим образом. Например нужно найти строку с индексом 11.

• начинаем просмотр корневого (верхнего) узла

• первое значение в нем 10

• идем к следующему 19, оно уже больше чем нам нужно

• по ссылке слева от 19 переходим к следующему нижнему узлу

• там первое значение 13, оно больше чем нам нужно

• опять по ссылке слева переходим к более нижнему элементу

• это уже будет листовой элемент, в нем уже лежат непосредственно данные

• просматриваем данные по порядку

• находим 11

• переходим по ссылке непосредственно к строке в таблице.

Скорость чтения из индекса

Такое устройство индекса позволяет обеспечить логарифмическую скорость поиска O(log n). Это очень быстро. Вот таблица где для наглядности посчитал сколько сравнений нужно сделать для поиска записи в таблице с разным количеством данных:

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

Отличия в индексах MyISAM и InnoDb

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

Кластерный индекс

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

Более того, если мы говорим о таблицах на основе движка InnoDb, то в целом понятие таблица довольно абстрактное. На картинке она нарисована просто для наглядности. На самом деле ни какой таблицы по сути не существует, а все данные просто хранятся в кластерном индексе.

Первичные и "вторичные" индексы в чем отличия

Выше было оговорено что для MyIsam нет разницы между первичными и "вторичными" ключами.

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

В InnoDb это устроено немного по другому.

Как уже говорил, таблица тут просто для наглядности. Все ее данные хранятся в первичном (кластерном ключе). Тут первичный ключ построен по полю "Id", вторичный по полю "Val". Видно что в листьях первичного ключа лежат значения индекса + все данные из строк таблицы. Во вторичном же ключе, в листьях лежат значения ключа + первичный ключ.

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

Покрывающие индексы

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

Смысл покрывающих индексов в том, что MySql может вытаскивать данные непосредственно из самого индекса, не читая при этом всю строку и вовсе не читая строку. Для такой оптимизации нужно чтобы все поля указанные в SELECT имелись в индексе. То есть например у нас имеется таблица с полями "id", "name", "surname", "age", "address". И мы проиндексировали ее по полю "id". В запросе мы хотим получить например "id" и "name". При таком условии MySql найдет по первичному ключу нужную строку, прочитает ее и отбросит все поля не указанные в SELECT. Если же мы немного оптимизируем этот запрос и построим индкес по двум полям "id" и "name", то в таком случае MySql найдя нужную строку по этому индексу не пойдет читать всю эту строку, а просто возьмет данные, которые нужны непосредственно из индекса. Правда есть обратная сторона такого подхода, а именно размер индекса в этом случае будет больше, по этому нужно грамотно подходить к построению покрывающих индексов.

Более подробно можно почитать в очень хорошей книге "MySQL по максимуму" Бэрон Шварц, Петр Зайцев, Вадим Ткаченко

Если вы работаете с сайтом, который постепенно растет, - увеличивается количество товаров, трафик с рекламы - то рано или поздно придется перейти в режим работы highload, высоких нагрузок на сервер. Но что делать, если ваш сайт не растет, а сервер все чаще не выдерживает, и происходит блокировка данных? Именно с этой проблемой мы столкнулись, дорабатывая сайт для интернет-магазина светового оборудования с ассортиментом более чем 100 000 товаров.

Исходная ситуация

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

Поиск проблемы

Мы провели аудит настроек сервера и сайта, разделив работы на два этапа: анализ back-end и front-end, и обнаружили низкую скорость загрузки страниц на back-ende - порядка 80 секунд на самых посещаемых страницах, что в итоге приводило к существенному снижению конверсии.

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

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

Решение

Шаг 1. Настройка баз данных

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

Шаг 2. Смена типа хранения на InnoDB

Почему мы выбрали InnoDB?

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

Главное преимущество InnoDB заключается в скорости работы при выполнении запроса к базе InnoDB происходит блокировка только строки, при выполнении же запроса к базе MyISAM блокируется вся таблица. Дело в том, что пока запрос не будет выполнен, никакие другие обращения к таблице/строке будут невозможны. А поскольку строки значительно меньше целых таблиц, InnoDB обрабатывает запросы быстрее.

Также была произведена оптимизация работы самой базы данных InnoDB. Например, были оптимизированы параметры:

# InnoDB parameters

innodb_file_per_table

innodb_flush_log_at_trx_commit

innodb_flush_method

innodb_buffer_pool_size

innodb_log_file_size

innodb_buffer_pool_instances

innodb_file_format

innodb_locks_unsafe_for_binlog

innodb_autoinc_lock_mode

transaction-isolation

innodb-data-file-path

innodb_log_buffer_size

innodb_io_capacity

innodb_io_capacity_max

innodb_checksum_algorithm

innodb_read_io_threads

innodb_write_io_threads

Промежуточные результаты

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

Это в свою очередь привело к уменьшению потребляемой оперативной памяти.

Шаг 3. Перенастройка Nginx и установка модулей кэширования brotli, pagespeed, proxy_buffering

Nginx позиционируется как простой, быстрый и надежный сервер, неперегруженный функциями. Уже длительное время Nginx обслуживает серверы многих высоконагруженных российских сайтов, например, Яндекс, Mail.Ru, ВКонтакте и Рамблер. Для улучшения производительности при использовании дополнительных серверов, Nginx поддерживает буферизацию (proxy_buffering) и кеширование (proxy_cache), чем мы и воспользовались.

Не обошлось и без курьезов настроек Nginx. У клиента был обычный интернет-магазин с товарами, тогда как настройки буферизации, которые мы обнаружили во время аудита, позволяли ему быть чуть ли ни стриминговым сервисом. Мы существенно уменьшили значения в параметре client_max_body_size, что в совокупности с перенастройкой Nginx еще больше снизило потребление памяти.

Шаг 4. Оптимизация настроек PHP-FPM и Memcache и отключение Apache

PHP-FPM нередко используется в паре с веб-сервером Nginx. Последний обрабатывает статические данные, а обработку скриптов отдает PHP-FPM. Такая реализация работает быстрее, чем распространенная модель Nginx + Apache.

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

Необходимым шагом стал перевод работы PHP-FPM на unix socket. Зачем это понадобилось? Nginx сам по себе довольно быстрый веб-сервер, однако самостоятельно он не может обрабатывать скрипты. Для этого необходим бэкенд в виде PHP-FPM. Чтобы вся эта связка работала без потери скорости, мы использовали unix socket способ подключения к PHP-FPM, позволяющий избегать сетевые запросы и дающий значительный прирост в скорости работы сайта.

Результаты работ

1. Время отклика главной страницы уменьшилось с 24 секунд до чуть более 3 секунд, внутренних до 5-8 сек.

2. Уменьшилось потребление серверных ресурсов.

3. Стабилизировалось поведение сервера - он перестал зависать.

4. Глубина просмотров увеличилась на 30%, и как следствие, это дало улучшение в SЕО, а также последующих продаж: растут поведенческие показатели => растут позиции сайта в выдаче => растет трафик => растут продажи.

5. Клиенту были даны рекомендации по оптимизации front-end части сайта для ускорения работы сайта. Например:

• оптимизировать графики и настройку выдачи изображений в формате webp;

• настроить lazyload-загрузки данных;

• вынести все некритические для отображения страницы скрипты в конец страницы.

Вывод

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