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

数据库优化，对于开发人员提高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应用开发和测试人员的协同努力。希望上面提到的十项优化建议，能够为您手头的软件产品，带来改善用户体验和资源管理的启发和思路。