Mysql在两个参数上使用左连接，在非常大的表上使用一个精确匹配和一个LIKE匹配-性能

Question

我有两个历史数据表-一个(OldData)是来自数据源的40,000条记录，其中包含我正在尝试清理的部分/不准确数据，另一个(LookupData)是包含100多万条准确记录的明确来源。

我正在尝试用较大的表中的记录来丰富第一个较小的表，我可以通过连接surname和一个称为服务编号的数值来预测匹配的记录，但在第一个表中，这些数字通常是不完整的。

OldData (部分/不准确数据)示例：

Surname | ServiceNumber
Smith | 12345
Jones | 9876
Brown | 234

LookupData示例：

Surname | ServiceNumber
SMITH | 12345
SMITH | 23456
JONES | 98765 
JONES | 19182
BROWN | T12345 
BROWN | 56789

期望的结果：

OldData.Surname | OldData.ServiceNumber | LookupData.ServiceNumber
Smith | 12345 | 12345
Jones | 9876  | 98765
Brown | 234   | T12345

我当前的查询是

SELECT OldData.*,LookupData.ServiceNumber
FROM `OldData` 
LEFT JOIN `LookupData` 
ON lower(OldData.Surname) = lower(LookupData.Surname) 
AND LookupData.ServiceNumber like concat('%',OldData.ServiceNumber,'%') 

但这似乎永远不会完成

如果我将测试范围缩小到一个姓氏，并添加

WHERE OldData.Surname='Devlin'

我从OldData获得了47行，并得到了找到任何匹配项的准确LookupData.ServiceNumber (如果没有找到，则返回null )，但是这个查询平均仍然需要27秒。

我在Surname字段和ServiceNumber字段上都有索引。

如果我在寻找不可能的事情，我至少想知道:)谢谢

Answer 1

让我们看一下查询的两个JOIN条件。

lower(OldData.Surname) = lower(LookupData.Surname) 

在等式的两端使用函数会减慢搜索速度。除非使用the BINARY operator，否则默认情况下，MySQL字符串搜索通常不区分大小写。此条件可以重写为

OldData.Surname = LookupData.Surname

第二个JOIN条件是：

LookupData.ServiceNumber like concat('%',OldData.ServiceNumber,'%') 

LIKE不利于性能，尤其是在开头有%时:因为MySQL索引通常是有序的，所以这会导致触发完整扫描，因为无法找到优化的搜索起点。在您的示例数据中，看起来您可以删除起始%。

使用INSTR可能不会提高性能。

您可以尝试使用regexp，例如：

LookupData.ServiceNumber REGEXP OldData.ServiceNumber

如果您确实需要在大数据上进行两端搜索，那么使用MySQL的方法是Full-Text Search Functions。这将需要在服务编号列上创建FULLTEXT索引(并可能将它们从数字转换为文本)，然后：

MATCH LookupData.ServiceNumber AGAINST OldData.ServiceNumber