最近、RDMBSの通信プロトコルに興味があり、代表的なPostgreSQL・MySQLの通信プロトコルの調査をしています。今回は、とくに興味があった問い合わせ(Query)とその応答の通信プロトコルについて整理してみます。
取っ掛りとなる調査ではありますが、その通信プロトコル仕様から、PostgreSQL・MySQLそれぞれの設計思想的なところも垣間見れ、興味深い調査となりました。端的に言えば、PostgreSQLは高速かつ合成的で合理的、対するMySQLの仕様は厳密で冗長的な設計の印象を持ちました。
RDBMSの質問(Query)操作とは
RDBMSへ対する操作は、質問(query)と更新(update)に大別されます。RDBMSの基礎となるリレーショナル代数は、前者の質問操作を対象とするものであり、挿入(insert)や削除(delete)などは後者の更新処理に含まれます[1]。
リレーショナル代数の質問は再帰性(recursiveness)があり、その結果は再びリレーショナルとして表現されます。この結果を表すリレーションを、リレーショナル代数では結果リレーション(result relation)、SQLでは導出表(derived table)と呼ばれています[1]。
すなわち、質問(query)操作についてはリレーショナル代数の結果リレーション相当の情報が含まれる必要があります。
通信プロトコル
PostgreSQL・MySQLともに通信プロトコルの基本となる通信パケットが定義されており、通信パケットに含まれるデータについても基本となるデータ型が定義されています。
通信パケット
通信パケットは、PostgreSQL・MySQLともに通信プロトコルの基本となるパケット形式で、全ての通信プロトコルは、このパケット仕様に準じています。
PostgreSQL・MySQLともに通信パケットのサイズを示すメッセージ(ペイロード)長が含まれています。以下の表に示す通り、相違点としては、PostgreSQLがメッセージ種別からパケットが開始されるのに対して、MySQLのメッセージ種別はペイロード部に含まれています。
| 項目 | PostgreSQL | MySQL |
|---|---|---|
| メッセージ種別 | byte<1> | - |
| ペイロード長 | int<4> | int<3> |
| シーケンス番号 | - | int<1> |
| ペイロード | string<var> or binary | string |
また、MySQLではシーケンス番号がパケットふくまれており、通信パケットに含まれるコマンド数によりインクリメントされます。また、MySQLの最大ペイロード長は16MB(0xFFFFFF)とPostgreSQLと比べて少なめですが、その最大長を超える場合には分割されて送信されます。
参考資料
- Messaging Overview - PostgreSQL 9.3.25 Documentation Frontend/Backend Protocol
- MySQL Packets - MySQL Internals Manual / MySQL Client/Server Protocol
通信データ型
通信データ型は、前述の通信パケットの含まれまれるデータ型を定義しているものです。
PostgreSQL・MySQLともに、いくつかの基本データ型が定義されており、両者と整数および文字列型のメッセージデータを基本としています。
| 項目 | PostgreSQL | MySQL |
|---|---|---|
| 固定整数型 | Intn(i) | int |
| 固定整数配列型 | Intn[k] | - |
| 可変整数型 | - | int<lenenc> |
| 固定文字列 | String(s) | string<fix> |
| 可変文字列 | - | string<NUL>, string<EOF>、string<lenenc>など |
| バイト列 | Byten(c) | - |
PostgreSQLでは整数の配列型(Intn[k])が定義されており、MySQLは整数および文字列型ともに自動可変型のデータ型(int<lenenc>, string<lenenc>)が定義されているのが特徴的です。
なお、整数型の表現形式については、PostgreSQLのネットワークバイトオーダー(ビッグエンディアン)で、MySQLはリトルエンディアンとなります。
参考資料
- Message Data Types - PostgreSQL 9.3.25 Documentation Frontend/Backend Protocol
- Basic Data Types - MySQL Internals Manual / MySQL Client/Server Protocol
SQLクエリーの要求および応答
今回は、通信プロトコルのうちSQLクエリーの要求および応答をピックアップして整理しています。なお、今回紹介する以外の通信プロトコルとしては、認証系やレプリケーションなどの内部通信に関するものがあります。
問い合わせ(Query)パケット
SQLの問い合わせについては、PostgreSQL・MySQLともに文字列型をベースとした通信パケット仕様です。単純なクエリーについては、問い合わせ(Query)文字列が、そのままクライアントから送信されます。PostgreSQL・MySQLサーバーへ単純クエリーの送信パケットを比較したものが、以下の表となります。
| 項目 | PostgreSQL | - | MySQL | - |
|---|---|---|---|---|
| - | データ (例) | データ型 | データ (例) | データ型 |
| メッセージ種別 | Q (0x51) | byte<1> | - | - |
| ペイロード長 | クエリー文字列長 | int<4> | 1 + クエリー文字列長 + 1 | int<3> |
| シーケンス番号 | - | - | 0 | int<1> |
| ペイロード | String(s) | String(s) | COM_QUERY (0x03) | int<1> |
| - | - | - | クエリー文字列 | string<EOF> |
単純なクエリー送信については、通信パケット形式の相違はありますが、その内容についてはPostgreSQL・MySQLともに差はありません。いずれも、クエリー文字列を、テキスト文字列として送信する形式となります。
参考資料
- Message Formats - PostgreSQL 9.3.25 Documentation Frontend/Backend Protocol
- COM_QUERY - MySQL Internals Manual / MySQL Client/Server Protocol
応答(Response)パケット
SQLの応答処理については、PostgreSQL・MySQLともに前述の結果リレーション(result relation)、SQLでは導出表(derived table)としての応答が求められており、結論としては、いずれも応答の通信パケットにも「カラム(フィールド)情報 + (行単位データ × データ数)」が含まれます。
問い合わせパケットとおなじく、応答パケットの要素としては大差はありません。ただし、前述の基本となる通信パケット仕様の違いにより、問い合わせパケットに比べると応答パッケトの差異は大きくなります。まずは、PostgreSQLと比較して厳密に定義され、その仕様も読みやすいMySQLプロトコルから説明します。
MySQL応答 - 個別の応答定義
MySQLの通信パケット仕様は、前述のペイロード部がメッセージ毎に定義されています。そのペイロード部には、メッセージ毎にその種別や、含まれるデータの種類が、その順番や出現数を含めて定義されています。公式仕様としては、厳密かつ理解しやすいのが特徴的です。
今回の対象となる結果リレーション(result relation)応答を例とすれば、以下のMySQLの公式ドキュメントの図にもある通りに、最初にカラム情報(スキーマ名、テーブル名、カラム型など)が送信され、つづいて応答結果としての行(Resultset)が送信される形式として、厳密に定義されています。
また、複数クエリーの応答については、Multi-Resultsetとして定義されており、ペイロード最後の状態(status)フラグにより確認できます。
PostgreSQL応答 - 合成的な応答定義
対するPostgreSQLについては、MySQLと比較すると、そのメッセージは合成的です。PostgreSQLには、通信パケットの先頭にメッセージ種別が定義されていますが、MySQLのようにメッセージ毎に詳細な定義がある形式ではなく、単一目的のメッセージを複数組み合わせることにより、メッセージを構成する形式をとります。
今回の対象となる結果リレーション(result relation)応答を例とすれば、その組み合わされる応答のパケットに「行情報(RowDescription)」「行単位データ(DataRow)」が含まれる形式となります。その行情報にはMySQLの応答パケットのカラム情報とおなじく、相当するフィールド名やフィールド型の情報が含まれています。
ただし、最新のMySQLプロトコル仕様(CLIENT_PROTOCOL_41)に比べると、フィールドの元となるテーブルやカラムが数値で返されたり、エイリアス名などの情報がないなど、若干簡素な仕様なため、クライアント(ドライバ)レベルの実装では、送信情報を参照するなどの、実装上の工数が必要な印象です。
参考資料
- Message Formats - PostgreSQL 9.3.25 Documentation Frontend/Backend Protocol
- COM_QUERY Response - MySQL Internals Manual / MySQL Client/Server Protocol
- ProtocolText::Resultset - MySQL Internals Manual / MySQL Client/Server Protocol
- Multi-Resultset - MySQL Internals Manual / MySQL Client/Server Protocol
最後に
今回は、興味があったRDBMSの通信プロトコルを、PostgreSQLとMySQLの問い合わせ(Query)とその応答を中心に整理してみました。問い合わせ応答に求められるSQLの導出表(derived table)については、いずれも「カラム(フィールド)情報 + (行単位データ × データ数)」の組み合わせから成る結果リレーション(result relation)に壮とする応答でした。
両者の通信プロトコルの印象ですが、PostgreSQLについては単純なメッセージを組み合わせる設計思想なため、クライアントドライバの実装は容易なものの、そのメッセージが必須なのかオプションなのかなどの仕様が明示されておらず、厳密な実装は難しそうな印象です。また、MySQLの仕様に比べると、フィールド情報に整数を利用するなど高速化が意識されているものの、応答メッセージの例に示した通り、メッセージに含まれる情報が少ないため、クライアントからの送信情報と照らし合わせて実装する必要がありそうです。
対するMySQLは、メッセージがメッセージ毎に明確に仕様化されており、厳密な実装はしやすい印象です。ただし、メッセージ毎に個別に定義されているため、単純に実装してしまうと、工数が大変そうです。ただし、MySQLの応答はPostgreSQLと比較すると、冗長とも思われる多くの情報が含まれるため、クライアントの実装については、送信情報を参照することなく、簡単に実装できそうな印象です。
今回は、興味があった問い合わせ(Query)とその応答を中心に調査してみましたが、またの機会があれば、他のシステム系の通信メッセージについても調べてみたいと思います。