应用程序开发人员常见的数据库开发错误是什么？
1.没有使用适当的指数
这是一个相对容易的,但它仍然会一直发生.外键应该有索引.如果你在a中使用某个字段,WHERE你应该(可能)有一个索引.此类索引通常应根据您需要执行的查询覆盖多个列.
2.不强制参照完整性
您的数据库可能会有所不同,但如果您的数据库支持参照完整性 - 意味着所有外键都保证指向存在的实体 - 您应该使用它.
在MySQL数据库上看到这种失败是很常见的.我不相信MyISAM支持它.InnoDB.您会发现使用MyISAM的人或使用InnoDB但未使用它的人.
更多信息:
如果我总是用php控制我的数据库输入,那么像NOT NULL和FOREIGN KEY这样的约束有多重要？
外键在数据库设计中是否真的必要？
外键在数据库设计中是否真的必要？
3.使用自然而非代理(技术)主键
自然键是基于(表面上)唯一的外部有意义数据的键.常见的例子是产品代码,双字母州代码(US),社会安全号码等.代理或技术主键是那些在系统外绝对没有意义的主键.它们纯粹是为了识别实体而发明的,通常是自动递增字段(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模型.这是一种非常强大且灵活的建模技术,但可以导致许多连接.在这个模型中有:
党:人民和组织;
党的角色:这些政党所做的事情,例如雇员和雇主;
派对角色关系:这些角色如何相互关联.
例:
泰德是一个人,是党的一个子类型;
特德有很多角色,其中一个是员工;
英特尔是一个组织,是党的子类型;
英特尔有很多角色,其中一个是雇主;
英特尔聘请泰德,这意味着他们各自的角色之间存在关系.
因此,有五个表连接到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 = ?
要么
SELECT * FROM users WHERE username = :username
取决于您的平台.
通过这样做,我看到数据库瘫痪了.基本上,每次任何现代数据库遇到新查询时,都必须编译它.如果它遇到了之前看到的查询,那么您将为数据库提供缓存已编译查询和执行计划的机会.通过大量执行查询,您可以为数据库提供相应优化的机会(例如,通过将已编译的查询固定在内存中).
使用预准备语句还可以为您提供有关某些查询使用频率的有意义的统计信息.
准备好的语句也可以更好地保护您免受SQL注入攻击.
9.没有足够的正常化
数据库规范化基本上是优化数据库设计或如何将数据组织到表中的过程.
就在本周,我遇到了一些代码,其中有人破坏了一个数组并将其插入到数据库中的单个字段中.规范化可以将该数组的元素视为子表中的单独行(即一对多关系).
这也出现在存储用户ID列表的Best方法中:
我在其他系统中看到列表存储在序列化的PHP数组中.
但缺乏正常化有多种形式.
更多:
正常化:距离足够远？
SQL by Design:为什么需要数据库规范化
10.正常化太多了
这似乎与前一点相矛盾,但正常化与许多事情一样,是一种工具.它是达到目的的手段,而不是目的本身.我认为许多开发人员忘记了这一点并开始将"手段"视为"结束".单元测试就是一个很好的例子.
我曾经在一个系统上工作,这个系统对于客户来说具有巨大的层次结构,例如:
Licensee -> Dealer Group -> Company -> Practice -> ...
这样你就可以在获得任何有意义的数据之前加入大约11个表.这是规范化过程的一个很好的例子.
更重要的是,谨慎和考虑的非规范化可以带来巨大的性能优势,但在执行此操作时必须非常小心.
更多:
为什么过多的数据库规范化可能是一件坏事
在数据库设计中需要多少程度的规范化？
何时不规范SQL数据库
也许正常化不正常
关于编码恐怖的所有数据库规范化之母的争论
11.使用独占弧
独占弧是一种常见错误,其中使用两个或多个外键创建表,其中只有一个外键可以为非空. 大错. 一方面,维护数据完整性变得更加困难.毕竟,即使使用引用完整性,也没有任何东西阻止设置这些外键中的两个或多个(尽管有复杂的检查约束).
从关系数据库设计的实用指南:
我们强烈建议尽可能不使用独有的弧形结构,因为它们很难编写代码并造成更多的维护困难.
12.根本不对查询进行性能分析
实用主义至高无上,特别是在数据库世界中.如果你坚持原则,以至于他们已经成为教条,那么你很可能会犯错误.以上面的聚合查询为例.聚合版本可能看起来"不错",但其性能很糟糕.绩效比较应该结束辩论(但事实并非如此),但更重要的是:首先喷出这些不明智的观点是无知的,甚至是危险的.
13.过度依赖UNION ALL,特别是UNION结构
SQL术语中的UNION仅连接全等数据集,这意味着它们具有相同类型和数量的列.它们之间的区别在于UNION ALL是一个简单的连接,应尽可能首选,而UNION将隐式执行DISTINCT以删除重复的元组.
像DISTINCT一样,UNION也有自己的位置.有效的应用程序.但是如果你发现自己做了很多,特别是在子查询中,那么你可能做错了.这可能是一个糟糕的查询构造或设计不良的数据模型迫使你做这些事情的情况.
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语句支持所有数据更改.在这种情况下,不需要特殊的事务处理,因为您的数据库引擎应该自动执行.
但是,如果任何进程确实需要将多个语句作为一个单元执行以使数据保持一致状态,则必须进行适当的事务控制.
在第一个语句之前开始一个事务.
在最后一个语句之后提交事务.
在任何错误上,回滚事务.非常NB!不要忘记跳过/中止错误后面的所有语句.
还建议您仔细关注数据库连接层和数据库引擎如何在这方面进行交互的子网站.
17.不理解"基于集合"的范式
SQL语言遵循适用于特定类型问题的特定范例.尽管有各种特定于供应商的扩展,但语言仍在努力处理Java,C#,Delphi等语言中的微不足道的问题.
这种缺乏理解在某些方面表现出来.
在数据库上不恰当地施加过多的程序或命令逻辑.
不适当或过度使用游标.特别是当单个查询就足够了.
错误地假设在多行更新中每行触发一次触发器.
确定明确的责任分工,并努力使用适当的工具来解决每个问题.
开发人员犯下的关键数据库设计和编程错误
自私的数据库设计和使用. 开发人员经常将数据库视为其个人持久对象存储库,而不考虑数据中其他利益相关者的需求.这也适用于应用程序架构师.糟糕的数据库设计和数据完整性使第三方难以处理数据,并且可能大大增加系统的生命周期成本.报告和MIS往往是应用程序设计中的一个表兄弟,只是作为事后的想法.
滥用非规范化数据.过度使用非规范化数据并尝试在应用程序中维护它是数据完整性问题的一个方法.谨慎使用非规范化.不希望向查询添加连接不是非规范化的借口.
害怕写SQL. SQL不是火箭科学,实际上非常擅长完成它的工作.O/R映射层非常擅长95%的简单查询并且非常适合该模型.有时SQL是完成这项工作的最佳方式.
Dogmatic'无存储程序'政策. 无论你是否认为存储过程是邪恶的,这种教条式的态度在软件项目中都没有.
不了解数据库设计. 规范化是你的朋友,它不是火箭科学. 连接和基数是相当简单的概念 - 如果您参与数据库应用程序开发,那么就没有理由不理解它们.
不在数据库模式上使用版本控制
直接针对实时数据库
不读取和理解更高级的数据库概念(索引,聚簇索引,约束,物化视图等)
未能测试可扩展性...只有3或4行的测试数据永远不会给你真实的现场表现
过度使用和/或依赖存储过程.
一些应用程序开发人员将存储过程视为中间层/前端代码的直接扩展.这似乎是Microsoft堆栈开发人员的一个共同特征(我只是一个,但我已经成长了),并生成了许多执行复杂业务逻辑和工作流处理的存储过程.在其他地方做得更好.
在已经证实某些真正的技术因素需要使用它们(例如,性能和安全性)的情况下,存储过程是有用的.例如,保持大数据集的聚合/过滤"接近数据".
我最近不得不帮助维护和增强一个大型Delphi桌面应用程序,其中70%的业务逻辑和规则是在1400个SQL Server存储过程中实现的(其余部分在UI事件处理程序中).这是一场噩梦,主要是由于对TSQL引入有效的单元测试,缺乏封装和糟糕的工具(调试器,编辑器).
过去与Java团队合作,我很快发现在这种环境中通常完全相反.Java架构师曾告诉我:"数据库用于数据,而不是代码."
这些天我认为根本不考虑存储过程是错误的,但是在它们提供有用的好处的情况下应该谨慎使用它们(不是默认)(参见其他答案).
头号问题？他们只测试玩具数据库.因此,当数据库变大时,他们不知道他们的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)
The subquery in this example is correlated to the outer query by the store_id and would be executed for every employee in your system. One way that this query could be optimized is to move the subquery to an inline-view.
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
In this example, the query in the from clause is now an inline-view (again some Oracle specific syntax) and is only executed once. Depending on your data model, this query will probably execute much faster. It would perform better than the first query as the number of employees grew. The first query could actually perform better if there were few employees and many stores (and perhaps many of stores had no employees) and the daily_sales table was indexed on store_id. This is not a likely scenario but shows how a correlated query could possibly perform better than an alternative.
I've seen junior developers correlate subqueries many times and it usually has had a severe impact on performance. However, when removing a correlated subquery be sure to look at the explain plan before and after to make sure you are not making the performance worse.
根据我的经验:
不与经验丰富的DBA沟通.
使用Access而不是"真正的"数据库.有很多很棒的小型甚至免费的数据库,如SQL Express,MySQL和SQLite,它们可以更好地工作和扩展.应用通常需要以意想不到的方式扩展.
忘记在表之间建立关系.我记得当我第一次开始在现任雇主工作时必须清理它.
我想补充一点:在高性能代码上支持"优雅"代码.对应用程序开发人员来说,最适合数据库的代码通常很难看.
相信那些过早优化的废话.数据库必须考虑原始设计和任何后续开发中的性能.在我看来,性能是数据库设计的50%(40%是数据完整性,最后10%是安全性).一旦真实用户和实际流量针对数据库放置,那么从下到上执行的数据库将执行不良.过早优化并不意味着没有优化!这并不意味着您应该编写几乎总是表现不佳的代码,因为您会发现它更容易(例如,除非所有其他方法都失败,否则永远不应该在生产数据库中使用游标).这意味着在您需要之前,您不需要考虑挤出最后一点性能.关于什么在数据库上表现更好是众所周知的,在设计和开发中忽略这一点至多是短视的.
使用Excel存储(大量)数据.
我见过公司持有数千行并使用多个工作表(由于以前版本的Excel的行限制为65535).
Excel非常适合报表,数据表示和其他任务,但不应将其视为数据库.
不使用参数化查询.它们在停止SQL注入方面非常方便.
这是另一个答案中提到的不清理输入数据的具体示例.
对于基于SQL的数据库:
没有利用CLUSTERED INDEXES或选择错误的列到CLUSTER.
不使用SERIAL(autonumber)数据类型作为PRIMARY KEY来连接父/子表关系中的FOREIGN KEY(INT).
当许多记录被INSERTED或DELETED时,不会在表上更新统计信息.
在插入或删除多行时,不重组(即卸载,删除,重新创建,加载和重新索引)表(某些引擎在物理上将删除的行保留在带有删除标志的表中.)
不利用具有高事务率的大型表上的FRAGMENT ON EXPRESSION(如果支持).
为列选择错误的数据类型!
没有选择合适的列名.
不在表的末尾添加新列.
没有创建适当的索引来支持常用的查询.
在具有少量可能值的列上创建索引并创建不必要的索引.
......还有更多要补充.
当开发人员使用嵌套的select语句甚至函数时,我讨厌在查询的"SELECT"部分内返回select语句的结果.
我真的很惊讶我在这里的任何其他地方都没有看到这个,也许我忽略了它,尽管@adam也有类似的问题.
例:
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
在这种情况下,如果MyTable返回10000行,结果就好像查询只运行了20001个查询,因为它必须运行初始查询并为每行结果查询每个其他表一次.
开发人员可以在开发环境中工作,他们只返回几行数据,子表通常只有少量数据,但在生产环境中,这种查询可能会成倍增加成本数据被添加到表中.
一个更好(不一定完美)的例子是这样的:
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
这允许数据库优化器将数据混合在一起,而不是从主表中重新查询每个记录,我通常会发现当我必须修复创建此问题的代码时,我通常最终会将查询速度提高100%或者更多同时减少CPU和内存使用量.
在修复生产数据库中的某些问题之前不进行备份.
在存储过程中对存储对象(如表,视图)使用DDL命令.
害怕使用存储过程或害怕使用ORM查询,无论哪个更有效/适合使用.
忽略数据库分析器的使用,它可以告诉您最终将ORM查询转换为什么,从而验证逻辑,甚至在不使用ORM时进行调试.
没有做正确的规范化水平.您希望确保数据不重复,并且您要根据需要将数据拆分为不同的数据.你还需要确保你没有关注正常化过远,这将影响性能.
将数据库视为一种存储机制(即美化的集合库),从而从属于其应用程序(忽略共享数据的其他应用程序)
1 -不必使用where子句中的值的函数,并且未使用该索引的结果.
例:
where to_char(someDate,'YYYYMMDD') between :fromDate and :toDate
代替
where someDate >= to_date(:fromDate,'YYYYMMDD') and someDate < to_date(:toDate,'YYYYMMDD')+1
在较小程度上:不向那些需要它们的值添加功能索引......
2 -不添加检查约束以确保数据的有效性.查询优化器可以使用约束,它们确实有助于确保您可以信任不变量.没有理由不使用它们.
3 -在纯懒惰或时间压力下向表中添加非标准化列.事情通常不是这样设计的,而是演变成这个.最终的结果是,当你在未来的演变中被丢失的数据完整性所困扰时,试图清理混乱的大量工作.
想一想,没有数据的表是非常便宜的重新设计.一张表有几百万条没有完整性的记录......重新设计并不那么便宜.因此,在创建列或表时执行正确的设计将在黑桃中摊销.
4 -与数据库本身无关,但实际上很烦人.不关心SQL的代码质量.您的SQL以文本表达的事实并不能将逻辑隐藏在字符串操作算法的堆中.完全可以以一种实际上可由您的程序员读取的方式在文本中编写SQL.
由于诸如"它太神奇"或"不在我的数据库"之类的原因而无法解除像Hibernate这样的ORM .
过分依赖像Hibernate这样的ORM,并试图在不合适的地方进行操作.
之前已经说过,但是: 索引,索引,索引.我见过很多表现不佳的企业网络应用程序,通过简单地进行一些分析(查看哪些表被大量搜索),然后在这些表上添加索引来修复.这甚至不需要SQL编写知识的方式,并且收益很大.
避免像瘟疫一样的数据重复.有些人主张一点点重复不会伤害,并会提高性能.嘿,我不是说你必须将你的模式折磨成第三范式,直到它如此抽象,甚至连DBA都不知道发生了什么.只需理解,无论何时复制一组名称,邮政编码或运输代码,副本最终都会彼此不同步.它会发生.然后,当您运行每周维护脚本时,您将自己踢.
最后:使用清晰,一致,直观的命名约定.就像编写良好的代码片段应该是可读的一样,一个好的SQL模式或查询应该是可读的,并且实际上告诉你它正在做什么,即使没有注释也是如此.你需要在六个月内感谢自己,那时你需要对桌子进行维护. "SELECT account_number, billing_date FROM national_accounts"比"SELECT ACCNTNBR,BILLDAT FROM NTNLACCTS"更容易使用.
在运行DELETE查询之前不执行相应的SELECT查询(特别是在生产数据库上)!
我二十年来见过的最常见的错误:没有提前计划.许多开发人员将创建数据库和表,然后在构建应用程序时不断修改和扩展表.最终结果往往是混乱,效率低下,以后难以清理或简化.
京公网安备 11010802040832号 | 京ICP备19059560号-6 