mySQL 针对 NxM 字符串搜索的优化

Question

抱歉，这个主题很笼统，但我想不出更好的了:(

假设我有一些固定字符串(名为 s1、s2、... sn，不超过 5 或 6，在示例中为 4 个)，我必须使用一些 VARCHAR(50 ) 字段(称为 t1、t2、t3...不超过 5 个，但示例中为 4 个)。简单的查询看起来像这样:

SELECT id FROM tbl WHERE (s1 = t1) or (s1 = t2) or (s1 = t3) or (s1 = t4) OR
                         (s2 = t1) or (s2 = t2) or (s2 = t3) or (s2 = t4) OR
                         (s3 = t1) or (s3 = t2) or (s3 = t3) or (s3 = t4) OR
                         (s4 = t1) or (s4 = t2) or (s4 = t3) or (s4 = t4) OR 
                         (s5 = t1) or (s5 = t2) or (s5 = t3) or (s5 = t4)  

起初，我想在创建 t1,t2... 时生成一个大字符串，将所有 t1+t2+t3+t4 连接起来……因为创建它们时它不会“花费时间”，然后在 mySQL 中使用某种“substr”来查找字符串。类似的东西(同样，我不知道 mySQL 语法)

SELECT id FROM tbl WHERE (s1 in conc) or (s2 in conc) or (s3 in conc) or (s4 in conc) or (s5 in conc)

真正的问题在于表中元素的数量。“tbl”有大约 50k 个元素，我必须使用不同的 s1/s2/s3/s4/s5 运行这个查询大约 10k 次(我也从数据库中获取它们，但我将它们全部读取到内存中以加快速度) .

内存大小不应成为问题:每行大约 250 字节长，因此 RAM 中的表总共可能约为 12Mb。将其加载到数组中并直接在 RAM 中进行搜索是否有意义？此时，有没有一种方法可以强制 mySQL 服务器始终将一张表保留在 RAM 中，并且知道它不能增长太多，而且与它节省的时间相比，12Mb 的内存量非常小？ (我正在从 PHP 运行查询)。

当然，我不在乎查询是否看起来愚蠢、丑陋或不优雅。我正在寻找尽可能快的东西，只是因为我必须按照所说的那样运行该查询大约 500k 次。也欢迎任何有关索引等的建议:)

PS:现在我正在考虑在凌晨 4 点的 CRON 作业中每 24 小时执行一次，但是如果能够按需执行并且无需停止服务器 10 分钟......(一毫秒)，那就太好了每个都是 500 秒，因此 100% CPU 时间几乎有 9 分钟...)

Answer 1

如果你想要性能，那么你需要使用索引。不幸的是，or往往会妨碍索引的使用。

一种解决方案如下。独立地在每一列上创建一个索引，这样您就可以为每个“t”字段创建一个索引。然后构建常量字符串，以便您可以使用 in 。将查询表述为:

SELECT id FROM tbl WHERE t1 in (s1, s2, s3,  . . .) 
UNION
SELECT id FROM tbl WHERE t2 in (s1, s2, s3,  . . .) 
UNION
SELECT id FROM tbl WHERE t3 in (s1, s2, s3,  . . .) 
UNION
SELECT id FROM tbl WHERE t4 in (s1, s2, s3,  . . .) 
UNION
SELECT id FROM tbl WHERE t5 in (s1, s2, s3,  . . .) 

MySQL 应该为每个子查询使用一个索引。 union删除重复项会产生开销。想必，通过不同领域的比较，这是可能的。

另一种方法是使用 MATCH() . . . AGAINST带有全文索引。您可以阅读有关这些的更多信息 here .