ActiveRecord::Relation#explainを使うと、実行するSQLのEXPLAINの結果を取得出来ます。ActiveRecord::Relation#explainではデフォルト設定でしか結果を取得出来なのですが、EXPLAINにはオプションが設定できます。
特にMySQLではオプティマイザが評価したSQLのコスト等の詳細な情報はJSON形式のEXPLAINでしか取得できません😢
実際の結果の違いを見るとJSON形式のほうが有用な情報が多いことが分かると思います👀
EXPLAIN SELECT `samples`.* FROM `samples` WHERE `samples`.`title` = 'hoge' +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | samples | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.0 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set (0.00 sec) EXPLAIN FORMAT=JSON SELECT `samples`.* FROM `samples` WHERE `samples`.`title` = 'hoge' +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | { "query_block": { "select_id": 1, "cost_info": { "query_cost": "0.35" }, "table": { "table_name": "samples", "access_type": "ALL", "rows_examined_per_scan": 1, "rows_produced_per_join": 1, "filtered": "100.00", "cost_info": { "read_cost": "0.25", "eval_cost": "0.10", "prefix_cost": "0.35", "data_read_per_join": "1K" }, "used_columns": [ "id", "category", "title", "body" ], "attached_condition": "(`sample`.`samples`.`title` = 'hoge')" } } } | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
というわけで今回はActiveRecordにモンキーパッチを当てて下記のような形でJSON形式で取得出来るようにしてみました。
最終的な使い方はこちら
reuqire 'monkey_path.rb' # モンキーパッチのコードをrequire Mysql::Sample.with_title.explain # => 通常のEXPLAINの結果 Mysql::Sample.with_title.explain(json: true) # => JSON形式のEXPLAINの結果
実際のモンキーパッチのコードはこちら🐵
ActiveRecord::Relation#explainに引数json: falseを追加して、最終的に実行されるDatabaseStatements#explainまで引数を引き継いでいって、その引数の値を元にFORMATを指定する文言を作成してSQLに設定しています👩🏭(もう少しいいやり方ありそう・・・🤔)
# frozen_string_literal: true require 'active_support' require 'active_record' # NOTE: # This monkey patch add argument(json) for ActiveRecord::Relation#explain. # Argument json is default false. Therefore, it does not affect the default behavior. # Only MySql and Postgresql are supported. module ActiveRecord class Relation # NOTE: https://github.com/rails/rails/blob/master/activerecord/lib/active_record/relation.rb#L239-L241 def explain(json: false) # NOTE: add arg json exec_explain(collecting_queries_for_explain { exec_queries }, json: json) # NOTE: add arg json end end module Explain # NOTE: https://github.com/rails/rails/blob/master/activerecord/lib/active_record/explain.rb#L19-L36 def exec_explain(queries, json: false) # NOTE: add arg json str = queries.map do |sql, binds| msg = +"EXPLAIN for: #{sql}" unless binds.empty? msg << " " msg << binds.map { |attr| render_bind(attr) }.inspect end msg << "\n" msg << connection.explain(sql, binds, json: json) # NOTE: add arg json end.join("\n") # Overriding inspect to be more human readable, especially in the console. def str.inspect self end str end end module ConnectionAdapters module PostgreSQL module DatabaseStatements # https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/mysql/database_statements.rb#L31-L38 def explain(arel, binds = [], json: false) # NOTE: add arg json format_option = "(FORMAT JSON)" if json # NOTE: get format option sql = "EXPLAIN #{format_option} #{to_sql(arel, binds)}" # NOTE: set format option PostgreSQL::ExplainPrettyPrinter.new.pp(exec_query(sql, "EXPLAIN", binds)) end end end module MySQL module DatabaseStatements # NOTE: https://github.com/rails/rails/blob/master/activerecord/lib/active_record/connection_adapters/postgresql/database_statements.rb#L7-L10 def explain(arel, binds = [], json: false) # NOTE: add arg json format_option = "FORMAT=JSON" if json # NOTE: get format option sql = "EXPLAIN #{format_option} #{to_sql(arel, binds)}" # NOTE: set format option start = Concurrent.monotonic_time result = exec_query(sql, "EXPLAIN", binds) elapsed = Concurrent.monotonic_time - start MySQL::ExplainPrettyPrinter.new.pp(result, elapsed) end end end end end
MySQLだけじゃなくてPostgresqlにも対応してみました🙌(SQLiteはJSON形式のEXPLAINに対応していないようだったので対応していません)
