■2-05■ エクゼキュータの処理
エクゼキュータの処理を簡単に説明します。ここではMySQLのデフォルトのストレージエンジンであるMyISAMを例にとります。厳密言えばインデックスの仕組みなど詳細は異なりますが、チューニングの大きな武器であるEXPLAINの出力を理解するために必要最小限の情報を記述しました。エクゼキュータの処理は、単一テーブルの問い合わせ(Simple Query)と複数テーブルを結合する問い合わせ(Join Query)で異なるので、それぞれについて具体例を使って説明します。
ただし、index_mergeとunique_subquery、およびindex_subqueryはrefの変形バージョンなので、説明を省略します。
・図について
本項では図をもとに解説するので、ここで図の表記法を説明します(【図.2-4】参照)。
図.2-4 表記法
|
【図.2-4】において、左側にはMySQLの実際の処理が、右側には本書での表記法が描かれています。
まず、MySQLの処理について説明します。
エクゼキュータはハードディスク上のインデックスデータやテーブルデータを、メモリ上の各種バッファに読み込んでから処理します。
【図.2-4】は、インデックスデータ`2'をkey_bufferに読み込み、さらにそのインデックスデータが参照しているテーブルデータを順次read_bufferに読み込んでいる状況です。
しかし、ハードディスクとメモリを逐一描くのは面倒なので、原則としてインデックスデータとテーブルデータを描き、
バッファに読み込んだデータは背景を色付けして表現します。
単一テーブルの問い合わせ(Simple Query)
●例1 :system
次のSELECT文を分析します。テーブルcellにはデータが1行しかありません。[(例1)] SELECT * FROM cell;
検索方式は`system'が選択されます。
mysql> EXPLAIN SELECT * FROM cell; +----+-------------+-------+--------+---------------+ ... | id | select_type | table | type | possible_keys | ... +----+-------------+-------+--------+---------------+ ... | 1 | SIMPLE | cell | system | NULL | ... +----+-------------+-------+--------+---------------+ ... 1 row in set (0.00 sec)
このSELECT文はインデックスを利用しないので、バッファ`read_rnd_buffer'で処理されます(【図.2-5】参照)。
図.2-5 例1のSELECT文の処理
|
バッファに読み込んだ回数はステータス情報`Handler_read_rnd_next'に加算されます。ここでは1行のみ読み込むので`+1'が加算されます。
●例2 :const
次のSELECT文を分析します。テーブルt1_pkeyの列pkeyには、主キー制約が設定されています。[(例2)] SELECT * FROM t1_pkey WHERE pkey = 2;
WHERE句の条件が``pkey = 定数''、且つ、列pkeyが主キーなので、検索方式として`const'が選択されます。
mysql> EXPLAIN SELECT * FROM t1_pkey WHERE pkey = 2; +----+-------------+---------+-------+---------------+ ... | id | select_type | table | type | possible_keys | ... +----+-------------+---------+-------+---------------+ ... | 1 | SIMPLE | t1_pkey | const | PRIMARY | ... +----+-------------+---------+-------+---------------+ ... 1 row in set (0.00 sec)
このSELECT文はバッファ`key_buffer'と`read_buffer'で処理されます(【図.2-6】参照)。
図.2-6 例2のSELECT文の処理
|
-
(1)テーブル`t1_pkey'のインデックスファイル`t1_pkey.MYI'からkey_bufferに、条件``pkey = 2''に一致するデータを含むブロックを読み込みます。
key_bufferへの読み込みはブロック単位で、必要な部分のみ行われます。
この例では、テーブルt1_pkeyの列`pkey=2'を含むインデックスブロックの読み込みをリクエストします。key_bufferはキャッシュとしても機能するので、まだ読み込まれていない場合にのみ、実際にインデックスブロックを読み込みます。
key_bufferへの読み込みをリクエストした回数はステータス情報`Key_read_requests'に、実際に読み込んだ回数は`Key_reads'に加算します。
(2)key_buffer中のデータが参照しているテーブルデータをread_bufferに読み込みます。
key_bufferのデータをアクセスした回数はステータス情報`Handler_read_key'に加算されます。ここでは``pkey = 2''となるデータに1回アクセスしたので、`+1'が加算されます。
●例3 :ref
次のSELECT文を分析します。テーブルt1_idxの列idxは、インデックスが設定されています。[(例3)] SELECT * FROM t1_idx WHERE idx = 2;
WHERE句の条件が``idx = 定数''なので、検索方式として`ref'が選択されます。
mysql> EXPLAIN SELECT * FROM t1_idx WHERE idx = 2; +----+-------------+--------+------+---------------+------+ ... | id | select_type | table | type | possible_keys | key | ... +----+-------------+--------+------+---------------+------+ ... | 1 | SIMPLE | t1_idx | ref | idx | idx | ... +----+-------------+--------+------+---------------+------+ ...
このSELECT文はバッファ`key_buffer'と`read_buffer'で処理されます(【図.2-7】参照)。
図.2-7 例3のSELECT文の処理
|
-
(1)インデックスデータをkey_bufferに読み込む部分は、検索方式`const'の場合と同じです(例2参照)。
(2)key_buffer中のデータが参照しているテーブルデータを、データファイル`t1_idx.MYD'からread_bufferに読み込みます。
インデックスは複数の行を参照していることもあるので、それらを順にスキャンしてread_bufferに読み込みます。この例では、`idx=2'である3行がread_bufferに読み込まれます。
read_bufferに読み込んだ回数はステータス情報`Handler_read_next'に加算されます。この例では`+3'が加算されます。
●例4 :range
次のSELECT文を分析します。テーブルt1_idxの列idxには、インデックスが設定されています。[(例4)] SELECT * FROM t1_idx WHERE idx < 3;
WHERE句の条件が``idx < 定数''なので、検索方式として`range'が選択されます。
mysql> EXPLAIN SELECT * FROM t1_idx WHERE idx < 5; +----+-------------+--------+-------+---------------+------+ ... | id | select_type | table | type | possible_keys | key | ... +----+-------------+--------+-------+---------------+------+ ... | 1 | SIMPLE | t1_idx | range | idx | idx | ... +----+-------------+--------+-------+---------------+------+ ... 1 row in set (0.00 sec)
このSELECT文はバッファ`key_buffer'と`read_buffer'で処理されます(【図.2-8】参照)。
図.2-8 例4のSELECT文の処理
|
-
(1)条件``idx = 1''に一致するインデックスデータをkey_bufferに読み込み、インデックスが参照しているテーブルデータをread_bufferに読み込む部分は、検索方式`ref'と同じです(例3参照)。
(2)上記と同様の処理を、条件``idx < 3''に一致する間、繰り返します。
●例5 :index
次のSELECT文を分析します。テーブルt1_midxsの列idxと列dataには、マルチカラムインデックスが設定されています。[(例5)] SELECT * FROM t1_midxs WHERE data = 234;
WHERE句の条件は``data = 定数''なので、検索方式として`index'が選択されます。これは、列dataがマルチカラムインデックス(idx,data)を構成する1要素だからです。
mysql> EXPLAIN SELECT * FROM t1_midxs WHERE data = 234; +----+-------------+----------+-------+---------------+------+ ... | id | select_type | table | type | possible_keys | key | ... +----+-------------+----------+-------+---------------+------+ ... | 1 | SIMPLE | t1_midxs | index | NULL | idx | ... +----+-------------+----------+-------+---------------+------+ ... 1 row in set (0.00 sec)
このSELECT文はバッファ`key_buffer'と`read_buffer'で処理されます(【図.2-9】参照)。
図.2-9 例5のSELECT文の処理
|
-
(1)インデックス情報の先頭部分``idx=1,data=234''をkey_bufferに読み込みます。
(2)インデックスが参照しているテーブルデータをread_bufferに読み込み、条件``data =234''に一致するか否か検査します。
(3)同様の処理(1),(2)をインデックス全てに渡って行います。よって、全てのテーブルデータがread_bufferに読み込まれることになります。
●例6 :ALL
次のSELECT文を分析します。テーブルt1にはなんの制約も設定されていません。
[(例6)] SELECT * FROM t1 WHERE data = 4531;
インデックスが使えないので、検索方式として`ALL'が選択されます。
mysql> EXPLAIN SELECT count(*) FROM t1 WHERE data = 4531; +----+-------------+--------+------+---------------+------+ ... | id | select_type | table | type | possible_keys | key | ... +----+-------------+--------+------+---------------+------+ ... | 1 | SIMPLE | t1 | ALL | NULL | NULL | ... +----+-------------+--------+------+---------------+------+ ... 1 row in set (0.00 sec)
このSELECT文はバッファ`read_rnd_buffer'で処理されます(【図.2-10】参照)。
図.2-10 例6のSELECT文の処理
|
-
(1)データファイル`t1.MYD'から1行目のデータがread_rnd_bufferに読み込まれ、検索条件``data=234''に一致するか否か検査します。
(2)同様に全ての行がバッファに読み込まれ、検索条件が検査します。
read_rnd_bufferに読み込まれた行数は、ステータス情報`Handler_read_rnd_next'に加算します。この場合はテーブルt1の行数が加算されることになります。
複数テーブルを結合する問い合わせ(Join Query)
ここで説明に使うテーブルはt1_pkeyとt2_idxで、テーブルt1_pkeyは列pkeyに主キー(PRIMARY KEY)制約、テーブルt2_idxは列idxにインデックスが設定されています。●例7
次のSELECT文を分析します。[(例7)] SELECT * FROM t1_pkey AS a, t2_idx AS b WHERE a.pkey = b.idx AND a.pkey = 2;
このSELECT文の問い合わせ計画は次のとおりで、検索方式はテーブルt1_pkeyは`const'、テーブルt2_idxは`ref'が選択されることが分かります。
mysql> EXPLAIN SELECT * FROM t1_pkey AS a, t2_idx AS b
-> WHERE a.pkey = b.idx AND a.pkey = 2;
+----+-------------+---------+-------+---------------+---------+ ...
| id | select_type | table | type | possible_keys | key | ...
+----+-------------+---------+-------+---------------+---------+ ...
| 1 | SIMPLE | t1_pkey | const | PRIMARY | PRIMARY | ...
| 1 | SIMPLE | t2_idx | ref | idx | idx | ...
+----+-------------+---------+-------+---------------+---------+ ...
2 rows in set (0.00 sec)
以下、エクゼキュータの処理を説明します(【図.2-11】参照)。
図.2-11 例7のSELECT文の処理
|
-
(1)インデックスの読み込み
テーブルの検索方式は`const'と`ref'なので、両テーブルのインデックスがkey_bufferに読み込まれます。
ここで、次に示すWHERE句の簡略化が既に行われているので、テーブルt1_pkeyの列`pkey=2'に関するインデックスと、テーブルt2_idxの列`idx=2'に関するインデックスの、計2つのデータがkey_bufferに読み込まれます。
WHERE a.pkey = b.idx AND a.pkey = 2 → WHERE a.pkey = 2 AND b.idx = 2
インデックスのkey_bufferへの読み込みはブロック単位で、必要な部分のみ行われます。
この例では、テーブルt1_pkeyの列`pkey=2'、およびテーブルt2_idxの列`idx=2'が含まれるインデックスのブロック読み込みをリクエストします。key_bufferはキャッシュとしても機能するので、まだ読み込まれていない場合にのみ、実際にインデックスデータを読み込みます。
key_bufferへの読み込みリクエスト回数はステータス情報`Key_read_requests'に、実際に読み込んだ回数は`Key_reads'に加算されます。
(2)read_bufferへの読み込みと、join_bufferへの書き込み
テーブルt2_idxの列idxはインデックスなので、`idx=2'となるデータが複数存在する可能性があります。
エクゼキュータはテーブルt2_idxの列`idx=2'となる行を順次read_bufferに読み込み、その都度、テーブル`t1_pkey'の列`pkey=2'となる行との結合(join)結果をjoin_bufferに書き込みます。
インデックスを基に読み込んだテーブルデータ数(行数)はステータス情報`Handler_read_next'に加算されます。この例では列`idx=2'となる行が3行あるので、`+3'が加算されます。
なお、key_bufferに読み込んだインデックスのうち、実際に検索に使ったkey_bufferのデータ数はステータス情報`Handler_read_key'に加算されます。この例では`+2'が加算されます。なぜなら、WHERE句の条件から``t1_pkey.pkey = 2''と``t2_idx.idx = 2''の2つのデータ(ブロック)を読み込んで、検索を実行したからです。
●例8
次のSELECT文を分析します。[(例8)] SELECT * FROM t1_pkey AS a, t2_idx AS b WHERE a.pkey = b.idx AND a.pkey < 3;
このSELECT文の問い合わせ計画は次のとおりで、検索方式はテーブルt1_pkeyは`range'、テーブルt2_idxは`ref'が選択されることが分かります。
mysql> EXPLAIN SELECT * FROM t1_pkey AS a, t2_idx AS b
-> WHERE a.pkey = b.idx AND a.pkey < 3;
+----+-------------+---------+-------+---------------+---------+ ...
| id | select_type | table | type | possible_keys | key | ...
+----+-------------+---------+-------+---------------+---------+ ...
| 1 | SIMPLE | t1_pkey | range | PRIMARY | PRIMARY | ...
| 1 | SIMPLE | t2_idx | ref | idx | idx | ...
+----+-------------+---------+-------+---------------+---------+ ...
以下、エクゼキュータの処理を説明します(【図.2-12】参照)。
図.2-12 例8のSELECT文の処理
|
-
(1)インデックスの読み込み
テーブルの検索方式は`range'と`ref'なので、両テーブルのインデックスをkey_bufferに読み込みます。
テーブルt1_pkeyの列`pkey=1'のインデックスを読み込み、それに対応するテーブルt2_idxの列`idx=1'のインデックスも読み込みます(``pkey=idx''だから)。
(2)read_bufferへの読み込みと、join_bufferへの書き込み
key_buffer内のインデックスが参照しているデータをread_bufferに読み込み、次にテーブルt1_pkeyとテーブルt2_idxのデータを結合してjoin_bufferに書き込みます。
(3)同様の処理を行う
WHERE句の条件``t1_pkey.pkey < 3''を満たす間、同様の処理(1),(2)を繰り返します。
●例9
次のSELECT文を分析します。
[(例9)] SELECT * FROM t1_pkey AS a, t2_idx AS b WHERE a.pkey = b.idx AND b.data = 234;
このSELECT文の問い合わせ計画は次のとおりで、検索方式はテーブルt1_pkeyは`ALL'、テーブルt2_idxは`ref'が選択されます。
mysql> EXPLAIN SELECT * FROM t1_pkey AS a, t2_idx AS b
-> WHERE a.pkey = b.idx AND b.data = 234;
+----+-------------+---------+------+---------------+------+ ...
| id | select_type | table | type | possible_keys | key | ...
+----+-------------+---------+------+---------------+------+ ...
| 1 | SIMPLE | t1_pkey | ALL | PRIMARY | NULL | ...
| 1 | SIMPLE | t2_idx | ref | id | id | ...
+----+-------------+---------+------+---------------+------+ ...
2 rows in set (0.11 sec)
以下、エクゼキュータの処理を説明します(【図.2-13】参照)。
図.2-13 例9のSELECT文の処理
|
-
(1)テーブルデータとインデックスの読み込み
テーブルt1_pkeyの検索方式は`ALL'なのでインデックスを利用せず、1行毎に処理します。テーブルt1_pkeyの`pkey = 3'となるデータをread_rnd_bufferに読み込み、対応するテーブルt2_idxの`idx = 3'であるインデックスデータをkey_bfferに読み込みます。
(2)データの読み込みと値の検査
key_bufferに読み込んだインデックスが参照しているテーブルデータをread_bufferに読み込み、次に、テーブルt1_pkeyとテーブルt2_idxのデータを結合してjoin_bufferに書き込みます。
(3)同様の処理を繰り返す
テーブルt1_pkeyの全ての行について、同様の処理(1),(2)を行います。
[PREVIOUS][UP][NEXT]