mysql - 比较字符串与大量记录的相似度

Question

数据为相机和镜头产品名称。

我的产品表中有 55,000 条记录，我想将它们与干净的 3500 条主记录集进行比较，以便我知道它们是什么以提供附加信息。

产品表每天都会更新，当它丢失和增加数千条记录时，性能很重要。

这是我正在使用的数据示例，这 5 条记录

Canon 45MM 2.8 TSE    
Canon 45mm F2.8 TS-E
Canon 45mm F/2.8L Tilt-Shift - Boxed
Canon EF TS-E 45mm f/2.8 Tilt-shift Black Lens
Canon TS-e 45mm f2.8 Lens - Unboxed

应全部与主记录匹配

Canon TS-E 45mm f/2.8

我尝试使用全文搜索来比较字符串，速度非常快，但结果很差。

接下来我尝试了这个 Levenshtein 距离函数 https://lucidar.me/en/web-dev/levenshtein-distance-in-mysql/

每次比较(1条记录与3500条主记录)可能需要30-60秒，结果更好。一些例子。

佳能 85mm 1.2 MK II L - 7 号

    M_PRODUCTNAME   SCORE
1   Canon EOS 5D Mark II    14
2   Canon EOS 6D Mark II    14
3   Canon EOS-1D Mark II N  14
4   Canon EF 85mm F1.2  14
5   Canon EF 50mm F1.8 II   14
6   Canon EOS 7D Mark II    14
7   Canon EF 85mm F1.2L II USM  14
8   Canon EOS 5D Mark III   14
9   Canon EOS-1D Mark II    14
10  Canon EOS M6 Mark II    14

佳能 EF 80-200mm f4-5.6 II 镜头 - 数字 1(记录中的实际错误应该是 f4.5 而不是 f4!)

    M_PRODUCTNAME   SCORE
1   Canon EF 80-200mm f/4.5-5.6 II  12
2   Canon EF 70-300mm f/4-5.6L IS USM   13
3   Canon EF 70-300mm f/4-5.6 IS USM    13
4   Canon EF 70-200mm F4L IS II USM 14
5   Canon EF 55-200mm f/4.5-5.6 II USM  14
6   Canon EF 70-300 F4-5.6 IS II USM    15
7   Canon EF 70-200mm f/2.8L USM    15
8   Canon EF 70-200mm F4L IS USM    15
9   Canon EF 70-200mm f/2.8L IS USM 15
10  Canon EF 70-200mm F4L USM   15

佳能 fit zenitar c 镜头16 mm f2.8 - 不匹配

    M_PRODUCTNAME   SCORE
1   7artisans 12mm F2.8 22
2   Canon TS-E 45mm f/2.8   22
3   Canon TS-E 90mm f/2.8   22
4   7artisans 25mm F1.8 23
5   Canon TS-E 17mm f/4L    23
6   Canon EF 28mm f/2.8 23
7   Canon Extender EF 1.4x III  23
8   Canon Extender EF 1.4x II   23
9   Canon EF 24mm f/2.8 23
10  Canon EF 35mm F2.0  23

佳能 EOS IX APS 胶片自动对焦和手动单反 EF/EFS 安装相机机身 - 已测试 - 不匹配

    M_PRODUCTNAME   SCORE
1   Minolta Maxxum 7 35mm SLR Camera (Body Only)    60
2   Canon EOS 400D (EOS Digital Rebel XTi / EOS Kiss Digital X) 61
3   Canon EOS 300D (EOS Digital Rebel / EOS Kiss Digital)   61
4   Canon EOS 350D (EOS Digital Rebel XT / EOS Kiss Digital N)  61
5   Holga 120FN Medium Format Plastic Camera with Flash 62
6   Canon EOS 1100D (EOS Rebel T3 / EOS Kiss X50)   62
7   Canon EOS 1200D (EOS Rebel T5 / EOS Kiss X70)   62
8   Canon EF-S 35mm F2.8 Macro IS STM   62
9   Canon EF-M 28mm F3.5 Macro IS STM   62
10  Canon EF-S 60mm f/2.8 Macro USM 62

我相信我也许能够调整角色删除/更改/添加的评分，但即使如此，运行时间仍然太长。

例如耗时 35 秒的查询。

SELECT m_productname, levenshtein(m_productname, 'Tamron SP 45mm f/1.8 Di VC USD, Canon EF Fit') AS score FROM m_product ORDER by score

此外，我仍然不确定当低分匹配不正确时如何处理数据，在某些时候可能需要一些手动输入 - 但是也许这又是一个令人头疼的问题。

要么我需要 Levenshtein 来给我更好的得分并跑得更快，要么我需要另一种方法，有什么想法吗？

我需要能够在第一个实例中运行查询 55,000 次，然后每天为新条目运行大约 3000 次。所以每次查询 30 秒是不行的。

如果有其他选择，我正在使用 ColdFusion。

Answer 1

您的数据似乎包含大量信息，可以分为更简洁的列。在我更多地研究 Levenshtein 方法之前，我会花时间编写一些转换脚本，这些脚本会将这些数据解析为同一个表中的其他列。换句话说，不要有这样的表:

M_PRODUCTNAME
Canon EF 70-200mm f/2.8L USM

我有一个像这样的表:

M_PRODUCTNAME                   BRAND   APERTURE  FSTOP
Canon EF 70-200mm f/2.8L USM    Canon   70-200    2.8L

如何分解这些数据？就我个人而言，我会获取每个完整的产品名称，并用空格将其分割成一个数组。然后我会查看数组中的每个项目以应用转换逻辑。该商品是否包含您的任何知名品牌？然后将该品牌添加到 BRAND 列中。字符串是否以“mm”结尾？然后我将该项目添加到 APERTURE 列中。该项目是以“f/”还是“F/”开头？然后我会将该项目添加到 FSTOP 列。

此方法无法捕获 M_PRODUCTNAME 数据中的所有异常情况。但您可能可以对其进行调整以提取大量有意义的元数据。一旦数据被提取到其他列中，搜索这些元素就会变得更快更容易。

此外，将该数据提取到多列中的算法可能与用于从目标字符串中查找要搜索的元素的算法相同。