SQL语言艺术(四)机动灵活：思考SQL语句

这章深入探讨SQL的双重特性，SQL与数据库引擎的关系以及对优化器的影响

关系数据库的出现，要归功于E.F. Codd的关系理论开创性研究成果。Codd的研究成果为数据库学科提供了坚实的数学基础，简单地讲，关系理论论证了可以通过一组关系运算符来查找满足某些条件的数据，这些关系运算符支持任何基本查询。需要注意的是，关系理论只关心如何根据查询条件取得正确的数据集，不包括排序，统计等功能，关系理论研究的是集合，但不涉及为这些集合排序

SQL与数据库

数据库的主要作用是存储数据，并且提供可靠的基础设施允许多个用户访问这些数据，同时能够处理资源竞争保持数据一致性

SQL是一种声明性的语言，SQL查询包含了两层的操作，第一层是关系操作的核，它负责找出我们要操作的数据集，第二层是非关系操作层，它对有限的数据结果集进行处理从而产生用户期望的最终结果

SQL与优化器

当SQL引擎处理查询时，优化器会借助关系理论，对开发者提供的语义无误的原始查询进行有效的等价变换，找出执行查询最高效的方式。尽管优化器对非关系操作层也偶有用途，但以关系理论为支持的优化器主要作用于关系操作层。在优化的过程中，优化器会检查下列因素：定义了哪些索引，相关表的数据量，数据的物理布局，可用内存大小，CPU数量等，最终对理论上等价的不同优化方案做出权衡，产生有可能是最优的查询执行方案

SQL是用来表达要做什么，而不是怎么做，从要做什么到如何来做的任务就是由优化器完成的。SQL应尽量减少非关系操作，尽量在关系操作层完成大部分工作，否则优化器在非关系操作层难以保证返回的结果数据和原始查询执行的结果一样

平常所写的SQL往往会涉及到嵌套的子查询，如果内层查询使用了order by之类的非关系操作，尽管外层的select看似关系操作，但其实是对一个内嵌视图的结果集进行操作，此时外层SQL也变成非关系操作了。一旦查询中的关系操作结束，就再也回不去了

一旦离开了关系操作层，查询语句的编写对性能影响重大，因为SQL引擎将严格执行它规定的执行路径，所以尽可能在关系层完成更多的操作，关系层的执行是可以优化的。掌握SQL语言的关键就是要懂得它的双重特性

掌握SQL艺术的五大要素

1.查询需要访问的数据总量

访问1000行的表和1000万的表可能需要不同的查询方案，没有确定目标容量之前，很难断定查询执行的效率

2.定义结果集的查询条件

过滤条件的效率有高有低，尽早过滤不需要的数据

3.结果集的大小

努力使响应时间与返回记录数成正比，因为用户的耐心很大程度上与预期返回的记录条数有关

4.查询所涉及的表的数量

当需要连接多个表时，按常理应该质疑设计的正确性。虽然现代的DBMS都能非常高效地连接很多表，但对优化器来说，表数量的增加，复杂度将呈指数增长

5.并发用户数

并发性是必须认真对待的因素，一般而言我们必须考虑系统的整体吞吐量，而不是个别用户的响应时间


其中定义查询的过滤条件是最关键的因素之一，包括where子句和join操作，有时条件的计算顺序无足轻重，但有时却影响重大。下面用一个常见的例子来看看如何对过滤条件进行排序，数据库schema如图，方框的大小代表各表数据量的大小



假设要处理的问题是查找最近6个月居住在A市，订购了B车的所有客户。最直接的写法是：

select distinct 
 c.custname
     from customers c
          join orders o
            on o.custid = c.custid
          join orderdetail od
            on od.ordid = o.ordid
          join articles a
            on a.artid = od.artid
     where c.city = 'A'
       and a.artname = 'B'
       and o.ordered >= somefunc

这种方式使用distinct来去除重复，实际情况中避免在最高层使用distinct应该是一条基本规则，因为即使遗漏了某个查询条件，distinct也会使查询看似正确地执行，发现重复数据容易，发现数据不准确很难。要摆脱distinct可以考虑存在性测试exists，改写的SQL如下：

select c.custname
    from customers c
    where c.city = 'A'
      and exists (select null
                  from orders o,
                       orderdetail od,
                       articles a
                  where a.artname = 'B'
                    and a.artid = od.artid
                    and od.ordid = o.ordid
                    and o.custid = c.custid
                    and o.ordered >= somefunc )

从以上and o.custid = c.custid可以看到exists子查询需要参照外层查询的当前记录，属于关联子查询，如果优化器不改写此查询，就必须先找出每个客户，然后逐一检查是否满足存在性测试，当来自A市的客户很少时执行效率很高，否则情况很糟糕，而且至关重要的是orders表中的custid是否有索引对性能影响很大。我们也可以将exists改为in，SQL如下：

select custname
    from customers
    where city = 'A'
      and custid in
                 (select o.custid
                  from orders o,
                       orderdetail od,
                       articles a
                  where a.artname = 'B'
                    and a.artid = od.artid
                    and od.ordid = o.ordid
                    and o.ordered >= somefunc)

in为非关联子查询，内层查询只需执行一次，不再依赖外层查询，这时用到的索引为customers的主键索引

当对大量记录做存在性检查时，选择in还是exists须斟酌。两种方式执行顺序不同，in是先执行子查询，再比较其它条件，exists依赖于外层查询，先比较其它条件，再判断存在性，对相关字段的索引要求也不同。如果内层查询的过滤条件比较有效，in的速度会比较快，反之则exists比较快

任何有聚合无关的条件都应放在where子句中，从而减少为进行group by而必须执行的排序操作所处理的数据量

查询的编写方式经常会与数据隐含的假设相关，最终的结果集都是一样的，但执行速度可能会有极大的差异。查询编写的方式会影响执行路径，尤其是应用无法在真正的关系环境中表达条件时。若想让优化器发挥极致，就必须扩大关系处理的工作量，关确保非关系的部分对最后结果集的影响最小