すべてがシンプルです。 ここでは、PostgreSQLの場合の「ダミーのクエリ解析の基本」と、PostgreSQLおよびMySQLでクエリを記述する必要がない方法と、すべてを同じように記述した場合の動作に関する本番の素晴らしい非発明の例があります。
はじめに
人やドキュメントにかなりよく説明されているいくつかの明らかなことについてお話します。これらは通常、1ダースの熊手を入力した後、または一定数の手足を撃った後、または他の自傷行為の後に読みます。
いくつかの部分があり、論理的には相互に疎結合ですが、それらはすべて、ビジネスのさまざまなニーズを解決する過程で満たされ、何らかの形でそのニーズを満たしました。
と働くことのニュアンス
同じことは、大きな意味の負荷のない構文糖に非常に似ており、マーティンとファウラーの精神で大きな足布を別々の方法に分解することに関連付けられています。 ここでの主な機能は、特にクエリの最適化に関しては、これがメソッド/関数の類似物ではないということです。
私はすぐに読者に謝罪しますが、本文の後半では根本的に重要なリクエストのみが掲載され、リクエスト全体が公開されるわけではありません。 第一に、データ構造の機能に煩わされないようにするため、そして第二に、私が誤って私企業を投稿しないようにするためです。 ピースが完全に読めない場合は、強く叩かないでください。しかし、それらを改良する方法を提案してください。 ありがとう
方法は必要ありません。
クエリの本体からのソースSQLスライス
LEFT JOIN specifications_history AS specification_history ON specification_history.id = specification_detail.entity_history_id AND specification_history.specification_id = ANY(specification_parts.ids) LEFT JOIN specification_revision_details AS specification_section_detail ON specification_section_detail.specification_revision_id = specification_revision.id AND specification_section_detail.entity_type = 1002 LEFT JOIN specification_sections_history AS specification_section_history ON specification_section_history.id = specification_section_detail.entity_history_id LEFT JOIN specification_revision_details AS section_item_detail ON section_item_detail.specification_revision_id = specification_revision.id AND section_item_detail.entity_type = 1003 LEFT JOIN section_items_history AS section_item_history ON section_item_history.id = section_item_detail.entity_history_id
「洗練された」リクエスト
WITH revision_products AS ( SELECT DISTINCT specification_revision.id AS revision_id, specification_history.specification_id AS specification_id, section_item_history.product_id AS product_id FROM specification_revisions AS specification_revision INNER JOIN specification_revision_details AS specification_detail ON specification_detail.specification_revision_id = specification_revision.id AND specification_detail.entity_type = 1001 INNER JOIN specifications_history AS specification_histor ON specification_history.id = specification_detail.entity_history_id INNER JOIN specification_revision_details AS specification_section_detail ON specification_section_detail.specification_revision_id = specification_revision.id AND specification_section_detail.entity_type = 1002 INNER JOIN specification_sections_history AS specification_section_history ON specification_section_history.id = specification_section_detail.entity_history_id INNER JOIN specification_revision_details AS section_item_detail ON section_item_detail.specification_revision_id = specification_revision.id AND section_item_detail.entity_type = 1003 INNER JOIN section_items_history AS section_item_history ON section_item_history.id = section_item_detail.entity_history_id WHERE section_item_history.product_id IS NOT NULL )
ここでは、次のことが起こりました。多くのLEFT JOINがあるリクエストの本文から、WITHで削除され、INNER JOINに変わりました。 本体の読みやすさを向上させるために、ピースには調和のとれた名前が付けられ、実装の詳細はすべて引き離されました。 きれいなコードプラクティスを最大限に。 読みやすさで、本当に良くなりました。 リクエストの本文には、10の代わりに5つの結合が残っています。これは、リクエストの速度が75ミリ秒から95秒にすぐに低下したことです。 次のような興味深いものを紹介します：
-> Unique (cost=796821.66..848031.33 rows=5120967 width=12) (actual time=80769.666..94946.622 rows=315260 loops=1) -> Sort (cost=796821.66..809624.07 rows=5120967 width=12) (actual time=80769.663..90662.993 rows=37659600 loops=1) Sort Key: specification_revision_1.id, specification_history.specification_id, section_item_history.product_id Sort Method: external merge Disk: 809888kB
つまり、誰かが37リャモフの行を取り、1ギガのメモリ内でそれらを元気に並べ始めました。 すぐに質問が発生しました：
- 「そして、最大のテーブルに1.5ccがある場合、37ccの行はどこにありますか？」
- 「アルゴリズムを変更せず、コードを読み取り可能にしました。なぜすべてがハングしたのですか？」
- 「彼は宣言的です、私たちは私たちが望むものを言いましたが、すべてが壊れた理由を言いませんでしたか？」
回答 ：本体からWITHに結合を転送すると、 ドキュメントに記載されているとおりになりました。
WITHクエリ（共通テーブル式）
WITHクエリの有用な特性は、親クエリまたは兄弟WITHクエリによって複数回参照される場合でも、親クエリの実行ごとに1回だけ評価されることです。 したがって、複数の場所で必要な高価な計算をWITHクエリ内に配置して、冗長な作業を回避できます。 別の可能なアプリケーションは、副作用のある関数の望ましくない多重評価を防ぐことです。 ただし、このコインのもう1つの側面は、オプティマイザーが通常のサブクエリよりも親クエリからWITHクエリに制限をプッシュできないことです。 通常、WITHクエリは、親クエリが後で破棄する可能性のある行を抑制することなく、記述されたとおりに評価されます。 （ただし、前述のように、クエリへの参照が限られた数の行のみを要求する場合、評価が早期に停止する可能性があります。）
要するに、WITHクエリは1回実行され、多くの場合、最適化されていません。つまり、使用される場所は実行計画に影響しません。
つまり、リクエストの一部を独立した部分のレベルに残し、そこから重要な条件を追加することを忘れて、ほとんどのトマトの選択を減らしました。 その結果、彼らはベース全体を燃やしてから、このモンスターを本体に渡し、そこから数十行を取り出しました。
上記の特定のケースでは、WHEREには「product_id = 1234」という形式の条件があり、これによりメインデータの制限が設定されました。 この条件がWITHにドラッグされた場合、すべてがほぼ同じ速度で動作し続けます。 ただし、これは条件の右側の静的な値の場合にのみ実行できます。 たとえば、再帰クエリ中に理想的なクエリを取得した場合、WITHを使用するとこのような状態は解消されず、クエリを断片に分割するというアイデアは恥ずかしく遅くなります。
結論 ：
- ドキュメントを読む必要があります;
- すべての開発プラクティスが、この開発自体のさまざまな分野で同等に役立つわけではありません。
可視化の説明
Explain.depesz.comで誰もが最新の状態にあると思います。 リクエストの何が問題なのかを美しく示しています。
実際、これはExplainコマンドのデフォルト出力の単なる色付けですが、最初は非常に明確で、特に何に注目すべきかがわかるまで役立ちます...
ここでは、各列にいくつかの単語を付けて、それらが実行結果にどのように影響するかを説明します。 そうです、それはhelpにそこに書かれていますが 、スナップするまでヘルプを読む人はほとんどいません。
- ＃-リクエストの実行中の操作のシーケンス番号のみ
- 排他的-特定の操作を完了するまでの時間（ミリ秒）
- 包括的-コマンドのパイプライン全体を実行する時間（たとえば、上の図では、uniqを実行するには、少なくともソートを行う必要があります）
- 行X-スケジューラーが操作が返す行数を無駄にしたときに
Akellaをどれだけ見逃したか（はい、これは要求の処理方法に関する後続の意思決定にとって重要です）
初心者向けオプティマイザーのヒント
すべてが遅くなり、どこから始めるべきかわからない場合は、ここにいくつかのヒントがあります。 前の段落から色付きの説明を（できれば分析を使用して）取得し、それを見てください。 ほとんどの場合、問題（読み取り80％+ランタイム）は、実行計画で説明されている操作の1つに集中しています。 つまり、Exclusive / Inclusiveによって、最も暗い場所と最も暗い場所を見つけます。 繰り返しますが、上記の例は、クエリが実行される合計95秒のうち、uniq操作が94秒続くことを示しています。 同じ場所で、uniqソートではほとんどすべての時間がかかり、90秒かかります。 ここでは、行数、ソートアルゴリズム、および使用メモリの形式で問題を確認できます。 残っているのは、「誰が責任を負い、何をすべきか」を理解することだけです。 ここでは、ターゲットデータベースのデータ構造とクエリ結果の要件に関する知識のみが役立ちます。 数行を再配置するか、追加の条件を追加するだけで十分な場合があります。また、元の形式でできることは速度を落とすことだけなので、要求を完全に書き換える必要がある場合があります。
大きな「Rows X」にも注意を払う価値があります。 これは、予測および実際のサンプリング結果の欠落を示しており、ほとんどの場合、テーブルの統計が不十分であることが原因です。 これにより、最適でないクエリ実行プランが作成される場合があります。 たとえば、100万個の要素を持つテーブルから1つの行を選択します。 スケジューラが選択の結果が1行ではなく〜200,000になると判断した場合、インデックスで検索するのではなく、フルスキャンを実行します。これは、結果の行とテーブルのサイズの比率に対する最適な戦略であるためです。 速度について自分で結論を導きます。
標準レーキ
以下は、実際に最も頻繁に発生したものであり、クエリのわいせつな動作を引き起こしたものです。
- 未知の回避策やラウンドアバウトを介したデータ構造とデータ結合の理解の欠如、または、さらに良いことに、不要なデータの結合。 最後のそのようなケースはMySQLでした。 ここに、問題の本質を伝えるわずかに軽量化された例を示します。
SELECT ordered_products.* FROM products, products AS ordered_products GROUP BY ordered_products.id
一方では、彼らは単にFROM内の余分なテーブルを示し、それを決して使用しませんでした。 一方、CROSS JOINと2つのテーブルの暗黙的な結合を取得しました。 結果の行（少なくとも、MySQLバージョン5.5の場合）。 私の場合、productsテーブルには4万行ありましたが、クエリの終了を待ちませんでした。 私の知る限り、Oracleは結合の削除を行うことができますが、いずれにしても、DBMSの機能に依存するのではなく、頭の中で考えることをお勧めします。
ボーナス：ActiveRecordでそれを行い、すべてをハングアップする方法Product.joins(", (#{Product.table_name}) AS ordered_products"). select('ordered_products.*'). group("ordered_products.#{Product.primary_key}")
- アウタージョインへの愛。 それらは、少なくとも中間結果の線の幾何学的な成長を生成し、クエリが一部の入力データでスローダウンし、DBMSがデータ量で窒息することが簡単にわかります。 極端な例は、上記のテストでのクエリ（WITHを使用）です。 厳密なproduct_id制約の下でうまく機能しました。 同じリクエストは5〜15個の識別子の配列でうまく機能し、クエリ実行時間は直線的に増加しましたが、さらに配列内の後続の各識別子はクエリ実行時間を2〜3倍増加しました。 問題はOUTER JOINのセットに正確にあり、処理された行の数を乗法的に増加させ、その数がとてつもなく大きくなり、マイナーな開発者に実行計画を示すことができませんでした。
- 前の段落を続ける：いくつかは、左/右の代わりに完全な外部結合を配置することを好みます。これは、ほとんどの場合に十分です（Habrの住民でテストされ、 インタビューについての以前の記事からの要求について議論しました）。 問題は依然として同じです。過剰なデータを生成し、リソース消費を増やします。 個人から：FULL OUTER JOINは、最近2年ぶりに制作で本当に必要になりました...彼は子供の頃幸せでした。
- PostgreSQLなどの関数を使用した素晴らしい魔法は、宣言型の結合のペアの代わりに同じことを試みますが、それらを変換する関数と組み合わせて、配列やその他のデータ構造を介して命令型スタイルで実行します。 残念ながら、私は例を見つけることができないので、一言言わなければなりません。 私はそのようなものがstackoverflowで定期的に点滅することを覚えています。 唯一の良いニュースは、彼らがリーダーのように打ち負かされることはほとんどないということです。
終わり
どうもありがとう。 あなたがあなたが必要としない方法のあなた自身の素晴らしい例を持っているなら、静かにしないでください、多くの「有害なヒント」と「生きている例」はありません。 そして、この記事は、試験のチケットのように、話す口実に過ぎません。