mysql - 用于优化小表上的重复查询的临时表或 View 的替代方案

Question

我有一个大约有 40,000 行的表。每个都有一个以空格分隔的数字列表，范围从 1 到 100。示例行在一列中具有唯一的标识名称，后跟另一列中的“4 8 18 32”。

我想将名称的自由文本搜索与包含特定数字的行数计数结合起来。因此，举例来说，请统计有多少行与 %word% 匹配，以及 1 到 100 之间的每个数字。这就是一百个查询。如果上面的“4 8 18 32”是表中唯一的匹配项，则计数将为 1: 0, 2: 0, 3: 0, 4: 1, ... 8: 1, ... 32: 1.

目前，我正在尝试使用最小标记长度为 1 的 InnoDB FULLTEXT 索引来执行此操作，然后以二进制模式进行搜索。我的查询如下所示:

(SELECT COUNT(*) FROM `table` WHERE MATCH (`numbers`) AGAINST ('+34' IN BOOLEAN MODE) AND `name` LIKE '%word%') as `34`,
(SELECT COUNT(*) FROM `table` WHERE MATCH (`numbers`) AGAINST ('+35' IN BOOLEAN MODE) AND `name` LIKE '%word%') as `35`,

并连接成一个结果。这大约需要两三秒，这是一个很长的时间。

我尝试使用临时表进行 LIKE 匹配，然后仅使用 bool 模式搜索数字，但我无法在查询中多次重复使用临时表引用，并且这里重复使用了一百次。我还尝试创建 LIKE 部分的 View ，但没有看到太多加速。告诉 View 使用算法=temptable 是不可能的，因为全文索引无法传输。

我是否以根本错误的方式使用数据库？有没有一种策略可以显着加快速度？

Answer 1

我不会对表进行 100 个单独的查询，而是只尝试一个查询:

SELECT 
       -- using CASE expression --
       SUM( case when `numbers` like '%+34%' and `name` LIKE '%word%'
                 then 1 else 0 end ) As `34`,
       -- or using IF ---
       SUM( IF( `numbers` like '%+35%' and `name` LIKE '%word%', 1, 0 )) As `35`
       .......
FROM table;

此查询对表执行全表扫描，但仅扫描一次，而不是一系列 100 次扫描。
可能会更快 - 尝试一下。
全表扫描不是罪恶，索引并不总是好的。