计算执行的查询数量

I'd like to test that a certain piece of code performs as few SQL queries as possible.

我想测试的是，某段代码执行的SQL查询越少越好。

ActiveRecord::TestCase seems to have its own assert_queries method, which will do just that. But since I'm not patching ActiveRecord, it's of little use to me.

TestCase似乎有它自己的assert_queries方法，它会这样做。但是因为我不是在拍ActiveRecord，所以对我来说没什么用。

Does RSpec or ActiveRecord provide any official, public means of counting the number of SQL queries performed in a block of code?

RSpec或ActiveRecord提供任何官方的、公开的方法来计算在代码块中执行的SQL查询数吗?

6 个解决方案

#1

I think you answered your own question by mentioning assert_queries, but here goes:

我认为您通过提到assert_queries回答了您自己的问题，但是如下:

I would recommend taking a look at the code behind assert_queries and using that to build your own method which you can use to count queries. The main magic involved here is this line:

我建议您看看assert_queries背后的代码，并使用它来构建您自己的方法，您可以使用它来计数查询。这里的主要魔力是这样的:

ActiveSupport::Notifications.subscribe('sql.active_record', SQLCounter.new)

I had a bit of a tinker this morning and ripped out the parts of ActiveRecord that do the query counting and came up with this:

今天早上我做了个小改动，把ActiveRecord的部分删掉了，做了查询计数，然后得出了如下结论:

module ActiveRecord
  class QueryCounter
    cattr_accessor :query_count do
      0
    end

    IGNORED_SQL = [/^PRAGMA (?!(table_info))/, /^SELECT currval/, /^SELECT CAST/, /^SELECT @@IDENTITY/, /^SELECT @@ROWCOUNT/, /^SAVEPOINT/, /^ROLLBACK TO SAVEPOINT/, /^RELEASE SAVEPOINT/, /^SHOW max_identifier_length/]

    def call(name, start, finish, message_id, values)
      # FIXME: this seems bad. we should probably have a better way to indicate
      # the query was cached
      unless 'CACHE' == values[:name]
        self.class.query_count += 1 unless IGNORED_SQL.any? { |r| values[:sql] =~ r }
      end
    end
  end
end

ActiveSupport::Notifications.subscribe('sql.active_record', ActiveRecord::QueryCounter.new)

module ActiveRecord
  class Base
    def self.count_queries(&block)
      ActiveRecord::QueryCounter.query_count = 0
      yield
      ActiveRecord::QueryCounter.query_count
    end
  end
end

You will be able to reference the ActiveRecord::Base.count_queries method anywhere. Pass it a block wherein your queries are run and it will return the number of queries that have been executed:

您将能够引用ActiveRecord: Base。count_queries方法。传递给它一个您的查询正在运行的块，它将返回已执行的查询数:

ActiveRecord::Base.count_queries do
  Ticket.first
end

Returns "1" for me. To make this work: put it in a file at lib/active_record/query_counter.rb and require it in your config/application.rb file like this:

回报对我“1”。要做到这一点:将它放在lib/active_record/query_counter的文件中。在配置/应用程序中需要它。rb文件如下:

require 'active_record/query_counter'

Hey presto!

您看!

---

A little bit of explanation probably is required. When we call this line:

可能需要一点解释。当我们称这条线为:

    ActiveSupport::Notifications.subscribe('sql.active_record', ActiveRecord::QueryCounter.new)

We hook into Rails 3's little notifications framework. It's a shiny little addition to the latest major version of Rails that nobody really knows about. It allows us to subscribe to notifications of events within Rails by using the subscribe method. We pass in the event we want to subscribe to as the first argument then any object that responds to call as the second.

我们连接到Rails 3的小通知框架。这是Rails最新主要版本的一个亮点，没有人真正了解它。它允许我们使用subscribe方法订阅Rails中的事件通知。我们传递的事件是我们想要订阅的第一个参数，然后任何响应调用的对象作为第二个参数。

In this case when a query is executed our little query counter will dutifully increment the ActiveRecord::QueryCounter.query_count variable, but only for the real queries.

在这种情况下，当执行查询时，我们的小查询计数器将尽职地增加ActiveRecord::QueryCounter。query_count变量，但只用于真正的查询。

Anyway, this was fun. I hope it comes useful to you.

无论如何,这是有趣的。我希望它对你有用。

#2

My vision of Ryan's script (cleaned up a bit and wrapped in a matcher), hope it is still actual for someone:

我对Ryan的剧本的看法(整理了一下，用编码器包装)，希望它对某些人来说仍然是真实的:

I put this to spec/support/query_counter.rb

我把它放到spec/support/query_counter.rb中

module ActiveRecord
  class QueryCounter

    attr_reader :query_count

    def initialize
      @query_count = 0
    end

    def to_proc
      lambda(&method(:callback))
    end

    def callback(name, start, finish, message_id, values)
      @query_count += 1 unless %w(CACHE SCHEMA).include?(values[:name])
    end

  end
end

and this to spec/support/matchers/exceed_query_limit.rb

这个规范/支持/匹配器/ exceed_query_limit.rb

RSpec::Matchers.define :exceed_query_limit do |expected|

  match do |block|
    query_count(&block) > expected
  end

  failure_message_for_should_not do |actual|
    "Expected to run maximum #{expected} queries, got #{@counter.query_count}"
  end

  def query_count(&block)
    @counter = ActiveRecord::QueryCounter.new
    ActiveSupport::Notifications.subscribed(@counter.to_proc, 'sql.active_record', &block)
    @counter.query_count
  end

end

Usage:

用法:

expect { MyModel.do_the_queries }.to_not exceed_query_limit(2)

#3

Here's another formulation of Ryan's and Yuriy's solution that's just a function you add to your test_helper.rb:

这是Ryan和Yuriy解决方案的另一种表述，这是你在test_helper.rb中添加的一个函数。

def count_queries &block
  count = 0

  counter_f = ->(name, started, finished, unique_id, payload) {
    unless payload[:name].in? %w[ CACHE SCHEMA ]
      count += 1
    end
  }

  ActiveSupport::Notifications.subscribed(counter_f, "sql.active_record", &block)

  count
end

Usage is just:

用法是:

c = count_queries do
  SomeModel.first
end

#4

• helpful error message
• 有用的错误信息
• removes subscribers after execution
• 删除用户后执行

(based on Jaime Cham's answer)

(基于詹姆·查姆的回答)

class ActiveSupport::TestCase
  def sql_queries(&block)
    queries = []
    counter = ->(*, payload) {
      queries << payload.fetch(:sql) unless ["CACHE", "SCHEMA"].include?(payload.fetch(:name))
    }

    ActiveSupport::Notifications.subscribed(counter, "sql.active_record", &block)

    queries
  end

  def assert_sql_queries(expected, &block)
    queries = sql_queries(&block)
    queries.count.must_equal(
      expected,
      "Expected #{expected} queries, but found #{queries.count}:\n#{queries.join("\n")}"
    )
  end
end

#5

Here's a version that makes it easy to count queries matching a given pattern.

这里有一个版本，可以方便地计算匹配给定模式的查询。

module QueryCounter

  def self.count_selects(&block)
    count(pattern: /^(\s+)?SELECT/, &block)
  end

  def self.count(pattern: /(.*?)/, &block)
    counter = 0

    callback = ->(name, started, finished, callback_id, payload) {
      counter += 1 if payload[:sql].match(pattern)
      # puts "match? #{!!payload[:sql].match(pattern)}: #{payload[:sql]}"
    }

    # http://api.rubyonrails.org/classes/ActiveSupport/Notifications.html
    ActiveSupport::Notifications.subscribed(callback, "sql.active_record", &block)

    counter
  end

end

Usage:

用法:

test "something" do
  query_count = count_selects {
    Thing.first
    Thing.create!(size: "huge")
  }
  assert_equal 1, query_count
end

#6

Based on Jaime's answer, the following supports an assertion for the number of queries so far in the current test case, and will log the statements in case of failure. I think it's useful pragmatically to combine a SQL check like this with a functional test as it reduces the setup effort.

根据Jaime的回答，下面的内容支持当前测试用例中查询的数量的断言，并将在失败时记录语句。我认为，将这样的SQL检查与功能测试结合使用是有用的，因为它减少了安装工作。

class ActiveSupport::TestCase

   ActiveSupport::Notifications.subscribe('sql.active_record') do |name, started, finished, unique_id, payload|
     (@@queries||=[]) << payload unless payload[:name].in? %w(CACHE SCHEMA)
   end

   def assert_queries_count(expected_count, message=nil)
     assert_equal expected_count, @@queries.size,
       message||"Expected #{expected_count} queries, but #{@@queries.size} queries occurred.#{@@queries[0,20].join(' ')}"
   end

   # common setup in a super-class (or use Minitest::Spec etc to do it another way)
   def setup
     @@queries = []
   end

end

Usage:

用法:

def test_something
   post = Post.new('foo')
   assert_queries_count 1 # SQL performance check
   assert_equal "Under construction", post.body # standard functional check
end

Note the query assertion should happen immediately in case the other assertions themselves trigger extra queries.

注意，如果其他断言本身触发了额外的查询，那么应该立即执行查询断言。