应用程序开发人员犯的数据库开发错误

Question

就目前而言，这个问题不适合我们的问答形式。我们希望答案得到事实、引用或专业知识的支持，但这个问题可能会引起辩论、争论、投票或扩展讨论。如果你觉得这个问题可以改进并可能重新打开，visit the help center 寻求指导。




9年前关闭。










锁定。这个问题及其答案是 locked 因为这个问题是题外话但具有历史意义。它目前不接受新的答案或互动。








应用程序开发人员常犯的数据库开发错误有哪些？

Answer 1

1. 未使用适当的索引

这是一个相对容易的，但它仍然发生在所有的时间。外键应该有索引。如果您在 WHERE 中使用一个字段，则您应该(可能)在其上有一个索引。根据您需要执行的查询，此类索引通常应涵盖多个列。

2. 未实现参照完整性

您的数据库可能在这里有所不同，但如果您的数据库支持参照完整性——这意味着所有外键都保证指向一个存在的实体——您应该使用它。

在 MySQL 数据库上看到这种失败是很常见的。我不相信 MyISAM 支持它。 InnoDB 确实如此。你会发现有人在使用 MyISAM 或那些正在使用 InnoDB 但无论如何都没有使用它的人。

更多在这里:

How important are constraints like NOT NULL and FOREIGN KEY if I’ll always control my database input with php?

Are foreign keys really necessary in a database design?

Are foreign keys really necessary in a database design?

3. 使用自然而不是代理(技术)主键

自然键是基于外部有意义的数据的键，这些数据(表面上)是唯一的。常见的例子是产品代码、两个字母的州代码(美国)、社会安全号码等。代理或技术主键是那些在系统之外绝对没有意义的键。它们纯粹是为了识别实体而发明的，通常是自动递增的字段(SQL Server、MySQL 等)或序列(最显着的是 Oracle)。

在我看来，你应该 总是 使用代理键。这个问题出现在这些问题中:

How do you like your primary keys?

What's the best practice for primary keys in tables?

Which format of primary key would you use in this situation.

Surrogate vs. natural/business keys

Should I have a dedicated primary key field?

这是一个有点争议的话题，你不会得到普遍的同意。虽然您可能会发现有些人认为自然键在某些情况下是可以的，但除了可以说是不必要的之外，您不会发现对代理键的任何批评。如果你问我，这是一个很小的缺点。

请记住，即使是 countries can cease to exist(例如，南斯拉夫)。

4. 编写需要 DISTINCT 才能工作的查询

您经常在 ORM 生成的查询中看到这一点。查看 Hibernate 的日志输出，您将看到所有查询都以:

SELECT DISTINCT ...

这是确保您不会返回重复行从而获得重复对象的一种快捷方式。你有时也会看到人们这样做。如果你看到太多，那就是一个真正的危险信号。并不是说 DISTINCT 不好或没有有效的应用程序。它确实(在这两个方面)，但它不是编写正确查询的替代品或权宜之计。

从 Why I Hate DISTINCT :

Where things start to go sour in my opinion is when a developer is building substantial query, joining tables together, and all of a sudden he realizes that it looks like he is getting duplicate (or even more) rows and his immediate response...his "solution" to this "problem" is to throw on the DISTINCT keyword and POOF all his troubles go away.

5. 赞成聚合而不是联接

数据库应用程序开发人员的另一个常见错误是没有意识到与连接相比，聚合(即 GROUP BY 子句)要贵多少。

为了让您了解这是多么普遍，我已经在这里写了好几次关于这个主题的文章，并因此受到了很多反对。例如:

从 SQL statement - “join” vs “group by and having” :

First query:

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

Query time: 0.312 s

Second query:

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

Query time: 0.016 s

That's right. The join version I proposed is twenty times faster than the aggregate version.

6. 不通过 View 简化复杂查询

并非所有数据库供应商都支持 View ，但对于那些支持 View 的供应商，如果使用得当，它们可以大大简化查询。例如，在一个项目中，我将 generic Party model 用于 CRM。这是一种极其强大且灵活的建模技术，但会导致许多连接。在这个模型中有:

党 :人和组织；

当事人角色 :当事人所做的事情，例如雇员和雇主；

当事人角色关系 :这些角色之间的关系。

例子:

Ted 是一个 Person，是 Party 的一个子类型；

Ted有很多角色，其中之一是Employee；

Intel是一个组织，是Party的一个子类型；

Intel 有很多角色，其中之一就是雇主；

Intel 聘用了 Ted，这意味着他们各自的角色之间存在关系。

所以有五个表连接起来，将 Ted 与他的雇主联系起来。你假设所有员工都是个人(不是组织)并提供这个助手 View :

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

突然之间，您对所需的数据有了一个非常简单的 View ，但使用的是高度灵活的数据模型。

7. 未净化输入

这是一个巨大的。现在我喜欢 PHP，但是如果您不知道自己在做什么，那么创建容易受到攻击的站点真的很容易。没有什么比 story of little Bobby Tables 更能概括它的了。

用户通过 URL 提供的数据、表单数据 和 cookie 应始终被视为恶意和 sanitizer 。确保你得到你所期望的。

8. 不使用准备好的语句

准备好的语句是当您编译查询时减去插入、更新和 WHERE 子句中使用的数据，然后再提供。例如:

SELECT * FROM users WHERE username = 'bob'

对比

SELECT * FROM users WHERE username = ?

要么

SELECT * FROM users WHERE username = :username

取决于您的平台。

我已经看到数据库因这样做而屈服。基本上，任何现代数据库每次遇到新查询时都必须对其进行编译。如果它遇到一个以前见过的查询，你就给了数据库缓存编译查询和执行计划的机会。通过执行大量查询，您可以让数据库有机会弄清楚并相应地进行优化(例如，通过将已编译的查询固定在内存中)。

使用准备好的语句还将为您提供有关使用某些查询的频率的有意义的统计信息。

准备好的语句还可以更好地保护您免受 SQL 注入(inject)攻击。

9. 标准化不够

Database normalization 基本上是优化数据库设计或如何将数据组织到表中的过程。

就在本周，我遇到了一些代码，其中有人内爆了一个数组并将其插入到数据库中的单个字段中。规范化是将该数组的元素视为子表中的单独行(即一对多关系)。

这也出现在 Best method for storing a list of user IDs 中:

I've seen in other systems that the list is stored in a serialized PHP array.

但规范化的缺乏有多种形式。

更多的:

Normalization: How far is far enough?

SQL by Design: Why You Need Database Normalization

10. 标准化太多

这似乎与前一点相矛盾，但与许多事情一样，规范化是一种工具。它是达到目的的手段，而不是目的本身。我认为许多开发人员忘记了这一点，并开始将“手段”视为“目的”。单元测试就是一个很好的例子。

我曾经在一个系统上工作过，该系统为客户提供了一个巨大的层次结构，类似于:

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

这样您就必须将大约 11 个表连接在一起才能获得任何有意义的数据。这是规范化走得太远的一个很好的例子。

更重要的是，仔细和深思熟虑的非规范化可以带来巨大的性能优势，但在执行此操作时必须非常小心。

更多的:

Why too much Database Normalization can be a Bad Thing

How far to take normalization in database design?

When Not to Normalize your SQL Database

Maybe Normalizing Isn't Normal

The Mother of All Database Normalization Debates on Coding Horror

11. 使用独占弧

排他弧是一种常见的错误，其中一个表是用两个或多个外键创建的，其中一个并且只有一个可以是非空的。 大错误。 一方面，维护数据完整性变得更加困难。毕竟，即使具有参照完整性，也没有什么可以阻止设置两个或多个这些外键(尽管存在复杂的检查约束)。

从 A Practical Guide to Relational Database Design :

We have strongly advised against exclusive arc construction wherever possible, for the good reason that they can be awkward to write code and pose more maintenance difficulties.

12. 根本不做查询性能分析

实用主义至高无上，尤其是在数据库领域。如果你坚持原则到它们已经成为教条的地步，那么你很可能犯了错误。以上面的聚合查询为例。聚合版本可能看起来“不错”，但其性能却很糟糕。性能比较应该结束辩论(但它没有)但更重要的是:首先发表这种不明智的观点是无知的，甚至是危险的。

13. 过度依赖 UNION ALL 尤其是 UNION 结构

SQL 术语中的 UNION 只是连接一致的数据集，这意味着它们具有相同的类型和列数。它们之间的区别在于 UNION ALL 是一个简单的连接，应该尽可能地首选，而 UNION 将隐式执行 DISTINCT 以删除重复的元组。

UNION 和 DISTINCT 一样，都有自己的位置。有有效的应用程序。但是如果你发现自己做了很多事情，特别是在子查询中，那么你可能做错了。这可能是查询构造不当或数据模型设计不当迫使您执行此类操作的情况。

UNION，尤其是在连接或依赖子查询中使用时，可能会削弱数据库。尽量避免它们。

14. 在查询中使用 OR 条件

这可能看起来无害。毕竟，AND 是可以的。或者应该也可以吧？错误的。基本上，AND 条件 限制了 数据集，而 OR 条件 增长了 它但不适合优化。特别是当不同的 OR 条件可能相交时，从而迫使优化器有效地对结果进行 DISTINCT 操作。

坏的:

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

更好的:

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

现在您的 SQL 优化器可以有效地将第一个查询转换为第二个查询。但它可能不会。只是不要这样做。

15. 没有设计他们的数据模型以使其适用于高性能解决方案

这是一个很难量化的点。通常通过其效果观察到。如果您发现自己为相对简单的任务编写了粗糙的查询，或者用于查找相对简单信息的查询效率不高，那么您的数据模型可能很差。

在某些方面，这一点总结了所有较早的内容，但它更像是一个警示故事，即执行诸如查询优化之类的事情通常应该先做，而应该在其次做。首先，在尝试优化性能之前，您应该确保拥有良好的数据模型。正如克努斯所说:

Premature optimization is the root of all evil

16. 数据库事务使用不当

特定进程的所有数据更改都应该是原子的。 IE。如果操作成功，它会完全执行。如果失败，则数据保持不变。 - 不应该有“半完成”的变化的可能性。

理想情况下，实现这一点的最简单方法是整个系统设计应努力通过单个 INSERT/UPDATE/DELETE 语句支持所有数据更改。在这种情况下，不需要特殊的事务处理，因为您的数据库引擎应该自动执行此操作。

但是，如果任何进程确实需要将多个语句作为一个单元执行以保持数据处于一致状态，则需要适当的事务控制。

在第一条语句之前开始一个事务。

在最后一条语句之后提交事务。

出现任何错误，回滚事务。而且很NB!不要忘记跳过/中止错误之后的所有语句。

还建议仔细注意您的数据库连接层如何与数据库引擎在这方面交互的微妙之处。

17. 不理解“基于集合”的范式

SQL 语言遵循适用于特定类型问题的特定范式。尽管有各种特定于供应商的扩展，但该语言仍难以处理 Java、C#、Delphi 等语言中微不足道的问题。

这种缺乏理解表现在几个方面。

不恰本地在数据库上强加过多的程序或命令式逻辑。

不适当或过度使用游标。特别是当一个查询就足够了。

错误地假设触发器在多行更新中影响的每一行触发一次。

确定明确的职责分工，力求用合适的工具解决每一个问题。