MySQL入門：explainの出力について -vol.4-

よっしー

こんにちは。よっしーです(^^)

今日は、mysqlのexplainについてご紹介します。

背景
EXPLAINの出力を解釈する
おわりに

背景

mysqlのexplainを調査したの内容を備忘として残しました。

explainの一次情報には下記を利用しています。

fw_error_www

EXPLAINの出力を解釈する

EXPLAINの出力のrows列の値を掛け合わせることで、クエリを実行するためにMySQLが調査する必要がある行のおおよその数を知ることができます。これは、クエリを実行するためにMySQLがどれだけの行を検討する必要があるかを大まかに教えてくれるはずです。max_join_sizeシステム変数でクエリを制限している場合、この行の積はまた、実行する複数テーブルのSELECTステートメントと中止するステートメントを決定するために使用されます。

以下の例は、EXPLAINによって提供された情報に基づいて、複数テーブルの結合を進行的に最適化する方法を示しています。

次のSELECTステートメントがあり、これをEXPLAINを使用して調査する予定ですと仮定しましょう。

EXPLAIN SELECT tt.TicketNumber, tt.TimeIn,
               tt.ProjectReference, tt.EstimatedShipDate,
               tt.ActualShipDate, tt.ClientID,
               tt.ServiceCodes, tt.RepetitiveID,
               tt.CurrentProcess, tt.CurrentDPPerson,
               tt.RecordVolume, tt.DPPrinted, et.COUNTRY,
               et_1.COUNTRY, do.CUSTNAME
        FROM tt, et, et AS et_1, do
        WHERE tt.SubmitTime IS NULL
          AND tt.ActualPC = et.EMPLOYID
          AND tt.AssignedPC = et_1.EMPLOYID
          AND tt.ClientID = do.CUSTNMBR;

この例について、以下の仮定を行います。

比較される列は、次のように宣言されています。

Table	Column	Data Type
`tt`	`ActualPC`	`CHAR(10)`
`tt`	`AssignedPC`	`CHAR(10)`
`tt`	`ClientID`	`CHAR(10)`
`et`	`EMPLOYID`	`CHAR(15)`
`do`	`CUSTNMBR`	`CHAR(15)`

テーブルには以下のインデックスが存在します。

Table	Index
`tt`	`ActualPC`
`tt`	`AssignedPC`
`tt`	`ClientID`
`et`	`EMPLOYID` (primary key)
`do`	`CUSTNMBR` (primary key)

最初の段階では、何の最適化も行われていない状態で、EXPLAINステートメントは次の情報を生成します。

table type possible_keys key  key_len ref  rows  Extra
et    ALL  PRIMARY       NULL NULL    NULL 74
do    ALL  PRIMARY       NULL NULL    NULL 2135
et_1  ALL  PRIMARY       NULL NULL    NULL 74
tt    ALL  AssignedPC,   NULL NULL    NULL 3872
           ClientID,
           ActualPC
      Range checked for each record (index map: 0x23)

「type」が各テーブルに対して「ALL」であるため、この出力はMySQLがすべてのテーブルのカテシアン積（つまり、すべての行の組み合わせ）を生成していることを示しています。これは、各テーブルの行数の積を調査する必要があるため、かなりの時間がかかります。この場合、この積は 74 × 2135 × 74 × 3872 = 45,268,558,720 行です。もしテーブルがより大きい場合、かかる時間を想像するのは難しいでしょう。

この問題の一つは、MySQLが列のインデックスを、それらが同じ型およびサイズとして宣言されている場合により効率的に使用できることです。この文脈では、VARCHARとCHARは同じとみなされますが、それらが同じサイズとして宣言されている場合です。tt.ActualPCはCHAR(10)と宣言されており、et.EMPLOYIDはCHAR(15)ですので、長さの不一致があります。

列の長さの不一致を修正するために、ALTER TABLEを使用してActualPCの長さを10文字から15文字に拡張して修正してください：

mysql> ALTER TABLE tt MODIFY ActualPC VARCHAR(15);

今、tt.ActualPCとet.EMPLOYIDはどちらもVARCHAR(15)です。再度EXPLAINステートメントを実行すると、この結果が得られます：

table type   possible_keys key     key_len ref         rows    Extra
tt    ALL    AssignedPC,   NULL    NULL    NULL        3872    Using
             ClientID,                                         where
             ActualPC
do    ALL    PRIMARY       NULL    NULL    NULL        2135
      Range checked for each record (index map: 0x1)
et_1  ALL    PRIMARY       NULL    NULL    NULL        74
      Range checked for each record (index map: 0x1)
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC 1

これは完璧ではありませんが、はるかに改善されています。行の値の積は74分の1に減少しています。このバージョンは数秒で実行されます。

tt.AssignedPC = et_1.EMPLOYIDおよびtt.ClientID = do.CUSTNMBRの比較における列の長さの不一致を解消するために、2つ目の変更を行うことができます。

mysql> ALTER TABLE tt MODIFY AssignedPC VARCHAR(15),
                      MODIFY ClientID   VARCHAR(15);

この修正を行った後、EXPLAINは以下のような出力を生成します：

table type   possible_keys key      key_len ref           rows Extra
et    ALL    PRIMARY       NULL     NULL    NULL          74
tt    ref    AssignedPC,   ActualPC 15      et.EMPLOYID   52   Using
             ClientID,                                         where
             ActualPC
et_1  eq_ref PRIMARY       PRIMARY  15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY  15      tt.ClientID   1

この段階では、クエリはほぼ最適化されています。残る問題は、デフォルトではMySQLがtt.ActualPC列の値が均等に分布していると仮定していることで、ttテーブルの場合はそうではありません。幸い、MySQLにキー分布を解析するよう指示するのは簡単です。

mysql> ANALYZE TABLE tt;

追加のインデックス情報を使用することで、結合が完璧に最適化され、EXPLAINは以下の結果を生成します：

table type   possible_keys key     key_len ref           rows Extra
tt    ALL    AssignedPC    NULL    NULL    NULL          3872 Using
             ClientID,                                        where
             ActualPC
et    eq_ref PRIMARY       PRIMARY 15      tt.ActualPC   1
et_1  eq_ref PRIMARY       PRIMARY 15      tt.AssignedPC 1
do    eq_ref PRIMARY       PRIMARY 15      tt.ClientID   1

EXPLAINの出力のrows列は、MySQLの結合最適化プロセスからの教育的な推測です。行の積とクエリの実際の行数を比較して、それらの数値が実際の状況にかなり近いかどうかを確認してみてください。もし数値に大きな違いがある場合、SELECTステートメントでSTRAIGHT_JOINを使用し、FROM句でテーブルの順序を変更してみることで、パフォーマンスを向上させることができるかもしれません（ただし、STRAIGHT_JOINはセミジョインの変換を無効にするため、インデックスの使用を防ぐ可能性があります。

また、EXPLAIN SELECTをサブクエリと一緒に使用する場合、データを変更するステートメントを実行することがあるかもしれません。