[解決済み] アプリケーション開発者が陥りやすいデータベース開発の失敗例【終了しました

質問

アプリケーション開発者が陥りがちなデータベース開発の失敗とは？

解決方法は？

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

これは比較的簡単なことなのですが、それでもよくあることなのです。 外部キーにはインデックスが必要です。 もしあなたがフィールドを WHERE は、（おそらく）その上にインデックスを持つ必要があります。 このようなインデックスは、実行するクエリに応じて、しばしば複数のカラムをカバーする必要があります。

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

データベースによって異なるかもしれませんが、もしデータベースが参照整合性（すべての外部キーが存在するエンティティを指すことが保証されていること）をサポートしているならば、それを使うべきでしょう。

MySQLのデータベースでこの失敗を見ることはよくあることです。 MyISAMはサポートしていないと思う。 InnoDBはサポートしている。 MyISAMを使っている人や、InnoDBを使っているけどとにかく使っていない人を見かけることがあります。

詳しくはこちら

• データベースの入力を常にphpで制御する場合、NOT NULLやFOREIGN KEYのような制約はどの程度重要でしょうか？
• 外部キーは本当にデータベース設計に必要ですか？
• データベースの設計において外部キーは本当に必要なのか？

3. 代理（技術）主キーではなく、自然主キーの使用

自然キーとは、外部的に意味のあるデータを基にしたキーで、（表向きは）一意である。 一般的な例としては、製品コード、2文字の州コード（米国）、社会保障番号などがあります。 代理キーまたは技術的キーは、システムの外部ではまったく意味を持たないものです。 これは純粋に実体を識別するために作られたもので、通常は自動インクリメントのフィールド（SQL Server、MySQL、その他）またはシーケンス（最も有名なのはOracle）である。

私の意見では、以下のようにすべきです。 常に はサロゲートキーを使用します。 この問題は、これらの質問で出てきました。

• 主キーはどうしますか？
• テーブルの主キーに関するベストプラクティスは何ですか？
• このような場合、どの形式の主キーを使用しますか。
• 代理キーと自然キー/ビジネスキーの比較
• 専用の主キーフィールドを用意した方が良いですか？

これは、普遍的な同意を得ることができない、やや議論のあるトピックです。 自然なキーはある状況下では問題ないと考える人はいるかもしれませんが、代理キーについては、間違いなく不要であるということ以外、批判は見当たりません。 私に言わせれば、これは非常に小さなマイナス点です。

覚えておいてください。 国が消滅することもある (例えばユーゴスラビア)。

4. を必要とするクエリを書く DISTINCT を動作させる

ORMが生成するクエリでよく見かける光景です。 Hibernateのログ出力を見てみると、すべてのクエリが、このように始まっていることがわかります。

SELECT DISTINCT ...

これは、重複した行を返さないようにするためのちょっとした近道で、結果として重複したオブジェクトを取得することになります。 このような方法をとっている人を時々見かけます。 もし、これを頻繁に見かけたら、本当に赤信号です。 とはいえ DISTINCT が悪いとか、有効な用途がないとか。 しかし、正しいクエリを書くための代用品でもなければ、その場しのぎでもありません。

から 私がDISTINCTを嫌いな理由 :

私が思うに、物事がうまくいかなくなり始めるのは 開発者が 実質的なクエリの構築、結合 テーブルを結合した後、突然 ということに気づきました。 見た目 のように 重複した（あるいはそれ以上の）行を取得する。 と即答しています。 この問題に対する解決策は、次のとおりです。 DISTINCT キーワードを投入し プー は、彼の悩みをすべて解決してくれる。

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

データベースアプリケーションの開発者にありがちなもう一つの間違いは、集計（つまり GROUP BY 節)は、結合と比較して

このことがどれだけ広まっているかは、ここで何度かこの話題を書いて、たくさんdownvotedされたので、おわかりいただけると思います。 例えば

から SQLステートメント - "join "対 "group by and having" :

最初のクエリ

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 s

そうなんです。私が作った結合バージョン 提案したのは 20倍速い 集計版

6. ビューによる複雑なクエリを簡略化しない

すべてのデータベースベンダーがビューをサポートしているわけではありませんが、サポートしているベンダーでは、適切に使用すれば、クエリを大幅に簡素化することができます。 例えば、あるプロジェクトで私は 汎用パーティーモデル をCRMに採用しました。 これは非常に強力で柔軟なモデリング手法ですが、多くの結合を引き起こす可能性があります。 このモデルでは、以下のようなものがありました。

• パーティー : 人と組織です。
• パーティーの役割 例えば、Employee（従業員）とEmployer（雇用者）のように、当事者が行ったこと。
• 当事者の役割関係 : それらの役割が互いにどのように関連しているか。

例

• Ted は Person であり、Party のサブタイプである。
• Tedは多くの役割を持ち、そのうちの1つがEmployeeである。
• Intelは組織であり、Partyのサブタイプである。
• インテルには多くの役割がありますが、そのうちのひとつが雇用者です。
• インテルはテッドを雇用しており、それぞれの役割の間に関係があることを意味する。

つまり、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やフォームデータなど、ユーザーから提供されるデータ およびクッキー は常に敵対的なものとして扱われ、サニタイズされる必要があります。 期待するものを得ることができるかを確認する。

8. プリペアドステートメントを使用しない

プリペアドステートメントとは、クエリから挿入、更新、削除に使用するデータを除いたものをコンパイルすることです。 WHERE 節を作成し、後でそれを提供します。 例えば

SELECT * FROM users WHERE username = 'bob'

対

SELECT * FROM users WHERE username = ?

または

SELECT * FROM users WHERE username = :username

は、お使いのプラットフォームによって異なります。

私は、この方法によって、データベースが膝をつくのを見たことがあります。 基本的に、最近のデータベースは新しいクエリに遭遇するたびに、それをコンパイルしなければなりません。 もし、以前に見たことのあるクエリに遭遇したら、データベースはコンパイルされたクエリと実行計画をキャッシュする機会を与えられることになります。 何度もクエリを実行することで、データベースがそれを理解し、それに応じて最適化する機会を与えることになります（例えば、コンパイル済みのクエリをメモリに固定する）。

プリペアドステートメントを使用することで、特定のクエリの使用頻度に関する有意義な統計も得ることができます。

プリペアドステートメントは、SQLインジェクション攻撃からの保護も強化します。

9. 正規化が十分でない

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

今週、ある人が配列をインプロードして、データベースの1つのフィールドに挿入しているコードに遭遇しました。 これを正規化すると、配列の要素を子テーブルの別の行として扱うことになります(つまり一対多の関係です)。

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

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

しかし、正規化されていないことは、さまざまな形で現れます。

もっと見る

• 正規化する。どこまでが十分な距離か？
• SQL by Design。データベースの正規化が必要な理由

10. ノーマライゼーションのしすぎ

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

私はかつて、クライアントに対して次のような巨大な階層を持つシステムで仕事をしたことがあります。

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

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

さらに言えば、慎重に検討した上で非正規化を行えば、パフォーマンスに大きなメリットをもたらしますが、これを行う際には本当に注意しなければなりません。

もっと見る

• データベース正規化のしすぎが悪いことである理由
• データベース設計における正規化はどこまでやればいいのか？
• SQLデータベースを正規化しない場合
• 正規化することは正常ではないのかもしれない
• Coding Horrorにおけるデータベース正規化に関する議論の母体

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文を通してすべてのデータ変更をサポートするように努力することです。この場合、データベースエンジンが自動的にトランザクション処理を行うため、特別なトランザクション処理は必要ありません。

しかし、データの一貫性を保つために、複数のステートメントを一度に実行する必要がある場合は、適切なトランザクション制御が必要です。

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

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

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

SQL言語は、特定の種類の問題に適した特定のパラダイムに従っています。様々なベンダー固有の拡張はともかく、この言語は、Java、C#、Delphiなどの言語では些細な問題を扱うのに苦労しています。

この理解不足は、いくつかの点で現れている。

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

責任分担を明確にし、それぞれの問題に対して適切なツールを使用するよう心がける。