Медленный UPDATE в MySQL? Разгоним!

Небольшое введение

Пришло мне недавно ТЗ на проект с большим товарным каталогом. Причем большим - это мягко сказано. Всего номенклатура насчитывала порядка 230 000 товарных позиций. Но было одно утешение, файлы обновления приходили не полностью, а где-то по 24 000 позиции. Что, в принципе, тоже немало.

Забыл оговориться, что этот проект я разрабатывал на CMS 1С-Битрикс. Это был не типовой интернет-магазин, это была уникальная разработка закрытой системы заказа запчастей с кучей нюансов.

И вот настал тот день, когда нужно было наладить полноценную обработку присылаемых выгрузок.

Пути решения поставленной задачи

Мысленно понимая, что в один шаг скрипт мне не обработает такое количество информации (как оказалось позже, это была опрометчивая мысль), было принято решение написать пошаговое обновление каталога, используя AJAX.

Сказано - сделано! Благо сложного в этом ничего нет. Настало время запустить обработку. Время шага я установил в 30 секунд. Жамкаю "Обновить". Жду... Проходит 30 секунд, получаю ответ с первого шага: "Обновлено 36 элементов из 24274". Wat?! Какого черта? Тридцать шесть элементов за 30 секунд? Да я часов 5 буду ждать, пока все обновится! Битрикс, что ты делаешь, ахаха, прекрати =\

Конечно же я в курсе бугурта по поводу тяжеловесного API Битрикса. Лично я всегда пропускал это сквозь пальцы, кому не нравится - пусть просто не используют его, это личный выбор каждого. Я не имею ничего против API Битрикса, как старого, так и движка D7. Но все же я решил отказаться от чудесного апи и переписал код на голом php + mysql.

Запускаю скрипт второй версии. На сей раз я поставил время шага 60 секунд. Жду... "Обновлено 232 элементов из 24274". Хм.. Ну, что ж, уже лучше. Но не идеально же! Причем ого-го как не идеально! В чем же дело? Почему так медленно? И я вежливо ушел курить интернет.

Теория и практика

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

Небольшое отступление. Я предварительно изменил место хранения свойств элементов инфоблока в отдельной таблице. Результата это не дало. Но только на первом этапе. Идем далее.

Итак, воспользовавшись встроенным инструментом выполнения SQL-запросов Битрикса, я выполнил свой запрос на обновление одного элемента:

Гребаная печальбеда, 0.3 секунды! На один запрос! И это в лучшем случае. При повторении процедуры несколько раз, бывало выходило и 0.8. Отсюда мы и получаем около 40 запросов за 30 секунд, что просто ни о чем.

Хорошо, надо собраться с силами и продолжать! Я, честно признаться, не сильный знаток в области глубокого анализа и оптимизации MySQL баз. Но делать нечего, придется вникать.

И первое, что я сделал - это подставил выражение (поправьте, если это называется не так) EXPLAIN перед своим запросом. Для чего это надо. Для того, чтобы получить полезную информацию о запросе и строении таблицы в целом. Вот, что я получил на выходе:

Я честно не знал, что значат все эти поля, просто догадывался. Поэтому ушел гуглить. И нагуглил... Мать честная! Это что же получается!

Оптимизация и индексы

Нам важны 2 столбца из этой таблицы: это столбцы key и rows. Начну со второго. Rows указывает на то, сколько строк было затронуто при запросе. В моем случае были затронуты ВСЕ строки. Каждый раз, при каждом UPDATE, MySQL выбирал мне 235 451 запись, проходился по ним, находил нужную и обновлял. Та-дам! Вот тебе и 0.3 секунды на один запрос. Еще бы, обойди попробуй быстрее такое количество информации.

Как же решить эту проблему? Оказывается, существует такая вещь, как индексы таблиц. Они отображаются в столбце key.

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

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

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

Для создания индексов таких больших таблиц лучше использовать командную строку и утилиту mysql. Просто подключаетесь по SSH к серверу, подключаетесь к mysql и выполняете запрос на создание индекса.

Вот запрос на создание простого индекса, который я использовал:

ALTER TABLE b_iblock_element_prop_s1 ADD INDEX ind_article (PROPERTY_1(50));

Пара пояснений:

• ind_article - название индекса.
• PROPERTY_1(50) - столбец и (длина строки). Для текстовых полей указание длины ОБЯЗАТЕЛЬНО!

Через консоль индекс создается практически моментально (честно не знаю, почему так - через админку провал, через консоль - мгновенный успех).

Финальные замеры

После всего проделанного снова проводим замер запроса:

Примерно в 500 раз быстрее, Карл! Ну ничего себе! А что нам скажет EXPLAIN:

Rows стало равным 1, как раз то, чего мы и добивались. Теперь MySQL знает, где искать эту запись и ей не надо вытаскивать все. Также в столбце key и possible_keys мы видим название нашего созданного индекса.

Ну, что ж, осталось запустить обновление тех 24274 товаров и посмотреть, за сколько же пройдет у нас обновление. Время шага оставляю 60 секунд. Жамкаю "Обновить"...

Послесловие

... проходит 4 секунды и я вижу надпись "Товары были успешно обновлены". Не веря своим глазам, я лезу проверять последний товар из файла - обновилось! Всё прошло успешно!

Вот таким нехитрым... хотя... вполне себе хитрым способом, я смог разогнать скорость обновления товаров более, чем в 500 раз, и сократить время обновления 24 000 товаров с 5 часов до феноменальных 4 секунд.

Удачных вам оптимизаций! ;)