mysql - 数据导入过程中永不结束的MySQL查询

Question

我正在研究从一组 CSV 文件到我的主数据库的数据导入例程，但我一直在处理这组特定的数据。我已使用 LOAD DATA LOCAL INFILE 将 CSV 数据转储到我的表 feed_hcp_leasenote 中:

CREATE TABLE `feed_hcp_leasenote` (
 `BLDGID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `LEASID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `NOTEDATE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `REF1` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `REF2` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `LASTDATE` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `USERID` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `NOTETEXT` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `tempid` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`tempid`),
 KEY `BLDGID` (`BLDGID`),
 KEY `LEASID` (`LEASID`),
 KEY `REF1` (`REF1`),
 KEY `NOTEDATE` (`NOTEDATE`)
) ENGINE=MyISAM AUTO_INCREMENT=65002 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

我正在尝试将此数据导入到两个表中:lease_notes 和 customfield_data。 Lease_notes 仅存储唯一的 ID 值、注释本身以及将其链接到租赁表的盖子。 customfield_data 存储系统和用户创建的字段的各种数据，每个记录通过 linkid 字段链接到另一个表。这是lease_note 表:

CREATE TABLE `lease_notes` (
 `lnid` int(11) NOT NULL AUTO_INCREMENT,
 `notetext` longtext COLLATE utf8_unicode_ci NOT NULL,
 `lid` int(11) NOT NULL COMMENT 'Lease ID',
 PRIMARY KEY (`lnid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

以及customfield_data 表:

CREATE TABLE `customfield_data` (
 `cfdid` int(11) NOT NULL AUTO_INCREMENT,
 `data_int` int(11) DEFAULT NULL,
 `data_date` datetime DEFAULT NULL,
 `data_smtext` varchar(1000) COLLATE utf8_unicode_ci DEFAULT NULL,
 `data_lgtext` longtext COLLATE utf8_unicode_ci,
 `data_numeric` decimal(20,2) DEFAULT NULL,
 `linkid` int(11) DEFAULT NULL COMMENT 'ID value of specific item',
 `cfid` int(11) NOT NULL COMMENT 'Custom field ID',
 PRIMARY KEY (`cfdid`),
 KEY `data_smtext` (`data_smtext`(333)),
 KEY `linkid` (`linkid`),
 KEY `cfid` (`cfid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

卡住的查询如下:

SELECT NOTEDATE, REF1, REF2, LASTDATE, USERID, feed_hcp_leasenote.NOTETEXT, leases.lid, lease_notes.lnid
FROM feed_hcp_leasenote
JOIN customfield_data mrileaseid ON feed_hcp_leasenote.LEASID = mrileaseid.data_smtext AND mrileaseid.cfid = ?
JOIN leases ON mrileaseid.linkid = leases.lid
JOIN suites ON leases.sid = suites.sid
JOIN floors ON suites.fid = floors.fid
JOIN customfield_data coid ON floors.bid = coid.linkid AND coid.cfid = ? AND coid.data_smtext = feed_hcp_leasenote.BLDGID
JOIN customfield_data status ON leases.lid = status.linkid AND status.cfid = ? AND status.data_smtext <> ?
LEFT JOIN lease_notes ON leases.lid = lease_notes.lid
LEFT JOIN customfield_data notedate ON lease_notes.lnid = notedate.linkid AND notedate.data_date = feed_hcp_leasenote.NOTEDATE AND notedate.cfid = ?
LEFT JOIN customfield_data ref1 ON lease_notes.lnid = ref1.linkid AND ref1.data_smtext = feed_hcp_leasenote.REF1 AND ref1.cfid = ?

我的目标是返回 feed_hcp_leasenote 中的所有记录，并根据lease_notes.lnid是否为空，根据需要插入或更新记录(空值将是插入，而不是空值将是更新。)问题是所提供的数据使用 4 个字段的组合来确定唯一性:BLDGID、LEASID、NOTEDATE 和 REF1。如果没有正确的 BLDGID 和 LEASID(在我的查询中转换为有效的盖子，注释将不存在。它可以将现有记录与有效的盖子、NOTEDATE 和 REF1 相匹配，但如果这些记录不存在)匹配，那么我可以假设这是一个新记录。

如果我从 SELECT 中删除所有 LEFT JOIN 和lease_notes.lnid，它会正确执行并为我提供所有记录。由于我无法使原始查询正常工作，因此我想到了循环所有结果并执行另一个 SELECT 来查看所标注的日期和 ref1 是否匹配。如果没有，我插入，否则更新。虽然这种方法有效，但它每秒只能处理大约 20 条记录，当我一次处理 30,000 条记录时，这是一个问题。

由于我在上一个问题中被问到，因此以下是我的查询的解释:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE  status  ref     data_smtext,linkid,cfid     cfid    4   const   934     Using where
1   SIMPLE  mrileaseid  ref     data_smtext,linkid,cfid     linkid  5   rl_hpsi.status.linkid   19  Using where
1   SIMPLE  leases  eq_ref  PRIMARY,sid     PRIMARY     4   rl_hpsi.mrileaseid.linkid   1   Using where
1   SIMPLE  suites  eq_ref  PRIMARY,fid     PRIMARY     4   rl_hpsi.leases.sid  1    
1   SIMPLE  floors  eq_ref  PRIMARY,bid     PRIMARY     4   rl_hpsi.suites.fid  1    
1   SIMPLE  feed_hcp_leasenote  ref     BLDGID,LEASID   LEASID  153     rl_hpsi.mrileaseid.data_smtext  19  Using where
1   SIMPLE  coid    ref     data_smtext,linkid,cfid     data_smtext     1002    rl_hpsi.feed_hcp_leasenote.BLDGID   10  Using where
1   SIMPLE  lease_notes     ALL     NULL    NULL    NULL    NULL    15000    
1   SIMPLE  notedate    ref     linkid,cfid     linkid  5   rl_hpsi.lease_notes.lnid    24   
1   SIMPLE  ref1    ref     data_smtext,linkid,cfid     data_smtext     1002    rl_hpsi.feed_hcp_leasenote.REF1     10   

有人能指出我正确的方向吗？谢谢!

Answer 1

来 self 们的评论:

答案是添加使条目对目标表唯一的列，并在它们上创建复合唯一键。然后，在插入到该表时，使用 INSERT ON DUPLICATE KEY UPDATE 来防止重复数据。插入完成后，如果不再需要这些列，您可以删除它们，以防止将数据存储在多个表中。