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

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

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

1.对SELECT查询使用EXPLAIN

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

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

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

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

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

3.尽量使用ID字段

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

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

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

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

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

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

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

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

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

6.让各个列更加紧凑

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

其实，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的负载。

9.批量运行DELETE和UPDATE

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

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

小结

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

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