Builder开发者在线SQL查询数据库时,适当遵循一些原则可以让工作变得更加轻松,本文就列举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的要求也更低,并发性可以大大提高。
另外要提醒的是,执行删除和更新操作应尽量选择非高峰时段。
总结
遵循这些方法总是能收到效果,但在实践中,我们应该根据实际情况,选用一种或几种最佳方案,来提高我们执行查询的速度,从而节省查询时间,提高查询效率。
原文转载于推酷网:http://www.tuicool.com/articles/baamei
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的要求也更低,并发性可以大大提高。
另外要提醒的是,执行删除和更新操作应尽量选择非高峰时段。
总结
遵循这些方法总是能收到效果,但在实践中,我们应该根据实际情况,选用一种或几种最佳方案,来提高我们执行查询的速度,从而节省查询时间,提高查询效率。
原文转载于推酷网:http://www.tuicool.com/articles/baamei