使用LIKE子句加速MySQL内连接
使用LIKE子句加速MySQL内连接
提问于 2018-06-15 06:09:02
我有以下两个表，api_analytics_data和telecordia。
CREATE TABLE `api_analytics_data` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`upload_file_id` bigint(20) NOT NULL,
`partNumber` varchar(100) DEFAULT NULL,
`clei` varchar(45) DEFAULT NULL,
`description` varchar(150) DEFAULT NULL,
`processed` tinyint(1) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_aad_clei` (`clei`),
KEY `idx_aad_pn` (`partNumber`),
KEY `id_aad_processed` (`processed`),
KEY `idx_combo1` (`partNumber`,`clei`,`upload_file_id`)
) ENGINE=InnoDB CHARSET=latin1;
CREATE TABLE `telecordia` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`ProdID` varchar(50) DEFAULT NULL,
`Mfg` varchar(20) DEFAULT NULL,
`Pn` varchar(50) DEFAULT NULL,
`Clei` varchar(50) DEFAULT NULL,
`Series` varchar(50) DEFAULT NULL,
`Dsc` varchar(50) DEFAULT NULL,
`Eci` varchar(50) DEFAULT NULL,
`AddDate` date DEFAULT NULL,
`ChangeDate` date DEFAULT NULL,
`Cost` float DEFAULT NULL,
PRIMARY KEY (`tid`),
KEY `telecordia.ProdID` (`ProdID`) USING BTREE,
KEY `telecordia.clei` (`Clei`),
KEY `telecordia.pn` (`Pn`),
KEY `telcordia.eci` (`Eci`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;用户使用Excel/CSV文件通过web界面将数据上传到api_analytics_data。数据包含partNumbers或CLEI。然后，我通过加入远程视频表来更新api_analytics_data表。远程视频表是partNumber和Cleis的主列表。
因此，如果用户上传了一个CLEIs文件，我使用的update/join是：
update api_analytics_data aad
inner join telecordia t on aad.clei = t.Clei
set aad.partNumber = t.Pn
where aad.partNumber is null
and aad.upload_file_id = 5;它工作得很快，但不是很彻底。我的问题是，上传的CLEI可能只是远程视频表中CLEI的一个子串。
例如，上传的CLEI可以是"5SC1DX0“。在telcordia表中，正确匹配的行为：
tid: 184324
ProdID: 472467
Mfg: PLSE
Pn: AUA58-2-REV-E
Clei: 5SC1DX04AA
Series: null
Dsc: DL SGL-PTY POTS CU RT
Eci: 205756
AddDate: 1994-03-18
ChangeDate: 1998-04-13
Cost: null所以很明显，我的更新在这种情况下不起作用，即使5SC1DX0和5SC1DX04AA是同一部分。
我需要的是通配符搜索。然而，当我尝试这个的时候，它是非常慢的。在将大约4500行上载到api_analytics_data表中之后，它将运行大约10分钟，然后断开与服务器的连接。
update api_analytics_data aad
inner join telecordia t on aad.clei like concat(t.Clei,'%')
set aad.partNumber = t.Pn
where aad.partNumber is null
and aad.upload_file_id = 5;有没有办法优化它，让它运行得更快？
回答 1
Stack Overflow用户
发布于 2018-06-15 06:18:39
正确的答案是“不”。更好的做法是在telecordia中创建一个新列，其中包含正确的Clei值，该列可用于连接表。在最新版本的MySQL中，这甚至可以是一个计算列并被索引。
也就是说，如果匹配部分的长度始终相同，则可以执行某些操作。如果是这样的话，试试这个：
update api_analytics_data aad inner join
telecordia t
on t.Clei = left(aad.clei, 7)
set aad.partNumber = t.Pn
where aad.partNumber is null and aad.upload_file_id = 5;对于这个查询，您需要一个关于api_analytics_data(upload_fiel_id, partNumber, clei)和telecordia(clei, pn)的索引。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50866618
复制相关文章
相似问题