|
|
|
|
公众号矩阵

适合Web开发者的十大数据库优化实践

数据库优化是一个漫长的调试过程,需要Web应用开发和测试人员的协同努力。本文给出十项优化建议,以便您能改善用户体验和资源管理。

作者:陈峻编译来源:51CTO|2021-03-01 09:00

【51CTO.com快译】数据库优化,对于开发人员提高Web应用程序的性能,进而改善用户体验是至关重要的。如果方法得当,目标数据库的性能不但会得到提升,而且能够减少业务能力瓶颈,以及节省系统资源的占用。

当然,除了查找和运用那些专业的DBA(数据库管理员)级优化技术,Web开发人员完全可以试着采用如下十种有关SQL查询的数据库优化实践。

1.对SELECT查询使用EXPLAIN

通过深入了解应用程序如何执行查询,进而找到潜在的可优化位置,可以说,使用EXPLAIN关键字是快速修复那些缓慢查询的好方法。

我们只需要在SELECT查询的前面键入EXPLAIN关键字,便可在并不触发实际查询的基础上,深入了解具体的执行计划。通过仔细分析执行结果,我们便可识别出那些潜在的瓶颈,以及相关的程序设计问题。例如:缺少索引,或是需要减少扫描的行数,甚至有必要改善数据表的结构。

2.将索引添加到搜索列中

如果您有需要按照特定列的方式去搜索数据表,那么就可以通过将索引添加到该列中,以减少响应时间,提高查询的性能,并优化资源的使用。尽管我们所创建的索引,不一定能保证对所有的查询都行之有效,但是在大多数情况下,还是能够起到立竿见影的效果。

当然,凡事都有两面性,索引表的建立要比未建立索引表更花费时间。毕竟索引也需要及时得到更新,以体现数据表的正确状态。因此,一种较为明智的做法是:只为经常要用到搜索服务的列创建索引,而不必为那些需要频繁更新的表建立索引。

3.尽量使用ID字段

总的说来,在数据表中使用“ID(Identity)”字段作为主键,有着如下优点:

首先是更快捷 -- 您可以在查询中使用简单的整型(integer),而非长的字符串型(longer string)。显然,由于整型短得多,因此执行起来更节省内存。

其次是更安全 -- 直接使用应用里的关键数据字段,往往存在着许多问题。例如:如果您使用名称或地址作为主键,那么用户一旦更改了其名称、地址、甚至输入了错别字等情况,都可能导致原有对应关系的错乱。

因此,为了加快查询的速度并提高工作效率,请向每个数据表中添加一个Identity列,以便开发团队将其用作带有AUTO_INCREMENT(请参见--https://www.w3schools.com/sql/sql_autoincrement.asp)的主键,以及合适的INT变量类型。

4.在默认情况下避免出现NULL(空)值

根据MySQL文档,NULL列需要占有额外的空间(请参见--https://dev.mysql.com/doc/refman/8.0/en/data-size.html)。因此为了减少数据库对于可用存储空间的占用,开发者通常会趋向于选用NOT NULL,让索引得到更加有效地使用,并通过省去判断、或测试每个值是否为NULL,以提高查询的速度。

如果您在数据库中使用到了虚拟主机,那么节省空间就显得更加重要了。毕竟,即使是最好的虚拟机服务,也无法提供无限的存储空间。虽然当前存储设备和资源的获取成本已大幅降低,但是如果您正在经营、或是目标成为一家拥有成千上万种产品的电商网站,那么节省存储资源是很有必要的。因此,通过使用NOT NULL,您将能够像处理任何变量那样,去使用各个字段,进而避免由NULL引发的各种问题。

5.对查询使用无缓冲模式

您可以使用“无缓冲查询(unbuffered queries)”(请参见--https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlinfo.concepts.buffering.html),来节省查询需要的时间和内存。

默认情况下,SQL查询使用的是缓冲模式。由于在查询完成之前,程序不会返回任何结果,而且会将过程结果存储到内存中,因此这会在无形中增加了程序的等待时间,并浪费了内存资源。显然,如果查询的数量较多,并且数据库本身较为庞大的话,那么软件应用就需要大量的内存,来进行缓冲查询。

相反,对于无缓冲查询来说,在执行查询之前,其结果并不会自动存储。当检索到第一行后,您就可以开始使用它们了。

值得注意的是:在处理结果集时,无缓冲查询并不允许在同一连接上,发出过多的更多查询要求。

6.让各个列更加紧凑

优化磁盘空间,对于保持数据库引擎的正常运行,是至关重要的。而确保不产生性能障碍的一种简单方法便是:使用小而紧凑的列。

为此,您应该始终选择对应用程序最实用的整数类型。例如:如果您知道目标数据表将不会产生大量的行,那么请不要让数据系统自动使用INT作为主键(请参见--https://www.w3schools.com/sql/sql_datatypes.asp)。您完全可以受益于使用SMALLINT,甚至TINYINT。

其实,DATE和DATETIME也是如此。如果您不需要用到时间部分,那么只需使用DATE即可。由于DATETIME在数据类型上占有8个字节,而DATE仅占有3个字节,因此您可以直接节省5个字节。

7.保持表格处于静态(固定长度)

优化数据库性能的另一种好方法是:使用静态表。也就是说,数据表不应包含诸如TEXT或BLOB等可变长度的列。您可以使用CHAR、VARCHAR、BINARY和VARBINARY类型的列,但是需要对其进行填充,以匹配指定的列宽。固定长度的表不但运行得更加快速,而且更容易缓存。同时,静态表更为安全,也更易于在崩溃后被重建。

当然在某些情况下,特别是在使用CHAR和VARCHAR列时,静态表也可能比会动态格式表,需要更多的磁盘空间。这就需要您在性能提升和磁盘空间上,进行权衡比较了。

8.安装对象关系映射器(Object-Relational Mapper,ORM)

由于ORM可以为您处理大量重复性任务,大幅减少代码的编写量,因此ORM可协助消除各种人为的错误因素。与此同时,由于ORM能够及时对查询进行清理,让SQL注入变得更加困难,因此系统的安全性也能够得以提升。此外,ORM还会将各种实体缓存在内存中,以减少数据库和CPU的负载。

当然,ORM并非十全十美,为了避免滥用ORM(请参见--https://dzone.com/articles/object-relational-mapping-pitfalls),您也可以使用一些性能调整,以及优化器类型的插件。

9.批量运行DELETE和UPDATE

在大型数据表中,删除和更新数据通常是作为同一事务被执行的,因此这两种操作往往既复杂又耗时。而一旦发生了任何中断的情况,我们在回滚整个事务时,会更加耗时耗力。如果您能够采取批量运行DELETE和UPDATE(请参见--https://www.mssqltips.com/sqlservertip/5636/optimize-large-sql-server-insert-update-and-delete-processes-by-using-batches/)的做法,则可以通过增加并发性和减少瓶颈,来节省大量的时间。例如:您可以一次性删除和更新较少的行数,进而在将批处理提交到磁盘的同时,执行其他类型的查询。这些都可以让您减少执行回滚所需要的时间。

10.使用PROCEDURE ANALYSE(),来获得更多的提示

优化数据库的最后一项实践是:使用数据库的内置功能--PROCEDURE ANALYSE()。通过将其添加到SQL语句中,我们可以全面查看数据列,发现那些最佳的数据类型和长度。而在将新的数据导入对应的数据表后,我们则可以及时检查现有的表中,是否存在着任何不一致的情况。

小结

总的说来,数据库优化是一个漫长的调试过程,需要Web应用开发和测试人员的协同努力。希望上面提到的十项优化建议,能够为您手头的软件产品,带来改善用户体验和资源管理的启发和思路。

原文标题:10 Database Optimization Best Practices for Web Developers,作者: Kaarle Varkki

【51CTO译稿,合作站点转载请注明原文译者和出处为51CTO.com】

【编辑推荐】

  1. SAP Fiori开发指南
  2. C++开发环境搭建-1部分第2课
  3. Oracle12c数据库培训教程02:Linux系统Oracle12c数据库安装与维护
  4. 项目中数据库密码没有加密导致了数据泄露!!
  5. 恶意软件开发语言正从C/C++转向Go 近年来呈爆发式增长
【责任编辑:华轩 TEL:(010)68476606】

点赞 0
分享:
大家都在看
猜你喜欢

订阅专栏+更多

数据湖与数据仓库的分析实践攻略

数据湖与数据仓库的分析实践攻略

助力现代化数据管理:数据湖与数据仓库的分析实践攻略
共3章 | 创世达人

6人订阅学习

云原生架构实践

云原生架构实践

新技术引领移动互联网进入急速赛道
共3章 | KaliArch

35人订阅学习

数据中心和VPDN网络建设案例

数据中心和VPDN网络建设案例

漫画+案例
共20章 | 捷哥CCIE

222人订阅学习

订阅51CTO邮刊

点击这里查看样刊

订阅51CTO邮刊

51CTO服务号

51CTO官微