☰

アプリケーション開発者が犯すデータベース開発のミス [closed] 質問する

tag-icon • tag-icon database database-design
アプリケーション開発者が犯すデータベース開発のミス [closed] 質問する

アプリケーション開発者が犯すデータベース開発の一般的な間違いは何ですか?

ベストアンサー1

1. 適切なインデックスを使用していない

これは比較的簡単ですが、それでも頻繁に発生します。外部キーにはインデックスが必要です。フィールドを使用している場合は、WHERE(おそらく) インデックスが必要です。このようなインデックスは、実行する必要があるクエリに基づいて、複数の列をカバーすることがよくあります。

2. 参照整合性を強制しない

データベースはここで異なる場合がありますが、データベースが参照整合性をサポートしている場合 (つまり、すべての外部キーが存在するエンティティを指すことが保証されている場合)、それを使用する必要があります。

MySQL データベースでは、この障害がよく見られます。MyISAM はこれをサポートしていないと思います。InnoDB はサポートしています。MyISAM を使用している人もいれば、InnoDB を使用しているが、いずれにしても使用していない人もいます。

詳細はこちら:

3. 代理（技術的）主キーではなく自然主キーを使用する

自然キーは、外部的に意味のある、(表面上は) 一意なデータに基づくキーです。一般的な例としては、製品コード、2 文字の州コード (米国)、社会保障番号などがあります。代理主キーまたは技術的主キーは、システム外部ではまったく意味を持たないキーです。これらはエンティティを識別するためだけに考案されたもので、通常は自動増分フィールド (SQL Server、MySQL、その他) またはシーケンス (最も有名なのは Oracle) です。

私の意見では、常に代理キーを使用するべきです。この問題は、次の質問で取り上げられています。

これは多少議論の余地のあるトピックであり、誰もが同意することはないだろう。自然キーは状況によっては問題ないと考える人もいるかもしれないが、代理キーはおそらく不要であるという以外に批判する人はいないだろう。私に言わせれば、それはかなり小さな欠点だ。

覚えておいてください、国は消滅する可能性がある（例えば、ユーゴスラビア）。

DISTINCT4.動作を要求するクエリの作成

これは、ORM で生成されたクエリでよく見られます。Hibernate からのログ出力を見ると、すべてのクエリが次のように始まっていることがわかります。

SELECT DISTINCT ...

これは、重複した行を返さず、重複したオブジェクトを取得しないことを保証するためのちょっとした近道です。これを行っている人を時々見かけるでしょう。あまりにも頻繁に見かける場合は、本当に危険信号です。これはDISTINCT悪いことでも、有効なアプリケーションがないわけでもありません。(どちらの点でも) 有効なアプリケーションがありますが、正しいクエリを書くための代替手段でも、その場しのぎの手段でもありません。

からDISTINCTが嫌いな理由:

私の意見では、事態が悪化し始めるのは、開発者が実質的なクエリを構築し、テーブルを結合しているときに、突然、重複した行 (またはそれ以上の行) を取得しているように見えることに気付き、すぐにこの「問題」に対する「解決策」として DISTINCT キーワードを追加すると、すべての問題が消えてしまうことです。

5. 結合よりも集約を優先する

GROUP BYデータベース アプリケーション開発者が犯すもう 1 つのよくある間違いは、結合に比べて集約 (句) がどれだけコストがかかるかを認識していないことです。

これがどれほど広まっているかを知るために、私はこのトピックについてここで何度か書いてきましたが、多くの反対票を投じられました。例えば、

からSQL ステートメント - 「結合」と「グループ化と割り当て」:

最初のクエリ:

SELECT userid
FROM userrole
WHERE roleid IN (1, 2, 3)
GROUP by userid
HAVING COUNT(1) = 3

クエリ時間: 0.312 秒

2番目のクエリ:

SELECT t1.userid
FROM userrole t1
JOIN userrole t2 ON t1.userid = t2.userid AND t2.roleid = 2
JOIN userrole t3 ON t2.userid = t3.userid AND t3.roleid = 3
AND t1.roleid = 1

クエリ時間: 0.016秒

そうです。私が提案した結合バージョンは、集約バージョンよりも 20 倍高速です。

6. ビューを通じて複雑なクエリを単純化しない

すべてのデータベースベンダーがビューをサポートしているわけではありませんが、サポートしているベンダーでは、ビューを賢く使用すればクエリを大幅に簡素化できます。たとえば、あるプロジェクトでは、一般的なパーティーモデルCRM 向け。これは非常に強力で柔軟なモデリング手法ですが、多くの結合が発生する可能性があります。このモデルには次のものがありました。

  • 政党：人々と組織;
  • 当事者の役割: 従業員や雇用主など、当事者が行ったこと。
  • 当事者の役割関係: それらの役割が互いにどのように関連しているか。

例：

  • テッドは人物であり、パーティーのサブタイプです。
  • テッドには多くの役割があり、そのうちの 1 つは従業員です。
  • Intel は組織であり、政党のサブタイプです。
  • Intel には多くの役割があり、その 1 つが雇用主です。
  • Intel は Ted を雇用しており、それぞれの役割の間には関係があることを意味します。

したがって、Ted と彼の雇用主をリンクするために結合されたテーブルは 5 つあります。すべての従業員が個人 (組織ではない) であると想定し、次のヘルパー ビューを提供します。

CREATE VIEW vw_employee AS
SELECT p.title, p.given_names, p.surname, p.date_of_birth, p2.party_name employer_name
FROM person p
JOIN party py ON py.id = p.id
JOIN party_role child ON p.id = child.party_id
JOIN party_role_relationship prr ON child.id = prr.child_id AND prr.type = 'EMPLOYMENT'
JOIN party_role parent ON parent.id = prr.parent_id = parent.id
JOIN party p2 ON parent.party_id = p2.id

すると突然、非常に柔軟なデータ モデル上で、必要なデータの非常にシンプルなビューが得られるようになります。

7. 入力をサニタイズしない

これは大きな問題です。私はPHPが好きですが、何をしているのかわからなければ、攻撃に対して脆弱なサイトを作るのは非常に簡単です。小さなボビー・テーブルの物語。

URL、フォーム データ、Cookieを通じてユーザーから提供されるデータは、常に悪意のあるものとして扱い、サニタイズする必要があります。期待どおりのものを取得していることを確認してください。

8. 準備されたステートメントを使用しない

準備されたステートメントとは、挿入、更新、句で使用されるデータを除いたクエリをコンパイルしWHERE、後でそれを提供するものです。例:

SELECT * FROM users WHERE username = 'bob'

対

SELECT * FROM users WHERE username = ?

または

SELECT * FROM users WHERE username = :username

プラットフォームによって異なります。

これを行うことでデータベースが機能不全に陥るのを見たことがあります。基本的に、最新のデータベースは新しいクエリに遭遇するたびにそれをコンパイルする必要があります。以前に見たことのあるクエリに遭遇した場合、データベースにコンパイルされたクエリと実行プランをキャッシュする機会を与えることになります。クエリを頻繁に実行することで、データベースにそれを理解し、それに応じて最適化する機会を与えることになります (たとえば、コンパイルされたクエリをメモリに固定するなど)。

準備されたステートメントを使用すると、特定のクエリが使用される頻度に関する有意義な統計も得られます。

準備されたステートメントは、SQL インジェクション攻撃に対する保護も強化します。

9. 標準化が不十分

データベースの正規化基本的には、データベース設計を最適化するプロセス、つまりデータをテーブルに整理する方法です。

今週、誰かが配列を分解してデータベースの単一のフィールドに挿入したコードに遭遇しました。これを正規化すると、その配列の要素を子テーブル内の別の行として扱うことになります (つまり、1 対多の関係)。

これはまた、ユーザーIDのリストを保存するための最適な方法:

他のシステムでは、リストがシリアル化された PHP 配列に格納されているのを見たことがあります。

しかし、正常化の欠如はさまざまな形で現れます。

もっと：

10. 過剰に標準化する

これは前のポイントと矛盾しているように思えるかもしれませんが、正規化は、他の多くのものと同様に、ツールです。正規化は目的を達成するための手段であり、それ自体が目的ではありません。多くの開発者がこのことを忘れて、「手段」を「目的」として扱い始めていると思います。ユニット テストは、この典型的な例です。

私はかつて、次のようなクライアントの巨大な階層を持つシステムに取り組んでいました。

Licensee ->  Dealer Group -> Company -> Practice -> ...

そのため、意味のあるデータを取得する前に、約 11 個のテーブルを結合する必要がありました。これは、正規化が行き過ぎた良い例です。

さらに言えば、慎重かつ熟慮された非正規化はパフォーマンス上の大きな利点をもたらしますが、これを行うときは非常に注意する必要があります。

もっと：

11. 排他的アークの使用

排他アークは、2 つ以上の外部キーを持つテーブルが作成され、そのうちの 1 つだけが非 NULL になる場合によく発生する間違いです。これは大きな間違いです。まず、データの整合性を維持するのがさらに難しくなります。結局のところ、参照整合性があっても、これらの外部キーを 2 つ以上設定することを防ぐことはできません (複雑なチェック制約にかかわらず)。

からリレーショナル データベース設計の実践ガイド:

排他的アーク構築は、コードの記述が面倒になり、メンテナンスが困難になる可能性があるため、可能な限り使用しないことを強くお勧めします。

12. クエリのパフォーマンス分析をまったく行わない

特にデータベースの世界では、実用主義が最優先です。原則に固執しすぎて教義になってしまったら、間違いを犯している可能性が高いです。上記の集計クエリの例を見てみましょう。集計バージョンは「見栄えが良い」かもしれませんが、パフォーマンスはひどいものです。パフォーマンスの比較で議論は終わるはずでしたが (結局終わりませんでした)、もっと重要なことは、そもそもそのような無知な意見を述べることは無知であり、危険でさえあるということです。

13. UNION ALL、特にUNION構造への過度の依存

SQL 用語の UNION は、一致するデータ セットを連結するだけです。つまり、同じタイプと列数を持つデータ セットを連結するだけです。両者の違いは、UNION ALL は単純な連結であり、可能な限り優先されるべきであるのに対し、UNION は重複するタプルを削除するために暗黙的に DISTINCT を実行することです。

UNION は、DISTINCT と同様に、適切な用途があります。有効な用途があります。しかし、特にサブクエリで UNION を頻繁に使用している場合は、おそらく何かが間違っている可能性があります。クエリの構築が適切でない場合や、データ モデルの設計が適切でない場合、このような操作を強いられる可能性があります。

UNION は、特に結合や従属サブクエリで使用される場合、データベースに悪影響を及ぼす可能性があります。可能な限り使用を避けるようにしてください。

14. クエリでOR条件を使用する

これは無害に思えるかもしれません。結局のところ、AND は OK です。OR も OK のはずですよね? 違います。基本的に、AND 条件はデータ セットを制限しますが、OR 条件はデータ セットを拡大しますが、最適化に適した方法ではありません。特に、異なる OR 条件が交差する可能性があり、その結果、オプティマイザーが事実上 DISTINCT 操作を実行するように強制する場合はそうです。

悪い：

... WHERE a = 2 OR a = 5 OR a = 11

より良い：

... WHERE a IN (2, 5, 11)

これで、SQL オプティマイザーは最初のクエリを 2 番目のクエリに効果的に変換できるかもしれません。しかし、そうならない可能性もあります。その場合は、実行しないでください。

15. 高性能なソリューションに適したデータモデルを設計していない

これは定量化が難しいポイントです。通常は、その効果によって観察されます。比較的単純なタスクに対して厄介なクエリを作成したり、比較的単純な情報を見つけるためのクエリが効率的でない場合、データ モデルが適切でない可能性があります。

ある意味、この点はこれまでの点すべてを要約していますが、クエリの最適化などは、本来は 2 番目に行うべきところを最初に行うことが多いという教訓に近いものです。何よりもまず、パフォーマンスの最適化を試みる前に、適切なデータ モデルがあることを確認する必要があります。Knuth 氏は次のように言っています。

時期尚早な最適化は諸悪の根源である

16. データベーストランザクションの誤った使用

特定のプロセスのすべてのデータ変更はアトミックである必要があります。つまり、操作が成功した場合は完全に実行されます。失敗した場合は、データは変更されません。- 「中途半端な」変更が行われる可能性があってはなりません。

理想的には、これを実現する最も簡単な方法は、システム設計全体で、単一の INSERT/UPDATE/DELETE ステートメントを通じてすべてのデータ変更をサポートするようにすることです。この場合、データベース エンジンが自動的にトランザクション処理を実行するため、特別なトランザクション処理は必要ありません。

ただし、データを一貫した状態に保つために、プロセスで複数のステートメントを 1 つの単位として実行する必要がある場合は、適切なトランザクション制御が必要です。

  • 最初のステートメントの前にトランザクションを開始します。
  • 最後のステートメントの後にトランザクションをコミットします。
  • エラーが発生した場合は、トランザクションをロールバックします。そして、非常に注意してください。エラーの後に続くすべてのステートメントをスキップ/中止することを忘れないでください。

また、この点に関して、データベース接続層とデータベース エンジンがどのように相互作用するかという微妙な点にも注意を払うことをお勧めします。

17. 「セットベース」パラダイムを理解していない

SQL 言語は、特定の種類の問題に適した特定のパラダイムに従います。さまざまなベンダー固有の拡張機能にもかかわらず、この言語は、Java、C#、Delphi などの言語では些細な問題に対処するのに苦労しています。

この理解不足はいくつかの形で現れます。

  • データベースに過度に手続き型または命令型のロジックを不適切に課す。
  • カーソルの不適切または過剰な使用。特に、単一のクエリで十分な場合。
  • 複数行の更新では、影響を受ける行ごとにトリガーが 1 回実行されると誤って想定しています。

責任の分担を明確にし、それぞれの問題を解決するために適切なツールを使用するよう努めます。

おすすめ記事