计算执行的查询数量

Question

我想测试某段代码是否执行尽可能少的 SQL 查询。

ActiveRecord::TestCase 似乎有自己的 assert_queries 方法，它就能做到这一点。但由于我没有修补 ActiveRecord，所以它对我来说没什么用处。

RSpec 或 ActiveRecord 是否提供任何官方、公开的方法来计算代码块中执行的 SQL 查询的数量？

Answer 1

我认为您通过提到 assert_queries 回答了自己的问题，但这里是：

我建议您查看 assert_queries 背后的代码，并使用它来构建您自己的方法可以用来统计查询。这里涉及的主要魔力是这一行：

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

今天早上我做了一些修补，撕掉了 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

您将能够引用 ActiveRecord::Base .count_queries 方法在任何地方。将其传递给运行查询的块，它将返回已执行的查询数：

ActiveRecord::Base.count_queries do
  Ticket.first
end

对我来说返回“1”。要实现此功能：将其放入 lib/active_record/query_counter.rb 的文件中，并在 config/application.rb 文件中要求它，如下所示：

require 'active_record/query_counter'

嘿，快点！

---

可能需要一点解释。当我们调用这一行时：

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

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

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

不管怎样，这很有趣。我希望它对你有用。

Answer 2

我对 Ryan 脚本的看法（清理了一下并包装在匹配器中），希望它对某人来说仍然是真实的：

我将其放入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

并将其放入spec/support/matchers/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

用法：

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

Answer 3

这是 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

用法如下：

c = count_queries do
  SomeModel.first
end

Answer 4

• 有用的错误消息
• 在执行后删除订阅者

（基于 Jaime Cham 的回答）

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

Answer 5

根据 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

用法：

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

请注意，查询断言应该立即发生，以防其他断言本身触发额外的查询。

Answer 6

这是一个可以轻松计算与给定模式匹配的查询的版本。

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

用法：

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

Answer 7

我最终创建了一个小 gem 来抽象这个问题：sql_spy。

只需将其添加到您的 Gemfile 中：

gem "sql_spy"

将代码包装在 SqlSpy.track { ... }:

queries = SqlSpy.track do
  # Some code that triggers ActiveRecord queries
  users = User.all
  posts = BlogPost.all
end

... 中，并在断言中使用该块的返回值：

expect(queries.size).to eq(2)
expect(queries[0].sql).to eq("SELECT * FROM users;")
expect(queries[0].model_name).to eq("User")
expect(queries[0].select?).to be_true
expect(queries[0].duration).to eq(1.5)

Answer 8

我添加了基于 Yuriy 的解决方案检查每个表的查询的功能

# spec/support/query_counter.rb
require 'support/matchers/query_limit'

module ActiveRecord
  class QueryCounter
    attr_reader :queries

    def initialize
      @queries = Hash.new 0
    end

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

    def callback(name, start, finish, message_id, values)
      sql = values[:sql]

      if sql.include? 'SAVEPOINT'
        table = :savepoints
      else
        finder = /select.+"(.+)"\..+from/i if sql.include? 'SELECT'
        finder = /insert.+"(.+)".\(/i if sql.include? 'INSERT'
        finder = /update.+"(.+)".+set/i if sql.include? 'UPDATE'
        finder = /delete.+"(.+)" where/i if sql.include? 'DELETE'
        table = sql.match(finder)&.send(:[],1)&.to_sym
      end

      @queries[table] += 1 unless %w(CACHE SCHEMA).include?(values[:name])

      return @queries
    end

    def query_count(table = nil)
      if table
        @queries[table]
      else
        @queries.values.sum
      end
    end
  end
end

RSpec 匹配器看起来像

# spec/support/matchers/query_limit.rb
RSpec::Matchers.define :exceed_query_limit do |expected, table|
  supports_block_expectations

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

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

  failure_message_when_negated do |actual|
    queries = 'query'.pluralize expected
    table_name = table.to_s.singularize.humanize.downcase if table

    out = "expected to run a maximum of #{expected}"
    out += " #{table_name}" if table
    out += " #{queries}, but got #{@counter.query_count table}"
  end
end

RSpec::Matchers.define :meet_query_limit do |expected, table|
  supports_block_expectations

  match do |block|
    if expected.is_a? Hash
      results = queries_count(table, &block)
      expected.all? { |table, count| results[table] == count }
    else
      query_count(&block) == expected
    end
  end

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

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

  def message(expected, table, negated = false)
    queries = 'query'.pluralize expected
    if expected.is_a? Hash
      results = @counter.queries
      table, expected = expected.find { |table, count| results[table] != count }
    end

    table_name = table.to_s.singularize.humanize.downcase if table

    out = 'expected to'
    out += ' not' if negated
    out += " run exactly #{expected}"
    out += " #{table_name}" if table
    out += " #{queries}, but got #{@counter.query_count table}"
  end

  failure_message do |actual|
    message expected, table
  end

  failure_message_when_negated do |actual|
    message expected, table, true
  end
end

用法

expect { MyModel.do_the_queries }.to_not meet_query_limit(3)
expect { MyModel.do_the_queries }.to meet_query_limit(3)
expect { MyModel.do_the_queries }.to meet_query_limit(my_models: 2, other_tables: 1)