プログラマーが行う10の潜在的なSQLエラー

元の記事は「Java開発者が作成する10個のSQLエラー」と呼ばれますが、概して、その中に含まれる原則はどの言語にも起因します。







Javaプログラマーは、レベルに応じて、オブジェクト指向および命令型の思考を妨げます。

-習得（誰でも命令的にプログラミングできます）

-ドグマ（テンプレートをどこにでも適用して名前を付けるためのテンプレート）

-気分（真のオブジェクトアプローチを適用することは、命令よりも少し難しい）



しかし、Java開発者がSQLコードを作成すると、すべてが変わります。

SQLは、オブジェクト指向または命令型の思考とは関係のない宣言型言語です。 SQでリクエストを表現することは非常に簡単ですが、それを正確かつ最適に表現することはかなり困難です。 開発者は、プログラミングパラダイムを再考する必要があるだけでなく、集合論の観点から考える必要もあります。



以下は、JDBCまたはjOOQのSQLを使用してJava開発者が犯したよくある間違いです（特定の順序なし）。 他の10個のエラーについては、この記事を参照してください 。

1. NULLを忘れた

NULLの誤解は、おそらくJava開発者がSQLを記述するときに犯す最大の間違いです。 これは、NULLがUNKNOWNとも呼ばれるためかもしれません。 単にUNKNOWNと呼ばれていれば、理解しやすいでしょう。 もう1つの理由は、データを取得して変数をバインドすると、JDBCはJava NULLにSQL NULLを反映するためです。 これにより、NULL = NULL（SQL）がnull == null（JAVA）と同じように動作する可能性があります。



NULL誤解の最も明確な例の1つは、 NULL述部がストリング値式で使用される場合です。



別の、より具体的な問題は、NOT IN anti-joinsのNULL値の理解がない場合に発生します 。



薬：

あなた自身を訓練します。 複雑なことはありません-SQLを書くときは常にNULLについて考えてください：

-この述語はNULLに関して正しいですか？

-NULLはこの関数の結果に影響しますか？

2. Javaメモリ内のデータの処理

多くのJavaプログラマーがSQLをよく知っているわけではありません。 ランダムに参加し、奇妙なUNIONと申し分なく。 ウィンドウ関数はどうですか？ グループ化セット？ 多くのJava開発者はSQLデータをメモリにロードし、適切なコレクションに変換し、冗長な循環構造を使用してこれらのコレクションに必要な計算を実行します（少なくともJAVA 8のコレクションが改善されるまで）。



ただし、一部のSQLデータベースは、これに適した、記述しやすい追加の（SQL標準！）OLAP関数をサポートしています。 1つの例（標準ではない）は、 Oracleの優れたMODELステートメントです。 データベースに処理を行わせ、結果をJavaメモリにプルするだけです。 結局、一部の賢い人がすでにこれらの高価な製品を最適化しているからです。 したがって、データベースでOLAPを使用すると、次の2つのことが得られます。

-シンプル。 おそらく、JavaよりもSQLで正しく記述する方が簡単です。

-パフォーマンス。 DBは、アルゴリズムよりも高速である可能性があります。 さらに重要なことは、何百万ものレコードを有線で取得する必要がないことです。



薬：

Javaを使用してデータ指向のアルゴリズムを作成するたびに、「この作業をデータベースに移行することは可能ですか？」と自問してください。

3. UNION ALLの代わりにUNIONを使用する

UNION ALLにはUNIONに関する追加の単語が必要であるという事実に恥をかきます。 SQL標準が以下をサポートするように定義されていれば、はるかに優れています。

-UNION（複製を許可）

-UNION DISTINCT（重複を削除）



重複除去はあまり一般的に使用されないだけでなく、大きなサンプリング結果ではかなり遅いです。 2つのサブクエリを順序付けし、各タプルを後続のタプルと比較する必要があります。



SQL標準でINTERSECT ALLおよびEXCEPT ALLが定義されている場合でも、すべてのデータベースがこれらの使用頻度の低い操作セットを実装できるわけではないことに注意してください。



薬：

UNIONを作成するたびにUNION ALLを作成するかどうかを検討してください。

4. JDBCを使用して大きなサンプルをページ分割する

ほとんどのデータベースは、LIMIT ... OFFSET、TOP ... START AT、OFFSET ... FETCH演算子を使用して、ある種のページネーションをサポートしています。 これらのステートメントがサポートされていない場合でも、 ROWNUM（Oracle）またはROW_NUMBER（）OVER（）フィルタリング（DB2、SQL Server 2008など）が存在する可能性があります。 これは主に大きな変位に適用されます！



薬：

これらの演算子、またはこれらの演算子を模倣できるツール（jOOQなど）を使用してください。

5. Javaメモリでのデータ接続

SQLの初期から現在まで、一部の重厚なJavaプログラマーはJOINを作成します。 JOINの実行速度が遅いという時代遅れの恐れがあります。 これは、オーバーヘッドオプティマイザーが、結合されたテーブルのセルを作成する前にテーブル全体をメモリにロードすることにより、ネストされたループを作成することを選択した場合に該当します。 しかし、これはめったに起こりません。 通常の述語、制限、インデックス、MERGE JOINまたはHASH JOIN操作は非常に高速です-それはすべて正しいメタデータに依存します（ Tom Kyteはこれについてよく書いています ）。 ただし、2つの別々のクエリを使用して2つのテーブルをロードし、何らかの方法でJavaメモリに結合するJava開発者はまだほとんどいないでしょう。



薬：

さまざまな段階でさまざまな表から選択する場合、もう一度考え直してください。突然、要求を1つ表現できます。

6. DISTINCTまたはUNIONを使用して、ランダムなデカルト積から重複を削除する

複雑な結合（JOIN）により、開発者は意味のあるSQLクエリ関係を追跡できなくなります。 より具体的には、複合外部キーとの関係を使用する場合、JOIN ... ONステートメントに意味のある述部を追加することを忘れる場合があります。 これにより、文字列が常に重複したり、例外的な状況でのみ重複する可能性があります。 その後、一部の開発者はDISTINCTステートメントを追加して、データの複製を停止します。 これは3つの理由で正しくありません。

「結果を癒すことはできますが、原因を癒すことはできません。」 それでも、これは境界条件の下で結果を解決しないかもしれません。

-これは、大規模なサンプルでは時間がかかります。 DISTINCTは、ORDER BY操作を実行して重複を削除します。

-これは、メモリにロードされるデカルトの大規模な作品の場合は遅いです。



薬：

一般に、不要な重複が発生した場合は、JOIN述語を修正します。 おそらくどこかで小さなデカルト積が形成されたでしょう。

7. MERGEステートメントの回避

これは実際には間違いではありませんが、おそらく強力なMERGEオペレーターの知識や恐怖の欠如です。 一部のデータベースは、他の形式のUPSERTステートメント、たとえばMySQL ON DUPLICATE KEY UPDATEを認識しています。 実際、MERGEは、特にSQL ServerなどのSQL標準を大幅に拡張するデータベースでは、非常に強力です。



薬：

INSERTとUPDATEまたはSELECT ... FOR UPDATEとINSERT / UPDATEのチェーンを構築してUPSERTを実行している場合は、もう一度考えてください。 リソースを奪い合うリスクを冒す代わりに、より単純なMERGEリクエストを書くことができます。

8.ウィンドウ関数の代わりに集約関数を使用する

ウィンドウ関数が出現する前は、SQLでデータを集計する唯一の方法は、GROUP BYを投影で集計関数とともに使用することでした。 これはほとんどの場合にうまく機能し、集約データに通常のデータを入力する必要がある場合、グループ化されたクエリを添付クエリに書き込むことができます。

ただし、SQL：2003では、多くのデータベースプロバイダーによって実装されるウィンドウ関数が定義されています。 ウィンドウ関数は、グループ化されていないサンプルのデータを集計できます。 実際、各ウィンドウ関数は独自のPARTITION BY操作をサポートしています。これは、レポートを作成するための優れたツールです。



ウィンドウ関数を使用すると、次のことが可能になります。

-より読みやすいSQLを作成します（サブクエリで強調表示されていないGROUP BY式）

-以来のパフォーマンスの改善 RDBMSはウィンドウ関数をより簡単に最適化できます



薬：

サブクエリでGROUP BY式を記述する場合、ウィンドウ関数で表現できるかどうかを考えますか？

9.さまざまなパラメーターでメモリ内の並べ替えを使用する

ORDER BY演算子は、CASEを含む多くのタイプの式をサポートします。これは、ソートパラメーターを定義するときに非常に便利です。 次の理由だけで、Javaメモリ内のデータをソートしないでください。

-SQLのソートが遅すぎる。

-SQLソートではこれができません。



薬：

Javaメモリ内のSQLデータを並べ替える場合、この並べ替えをデータベースに転送できるかどうかを考えてください。 これは、データベースのページネーションに適しています。

10.複数のレコードを一度に1つずつ挿入します

JDBCはバッチとは何かを知っているので、それを使用する必要があります。 数千のレコードを次々に挿入しないでください。毎回新しいPreparedStatementを作成します。 すべてのレコードが1つのテーブルに移動する場合、1つのSQLクエリと複数の関連データセットを使用して、INSERTクエリのバッチを作成します。 データベースとその構成によっては、UNDOログをクリーンに保つために、一定数のレコードが挿入された後にコミットする必要がある場合があります。



薬：

常に大きなデータセットのバッチ挿入を使用します。

トピックに関するいくつかの興味深い本

-Bill KarwinによるSQLアンチパターン

-Markus WinandによるSQLパフォーマンスの説明