LOGO OA教程 ERP教程 模切知识交流 PMS教程 CRM教程 开发文档 其他文档  
 
网站管理员

[转帖]让SQL起飞(优化)

freeflydom
2023年4月19日 11:23 本文热度 336

最近博主看完了《SQL进阶教程》这本书,看完后给博主打开了SQL世界的新大门,对于 SQL 的理解不在局限于以前的常规用法。借用其他读者的评论,

读完醍醐灌顶,对SQL做到了知其然更能知其所以然。全书从头到尾强调了 SQL的内在逻辑是基于集合论和谓词逻辑,而着两条主线恰恰在使用SQL起到了至关重要的指导作用。

本文给大家总结如何让SQL起飞(优化)

一、SQL写法优化

在SQL中,很多时候不同的SQL代码能够得出相同结果。从理论上来说,我们认为得到相同结果的不同SQL之间应该有相同的性能,但遗憾的是,查询优化器生成的执行计划很大程度上受到SQL代码影响,有快有慢。因此如果想优化查询性能,我们必须知道如何写出更快的SQL,才能使优化器的执行效率更高。

1.1 子查询用EXISTS代替IN

当IN的参数是子查询时,数据库首先会执行子查询,然后将结果存储在一张临时的工作表里(内联视图),然后扫描整个视图。很多情况下这种做法都非常耗费资源。使用EXISTS的话,数据库不会生成临时的工作表。但是从代码的可读性上来看,IN要比EXISTS好。使用IN时的代码看起来更加一目了然,易于理解。因此,如果确信使用IN也能快速获取结果,就没有必要非得改成EXISTS了。

这里用Class_A表和Class_B举例,


我们试着从Class_A表中查出同时存在于Class_B表中的员工。下面两条SQL语句返回的结果是一样的,但是使用EXISTS的SQL语句更快一些。

--慢
select *  from Class_A where id IN (select id                
from Class_B);
--快
select *  from Class_A  A where EXISTS
        (select *          
        from Class_B  B          
        where 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表举例:


我们思考一下如何从上面的商品表Items中找出同时存在于销售记录表SalesHistory中的商品。简而言之,就是找出有销售记录的商品。

在一(Items)对多(SalesHistory)的场景下,我们需要对item_no去重,使用DISTINCT去重,因此SQL如下:

select DISTINCT I.item_no   
from Items I INNER JOIN SalesHistory SH     
ON I. item_no = SH. item_no; 
item_no 
-------     
10     
20     
30


使用EXISTS代替DISTINCT去重,SQL如下:

select item_no   
from Items I  
where EXISTS           
(select *               
from SalesHistory SH             
where I.item_no = SH.item_no); 
item_no 
-------     
10     
20     
30


这条语句在执行过程中不会进行排序。而且使用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_date HAVING sale_date = '2007-10-01'; 
--聚合前使用where子句过滤 
select sale_date, SUM(quantity)   
from SalesHistory  
where sale_date = '2007-10-01'  
GROUP BY sale_date;


但是从性能上来看,第二条语句写法效率更高。原因有两个:

  1. 使用GROUP BY子句聚合时会进行排序,如果事先通过where子句筛选出一部分行,就能够减轻排序的负担。

  2. 在where子句的条件里可以使用索引。HAVING子句是针对聚合后生成的视图进行筛选的,但是很多时候聚合后的视图都没有继承原表的索引结构。

二、真的用到索引了吗

2.1 隐式的类型转换

如下,col_1字段是char类型:

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)); -- 走了索引


当查询条件左边和右边类型不一致时会导致索引失效。

2.2 在索引字段上进行运算

如下:

select *   from SomeTable  where col_1 * 1.1 > 100;


在索引字段col_1上进行运算会导致索引不生效,把运算的表达式放到查询条件的右侧,就能用到索引了,像下面这样写就OK了。

where col_1 > 100 / 1.1


如果无法避免在左侧进行运算,那么使用函数索引也是一种办法,但是不太推荐随意这么做。使用索引时,条件表达式的左侧应该是原始字段请牢记,这一点是在优化索引时首要关注的地方。

2.3 使用否定形式

下面这几种否定形式不能用到索引。

  • <>

  • !=

  • NOT

这个是跟具体数据库的优化器有关,如果优化器觉得即使走了索引,还是需要扫描很多很多行的哈,他可以选择直接不走索引。平时我们用!=、<>、not in的时候,要注意一下。

2.4 使用OR查询前后没有同时使用索引

例如下表:

create TABLE test_tb (  id int(11) NOT NULL AUTO_INCREMENT,  name varchar(55) NOT NULL PRIMARY KEY (id) )  ENGINE=InnoDB DEFAULT CHARSET=utf8;


使用OR条件进行查询

select *  from test_tb  where id = 1 OR name = 'tom'


这个SQL的执行条件下,很明显id字段查询会走索引,但是对于OR后面name字段的查询是需要进行全表扫描的。在这个场景下,优化器直接进行一遍全表扫描就完事了。

2.5 使用联合索引时,列的顺序错误

使用联合索引需要满足最左匹配原则,即最左优先。如果你建立一个(col_1, col_2, col_3)的联合索引,相当于建立了 (col_1)、(col_1,col_2)、(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)必须写在查询条件的开头,而且索引中列的顺序不能颠倒。

2.6 使用LIKE查询

并不是用了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%';


2.7 连接字段字符集编码不一致

如果两张表进行连接,关联字段编码不一致会导致关联字段上的索引失效,这是博主在线上经历一次SQL慢查询后的得到的结果,举例如下,有如下两表,它们的name字段都建有索引,但是编码不一致,user表的name字段编码是utf8mb4,user_job表的name字段编码是utf8,

create TABLE `user` (  
 `id` int NOT NULL AUTO_INCREMENT,  
  `name` varchar(255) CHARACTER   SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,   
  `age` int NOT NULL,   
  PRIMARY KEY (`id`),   
  KEY `idx_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8; 
create TABLE `user_job` (  
   `id` int NOT NULL,   
   `userId` int NOT NULL,  
    `job` varchar(255) DEFAULT NULL,   
    `name` varchar(255) DEFAULT NULL,   
    PRIMARY KEY (`id`),   
    KEY `idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;


进行SQL查询如下:

EXPLAIN select *  from `user` u  join user_job j on u.name = j.name

由结果可知,user表的查询没有走索引。想要user表也走索引,那就需要把user表name字段的编码改成utf8即可。

三、减少中间表

在SQL中,子查询的结果会被看成一张新表,这张新表与原始表一样,可以通过代码进行操作。这种高度的相似性使得SQL编程具有非常强的灵活性,但是如果不加限制地大量使用中间表,会导致查询性能下降。

频繁使用中间表会带来两个问题,一是展开数据需要耗费内存资源,二是原始表中的索引不容易使用到(特别是聚合时)。因此,尽量减少中间表的使用也是提升性能的一个重要方法。

3.1 使用HAVING子句

对聚合结果指定筛选条件时,使用HAVING子句是基本原则。不习惯使用HAVING子句的人可能会倾向于像下面这样先生成一张中间表,然后在where子句中指定筛选条件。例如下面:

select *    
from (     
select sale_date, MAX(quantity) max_qty       
from SalesHistory        
GROUP BY sale_date      
) tmp  where max_qty >= 10


然而,对聚合结果指定筛选条件时不需要专门生成中间表,像下面这样使用HAVING子句就可以。

select sale_date, MAX(quantity)   
from SalesHistory  GROUP BY sale_date HAVING MAX(quantity) >= 10;


HAVING子句和聚合操作是同时执行的,所以比起生成中间表后再执行的where子句,效率会更高一些,而且代码看起来也更简洁。

3.2 对多个字段使用IN

当我们需要对多个字段使用IN条件查询时,可以通过 || 操作将字段连接在一起变成一个字符串处理。

select *   
from Addresses1 A1  where id || state || city     
IN (select id || state|| city           
from Addresses2 A2);


这样一来,子查询不用考虑关联性,而且只执行一次就可以。

3.3 先进行连接再进行聚合

连接和聚合同时使用时,先进行连接操作可以避免产生中间表。原因是,从集合运算的角度来看,连接做的是“乘法运算”。连接表双方是一对一、一对多的关系时,连接运算后数据的行数不会增加。而且,因为在很多设计中多对多的关系都可以分解成两个一对多的关系,因此这个技巧在大部分情况下都可以使用。

到此本文讲解完毕,感谢大家阅读,感兴趣的朋友可以点赞加关注,你的支持将是我更新动力😘。


https://juejin.cn/post/7221735480576245819


该文章在 2023/4/19 16:18:45 编辑过
关键字查询
相关文章
正在查询...
点晴ERP是一款针对中小制造业的专业生产管理软件系统,系统成熟度和易用性得到了国内大量中小企业的青睐。
点晴PMS码头管理系统主要针对港口码头集装箱与散货日常运作、调度、堆场、车队、财务费用、相关报表等业务管理,结合码头的业务特点,围绕调度、堆场作业而开发的。集技术的先进性、管理的有效性于一体,是物流码头及其他港口类企业的高效ERP管理信息系统。
点晴WMS仓储管理系统提供了货物产品管理,销售管理,采购管理,仓储管理,仓库管理,保质期管理,货位管理,库位管理,生产管理,WMS管理系统,标签打印,条形码,二维码管理,批号管理软件。
点晴免费OA是一款软件和通用服务都免费,不限功能、不限时间、不限用户的免费OA协同办公管理系统。
Copyright 2010-2024 ClickSun All Rights Reserved