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


当前回答

认为他们是dba和数据建模师/设计人员,而他们在这些领域没有任何正式的灌输。 认为他们的项目不需要DBA,因为这些东西都很简单/琐碎。 未能正确区分应该在数据库中完成的工作和应该在应用程序中完成的工作。 未验证备份,或未备份。 在他们的代码中嵌入原始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

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.

我曾多次看到初级开发人员关联子查询,这通常会对性能产生严重影响。但是,当删除一个相关的子查询时,一定要查看之前和之后的解释计划,以确保您没有使性能变差。

使用ORM进行批量更新 选择多于需要的数据。同样,这通常在使用ORM时完成 在循环中触发sql。 没有良好的测试数据,只在实时数据上注意到性能下降。

有一件事我想补充,学习使用分析函数,如分区BY, RANK, DENSE_RANK (Oracle)。它们对于复杂的查询是绝对必要的。

其他建议是,如果可能的话,在您的开发团队中有一个专门的数据库开发人员,他应该是SQL、数据库建模、调优等方面的专家(但不是DBA)。这种技能是一笔巨大的财富。

我不得不说,应用程序开发人员犯的最大错误是没有正确地规范化数据库。

作为一名应用程序开发人员,我意识到正确的数据库结构、规范化和维护的重要性;我花了无数的时间自学数据库结构和管理。根据我的经验,每当我开始与不同的开发人员合作时,我通常必须重组整个数据库并更新应用程序以适应,因为它通常是畸形的和有缺陷的。

For example, I started working with a new project where the developer asked me to implement Facebook Connect on the site. I cracked open the database to see what I had to work with and saw that every little bit of information about any given user was crammed into one table. It took me six hours to write a script that would organize the table into four or five separate tables and another two to get the app to use those tables. Please, normalize your databases! It will make everything else less of a headache.

不了解DBMS的工作原理。

如果不了解离合器的工作原理,你就不能正确地驾驶变速杆。如果不了解实际上只是在硬盘上写入文件,就无法理解如何使用数据库。

具体地说:

Do you know what a Clustered Index is? Did you think about it when you designed your schema? Do you know how to use indexes properly? How to reuse an index? Do you know what a Covering Index is? So great, you have indexes. How big is 1 row in your index? How big will the index be when you have a lot of data? Will that fit easily into memory? If it won't it's useless as an index. Have you ever used EXPLAIN in MySQL? Great. Now be honest with yourself: Did you understand even half of what you saw? No, you probably didn't. Fix that. Do you understand the Query Cache? Do you know what makes a query un-cachable? Are you using MyISAM? If you NEED full text search, MyISAM's is crap anyway. Use Sphinx. Then switch to Inno.