如何在Rails和PostgreSQL中高效地搜索最后一个符合条件的记录？

Question

假设你想找到与字符串“Joe”匹配的数据库中最后一条记录（最高ID）。数据库中有100,000多条记录，并且有许多匹配项（比如成千上万个）。那么，最有效的方法是什么？PostgreSQL需要找到所有记录吗，还是只需要找到最后一条记录？这个查询是否特别慢？此操作在Rails 3.0.7、Ruby 1.9.2和PostgreSQL 8.3下进行。

- B Seven

---

如果正确设置索引，它不应该很慢。 - Vincent Savard

那么，这意味着只需要在“name”上创建一个索引？索引的类型重要吗？ - B Seven

Answer 1

重要的一点是要有一个匹配索引。您可以尝试这个小测试设置:

为测试创建模式x:

-- DROP SCHEMA x CASCADE;  -- to wipe it all for a retest or when done.
CREATE SCHEMA x;
CREATE TABLE x.tbl(id serial, name text);

插入10000个随机行：

INSERT INTO x.tbl(name) SELECT 'x' || generate_series(1,10000);

插入另外10,000行相同名称的数据:

INSERT INTO x.tbl(name) SELECT 'y' || generate_series(1,10000)%20;

删除10%的随机内容，使之更符合真实情况：

DELETE FROM x.tbl WHERE random() < 0.1;

ANALYZE x.tbl;

查询可以是这个样子：

SELECT *
FROM   x.tbl
WHERE  name = 'y17'
ORDER  BY id DESC
LIMIT  1;

--> 总运行时间：5.535 毫秒

CREATE INDEX tbl_name_idx on x.tbl(name);

--> 总运行时间：1.228毫秒

DROP INDEX x.tbl_name_idx;
CREATE INDEX tbl_name_id_idx on x.tbl(name, id);

--> 总运行时间：0.053毫秒

DROP INDEX x.tbl_name_id_idx;
CREATE INDEX tbl_name_id_idx on x.tbl(name, id DESC);

--> 总运行时间：0.048毫秒

DROP INDEX x.tbl_name_id_idx;
CREATE INDEX tbl_name_idx on x.tbl(name);
CLUSTER x.tbl using tbl_name_idx;

--> 总运行时间：1.144毫秒

DROP INDEX x.tbl_name_id_idx;
CREATE INDEX tbl_name_id_idx on x.tbl(name, id DESC);
CLUSTER x.tbl using tbl_name_id_idx;

--> 总运行时间: 0.047 毫秒

结论

使用适当的索引，查询的性能提高超过100倍。
最佳表现是一个多列索引，其中筛选列在前，排序列在后。
在此情况下匹配索引中的排序顺序会有所帮助。

聚集有助于简单索引，因为仍然需要从表中读取许多列，并且可以在聚集后的相邻块中找到这些列。但在这种情况下，它对多列索引没有帮助，因为只需从表中获取一条记录。
了解更多关于手册中的多列索引。

所有这些效果都随着表格大小的增加而增加。两个微小列的10000行只是一个非常小的测试用例。

- Erwin Brandstetter

Answer 2

您可以在Rails中组合查询，ORM将编写正确的SQL：

Model.where(:name=>"Joe").order('created_at DESC').first

这不应该导致检索所有的Model记录，甚至也不应导致表扫描。

- Eric

---

你的声明基于什么依据，认为这不应该导致表扫描？如果没有适当的索引，它将始终导致表扫描。 - Erwin Brandstetter

假设SELECT将使其无需考虑。 - Eric

这不是PostgreSQL（或任何其他关系型数据库）的工作方式。没有合适的索引就意味着要进行全表扫描，没有其他方法。（尽管主键列会自动创建索引。） - Erwin Brandstetter

我相信 where 子句将限制结果并避免表扫描。 - konyak

Answer 3

这可能是最简单的：

SELECT [columns] FROM [table] WHERE [criteria] ORDER BY [id column] DESC LIMIT 1

注意：索引在这里非常重要。如果你没有正确地建立索引，一个庞大的数据库无论如何都会慢于搜索。

- Chris Cashwell