江苏龙网

  1. 主页 > 生活百科 > >

优化 让SQL起飞

SQL

本文给大家总结如何让SQL起飞（优化）
一、SQL写法优化在SQL中，很多时候不同的SQL代码能够得出相同结果 。从理论上来说，我们认为得到相同结果的不同SQL之间应该有相同的性能，但遗憾的是，查询优化器生成的执行计划很大程度上受到SQL代码影响，有快有慢 。因此如果想优化查询性能，我们必须知道如何写出更快的SQL，才能使优化器的执行效率更高 。
1.1 子查询用EXISTS代替IN当IN的参数是子查询时，数据库首先会执行子查询，然后将结果存储在一张临时的工作表里（内联视图），然后扫描整个视图 。很多情况下这种做法都非常耗费资源 。使用EXISTS的话，数据库不会生成临时的工作表 。但是从代码的可读性上来看，IN要比EXISTS好 。使用IN时的代码看起来更加一目了然，易于理解 。因此，如果确信使用IN也能快速获取结果，就没有必要非得改成EXISTS了 。
这里用Class_A表和Class_B举例，

优化 让SQL起飞

文章插图
 
我们试着从Class_A表中查出同时存在于Class_B表中的员工 。下面两条SQL语句返回的结果是一样的，但是使用EXISTS的SQL语句更快一些 。
--慢SELECT *FROM Class_A WHERE id IN (SELECT idFROM Class_B);--快SELECT *FROM Class_AA WHERE EXISTS(SELECT *FROM Class_BBWHERE A.id = B.id);使用EXISTS时更快的原因有以下两个 。
  1. 如果连接列（id）上建立了索引，那么查询 tb_b 时不用查实际的表，只需查索引就可以了 。（同样的IN也可以使用索引，这不是重要原因）
  2. 「如果使用EXISTS，那么只要查到一行数据满足条件就会终止查询，不用像使用IN时一样扫描全表」 。在这一点上NOT EXISTS也一样 。
实际上，大部分情况在子查询数量较小的场景下EXISTS和IN的查询性能不相上下，由EXISTS查询更快第二点可知，子查询数量较大时使用EXISTS才会有明显优势 。
1.2 避免排序并添加索引在SQL语言中，除了ORDER BY子句会进行显示排序外，还有很多操作默认也会在暗中进行排序，如果排序字段没有添加索引，会导致查询性能很慢 。SQL中会进行排序的代表性的运算有下面这些 。
  • GROUP BY子句
  • ORDER BY子句
  • 聚合函数（SUM、COUNT、AVG、MAX、MIN）
  • DISTINCT
  • 集合运算符（UNION、INTERSECT、EXCEPT）
  • 窗口函数（RANK、ROW_NUMBER等）
如上列出的六种运算（除了集合运算符），它们后面跟随或者指定的字段都可以添加索引，这样可以加快排序 。
?
「实际上在DISTINCT关键字、GROUP BY子句、ORDER BY子句、聚合函数跟随的字段都添加索引，不仅能加速查询，还能加速排序 。」
?
1.3 用EXISTS代替DISTINCT为了排除重复数据，我们可能会使用DISTINCT关键字 。如1.2中所说，默认情况下，它也会进行暗中排序 。如果需要对两张表的连接结果进行去重，可以考虑使用EXISTS代替DISTINCT，以避免排序 。这里用Items表和SalesHistory表举例：
优化 让SQL起飞

文章插图
 
我们思考一下如何从上面的商品表Items中找出同时存在于销售记录表SalesHistory中的商品 。简而言之，就是找出有销售记录的商品 。
在一（Items）对多（SalesHistory）的场景下，我们需要对item_no去重，使用DISTINCT去重，因此SQL如下：
SELECT DISTINCT I.item_noFROM Items I INNER JOIN SalesHistory SHON I. item_no = SH. item_no;item_no-------102030使用EXISTS代替DISTINCT去重，SQL如下：
SELECT item_noFROM Items I WHERE EXISTS(SELECT ＊FROM SalesHistory SHWHERE I.item_no = SH.item_no);item_no-------102030这条语句在执行过程中不会进行排序 。而且使用EXISTS和使用连接一样高效 。
1.4 集合运算ALL可选项SQL中有UNION、INTERSECT、EXCEPT三个集合运算符 。在默认的使用方式下，这些运算符会为了排除掉重复数据而进行排序 。
?
MySQL还没有实现INTERSECT和EXCEPT运算
?
如果不在乎结果中是否有重复数据，或者事先知道不会有重复数据，请使用UNION ALL代替UNION 。这样就不会进行排序了 。
1.5 WHERE条件不要写在HAVING字句例如，这里继续用SalesHistory表举例，下面两条SQL语句返回的结果是一样的：
--聚合后使用HAVING子句过滤SELECT sale_date, SUM(quantity)FROM SalesHistory GROUP BY sale_dateHAVING sale_date = '2007-10-01';--聚合前使用WHERE子句过滤SELECT sale_date, SUM(quantity)FROM SalesHistory WHERE sale_date = '2007-10-01' GROUP BY sale_date;


推荐阅读


上一篇：如何在自己的电脑里搭建chatGPT

下一篇：C++ 自适应函数符和函数适配器