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

发布于 2022-7-26 17:45
浏览
0收藏

作者 | 乔冰诚 翻译

来源 | 新钛云服(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开发者数据库优化的最佳实践,你将开始改善用户体验和资源管理。

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

标签
已于2022-7-26 17:45:28修改
收藏
回复
举报
回复
添加资源
添加资源将有机会获得更多曝光,你也可以直接关联已上传资源 去关联
    相关推荐