我有一个包含3个表(a,b,c)的数据库,我需要将其保持在某个阈值以下。
a与b和c有一对多的关系。。。
具体来说,a、b和c有一个名为“g_id”的列,用于相互建立关系。。有点像一个图结构,其中a、b和c分别是图、节点和边。
我的目标是:每天,一个脚本都会得到这个数据库的大小,并从这三个表中删除行,直到数据库的总大小缩小到目标大小为止。
我尝试了以下操作:
获取数据库的大小
SELECT
TABLE_NAME,
round(((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024), 2) as SIZE_MB
FROM
information_schema.TABLES
WHERE
TABLE_NAME in ('A', 'B', 'C') AND
TABLE_SCHEMA = DATABASE()
ORDER BY
SIZE_MB DESC
尝试估计(a,b,c)的每个逻辑分组相对于它们的g\u id的大小。。。
SELECT
g_id,
SUM(length(col1)) + SUM(constant) as total
FROM (
(SELECT A.g_id, A.col1, 22 as constant FROM A) UNION ALL
(SELECT B.g_id, B.col1, 22 as constant FROM B) UNION ALL
(SELECT C.g_id, C.col1, 22 as constant FROM C) UNION ALL
) ABC
GROUP BY g_id
ORDER BY g_id;
其中22常量只是粗略估计每行存储一些固定的bigint、时间戳等的成本。。。col1是长度可变的文本字段。
在内存中加载第2部分之后,循环遍历所选的行并以编程方式将g\u id添加到列表中,直到选择了足够的行进行删除,以使数据库恢复到所需的大小。
最后,对表a、b、c执行delete where g\u id in({g\u id})。。。
问题是,从第1部分返回的大小似乎产生了一些“过头”的成本。例如,当我运行第1部分时,数据库的大小约为3gb,但当我将第2部分中的所有行相加时,数据库的大小仅为2Gb。而且随着表格的增长,似乎不存在可预测的增长差异。
information_schema.tables和我的估计和查询报告的大小不一致使我删除的行比实际需要的多。
一些问题:
我处理这个问题的方法正确吗?
我算错了吗?
如何确定总成本?
2条答案
按热度按时间hec6srdp1#
我认为你想删除记录的做法是错误的。删除记录是一项非常昂贵的操作。
相反,应该使用表分区。我不太清楚逻辑是什么,但这三个表都可以分区
g_id
. 假设这是按顺序分配的,您可以创建大约相当于一天新图形的批处理。然后可以通过删除旧分区来“删除”行。
q7solyqu2#
在innodb中,测量行大小是相当麻烦的。我发现将字段大小相加(8表示
BIGINT
,以及VARCHAR
,然后乘以2或3,将接近innodb所需的空间。更容易得到
Avg_row_size
从SHOW TABLE STATUS
(或同等标准)information_schema.Tables
).除非行大小变化很大,否则循环通过行不会变得更精确。即便如此,“平均值”也可能同样好。
对
PARTITION BY RANGE(TO_DAYS(..))
是一个很好的加速删除的方法。然后,您可以查看分区的大小,以确定何时应该删除最旧的分区。但是,假设您要删除“某个日期之前的所有行”。有关此类分区的更多信息:http://mysql.rjweb.org/doc.php/partitionmaint你说你的数据太大了,以至于分散在多个服务器上(“分片”)?请提供更多细节。通常有一些技术可以用来避免如此快速的数据爆炸。
例如,如果您正在使用
POINT
对于纬度和经度,这大约是在不使用POINT
.