Хочу поделиться опытом создания механизма генерации большой базы данных товаров. С его помощью наши пользователи могут за несколько минут сгенерировать более миллиона однотипных, но разных записей.
Мы создавали большую систему учета товаров. Количество товаров обещало превысить несколько миллионов и нам стало понятно, что ручного добавления товаров в таблицу будет недостаточно. Товары, как правило, однотипные и отличаются только параметрами. Например:
- Шуруп оцинкованный крест D 3мм длинна 16мм для дерева
- Шуруп черный крест D 3мм длинна 20мм для ГК
- Шуруп оцинкованный шлиц D 4мм длинна 16мм для металла
- Шуруп черный шлиц D 4мм длинна 20мм универсальный
- ...
Шурупов может быть несколько тысяч. Для группы товаров «метизы» нужны еще гвозди, болты, гайки, шайбы, дюбеля… В примере видно, что структура названия состоит из нескольких полей, каждое из которых имеет несколько вариантов значений. Весь набор названий шурупов- это язык (напомню, что язык- это множество строк) и этот язык может быть описан регулярным выражением. Обычно регулярное выражение используется для ответа на вопрос: принадлежит ли строка языку или нет. В нашем случае нужно решить обратную задачу: по регулярному выражению построить весь набор строк или сгенерировать язык. То есть, пользователь может ввести что-то вроде
Шуруп (оцинкованный|черный) D (2|3|3,5|4|5|6|7|8|9|10) длинна (10|12|16|20|25|30|40|50|60|80|100) (крест|шлиц) (для дерева|для металла|для гк|универсальный)
Программа должна сгенерировать всевозможные варианты шурупов. Надо учесть, что некоторые регулярные выражения описывают бесконечные языки, поэтому не любое выражение может использоваться для генерации данных. Например выражение [0-9]+ описывает язык целых неотрицательных чисел и он бесконечен.
Для генерации данных мы используем два способа.
Генерация с помощью пакета Automation
OpenSource пакет Automation умеет работать с конечными автоматами, чем по сути и является регулярное выражение. В числе прочих возможностей, библиотека умеет генерировать строки удовлетворяющие заданному регулярному выражению. Дальше дело техники- сгенерировать все варианты и поместить в базу данных.
Однако у этого способа есть существенный недостаток- скорость работы. Он хорошо работает на нескольких тысячах строк, но на миллионы записей может уходить несколько часов (в основном из-за медленной вставки в БД по одной строчке). Поэтому был разработан второй способ.
Генерация SQL по регулярному выражению
Представьте, что будет если выполнить SQL запрос (диалект MySQL)
select
concat('Шуруп диаметр ', t1.a, 'мм длинна ', t2.a, 'мм')
from (
SELECT '3' as a
union
select '4' as a
) as t1,
(
SELECT '16' as a
union
select '20' as a
) as t2Получится результат из 4 строк, который можно сразу вставить в таблицу:
Шуруп диаметр 3мм длинна 16мм Шуруп диаметр 4мм длинна 16мм Шуруп диаметр 3мм длинна 20мм Шуруп диаметр 4мм длинна 20мм
Этот простой пример показывает, что можно написать SELECT запрос и использовать его как часть INSERT запроса для вставки нужных строк в базу данных. Для генерации такого SQL мы написали несложный анализатор регулярных выражений. Он просто выделяет скобки внутри выражения, затем использует все тот же Automation, чтобы сгенерировать всевозможные варианты строк внутри скобок. То есть например для выражения
Шуруп диаметр (3|4)мм длинна (16|20)мм
Программа выделяет выражения в скобках 3|4 и 16|20, затем с помощью Automation для каждой пары скобок генерирует всевозможные значения и формирует SQL запрос.
Такой запрос выполняется намного быстрее, чем вставка по одной записи и способен добавить миллионы строк за несколько секунд.
Ограничение генерируемых данных
Оставалась последняя проблема. Представьте себе шуруп толщиной 2 мм и длинной 100 мм. Таких не существует. Нужен был механизм ограничения некоторых сочетаний параметров. Проще всего попросить пользователя написать функцию на каком-нибудь скриптовом языке, которая принимает все параметры и возвращает true/false. Но скорее всего это встретило бы непонимание со стороны пользователей. Поэтому мы решили использовать Pivot Table, чтобы пользователь мог выделить нужные сочетания параметров, которые мы потом просто добавляем в условия WHERE внутри SELECT запроса.
На иллюстрации изображена сводная таблица, с раскрытыми столбцами и выделенными желтым цветом ячейками. Синим цветом обозначаются товары, уже добавленные в базу, зеленым, те, что еще не добавлены. Видно что уже добавлены все шурупы черного цвета для дерева, и добавлена половина (110/220) из всех оцинкованных шурупов для металла (чтобы понять какие именно- нужно развернуть столбец «Материал для металла» в сводной таблице). Также пользователь выделил и готовится добавить шурупы для ГК черные под крестовую отвертку и оцинкованные под шлицевую отвертку.
Я опускаю подробности того, как мы обеспечивали удаление ошибочно сгенерированных данных и обработку повторного добавления уже существующих записей. Это больше относиться к тонкостям работы с MySQL (или другой БД) и не соответствует теме поста.
Данный механизм хорошо зарекомендовал себя. Пользователи смогли его освоить и используют в своей работе. На данный момент самый большой объем базы данных товаров у одного из наших клиентов превышает 50 млн записей. Большая часть этого объема была создана в первый месяц работы.
Автор: ukman
