MySQL. Оптимизация псевдо-больших данных

Работая над различными интересными задачами, мне только и приходилось слышать о существовании программ, которые работают с большими данными (в области действия одного сервера). И вот настал тот день, когда к нам обратился клиент, у которого сайт грузился очень долго. Задание для решения этой проблемы выдали моего коллеги. Немного прошло времени, до того как он подозвал меня к себе, с целью показать, столь диковинное для меня зрелище, связанное с объёмом таблиц, в которых находились данные после импорта категорий, характеристик, брендов товаров.

22 миллиона варианта категорий для нас показалось более чем приличным объёмом, тем более — вес таблицы с категориями получился чуть больше 1,6 Гиб. С такими размерами страницы начали грузиться дольше, чем хотелось бы. Ввиду того что, сроки на решение проблемы маленькие (чем быстрей тем лучше), руководство решило выделить на эту задачу, две единицы программистов, меня и моего коллегу. Разделив модуль на двоих, мне достались по объёму не самые сливки, но не менее ответственный участок, поскольку в нём — на загрузку данных, уходило значительное время.

▍ Задача

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

Имеются следующие таблицы:

1. Таблица связи

• id варианта характеристики (variant_id)
• ссылки на картинку (image_link)

Количество строк 995371:

CREATE TABLE `features_variants_links` (
    `variant_id` bigint(64) UNSIGNED NOT NULL,
    `image_link` varchar(255)  default '',
    PRIMARY KEY (`variant_id`)
)ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

2. Таблица связи:

• id варианта характеристики (variant_id)
• наименование характеристики (name)
• описание характеристики (description)
• код языка (lang_code)

Количество строк 2285984:

CREATE TABLE feature_variants_descriptions (
  variant_id bigint(64) UNSIGNED NOT NULL,
  name varchar(255)  default '',
  description text,
  lang_code char(2) NOT NULL default 'ru',
PRIMARY KEY (variant_id,lang_code) )ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

3. Таблица связи:

• id характеристики (feature_id)
• id варианта характеристики (variant_id)

Количество строк 1142994:

CREATE TABLE feature_variants (
  variant_id bigint(64) UNSIGNED NOT NULL,
  feature_id bigint(64) UNSIGNED NOT NULL,
PRIMARY KEY (variant_id,feature_id) ,
  KEY feature_id (feature_id) ,
  KEY variant_id (variant_id) )ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

Запрос наиболее продолжительного времени выполнения процесса:

SELECT SQL_CALC_FOUND_ROWS *
FROM feature_variants AS variants
JOIN features_variants_links AS variants_links ON variants.variant_id = variants_links.variant_id
JOIN feature_variants_descriptions AS variants_descriptions ON variants.variant_id = variants_descriptions.variant_id
WHERE variants_links.feature_id IN (127)
  AND  variants_descriptions.lang_code = 'ru'
ORDER BY variants_descriptions.variant ASC
LIMIT 0, 10

Время выполнения запроса:
Это самый долгий запрос, из моей половины модуля. Решение проблемы я начну с изменения структуры таблиц. Вторым шагом оптимизация самого запроса.

▍ Решение

Первое, что сразу бросается в глаза, и что я не могу пропустить, это наиболее часто встречающийся способ подсчёта строк, с помощью модификатора «SQL_CALC_FOUND_ROWS» и сопутствующая функция «FOUND_ROWS()». В подсистеме хранения данных «MyISAM» этот вариант подсчёта общего количества строк в выборке, использовать не всегда благоразумно, поскольку точное количество строк кэшируется системой хранения. Чего не скажешь о «InnoDB», в которой такие запросы чаще показывают большую скорость.

Если убрать модификатор «SQL_CALC_FOUND_ROWS», и выполнить подсчёт строк во втором запросе через функцию COUNT(*), то получится следующий результат:
После внесённых изменений время выполнения запроса сократилось на 4 секунды. По сравнению с тем что было, разница кажется не существенной, но 4 секунды, на фоне предшествующего времени загрузки, это уже много.

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

1. Таблица будет содержать в себе:

• ключ описания характеристики (feature_descriptions_id)
• код языка (lang_code)
• описание варианта характеристики (description)

Количество строк 224248:

CREATE TABLE test_feature_descriptions (
  feature_descriptions_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT,
  lang_code CHAR(2) NOT NULL default 'ru',
  description text ,
PRIMARY KEY (feature_descriptions_id) ,
 KEY (lang_code))ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

2. Таблица будет содержать в себе:

• ключ наименование характеристики (feature_name_id)
• код языка (lang_code)
• наименование характеристики (name)

Количество строк 10294:

CREATE TABLE test_feature_name (
  feature_name_id BIGINT(64) UNSIGNED NOT NULL AUTO_INCREMENT,
  lang_code CHAR(2) NOT NULL default 'ru',
  name varchar(255)  default '' ,
PRIMARY KEY (feature_name_id) ,
KEY (lang_code))ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

3. Таблица будет содержать в себе:

• ключ варианта имени характеристики и описания характеристики (feature_name_descriptions_id)
• вариант характеристики (variant_id)
• связь с таблицей «test_feature_name» (feature_name_id)
• связь с таблицей «test_feature_descriptions» (feature_name_descriptions_id)

Количество строк 224998:

CREATE TABLE test_feature_name_descriptions (
  feature_name_descriptions_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  variant_id BIGINT(64) UNSIGNED NOT NULL,
  feature_name_id BIGINT(64) UNSIGNED NOT NULL,
  feature_descriptions_id BIGINT(64) UNSIGNED NOT NULL ,
PRIMARY KEY (feature_name_descriptions_id) ,
  KEY (variant_id, feature_descriptions_id, feature_name_id))ENGINE=MyISAM DEFAULT CHARSET=UTF8

---

Как можно видеть, объём данных уменьшился примерно в 4 раза. Запрос теперь выглядит так:

SELECT * FROM feature_variants AS variants
JOIN features_variants_links AS variants_links ON variants.variant_id = variants_links.variant_id
LEFT JOIN test_feature_name_descriptions AS variants_descriptions ON variants.variant_id = variants_descriptions.variant_id
LEFT JOIN test_feature_name ON test_feature_name.feature_name_id = variants_descriptions.feature_name_id
LEFT JOIN test_feature_descriptions ON test_feature_descriptions.feature_descriptions_id = variants_descriptions.feature_descriptions_id
WHERE variants_links.feature_id IN (127)
  AND test_feature_name.lang_code = 'ru'
  AND test_feature_descriptions.lang_code = 'ru'
ORDER BY test_feature_name.name ASC
LIMIT 0, 10

За счёт уменьшения объёма данных удалось улучшить показатель скорости. Но всё равно долго. Для такой структуры таблиц напрашивается тип «InnoDB». Так как внешние ключи «MyISAM» не поддерживает. Ради эксперимента я решил попробовать сначала без связи внешних ключей, и замерить разницу. Вот результат:
Даже без внешних ключей, у «InnoDB» видно преимущество. Вариант с использованием функции FOUND_ROWS():
Выиграли примерно полсекунды. Но здесь есть свои нюансы. Данный вариант уже можно считать устаревшем с версии MySQL 8.0.17, и разработчики в дальнейшем обещают удалить модификатор SQL_CALC_FOUND_ROWS и сопутствующую функцию. Поэтому (по возможности) лучше избегать этот метод и использовать COUNT(*), как рекомендует автор документации. Ссылка на источник.

Итак, для таких запросов, более эффективно себя показывает система хранения «InnoDB». Если добавить таблице «test_feature_name_descriptions» «FOREIGN KEY», то можно добиться ещё чуть большей производительности. Я добавил два внешних ключа на поле «feature_name_id» и «feature_name_descriptions_id»
Даже без внешних ключей, у «InnoDB» видно преимущество. Вариант с использованием функции FOUND_ROWS()
На этом этапе у меня получилась максимальная производительность. Можно добиться скорости выполнение за доли секунд, за счёт добавления ещё одной таблицы, которая уменьшит количество элементов в выборке до сотен. Но это метод уже выходит за рамки показанных таблиц моей половины модуля, и данная статья была бы уже не актуальна, так как в том варианте я не обнаружил бы просадку производительности.

Второй вариант предполагает пожертвовать сортировкой «ORDER BY», например, предоставить выбор сортировки пользователю, по нажатию на соответствующую кнопку, подгружая данные ajax-ом. Без неё скорость первого запроса составила 0.00096500 секунды. Поскольку оптимизировать таблицы уже некуда, то можно заняться оптимизацией самого запроса. Первый запрос можно не трогать, так как получаемые данные из запроса нам нужны. Запрос на получение количества данных будет иметь следующий вид:

SELECT * FROM feature_variants AS variants
JOIN features_variants_links AS variants_links ON variants.variant_id = variants_links.variant_id
WHERE variants_links.feature_id IN (127)

Я убрал LEFT JOIN, так как он не влияет на количество элементов в выборке. ORDER BY и LIMIT, были исключены ещё в предыдущих запросах. Результат получился таким:

▍ Итог

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

Также в коде, был доработан метод, для того чтобы он, из запроса убирал не только ORDER BY и LIMIT, но и LEFT JOIN. Для небольших данных, это решение задачи вполне подходит, но если объём разрастётся до действительно больших размеров данных, то решение будет выглядеть по-другому. А как — предлагаю обсудить в комментариях.

Telegram-канал с полезностями и уютный чат