UPDATE
reuters_rkd_estimate e
SET
e.fperiod_seqno =
(
SELECT
periodseries_seqno
FROM
fdp.reuters_rkd_pm
WHERE
xref = e.xref
AND periodtype = e.period_type
AND fyearend = e.fyear_end
AND fperiodend = e.fperiod_end
AND periodseries_startdate <= e.origdate
AND (
periodseries_enddate IS NULL
OR periodseries_enddate >= e.origdate ) LIMIT 1)
WHERE
e.period_type = 'A'
AND e.fperiod_seqno IS NULL
AND e.xref='100005165';fdp.reuters_rkd_pm表有900W记录左右。
fdp.reuters_rkd_estimate 表有340W记录左右。需要update的总记录数量为209条。里面的子查询sql语句的where条件字段都是主键索引字段。外面的update表fdp.reuters_rkd_estimate的where条件字段也是索引字段(组合索引)。要是select语句,我还可以explain下,碰到update语句了,我真的不知道怎么处理了,本来想建临时表,将数据放在临时表来处理的,但是leader说那样太麻烦了,不让,我现在该如何优化处理。
reuters_rkd_estimate e
SET
e.fperiod_seqno =
(
SELECT
periodseries_seqno
FROM
fdp.reuters_rkd_pm
WHERE
xref = e.xref
AND periodtype = e.period_type
AND fyearend = e.fyear_end
AND fperiodend = e.fperiod_end
AND periodseries_startdate <= e.origdate
AND (
periodseries_enddate IS NULL
OR periodseries_enddate >= e.origdate ) LIMIT 1)
WHERE
e.period_type = 'A'
AND e.fperiod_seqno IS NULL
AND e.xref='100005165';fdp.reuters_rkd_pm表有900W记录左右。
fdp.reuters_rkd_estimate 表有340W记录左右。需要update的总记录数量为209条。里面的子查询sql语句的where条件字段都是主键索引字段。外面的update表fdp.reuters_rkd_estimate的where条件字段也是索引字段(组合索引)。要是select语句,我还可以explain下,碰到update语句了,我真的不知道怎么处理了,本来想建临时表,将数据放在临时表来处理的,但是leader说那样太麻烦了,不让,我现在该如何优化处理。
是否可以用min(periodseries_seqno)或者max(periodseries_seqno)来代替可以的话,直接关联update即可,不需要用子查询了
reuters_rkd_estimate的表结构:delimiter $$CREATE TABLE `reuters_rkd_estimate` (
`xref` int(10) unsigned NOT NULL,
`fperiod_seqno` tinyint(4) DEFAULT NULL,
`fyear_end` int(6) unsigned NOT NULL,
`fperiod_end` int(6) unsigned NOT NULL,
`period_type` char(1) NOT NULL,
`period_num` tinyint(4) NOT NULL,
`period_enddate` datetime NOT NULL,
`fyestimate_type` varchar(20) NOT NULL,
`fyestimate_unit` varchar(2) NOT NULL,
`startdate` datetime NOT NULL,
`origdate` datetime NOT NULL,
`expiredate` datetime DEFAULT NULL,
`high` decimal(18,4) NOT NULL,
`low` decimal(18,4) NOT NULL,
`mean` decimal(18,4) NOT NULL,
`stddev` decimal(18,4) DEFAULT NULL,
`median` decimal(18,4) NOT NULL,
`numofests` tinyint(4) DEFAULT NULL,
`currency_code` char(3) DEFAULT NULL,
`conestimate_unit` varchar(2) DEFAULT NULL,
`upd_stmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`xref`,`fyear_end`,`fperiod_end`,`period_type`,`period_num`,`fyestimate_type`,`origdate`),
KEY `ix_reuters_rkd_estimate_seqno` (`fperiod_seqno`),
KEY `ix_reuters_rkd_estimate_xref` (`xref`),
KEY `ix_reuters_rkd_estimate_expiredate` (`expiredate`),
KEY `ix_reuters_rkd_estimate_type` (`fyestimate_type`),
KEY `ix_reuters_rkd_estimate_period_type` (`period_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='路透预测数据,只有需要的历史数据\n且添加了预测周期'$$-- reuters_rkd_pm的表结构:
delimiter $$CREATE TABLE `reuters_rkd_pm` (
`xref` int(10) unsigned NOT NULL COMMENT '/periodsFile/coIds/coId[@type="XRef"]\n公司编号',
`prod_dt` datetime NOT NULL COMMENT '/periodsFile/production/@date\nrkd系统数据产生日期,GMT',
`event_cd` varchar(20) NOT NULL COMMENT '/periodsFile/periods/event/@code\n记录刷新方式,记录最后一次操作本条记录时的刷新方式。\nHistorical-Delete:删除指定数据\n其他状态为按照主键替换或插入',
`event_asof` datetime NOT NULL COMMENT '/periodsFile/periods/event/@asOf\n数据更新时间',
`periodseries_startdate` datetime NOT NULL,
`periodseries_enddate` datetime DEFAULT NULL,
`periodseries_seqno` tinyint(4) NOT NULL,
`fyearend` char(6) NOT NULL,
`fperiodend` char(6) NOT NULL,
`periodtype` char(1) NOT NULL,
`periodnum` tinyint(3) unsigned NOT NULL DEFAULT '0',
`period_length` tinyint(3) unsigned NOT NULL,
`period_length_unit` char(1) NOT NULL,
`period_advance_date` datetime DEFAULT NULL,
`period_expect_date` datetime DEFAULT NULL,
`period_data_status` char(1) DEFAULT NULL,
`period_et_phase` tinyint(3) unsigned DEFAULT NULL,
`processed` tinyint(1) NOT NULL DEFAULT '0',
`upd_stmp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '数据插入/更新时间',
PRIMARY KEY (`periodseries_startdate`,`periodseries_seqno`,`fyearend`,`fperiodend`,`periodtype`,`periodnum`,`xref`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Peroid Model\n路透预测周期定义\n更新周期:每日刷新'$$
900多W数据啊,你要是group 不得要命啊?
那你limit 1的话,能保证就是要update的值吗?
我的意思 group by 不是比limit更消耗资源吗? 需要的时间更长啊?
create index xxxx2 on `reuters_rkd_pm`(xref,periodtype ,fperiodend ,periodseries_startdate,periodseries_enddate );