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

    云和安全管理服务专家 新钛云服乔冰诚 翻译

    数据库优化已经成为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开发者数据库优化的最佳实践,你将开始改善用户体验和资源管理。

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

    原文链接:
    https://dzone.com/articles/10-database-optimization-best-practices-for-web-de

    «
    »
以专业成就每一位客户,让企业IT只为效果和安全买单

以专业成就每一位客户,让企业IT只为效果和安全买单