用短语匹配短语和句子

Question

我的情况是这样的： 我有一个表（phrases），有大约500000个条目。每个条目都是一个短语，如：

Beating Around the Bush
Burst Your Bubble
Cry Wolf
Curiosity Killed The Cat 

我还有另一个表（full_sen），有4000个条目，每个条目都是一个句子，里面有一个短语。你知道吗

我想匹配full_sen表，找出哪些句子中有短语。你知道吗

我需要这个经常运行，因为这两个表将有新的项目定期，我需要一些可以快速运行。你知道吗

最简单的查询是遍历500k表并进行LIKE %phrase%搜索 对full_sen表，但这需要永远。。。你知道吗

如果有帮助的话，我不介意合并PHP或Python；它不一定只是一个查询。你知道吗

full_sen    CREATE TABLE `full_sen` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `sku` varchar(21) COLLATE utf8_unicode_ci DEFAULT NULL,
 `user_id` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `full_sen_sku_670bbe26026365f4_uniq` (`sku`),
 KEY `full_sen_e8701ad4` (`user_id`),
 CONSTRAINT `full_sen_user_id_295adcd84efdd880_fk_auth_user_id` FOREIGN KEY (`user_id`) REFERENCES `auth_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5232 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

phrases CREATE TABLE `phrases` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `date` date NOT NULL,
 `serial_number` varchar(20) NOT NULL,
 `title` varchar(250) NOT NULL,
 `status_number` smallint(5) unsigned,
 PRIMARY KEY (`id`),
 UNIQUE KEY `serial_number` (`serial_number`),
 KEY `phrases_status_number_5f42a821_uniq` (`status_number`),
 FULLTEXT KEY `title` (`title`)
) ENGINE=InnoDB AUTO_INCREMENT=632144 DEFAULT CHARSET=latin

名字和头衔是我要说的领域。你知道吗

谢谢。你知道吗

Answer 1

可以做两件事来提高性能：预计算和使用索引。它们中的每一个都将极大地提高性能，您甚至可以将它们结合起来。让我们看看：

正如您自己已经发现的，最简单的解决方案是在phrases表中循环，并对每一行在full_sen表中做一个LIKE %phrase%（旁注：您编写了LIKE %sentence%，但我认为这是一个输入错误-也许我稍后会编辑您的文章）。你知道吗

但实际上，没有必要这么做。你知道吗

创建名为phrase_sentence_pairs的第三个表，其中包含列ID（这只是行ID/主键）、列phrase_id和列sentence_id。phrase_id和sentence_id必须被索引（非唯一）。当然，在开始时，您必须根据下面解释的算法，用您已有的所有数据填充该表。这可能需要很长时间，但这应该无关紧要，因为它必须只做一次。你知道吗

现在，当一个新短语被添加到phrases表中时，您只需要扫描整个full_sen表中的一个短语。每次短语包含在句子中时，都会向phrase_sentence_pairs表中添加新行，以便phrase_id存储phrases表中新短语的ID，sentence_id存储full_sen表中当前匹配行的ID。你知道吗

同样地，当一个新句子被添加到full_sen表中时，您将遍历phrases表，并针对每一行检查短语是否包含在新句子中。如果是，则向phrase_sentence_pairs表中添加新行，以便sentence_id存储full_sen表中新句子的ID，并且phrase_id存储phrases表中当前匹配行的ID。你知道吗

在性能方面，这会导致以下情况：

您将不再直接从phrases和full_sen表中SELECT。相反，例如，当您要查找给定短语的所有句子时，首先从phrases表中获取该短语的ID，然后从phrase_sentence_pairs表中选择phrase_id匹配的所有记录。你知道吗

相反的动作（把所有短语都带到某个句子中）的工作方式是相同的。你知道吗

与以前相比，从phrase_sentence_pairs表中进行选择的速度会非常快。这主要是因为数据库现在比较的是数字而不是部分匹配的字符串，而且（更重要的是）这个SELECT可以使用索引来完成。你知道吗

另一方面，插入会比较慢。但总体表现仍将大幅提升：

让我们假设SELECT从phrase_sentence_pairs到SELECT所花费的时间可以忽略不计（在这种情况下，这个假设是可以的），因此我们将不进一步考虑它。对于以前的版本，每次选择时都必须对500K * 4K记录进行LIKE字符串比较。你知道吗

相反，在插入新短语时，您现在必须对4K记录进行LIKE字符串比较（这意味着500K（！）对于必须扫描的行），以及插入新句子时的500K记录（对于必须扫描的行，这意味着4K因子）。你知道吗

即使我们考虑到我们现在在插入短语或句子时必须写入两个表而不是一个表，并且写入索引列当然比写入非索引列慢，我认为这是一个非常好的交易。你知道吗

如果插入速度仍然不够快，还有另一个技巧可以大大提高扫描速度。但在我详细说明之前，请先尝试我上面的建议，并报告性能提高（最好在问题末尾添加）。你知道吗

最后，请注意，UPDATE到phrases或full_sen表的UPDATE必须导致与INSERT运行几乎相同的代码，并且必须在DELETE到phrases表的DELETE之后运行适当的代码或full_sen表。你知道吗