Explain

EXPLAINは、与えられたクエリーを実行するためにどのように「計画を立てたか」を表示します。これはつまり、実行前のビューであり、クエリーをプロファイリングするのと混同しないようにしてください。

EXPLAINは、場合によっては何千通りにもなりうるクエリーの実行方法を評価するプロセスの後で、「最適な計画」と考えられるものを表示します。他の計画がどのようなものだったかは"possible_keys"に簡単な情報がありますが、一般的にはこの情報を得るにはOPTIMIZER_TRACEを確認する必要があります。

常にEXPLAIN FORMAT=JSONを使うことをおすすめします。これは、(デフォルトのフォーマットでは表示されない)クエリーコストを出力するからです。「どの程度の影響があるか」で考えて話ができるという点で、コストは重要です。ここで私が言いたいのは、データベースの専門家が「一時テーブルを作るのはダメ」「結合はダメ」と言っているのを聞いたことがあるということです。これらのコメントは、重要な背景情報がないので、直接回答するのは困難です。水だって十分な量があれば悪いとも言えます。

実行する可能性のあるクエリーにEXPLAIN FORMAT=JSONをつけて実行してみるのと同じように、実行中のコネクションに対してEXPLAIN FORMAT=JSON FOR CONNECTION <コネクションID>を実行して、実行時にどのような最適化がされているかを確認することもできます。これは、データ(とそれを元にした統計情報)に対する変更が計画の選択に影響を及ぼすにつれて起きる一時的なエラーを診断するのに役立つことがあります。

例1) テーブルスキャンの処理を表示するEXPLAIN

EXPLAIN FORMAT=JSON
SELECT * FROM Country WHERE continent='Asia' and population > 5000000;
{
  "query_block": {
   "select_id": 1,
   "cost_info": {
   "query_cost": "53.80"            # このクエリは53.80コストユニットかかる
   },
   "table": {
   "table_name": "Country",
   "access_type": "ALL",            # ALLはテーブルスキャンの意味
   "rows_examined_per_scan": 239,   # テーブル内の239行全てにアクセス
   "rows_produced_per_join": 11,
   "filtered": "4.76",
   "cost_info": {
      "read_cost": "51.52",
      "eval_cost": "2.28",
      "prefix_cost": "53.80",
      "data_read_per_join": "2K"
   },
   "used_columns": [
      "Code",
      "Name",
      "Continent",
      "Region",
      "SurfaceArea",
      "IndepYear",
      "Population",
      "LifeExpectancy",
      "GNP",
      "GNPOld",
      "LocalName",
      "GovernmentForm",
      "HeadOfState",
      "Capital",
      "Code2"
   ],
   "attached_condition": "((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000))"
   }
  }
}

例1ではEXPLAINは、53.80コストユニットのコストをかけてテーブルスキャン(access_type ALL)を行ってクエリーを実行する予定であることを表しています。出力にpossible_keysの項目がないことから(通常はaccess_typeの下にあります)、使用できる可能性のあるインデックスはありません。

rows_examined_per_scanの数は、世界に存在する国の数とほぼ一致していて、テーブルにある行数(239)と同じです。これはあくまで見積もりであって、実際にテーブルスキャンすることはパフォーマンスに影響を及ぼすので、100％正しいとは限りません。

attached_conditionは、行 ((`world`.`Country`.`Continent` = 'Asia') and (`world`.`Country`.`Population` > 5000000)) を読み込む時に適用されるフィルターを表しています。インデックスがあれば、行を読み込む前にこのattached_conditionsが充足される可能性もありますが、ここに表示があるということは、そうではないということになります。

私は、rows_examined_per_scanと、クエリーが実行される際にクライアントに送信される行数を比較するのが好きです¹。送信される行はクエリーを実行するまで分かりませんが、実行してみると、その数が32であることが分かります。つまり、このクエリーはクライアントに送信される7.5倍の行数を確認しているということであり、最適化の可能性を示唆しています。

注意として、全部で239行というのは大きなものではなく、全行がメモリーに収まるだろうということを言っておくのは重要でしょう。しかし、テーブルスキャンはメモリーに収まることを強く要求する性質があり、テーブルが大きくなるにつれてパフォーマンスの状況は「崖を転げ落ちるように」悪くなるでしょう。インデックスを追加すれば、パフォーマンスも良くなりますし、データの増大に対してパフォーマンスを一定に保てます。