Как обрабатывать статистику за длительный период

К нам поступил запрос:

Статистика по поиску артикулов на сайте не отображается, долго грузит страницу и выдает в конечном итоге 500 ошибку сервера

Изучаем проблему

Интернет-магазин по продаже автомобильных запчастей для b2b и b2c, написан на Bitrix, в качестве базы данных используется MariaDB 10.3 Series и Memcached Server для кэширования. Страница находится в административной части bitrix и подгружает данные из выделенной таблицы. Данные следующие:

Поля таблицы: запрашиваемый артикул и бренд запчасти, ID поставщика (интернет-магазин продает не только запчасти со своего склада, но так же и со сторонних складов и платформ), ID магазина (интернет-магазин реализован с применением мультисайтовости, то есть на одной платформе работает большое количество сайтов), количество найденных запчастей и их аналогов, и дата поиска артикула.

По умолчанию выгружалась стратистика за весь период, а это 6 лет данных (с 2013 по 2018 год, включительно), 7 миллионов записей в таблице БД:

Еще и запрос содержит в себе вычисления и логику:

SELECT 
    article, 
    brand_title, 
    supplier_id, 
    count(*) as requests, 
    round(count(IF(variants>0,1,NULL))/count(*)*100,0) as good_requests, 
    round(count(IF(analogs>0,1,NULL))/count(*)*100,0) as analog_exist, 
    round(avg(NULLIF(analogs ,0)),0) as avg_analogs, 
    count(IF(variants>0,1,NULL)) as article_found, 
    count(*) - count(IF(variants>0,1,NULL)) as article_not_found 
FROM `b_lm_search_statistics` 
WHERE (
    supplier_id IS NULL OR 
    supplier_id IN ('123', ...)
) 
GROUP BY article, brand_title, supplier_id 
ORDER BY requests DESC;

Неудивильно, что запрос выполнялся большее 300 секунд, а страница грузится 8 минут!

Индекс для таблицы

В самом начале мы посмотрели результат выполнения EXPLAIN:

Конечно же, хоть индексы и были у этой таблицы, они просто не использовались для данного запроса, индекс в данном случае тут нужен для группировки GROUP BY article, brand_title, supplier_id.

Добавляем комплексный индекс:

ALTER TABLE `b_lm_search_statistics` ADD INDEX group_index (`article`, `brand_title`, `supplier_id`);

Ура, теперь индекс применяется, смотрим еще раз результат выполнения EXPLAIN:

Время выполнения запроса сократилось в 15 раз, что составило приблизительно 20 секунд вместо 600 секунд без индекса.

В дальнейших поисках

Серьезно менять архитектуру проекта не хотелось, то есть применять, например sphynx, поэтому решили обойтись тем, что есть:

1) Отключение группировки и добавление лимита невозможно, так как он собирает суммарные данные по всей таблице, за выбранный период времени.

2) Оптимизация запроса, убрать все эти вычисления из sql, серьезно не сократит время выполнения запроса.

3) Пробовали добавить представление, но это невозможно, так как может выполняться фильтрация по данным, которых не будет уже в представлении, оно содержит агрегированные данные.

Партицирование данных MySQL

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

Задумка была следующая, если мы сделаем партиции по годам, то время выполнения запроса за временные интервалы должно сократиться, так как БД будет делать выборку не по всей таблице, а по партициям.

Пробуем. Выполнять будем на текущей таблице, без создания новой. Указанные действия мы выполняли в девелоперской среде и потом переносили все изменения с помощью миграций.

Если вы создаете партиции на боевом сайте, то нужно исключить попадание новых данных в таблицу, для этого останавливаем веб-сервер nginx, apache или делаем в отдельной таблице. Не забываем сделать бэкап!

Шаг 1. Убираем AUTO INCREMENT из таблицы:

ALTER TABLE `b_lm_search_statistics` MODIFY `id` INT(11) NOT NULL;

Шаг 2. Удаляем PRIMARY key из таблицы, для таблицы с партицированием он другой, комплексный, содержит в себе помимо id, еще и ключ по которому делаем партицирование данных:

ALTER table `b_lm_search_statistics` DROP PRIMARY KEY;

Шаг 3. Создаем новый PRIMARY KEY, партицирование делаем по годам, то есть нам нужен ключ added:

ALTER TABLE `b_lm_search_statistics` ADD CONSTRAINT `primary` PRIMARY KEY (`id`, `added`);

Шаг 4. Возвращаем AUTO INCREMENT.

ALTER TABLE `b_lm_search_statistics` MODIFY `id` INT(11) NOT NULL AUTO_INCREMENT;

Шаг 5. Делаем разбиение, в запросе указываем все года с временной меткой окончания года, для удобства пользуемся функцией UNIX_TIMESTAMP, а для текущего указываем MAXVALUE:

ALTER TABLE `b_lm_search_statistics` PARTITION BY RANGE (UNIX_TIMESTAMP(`added`))
(
    PARTITION p2013 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01 00:00:00')),
    PARTITION p2014 VALUES LESS THAN (UNIX_TIMESTAMP('2015-01-01 00:00:00')),
    PARTITION p2015 VALUES LESS THAN (UNIX_TIMESTAMP('2016-01-01 00:00:00')),
    PARTITION p2016 VALUES LESS THAN (UNIX_TIMESTAMP('2017-01-01 00:00:00')),
    PARTITION p2017 VALUES LESS THAN (UNIX_TIMESTAMP('2018-01-01 00:00:00')),
    PARTITION pMAXVALUE VALUES LESS THAN (MAXVALUE)
)

Проверяем, что получилось. Выполняем запрос для выворки статистики за 2 месяца, раньше было ~20 секунд за любой временной интервал, теперь меньше 2 секунд. Это успех, ускорение в 10 раз!

Дорабатываем страницу в административной панели Битрикс

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

Находим строчку с установкой данных из фильтра:

$filter_date_from = strlen($_GET['filter_date_from']) > 0 ? (string) $_GET['filter_date_from'] : '';

и меняем его на:

// Если у нас не указан ни один фильтр, добавляем в фильтр даты от, дату, которая была 30 дней назад
$defaultDate = empty($_GET['filter_article']) && 
               empty($_GET['filter_brand_title']) && 
               empty($_GET['filter_date_from']) &&
               empty($_GET['filter_date_to'])
    ? date("d-m-Y", mktime(0, 0, 0, date('m'), date('d') - 30, date('Y'))) 
    : '';

$filter_date_from = strlen($_GET['filter_date_from']) > 0 ? (string) $_GET['filter_date_from'] : $defaultDate;

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