我的数据库设计技巧很臭。(My Database Design skills stink. Where to seek remedy?)

我有一个网站，在数据库设计的流量和复杂性方面都在不断扩展。 我一直以来都是开发人员，而且从来没有真正成为数据库管理员，除了我需要做的工作以使我的代码运行。 这需要改变 - 我需要提高数据库方面的效率。

举一个模糊的例子，我正在寻找如何学习：

优化复杂的表/关系以实现性能/扩展 如何有效地索引。 （目前我在外键上抛出索引，这就是它） 复杂数据库的一般设计原则

我发现的大部分资源要么更多地针对SQL的基础知识（“这是一个SELECT查询，一个JOIN等”），要么主要关注数据库之外的性能问题。

所以，我知道这有点模糊 - 但我应该在哪里确保我的数据库以最有效和最完整的方式设计？

I have a web site that's been progressivelly expanding in both traffic and complexity of database design. I've always worked as a developer first & foremost, and never really been much of a DB administrator beyond what I need to do to get my code running. This needs to change - I need to improve efficiency on the database side of things.

To give a vague example, I'm looking for how to go about learning:

Optimising complex tables/relationships for performance/scaling How to index efficiently. (At the moment I throw indexes on foreign keys, and that's about it) General design principles for complex databases

Most of the resources I've found are either directed more towards the basics of SQL ("this is a SELECT query, a JOIN, etc") or focus primarily on performance issues outside the DB.

So, I know this is a little vague - but where should I look to ensure my database is designed in the most most efficient & integral manner possible?

最满意答案

了解数据建模。 选择正确的数据结构始终是关键的第一步，对于一般的编程和特别是数据库。 性能不能在糟糕的数据结构之上“闩上”！ ERwin方法指南可能不是开始学习数据建模的坏方法。 了解DBMS如何在物理层面组织数据。 这将极大地帮助您理解如何“塑造”数据的性能以及如何有效地利用现代DBMS提供的许多性能机制。 使用指数，卢克！ 是一个关于这个主题的优秀教程。 了解如何有效地访问数据库，并确保您真正了解将从您的代码中调用的客户端API。 不同的API有自己的特性，但它们都共享一些共同的主题，例如参数绑定，查询准备和提取。 即使您被ORM“屏蔽”，也不必手动绑定参数，这仍然是“在幕后”，并且理解它会提高您编写高性能代码的能力。 测量，测量，测量。 现代信息系统非常复杂，甚至专家发现自己做出了错误的假设，所以不要依赖假设！ Learn about data modeling. Choosing the right data structure is always a crucial first step, for programming in general and databases in particular. Performance cannot be "bolted" on top of a bad data structure! The ERwin Methods Guide is probably not a bad way to start learning about data modeling. Learn how DBMSes organize data at the physical level. This will help you immensely in understanding how to "shape" your data for performance and how to effectively leverage many of the performance mechanisms modern DBMSes put at your disposal. Use The Index, Luke! is an excellent tutorial on the topic. Learn how to efficiently access the database and make sure you really understand the client API that will be called from your code. Different APIs have their own idiosyncrasies, but they all share some common themes, such as parameter binding, query preparation and fetching. Even if you are "shielded" by an ORM from ever having to, say, bind parameters manually, this is still taking place "under the covers" and understanding it raises your ability to write performant code. Measure, measure, measure. Modern information systems are immensely complex and even experts find themselves making incorrect assumptions, so don't rely on assumptions!我的数据库设计技巧很臭。(My Database Design skills stink. Where to seek remedy?)

我有一个网站，在数据库设计的流量和复杂性方面都在不断扩展。 我一直以来都是开发人员，而且从来没有真正成为数据库管理员，除了我需要做的工作以使我的代码运行。 这需要改变 - 我需要提高数据库方面的效率。

举一个模糊的例子，我正在寻找如何学习：

优化复杂的表/关系以实现性能/扩展 如何有效地索引。 （目前我在外键上抛出索引，这就是它） 复杂数据库的一般设计原则

我发现的大部分资源要么更多地针对SQL的基础知识（“这是一个SELECT查询，一个JOIN等”），要么主要关注数据库之外的性能问题。

所以，我知道这有点模糊 - 但我应该在哪里确保我的数据库以最有效和最完整的方式设计？

I have a web site that's been progressivelly expanding in both traffic and complexity of database design. I've always worked as a developer first & foremost, and never really been much of a DB administrator beyond what I need to do to get my code running. This needs to change - I need to improve efficiency on the database side of things.

To give a vague example, I'm looking for how to go about learning:

Optimising complex tables/relationships for performance/scaling How to index efficiently. (At the moment I throw indexes on foreign keys, and that's about it) General design principles for complex databases

Most of the resources I've found are either directed more towards the basics of SQL ("this is a SELECT query, a JOIN, etc") or focus primarily on performance issues outside the DB.

So, I know this is a little vague - but where should I look to ensure my database is designed in the most most efficient & integral manner possible?

最满意答案

了解数据建模。 选择正确的数据结构始终是关键的第一步，对于一般的编程和特别是数据库。 性能不能在糟糕的数据结构之上“闩上”！ ERwin方法指南可能不是开始学习数据建模的坏方法。 了解DBMS如何在物理层面组织数据。 这将极大地帮助您理解如何“塑造”数据的性能以及如何有效地利用现代DBMS提供的许多性能机制。 使用指数，卢克！ 是一个关于这个主题的优秀教程。 了解如何有效地访问数据库，并确保您真正了解将从您的代码中调用的客户端API。 不同的API有自己的特性，但它们都共享一些共同的主题，例如参数绑定，查询准备和提取。 即使您被ORM“屏蔽”，也不必手动绑定参数，这仍然是“在幕后”，并且理解它会提高您编写高性能代码的能力。 测量，测量，测量。 现代信息系统非常复杂，甚至专家发现自己做出了错误的假设，所以不要依赖假设！ Learn about data modeling. Choosing the right data structure is always a crucial first step, for programming in general and databases in particular. Performance cannot be "bolted" on top of a bad data structure! The ERwin Methods Guide is probably not a bad way to start learning about data modeling. Learn how DBMSes organize data at the physical level. This will help you immensely in understanding how to "shape" your data for performance and how to effectively leverage many of the performance mechanisms modern DBMSes put at your disposal. Use The Index, Luke! is an excellent tutorial on the topic. Learn how to efficiently access the database and make sure you really understand the client API that will be called from your code. Different APIs have their own idiosyncrasies, but they all share some common themes, such as parameter binding, query preparation and fetching. Even if you are "shielded" by an ORM from ever having to, say, bind parameters manually, this is still taking place "under the covers" and understanding it raises your ability to write performant code. Measure, measure, measure. Modern information systems are immensely complex and even experts find themselves making incorrect assumptions, so don't rely on assumptions!