提高SQL查询性能的7个法宝

每个数据库平台上的SQL开发人员都是在困难中求得生存，我们总是一次又一次犯同样的错误，这是因为数据库领域还相对不成熟，是的，每个数据库厂商都在做着各种不同的努力，但作为开发人员仍然要克服各种问题，无论是在SQL Server，Oracle，DB2，Sybase，MySQL数据库，还是其它关系数据库平台上编写SQL代码，并发性、资源管理、空间管理和SQL运行速度总是困扰着开发人员。

　　遗憾的是，其中部分问题的解决没有灵丹妙药，也几乎没有最佳实践。通常，开发人员有自己喜欢的SQL书写习惯，一般不愿意去研究其它可行方案，当然这可能是因为缺少培训的原因。我见得最多的就是在测试环境中SQL查询运行良好，但尚未在生产系统上进行试运行，就草草收场了，至于后来发现有问题，再被动式修改，因此最终用户就痛苦了。

　　我不期望开发人员成为DBA，但我们编写代码时必须考虑生产时的问题，如果不在开发初期这么做，DBA发现后只能迫使我们返工。

　　我们通常说数据库调试是一门技术，更是一门艺术，这是因为很少有现成的规则可以适应一切问题的解决，你在一个系统上解决的问题在另一个系统上可能就不是问题了，反之亦然。涉及到查询调整时，没有一个答案是完全正确的，但这并不意味着你应该放弃。

　　适当遵循一些原则可以让工作变得更加轻松，本文就列举7个可以灵活运用的原则，它们可以帮助你提高SQL查询速度，当然这些技巧你可以咨询DBA获得更多的信息。

　　1、用case代替update

　　要更新一条记录，我们立即会想到update，这个问题非常常见，许多开发人员经常忽视这个原则，因为使用update看起来非常自然，非常合乎逻辑。

　　假设你从Customer表中提取记录，你想将超过10万美元的订单标记为“Preferred”，因此你会想到使用一条update语句将CustomerRank列更新为“Preferred”，问题是update语句是有日志的，这就意味着每条记录它会写两次，解决这个问题的办法就是在SQL查询中内嵌case语句，在向表写入“Preferred”标志前，它会用订单金额条件对每一行进行检查，满足条件的才会更新，性能的提升是惊人的。

　　2、不要盲目地重用代码

　　这个问题也非常常见，在工作中直接用别人写好的代码是一件痛快的事情，你知道这些代码可以查询出你需要的数据，但问题是往往有些数据不是你需要的，但我们常常不愿意做一下修改，因此返回的数据集往往是一个超集，很可能多用一个外连接或是一个where子句就可以解决问题，因此在复用代码时最好检查一下，如有必要略做适应性修改。

　　3、只提取你需要的列

　　这个问题和2有点类似，但这次是指定具体的列。也许我们在使用select * 时感觉很畅快，多省事呀!如果要将每个列名都写出来，太麻烦了，这是很多人的想法，但这种想法是错误的，因为这样做会取出多余的数据列，我无数次看到犯这种错误的代码，曾经有一位开发人员对一张有120列，上百万行数据的表使用select * 查询，但他只会用到其中的三五列，这是对资源的极大浪费，我们建议拒绝书写select * ，你要什么就查询什么，多余的返回结果对你没用，虽然不影响你要实现的功能，但对数据库性能却有极大的影响。

　　4、尽可能只查询一次大表

　　这也是我看到很多人犯的错误，例如，某存储过程从一张上百万条记录的大表中取数据，开发人员想提取居住在加利福利亚且收入高于4万美元的客户信息，因此它先将居住在加利福利亚的客户取出放在一张临时表中，然后再查询收入高于4万美元的客户，将查询结果放入另一张临时表中，最后，他连接这两张临时表查询出最终的结果。

　　可能有人认为我是在开玩笑吧?但事实是确实有人这么做，这应该在一个查询中就能完成，却查询了两次大表。

　　有种稍微不同的情况是，当一个过程中的多个步骤需要大表的子集时，每一步可能都必须查询一次大表。避免多次查询的办法是持久化第一次查询的子集，然后将后面的步骤指向该持久化子集。

　　5、使用临时表

　　这个问题解决起来可能稍微有点麻烦，但其效果比较明显，其实在很多时候你都可以使用临时表，通过临时表可以有效地减少对大表的操作，如果你必须连接一个表到大表，并且在大表上有条件，这时就可以将大表中需要的数据输出到临时表中，然后再用该临时表进行连接，这样查询速度会有明显改进。如果你的存储过程中有多个查询需要需要连接到相同的表时，也可以使用临时表。

　　6、预存数据

　　这一条是我最喜欢的，因为它是一项很老的技术，常常被人们忽视，如果你有一个报表或存储过程需要连接大表，提前提取大表中的数据，持久化存储到另一张表中，报表就可以使用预存的数据集，从而提高整体执行效率。

　　并不是所有时候你都有机会利用该技术，但一旦能利用上，你会发现它是节省服务器资源很有效的办法。

　　但遗憾的是，很多开发人员都在尽力回避这种技术，实际上只需要创建一个视图就可以把问题解决了，但这种方法的问题是每个需要它的报表运行时都会执行一次，但对于同一个报表，假设10分钟前运行了一次，现在有人要再运行该报表，那么对大表的连接操作就可以避免掉了。我建议对那些经常被查询的表使用该技术将数据预存起来，可以节省大量的服务器资源。

　　7、分批删除和更新

　　这也是一个容易被忽视的技巧，对一个大表做数据删除或更新操作，如果操作不当可能是一场噩梦，问题是这两种操作都是单一的事务，如果你需要杀死它们，或它们在执行时系统遇到问题，必须全部回滚整个事务，这个时间可能非常长，这就是为什么我们在删除数十万条记录时，如果试图中途杀死进程几乎没用的原因，这些操作也会影响到其它事务，搞不好会造成死循环，因此应慎用。

　　解决这个问题的办法就是分批少量删除或更新，首先，无论什么原因需要结束事务，只需要回滚少量的行，此外，小批量提交数据写入磁盘，对I/O的要求也更低，并发性可以大大提高。

　　另外要提醒的是，执行删除和更新操作应尽量选择非高峰时段。

　　总结

　　遵循这些方法总是能收到效果，但在实践中，应该评估选用一种或几种最佳方案，大家一定要记住，没有那种办法是万能的。另外，这些技巧适用于所有数据库品种，因此你必须全部掌握!

相关阅读：

• SQL优化原则
• SQL优化思考
• 关于SQL优化的一些知识