（转摘）_《数据库设计入门经典》:构建快速执行的数据库模型_8.3 SELECT命令

8.3  SELECT命令
SELECT命令用于数据库的查询。要建立高效的查询，需要注意如下几点：
●      
查询所有字段——相比使用*字符检索所有字段，对特定字段名称的检索更有效率。*字符要求查找元数据字典进行元数据解释，以此查找表中字段，从而增加了系统开销。在高并发情况下，已经非常繁忙的数据库(如OLTP数据库)再频繁进行数据字典查找会使该数据库承受极大的并发处理量。思考如下查询：
SELECT NAME FROM AUTHOR;
该查询要比下面这条查询快：
SELECT * FROM AUTHOR;
●      
读取索引——存在索引时应该使用索引。直接从索引读取字段值时，就完全不必读取表，由于索引占用的物理空间较少，因此速度也更快。使得I/O活动也更少。在图8-1所示ERD代码段中，使用如下查询读取EDITION表应该改写为直接读取索引，因为图中主键是自动索引的。EDITION表的主键是ISBN字段。
SELECT ISBN FROM EDITION;
                                

                                                     图8-1  读取索引代替读取表
提示：
并非所有数据库引擎都允许直接扫描索引，即使SELECT命令支持也不行。
●       简单别名——较短的别名有助于保持SQL代码更易读，尤其能够帮助程序员将来进行修改。易于维护的代码不易出错，也更容易进行正确的调整。思考如下查询：
SELECT A.NAME, P.TITLE, E.ISBN
FROM AUTHOR A JOIN PUBLICATION P USING (AUTHOR_ID)
  JOIN EDITION E USING (PUBLICATION_ID);
该查询比下面这个查询更易处理：
SELECT AUTHOR.NAME, PUBLICATION.TITLE, EDITION.ISBN
FROM AUTHOR JOIN PUBLICATION USING (AUTHOR_ID)
  JOIN EDITION USING (PUBLICATION_ID);
为什么呢？因为代码较短。代码越短越容易处理。所以这种情况下代码越短越好。
8.3.1  用WHERE子句筛选
WHERE子句可用于包含需要的记录或排除不需要的记录(或两者皆用)。可以用几种特定的形式来建立WHERE子句，从而加快SQL代码的执行。使用WHERE子句能够与表中索引、排序和物理顺序进行匹配，因而可用于SQL语句调整。换言之，根据元数据的构造方式进行筛选。
提示：
WHERE子句用于筛选记录，因此可以用在SELECT命令、UPDATE命令和DELETE命令中。
建立高效筛选时需要记住如下几点：
●       单个记录搜索——最好利用单个表中的主键进行筛选，并查找单个记录或少量几个记录。如下查询会查找主键标识符为10的作者：
SELECT * FROM AUTHOR WHERE AUTHOR_ID = 10;
●      
记录的范围搜索——使用>、>=、<和<=运算符来执行范围搜索。范围搜索的效率不如使用=运算符的搜索，因为需要查找一组记录而不是单个记录。范围搜索仍可以使用索引并具有较高效率。如下查询查找作者标识符在5到10之内的所有记录：
SELECT * FROM AUTHOR WHERE AUTHOR_ID >= 5 AND AUTHOR_ID <= 10;
●      
非WHERE子句——使用NOT、!=或<>(不同数据库的情况不同)来查找数据库中不存在的记录。这时将忽略索引并读取整个表。下面的查询将读取所有记录，但不包括作者标识符为10的记录：
SELECT * FROM AUTHOR WHERE AUTHOR_ID != 10;
●      
LIKE运算符——谨慎使用LIKE运算符。该运算符通常会忽略索引并进行全表扫描。在搜索少量记录时，这样做的效率非常低。在一千万条记录中搜索10条记录时，最好用相等性来查找这10条记录，而不是在一千万条记录中进行相似查找，因为这样需要读取所有一千万条记录。下面的查询将查找所有姓名中含有元音“a”的作者，执行该查询将读取整个表：
SELECT * FROM AUTHOR WHERE NAME LIKE '%a%';
●      
WHERE子句中的函数——在WHERE子句中，必须谨慎使用各种函数表达式。如果要让SQL语句使用索引，那么最好不要使用函数。下面的查询中所用的函数会禁用PRINT_DATE字段上创建的索引：
SELECT * FROM EDITION WHERE TO_CHAR(PRINT_DATE,'DD-MON-YYYY')='01-JAN-2005';
要利用索引且不对表中字段使用函数，可以在表达式的另一端使用字面值：
SELECT * FROM EDITION WHERE PRINT_DATE=TO_DATE('01-JAN-2005','DD-MON-YYYY');
●      
大表与小表——如果表很小，那么读取表要比读取表上的索引更加高效。对读取单个表中几大部分的情况也是如此。如果能够立即读取足够的表信息，那么最好忽略索引。因为读取索引需要扫描索引，然后将指针传递到表上并按照找到的索引值来扫描表。如果读取的表信息较多，那么查找表记录索引的扫描活动要比表的读取(同时忽略索引)更耗时。
●       复合索引字段顺序——在很多数据库中，WHERE子句中的字段顺序可决定索引是否匹配。例如，如下创建3个索引字段的复合索引：
CREATE INDEX CAK_EDITION_1 ON EDITION (PUBLISHER_ID, PUBLICATION_ID, ISBN);
使用如下WHERE子句访问表时，由于在索引字段序列中包含了所有字段，因此查询会使用索引：
SELECT … WHERE PUBLISHER_ID=1 AND PUBLICATION=10 AND ISBN='1555583059';
使用如下WHERE子句访问表时，可能不会使用复合索引(取决于不同数据库情况)：
SELECT … WHERE ISBN='1555583059' AND PUBLISHER_ID=1 AND PUBLICATION=10;
SELECT … WHERE ISBN='1555583059';
SELECT … WHERE PUBLISHER_ID=1;
提示：
某些数据库允许在上一个查询中使用复合索引，但并不常见。
上文中的第一个查询与索引字段顺序不匹配。第二个查询仅包含了索引中的最后一个字段。第三个查询仅包含了索引中的第一个字段。
●       IN与EXISTS集合运算符——IN运算符常用于测试一系列文字值中的某个值，如下查询所示：
SELECT * FROM AUTHOR WHERE AUTHOR_ID IN (1,2,3,4,5);
EXISTS用于检查值的动态集合，例如由子查询生成的集合，示例如下：
SELECT * FROM AUTHOR WHERE EXISTS
  (SELECT AUTHOR_ID FROM PUBLICATION);
根据IN和EXISTS的用法不同，这两个运算符的效率也可以较高。换言之，对非索引字段执行IN检索就需要进行全表扫描。上文中的IN运算符用于检查主键值，因此会使用索引。EXISTS运算符会对两个表进行全表扫描。可以合并两个查询中的WHERE子句并利用该调用查询与子查询的相关性来提高效率。
●       使用AND和OR-AND和OR命令能够生成多个表达式的逻辑组合，如下面的WHERE子句：
SELECT * FROM AUTHOR
WHERE NAME LIKE '%a%' OR (AUTHOR_ID >= 5 AND AUTHOR_ID <= 10);
使用AND和OR运算符时要注意匹配索引，只要索引字段的使用有所遗漏就会导致全表扫描。
某些数据库允许使用内置的特殊功能，例如正则编程CASE语句。有时用这些语句类似函数的CASE语句，能够替换AND和OR运算符逻辑连接的表达式，从而提高效率。UNION子句是另一种不错的做法。UNION能够将两个不同的查询合并为单组记录，相当于OR逻辑操作。
8.3.2  HAVING子句与WHERE子句
混淆WHERE子句筛选与HAVING子句筛选的功能是常见的编程错误。WHERE子句在数据库中读取记录时(发生I/O活动后)筛选记录。HAVING子句会(GROUP
BY子句的一部分)筛选聚合组，并在数据库所有I/O活动完成后进行筛选。如果使用WHERE子句的效率更高，就不要使用HAVING子句，反之亦然。下面的示例中，可以将HAVING子句中的PUBLISHER_ID限制条件转移到WHERE子句中：
SELECT ISBN, PUBLISHER_ID, AVG(LIST_PRICE)
FROM EDITION
GROUP BY ISBN
HAVING PUBLISHER_ID > 5 AND AVG(LIST_PRICE) > 10;
将HAVING子句中的PUBLISHER_ID限制条件转移到WHERE子句中，修改后的查询如下所示：
SELECT ISBN, PUBLISHER_ID, AVG(LIST_PRICE)
FROM EDITION
WHERE PUBLISHER_ID > 5
GROUP BY ISBN
HAVING AVG(LIST_PRICE) > 10;
为什么要将HAVING子句中的条件移到WHERE子句中呢？如前所述，WHERE会在发生I/O活动时执行筛选。也就是说，上面的代码第二个查询中使用的WHERE应该是理想情况，将不会读取PUBLISHER_ID值小于等于5的记录。在HAVING子句中保留PUBLISHER_ID筛选并不会限制I/O活动，因为HAVING子句会在所有WHERE子句筛选完成后，即所有I/O活动完成后才进行。如果筛选能放入WHERE子句中，应该放入WHERE子句中，而不是放入HAVING子句中，这样大部分情况下就能够保证最高执行效率。
8.3.3  连接
第5章大量讲述了有关连接的内容。本节将从性能调整的角度来简单讨论连接。有很多种不同的连接。有些连接类型本身就具有优秀的性能。有些连接类型则需要一些手动调整。有些连接类型可能很难调整。不同的连接类型及其调整属性如下所示：
●      
内部连接——内部连接是两个表的交集。这种连接通常在两个表的两个参照完整性键之间进行。交集是最高效的连接类型，因为这种连接按照相等性(=符号)在两个表间匹配记录。下面的查询将按照表间主键和外键的链接(一对多关系)，连接PUBLISHER表和EDITION表。这两个表如图8-1所示。
SELECT P.NAME AS Publisher, E.ISBN
FROM PUBLISHER P JOIN EDITION E USING (PUBLISHER_ID);
提示：
自连接是一种特殊类型的交集，会以层次结构返回单个表中多个层次的记录。
●       外部连接——
内部连接返回的是两个表的交集。外部连接的返回值则与内部连接相反。外部连接会返回一个表中的记录，且这些记录是另一个表所不包含的。查询中使用的外部连接过多时，表明数据库模型可能粒度过高。由于外部连接的编写复杂，因此外部连接的调整要比内部连接更难。下文查询会返回PUBLISHER表和EDITION表的交集，以及当前没有出版图书的出版社(即外部连接的外面部分)：
SELECT P.NAME AS Publisher, E.ISBN
FROM PUBLISHER P LEFT OUTER JOIN EDITION E USING (PUBLISHER_ID);
●       交叉连接——
交叉连接是一种笛卡尔乘积，将一个表中的所有记录连接到另一个表中的所有记录，而不考虑两个表键的有意义的参照完整性连接，或其他字段连接。很明显，根据两个表中记录的数量、两个表的关系以及记录总数，交叉连接可能会很慢。下面的查询不考虑PUBLICATION表与EDITION表关系，返回所有出版社以及所有版本。也就是说，返回每个出版社的每一本书，且不考虑该书的出版社。即使某个出版社并未出版某一本书，仍会返回一条记录，并包含完全无关的出版社和图书(这些图书应该属于其他出版社，或者也许根本没有出版社——自发行的图书)。
SELECT P.NAME AS Publisher, E.ISBN
FROM PUBLISHER P CROSS JOIN EDITION E;
提示：
对于所有类型的数据库模型，连接性能调整的最重要因素是最小化连接中的表数。
任何编码较差的连接都可能效率较低。在数据仓库中，由于数据量非常大，因此低效率的连接对数据库性能的影响也非常大。OLTP数据库也可能会受此影响而无法使用，不过由于OLTP数据库的物理尺寸较小，所以无法使用的可能性也较小。我过去曾接手过一个特别的项目，其中有一个极小的10GB数据库，每个查询中包含15个以上表组成的连接。短短的10个记录的Web页面代码需要30秒钟才能将响应返回给客户。我用了大约2个月来说服开发人员对软件进行调整，然后他们花了一周时间进行修改。修改后的网站响应等待时间从30秒变成了不到半秒钟。如果网站的反应时间大于7秒钟，Internet用户一般就会失去兴趣。
调整SQL代码连接时有如下基本规则：
●       先进行大的筛选——
对最大的几个表进行筛选，以此减少记录连接。从筛选程度最高的表开始向下检索，越大的表最好进行越多筛选。将大型表连接到其他表之前，应该尽可能地减少表中检索的记录数。筛选程度越高的表，检索记录所占表中记录的百分比就越小；最大的表筛选程度也最高。
●       使用索引——
在编写代码时尽可能地利用索引，不过非常小的表除外。如果表较小，忽略表中索引能使表的读取更为高效。因此读取较小的表时常常忽略索引。有时甚至读取较大的表时最好也忽略索引，特别是大型表中记录的读取百分比较高的情况。
●       嵌套子查询半连接——
使用子查询嵌套层能够调整连接，至少能够简化连接(以便调整)。这种调整对高度规范化的OLTP数据库模型非常合适，对非规范化的数据仓库数据库模型则无效。
8.3.4  自动计数器
很多关系数据库都有某种自动计数器。自动计数器是用于计算整数值的特殊数据库对象，通常从1开始向上计数。数据库常用自动计数器来填充主键代理键的整数值。在功能匮乏的过去，在自动计数器发明之前，无法使用唯一代理键这类功能，或只能存储在单个中央系统元数据表中。在高并发环境中，使用单个中央系统数据表很可能是灾难性的，会导致系统数据表中出现严重的热块问题。热块问题指同一时间(并发)有大量查询访问物理磁盘的同一区域。多个操作会竞争相同的资源。最终导致资源匮乏、性能下降。
本节拓展了第5章的细节，深入讨论了SQL代码、特别是查询代码的性能调整。设计粗劣的表结构最终会反映到查询中，使查询的性能无法提高到令人满意的程度。设计完善的表结构就能实现高性能的查询。索引对查询性能和数据库模型总体性能也很重要。