假设你想找到与字符串“Joe”匹配的数据库中最后一条记录(最高ID)。数据库中有100,000多条记录,并且有许多匹配项(比如成千上万个)。那么,最有效的方法是什么?PostgreSQL需要找到所有记录吗,还是只需要找到最后一条记录?这个查询是否特别慢?此操作在Rails 3.0.7、Ruby 1.9.2和PostgreSQL 8.3下进行。
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行只是一个非常小的测试用例。
您可以在Rails中组合查询,ORM将编写正确的SQL:
Model.where(:name=>"Joe").order('created_at DESC').first
这不应该导致检索所有的Model记录,甚至也不应导致表扫描。
where
子句将限制结果并避免表扫描。 - konyak这可能是最简单的:
SELECT [columns] FROM [table] WHERE [criteria] ORDER BY [id column] DESC LIMIT 1
注意:索引在这里非常重要。如果你没有正确地建立索引,一个庞大的数据库无论如何都会慢于搜索。