代码之家 › 专栏 › 技术社区 › cite

更好的SQL-:group vs:select=>DISTINCT

activerecord postgresql ruby-on-rails sql

cite · 技术社区 · 15 年前

让我们假设三个模型,标准联接:

class Mailbox < ActiveRecord::Base
  has_many :addresses
  has_many :domains, :through => :addresses
end

class Address < ActiveRecord::Base
  belongs_to :mailbox
  belongs_to :domain
end

class Domain < ActiveRecord::Base
  has_many :addresses
  has_many :mailboxes, :through => :addresses
end

显然,对于任何给定邮箱,如果您想知道它在哪些域中有地址,您有两种可能的方法:

m = Mailbox.first
# either: SELECT DISTINCT domains.id, domains.name FROM "domains" INNER JOIN 
#         "addresses" ON "domains".id = "addresses".domain_id WHERE 
#         (("addresses".mailbox_id = 1))
m.domains.all(:select => 'DISTINCT domains.id, domains.name')
# or: SELECT domains.id, domains.name FROM "domains" INNER JOIN "addresses" ON
#     "domains".id = "addresses".domain_id WHERE (("addresses".mailbox_id = 1))
#      GROUP BY domains.id, domains.name
m.domains.all(:select => 'domains.id, domains.name', 
  :group => 'domains.id, domains.name')

我的问题是,我不知道哪种解决方案更好。当我没有指定任何其他条件时,PostgreSQL查询规划器会倾向于第二个解决方案(按预期工作),但如果我向查询添加条件,它将归结为“唯一”与“组”:

使用“DISTINCT”:

 Unique  (cost=16.56..16.57 rows=1 width=150)
   ->  Sort  (cost=16.56..16.56 rows=1 width=150)
         Sort Key: domains.name, domains.id
         ->  Nested Loop  (cost=0.00..16.55 rows=1 width=150)
               ->  Index Scan using index_addresses_on_mailbox_id on addresses  (cost=0.00..8.27 rows=1 width=4)
                     Index Cond: (mailbox_id = 1)
               ->  Index Scan using domains_pkey on domains  (cost=0.00..8.27 rows=1 width=150)
                     Index Cond: (domains.id = addresses.domain_id)
                     Filter: (domains.active AND domains.selfmgmt)
(9 rows)

使用“分组方式”:

Group  (cost=16.56..16.57 rows=1 width=150)
   ->  Sort  (cost=16.56..16.56 rows=1 width=150)
         Sort Key: domains.name, domains.id
         ->  Nested Loop  (cost=0.00..16.55 rows=1 width=150)
               ->  Index Scan using index_addresses_on_mailbox_id on addresses  (cost=0.00..8.27 rows=1 width=4)
                     Index Cond: (mailbox_id = 1)
               ->  Index Scan using domains_pkey on domains  (cost=0.00..8.27 rows=1 width=150)
                     Index Cond: (domains.id = addresses.domain_id)
                     Filter: (domains.active AND domains.selfmgmt)
(9 rows)

我应该使用“:group”还是“:select=>“DISTINCT”?这种选择与其他现代RDBMS(如Oracle、DB2或MySQL)相同吗(我没有访问这些数据库的权限,因此无法执行测试)?

2 回复 | 直到 15 年前

user80168 user80168 15 年前

如果您使用的是Postgresql<8.4(考虑到计划,我想你是这样的)-通常使用 GROUP BY DISTINCT 因为它的计划更有效。

在8.4中没有区别,因为DISTINCT被“教导”能够使用组运算符。

mjv 15 年前

SQL必须执行相同的步骤来收集所需的信息,以便为GROUP BY或“等效”不同查询做准备:相同的筛选器、相同的排序等。区别在于最后一步,这里Postgre称之为“组”或“唯一”。

当GROUPBY查询在其SELECT中不包含任何聚合时,“GROUP”和“Unique”本质上是相同的(即使它们在服务器代码中归结为不同的方法)。在存在一些聚合的情况下,如say COUNT(*)、MAX(一些_字段)等。“Group”步骤需要更多的资源,因为它需要额外的存储来存储计数、最小值等,并且需要增加或与之比较等。

我会采用分组的方法,就像在SELECT中添加聚合一样,如果在某个时候需要这样的信息,而不会对查询做太多更改。即使未显示实际计数,应用程序也可以按此计数的降序显示域。