在大表中搜索

Question

我有超过一百万行的表格。此表表示用户信息，例如userName、电子邮件、性别、婚姻状况等。

当应用了一些条件时，我将在这个表中的所有行上编写search。

在简单的情况下，当搜索只在userName上执行时，查找结果需要4-7秒。

select from u where u.name ilike " ... "

是的，我得到了一些文件的索引。我使用explain analyse命令检查它们是否已被应用。

如何提高搜索效率？

我对Lucene有一些想法，它能帮上忙吗？

我想知道Facebook搜索是如何工作的，他们有数十亿用户，他们的搜索速度要快得多。

Answer 1

这三个查询之间有很大的区别：

a) SELECT * FROM u WHERE u.name LIKE "George%"

b) SELECT * FROM u WHERE u.name LIKE "%George"

c) SELECT * FROM u WHERE u.name LIKE "%George%"

a)第一个将使用u.name上的索引(如果有)，并且速度非常快。

b)第二个将不能在u.name上使用任何索引，但有一些方法可以相当容易地绕过这一点。

例如，您可以在存储REVERSE(name)的表中添加另一个字段nameReversed。有了该字段上的索引，查询将被重写为(并且将与第一个一样快)：

b2) SELECT * FROM u WHERE u.nameReversed LIKE REVERSE("%George") 

c)第三个查询的难度最大，因为前面的两个索引都没有任何帮助，并且查询将扫描整个表。替代方案包括：

使用专门针对此类问题的解决方案(搜索“全文搜索”)，如Sphinx。有关更多详细信息，请查看此问题：which-is-best-search-technique-to-search-records

如果您的字段只有名称(或另一组有限的单词，说出几百个不同的单词)，则可以使用这些名称(单词)创建另一个辅助表，并在表u中只存储一个外键。

当然，如果情况并非如此，并且您有数万或数百万个不同的单词，或者字段包含完整的短语，那么要解决许多辅助表的问题，就像为自己创建全文搜索工具一样。这是一个很好的练习，除了RDBMS之外，您不需要使用Sphinx (或其他)，但它不是微不足道的。

Answer 2

看一下Lucene Hibernate Search，它使用的是Lucene，但实现起来要容易得多。

谷歌或Facebook正在使用不同的方法。他们有分布式系统。Googles BigTable是一个很好的关键词，或者“映射和还原”概念(Apache Hadoop)是更多研究的一个很好的起点。

Answer 3

尝试使用表分区。在大型表场景中，对表进行分区可能会很有帮助。有关PostgreSQL的信息，请在此处试用PostgreSQL Partitioning。对于高可扩展的快速性能搜索，有时采用NoSQL数据库可能很有用(就像Facebook一样)。