MySQLにおける結合アルゴリズムの実装原理の分析

MySQL には、有名なネスト ループ結合という結合アルゴリズムが 1 つだけあります。他の多くのデータベースで提供されているハッシュ結合やソート マージ結合はありません。名前が示すように、ネスト ループ結合は実際には駆動テーブルの結果セットをループの基本データとして使用し、結果セットのデータをフィルタリング条件として使用して次のテーブルのデータを 1 つずつクエリし、結果をマージします。結合に 3 番目のテーブルが含まれる場合、最初の 2 つのテーブルの結合結果セットがループの基本データとして使用され、ループ クエリ条件を通じて 3 番目のテーブルでデータが再度クエリされる、というように繰り返します。

例と図を使って説明しましょう。後ほど、個人的なデータベース テスト環境での例 (MySQL が提供したものではなく、自分で設計したもの) を使用して、データベース内の 3 つのテーブルの結合クエリについて説明します。

注: ここでのコンテンツの一部は MySQL バージョン 5.1.18 以降にのみ反映されるため、このテストで使用されている MySQL バージョンは 5.1.26 です。

テーブル構造:

 1 sky@localhost : 例 11:09:32> show create table user_group\G
 2
 3 **************************** 1. 行 ****************************
 4
 5 テーブル: user_group
 6
 7 テーブルの作成: CREATE TABLE `user_group` (
 8
 9 `user_id` int(11) NULLではない、
 10
 11 `group_id` int(11) NOT NULL,
 12
 13 `user_type` int(11) NOT NULL,
 14
 15 `gmt_create` 日時 NOT NULL、
 16
 17 `gmt_modified` 日時がNULLではない、
 18
 19 `ステータス` varchar(16) NOT NULL,
 20
 21 キー `idx_user_group_uid` (`user_id`)
 22
 23) エンジン=InnoDB デフォルト文字セット=utf8
 24
 25 セット内 1 行 (0.00 秒)
 26
 27 sky@localhost : 例 11:10:32> show create table group_message\G
 28
 29 **************************** 1. 行 ****************************
 30
 31 テーブル: group_message
 32
 33 テーブルの作成: CREATE TABLE `group_message` (
 34
 35 `id` int(11) NOT NULL AUTO_INCREMENT,
 36
 37 `gmt_create` 日時 NOT NULL、
 38
 39 `gmt_modified` 日時 NOT NULL、
 40
 41 `group_id` int(11) NULLではない、
 42
 43 `user_id` int(11) NULLではない、
 44
 45 `author` varchar(32) NOT NULL,
 46
 47 `subject` varchar(128) NOT NULL,
 48
 49 主キー (`id`)、
 50
 51 キー `idx_group_message_author_subject` (`author`,`subject`(16))、
 52
 53 キー `idx_group_message_author` (`author`),
 54
 55 キー `idx_group_message_gid_uid` (`group_id`,`user_id`)
 56
 57 ) エンジン=InnoDB AUTO_INCREMENT=97 デフォルト文字セット=utf8
 58
 59 セット内 1 行 (0.00 秒)
 60
 61 sky@localhost : 例 11:10:43> show create table group_message_content\G
 62
 63 **************************** 1. 行 ****************************
 64
 65 テーブル: グループメッセージコンテンツ
66
 67 テーブルの作成: CREATE TABLE `group_message_content` (
 68
 69 `group_msg_id` int(11) NULLではない、
 70
 71 `content` テキストがNULLではない、
 72
 73 キー `group_message_content_msg_id` (`group_msg_id`)
 74
 75 ) エンジン=InnoDB デフォルト文字セット=utf8
 76
 77 セット内 1 行 (0.00 秒)

 1 m.subject msg_subject、c.content msg_contentを選択
2
 3 ユーザーグループ g、グループメッセージ m、グループメッセージコンテンツ c から
4
 5 ここで g.user_id = 1
 6
 7 かつ m.group_id = g.group_id
 8
 9 かつ c.group_msg_id = m.id

 1 sky@localhost : 例 11:17:04> explain select m.subject msg_subject, c.content msg_content
 2
 3 -> ユーザーグループ g、グループメッセージ m、グループメッセージコンテンツ c から
4
 5 -> g.user_id = 1 の場合
6
 7 -> かつ m.group_id = g.group_id
 8
 9 -> かつ c.group_msg_id = m.id\G
 10
 11 **************************** 1. 行 ****************************
 12
 13 id: 1
 14
 15 選択タイプ: シンプル
16
 17 表: g
 18
 19 タイプ: ref
 20
 21 個の可能なキー: user_group_gid_ind、user_group_uid_ind、user_group_gid_uid_ind
 22
 23 キー: user_group_uid_ind
 24
 25 キーの長さ: 4
 26
 27 参照: 定数
28
 29行: 2
 30
 31 追加:
 32
 33 ************************** 2. 行 ****************************
 34
 35 id: 1
 36
 37 選択タイプ: シンプル
38
 39 表: m
 40
 41 タイプ: ref
 42
 43 個の可能なキー: PRIMARY、idx_group_message_gid_uid
 44
 45 キー: idx_group_message_gid_uid
 46
 47 キーの長さ: 4
 48
 49 参照: example.g.group_id
 50
 51 行: 3
 52
 53 追加:
 54
 55 ************************** 3. 行 ****************************
 56
 57 id: 1
 58
 59 選択タイプ: シンプル
60
 61 表: c
 62
 63 タイプ: ref
 64
 65 個の可能なキー: idx_group_message_content_msg_id
 66
 67 キー: idx_group_message_content_msg_id
 68
 69 キーの長さ: 4
 70
 71 参照: example.m.id
 72
 73 行: 2
 74
 75 追加:

MySQL クエリ オプティマイザーが駆動テーブルとして user_group を選択していることがわかります。最初に、渡された条件 user_id を使用して、テーブルのインデックス user_group_uid_ind を介して const 条件のインデックス参照検索を実行します。次に、user_group テーブルからフィルターされた結果セットの group_id フィールドをクエリ条件として使用して、group_message でループ クエリを実行します。次に、user_group テーブルと group_message テーブルの結果セットの group_message id を条件として使用して、ループ クエリで group_message_content の group_msg_id と比較し、最終結果を取得します。特別なことは何もありません。後者は、前のものの結果セットを条件として参照します。実装プロセスは次の図で表すことができます。

次に、group_message_content を調整して idx_group_message_content_msg_id インデックスを削除し、その効果を確認します。

 1 sky@localhost : 例 11:25:36> group_message_content のインデックス idx_group_message_content_msg_id を削除します。
 2
 3 クエリは正常、96 行が影響を受けました (0.11 秒)
 4
 5 sky@localhost : 例 10:21:06> 説明
6
 7 -> m.subject msg_subject、c.content msg_content を選択
8
 9 -> ユーザーグループ g、グループメッセージ m、グループメッセージコンテンツ c から
10
 11 -> g.user_id = 1 の場合
12
 13 -> かつ m.group_id = g.group_id
 14
 15 -> かつ c.group_msg_id = m.id\G
 16
 17 ************************** 1. 行 ****************************
 18
 19 id: 1
 20
 21 選択タイプ: シンプル
22
 23 表: g
 24
 25 タイプ: ref
 26
 27 個の可能なキー: idx_user_group_uid
 28
 29 キー: idx_user_group_uid
 30
 31 キーの長さ: 4
 32
 33 参照: 定数
34
 35行: 2
 36
 37 追加:
 38
 39 **************************** 2. 行 ****************************
 40
 41 id: 1
 42
 43 選択タイプ: シンプル
44
 45 テーブル: m
 46
 47 タイプ: ref
 48
 49 個の可能なキー: PRIMARY、idx_group_message_gid_uid
 50
 51 キー: idx_group_message_gid_uid
 52
 53 キーの長さ: 4
 54
 55 参照: example.g.group_id
 56
 57 行: 3
 58
 59 追加:
 60
 61 **************************** 3. 行 ****************************
 62
 63 id: 1
 64
 65 選択タイプ: シンプル
66
 67 表: c
 68
 69 タイプ: ALL
 70
 71 可能なキー: NULL
 72
 73 キー: NULL
 74
 75 キー長さ: NULL
 76
 77 参照: NULL
 78
 79 行: 96
 80
 81 追加: where の使用; join buffer の使用

group_message_content テーブルへのアクセスが ref から ALL に変わっただけでなく、最後の行の Extra 情報も nothing から Using where; Using join buffer に変わったことがわかります。つまり、ref から ALL に変わったのは、使用できるインデックスがないため、当然フル テーブル スキャンが必要になるためであることは簡単に理解できます。また、Using where は、フル テーブル スキャンの後、取得する必要のあるコンテンツ フィールドは、where を使用してテーブル内のデータをフィルタリングすることによってのみ取得できるためでもあります。しかし、後に表示される Using join buffer とは何でしょうか。

MySQL には設定可能なパラメータ join_buffer_size があることはわかっています。ここでは、実際にこのパラメータによって設定されたバッファ領域を使用します。では、なぜ以前の実行計画では使用されなかったのでしょうか?

実際、Join Buffer は、Join タイプが ALL (例のように)、index、rang、または index_merge の場合にのみ使用できます。したがって、group_message_content テーブルの group_msg_id フィールドのインデックスを削除する前は、Join が ref タイプであるため、実行プランで Join Buffer が使用されることはありません。

Join Buffer を使用した後、次の図を使用して Join 完了プロセスを表すことができます。