mysql - MYSQL 对 4000 万条记录表的大量更新需要很长时间才能使用 128GB ram 专用服务器
问题描述
我们在需要很长时间才能更新单个表时遇到问题。该表包含约 3000 万行。
该作业每天运行,截断表并在该表中插入来自其他来源的新数据。
这是表格:
CREATE TABLE tempportfolio1 (
SR_NO int(4) NOT NULL AUTO_INCREMENT,
TR_DATE date DEFAULT NULL,
TRAN_CODE decimal(18,0) DEFAULT NULL,
TRAN_TYPE varchar(20) DEFAULT NULL,
SCH_CODE bigint(8) DEFAULT NULL,
Nature varchar(25) DEFAULT NULL,
UNITS decimal(19,4) DEFAULT NULL,
BAL_UNITS decimal(19,4) DEFAULT NULL,
DIVD_RECD double DEFAULT '0',
FOLIO_NO varchar(50) DEFAULT NULL,
FLAG varchar(5) DEFAULT NULL,
MBALANCE double DEFAULT NULL,
PBALANCE double DEFAULT NULL,
MTotalBalance double DEFAULT NULL,
PL_NOTIONAL decimal(19,4) DEFAULT NULL,
PL_BOOKED decimal(19,4) DEFAULT NULL,
AGE int(4) DEFAULT NULL,
RET_ABS decimal(19,4) DEFAULT NULL,
RET_CAGR decimal(19,4) DEFAULT NULL,
INDEX_AMT decimal(19,4) DEFAULT NULL,
RET_INDEX_ABS decimal(19,4) DEFAULT NULL,
Ret_Index_CAGR decimal(19,4) DEFAULT NULL,
CURRENT_AMT decimal(19,4) DEFAULT NULL,
GAIN_LOSS_LT decimal(19,4) DEFAULT NULL,
GAIN_LOSS_ST decimal(19,4) DEFAULT NULL,
UNITS_FOR_DIVID decimal(19,4) DEFAULT NULL,
factor double DEFAULT NULL,
LatestNav double DEFAULT '10',
NavDate date DEFAULT NULL,
IType int(4) DEFAULT NULL,
Rate double DEFAULT NULL,
CurrAmt double DEFAULT NULL,
IndexVal double DEFAULT NULL,
LatestIndexVal double DEFAULT NULL,
Field int(4) DEFAULT NULL,
Client_Code int(4) DEFAULT NULL,
Branch_Code int(4) DEFAULT NULL,
Rm_Code int(4) DEFAULT NULL,
Group_Name varchar(100) DEFAULT NULL,
Type1 varchar(20) DEFAULT NULL,
Type2 varchar(20) DEFAULT NULL,
IsOnline tinyint(3) unsigned DEFAULT NULL,
SFactor double DEFAULT NULL,
OSch_Code int(4) DEFAULT NULL,
PRIMARY KEY (SR_NO),
KEY SCH_Code (SCH_CODE),
KEY OSch_Code (OSch_Code)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
注意:有这个索引的原因是我们在 SP 中有许多选择和更新,这将减少表扫描。
UPDATE TempPortFolio1
INNER JOIN Clients
ON Clients.ClientId = TempPortFolio1.Client_Code
SET IType = InvCode;
UPDATE TempPortFolio1
INNER JOIN SchDate ON TempPortFolio1.Sch_Code = SchDate.Sch_Code
SET LatestNav = NavRs, NavDate = LDate;
UPDATE TempPortFolio1
SET RATE = 0
WHERE TRAN_TYPE = 'BONUS';
UPDATE TempPortFolio1
SET LatestNav = 10
WHERE LatestNav = 0 OR LatestNav IS NULL;
UPDATE TempPortFolio1
SET NavDate = Tr_date
WHERE NavDate < Tr_date AND Tran_Type <> 'Reinvestment';
UPDATE TempPortFolio1
SET Age = DATEDIFF(NAVDATE, TR_DATE),
CurrAmt = (LatestNav * Units),
PL_Notional = (UNITS * (LatestNav - Rate)),
Divd_Recd = 0;
UPDATE TempPortFolio1 TP INNER JOIN snature_new SM ON SM.CLASSCODE = TP.Type2
SET GAIN_LOSS_ST = (CASE WHEN (Age < 365) THEN PL_Notional ELSE NULL END),
GAIN_LOSS_LT = (CASE WHEN (Age >= 365) THEN PL_Notional ELSE NULL END)
WHERE SM.Indexation = 0;
UPDATE TempPortFolio1 TP INNER JOIN snature_new SM ON SM.CLASSCODE = TP.Type2
SET GAIN_LOSS_ST =
(CASE
WHEN (TIMESTAMPDIFF(MONTH, TR_DATE, NAVDATE) < 36)
THEN
PL_Notional
ELSE
NULL
END),
GAIN_LOSS_LT =
(CASE
WHEN (TIMESTAMPDIFF(MONTH, TR_DATE, NAVDATE) >= 36)
THEN
PL_Notional
ELSE
NULL
END)
WHERE SM.Indexation = 1;
UPDATE TempPortFolio1
SET RET_INDEX_ABS = ((LatestIndexVal - IndexVal) / IndexVal) * 100;
UPDATE TempPortFolio1
SET Ret_Index_CAGR =
CASE
WHEN Age <= 365
THEN
((CONVERT(RET_INDEX_ABS, decimal) / age) * 365)
ELSE
( POWER((((LatestIndexVal)) / (IndexVal)),
(365 / CONVERT(IFNULL(AGE, 1), decimal)))
- 1)
* 100
END
WHERE age <> 0
AND LatestIndexVal <> 0
AND IndexVal <> 0
AND AGE IS NOT NULL;
UPDATE TempPortFolio1
SET ret_abs =
( ((((UNITS * LATESTNAV) + DIVD_RECD)) - (UNITS * RATE))
/ (UNITS * RATE))
* 100
WHERE UNITS <> 0 AND rate <> 0;
UPDATE TempPortFolio1
SET RET_CAGR =
CASE
WHEN Age <= 365
THEN
((ret_abs / age) * 365)
ELSE
( POWER(
((((UNITS * LATESTNAV) + DIVD_RECD)) / (UNITS * RATE)),
(365 / CONVERT(IFNULL(AGE, 1), DECIMAL)))
- 1)
* 100
END
WHERE age <> 0 AND UNITS <> 0 AND rate <> 0 AND AGE IS NOT NULL;
UPDATE TempPortFolio1
SET Age = 0, LatestNav = 10
WHERE Age IS NULL;
UPDATE TempPortFolio1
SET Factor = (UNITS * RATE * AGE);
UPDATE TempPortFolio1
SET SFactor = (UNITS * RATE * IndexVal * AGE);
这些之间有很多更新,但需要的时间更少。 原因只有两个索引,因为以上所有查询都更新了整个表(4000 万条记录)。所以我认为不需要索引。
每次更新大约需要 25 分钟。服务器有足够的内存来进行所有操作。我试过临时表,但性能没有提高,因为整个表更新了,没有分区逻辑会帮助我这么想。?
我在 Windows 10 上运行此查询。有什么方法可以提高 UPDATE 查询的速度吗?任何与配置相关的更改都会有帮助吗?
请帮忙
- 编辑
这里是对多个连接表查询的解释 这里是更新 2 的解释计划
1 SIMPLE SchDate index PRIMARY,Sch_Code,IDX_1 Sch_Code 4 39064 100 Using index
1 SIMPLE TempPortFolio1 ref SCH_Code SCH_Code 9 SchDate.Sch_Code 1 100 Using index condition.
对于其他更新很简单,一张桌子,所以我认为不需要解释。
解决方案
使用 走过桌子PRIMARY KEY
。一次检查 1000 行。细节在这里讨论
UPDATE
必须保存旧行以防崩溃。这就是你UPDATE
的速度如此之慢的原因之一。而且,由于日志的大小,更新超过一定数量的行会变得更慢,因为它需要付出额外的努力来保存它们。
不要使用OFFSET
and LIMIT
-- 那只会越来越慢。
有些人UPDATEs
可能对索引没问题:
UPDATE TempPortFolio1
SET RATE = 0
WHERE TRAN_TYPE = 'BONUS';
可以使用INDEX(TRAN_TYPE)
.
但是那些没有WHERE
子句的人必须检查所有 40M 行。即使该表可能适合 buffer_pool,但仍需要很长时间。
这张桌子可能比它需要的更胖。
decimal(19,4)
占用 9 个字节,允许值最大为 999999999999999.9999;你真的有那么大的价值观吗?AGE int(4)
- 除非您以人类的秒数为单位谈论“年龄”,否则我建议您使用 1-byteTINYINT UNSIGNED
而不是 4-byteINT SIGNED
。(同时,这(4)
意味着什么。)哦,我知道这AGE
可能是“天”,所以也许 2 字节SMALLINT UNSIGNED
(范围 0..64K)可能是合适的。DOUBLE
占用 8 个字节,并且由于在二进制和十进制之间切换而存在额外舍入的风险。
通常进行大规模更新是架构设计不佳的标志,因为这意味着“价值”不是保存在一个地方,而是数百万个地方。冗余是数据库中的禁忌。
回慢UPDATE
。有什么顾虑:
- 只是查询需要很长时间?(分块将花费更长的时间。)
- 它正在阻止其他事情吗?(分块避免这种情况。)
- 你现在需要改变吗?(太糟糕了。)
- 您是否需要将所有相关行更改为相同的“即时”,即使该即时距您开始查询还有几分钟?(这就是你用单一的,慢的,
UPDATE
.)得到的。)
推荐阅读
- docker - 无法连接到 Docker 容器中的 Go Server
- logging - 如何将 AWS EC2 cloud-init 日志流式传输到 ELK?
- sql - 查询以组合两列如果它们不同,否则只返回一列的结果
- java - 如何在不透明的 JPanel 下获得颜色?
- flutter - Flutter:inAppWebview 不加载 initialUrl
- installation - 根据应用程序在 NSIS 中的语言安装具有相同名称的正确文件
- augmented-reality - 实体模型轮廓/边框颜色与材质不同?
- html - 我创建了内容大小和背景大小之间的关联。为什么?
- elasticsearch - 如何计算嵌套字段中的字段数?- 弹性搜索
- python - “ValueError:数据基数不明确”无法复制基本分类器的张量流教程。使用 Python