Нужен совет в модели данных

Нужна консультация по модели данных для моего варианта использования. У меня есть два параметра для хранения: A для вещей типа T и B для вещей типа U (который установлен в T) Допустим, что каждый объект типа T имеет 2 свойства p1 и p2, теперь A = (счетчик t с p1)/(счетчик t с p1) + (счетчик t с p1)

B = (A1 + A2 +..) для своего множества T's/(число T в U).

Теперь я должен решить проблему хранения и обновления как A, так и B всякий раз, когда новый объект типа T добавлен/изменен. (Почти мгновенно)

Я решил заняться вычислением A следующим образом: для поддержания таблицы типа (T id, № p1, № p2), тем самым каждый раз, когда число изменяется, я просто обновляю 2-й или 3-й столбцы и я может рассчитывать A на лету. Но я смущен тем, как оптимизировать вычисления B?? Мои первоначальные мысли заключались в том, чтобы написать триггер над таблицей выше, чтобы всякий раз, когда что-то обновлялось, пересчитайте B для этого объекта U, но я думаю, что это дало бы мне очень низкую производительность при масштабировании, Любые предложения, что еще я могу сделать здесь?

Пример: Скажем, U - город с множеством блоков (T). Теперь, каждый блок будет говорить p1 количество ресторанов non veg и p2 число veg. Таким образом, A для каждого блока будет p1/(p1 + p2) и B для каждого города будет A1 + A2 +../count (блоки) в этом городе. Как сохранить первоначально рассчитанные A и B для всех объектов, так что когда p1 и p2 продолжают меняться, мне нужно обновить A и B почти мгновенно.

Добавление метрик, чтобы иметь более четкое представление о желаемом решении,

У меня уже есть 100 тыс. блоков и около 1000 городов. И это число будет расти в будущем. Мое требование, когда я вычислил А и B для всех существующих данных, любое обновление к p1 и p2, которое причины изменения говорят "deltaA". Теперь этот "deltaA" должен быть легко добавлен к "A", а не пересчитывать A (аналогично для B), разве мы не можем иметь некоторую модель данных, которая может поддержать это?
Задержка должно быть ~ 100 мс i, e A и B должны быть доступны после изменения p1/p2.
Частота записи будет в шипах, это будет 100 или 1000 пишет одновременно или 3-5.

Ответ 1

Используя пример ваших городов/блоков, ваша схема может выглядеть примерно так:

CREATE TABLE cities (
    `city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `country_id` TINYINT UNSIGNED NOT NULL,
    `zip` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`city_id`)
);

CREATE TABLE blocks (
    `block_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `city_id` SMALLINT UNSIGNED NOT NULL,
    `p1` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
    `p2` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (`block_id`),
    FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`)
);

Ваш запрос для данного города (city_id = 123) будет выглядеть следующим образом:

Запрос 1

SELECT AVG(p1/(p1+p2)) AS B
FROM blocks b
WHERE b.city_id = 123

Примечание: AVG(x) = SUM(x) / COUNT(x)

Теперь, если вы беспокоитесь о производительности, вы должны определить некоторые ожидаемые числа:

Количество городов
(Средний) Количество блоков в городе
Оборудование, которое вы будете/можете использовать
Запросы, которые вы обычно запускаете
Количество запросов в час/минута/сек

Если вы определили эти числа, вы можете создать некоторые фиктивные/поддельные данные для запуска тестов производительности.

Вот пример с 1000 городами и 100K блоками (в среднем по 100 блоков в каждом городе):

Сначала создайте вспомогательную таблицу с порядковыми номерами 100K:

CREATE TABLE IF NOT EXISTS seq100k
    SELECT NULL AS seq
    FROM information_schema.COLUMNS c1
    JOIN information_schema.COLUMNS c2
    JOIN information_schema.COLUMNS c3
    LIMIT 100000;
ALTER TABLE seq100k CHANGE COLUMN seq seq MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;

С помощью MariaDB вы можете использовать плагин последовательности.

Сгенерировать данные:

DROP TABLE IF EXISTS blocks;
DROP TABLE IF EXISTS cities;
CREATE TABLE cities (
    `city_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `country_id` TINYINT UNSIGNED NOT NULL,
    `zip` VARCHAR(50) NOT NULL,
    `name` VARCHAR(100) NOT NULL,
    PRIMARY KEY (`city_id`)
)
    SELECT seq AS city_id
         , floor(rand(1)*10+1) as country_id
         , floor(rand(2)*99999+1) as zip
         , rand(3) as name
    FROM seq100k
    LIMIT 1000;

CREATE TABLE blocks (
    `block_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `city_id` SMALLINT UNSIGNED NOT NULL,
    `p1` SMALLINT UNSIGNED NOT NULL DEFAULT '0',
    `p2` SMALLINT UNSIGNED NOT NULL DEFAULT '1',
    PRIMARY KEY (`block_id`),
    FOREIGN KEY (`city_id`) REFERENCES `cities` (`city_id`)
)
    SELECT seq AS block_id
         , floor(rand(4)*1000+1) as city_id
         , floor(rand(5)*11) as p1
         , floor(rand(6)*20+1) as p2
    FROM seq100k
    LIMIT 100000;

Теперь вы можете запускать свои запросы. Обратите внимание, что я не буду использовать точное время выполнения. Если вам нужно, чтобы они были точными, вы должны использовать профилирование.

Запуск Query 1 мой GUI (HeidiSQL) показывает 0.000 sec, который я называю "почти мгновенным".

Возможно, вы захотите выполнить запрос типа:

Запрос 2

SELECT b.city_id, AVG(p1/(p1+p2)) AS B
FROM blocks b
GROUP BY b.city_id
ORDER BY B DESC
LIMIT 10

HeidiSQL показывает 0.078 sec.

Используя индекс покрытия

ALTER TABLE `blocks`
    DROP INDEX `city_id`,
    ADD INDEX `city_id` (`city_id`, `p1`, `p2`);

вы можете уменьшить время выполнения до 0.031 sec. Если это не достаточно быстро, вы должны подумать о некоторых стратегиях кэширования. Одним из способов (помимо кэширования на уровне приложения) является использование триггеров для управления новым столбцом в таблице cities (просто позвоните ему B):

ALTER TABLE `cities` ADD COLUMN `B` FLOAT NULL DEFAULT NULL AFTER `name`;

Определите триггер обновления:

DROP TRIGGER IF EXISTS `blocks_after_update`;
DELIMITER //
CREATE TRIGGER `blocks_after_update` AFTER UPDATE ON `blocks` FOR EACH ROW BEGIN
    if new.p1 <> old.p1 or new.p2 <> old.p2 then
        update cities c
        set c.B = (
            select avg(p1/(p1+p2))
            from blocks b
            where b.city_id = new.city_id
        )
        where c.city_id = new.city_id;
    end if;
END//
DELIMITER ;

Обновить тест:

Запрос 3

UPDATE blocks b SET p2 = p2 + 100 WHERE 1=1;
UPDATE blocks b SET p2 = p2 - 100 WHERE 1=1;

Этот запрос выполняется в 2.500 sec без триггера и 60 sec с помощью триггера. Это может показаться большим количеством накладных расходов. Но подумайте, что мы обновляем 100K строк дважды - это означает среднее значение 60K msec / 200K updates = 0.3 msec/update.

И теперь вы можете получить тот же результат от Query 2 с помощью

Запрос 4

SELECT c.city_id, c.B
FROM cities c
ORDER BY c.B DESC
LIMIT 10

"почти мгновенно" (0.000 sec).

Вы можете оптимизировать триггер, если вам нужно. Использование дополнительного столбца block_count в таблице cities (которому также необходимо управлять с помощью триггеров).

Добавить столбец:

ALTER TABLE `cities`
    ADD COLUMN `block_count` MEDIUMINT UNSIGNED NOT NULL DEFAULT '0' AFTER `B`;

Идентификационные данные:

UPDATE cities c SET c.block_count = (
    SELECT COUNT(*)
    FROM blocks b
    WHERE b.city_id = c.city_id
)
WHERE 1=1;

Перепишите триггер:

DROP TRIGGER IF EXISTS `blocks_after_update`;
DELIMITER //
CREATE TRIGGER `blocks_after_update` AFTER UPDATE ON `blocks` FOR EACH ROW BEGIN
    declare old_A, new_A double;
    if new.p1 <> old.p1 or new.p2 <> old.p2 then
        set old_A = old.p1/(old.p1+old.p2);
        set new_A = new.p1/(new.p1+new.p2);
        update cities c
            set c.B = (c.B * c.block_count - old_A + new_A) / c.block_count
            where c.city_id = new.city_id;
    end if;
END//
DELIMITER ;

С помощью этого триггера Запрос 3 теперь работает в 8.5 sec. Это означает накладные расходы 0.03 msec за обновление.

Обратите внимание, что вам также необходимо определить триггеры INSERT и DELETE. И вам нужно будет добавить больше логики (например, для обработки изменений в city_id при обновлениях). Но также возможно, что вам вообще не понадобится спусковой крючок.

Ответ 2

Вы также можете использовать метриализованное представление (понятие postgres), в mysql его не существует, но вы можете использовать таблицу для этого:

CREATE TABLE analyzeVeg 
SELECT b.city_id, AVG(p1/(p1+p2)) AS B
FROM blocks b
GROUP BY b.city_id;

Принял меня 1000 городов и 100 000 блоков 200 мс для создания и почти для запроса

select * from analyzeVeg

около 1 мс.

Вы можете либо актуализировать данные в триггере, либо в логике приложения:

UPDATE analyzeVeg a set B=(SELECT AVG(p1/(p1+p2)) FROM blocks b where b.city_id = a.city_id) WHERE a.city_id IN(SELECT city_id FROM blocks where block_id IN( :updated_block_ids ))

потребовалось 19 мс для обновления