logo标签列表

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

databasedatabase-design
566

应用程序开发人员常犯的数据库开发错误是什么？

与https://dev59.com/WHRC5IYBdhLWcg3wS_AF非常相似的内容。 - dkretz
40个回答
1002

1. 不使用合适的索引

这是相对容易解决，但仍然经常发生的问题。外键应该建立索引。如果你在WHERE中使用了一个字段，你应该（很可能）为其建立索引。这样的索引通常应该覆盖多个列，以便执行所需的查询。

2. 不强制执行参照完整性

你的数据库可能不同，但如果你的数据库支持参照完整性——也就是说，所有的外键都保证指向一个存在的实体——你应该使用它。

在MySQL数据库上看到这种失败是很常见的。我不认为MyISAM支持它。InnoDB支持它。你会发现有些人使用MyISAM，或者使用InnoDB，但还是没有使用它。

更多信息：

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

自然键是基于外部有意义的数据的键，这些数据（表面上）是唯一的。常见的例子是产品代码、两位字母的州代码（美国）、社会安全号码等等。代理或技术主键是完全没有意义的，它们仅仅为了标识实体而被发明，通常是自动增加的字段（SQL Server、MySQL等）或序列（最著名的是Oracle）。

在我看来，你应该始终使用代理键。这个问题在以下问题中已经出现过：

这是一个有争议的话题，关于这个问题不可能得到所有人的一致意见。虽然你可能会找到一些人认为在某些情况下自然键是可以使用的，但你不会看到除了被认为是无必要性以外对代理键的任何批评。如果你问我，这是一个非常小的缺点。

记住，即使国家可能会停止存在（例如南斯拉夫），这也是可能的。

4.编写需要使用DISTINCT的查询

通常在ORM生成的查询中看到这种情况。查看Hibernate的日志输出，您会看到所有查询都以以下开头：

SELECT DISTINCT ...

这是一种确保不返回重复行以避免获取重复对象的捷径。有时你也会看到其他人这样做。如果你看到这种情况过于频繁，那么这是一个真正的警示信号。并不是说DISTINCT是坏的或没有有效应用。它确实有（两方面都有），但它不是编写正确查询的替代品或临时方案。

来自Why I Hate DISTINCT:

在我看来，事情开始变得糟糕的地方是，当开发人员构建大量查询、连接表时，突然意识到它“看起来”像他正在获取重复（甚至更多）的行，他的立即反应......他的“解决方案”是添加 DISTINCT 关键字，所有问题都消失了。

5. 偏爱聚合而非连接

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

为了让您了解这种情况有多普遍，我在这个主题上写了几次，并因此被下降了很多。例如：

来自SQL 语句 - “join” vs “group by and having”:

第一个查询：

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

查询时间: 0.312 秒

第二个查询:

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秒

没错。我提出的联接版本比聚合版本快二十倍。

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

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

  • Party：人和组织；
  • Party Role：这些角色扮演的事情，例如员工和雇主；
  • Party Role Relationship：这些角色彼此之间的关系。

例如：

  • Ted 是一个人，是 Party 的子类型；
  • Ted 有很多角色，其中一个是员工；
  • 英特尔是一个组织，是 Party 的子类型；
  • 英特尔有很多角色，其中一个是雇主；
  • 英特尔雇用 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'

VS

SELECT * FROM users WHERE username = ?
or
SELECT * FROM users WHERE username = :username

根据你的平台而定。

我见过由于这样做而使数据库崩溃的情况。基本上，每当任何现代数据库遇到新查询时，它都必须编译它。如果它遇到了以前见过的查询，那么你就给了数据库缓存已编译的查询和执行计划的机会。通过频繁查询，你给了数据库发现并相应地优化的机会（例如，将已编译的查询固定在内存中）。

使用预处理语句还将为你提供有关某些查询使用频率的有意义的统计信息。

预处理语句还将更好地保护你免受SQL注入攻击。

9. 规范化不够

数据库规范化 基本上是优化数据库设计或将数据组织成表格的过程。

就在本周，我看到有人将一个数组implode后插入到数据库的单个字段中。规范化的方法是把该数组的每个元素作为子表中的一个单独的行来处理（即一对多关系）。

这也出现在存储用户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是可以的。OR也应该可以，对吧？错了。基本上，AND条件限制数据集，而OR条件扩展数据集，但不适合优化。特别是当不同的OR条件可能相交，从而强制优化器在结果上执行DISTINCT操作时。

不好：

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

更好的：

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

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

15. 数据模型设计不足以支持高性能解决方案

这是一个难以量化的问题，通常通过其效果来观察。如果您发现自己为相对简单的任务编写复杂的查询或者用于查找相对简单信息的查询不够高效，则可能有一个糟糕的数据模型。

在某种程度上，这一点总结了之前的所有内容，但更多的是一个警示故事：像查询优化这样的事情通常应该首先完成，而不是第一件事。首要的是确保您拥有一个良好的数据模型，然后再尝试优化性能。正如Knuth所说：

过早的优化是万恶之源

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

特定进程的所有数据更改应该是原子的。也就是说，如果操作成功，它应该完全成功。如果失败，数据应该不变。- 不应该出现“半成品”更改的可能性。

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

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

  • 在第一条语句之前开始一个事务。
  • 在最后一条语句之后提交事务。
  • 在任何错误发生时，回滚事务。非常重要！ 不要忘记跳过/中止错误后的所有语句。

同时建议注意关注数据库连接层和数据库引擎在这方面相互交互的细微差别。

17. 不理解“集合”范例

SQL语言遵循特定的范例，适用于特定类型的问题。除了各种供应商特定的扩展之外，该语言难以处理在Java、C＃、Delphi等语言中很普通的问题。

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

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

确定明确的职责划分，并努力使用适当的工具来解决每个问题。

9
关于MySQL外键语句，您说得没错，MyISAM不支持它们，但是您暗示仅使用MyISAM是糟糕的设计。我之所以使用MyISAM是因为InnoDB不支持全文搜索，我认为这并不是不合理的。 - Elle H
5
完全不同意第三点。是的，国家可能会消失，但是国家代码仍将代表相同的事物。货币代码或美国州也是如此。在这些情况下使用代理键很愚蠢，会在查询中创建更多开销，因为您必须包括一个额外的联接。我认为，对于用户特定的数据（因此不包括国家，货币和美国州），最好使用代理键。 - Thomas
1
回复：#11 强制执行数据完整性所需的检查约束条件是微不足道的。避免该设计的其他原因是存在的，但需要“复杂”的检查约束条件并不是其中之一。 - Thomas
2
使用＃3并不诚实。人工键的缺点不仅仅是“您可能不需要它”。具体而言，使用自然键将使您能够控制表中数据写入磁盘的顺序。如果您知道如何查询表格，则可以对其进行索引，以便同时访问的行最终位于同一页中。此外，您可以使用唯一的复合索引强制执行数据完整性。如果需要此功能，则必须将其添加到人工键索引之外。如果该复合索引是您的pkey，则可以一举两得。 - Shane H
1
@Greg：如果连接是内连接且没有userid、roleid重复，则两个查询将产生完全相同的结果集。1）此查询过滤roleid IN（1,2,3），并按userid计算它们的数量。HAVING子句仅筛选拥有所有3个角色的用户。2）此查询通过userid对每个角色的用户列表进行连接。INNER JOINS表示任何缺少任一角色的用户都将被排除。因此，最终列表仅包含拥有所有3个角色的用户。 - Disillusioned
显示剩余19条评论
110

开发人员常犯的关键数据库设计和编程错误

  • 自私的数据库设计和使用。开发人员经常将数据库视为自己的个人持久对象存储，而不考虑其他利益相关者在数据方面的需求。应用程序架构师也是如此。糟糕的数据库设计和数据完整性使得与数据一起工作的第三方变得困难，并且可能大大增加系统的生命周期成本。报告和MIS往往在应用程序设计中表现不佳，只是作为事后的想法。

  • 滥用非规范化数据。过度使用非规范化数据并试图在应用程序中维护它会导致数据完整性问题。请谨慎使用非规范化数据。不想在查询中添加连接符不是非规范化的借口。

  • 害怕编写SQL语句。 SQL并不是高深莫测的玄学，实际上在完成其工作时非常出色。 O/R映射层非常擅长处理95％的简单查询，并且很好地适配了这种模型。有时，SQL是完成工作的最佳方式。

  • 教条式的“不使用存储过程”策略。无论您是否认为存储过程是邪恶的，这种教条主义的态度都不适用于软件项目。

  • 不理解数据库设计。规范化是您的朋友，它并不高深莫测。连接和基数是相当简单的概念-如果您参与数据库应用程序开发，那么真的没有理由不了解它们。

2
有人可能会认为，事务应该在事务性数据库中完成，而报告和MIS应该在单独的分析数据库中完成。因此，您可以获得最佳的两个世界，并且每个人都很高兴（除了那些必须编写数据转换脚本以从前者构建后者的可怜人）。 - Chris Simpson
不仅是编写ETL的可怜人 - 任何使用系统数据的人，都会遇到MIS应用程序中质量差的数据问题，因为几个关键关系实际上没有在源头记录下来。所有参与无休止的调和冲突的人都会受到低质量数据带来的影响。 - ConcernedOfTunbridgeWells
我完全不同意第一点。数据库是用于持久化的，而不是用于进程间通信的。通常有更好的解决方案来解决这个问题。除非有明确的要求，否则您应该将数据库视为只有您的应用程序会使用它。即使有明确的要求，也要对其进行用户故事和根本原因分析，您很可能会发现填充请求者意图的更好方法。再说一遍，我在一家公司工作，其中CQRS这个短语相当普遍。 - George Mauer
3
简单例子：我有一个保险单管理系统，需要将500万份索赔的状态加载到转让再保险系统中，以计算可能的赔偿。这些系统都是旧的客户端-服务器COTS软件包，设计用于与更旧的大型机系统交互。为了财务控制目的，必须对两者进行调整。这项工作每月完成一次。按照您的逻辑，我会编写一系列用户故事来定义要求，并要求供应商报价添加Web服务包装器到其现有产品中。 - ConcernedOfTunbridgeWells
然而，我在那些常用“审计”、“金融服务机构监管局”和“萨班斯-奥克斯利法案”等短语的公司工作。 - ConcernedOfTunbridgeWells
2
那么你的数据库管理员要么懒惰，要么无能。 - ConcernedOfTunbridgeWells
80
  1. 不使用版本控制管理数据库架构
  2. 直接操作实时数据库
  3. 没有阅读和理解更高级的数据库概念（索引、聚集索引、约束、物化视图等）
  4. 未测试可扩展性...仅使用3或4行的测试数据无法真正反映实际性能。
1
我强烈支持第一点和第二点。每当我对数据库进行更改时，我都会转储其模式并对其进行版本控制；我设置了三个数据库，一个开发数据库，一个暂存数据库和一个生产数据库 - 绝不会在生产数据库上进行“测试”！！ - Ixmatus
46

过度使用和/或依赖存储过程。

一些应用程序开发人员认为存储过程是中间层/前端代码的直接扩展。这似乎是微软堆栈开发人员的共同特点（我是其中之一，但我已经摆脱了这种思维方式），并会产生许多执行复杂业务逻辑和工作流处理的存储过程。这个最好在其他地方完成。

当某些真正的技术因素必须要求它们的使用（例如性能和安全性）时，存储过程是有用的。例如，将大数据集的聚合/过滤“靠近数据”。

最近，我不得不帮助维护和增强一个大型 Delphi 桌面应用程序，其中 70% 的业务逻辑和规则是在 1400 个 SQL Server 存储过程中实现的（其余在 UI 事件处理程序中）。这是一场噩梦，主要是由于很难将有效的单元测试引入到 TSQL 中，缺乏封装和较差的工具（调试器、编辑器）。

以前曾与 Java 团队合作，我很快发现在那个环境中通常完全相反。一位 Java 架构师曾告诉我：“数据库是用于存储数据，而不是代码。”

现在我认为完全不考虑存储过程是一个错误，但应该谨慎使用（不是默认情况下），在它们提供有用的好处的情况下使用（请参见其他答案）。

4
存储过程在使用时往往会成为项目中的痛点，因此一些开发人员制定了“不使用存储过程”的规则。因此看起来它们之间存在一种公开的冲突。你的答案将有助于阐明何时实际上应选择其中一种方法。 - Warren P
好处：安全性 - 您不必给应用程序“删除* from…”的能力；调整 - DBA可以在不重新编译/部署整个应用程序的情况下调整查询；分析 - 在数据模型更改后重新编译一堆过程以确保它们仍然有效很容易；最后，考虑到SQL是由数据库引擎执行的（而不是您的应用程序），那么“数据库是用于数据而不是代码”的概念就是愚蠢的。 - NotMe
所以，你会将业务逻辑与用户界面混在一起，而与被操作的数据分离开来？这似乎不是一个好主意，特别是当数据操作由数据库服务器执行时，效率最高，而不是通过用户界面的往返传输。这也意味着更难控制应用程序，因为你不能依赖数据库对其数据进行控制，并且可能存在具有不同数据操作的不同版本的用户界面。这不好。除了通过存储过程，我不允许任何东西接触我的数据。 - David T. Macknet
如果需要将业务逻辑与用户界面分离，可以使用多层架构。或者，使用带有业务对象和逻辑的库，被不同的应用程序/用户界面使用。存储过程将您的数据/业务逻辑锁定到特定数据库中，在这种情况下更改数据库非常昂贵。而巨大的成本是不好的。 - too
@too：在大多数情况下，更改数据库的成本非常高昂。更不用说失去特定DBMS提供的性能和安全功能的想法了。此外，额外的层会增加复杂性并降低性能，而额外的层与您特定的语言相关联。最后，使用的语言更有可能发生变化，而不是数据库服务器。 - NotMe
41

首要问题是什么？他们只在玩具数据库上进行测试。所以当数据库变得庞大时，他们不知道他们的SQL会变得缓慢，最终需要有人来修复它（你可以听到我咬牙切齿的声音）。

2
数据库的大小是相关的，但更大的问题是负载——即使您在真实数据集上进行测试，也无法测试查询在数据库处于生产负载下时的性能，这可能会让您大吃一惊。 - davidcl
我认为数据库大小比负载更重要。我曾经看到过很多次，缺少关键索引 - 在测试期间从未出现性能问题，因为整个数据库都适合内存。 - Danubian Sailor
31

未使用索引。

28

相关子查询引起的性能问题

大部分情况下，应避免使用相关子查询。如果在子查询中引用了外部查询的列，则该子查询是相关的。当这种情况发生时，子查询至少会为每个返回的行执行一次，并且如果在包含相关子查询的条件之后应用其他条件，则可能会执行更多次。

请原谅这个牵强的例子和Oracle语法，但假设您想找到所有自上次商店销售额不足$10,000以来在任何商店被雇用的员工。

select e.first_name, e.last_name
from employee e
where e.start_date > 
        (select max(ds.transaction_date)
         from daily_sales ds
         where ds.store_id = e.store_id and
               ds.total < 10000)

这个例子中的子查询通过store_id与外部查询相关联，将为系统中的每个员工执行一次。优化此查询的一种方法是将子查询移动到内联视图中。
select e.first_name, e.last_name
from employee e,
     (select ds.store_id,
             max(s.transaction_date) transaction_date
      from daily_sales ds
      where ds.total < 10000
      group by s.store_id) dsx
where e.store_id = dsx.store_id and
      e.start_date > dsx.transaction_date

在这个示例中，from子句中的查询现在是一个内联视图（再次是Oracle特定语法），只执行一次。根据数据模型的不同，这个查询可能会更快地执行。随着员工数量的增加，它比第一个查询表现得更好。如果有少量员工和许多商店（也许许多商店没有员工）并且daily_sales表按store_id索引，第一个查询实际上可能表现得更好。这不是一个常见的场景，但显示了相关查询如何可能比替代方案表现得更好。
我看到过初级开发人员多次关联子查询，通常对性能有严重影响。然而，在删除相关子查询之前，请务必查看解释计划，以确保您不会使性能变差。
1
很好的观点，强调您相关观点之一 - 测试您的更改。学习使用解释计划（查看数据库实际执行查询所做的操作以及它的成本），在大数据集上进行测试，并且不要使SQL过于复杂、难以阅读/难以维护，以优化真正提高性能的方案。 - Rob Whelan
21

根据我的经验：
不与有经验的数据库管理员进行沟通。

17

使用 Access 而非“真正”的数据库。有许多出色的小型甚至免费的数据库，如SQL Express、MySQL和SQLite，它们能够更好地进行工作和扩展。应用程序常常需要以意想不到的方式扩展。

16

忘记在表之间设置关联。我记得当我第一次在目前的雇主工作时，必须清理这个问题。

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接