MySQLの基礎

MySQL の実行フロー#

MySQL のアーキテクチャは 2 層に分かれています：サーバー層とストレージエンジン層、

サーバー層は接続の確立、SQL の解析と実行を担当します。MySQL のほとんどのコア機能モジュールはここで実装されており、主にコネクタ、クエリキャッシュ、パーサー、プリプロセッサ、オプティマイザ、エグゼキュータなどが含まれます。また、すべての組み込み関数（日時、時間、数学、暗号関数など）やすべてのストレージエンジンを超えた機能（ストアドプロシージャ、トリガー、ビューなど）もサーバー層で実装されています。
ストレージエンジン層はデータの保存と取得を担当します。InnoDB、MyISAM、Memory などの複数のストレージエンジンをサポートしており、異なるストレージエンジンは共通のサーバー層を使用します。現在最も一般的に使用されているストレージエンジンは InnoDB で、MySQL 5.5 以降、InnoDB は MySQL のデフォルトストレージエンジンとなりました。一般に言われるインデックスデータ構造は、ストレージエンジン層によって実装されており、異なるストレージエンジンがサポートするインデックスタイプは異なります。たとえば、InnoDB がサポートするインデックスタイプは B + ツリーであり、デフォルトで使用されます。つまり、データベース内で作成された主キーインデックスと二次インデックスはデフォルトで B + ツリーインデックスを使用します。

ステップ 1：コネクタ#

コネクタの作業：

クライアントと TCP の 3 ウェイハンドシェイクを行い接続を確立する；
クライアントのユーザー名とパスワードを検証し、ユーザー名またはパスワードが間違っている場合はエラーを報告する；
ユーザー名とパスワードが正しい場合、そのユーザーの権限を読み取り、その後の権限ロジックの判断はこの時点で読み取った権限に基づいて行われる；

MySQL サービスに接続しているクライアントの数を確認するにはどうすればよいですか？

show processlist を実行します。

アイドル接続は常に占有されますか？

wait_timeout パラメータによって制御されており、デフォルト値は 8 時間（28880 秒）です。アイドル接続がこの時間を超えると、コネクタは自動的に切断します。

MySQL の接続数には制限がありますか？

max_connections パラメータによって制御されます。

長い接続がメモリを占有する問題をどう解決しますか？

定期的に長い接続を切断する
クライアントが積極的に接続をリセットする
MySQL 5.7 バージョンでは mysql_reset_connection () 関数のインターフェースが実装されており、クライアントが大きな操作を実行した後、コード内で mysql_reset_connection 関数を呼び出して接続をリセットし、メモリを解放する効果を得ます。このプロセスでは再接続や権限の再検証は必要ありませんが、接続は作成されたばかりの状態に戻ります。

ステップ 2：クエリキャッシュ#

クライアントが MySQL サービスに SQL 文を送信すると、MySQL サービスは SQL 文を受け取った後、最初のフィールドを解析し、どのタイプの文であるかを確認します。
もし SQL がクエリ文（select 文）であれば、MySQL はまずクエリキャッシュ（Query Cache）を確認し、以前にこのコマンドが実行されたかどうかを調べます。このクエリキャッシュはキー - バリュー形式でメモリに保存されており、キーは SQL クエリ文、バリューは SQL 文のクエリ結果です。
もしクエリ文がクエリキャッシュにヒットすれば、バリューをクライアントに直接返します。もしクエリ文がクエリキャッシュにヒットしなければ、次に進んで実行を続け、実行が完了した後、クエリ結果がクエリキャッシュに保存されます。
しかし、更新が頻繁なテーブルに対しては、クエリキャッシュのヒット率は非常に低くなります。なぜなら、テーブルに更新操作があると、そのテーブルのクエリキャッシュはクリアされるからです。したがって、MySQL 8.0 バージョンではクエリキャッシュが削除されました。

ステップ 3：SQL の解析#

正式に SQL クエリ文を実行する前に、MySQL はまず SQL 文を解析します。この作業は「パーサー」によって行われます。

パーサー#

パーサーは以下の 2 つの作業を行います。
最初の作業は、字句解析です。MySQL は入力された文字列に基づいてキーワードを識別し、SQL 構文ツリーを構築します。これにより、後のモジュールが SQL のタイプ、テーブル名、フィールド名、where 条件などを取得しやすくなります。
2 つ目の作業は、構文解析です。字句解析の結果に基づいて、構文解析器は構文ルールに従って、入力された SQL 文が MySQL の構文を満たしているかどうかを判断します。
もし入力された SQL 文の構文が正しくなければ、パーサーの段階でエラーが報告されます。
ただし注意が必要なのは、テーブルが存在しない場合やフィールドが存在しない場合は、パーサー内で処理されるわけではありません。

ステップ 4：SQL の実行#

プリプロセッサ#

まず、プリプロセッサの段階で何が行われるかを説明します。

SQL クエリ文内のテーブルやフィールドが存在するかどうかを確認します；
select *の*記号を、テーブル上のすべての列に展開します；

オプティマイザ#

プリプロセッサ段階を経た後、SQL クエリ文の実行計画を策定する必要があります。この作業は「オプティマイザ」によって行われます。
オプティマイザは SQL クエリ文の実行プランを確定することを主な役割としています。たとえば、テーブル内に複数のインデックスがある場合、オプティマイザはクエリコストを考慮して、どのインデックスを使用するかを決定します。

エグゼキュータ#

オプティマイザを経て実行プランが確定した後、MySQL は実際に文を実行し始めます。この作業は「エグゼキュータ」によって行われます。実行中、エグゼキュータはストレージエンジンと対話し、対話はレコード単位で行われます。

主キーインデックスクエリ#

select * from product where id = 1;

このクエリ文のクエリ条件は主キーインデックスを使用しており、等値クエリです。同時に主キー id は一意であり、同じ id のレコードは存在しないため、オプティマイザはアクセスタイプを const としてクエリを実行することを決定します。つまり、主キーインデックスを使用して 1 つのレコードをクエリします。この場合、エグゼキュータとストレージエンジンの実行フローは次のようになります：

エグゼキュータが最初のクエリを実行すると、read_first_record 関数ポインタが指す関数が呼び出されます。オプティマイザが選択したアクセスタイプは const であり、この関数ポインタは InnoDB エンジンのインデックスクエリインターフェースを指します。条件 id = 1 をストレージエンジンに渡し、ストレージエンジンは条件に合致する最初のレコードを特定します。
ストレージエンジンは主キーインデックスの B + ツリー構造を通じて id = 1 の最初のレコードを特定します。もしレコードが存在しない場合、エグゼキュータにレコードが見つからないエラーを報告し、クエリは終了します。もしレコードが存在する場合、レコードはエグゼキュータに返されます；
エグゼキュータはストレージエンジンからレコードを読み取った後、そのレコードがクエリ条件に合致するかどうかを判断します。合致すればクライアントに送信し、合致しなければそのレコードをスキップします。
エグゼキュータのクエリプロセスは while ループであるため、もう一度クエリを実行しますが、今回は最初のクエリではないため、read_record 関数ポインタが指す関数が呼び出されます。オプティマイザが選択したアクセスタイプは const であり、この関数ポインタは常に - 1 を返す関数を指します。したがって、この関数を呼び出すと、エグゼキュータはループを終了し、クエリが終了します。

全表スキャン#

select * from product where name = 'iphone';

このクエリ文のクエリ条件はインデックスを使用していないため、オプティマイザはアクセスタイプを ALL としてクエリを実行することを決定します。つまり、全表スキャンの方法でクエリを実行します。この場合、エグゼキュータとストレージエンジンの実行フローは次のようになります：

エグゼキュータが最初のクエリを実行すると、read_first_record 関数ポインタが指す関数が呼び出されます。オプティマイザが選択したアクセスタイプは all であり、この関数ポインタは InnoDB エンジンの全スキャンインターフェースを指します。ストレージエンジンにテーブル内の最初のレコードを読み取るように指示します；
エグゼキュータは読み取ったレコードの name が iphone であるかどうかを判断します。もしそうでなければスキップし、そうであればレコードをクライアントに送信します（そうです、サーバー層はストレージエンジンからレコードを 1 つ読み取るたびにクライアントに送信します。クライアントが表示する際にすべてのレコードを直接表示するのは、クライアントがクエリ文の実行が完了した後にすべてのレコードを表示するからです）。
エグゼキュータのクエリプロセスは while ループであるため、もう一度クエリを実行します。read_record 関数ポインタが指す関数が呼び出されます。オプティマイザが選択したアクセスタイプは all であり、read_record 関数ポインタは InnoDB エンジンの全スキャンインターフェースを指します。したがって、ストレージエンジン層に前回のレコードの次のレコードを読み取るように要求します。ストレージエンジンは次のレコードを取得し、エグゼキュータ（サーバー層）に返します。エグゼキュータは条件を判断し、クエリ条件に合致しない場合はそのレコードをスキップし、合致する場合はクライアントに送信します；
上記のプロセスを繰り返し、ストレージエンジンがテーブル内のすべてのレコードを読み終え、エグゼキュータ（サーバー層）に読み終えた情報を返します；
エグゼキュータはストレージエンジンからのクエリ完了情報を受け取り、ループを終了し、クエリを停止します。

インデックスプッシュダウン#

インデックスプッシュダウンは、二次インデックスのクエリ時の回表操作を減少させ、クエリの効率を向上させることができます。なぜなら、サーバー層が担当する一部の作業をストレージエンジン層に委任するからです。

select * from t_user where age > 20 and reward = 100000;

結合インデックスが範囲クエリ（>、<）に遭遇すると、マッチングが停止します。つまり、age フィールドは結合インデックスを使用できますが、reward フィールドはインデックスを利用できません。
インデックスプッシュダウンを使用しない場合（MySQL 5.6 以前のバージョン）、エグゼキュータとストレージエンジンの実行フローは次のようになります：

サーバー層はまずストレージエンジンのインターフェースを呼び出し、クエリ条件を満たす最初の二次インデックスレコードを特定します。つまり、age > 20 の最初のレコードを特定します；
ストレージエンジンは二次インデックスの B + ツリーを使用してこのレコードを迅速に特定し、主キー値を取得します。そして回表操作を実行します。完全なレコードをサーバー層に返します；
サーバー層はそのレコードの reward が 100000 に等しいかどうかを判断します。成立すればクライアントに送信し、そうでなければそのレコードをスキップします；
次に、ストレージエンジンに次のレコードを要求し、ストレージエンジンは二次インデックスでレコードを特定し、主キー値を取得し、回表操作を実行して完全なレコードをサーバー層に返します；
このプロセスを繰り返し、ストレージエンジンがテーブル内のすべてのレコードを読み終えます。

インデックスプッシュダウンを使用しない場合、二次インデックスレコードをクエリするたびに回表操作が必要であり、その後サーバーがそのレコードの reward が 100000 に等しいかどうかを判断します。
インデックスプッシュダウンを使用すると、reward が 100000 に等しいかどうかの判断がストレージエンジン層に委任されます。プロセスは次のようになります：

サーバー層はまずストレージエンジンのインターフェースを呼び出し、クエリ条件を満たす最初の二次インデックスレコードを特定します。つまり、age > 20 の最初のレコードを特定します；
ストレージエンジンは二次インデックスを特定した後、回表操作を実行せず、まずそのインデックスに含まれる列（reward 列）の条件（reward が 100000 に等しいかどうか）を判断します。条件が成立しない場合、その二次インデックスをスキップします。条件が成立する場合、回表操作を実行し、完全なレコードをサーバー層に返します。
サーバー層は他のクエリ条件（このクエリには他の条件はありません）が成立するかどうかを判断します。成立すればクライアントに送信し、そうでなければそのレコードをスキップし、ストレージエンジンに次のレコードを要求します。
このプロセスを繰り返し、ストレージエンジンがテーブル内のすべてのレコードを読み終えます。

インデックスプッシュダウンを使用すると、reward 列は結合インデックスを使用できませんが、結合インデックス（age、reward）に含まれているため、reward = 100000 を満たすレコードをストレージエンジンがフィルタリングした後に回表操作を実行して完全なレコードを取得します。インデックスプッシュダウンを使用しない場合に比べて、多くの回表操作を節約できます。
実行計画の Extr 部分に「Using index condition」と表示されると、インデックスプッシュダウンが使用されていることを示します。

MySQL のレコードはどのように保存されるのか？#

MySQL のデータはどのファイルに保存されているのか？#

MySQL のデータはすべてディスクに保存されており、具体的にはどのファイルに保存されているか、InnoDB ストレージエンジンを展開して議論します。
3 つのファイルがあり、これらのファイルはそれぞれ次のことを表しています：

db.opt：現在のデータベースのデフォルト文字セットと文字検証ルールを保存します。
t_order.frm：t_order のテーブル構造がこのファイルに保存されます。MySQL でテーブルを作成すると、必ず.frm ファイルが生成され、このファイルは各テーブルのメタデータ情報を保存するためのもので、主にテーブル構造の定義が含まれています。
t_order.ibd：t_order のテーブルデータがこのファイルに保存されます。テーブルデータは共有テーブルスペースファイル（ファイル名：ibdata1）に存在することも、専用テーブルスペースファイル（ファイル名：テーブル名.ibd）に保存されることもあります。この動作は innodb_file_per_table パラメータによって制御され、innodb_file_per_table パラメータが 1 に設定されている場合、ストレージされたデータ、インデックスなどの情報は専用テーブルスペースに個別に保存されます。MySQL 5.6.6 バージョン以降、このデフォルト値は 1 です。したがって、このバージョン以降、MySQL の各テーブルのデータは独立した.ibd ファイルに保存されます。

これで、データベーステーブルのデータが「テーブル名.ibd」のファイルに保存されていることがわかりました。このファイルは専用テーブルスペースファイルとも呼ばれます。

テーブルスペースファイルの構造はどのようになっているのか？#

テーブルスペースはセグメント（segment）、エクステント（extent）、ページ（page）、行（row）で構成されており、InnoDB ストレージエンジンの論理ストレージ構造は大まかに次の図のようになります：

行（row）#

データベーステーブルのレコードは行（row）単位で保存されており、各行のレコードは異なる行形式に応じて異なるストレージ構造を持っています。後で詳しく説明します。

ページ（page）#

レコードは行単位で保存されますが、データベースの読み取りは「行」単位では行われません。そうでなければ、一度の読み取り（つまり、一度の I/O 操作）で 1 行のデータしか処理できず、効率が非常に低くなります。
したがって、InnoDB のデータは「ページ」単位で読み書きされます。つまり、レコードを読み取る必要があるとき、行レコードをディスクから読み取るのではなく、ページ単位で全体をメモリに読み込みます。
デフォルトで各ページのサイズは 16KB であり、最大で 16KB の連続ストレージスペースを保証します。
ページは InnoDB ストレージエンジンのディスク管理の最小単位であり、データベースは毎回 16KB 単位で読み書きされます。つまり、ディスクからメモリに最低でも 16K の内容を読み取り、メモリからディスクに最低でも 16K の内容をフラッシュします。
ページのタイプは多くあり、一般的なものにはデータページ、undo ログページ、オーバーフローページなどがあります。データベーステーブルの行レコードは「データページ」で管理されています。

エクステント（extent）#

InnoDB ストレージエンジンは B + ツリーを使用してデータを組織しています。
B + ツリーの葉ノード層は双方向リストで接続されています。もしページ単位でストレージスペースを割り当てると、リスト内の隣接する 2 つのページの物理位置は連続していない可能性があり、非常に離れている可能性があります。これにより、ディスククエリ時に大量のランダム I/O が発生し、ランダム I/O は非常に遅いです。
この問題を解決するのは簡単です。リスト内の隣接するページの物理位置も隣接させることで、順次 I/O を使用できるようになります。これにより、範囲クエリ（葉ノードをスキャンする際）のパフォーマンスが向上します。
具体的にはどう解決するのでしょうか？
テーブル内のデータ量が大きい場合、特定のインデックスにスペースを割り当てる際、ページ単位ではなくエクステント（extent）単位で割り当てます。各エクステントのサイズは 1MB であり、16KB のページの場合、連続する 64 ページが 1 つのエクステントに割り当てられます。これにより、リスト内の隣接するページの物理位置も隣接し、順次 I/O を使用できるようになります。

セグメント（segment）#

テーブルスペースは各セグメント（segment）で構成されており、セグメントは複数のエクステント（extent）で構成されています。セグメントは一般にデータセグメント、インデックスセグメント、ロールバックセグメントなどに分かれます。

インデックスセグメント：B + ツリーの非葉ノードのエクステントの集合を保存します；
データセグメント：B + ツリーの葉ノードのエクステントの集合を保存します；
ロールバックセグメント：ロールバックデータのエクステントの集合を保存します。以前にトランザクションの隔離について説明したとき、MVCC はロールバックセグメントを利用して多バージョンのデータをクエリすることを実現しました。

InnoDB の行形式にはどのようなものがありますか？#

InnoDB は 4 種類の行形式を提供しており、それぞれ Redundant、Compact、Dynamic、Compressed 行形式です。
Redundant 行形式は現在ほとんど使用されておらず、ここでは Compact 行形式を重点的に紹介します。Dynamic と Compressed の 2 つの行形式は Compact に非常に似ています。

COMPACT 行形式はどのようなものですか？#

完全なレコードは「レコードの追加情報」と「レコードの実データ」の 2 つの部分に分かれています。

レコードの追加情報#

レコードの追加情報は 3 つの部分から構成されています：可変長フィールドの長さリスト、NULL 値リスト、レコードヘッダー情報。

可変長フィールドの長さリスト

varchar (n) と char (n) の違いは皆さんもよくご存知でしょう。char は固定長で、varchar は可変長です。可変長フィールドが実際に保存するデータの長さ（サイズ）は固定されていません。
したがって、データを保存する際には、データが占めるサイズも保存する必要があります。それを「可変長フィールドの長さリスト」に保存し、データを読み取る際にはこの「可変長フィールドの長さリスト」に基づいて対応する長さのデータを読み取ります。他の TEXT、BLOB などの可変長フィールドも同様に実装されています。

なぜ「可変長フィールドの長さリスト」の情報は逆順に保存されるのですか？

「レコードヘッダー情報」内の次のレコードを指すポインタは、次のレコードの「レコードヘッダー情報」と「実データ」の間の位置を指します。このようにすることで、左に読めばレコードヘッダー情報、右に読めば実データとなり、便利です。
同様の理由で、NULL 値リストの情報も逆順に保存する必要があります。

各データベーステーブルの行形式には「可変長フィールドのバイト数リスト」がありますか？

データテーブルに可変長フィールドがない場合、たとえばすべてが int 型のフィールドである場合、この時点でテーブルの行形式には「可変長フィールドの長さリスト」は存在しません。

varchar (n) の n の最大値はどれくらいですか？

MySQL は、TEXT、BLOB などの大きなオブジェクト型を除いて、他のすべての列（隠し列やレコードヘッダー情報を除く）が占めるバイト長の合計が 65535 バイトを超えてはならないと規定しています。
varchar (n) フィールドタイプの n は、最大で保存できる文字の数を表しており、バイトサイズではありません。varchar (n) が最大で許容されるバイト数を計算するには、データベーステーブルの文字セットを考慮する必要があります。文字セットは、1 文字が何バイトを占めるかを示します。たとえば、ascii 文字セットでは、1 文字が 1 バイトを占めるため、varchar (100) は最大で 100 バイトのデータを保存できることを意味します。
複数のフィールドがある場合、すべてのフィールドの長さ + 可変長フィールドのバイト数リストが占めるバイト数 + NULL 値リストが占めるバイト数 <= 65535 であることを保証する必要があります。

NULL 値リスト

NULL 値を許可する列が存在する場合、各列に対応する 1 ビットのバイナリがあり、バイナリは列の順序に逆順に配置されます。

バイナリの値が 1 の場合、その列の値は NULL です。
バイナリの値が 0 の場合、その列の値は NULL ではありません。

さらに、NULL 値リストは整数バイト数のビットで表す必要があります（1 バイト 8 ビット）。使用するバイナリビット数が整数バイト数に満たない場合、高位ビットに 0 を補います。

各データベーステーブルの行形式には「NULL 値リスト」がありますか？

データテーブルのフィールドがすべて NOT NULL として定義されている場合、この時点でテーブルの行形式には NULL 値リストは存在しません。
したがって、データベーステーブルを設計する際には、通常フィールドを NOT NULL として設定することが推奨されます。これにより、少なくとも 1 バイトのスペースを節約できます（NULL 値リストは少なくとも 1 バイトのスペースを占めます）。

「NULL 値リスト」は固定で 1 バイトのスペースですか？もしそうなら、1 つのレコードに 9 つのフィールド値がすべて NULL の場合、どうやって表現しますか？

「NULL 値リスト」のスペースは固定で 1 バイトではありません。
1 つのレコードに 9 つのフィールド値がすべて NULL の場合、2 バイトの「NULL 値リスト」が作成されます。このように続きます。

レコードヘッダー情報

レコードヘッダー情報には多くの内容が含まれており、すべてを列挙することはできませんが、いくつかの重要な点を挙げます：

delete_mask：このデータが削除されているかどうかを示します。これにより、delete でレコードを削除する際、実際にはレコードを削除するのではなく、このレコードの delete_mask を 1 としてマークすることがわかります。
next_record：次のレコードの位置。これにより、レコード間はリンクリストで構成されていることがわかります。前述のように、次のレコードの「レコードヘッダー情報」と「実データ」の間の位置を指します。このようにすることで、左に読めばレコードヘッダー情報、右に読めば実データとなり、便利です。
record_type：現在のレコードのタイプを示します。0 は通常のレコード、1 は B + ツリーの非葉ノードレコード、2 は最小レコード、3 は最大レコードを示します。

レコードの実データ#

レコードの実データ部分には、定義したフィールドの他に 3 つの隠しフィールドがあります。それぞれ row_id、trx_id、roll_pointer です。これらの 3 つのフィールドが何であるかを見てみましょう。

row_id：テーブルを作成する際に主キーまたは一意制約列を指定した場合、row_id の隠しフィールドは存在しません。主キーも一意制約も指定されていない場合、InnoDB はレコードに row_id の隠しフィールドを追加します。row_id は必須ではなく、6 バイトを占めます。
trx_id：トランザクション ID で、このデータがどのトランザクションによって生成されたかを示します。trx_id は必須で、6 バイトを占めます。
roll_pointer：このレコードの前のバージョンのポインタです。roll_pointer は必須で、7 バイトを占めます。

trx_id と roll_pointer は MVCC メカニズムに関連しています。

行がオーバーフローした場合、MySQL はどのように処理するのか？#

MySQL におけるディスクとメモリの相互作用の基本単位はページであり、1 ページのサイズは一般に 16KB、つまり 16384 バイトです。一方、varchar (n) 型の列は最大で 65532 バイトを保存でき、TEXT や BLOB などの大きなオブジェクトはさらに多くのデータを保存できる可能性があります。このため、1 ページに 1 つのレコードを保存できない場合があります。この場合、行のオーバーフローが発生し、余分なデータは別の「オーバーフローページ」に保存されます。
1 つのデータページに 1 つのレコードを保存できない場合、InnoDB ストレージエンジンは自動的にオーバーフローしたデータを「オーバーフローページ」に保存します。通常、InnoDB のデータは「データページ」に保存されますが、行がオーバーフローした場合、オーバーフローしたデータは「オーバーフローページ」に保存されます。
行がオーバーフローした場合、レコードの実データ部分にはその列の一部のデータのみが保存され、残りのデータは「オーバーフローページ」に保存されます。そして、実データ部分にはオーバーフローページのアドレスを指す 20 バイトが保存され、残りのデータがどのページにあるかを見つけることができます。大まかには次の図のようになります。

上記は Compact 行形式が行オーバーフローした場合の処理です。
Compressed と Dynamic の 2 つの行形式は Compact に非常に似ていますが、行オーバーフローしたデータの処理にはいくつかの違いがあります。
これらの 2 つの形式は完全な行オーバーフロー方式を採用しており、レコードの実データ部分にはその列の一部のデータは保存されず、オーバーフローページを指す 20 バイトのポインタのみが保存されます。実際のデータはすべてオーバーフローページに保存され、次のように見えます：