使用高效的查询
从理论上来说,得到相同结果的不同代码应该有相同的性能,但遗憾的是,查询优化
器生成的执行计划很大程度上要受到代码外部结构的影响。因此如果想优化查询性能,必须知道如何写代码才能使优化器的执行效率更高。
参数是子查询时,使用EXISTS代替IN
在大多时候, [NOT] IN 和 [NOT] EXISTS 返回的结果是相同的。但是两者用于子查询时, EXISTS 的速度会更快一些,原因有以下两个。
- 如果连接列上建立了索引, 那么子查询不用查实际的表,只需查索引就可以了。
- 如果使用 EXISTS ,那么只要查到一行数据满足条件就会终止查询,不用像使用 IN 时一样扫描全表。 在这一点上 NOT EXISTS 也一样。
当 IN 的参数是子查询时, 数据库首先会执行子查询, 然后将结果存储在一张临时的工作表里(内联视图) ,然后扫描整个视图。 很多情况下这种做法都非常耗费资源。使用 EXISTS 的话, 数据库不会生成临时的工作表。
参数是子查询时,使用连接代替IN
要想改善 IN 的性能,除了使用 EXISTS,还可以使用连接。这种写法至少能用到一张表的“id”列上的索引。 而且, 因为没有了子查询, 所以数据库也不会生成中间表。
避免排序
在数据库内部频繁地进行着暗中的排序。会进行排序的代表性的运算有下面这些:
- GROUP BY 子句
- ORDER BY 子句
- 聚合函数(SUM 、 COUNT 、 AVG 、 MAX 、 MIN )
- DISTINCT
- 集合运算符(UNION 、 INTERSECT 、 EXCEPT )
- 窗口函数(RANK 、 ROW_NUMBER 等)
灵活使用集合运算符的 ALL 可选项
SQL 中有 UNION 、 INTERSECT 、 EXCEPT 三个集合运算符。在默认的使用方式下, 这些运算符会为了排除掉重复数据而进行排序。如果不在乎结果中是否有重复数据, 或者事先知道不会有重复数据,请使用 UNION ALL 代替 UNION 。 这样就不会进行排序了。
使用 EXISTS 代替 DISTINCT
为了排除重复数据, DISTINCT 也会进行排序。 如果需要对两张表的连接结果进行去重, 可以考虑使用 EXISTS 代替 DISTINCT , 以避免排序。
在极值函数中使用索引(MAX/MIN)
使用这两个函数时都会进行排序。 但是如果参数字段上建有索引, 则只需要扫描索引, 不需要扫描整张表。
能写在 WHERE 子句里的条件不要写在 HAVING 子句里
原因通常有两个。 第一个是在使用 GROUP BY 子句聚合时会进行排序, 如果事先通过
WHERE 子句筛选出一部分行, 就能够减轻排序的负担。 第二个是在WHERE 子句的条件里可以使用索引。 HAVING 子句是针对聚合后生成的视图进行筛选的, 但是很多时候聚合后的视图都没有继承原表的索引结构 。
在 GROUP BY 子句和 ORDER BY 子句中使用索引
一般来说, GROUP BY 子句和 ORDER BY 子句都会进行排序, 来对行进行排列和替换。 不过, 通过指定带索引的列作为 GROUP BY 和ORDER BY 的列, 可以实现高速查询。 特别是, 在一些数据库中, 如果操作对象的列上建立的是唯一索引, 那么排序过程本身都会被省略掉。
索引失效的情况
- 条件表达式的左侧不是原始字段
- 使用 IS NULL 或 IS NOT NULL 谓词
- 使用否定形式的比较操作符,如<>、!=、NOT IN
- 使用 OR
- 使用联合索引时, 列的顺序错误
- 使用 LIKE 谓词进行后方一致或中间一致的匹配
- 进行默认的类型转换
减少中间表
在 SQL 中, 子查询的结果会被看成一张新表, 这张新表与原始表一样, 可以通过代码进行操作。 这种高度的相似性使得 SQL 编程具有非常强的灵活性, 但是如果不加限制地大量使用中间表, 会导致查询性能下降。
频繁使用中间表会带来两个问题, 一是展开数据需要耗费内存资源,二是原始表中的索引不容易使用到(特别是聚合时) 。 因此, 尽量减少中间表的使用也是提升性能的一个重要方法。
- 灵活使用 HAVING 子句
- 需要对多个字段使用 IN 谓词时, 将它们汇总到一处
- 先进行连接再进行聚合
- 合理地使用视图