開発環境ではarproxy+explain_parserでexplainをログに吐く. arproxy.rbはconfig/initializer/arproxy.rb などに置く.

arproxy.rb

if defined?(Rails) && Rails.env.development?
  class ExplainTracer < Arproxy::Base
    def execute(sql, name = nil)
      return super(sql, name) unless load_and_select_query?(sql, name)

      result = super("EXPLAIN #{sql}", name)
      explain = ExplainParser.call(result).first
      log_for_explain(sql, explain)

      # must to call super with original query
      super(sql, name)
    end

    def log_for_explain(sql, explain)
      Rails.logger.info log_using_index(sql) if explain.using_index?
      Rails.logger.info log_possible_keys_not_found(sql) unless explain.possible_keys
      Rails.logger.error log_danger_sql(sql, 'using filesort') if explain.using_filesort?
      Rails.logger.error log_danger_sql(sql, 'using temporary') if explain.using_temporary?
    end

    def load_and_select_query?(sql, name)
      sql =~ /^SELECT/ && name =~ /Load/
    end

    def log_using_index(sql)
      "[Using Index] - #{sql}"
    end

    def log_possible_keys_not_found(sql)
      "[Not Found Possible Keys] - #{sql}"
    end

    def log_danger_sql(sql, type)
      at_mart_line = "@" * sql.size
      <<EOS
@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@#{at_mart_line}

    WARNING: #{type} - #{sql};

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@#{at_mart_line}
EOS
    end
  end

  Arproxy.configure do |config|
    config.adapter = 'mysql2'
    config.use ExplainTracer
  end

  Arproxy.enable!
end

Gemfile

gem 'arproxy'
gem 'explain_parser'