业余爱好者的数据库优化技术

Question

我们能否列出一些基本的优化技巧（包括从建模到查询、创建索引、视图到查询优化等）。如果每个答案只提供一种技术，那将非常有用。作为一个业余爱好者，我会觉得这很有用，谢谢。

为了避免过于笼统，让我们假设我们正在使用像MySQL或Oracle这样的主流数据库，并且该数据库将在大约10个表中包含50万至100万条记录，一些表具有外键约束，并且都使用最典型的存储引擎（例如：MySQL的InnoDB）。当然，基础知识，如主键和外键约束已定义好。

- Zombies

---

我希望它能得到更多的回答。 - Zombies

Answer 1

学习有关索引，并正确使用它们。通常来说，遵循以下准则：

• 每个表应该有一个聚集索引。
• 用于筛选和排序的字段是索引的好选择。
• 更具有选择性的字段更适合用于索引。
• 针对重要查询设计“覆盖索引”以获得最佳性能。
• 确保您的索引实际被使用，并删除未使用的索引。
• 如果您的表有15个字段，并且您创建了15个只包含单个字段的索引，则做错了:)

*如果您知道自己在做什么，则这些规则有一些例外。我个人的经验是Microsoft SQL Server，但我认为大部分建议都适用于其他关系数据库管理系统。

- BradC

---

在对可能变得庞大的表使用聚集索引时，应该采取一些谨慎措施。当你插入或更新一行时，聚集索引可能会导致表重新排序，从而造成性能损失。 - Nick DeVore

除非您的主键是GUID，否则在其上创建聚集索引是一个不好的想法。 - slashdottir

Answer 2

在我看来，最好的优化方法是让数据模型适合其所构建的问题域。如果不适合，则会导致编写困难或复杂的查询以获取所需信息，通常在针对数据库构建报告时会出现这种情况。因此，在设计数据库时，有一个关于用户需要系统提供哪些类型和性质的信息（例如报告）的想法是有帮助的。

- Thomas

---

“resulting system” 也许？不是“resulting symptom”吗？ - MJB

@MJB - 我认为我已经正确地陈述了。你怎么知道数据模型不适合问题域？症状是编写查询复杂或困难。 - Thomas

我明白了。我看错了。我以为你是在说“生成的系统很难编写”，现在我明白你的意思是“生成的症状是难以编写查询语句的”。是我搞错了。我以为那是个笔误。 - MJB

是的。他用“症状”这个词，基本上是在说某些明显的迹象（例如复杂的查询）往往表明数据模型与问题域不匹配。（顺便加一分） - Cam

Answer 3

当谈到数据库设计时，请查看数据库规范化，例如维基百科文章：规范化形式。

如果您有一个良好的设计，但仍需要优化性能，请尝试反规范化。

如果您有特定需求，关系模型无法高效处理，请查看涵盖术语NoSQL 的其他模型。

- Gabriel Ščerbák

---

这是非常好的建议 - 规范化并不总是答案！ - Timothy

Answer 4

一些查询/模式优化：

• 在使用 DISTINCT 或 GROUP BY 时要注意。我发现很多新开发人员会在不需要使用 DISTINCT 的地方使用它，或者可以使用 Exists 语句或派生查询更有效地重写它。

• 注意 Left Join。我经常发现新的 SQL 开发人员会忽略现有的模式，并在不必要的情况下使用 Left Join。例如：

Select
From Orders
    Left Join Customers
        On Customers.Id = Orders.CustomerId

如果Orders.CustomerId是必需的列，那么使用左连接是不必要的。

• 学习新特性。目前MySQL不支持公共表达式，这意味着某些类型的查询比使用CTEs编写更加繁琐且可能更慢。然而，这种情况不会永远存在。了解MySQL中的新语法特性，可以用来使现有查询更加高效。

• 并非在所有地方都需要使用代理键。可能有一些表更适合使用智能键（例如美国州缩写、货币代码等），这将使开发人员在许多情况下避免使用额外的连接。

• 如果可能，找到将数据归档到OLAP或报告服务器的方法。生产数据越小，运行速度就越快。

- Thomas

Answer 5

一个简洁地模拟您问题的设计始终是一个良好的开端。过度泛化数据模型可能会导致性能问题。例如，我听说过一些项目为了追求超级灵活性而将关系数据库管理系统（RDBMS）用作“名称/值”存储，结果性能非常糟糕。

一旦有了良好的设计，就可以使用RDBMS提供的工具来帮助实现良好的性能。单个字段主键（没有组合键），但将组合业务键作为索引并设置唯一约束条件，使用适当的数据类型，例如对于数字值使用适当的数字类型而不是char或类似类型。还应考虑RDBMS运行的硬件的物理属性，因为大部分查询时间通常是磁盘I/O - 但当然不要认为这是理所当然的 - 使用分析器找出时间去哪里了。

根据更新/查询比率，材料化视图/索引视图可以在改善慢速查询的性能方面非常有用。一个穷人的替代方法是使用触发器调用一个过程，该过程使用慢速、不经常更改的视图的结果填充表格。

查询优化有点像黑魔法，因为它通常依赖于数据库，但这里给出了一些经验法则-优化SQL。

最后，虽然可能超出您问题的预期范围，但在应用程序中使用一个好的数据访问层，并避免自己编写代码的诱惑 - 对于所有主要语言肯定有经过测试和性能良好的实现。在数据访问层、中间层和应用程序层使用缓存可以显著提高性能。

- mdma

Answer 6

尽可能使用较少的查询。使用“JOIN”，并对表进行分组，以便单个查询给出结果。

一个很好的例子是使用修改的先序树遍历（MPTT）在单个查询中按顺序获取树节点的所有父节点。

- Wernight

Answer 7

采用全面的优化方法。

考虑慢磁盘、网络延迟、内存不足和服务器负载对性能的影响。

- Timothy