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


当前回答

不使用索引。

其他回答

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

不了解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.

不对数据库模式使用版本控制 直接针对活动数据库工作 没有阅读和理解更高级的数据库概念(索引、聚集索引、约束、物化视图等) 没有测试可伸缩性……只有3或4行的测试数据永远不会给您真实的实时性能的真实画面

这之前已经说过了,但是:索引,索引,还是索引。我见过许多性能不佳的企业web应用程序,它们通过简单地进行一些分析(查看哪些表被频繁访问),然后在这些表上添加索引来解决问题。这甚至不需要太多的SQL编写知识,而且回报是巨大的。

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.

最后:使用清晰、一致、直观的命名约定。与一段编写良好的代码应该是可读的一样,一个好的SQL模式或查询应该是可读的,并且实际上告诉您它在做什么,甚至没有注释。六个月后,当你不得不对桌子进行维护时,你会感谢自己的。“选择account_number, billing_date FROM national_accounts”比“选择ACCNTNBR, BILLDAT FROM NTNLACCTS”更容易使用。

不使用索引。