MySQLのSELECT *：すべての列を取得する前に考えたいこと

LinkedInのMySQLグループで、MySQLクエリーのSELECT *を題材にしたミームを共有しました。 今回は、そのジョークの背景にある技術的な理由を説明します。

次のようなクエリーは、多くの開発者が一度は書いたことがあるでしょう。

SELECT *
FROM customers;

すぐに書けて便利ですし、列名がcustomer_nameなのか、full_nameなのか、それともname_final_v2なのかを思い出す手間も省けます。 しかし、この省略をアプリケーションコードに持ち込むと、入力を少し省いた代わりに、思いがけないコストが生じることがあります。

すべての列を取得するコスト

あるアプリケーションの画面で、顧客の氏名とメールアドレスが必要だとします。

SELECT *
FROM customers
WHERE customer_id = 42;

テーブルに自己紹介文、JSON形式の設定情報、BLOBまたはBase64形式のテキストで保存したプロフィール画像もあれば、このクエリーはそれらも返すようMySQLに要求します。 アプリケーションが不要な値を捨てても、そのデータはデータベースからクライアントへ転送され、クライアントは受信して処理する必要があります。 サンドイッチを注文したら、ショーケースの中身まで全部届くようなものです。

アプリケーションが実際に使う列を指定しましょう。

SELECT first_name, last_name, email
FROM customers
WHERE customer_id = 42;

行を見つけた後にも処理は続く

必要な行をすばやく見つけられても、アプリケーションの待ち時間が短くなるとは限りません。 結果はMySQLからデータベースクライアント（通常はアプリケーションサーバー）へ転送され、コードで扱える形にデコードされます。

結果セットが大きい場合や帯域幅が限られる場合には、検索よりも転送がボトルネックになることがあります。 SELECT *で使わない列まで取得すると、特に大きなテキスト、JSON、バイナリーデータが含まれる場合に、転送量が増加します。

仮に、平均200バイトの行を10,000行返すなら、データ量は約2MBです。 不要な列を含めた結果、1行の平均が5KBになると、同じ10,000行でも約50MBになります。 これらは、プロトコルのオーバーヘッドや圧縮を考慮する前の値です。 行数は同じでも、運ぶデータの量は大きく変わります。

必要な列だけを選べば、転送するデータ量を減らせます。 すべての性能問題が解決するわけではありませんが、使わないデータのために帯域幅やクライアントの処理時間を費やすことを避けられます。 性能を測る際は、結果を最後まで取得し、アプリケーションが処理するまでの時間も測定してください。 MySQLが行を見つけるまでの時間と、アプリケーションが結果を受け取るまでの時間は異なります。 性能監視やプロファイリングのツールを使うと、ネットワークのコストや時間を費やしている箇所を調べられます。

LIMITが制限するのは行数であり、1行に含まれるデータ量ではありません。 注文するサンドイッチの数を減らしても、1つごとにショーケースの中身が付いてくるなら、効果は限られます。

列の追加で変わる結果セット

次のクエリーを考えてみましょう。

SELECT *
FROM customers;

現在は6列を返していても、翌月に7つ目の可視列が追加されれば、更新後のテーブルに対する実行結果は7列になります。 MySQLは指定どおりに動作しています。 しかし、アプリケーション側では「すべて」を「この6列が今後も変わらず返る」と解釈していたかもしれません。

その場合、行を固定個数の変数に展開するコード、列の位置で値を対応付ける処理、特定の構造を前提とするエクスポートなどに問題が生じます。 列を明示すれば、アプリケーションがどの列に依存しているかをコードで示せます。

SELECT customer_id, first_name, last_name, email
FROM customers;

このクエリーなら、無関係な列を追加しても結果セットの列は増えません。 ただし、指定した列の名前を変更したり削除したりすれば、クエリーは失敗する可能性があります。 列の明示によって、通常のスキーマ変更が予期せず取得列を増やすことを防げます。

アプリケーションのキャッシュやORMのマッピングによって、新しい列がアプリケーションに現れるタイミングは変わることがあります。 キャッシュされた結果は以前の構造を保ち、モデルは定義済みのフィールドだけを公開するかもしれません。 それでも、ワイルドカードで指定した結果の構造が安定するわけではなく、理解すべき動作がもう一層増えることになります。

なお、MySQLの*には不可視列（invisible column）は含まれません。 ここでいう「すべて」は、可視列すべてを意味します。

カバリングインデックスで完結できなくなる場合

クエリーが必要とするすべての列をインデックスが含んでいれば、MySQLはテーブルの行全体を参照せず、インデックスだけから値を取得できます。 このようなインデックスをカバリングインデックスと呼びます。 たとえば、次のインデックスがあるとします。

CREATE INDEX idx_orders_customer_status
ON orders (customer_id, status);

次のクエリーでは、このインデックスをカバリングインデックスとして利用できる可能性があります。

SELECT customer_id, status
FROM orders
WHERE customer_id = 42;

これを次のように変更すると、どうなるでしょうか。

SELECT *
FROM orders
WHERE customer_id = 42;

インデックスに含まれない列がテーブルにあれば、必要なデータをそのインデックスだけでは取得できません。 一致する行を探す際には、引き続きインデックスが役立つ可能性があります。 SELECT *を指定しただけで、インデックスが使われなくなったり、必ず全表走査になったりするわけではありません。 ただし、そのカバリングインデックスだけでクエリーを処理できる機会を失うことがあります。

実際の実行計画はEXPLAINで確認してください。 性能について判断するには、実行計画などの根拠が必要です。

結合で重複する列名

次のクエリーは、一見すると問題がないように見えます。

SELECT *
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id;

しかし、*は両方のテーブルの可視列をすべて返すため、customer_idも両方から返されます。 両テーブルにcreated_atやstatusがあれば、それらの列名も重複します。 結果セットの列名が重複すると、アプリケーションで値を対応付ける際に紛らわしくなり、具体的な動作もクライアントライブラリーによって異なります。

必要な列を明示し、必要に応じて別名を付けます。

SELECT
    c.customer_id,
    c.email,
    o.order_id,
    o.status AS order_status
FROM customers AS c
JOIN orders AS o
    ON o.customer_id = c.customer_id;

これなら、スキーマを調べ回らなくても、クエリーが何を返すためのものかを読み取れます。

意図していない列の公開を防ぐ

APIレスポンスのデータを取得するクエリーが、次のようになっているとします。

SELECT *
FROM users
WHERE user_id = 42;

後からinternal_notesという可視列を追加したとします。 アプリケーションが行全体を自動的にシリアライズしていれば、その列が本来含まれるべきでないレスポンスに入り込む可能性があります。 同様の問題は、ログやエクスポートでも起こります。

取得する列を指定すると、こうした意図しない情報公開を減らせます。 これは権限設定や、レスポンスに含める項目を明示的に対応付ける処理を補う対策です。 それらの代わりになるものではありません。 公開プロフィールのAPIに、サポート担当者が残した返金対応のメモまで含める必要はないはずです。

SELECT *が適している場面

不慣れなテーブルの中身を調べるとき、問題を調査するとき、SQLクライアントで手早くクエリーを実行するときには、SELECT *が役立ちます。 可視列をすべて取得することが意図した要件である場合にも、適切な選択になり得ます。 問題となるのは、アプリケーションが本当に必要とするデータを検討せず、アプリケーションコードで常に使うことです。

なお、COUNT(*)は役割が異なります。 これは行数を数えるもので、すべての列をアプリケーションへ返す要求ではありません。 SELECT *の取得列に関する議論とは区別してください。

クエリーを確定する前の確認事項

クエリーをコードにコミットする前に、次の点を確認します。

• 呼び出し元が実際に使う列はどれか。
• 不要なデータがどのくらいクライアントへ転送されるか。
• 列を追加したとき、結果セットが予期せず変わらないか。
• 取得する列を絞ることで、カバリングインデックスを利用できないか。
• 不要な項目がレスポンス、ログ、エクスポートに含まれていないか。

確認したら、必要な列をクエリーに書き出しましょう。 入力は少し増えますが、コードエディターの補完機能も利用できます。 SELECT *は、文字どおり可視列をすべて必要とする場面で使い、それ以外では取得する列をMySQLに明示してください。