SQL语言艺术(八)认识困难：处理困难

本章讨论对SQL而言实际中经常遇到的困难与相应的解决策略

看似高效的查询条件

有些选择条件乍一看很高效，但需要稍加修改才能发挥潜力。遇到基于键值的一部分比较等特殊情况，比如信用卡前置码比较，可使用范围条件来表达，并尽量使用下限和上限值

select count(*)
    from credit_card_check
    where ? like prefix + '%'

如果prefix字段长度可变，很可能无法使用这个字段上的索引，可以将条件改为：

where substring(? + '0000000000000000000', 1, 19) between lower_bound
                                                          and upper_bound

抽象层封装不当

在程序中对数据库访问进行封装时，有很多需要注意的地方。比如先查出一批数据，然后在程序中进行过滤，这时应该考虑将过滤直接放在SQL中

分布式系统的复杂性

在分布式系统的情况下，不仅访问远程服务器会造成性能下降，如果本地无法访问远程数据库的字典信息，则优化器无法进行适当的优化

尽量在靠近数据的地方进行过滤操作，如果查询要连接远程的多张表，最好先在远程服务器上join好涉及到的表，再接结果返回到与本地表进行join操作，这种情况下可以考虑将远程表的join操作定义成视图，避免DBMS分别读取两个远程表的数据

动态定义的搜索条件常常成为性能瓶颈

动态条件背后是一连串可怕的需求：支持用户通过界面输入搜索条件和排序条件。这类应用一般表现为：开始查询执行地相当好，但随着时间的推移，查询变得非常慢。此类问题很难修正，因为所有的东西都是动态的。要查询的是相同表的相同字段，而搜索条件会发生变化，成功的关键是如何聪明地产生SQL查询

select语句中有可能要用到distinct，因为如果用户没有输入任何条件，返回的结果里面可能会有重复记录。另外不能直接将SQL语句与用户的输入值进行拼接，而是以'?'做为占位符，采用SQL预编译的形式，一方面数据库会缓存预编译的SQL，便于重复使用提高性能，另一方面可以避免SQL注入的漏洞

动态拼接没有完美的解决方案，从设计简单角度讲，可能会把所有涉及的表都预先连接好，再动态生成用户输入的过滤条件。如果负载量比较大，出现性能问题的时候，可以考虑定制SQL，即根据用户的输入只连接需要的表，但这会加大设计上的复杂度。以更聪明的方式建立查询，可以更好地控制安全性，结果正确性和性能，而更为简单的解决方案可能会牺牲某一方面的质量，具体的方案要视实际情况而定