SQL语言艺术(八)认识困难:处理困难
程序员文章站
2022-05-03 12:53:09
...
本章讨论对SQL而言实际中经常遇到的困难与相应的解决策略
[b][size=medium]看似高效的查询条件[/size][/b]
有些选择条件乍一看很高效,但需要稍加修改才能发挥潜力。遇到基于键值的一部分比较等特殊情况,比如信用卡前置码比较,可使用范围条件来表达,并尽量使用下限和上限值
如果prefix字段长度可变,很可能无法使用这个字段上的索引,可以将条件改为:
[b][size=medium]抽象层封装不当[/size][/b]
在程序中对数据库访问进行封装时,有很多需要注意的地方。比如先查出一批数据,然后在程序中进行过滤,这时应该考虑将过滤直接放在SQL中
[b][size=medium]分布式系统的复杂性[/size][/b]
在分布式系统的情况下,不仅访问远程服务器会造成性能下降,如果本地无法访问远程数据库的字典信息,则优化器无法进行适当的优化
尽量在靠近数据的地方进行过滤操作,如果查询要连接远程的多张表,最好先在远程服务器上join好涉及到的表,再接结果返回到与本地表进行join操作,这种情况下可以考虑将远程表的join操作定义成视图,避免DBMS分别读取两个远程表的数据
[b][size=medium]动态定义的搜索条件常常成为性能瓶颈[/size][/b]
动态条件背后是一连串可怕的需求:支持用户通过界面输入搜索条件和排序条件。这类应用一般表现为:开始查询执行地相当好,但随着时间的推移,查询变得非常慢。此类问题很难修正,因为所有的东西都是动态的。要查询的是相同表的相同字段,而搜索条件会发生变化,成功的关键是如何聪明地产生SQL查询
select语句中有可能要用到distinct,因为如果用户没有输入任何条件,返回的结果里面可能会有重复记录。另外不能直接将SQL语句与用户的输入值进行拼接,而是以'?'做为占位符,采用SQL预编译的形式,一方面数据库会缓存预编译的SQL,便于重复使用提高性能,另一方面可以避免SQL注入的漏洞
动态拼接没有完美的解决方案,从设计简单角度讲,可能会把所有涉及的表都预先连接好,再动态生成用户输入的过滤条件。如果负载量比较大,出现性能问题的时候,可以考虑定制SQL,即根据用户的输入只连接需要的表,但这会加大设计上的复杂度。以更聪明的方式建立查询,可以更好地控制安全性,结果正确性和性能,而更为简单的解决方案可能会牺牲某一方面的质量,具体的方案要视实际情况而定
[b][size=medium]看似高效的查询条件[/size][/b]
有些选择条件乍一看很高效,但需要稍加修改才能发挥潜力。遇到基于键值的一部分比较等特殊情况,比如信用卡前置码比较,可使用范围条件来表达,并尽量使用下限和上限值
select count(*)
from credit_card_check
where ? like prefix + '%'
如果prefix字段长度可变,很可能无法使用这个字段上的索引,可以将条件改为:
where substring(? + '0000000000000000000', 1, 19) between lower_bound
and upper_bound
[b][size=medium]抽象层封装不当[/size][/b]
在程序中对数据库访问进行封装时,有很多需要注意的地方。比如先查出一批数据,然后在程序中进行过滤,这时应该考虑将过滤直接放在SQL中
[b][size=medium]分布式系统的复杂性[/size][/b]
在分布式系统的情况下,不仅访问远程服务器会造成性能下降,如果本地无法访问远程数据库的字典信息,则优化器无法进行适当的优化
尽量在靠近数据的地方进行过滤操作,如果查询要连接远程的多张表,最好先在远程服务器上join好涉及到的表,再接结果返回到与本地表进行join操作,这种情况下可以考虑将远程表的join操作定义成视图,避免DBMS分别读取两个远程表的数据
[b][size=medium]动态定义的搜索条件常常成为性能瓶颈[/size][/b]
动态条件背后是一连串可怕的需求:支持用户通过界面输入搜索条件和排序条件。这类应用一般表现为:开始查询执行地相当好,但随着时间的推移,查询变得非常慢。此类问题很难修正,因为所有的东西都是动态的。要查询的是相同表的相同字段,而搜索条件会发生变化,成功的关键是如何聪明地产生SQL查询
select语句中有可能要用到distinct,因为如果用户没有输入任何条件,返回的结果里面可能会有重复记录。另外不能直接将SQL语句与用户的输入值进行拼接,而是以'?'做为占位符,采用SQL预编译的形式,一方面数据库会缓存预编译的SQL,便于重复使用提高性能,另一方面可以避免SQL注入的漏洞
动态拼接没有完美的解决方案,从设计简单角度讲,可能会把所有涉及的表都预先连接好,再动态生成用户输入的过滤条件。如果负载量比较大,出现性能问题的时候,可以考虑定制SQL,即根据用户的输入只连接需要的表,但这会加大设计上的复杂度。以更聪明的方式建立查询,可以更好地控制安全性,结果正确性和性能,而更为简单的解决方案可能会牺牲某一方面的质量,具体的方案要视实际情况而定
上一篇: MySQL优化-----索引
推荐阅读