example - database design tool




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

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


  1. Using an ORM to do bulk updates
  2. Selecting more data than needed. Again, typically done when using an ORM
  3. Firing sqls in a loop.
  4. Not having good test data and noticing performance degradation only on live data.

  1. 不使用数据库模式的版本控制
  2. 直接针对实时数据库进行工作
  3. 没有阅读和理解更高级的数据库概念(索引,聚集索引,约束,物化视图等)
  4. 未能测试可扩展性......只有3或4行的测试数据永远不会给你真实的现场表演的真实情况

1 - Unnecessarily using a function on a value in a where clause with the result of that index not being used.

例:

where to_char(someDate,'YYYYMMDD') between :fromDate and :toDate

代替

where someDate >= to_date(:fromDate,'YYYYMMDD') and someDate < to_date(:toDate,'YYYYMMDD')+1

And to a lesser extent: Not adding functional indexes to those values that need them...

2 - Not adding check constraints to ensure the validity of the data. Constraints can be used by the query optimizer, and they REALLY help to ensure that you can trust your invariants. There's just no reason not to use them.

3 - Adding unnormalized columns to tables out of pure laziness or time pressure. Things are usually not designed this way, but evolve into this. The end result, without fail, is a ton of work trying to clean up the mess when you're bitten by the lost data integrity in future evolutions.

Think of this, a table without data is very cheap to redesign. A table with a couple of millions records with no integrity... not so cheap to redesign. Thus, doing the correct design when creating the column or table is amortized in spades.

4 - not so much about the database per se but indeed annoying. Not caring about the code quality of SQL. The fact that your SQL is expressed in text does not make it OK to hide the logic in heaps of string manipulation algorithms. It is perfectly possible to write SQL in text in a manner that is actually readable by your fellow programmer.


1.不使用适当的指数

这是一个相对容易的问题,但它始终在发生。 外键应该有索引。 如果您在WHERE使用字段,您应该(可能)有一个索引。 根据您需要执行的查询,这些索引通常应涵盖多列。

2.不强制参照完整性

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

在MySQL数据库上看到这种失败很常见。 我不相信MyISAM支持它。 InnoDB的确如此。 您会发现使用MyISAM的用户或使用InnoDB但尚未使用InnoDB的用户。

更多这里:

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

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

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

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

请记住,即使是国家也不会存在 (例如南斯拉夫)。

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

您经常在ORM生成的查询中看到这一点。 看一下Hibernate的日志输出,你会看到所有的查询都以这个开头:

SELECT DISTINCT ...

这是确保您不返回重复行并因此获取重复对象的快捷方式。 有时你会看到人们也在做这个。 如果你看到它太多,这是一个真正的红旗。 不是DISTINCT不好或没有有效的应用程序。 它确实(在这两个方面),但它不是写出正确查询的替代品或权宜之计。

我为什么讨厌DISTINCT

在我看来,开发人员开始构建实质性查询,将表连接在一起时,突然间,他意识到,他看起来像是获得了重复(甚至更多)的行和他的直接响应......他对这个“问题”的“解决方案”是抛出DISTINCT关键字, POOF所有的麻烦都消失了。

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秒

那就对了。 我建议的连接版本比汇总版本快20倍。

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

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

  • :人民和组织;
  • 党角色 :这些党派所做的事情,例如雇员和雇主;
  • 党角色关系 :这些角色是如何相互关联的。

例:

  • 特德是一个人,是党的一个子类型;
  • 泰德有很多角色,其中之一是员工;
  • 英特尔是一个组织,是党的一个子类型;
  • 英特尔有很多角色,其中之一是雇主;
  • 英特尔雇用泰德,这意味着他们各自的角色之间存在关系。

所以有五张表连接起来,将特德与他的雇主联系起来。 您假定所有员工都是人员(不是组织)并提供此辅助视图:

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 = ?

要么

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,而UNION会隐式执行DISTINCT以删除重复的元组。

联盟,像DISTINCT一样,有它们的位置。 有有效的应用程序。 但是如果你发现自己做了很多,特别是在子查询中,那么你可能做错了什么。 这可能是一个糟糕的查询构造或者设计不好的数据模型,迫使你做这样的事情。

UNION特别适用于连接或从属子查询时,可能会削弱数据库。 尽可能避免使用它们。

14.在查询中使用OR条件

这看起来可能无害。 毕竟,ANDs是好的。 或者应该没问题吧? 错误。 基本上,一个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等语言中微不足道的问题。

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

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

确定明确的责任分工,力求用适当的工具来解决每一个问题。


密钥数据库设计和开发人员编程错误

  • 自私的数据库设计和使用。 开发人员经常将数据库视为其个人持久对象存储,而不考虑数据中其他利益相关者的需求。 这也适用于应用程序架构师。 较差的数据库设计和数据完整性使第三方很难处理数据,并且可能大幅增加系统的生命周期成本。 报告和管理信息系统往往是应用程序设计中的差异表亲,只能作为事后考虑。

  • 滥用非规范化数据。 过度使用非规范化数据并试图在应用程序中维护数据是数据完整性问题的秘诀。 谨慎使用去标准化。 不想将连接添加到查询中不是非规范化的借口。

  • 害怕编写SQL。 SQL不是火箭科学,实际上相当擅长。 O / R映射层非常适合做95%的简单查询,并且很适合该模型。 有时SQL是完成这项工作的最佳方式。

  • 教条式的“无存储程序”政策。 无论您是否认为存储过程是邪恶的,这种教条态度在软件项目中都没有位置。

  • 不理解数据库设计。 规范化是你的朋友,它不是火箭科学。 连接和基数是相当简单的概念 - 如果你参与数据库应用程序的开发,真的没有理由不理解它们。


相关子查询导致的性能不佳

大多数时候你想避免相关的子查询。 如果在子查询中存在对来自外部查询的列的引用,则子查询是相关的。 发生这种情况时,如果在应用包含相关子查询的条件后应用了其他条件,则子查询至少会针对每个返回的行执行一次,并且可能会执行多次。

请原谅这个人为的例子和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上​​建立索引,则第一个查询实际上可以执行得更好。 这不是一个可能的情况,但显示了相关查询如何可能比替代方案更好地执行。

我看到初级开发人员多次关联子查询,并且通常会对性能产生严重影响。 但是,在删除相关的子查询时,务必查看前后的解释计划 ,以确保不会使性能变差。



I hate it when developers use nested select statements or even functions the return the result of a select statement inside the "SELECT" portion of a query.

I'm actually surprised I don't see this anywhere else here, perhaps I overlooked it, although @adam has a similar issue indicated.

例:

SELECT
    (SELECT TOP 1 SomeValue FROM SomeTable WHERE SomeDate = c.Date ORDER BY SomeValue desc) As FirstVal
    ,(SELECT OtherValue FROM SomeOtherTable WHERE SomeOtherCriteria = c.Criteria) As SecondVal
FROM
    MyTable c

In this scenario, if MyTable returns 10000 rows the result is as if the query just ran 20001 queries, since it had to run the initial query plus query each of the other tables once for each line of result.

Developers can get away with this working in a development environment where they are only returning a few rows of data and the sub tables usually only have a small amount of data, but in a production environment, this kind of query can become exponentially costly as more data is added to the tables.

A better (not necessarily perfect) example would be something like:

SELECT
     s.SomeValue As FirstVal
    ,o.OtherValue As SecondVal
FROM
    MyTable c
    LEFT JOIN (
        SELECT SomeDate, MAX(SomeValue) as SomeValue
        FROM SomeTable 
        GROUP BY SomeDate
     ) s ON c.Date = s.SomeDate
    LEFT JOIN SomeOtherTable o ON c.Criteria = o.SomeOtherCriteria

This allows database optimizers to shuffle the data together, rather than requery on each record from the main table and I usually find when I have to fix code where this problem has been created, I usually end up increasing the speed of queries by 100% or more while simultaneously reducing CPU and memory usage.


Not executing a corresponding SELECT query before running the DELETE query (particularly on production databases)!


Not having an understanding of the databases concurrency model and how this affects development. It's easy to add indexes and tweak queries after the fact. However applications designed without proper consideration for hotspots, resource contention and correct operation (Assuming what you just read is still valid!) can require significant changes within the database and application tier to correct later.


The most common mistake I've seen in twenty years: not planning ahead. Many developers will create a database, and tables, and then continually modify and expand the tables as they build out the applications. The end result is often a mess and inefficient and difficult to clean up or simplify later on.


This has been said before, but: indexes, indexes, indexes . I've seen so many cases of poorly performing enterprise web apps that were fixed by simply doing a little profiling (to see which tables were being hit a lot), and then adding an index on those tables. This doesn't even require much in the way of SQL writing knowledge, and the payoff is huge.

Avoid data duplication like the plague. Some people advocate that a little duplication won't hurt, and will improve performance. Hey, I'm not saying that you have to torture your schema into Third Normal Form, until it's so abstract that not even the DBA's know what's going on. Just understand that whenever you duplicate a set of names, or zipcodes, or shipping codes, the copies WILL fall out of synch with each other eventually. It WILL happen. And then you'll be kicking yourself as you run the weekly maintenance script.

And lastly: use a clear, consistent, intuitive naming convention. In the same way that a well written piece of code should be readable, a good SQL schema or query should be readable and practically tell you what it's doing, even without comments. You'll thank yourself in six months, when you have to to maintenance on the tables. "SELECT account_number, billing_date FROM national_accounts" is infinitely easier to work with than "SELECT ACCNTNBR, BILLDAT FROM NTNLACCTS".


a) Hardcoding query values in string
b) Putting the database query code in the "OnButtonPress" action in a Windows Forms application

I have seen both.


不使用参数化查询。 他们在停止SQL注入方面非常方便。

这是另一个答案中提到的不清理输入数据的具体示例。


使用Access而不是“真实”的数据库。 有很多很棒的小型甚至免费的数据库,比如SQL ExpressMySQLSQLite ,它们的工作和扩展都会更好。 应用程序通常需要以意想不到的方式扩展。


使用Excel存储(大量)数据。

我看到公司持有数千行并使用多个工作表(由于Excel以前版本中的行数限制为65535)。

Excel非常适合用于报告,数据演示和其他任务,但不应将其视为数据库。


头号问题? 他们只测试玩具数据库。 所以他们不知道他们的SQL会在数据库变大时抓取,并且稍后有人必须前来修复它(您听到的声音是我的牙齿磨碎)。


忘记建立表格之间的关系。 我记得当我第一次开始在现在的雇主工作时,必须清理这个问题。


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

一些应用程序开发人员将存储过程视为中间层/前端代码的直接扩展。 这似乎是微软堆栈开发人员的一个共同特点,(我是一个人,但我已经长大了),并产生了许多执行复杂业务逻辑和工作流程处理的存储过程。 这在其他地方做得更好。

存储过程在实际证明某些实际技术因素需要使用(例如,性能和安全性)的情况下非常有用。例如,保持大数据集的“接近数据”的聚合/过滤。

我最近不得不帮助维护和增强一个庞大的Delphi桌面应用程序,其中70%的业务逻辑和规则是在1400个SQL Server存储过程中实现的(其余部分在UI事件处理程序中)。 这是一场噩梦,主要原因在于向TSQL引入了有效的单元测试,缺乏封装和糟糕的工具(调试器,编辑器)。

在过去与一个Java团队合作的过程中,我很快发现,在这种环境下,情况往往完全相反。 一位Java架构师曾告诉我:“数据库用于数据,而不是代码。”

现在我认为根本不考虑存储过程是一个错误,但是在它们提供有用的好处的情况下(请参阅其他答案),应该谨慎使用它们(不是默认情况下)。


  • Dismissing an ORM like Hibernate out of hand, for reasons like "it's too magical" or "not on my database".
  • Relying too heavily on an ORM like Hibernate and trying to shoehorn it in where it isn't appropriate.




database-design