Web开发人员的10个数据库优化最佳实践

作者 | 乔冰诚 翻译

来源 | 新钛云服（ID：newtyun）

转载请联系授权（微信ID：zlm935177782）

数据库优化已经成为web开发人员提高web应用程序性能，从而改善用户体验的关键。对一些人来说，这可能听起来不太吸引人，但好处是值得的。如果正确地优化数据库，就可以提高性能、减少瓶颈并节省资源。

幸运的是，有些优化技术在sql查询中不需要dba级别(数据库管理员)的专业知识。

为了帮助你开始，我们将看看10个最佳实践，为web开发者如何优化你的数据库。一旦您了解了这些，您就可以更深入地研究数据库优化。

一、在选择查询中使用EXPLAIN
使用EXPLAIN关键字是快速修复慢速查询的好方法。它让您了解如何执行查询，并帮助您找到查询优化的潜在位置。

只需在SELECT查询前输入EXPLAIN关键字。这不会触发实际的查询，相反，您将看到执行计划。

当您仔细查看结果时，您将能够确定潜在的瓶颈或任何其他问题，如缺少索引，并减少扫描行的数量。

有了EXPLAIN工具，您可以优化查询，如果有必要，甚至可以改进表结构。

二、向搜索列添加索引
如果您按特定列搜索表，则很可能通过索引该列来提高查询性能。

通过向搜索列添加索引，可以减少响应时间并优化资源使用。尽管索引可能对所有查询都没有帮助，但在大多数情况下它绝对有用。

但是要记住，有索引的表比没有索引的表需要更多的时间来更新。这是因为索引也需要更新。因此，明智的做法是只为频繁搜索的列创建索引，而不是为更新比读取次数更多的表创建索引。

三、尽可能的使用标识字段
在表中使用Identity字段作为主键有几个好处。

首先，它更快—您可以在查询中简单地使用整数，而不是更长的字符串字段。这将为您节省一些内存，因为整数可以更短。

其次，它更安全——使用应用程序数据字段可能会导致许多问题。例如，如果您使用名称或地址作为PRIMARY KEY，那么如果客户端或用户更改了他们的名称、移动了，甚至只是出现了拼写错误，那么您可能会遇到问题。

因此，为了加快查询速度并提高工作效率，可以在每个表中添加一个Identity列，这样就可以使用它作为一个PRIMARY KEY，并使用AUTO_INCREMENT和一个合适类型的INT变量。

四、默认情况下避免使用 NULL 值
尽量使用NOT NULL代替NULL。

一般来说，选择NOT NULL意味着更快的查询，这得益于更有效地使用索引，并避免了专门测试每个值是否为NULL的需要。最重要的是，你使用更少的存储空间，根据MySQL文档，NULL列需要额外的空间。

节省空间是特别重要的，如果你使用一个网络主机为你的数据库，因为即使一些最好的网络主机服务也不能提供无限的存储空间。现在，每个专栏节省一个比特看起来并不是很多，但是如果你在经营一个电子商务商店，有成千上万的产品，你会节省很多资源。

使用NOT NULL，您将能够像处理任何变量一样处理字段，同时避免使用NULL可能产生的某些边缘情况。

当然，仍然有一些情况下使用NULL更有益—但在大多数情况下，您可以使用NOT NULL实现相同的结果。

五、对查询使用无缓冲模式
为了节省时间和内存，可以使用无缓冲查询。

默认情况下，SQL查询使用缓冲模式。这会增加您的等待时间并消耗资源，因为在查询完成之前不会返回结果—而是存储在内存中。如果是较大的查询，并且您有一个巨大的数据库，那么使用缓冲查询将需要大量内存。

相比之下，当使用无缓冲查询时，在执行查询之前不会自动存储结果。一旦检索到第一行，您就可以开始处理它们。

但是请记住，在处理结果集时，未缓冲查询不允许在同一个连接上发出进一步的查询。

六、使列紧凑
优化磁盘空间对于保持数据库引擎良好运行至关重要。确保不会影响性能的一种非常简单的方法是使用小而紧凑的列。

因此，您应该始终选择对您的应用程序最有用的整数类型。例如，如果您知道您的表不会有大量的行，那么不要自动使用INT作为主键—您实际上可能会从使用SMALLINT甚至TINYINT中受益。

对于DATE和DATETIME也是如此——如果您不特别需要时间部分，只需使用DATE即可。DATETIME数据类型占用8个字节，而DATE只占用3个字节—因此这样可以节省5个字节。

七、保持表的静态(固定长度)
另一种优化数据库性能的方法是使用静态表。

这意味着您的表不应该包括任何长度可变的列，如TEXT或BLOB。您可以使用CHAR、VARCHAR、BINARY和VARBINARY列，但是需要填充它们以匹配指定的列宽度。

使用固定长度的表是有益的，因为它们更快，更容易缓存。除此之外，静态表更安全——它们在崩溃后更容易重建。

但是，静态表在某些情况下可能比动态格式的表需要更多的磁盘空间—特别是当您使用CHAR和VARCHAR列时。但是性能上的改进可能会超过对磁盘空间的任何关注。

八、安装对象关系映射器(ORM)
使用ORM是优化数据库工作方式的另一种好方法。

首先，ORM有助于消除人为错误因素，因为它为您做了很多事情。您不必自己编写那么多代码，而且ORM会为您处理许多重复的任务，从而减少您的工作量。

ORM还可以提高系统的安全性，因为准备和清理查询使SQL注入变得更加困难。

最重要的是，ORM将在内存中缓存实体，这减少了数据库和CPU的负载。

当然，ORM有其优点和缺点，可能并不完全适合您的使用。但是有一些简单的方法可以避免滥用对象-关系映射。此外，还有一些其他的性能调优和优化插件可能更适合您。

九、批量执行DELETE和UPDATE查询
删除和更新数据——尤其是在非常大的表中——可能会很复杂。这可能会花费很多时间，而且这两个命令都作为单个事务执行。这意味着，如果出现任何中断，整个事务都必须回滚，这可能会更加耗时。

但是，如果您遵循了批量运行DELETE和UPDATE查询的良好实践，您将能够通过增加并发性和减少瓶颈来节省时间。

如果一次删除和更新的行数较少，则可以在将批提交到磁盘时执行其他查询。而且您可能需要做的任何回滚都将花费更少的时间。

十、使用程序分析()获得更多提示
优化数据库的最后一个最佳实践是使用内置功能:PROCEDURE ANALYSE()。更具体地说，如果将该命令添加到某个SQL语句中，它将查看列，然后推荐最佳数据类型和数据长度。

在将新数据导入到表中之后——或者甚至检查现有表中是否存在不一致的地方——这可能特别有用。

如果您实现了这些建议，您可能会节省一些空间。然而，请记住，这些只是建议，你必须真正考虑它们是否适合你的特定目的。

结论
数据库优化有点棘手，但完全避免它会对您的web应用程序产生巨大的影响，导致性能问题。

如果你遵循了这10个web开发者数据库优化的最佳实践，你将开始改善用户体验和资源管理。

一旦实现了这些方法，请进一步了解如何提高数据库性能的技巧。