gpt4 book ai didi

mysql - 需要数据模型方面的建议

转载 作者:IT王子 更新时间:2023-10-29 00:34:26 25 4
gpt4 key购买 nike

需要有关我的用例的数据模型的建议。我有两个参数要存储,A 用于 T 类型的东西,B 用于 U 类型的东西(它是 T 的集合)假设每个 T 类型的对象都有 2 个属性 p1 和 p2,现在A= (t 与 p1 的计数)/(t 与 p1 的计数)+(t 与 p1 的计数)

B= (A1+A2+.. ) 表示它的 T's 集合/(U 中 T's 的数量)。

现在,每当添加/修改 T 类型的新对象时,我都必须处理 A 和 B 的存储和更新问题。(几乎立即)

我决定按如下方式处理 A 的计算,以维护一个类似 (T id, No. of p1, No. of p2) 的表,因此每次数字变化时我只更新第 2 列或第 3 列并且 i可以即时计算 A。但我对如何优化 B 的计算感到困惑?我最初的想法是在上面的表格上写一个触发器,这样每当有东西更新时,就为那个 U 对象重新计算 B,但我认为当我扩大规模时,这会给我带来非常糟糕的性能,有什么建议我还可以在这里做什么?

示例:假设 U 是一个有许多街区 (T) 的城市。现在,每个街区都会有 p1 数量的非蔬菜餐厅和 p2 数量的蔬菜。因此,每个 block 的 A 为 p1/(p1+p2)每个城市的 B 将是该城市的 A1+A2+../count(blocks)。我如何为所有对象存储最初计算的 A 和 B,这样当 p1 和 p2 不断变化时,我几乎需要立即更新 A 和 B。

添加指标,以更清楚地了解所需的解决方案,

  1. 我已经拥有 10 万个街区和近 1000 个城市。而这个数字 future 会上升。我的要求是,一旦我计算出 A和 B 对于所有现有数据,对 p1 和 p2 的任何更新导致变化说'deltaA'。现在这个'deltaA'应该很容易附加到'A'而不是重新计算A(对于B类似),我们不能有一些可以支持这个的数据模型吗?
  2. 延迟应该是 ~100ms,即 A 和 B 在 p1/p2 更改后应该可用。

  3. 写入频率会出现峰值,会是 100 或 1000同时写入或 3-5 个。

最佳答案

使用您的城市/街区示例,您的架构可能类似于:

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;

现在您可以运行查询了。请注意,我不会使用确切的运行时间。如果您需要它们准确无误,则应使用分析。

运行查询 1 我的 GUI (HeidiSQL) 显示 0.000 秒,我称之为“几乎即时”。

您可能想要运行如下查询:

查询 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 秒

使用覆盖索引

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

您可以将运行时间减少到 0.031 秒。如果这还不够快,您应该考虑一些缓存策略。一种方法(除了在应用程序级别缓存之外)是使用触发器来管理 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 秒,在有触发器的情况下运行 60 秒。这看起来可能有很多开销 - 但考虑一下,我们要更新 100K 行两次 - 这意味着平均 60K 毫秒/200K 更新 = 0.3 毫秒/更新

现在您可以使用 查询 2 获得相同的结果

查询 4

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

“几乎立即”(0.000 秒)。

如果需要,您仍然可以优化触发器。在 cities 表中使用附加列 block_count(也需要使用触发器进行管理)。

添加列:

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 秒 内运行。这意味着每次更新的开销为 0.03 毫秒

请注意,您还需要定义 INSERT 和 DELETE 触发器。并且您将需要添加更多逻辑(例如,处理更新时 city_id 中的更改)。但也有可能您根本不需要任何触发器。

关于mysql - 需要数据模型方面的建议,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43849442/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com