クエリー

どのようなものか

クエリーは、データベースが理解する言語で書かれたデータへの要求で、結
果が返ります。通常は行の集合で、ときには一つの数値です。PostgreSQLの
マニュアルは、一文で定義しています。データベースからデータを取得する
こと、または取得するコマンドをクエリーと呼び、SQLでは、そのコマンド
がSELECTです。日常的な使い方では意味が広がり、エンジニアは、データを
変更して何かを返す文を含め、データベースへ送るほぼすべての文をクエ
リーと呼びます。以下には、両方の意味が出てきます。複数形のqueries
は、通常、アプリケーションが送るすべての通信を指します。

どの請求書が最も長く支払い待ちになっているかを尋ねるクエリーは、口頭
の質問に近い表現になっています。

```sql
SELECT i.number, c.name, i.amount, i.due_on
FROM invoices AS i
JOIN customers AS c ON c.id = i.customer_id
WHERE i.paid_on IS NULL
  AND i.due_on < current_date
ORDER BY i.due_on
LIMIT 20;
```

記述するのは、何が欲しいかで、取得する方法は指定しません。PostgreSQL
のプランナーは、受け取ってから最初の行を返すまでの間に、請求書のテー
ブル全体を読む、`due_on`の索引をたどる、といった答えを得る方法を列挙
し、それぞれのコストを見積もり、コストが最も小さい方法を実行します。
そのコストは、慣例としてディスクから一ページを読むコストを基準とす
る、任意の単位で表します。そのため、画面上では似て見える二つのクエ
リーでも、対象データが増えると、コストが千倍違うことがあります。違い
が見えるのは、実行計画だけです。

各句は、書かれた順番では実行されません。マニュアルが説明するSELECTの
処理順は、最初にWITH、次にFROM、WHERE、グループ化とHAVING、その次に
出力する列の一覧で、ORDER
BYとLIMITは、その後です。多くの人が一度は戸惑う規則も、この順番で説
明できます。SELECTの出力列に付けた名前は、ORDER
BYとGROUP
BYでは使えますが、WHEREは、その名前が存在する前に処理されるため、式
をもう一度書く必要があります。どのクエリーも結果自体がテーブルになる
ため、入れ子にでき、サブクエリーやWITH句の行を、より大きいクエリーへ
渡せます。

なぜ重要なのか

リレーショナルデータベースを使うアプリケーションでは、ページの体感速
度や、システムに必要なハードウェアの量が、クエリーに大きく左右されま
す。どのクエリーが重要かは、直感ではなく計算で分かります。システムに
とってのコストは、一回にかかる時間に実行回数を掛けた値です。2
msの検索も、一時間に百万回実行すれば、毎時、合計で三十分を超えるデー
タベースの処理時間になります。一方、二秒かかるレポートでも、一日に二
回なら、ほとんど影響しません。

PostgreSQLは、pg_stat_statements拡張で、その集計を行います。リテラル
の値だけが違う文を一つの項目にまとめ、それぞれの実行回数と合計時間を
記録します。平均ではなく合計で並べることが、クエリーの改善で最初に役
立つ習慣です。遅く見えるだけの文ではなく、直す価値がある文を指し示す
からです。

どのクエリーにも、往復通信のコストがあります。アプリケーションが文を
送り、サーバーが構文を解析し、計画して実行し、行が戻ってきます。索引
を使う速い検索では、処理そのものより、往復の方が重い場合があります。
そのため、個々の文と同じくらい、やり取りの形が重要です。百行を変更す
る一つのUPDATEなら、往復は一回です。百行を読み、一行ずつ更新するルー
プでは、百一回になります。

クエリーを誰が書くか、という問題もあります。ORMやクエリービルダー
は、アプリケーションのコードから生成するため、単純な読み書きを手早く
実装でき、コードを一つの言語に保てます。一方、ページが遅くなるまで、
一つのリクエストが送る文の数や内容を隠してしまいます。手書きのSQLな
ら、両方が見えますが、ほかのコードと同様に、誰かが責任を持って保守す
る必要があります。実用的な分担は、日常的なレコード操作をORMに任せ、
性能上重要な処理経路を手書きし、合計コストの上位に来る処理では、生成
されたSQLを読むことです。

実際の活用例

データベースを分割する前に最適化するで示す、遅いデータベースへの対応
は、誰かが追加マシンの価格を調べるより先に、クエリーから始めます。最
初の手順では、pg_stat_statementsを合計実行時間で並べ、全体で最もコス
トのかかる文を、PostgreSQLに尋ねます。次に、一覧の先頭の文にEXPLAIN
(ANALYZE,
BUFFERS)を実行し、選ばれた計画、各手順が実際に返した行数とプランナー
の予測との差、読んだページ数を確認します。このノートの目安は、十行を
返すために百万行を走査している場合や、見積もりが百倍外れている場合
は、それだけで遅さを説明できることが多い、というものです。往復回数も
数え、この記事と同じ境界を引きます。データへアクセスする必要がある処
理はデータベースへ移し、業務規則はアプリケーションに残します。

クエリーは、ロックの役割も果たせます。最後の一枚を一度だけ売るでは、
短時間に注文が集中する販売で、二種類の失敗が起きます。在庫を読み、ア
プリケーションで判断し、新しい数を書き戻す方法では、二人の購入者が、
どちらも最後の一個を取れてしまいます。そのアプリケーションの処理中
ずっと行ロックを保つと、全購入者が、その後ろで待つことになります。解
決策は、在庫の確認と減算を、一つの条件付きUPDATEへまとめることです。
十分な在庫がある場合だけ数量を引き、新しい在庫数を返します。一行返れ
ば確保でき、零行なら売り切れです。PostgreSQLの既定の分離レベルである
READ
COMMITTEDでは、一人目が行を保持している間に二人目が到達すると、二人
目は待ち、コミット済みの行に対してWHERE句を再評価します。そのため、
最後の一個を得るのは、必ずどちらか一人です。在庫をゼロ以上に保つ
CHECK制約が、このクエリーを補強します。

問題が起きるところ

軽いクエリーが、負荷の大半を占めます。
データベースの負荷は高いのに、各呼び出しは数ミリ秒で終わるため、遅い
クエリーのログには何も出ません。原因は頻度です。リクエストのたびに、
一つの速いクエリーを呼び、多くの場合、ループや、全ページに現れる部品
から実行しています。pg_stat_statementsで文を合計実行時間順に並べ、結
果をキャッシュする、呼び出しをまとめる、絞り込み条件に合う索引でクエ
リーの処理負担を下げる、といった対策を取ります。

一ページが、何百本ものクエリーを送ります。
トレース内の各クエリーは一ミリ秒でも、ページには数秒かかります。典型
的な原因は、ORMが、親の行ごとに一つのクエリーを送り、関連するレコー
ドを遅延読み込みすることで、N+1パターンと呼ばれます。結合か、`WHERE
id =
ANY($1)`とキーの配列を使い、関連する行を一つのクエリーで読みます。一
つの文の実行時間と同じくらい、リクエスト当たりの文の数も注意して見ま
す。

最後の一個を、二度売ります。
二つのリクエストが、同じ在庫数を読み、どちらも十分あると判断して書き
込みます。読み取りと書き込みを別々の文にすると、確認と実行の間に隙間
が生まれ、同時に来る通信が、その隙間へ入り込みます。先ほどの条件付き
UPDATEとRETURNINGを使い、クエリーの中で隙間を閉じます。あるいは、
SELECT
i... FOR		engineer.company	70
UPDATEで取得したロックの保持時間をできる限り短くし、ロック取得からコ
ミットまでの間に、ネットワーク呼び出しを挟まない方法を取ります。

調査で、データを変えてしまいます。 遅いDELETEの計画を見るために
EXPLAIN
ANALYZEを実行したら、行が消えます。ANALYZEは、実際に文を実行し、出力
だけを捨てるため、すべての副作用が起きます。データを変更する文を解析
するときは、BEGINとROLLBACKで囲みます。あるいは、見積もりで十分な
ら、実行せずに計画だけを作る、通常のEXPLAINを使います。

参考資料

  * SELECT — 各句の処理順を含む、PostgreSQLのクエリーの完全な構文で
す。

  * 7.1. 概要 — クエリーについての章の冒頭で、用語を定義しています。

  * 51.1. クエリーの経路 —
クライアントが送った文が、パーサー、リライター、プランナーを通り、エ
グゼキューターへ届くまでです。

  * 14.1. EXPLAINの使い方 — 計画の読み方、コストの単位の意味、
ANALYZEが文を実行する理由です。

  * F.32. pg_stat_statements — SQLの計画と実行の統計を追跡する —
合計コストでクエリーを順位付けする拡張です。

  * 13.2. トランザクションの分離 — READ
COMMITTEDで二人目の書き手に何が見え、なぜWHERE句を再評価するのかを説
明します。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/query/	engineer.company	70
