mysql - 从一个巨大的表中获取随机值集

Question

我有一个包含大约 10 亿条记录的表。它具有以下结构:

id | name | first_id | second_id

我还有一个包含一组特定单词的数组:

$arr = ['camel', 'toe', 'glasses', 'book'];

我现在必须从该表中获取所有记录，其中:

- name contains one or more keywords from this array
- first_id matches 8
- second_id matches 55

这些值当然是组成的，它们在我的应用程序中动态变化。

我怎样才能做到最有效？

我尝试了以下方法:

SELECT *
FROM table t
WHERE (t.name LIKE '%camel%' OR t.name LIKE '%toe%' OR t.name LIKE '%glasses%' OR t.name LIKE '%book%') AND t.first_id = 8 AND t.second_id = 55;

但它执行大约 3.5 秒。

我只需要从此查询中获取大约 3-4 条随机记录，因此我也尝试将结果限制为 300。但它仍然给了我 700 毫秒，这太长了。

我也尝试过随机化 limit 和 offset，但我必须更早地计算所有结果，所以它会更慢。

有没有办法解决这个问题？

Answer 1

首先，了解如何使用 EXPLAIN SELECT。这应该告诉您一些有关 mysql 将如何为您的查询选择策略的信息。

如果只是使用first_id和 second_id将表减少到少量记录，应该很快，但这确实意味着您需要一个索引。只能使用 1 个索引，因此如何构建该索引取决于 first_id 的基数和 second_id .如果两者只包含有限的值(比如:不到一百)，您应该创建一个引用两者的索引。

但是如果表中仍然有大量记录，即使是那些 first_id和 second_id值，这意味着您需要在 name 上建立索引字段代替。

对于该字段，常规索引不会为您做任何事情。你需要一个FULLTEXT索引。