博客
关于我
强烈建议你试试无所不能的chatGPT,快点击我
SQL查询性能优化
阅读量:5323 次
发布时间:2019-06-14

本文共 5033 字,大约阅读时间需要 16 分钟。

使用高效的查询

  1. 使用 EXISTS 代替 IN
-- 查询A表中同时存在B表的数据-- 慢SELECT *  FROM Class_AWHERE id IN (SELECT id               FROM Class_B);               -- 快SELECT *  FROM Class_A AWHERE EXISTS (SELECT *                FROM Class_B B              WHERE A.id = B.id);

EXISTS更快的原因:

  • 如果连接列(id )上建立了索引,那么查询 Class_B 时不用查 实际的表,只需查索引就可以了。
  • 如果使用 EXISTS ,那么只要查到一行数据满足条件就会终止 查询,不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样。
  • 当 IN 的参数是子查询时,数据库首先会执行子查询,然后将结果存 储在一张临时的工作表里(内联视图),然后扫描整个视图,很多情况下很消耗资源。使用 EXISTS 的话,数据库不会生成临时的工作表。
  1. 参数是子查询时,使用连接代替 IN
    -- 使用连接代替INSELECT A.id, A.name  FROM Class_A A INNER JOIN Class_B BON A.id = B.id;
    • 这种写法至少能用到一张表的“id”列上的索引。
    • 因为没有了子查询,所以数据库也不会生成中间表。
  2. 避免排序

会进行排序的代表性的运算有下面这些:

  • GROUP BY 子句
  • ORDER BY 子句
  • 聚合函数(SUMCOUNTAVGMAXMIN
  • DISTINCT
  • 集合运算符(UNIONINTERSECTEXCEPT
  • 窗口函数(RANKROW_NUMBER 等)

排序如果只在内存中进行,那么还好;但是如果内存不足因而需要在 硬盘上排序,那么伴随着“呲啦呲啦”的硬盘访问声,排序的性能也会 急剧恶化

灵活使用集合运算符的 ALL 可选项

SQL 中有 UNION 、INTERSECT 、EXCEPT(MINUS) 三个集合运算符。在默认的使用方式下,这些运算符会为了排除掉重复数据而进行排序。

SELECT * FROM Class_AUNIONSELECT * FROM Class_B;

如果不在乎结果中是否有重复数据,或者事先知道不会有重复数据,请使用 UNION ALL 代替 UNION 。这样就不会进行排序了。

使用 EXISTS 代替 DISTINCT

为了排除重复数据,DISTINCT 也会进行排序。如果需要对两张表的连接结果进行去重,可以考虑使用 EXISTS 代替 DISTINCT ,以避免排序。

-- 使用DISTINCT去重SELECT DISTINCT I.item_no  FROM Items I INNER JOIN SalesHistory SHON I. item_no = SH. item_no;-- 使用exists去重,避免排序SELECT item_no  FROM Items IWHERE EXISTS (SELECT *                FROM SalesHistory SH              WHERE I.item_no = SH.item_no);
在极值函数中使用索引(MAX/MIN

SQL 语言里有 MAX 和 MIN 两个极值函数。使用这两个函数时都会进行排序。但是如果参数字段上建有索引,则

只需要扫描索引,不需要扫描整张表。

能写在 WHERE 子句里的条件不要写在 HAVING 子句里
-- 聚合后使用HAVING 子句过滤SELECT sale_date, SUM(quantity)  FROM SalesHistoryGROUP BY sale_dateHAVING sale_date = '2007-10-01';-- 聚合前使用WHERE 子句过滤SELECT sale_date, SUM(quantity)  FROM SalesHistoryWHERE sale_date = '2007-10-01'GROUP BY sale_date;

但是从性能上来看,第二条语句写法效率更高。原因通常有两个。第一个是在使用 GROUP BY 子句聚合时会进行排序,如果事先通过WHERE 子句筛选出一部分行,就能够减轻排序的负担。第二个是在WHERE 子句的条件里可以使用索引。HAVING 子句是针对聚合后生成的视图进行筛选的,但是很多时候聚合后的视图都没有继承原表的索引结构 。

GROUP BY 子句和 ORDER BY 子句中使用索引

一般来说,GROUP BY 子句和 ORDER BY 子句都会进行排序,来对行 进行排列和替换。不过,通过指定带索引的列作为 GROUP BY 和 ORDER BY 的列,可以实现高速查询。

避免索引未被使用

  1. 在索引字段上进行运算

    -- 不会用到索引SELECT *  FROM SomeTableWHERE col_1 * 1.1 > 100;--把运算的表达式放到查询条件的右侧,就能用到索引了SELECT *  FROM SomeTableWHERE col_1  > 100 / 1.1;--在查询条件的左侧使用函数时,也不能用到索引。SELECT *  FROM SomeTableWHERE SUBSTR(col_1, 1, 1) = 'a';

    使用索引时,条件表达式的左侧应该是原始字段!!!

  2. is null 谓词

    通常,索引字段是不存在 NULL 的,所以指定 IS NULL 和 IS NOT NULL 的话会使得索引无法使用,进而导致查询性能低下。

    在 DB2 和 Oracle 中,IS NULL 条件也能使用索引。这也许是因为它们在实现时为 NULL赋了某个具有特殊含义的值。但是,这个特性不是所有数据库都有的。

  3. 下面这几种否定形式不能用到索引,会进行全表查询。

    • <>

    • !=

    • NOT IN

  4. OR的使用

    在 col_1 和 col_2 上分别建立了不同的索引,或者建立了(col_1, col_2 )这样的联合索引时,如果使用 OR 连接条件,那么要么用不到索引,要么用到了但是效率比 AND 要差很多。

    SELECT *  FROM SomeTableWHERE col_1 > 100OR col_2 = 'abc';
  5. 使用联合索引时,列的顺序错误

    假设存在这样顺序的一个联合索引“col_1, col_2, col_3 ”这时,指定条件的顺序就很重要。

    ○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 AND col_3 = 500;○ SELECT * FROM SomeTable WHERE col_1 = 10 AND col_2 = 100 ;× SELECT * FROM SomeTable WHERE col_1 = 10 AND col_3 = 500 ;× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_3 = 500 ;× SELECT * FROM SomeTable WHERE col_2 = 100 AND col_1 = 10 ;

    联合索引中的第一列(col_1 )必须写在查询条件的开头,而且索引中列的顺序不能颠倒。有些数据库里顺序颠倒后也能使用索引,但是性能还是比顺序正确时差一些。

    如果无法保证查询条件里列的顺序与索引一致,可以考虑将联合索引拆分为多个索引。

  6. LIKE谓词

    使用 LIKE 谓词时,只有前方一致的匹配才能用到索引。

    × SELECT * FROM SomeTable WHERE col_1 LIKE '%a';× SELECT * FROM SomeTable WHERE col_1 LIKE '%a%';○ SELECT * FROM SomeTable WHERE col_1 LIKE 'a%';
  7. 进行默认的类型转换

    char 类型的“col_1”列指定条件的示例

    × SELECT * FROM SomeTable WHERE col_1 = 10;○ SELECT * FROM SomeTable WHERE col_1 = '10';○ SELECT * FROM SomeTable WHERE col_1 = CAST(10, AS CHAR(2));

    默认的类型转换不仅会增加额外的性能开销,还会导致索引不可用, 可以说是有百害而无一利。虽然这样写还不至于出错,但还是不要嫌麻烦,在需要类型转换时显式地进行类型转换吧(转换要写在 条件表达式的右边)。

减少使用中间表

在 SQL 中,子查询的结果会被看成一张新表,这张新表与原始表一 样,可以通过代码进行操作。

频繁使用中间表会带来两个问题:

  • 一是展开数据需要耗费内存资源。

  • 二是原始表中的索引不容易使用到(特别是聚合时)。

因此,尽量减 少中间表的使用也是提升性能的一个重要方法。

  1. 灵活使用having子句

    对聚合结果指定筛选条件时,使用 HAVING 子句是基本原则。

    不习惯使用 HAVING 子句的可能会倾向于生成一 张中间表

    SELECT *  FROM (SELECT sale_date, MAX(quantity) AS max_qtyFROM SalesHistoryGROUP BY sale_date) TMP ←----- 没用的中间表WHERE max_qty >= 10;--对聚合结果指定筛选条件时不需要专门生成中间表SELECT sale_date, MAX(quantity)  FROM SalesHistoryGROUP BY sale_dateHAVING MAX(quantity) >= 10;
  2. 需要对多个字段使用 IN 谓词时,将它们汇总到一处

    -- 这段代码中用到了两个子查询SELECT id, state, city  FROM Addresses1 A1WHERE state IN (SELECT state               FROM Addresses2 A2                 WHERE A1.id = A2.id)  AND city IN (SELECT city                 FROM Addresses2 A2             WHERE A1.id = A2.id);--把字段连接在一起,子查询不用考虑关联性,而且只执行一次就可以             SELECT *  FROM Addresses1 A1 WHERE id || state || city    IN (SELECT id || state|| city          FROM Addresses2 A2);-- 如果所用的数据库实现了行与行的比较SELECT *  FROM Addresses1 A1 WHERE (id, state, city)    IN (SELECT id, state, city          FROM Addresses2 A2);
  3. 合理使用视图

    视图是非常方便的工具。但是,如果没有经过深入思考就定义复杂的视图,可能会带来巨大的性能问题。特别是视图的定义语句中包含以下运算的时候,SQL会非常低效,执行速度也会变得非常慢。

    • 聚合函数(AVGCOUNTSUMMINMAX

    • 集合运算符(UNION 、*NTERSECTEXCEPT 等)

  4. 先进行连接再进行聚合

    连接和聚合同时使用时,先进行连接操作可以避免产 生中间表。原因是,从集合运算的角度来看,连接做的是“乘法运算”。连接表双方是一对一、一对多的关系时,连接运算后数据的行 数不会增加。

转载于:https://www.cnblogs.com/sanzashu/p/11045173.html

你可能感兴趣的文章
like tp
查看>>
posix多线程有感--线程高级编程(线程属性函数总结)(代码)
查看>>
spring-使用MyEcilpse创建demo
查看>>
DCDC(4.5V to 23V -3.3V)
查看>>
kettle导数到user_用于left join_20160928
查看>>
activity 保存数据
查看>>
typescript深copy和浅copy
查看>>
linux下的静态库与动态库详解
查看>>
hbuilder调底层运用,多张图片上传
查看>>
深入理解基于selenium的二次开发
查看>>
较快的maven的settings.xml文件
查看>>
Git之初体验 持续更新
查看>>
Exception in thread "AWT-EventQueue-0" java.lang.IllegalThreadStateException
查看>>
随手练——HDU 5015 矩阵快速幂
查看>>
启动redis一闪就关
查看>>
Maven之setting.xml配置文件详解
查看>>
ASP.NET 4.5 Web Forms and Visual Studio vs2013年入门1
查看>>
SDK目录结构
查看>>
malloc() & free()
查看>>
HDU 2063 过山车
查看>>