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

Question

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

Answer 1

1. 没有使用适当的索引

这是一个相对容易的事情，但它仍然一直在发生。外键应该有索引。如果你在 a
中使用一个字段，WHERE你应该（可能）在它上面有一个索引。根据您需要执行的查询，此类索引通常应涵盖多个列。

2. 不强制参照完整性

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

在 MySQL 数据库上看到这种故障是很常见的。我不相信 MyISAM 支持它。InnoDB 可以。您会发现使用 MyISAM 的人或使用 InnoDB
但无论如何都没有使用它的人。

3. 使用自然而不是代理（技术）主键

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

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

请记住，即使是国家也可能不复存在（例如，南斯拉夫）。

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

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

SELECT DISTINCT ...

这是确保您不会返回重复行并因此获得重复对象的捷径。你有时也会看到人们这样做。如果你看到它太多，这是一个真正的危险信号。这不是DISTINCT坏事或没有有效的应用程序。它确实（在这两个方面），但它不是编写正确查询的替代品或权宜之计。

从我为什么讨厌
DISTINCT：

在我看来，事情开始变得糟糕的地方是当开发人员正在构建大量查询，将表连接在一起时，突然间他意识到他 似乎
正在获得重复（甚至更多）行并且他的即时响应......他对这个“问题”的“解决方案”是使用 DISTINCT 关键字，然后 POOF
他所有的麻烦都消失了。

5. 聚合优于连接

数据库应用程序开发人员的另一个常见错误是没有意识到GROUP BY可以将聚合（即子句）与连接相比要昂贵得多。

为了让您了解它的广泛性，我在这里写了几次关于这个主题的文章，并因此而被否决了很多。例如：

来自SQL 语句 - “oin” vs “roup by andhave”：

第一个查询：

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

查询时间：0.312 s

第二个查询：

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

这是正确的。我提出的加入版本 比聚合版本快二十倍。

6.不通过视图简化复杂查询

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

• 党 ：个人和组织；
• 当事人角色 ：当事人所做的事情，例如雇员和雇主；
• 当事人角色关系 ：这些角色如何相互关联。

例子：

• Ted 是 Person，是 Party 的子类型；
• Ted 有很多角色，其中之一是 Employee；
• 英特尔是一个组织，是一方的子类型；
• 英特尔有很多角色，其中之一是雇主；
• 英特尔聘用了 Ted，这意味着他们各自的角色之间存在关系。

因此，连接了五张表以将 Ted 与他的雇主联系起来。您假设所有员工都是人员（而不是组织）并提供此帮助视图：

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. 标准化不够

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

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

这也出现在存储用户 ID 列表的最佳方法中：

我在其他系统中看到该列表存储在序列化的 PHP 数组中。

但缺乏标准化有多种形式。

10. 过度规范化

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

我曾经开发过一个系统，它为客户提供了一个巨大的层次结构，类似于：

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

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

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

11. 使用专属弧线

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

从关系数据库设计实用指南

我们强烈建议尽可能不要使用独占的弧形构造，因为它们编写代码会很尴尬并且会带来更多的维护困难。

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. 没有设计他们的数据模型来适应高性能的解决方案

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

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

过早的优化是万恶之源

16. 数据库事务的错误使用

特定进程的所有数据更改都应该是原子的。即如果操作成功，它会完全这样做。如果失败，则数据保持不变。- 不应该有“半途而废”的变化。

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

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

• 在第一个语句之前开始一个事务。
• 在最后一条语句之后提交事务。
• 出现任何错误时，回滚事务。而且很NB！不要忘记跳过/中止错误之后的所有语句。

还建议仔细注意您的数据库连接层和数据库引擎在这方面如何交互的细节。

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

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

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

• 对数据库不恰当地施加过多的程序或命令逻辑。
• 不恰当或过度使用游标。特别是当一个查询就足够了。
• 错误地假设触发器在多行更新中受影响的每行触发一次。

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