精华内容
下载资源
问答
  • sql优化技巧
    千次阅读
    2022-01-18 14:28:49

    1.使用分区

    clickhouse的表,走索引和非索引效率差距很大,在使用一个表进行查询时,必须限制索引字段。避免扫描全表

    • 确定索引分区字段,可以用show create table default.ods_user,查看本地表的建表语句,partition by 的字段就是分区字段。
    • 如果需要限制的时间和分区字段不是同一个字段时,可以扩大分区字段取数区间,然后再过滤

    2.distinct 和 group by

    优先使用group by,distinct满足不了的情况,可以使用group by,
    如果count(distinct uid)查询响应过长,可以使用groupBitmap(uid)代替

    3.global in,global not in 和bitmap

    当涉及到集合in的操作,如果字段是Int类型,可以转化成bitmap进行计算,效率会大幅提高

    -- global in
    select uid from xxx where uid global in (select uid from yyyy)
    
    -- bitmap
    with (select groupbitmapState(toUint64(uid)) from yyyy) as bitmap_uid
    select uid from xxx where bitmapContains(bitmap_uid,uid)==1
    
    -- global not in
    select uid from xxx where uid global in (select uid from yyyy)
    
    -- bitmap
    with (select groupbitmapState(toUint64(uid)) from yyyy) as bitmap_uid
    select uid from xxx where bitmapContains(bitmap_uid,uid)==0
    

    4.使用with子句代替重复查询,查询耗时有可能减半

    -- 重复子查询
    select t1.*, t2.*
    from (
         select *
         from test1_all
         where uid global in (
             select distinct uid
             from test_all
             where toDate(totalDate) = yesterday()
         )
    ) as t1 global
    left join(
        select distinct uid
        from test2_all
        where uid global not in (
            select uid
            from test_all
            where toDate(totalDate) = yesterday()
        )
    ) as t2 using uid;
    
    -- 使用with子句+bitmap代替
    
    with(
         select groupBitmapState(uid)
         from test_all
         where toDate(totalDate) = yesterday()
    ) as bm
    select t1.*, t2.*
    from (
             select *
             from test1_all
             where bitmapContains(bm)==1
    ) as t1 global
    left join(
        select *
        from test2_all
        where bitmapContains(bm)==0
    ) as t2 using uid;
    

    5.global in 和 in

    • in子查询,使用global in
    • in 枚举值,可以使用in,例如 age in (10,18)

    6.global all left join

    clickhouse的join操作,如a left join b,当a表和b表进行join的时候,ck会将b表加载到内存,然后遍历a表数据,与b表进行match,所以必须要左边大表,右边小表,切忌小表join大表。
    并且可以注意以下优化:

    • 能够提前过滤的左右表数据,提前过滤
    • join条件,数值类比字符串类型快
    • global all left join与global left join并无区别

    7.为什么查询sql有时候能执行

    为什么有些sql,有时候能运行有时候不能运行,因为我们的集群的ck机器,有些是128g内存,有些是256g内存的,all表是随机路由的 ,如果路由到256g的可以运行,128g可能就会失败了,对应此种情况,我们做出的解决方案是配置一个新的lb路由,将大查询sql全部路由到256g的机器上

    8.优化整体思路

    • 首先检查是否使用分区字段,必须使用分区字段避免扫描全表
    • 分而治之,如果一个业务的SQL,如果按某个字段,分成N批执行,最终的结果不变,那么就可以采取分批的方式优化,比如mod(uid,10)=batch,这样来实现分10批执行。
    • 提前缩小数据量,如果涉及到的表数据过大,可以提前过滤数据,再进行join操作,而不是join之后再加where条件过滤。尽量做到在每个select都能够过滤一部分数据
    更多相关内容
  • oracle数据库的sql优化技巧,方便自己写出高效,简洁,实用的sql语句。
  • MicroBatch和MiniBatch都是微批处理,只是微批的触发机制略有不同。原理同样是缓存一定的数据后再触发处理,以减少对State的访问,从而提升吞吐并减少数据的输出量。...MicroBatch是MiniBatch的升级版,主要基于事件...
  • 2、降低CPU计算 除了 IO 瓶颈之外,SQL优化中需要考虑的就是CPU运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到...
  • 非常试用的SQL优化技巧非常试用的SQL优化技巧
  • MySql Sql 优化技巧分享

    2020-09-09 19:48:43
    主要介绍了MySql Sql 优化技巧分享,非常不错,具有参考借鉴价值,需要的朋友可以参考下
  • SQL优化技巧

    2016-09-03 10:45:07
    13条SQL优化技巧
  • 有天发现一个带inner join的sql 执行速度虽然不是很慢(0.1-0.2),但是没有达到理想速度。两个表关联,且关联的字段都是主键,查询的字段是索引。  sql如下: SELECT p_item_token.*, p_item.product_type ...
  • 10条SQL优化技巧.docx

    2020-09-05 10:57:47
    10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx10条SQL优化技巧.docx...
  • sql优化SQL优化方法、SQL优化思路、SQL优化实战等
  • 15个常用的sql优化技巧

    千次阅读 多人点赞 2022-03-15 00:09:36
    作者:苏三说技术 前言 sql优化是一个大家都比较关注的热门话题,无论你在面试,还是工作中,都很有...这篇文章从15个方面,分享了sql优化的一些小技巧,希望对你有所帮助。 1. 避免使用select 很多时候,我们写sql语

    原文:https://www.cnblogs.com/12lisu/p/15535940.html
    作者:苏三说技术

    前言

    sql优化是一个大家都比较关注的热门话题,无论你在面试,还是工作中,都很有可能会遇到。

    如果某天你负责的某个线上接口,出现了性能问题,需要做优化。那么你首先想到的很有可能是优化sql语句,因为它的改造成本相对于代码来说也要小得多。

    那么,如何优化sql语句呢?

    这篇文章从15个方面,分享了sql优化的一些小技巧,希望对你有所帮助。

    1. 避免使用select

    很多时候,我们写sql语句时,为了方便,喜欢直接使用select *,一次性查出表中所有列的数据。

    反例:

    select * from user where id=1;
    

    在实际业务场景中,可能我们真正需要使用的只有其中一两列。查了很多数据,但是不用,白白浪费了数据库资源,比如:内存或者cpu。

    此外,多查出来的数据,通过网络IO传输的过程中,也会增加数据传输的时间。

    还有一个最重要的问题是: select不会走覆盖索引,会出现大量的回表操作,而从导致查询sql的性能很低。

    那么,如何优化呢?

    正例

    select name,age from user where id=1;
    

    sql语句查询时,只查需要用到的列,多余的列根本无需查出来。

    2. 用union all代替union

    我们都知道sql语句使用union关键字后,可以获取排重后的数据。而如果使用union all关键字,可以获取所有数据,包含重复的数据。

    反例:

    (select * from user where id=1) 
    union 
    (select * from user where id=2);
    

    排重的过程需要遍历、排序和比较,它更耗时,更消耗cpu资源。所以如果能用union all的时候,尽量不用union。

    正例:

    (select * from user where id=1) 
    union all
    (select * from user where id=2);
    

    除非是有些特殊的场景,比如union all之后,结果集中出现了重复数据,而业务场景中是不允许产生重复数据的,这时可以使用union。

    3. 小表驱动大表

    小表驱动大表,也就是说用小表的数据集驱动大表的数据集。

    假如有order和user两张表,其中order表有10000条数据,而user表有100条数据。时如果想查一下,所有有效的用户下过的订单列表。可以使用in关键字实现:

    select * from order
    where user_id in (select id from user where status=1)
    

    也可以使用exists关键字实现:

    select * from order
    where exists (select 1 from user where order.user_id = user.id and status=1)
    

    前面提到的这种业务场景,使用in关键字去实现业务需求,更加合适。

    为什么呢?

    因为如果sql语句中包含了in关键字,则它会优先执行in里面的子查询语句,然后再执行in外面的语句。如果in里面的数据量很少,作为条件查询速度更快。

    而如果sql语句中包含了exists关键字,它优先执行exists左边的语句(即主查询语句)。然后把它作为条件,去跟右边的语句匹配。如果匹配上,则可以查询出数据。如果匹配不上,数据就被过滤掉了。

    这个需求中,order表有10000条数据,而user表有100条数据。order表是大表,user表是小表。如果order表在左边,则用in关键字性能更好。

    总结一下:

    • in 适用于左边大表,右边小表。
    • exists 适用于左边小表,右边大表。

    不管是用in,还是exists关键字,其核心思想都是用小表驱动大表。

    4. 批量操作

    如果你有一批数据经过业务处理之后,需要插入数据,该怎么办?

    反例:

    for(Order order: list){
       orderMapper.insert(order):
    }
    

    在循环中逐条插入数据。

    insert into order(id,code,user_id) 
    values(123,'001',100);
    

    该操作需要多次请求数据库,才能完成这批数据的插入。

    但众所周知,我们在代码中,每次远程请求数据库,是会消耗一定性能的。而如果我们的代码需要请求多次数据库,才能完成本次业务功能,势必会消耗更多的性能。

    那么如何优化呢?

    正例:

    orderMapper.insertBatch(list):
    

    提供一个批量插入数据的方法。

    insert into order(id,code,user_id) 
    values(123,'001',100),(124,'002',100),(125,'003',101);
    

    这样只需要远程请求一次数据库,sql性能会得到提升,数据量越多,提升越大。

    但需要注意的是,不建议一次批量操作太多的数据,如果数据太多数据库响应也会很慢。批量操作需要把握一个度,建议每批数据尽量控制在500以内。如果数据多于500,则分多批次处理。

    5. 多用limit

    有时候,我们需要查询某些数据中的第一条,比如:查询某个用户下的第一个订单,想看看他第一次的首单时间。

    反例:

    select id, create_date 
    from order 
    where user_id=123 
    order by create_date asc;
    

    根据用户id查询订单,按下单时间排序,先查出该用户所有的订单数据,得到一个订单集合。 然后在代码中,获取第一个元素的数据,即首单的数据,就能获取首单时间。

    List<Order> list = orderMapper.getOrderList();
    Order order = list.get(0);
    

    虽说这种做法在功能上没有问题,但它的效率非常不高,需要先查询出所有的数据,有点浪费资源。

    那么,如何优化呢?

    正例:

    select id, create_date 
    from order 
    where user_id=123 
    order by create_date asc 
    limit 1;
    

    使用limit 1,只返回该用户下单时间最小的那一条数据即可。

    此外,在删除或者修改数据时,为了防止误操作,导致删除或修改了不相干的数据,也可以在sql语句最后加上limit。

    例如:

    update order set status=0,edit_time=now(3) 
    where id>=100 and id<200 limit 100;
    

    这样即使误操作,比如把id搞错了,也不会对太多的数据造成影响。

    6. in中值太多

    对于批量查询接口,我们通常会使用in关键字过滤出数据。比如:想通过指定的一些id,批量查询出用户信息。

    sql语句如下:

    select id,name from category
    where id in (1,2,3...100000000);
    

    如果我们不做任何限制,该查询语句一次性可能会查询出非常多的数据,很容易导致接口超时。

    这时该怎么办呢?

    select id,name from category
    where id in (1,2,3...100)
    limit 500;
    

    可以在sql中对数据用limit做限制。

    不过我们更多的是要在业务代码中加限制,伪代码如下:

    public List<Category> getCategory(List<Long> ids) {
       if(CollectionUtils.isEmpty(ids)) {
          return null;
       }
       if(ids.size() > 500) {
          throw new BusinessException("一次最多允许查询500条记录")
       }
       return mapper.getCategoryList(ids);
    }
    

    7. 增量查询

    有时候,我们需要通过远程接口查询数据,然后同步到另外一个数据库。

    反例:

    select * from user;
    

    如果直接获取所有的数据,然后同步过去。这样虽说非常方便,但是带来了一个非常大的问题,就是如果数据很多的话,查询性能会非常差。

    这时该怎么办呢?

    正例:

    select * 
    from user 
    where id>#{lastId} and create_time >= #{lastCreateTime} 
    limit 100;
    

    按id和时间升序,每次只同步一批数据,这一批数据只有100条记录。每次同步完成之后,保存这100条数据中最大的id和时间,给同步下一批数据的时候用。

    通过这种增量查询的方式,能够提升单次查询的效率。

    8. 高效的分页

    有时候,列表页在查询数据时,为了避免一次性返回过多的数据影响接口性能,我们一般会对查询接口做分页处理。

    在mysql中分页一般用的limit关键字:

    select id,name,age 
    from user limit 10,20;
    

    如果表中数据量少,用limit关键字做分页,没啥问题。但如果表中数据量很多,用它就会出现性能问题。

    比如现在分页参数变成了:

    select id,name,age 
    from user limit 1000000,20;
    

    mysql会查到1000020条数据,然后丢弃前面的1000000条,只查后面的20条数据,这个是非常浪费资源的。

    那么,这种海量数据该怎么分页呢?

    优化sql:

    select id,name,age 
    from user where id > 1000000 limit 20;
    

    先找到上次分页最大的id,然后利用id上的索引查询。不过该方案,要求id是连续的,并且有序的。

    还能使用between优化分页。

    select id,name,age 
    from user where id between 1000000 and 1000020;
    

    需要注意的是between要在唯一索引上分页,不然会出现每页大小不一致的问题。

    9. 用连接查询代替子查询

    mysql中如果需要从两张以上的表中查询出数据的话,一般有两种实现方式:子查询连接查询

    子查询的例子如下:

    select * from order
    where user_id in (select id from user where status=1)
    

    子查询语句可以通过in关键字实现,一个查询语句的条件落在另一个select语句的查询结果中。程序先运行在嵌套在最内层的语句,再运行外层的语句。

    子查询语句的优点是简单,结构化,如果涉及的表数量不多的话。

    但缺点是mysql执行子查询时,需要创建临时表,查询完毕后,需要再删除这些临时表,有一些额外的性能消耗。

    这时可以改成连接查询。 具体例子如下:

    select o.* from order o
    inner join user u on o.user_id = u.id
    where u.status=1
    

    10. join的表不宜过多

    根据阿里巴巴开发者手册的规定,join表的数量不应该超过3个。

    反例:

    select a.name,b.name.c.name,d.name
    from a 
    inner join b on a.id = b.a_id
    inner join c on c.b_id = b.id
    inner join d on d.c_id = c.id
    inner join e on e.d_id = d.id
    inner join f on f.e_id = e.id
    inner join g on g.f_id = f.id
    

    如果join太多,mysql在选择索引的时候会非常复杂,很容易选错索引。

    并且如果没有命中中,nested loop join 就是分别从两个表读一行数据进行两两对比,复杂度是 n^2。

    所以我们应该尽量控制join表的数量。

    正例:

    select a.name,b.name.c.name,a.d_name 
    from a 
    inner join b on a.id = b.a_id
    inner join c on c.b_id = b.id
    

    如果实现业务场景中需要查询出另外几张表中的数据,可以在a、b、c表中冗余专门的字段,比如:在表a中冗余d_name字段,保存需要查询出的数据。

    不过我之前也见过有些ERP系统,并发量不大,但业务比较复杂,需要join十几张表才能查询出数据。

    所以join表的数量要根据系统的实际情况决定,不能一概而论,尽量越少越好。

    11. join时要注意

    我们在涉及到多张表联合查询的时候,一般会使用join关键字。

    而join使用最多的是left join和inner join。

    • left join:求两个表的交集外加左表剩下的数据。
    • inner join:求两个表交集的数据。

    使用inner join的示例如下:

    select o.id,o.code,u.name 
    from order o 
    inner join user u on o.user_id = u.id
    where u.status=1;
    

    如果两张表使用inner join关联,mysql会自动选择两张表中的小表,去驱动大表,所以性能上不会有太大的问题。

    使用left join的示例如下:

    select o.id,o.code,u.name 
    from order o 
    left join user u on o.user_id = u.id
    where u.status=1;
    

    如果两张表使用left join关联,mysql会默认用left join关键字左边的表,去驱动它右边的表。如果左边的表数据很多时,就会出现性能问题。

    要特别注意的是在用left join关联查询时,左边要用小表,右边可以用大表。如果能用inner join的地方,尽量少用left join。

    12. 控制索引的数量

    众所周知,索引能够显著的提升查询sql的性能,但索引数量并非越多越好。

    因为表中新增数据时,需要同时为它创建索引,而索引是需要额外的存储空间的,而且还会有一定的性能消耗。

    阿里巴巴的开发者手册中规定,单表的索引数量应该尽量控制在5个以内,并且单个索引中的字段数不超过5个。

    mysql使用的B+树的结构来保存索引的,在insert、update和delete操作时,需要更新B+树索引。如果索引过多,会消耗很多额外的性能。

    那么,问题来了,如果表中的索引太多,超过了5个该怎么办?

    这个问题要辩证的看,如果你的系统并发量不高,表中的数据量也不多,其实超过5个也可以,只要不要超过太多就行。

    但对于一些高并发的系统,请务必遵守单表索引数量不要超过5的限制。

    那么,高并发系统如何优化索引数量?

    能够建联合索引,就别建单个索引,可以删除无用的单个索引。

    将部分查询功能迁移到其他类型的数据库中,比如:Elastic Seach、HBase等,在业务表中只需要建几个关键索引即可。

    13. 选择合理的字段类型

    char表示固定字符串类型,该类型的字段存储空间的固定的,会浪费存储空间。

    alter table order 
    add column code char(20) NOT NULL;
    

    varchar表示变长字符串类型,该类型的字段存储空间会根据实际数据的长度调整,不会浪费存储空间。

    alter table order 
    add column code varchar(20) NOT NULL;
    

    如果是长度固定的字段,比如用户手机号,一般都是11位的,可以定义成char类型,长度是11字节。

    但如果是企业名称字段,假如定义成char类型,就有问题了。

    如果长度定义得太长,比如定义成了200字节,而实际企业长度只有50字节,则会浪费150字节的存储空间。

    如果长度定义得太短,比如定义成了50字节,但实际企业名称有100字节,就会存储不下,而抛出异常。

    所以建议将企业名称改成varchar类型,变长字段存储空间小,可以节省存储空间,而且对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    我们在选择字段类型时,应该遵循这样的原则:

    • 能用数字类型,就不用字符串,因为字符的处理往往比数字要慢。
    • 尽可能使用小的类型,比如:用bit存布尔值,用tinyint存枚举值等。
    • 长度固定的字符串字段,用char类型。
    • 长度可变的字符串字段,用varchar类型。
    • 金额字段用decimal,避免精度丢失问题。

    还有很多原则,这里就不一一列举了。

    14. 提升group by的效率

    我们有很多业务场景需要使用group by关键字,它主要的功能是去重和分组。

    通常它会跟having一起配合使用,表示分组后再根据一定的条件过滤数据。

    反例:

    select user_id,user_name from order
    group by user_id
    having user_id <= 200;
    

    这种写法性能不好,它先把所有的订单根据用户id分组之后,再去过滤用户id大于等于200的用户。

    分组是一个相对耗时的操作,为什么我们不先缩小数据的范围之后,再分组呢?

    正例:

    select user_id,user_name from order
    where user_id <= 200
    group by user_id
    

    使用where条件在分组前,就把多余的数据过滤掉了,这样分组时效率就会更高一些。

    其实这是一种思路,不仅限于group by的优化。我们的sql语句在做一些耗时的操作之前,应尽可能缩小数据范围,这样能提升sql整体的性能。

    15. 索引优化

    sql优化当中,有一个非常重要的内容就是:索引优化。

    很多时候sql语句,走了索引,和没有走索引,执行效率差别很大。所以索引优化被作为sql优化的首选。

    索引优化的第一步是:检查sql语句有没有走索引。

    那么,如何查看sql走了索引没?

    可以使用explain命令,查看mysql的执行计划。

    例如:

    explain select * from `order` where code='002';
    

    结果:

    通过这几列可以判断索引使用情况,执行计划包含列的含义如下图所示:

    如果你想进一步了解explain的详细用法,可以看看我的另一篇文章《explain | 索引优化的这把绝世好剑,你真的会用吗?》

    说实话,sql语句没有走索引,排除没有建索引之外,最大的可能性是索引失效了。

    下面说说索引失效的常见原因:

    如果不是上面的这些原因,则需要再进一步排查一下其他原因。

    此外,你有没有遇到过这样一种情况:明明是同一条sql,只有入参不同而已。有的时候走的索引a,有的时候却走的索引b?

    没错,有时候mysql会选错索引。

    必要时可以使用force index来强制查询sql走某个索引。

    如果这篇文章对您有所帮助,帮忙点个赞哦!!!

    展开全文
  • sql优化技巧

    2013-07-04 19:47:33
    sql优化很重要
  • 搞懂这些SQL优化技巧,面试横着走

    万次阅读 多人点赞 2020-08-13 12:10:58
    无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注...

    小伙伴想精准查找自己想看的MySQL文章?喏 → MySQL专栏目录 | 点击这里

    BATJTMD等大厂的面试难度越来越高,但无论从大厂还是到小公司,一直未变的一个重点就是对SQL优化经验的考察。一提到数据库,先“说一说你对SQL优化的见解吧?”。SQL优化已经成为衡量程序猿优秀与否的硬性指标,甚至在各大厂招聘岗位职能上都有明码标注,如果是你,在这个问题上能吊打面试官还是会被吊打呢?

    目录

    SELECT语句 - 语法顺序:

    SELECT语句 - 执行顺序:

    SQL优化策略

    一、避免不走索引的场景

    二、SELECT语句其他优化

    三、增删改 DML 语句优化

    四、查询条件优化

    五、建表优化

    一张照片背后的故事(自娱角)


    有朋友疑问到,SQL优化真的有这么重要么?如下图所示,SQL优化在提升系统性能中是:(成本最低 && 优化效果最明显) 的途径。如果你的团队在SQL优化这方面搞得很优秀,对你们整个大型系统可用性方面无疑是一个质的跨越,真的能让你们老板省下不止几沓子钱。

    • 优化成本:硬件>系统配置>数据库表结构>SQL及索引。
    • 优化效果:硬件<系统配置<数据库表结构<SQL及索引。
    String result = "嗯,不错,";
    
    if ("SQL优化经验足") {
        if ("熟悉事务锁") {
            if ("并发场景处理666") {
                if ("会打王者荣耀") {
                    result += "明天入职" 
                }
            }
        }
    } else {
        result += "先回去等消息吧";
    } 
    
    Logger.info("面试官:" + result );

    别看了,上面这是一道送命题。

    好了我们言归正传,首先,对于MySQL层优化我一般遵从五个原则:

    1. 减少数据访问: 设置合理的字段类型,启用压缩,通过索引访问等减少磁盘IO
    2. 返回更少的数据: 只返回需要的字段和数据分页处理 减少磁盘io及网络io
    3. 减少交互次数: 批量DML操作,函数存储等减少数据连接次数
    4. 减少服务器CPU开销: 尽量减少数据库排序操作以及全表查询,减少cpu 内存占用
    5. 利用更多资源: 使用表分区,可以增加并行操作,更大限度利用cpu资源

    总结到SQL优化中,就三点:

    • 最大化利用索引;
    • 尽可能避免全表扫描;
    • 减少无效数据的查询;

    理解SQL优化原理 ,首先要搞清楚SQL执行顺序:

    SELECT语句 - 语法顺序:

    1. SELECT 
    2. DISTINCT <select_list>
    3. FROM <left_table>
    4. <join_type> JOIN <right_table>
    5. ON <join_condition>
    6. WHERE <where_condition>
    7. GROUP BY <group_by_list>
    8. HAVING <having_condition>
    9. ORDER BY <order_by_condition>
    10.LIMIT <limit_number>

     

    SELECT语句 - 执行顺序:

    FROM
    <表名> # 选取表,将多个表数据通过笛卡尔积变成一个表。
    ON
    <筛选条件> # 对笛卡尔积的虚表进行筛选
    JOIN <join, left join, right join...> 
    <join表> # 指定join,用于添加数据到on之后的虚表中,例如left join会将左表的剩余数据添加到虚表中
    WHERE
    <where条件> # 对上述虚表进行筛选
    GROUP BY
    <分组条件> # 分组
    <SUM()等聚合函数> # 用于having子句进行判断,在书写上这类聚合函数是写在having判断里面的
    HAVING
    <分组筛选> # 对分组后的结果进行聚合筛选
    SELECT
    <返回数据列表> # 返回的单列必须在group by子句中,聚合函数除外
    DISTINCT
    # 数据除重
    ORDER BY
    <排序条件> # 排序
    LIMIT
    <行数限制>


    SQL优化策略

    声明:以下SQL优化策略适用于数据量较大的场景下,如果数据量较小,没必要以此为准,以免画蛇添足。


    一、避免不走索引的场景

    1. 尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE username LIKE '%陈%'

    优化方式:尽量在字段后面使用模糊查询。如下:

    SELECT * FROM t WHERE username LIKE '陈%'

    如果需求是要在前面使用模糊查询,

    • 使用MySQL内置函数INSTR(str,substr) 来匹配,作用类似于java中的indexOf(),查询字符串出现的角标位置,可参阅《MySQL模糊查询用法大全(正则、通配符、内置函数等)》
    • 使用FullText全文索引,用match against 检索
    • 数据量较大的情况,建议引用ElasticSearch、solr,亿级数据量检索速度秒级
    • 当表数据量较少(几千条儿那种),别整花里胡哨的,直接用like '%xx%'。

     

    2. 尽量避免使用in 和not in,会导致引擎走全表扫描。如下:

    SELECT * FROM t WHERE id IN (2,3);

    优化方式:如果是连续数值,可以用between代替。如下:

    SELECT * FROM t WHERE id BETWEEN 2 AND 3;

    如果是子查询,可以用exists代替。详情见《MySql中如何用exists代替in》如下:

    -- 不走索引
    select * from A where A.id in (select id from B);
    -- 走索引
    select * from A where exists (select * from B where B.id = A.id);


     
    3. 尽量避免使用 or,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE id = 1 OR id = 3

    优化方式:可以用union代替or。如下:

    SELECT * FROM t WHERE id = 1
       UNION
    SELECT * FROM t WHERE id = 3

    4. 尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:

    SELECT * FROM t WHERE score IS NULL

    优化方式:可以给字段添加默认值0,对0值进行判断。如下:

    SELECT * FROM t WHERE score = 0

     

    5.尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。

    可以将表达式、函数操作移动到等号右侧。如下:

    -- 全表扫描
    SELECT * FROM T WHERE score/10 = 9
    -- 走索引
    SELECT * FROM T WHERE score = 10*9

     

    6. 当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:

    SELECT username, age, sex FROM T WHERE 1=1

    优化方式:用代码拼装sql时进行判断,没 where 条件就去掉 where,有where条件就加 and。


    7. 查询条件不能用 <> 或者 !=

    使用索引列作为条件进行查询时,需要避免使用<>或者!=等判断条件。如确实业务需要,使用到不等于符号,需要在重新评估索引建立,避免在此字段上建立索引,改由查询条件中其他索引字段代替。


    8. where条件仅包含复合索引非前置列

    如下:复合(联合)索引包含key_part1,key_part2,key_part3三列,但SQL语句没有包含索引前置列"key_part1",按照MySQL联合索引的最左匹配原则,不会走联合索引。详情参考《联合索引的使用原理》

    select col1 from table where key_part2=1 and key_part3=2


    9. 隐式类型转换造成不使用索引 

    如下SQL语句由于索引对列类型为varchar,但给定的值为数值,涉及隐式类型转换,造成不能正确走索引。 

    select col1 from table where col_varchar=123; 


    10. order by 条件要与where中条件一致,否则order by不会利用索引进行排序

    -- 不走age索引
    SELECT * FROM t order by age;
    
    -- 走age索引
    SELECT * FROM t where age > 0 order by age;

    对于上面的语句,数据库的处理顺序是:

    • 第一步:根据where条件和统计信息生成执行计划,得到数据。
    • 第二步:将得到的数据排序。当执行处理数据(order by)时,数据库会先查看第一步的执行计划,看order by 的字段是否在执行计划中利用了索引。如果是,则可以利用索引顺序而直接取得已经排好序的数据。如果不是,则重新进行排序操作。
    • 第三步:返回排序后的数据。

    当order by 中的字段出现在where条件中时,才会利用索引而不再二次排序,更准确的说,order by 中的字段在执行计划中利用了索引时,不用排序操作。

    这个结论不仅对order by有效,对其他需要排序的操作也有效。比如group by 、union 、distinct等。

    11. 正确使用hint优化语句

    MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言,处于版本变更带来的表结构索引变化,更建议避免使用hint,而是通过Analyze table多收集统计信息。但在特定场合下,指定hint可以排除其他索引干扰而指定更优的执行计划。

    1. USE INDEX 在你查询语句中表名的后面,添加 USE INDEX 来提供希望 MySQL 去参考的索引列表,就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)...
    2. IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引,可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) ...
    3. FORCE INDEX 为强制 MySQL 使用一个特定的索引,可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) ...

    在查询的时候,数据库系统会自动分析查询语句,并选择一个最合适的索引。但是很多时候,数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引,可以使用FORCE INDEX强制查询使用指定的索引。《MySQL中特别实用的几种SQL语句送给大家》博文建议阅读,干货

    例如:

    SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

     

    二、SELECT语句其他优化

    1. 避免出现select *

    首先,select * 操作在任何类型数据库中都不是一个好的SQL编写习惯。

    使用select * 取出全部列,会让优化器无法完成索引覆盖扫描这类优化,会影响优化器对执行计划的选择,也会增加网络带宽消耗,更会带来额外的I/O,内存和CPU消耗。

    建议提出业务实际需要的列数,将指定列名以取代select *。具体详情见《为什么大家都说SELECT * 效率低》


    2. 避免出现不确定结果的函数

    特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句,使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。


    3.多表关联查询时,小表在前,大表在后。

    在MySQL中,执行 from 后的表关联查询是从左往右执行的(Oracle相反),第一张表会涉及到全表扫描,所以将小表放在前面,先扫小表,扫描快效率较高,在扫描后面的大表,或许只扫描大表的前100行就符合返回条件并return了。

    例如:表1有50条数据,表2有30亿条数据;如果全表扫描表2,你品,那就先去吃个饭再说吧是吧。

    4. 使用表的别名

    当在SQL语句中连接多个表时,请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少哪些友列名歧义引起的语法错误。


    5. 用where字句替换HAVING字句

    避免使用HAVING字句,因为HAVING只会在检索出所有记录之后才对结果集进行过滤,而where则是在聚合前刷选记录,如果能通过where字句限制记录的数目,那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤,除此之外,应该将条件写在where字句中。

    where和having的区别:where后面不能使用组函数

    6.调整Where字句中的连接顺序

    MySQL采用从左往右,自上而下的顺序解析where子句。根据这个原理,应将过滤数据多的条件往前放,最快速度缩小结果集。


    三、增删改 DML 语句优化

    1. 大批量插入数据

    如果同时执行大量的插入,建议使用多个值的INSERT语句(方法二)。这比使用分开INSERT语句快(方法一),一般情况下批量插入效率有几倍的差别。

    方法一:

    insert into T values(1,2); 
    
    insert into T values(1,3); 
    
    insert into T values(1,4);

    方法二:

    Insert into T values(1,2),(1,3),(1,4); 


    选择后一种方法的原因有三。 

    • 减少SQL语句解析的操作,MySQL没有类似Oracle的share pool,采用方法二,只需要解析一次就能进行数据的插入操作;
    • 在特定场景可以减少对DB连接次数
    • SQL语句较短,可以减少网络传输的IO。

    2. 适当使用commit

    适当使用commit可以释放事务占用的资源而减少消耗,commit后能释放的资源如下:

    • 事务占用的undo数据块;
    • 事务在redo log中记录的数据块; 
    • 释放事务施加的,减少锁争用影响性能。特别是在需要使用delete删除大量数据的时候,必须分解删除量并定期commit。


    3. 避免重复查询更新的数据

    针对业务中经常出现的更新行同时又希望获得改行信息的需求,MySQL并不支持PostgreSQL那样的UPDATE RETURNING语法,在MySQL中可以通过变量实现。

    例如,更新一行记录的时间戳,同时希望查询当前记录中存放的时间戳是什么,简单方法实现:

    Update t1 set time=now() where col1=1; 
    
    Select time from t1 where id =1; 

    使用变量,可以重写为以下方式: 

    Update t1 set time=now () where col1=1 and @now: = now (); 
    
    Select @now; 

    前后二者都需要两次网络来回,但使用变量避免了再次访问数据表,特别是当t1表数据量较大时,后者比前者快很多。


    4.查询优先还是更新(insert、update、delete)优先

    MySQL 还允许改变语句调度的优先级,它可以使来自多个客户端的查询更好地协作,这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。我们首先应该确定应用的类型,判断应用是以查询为主还是以更新为主的,是确保查询效率还是确保更新的效率,决定是查询优先还是更新优先。下面我们提到的改变调度策略的方法主要是针对只存在表锁的存储引擎,比如 MyISAM 、MEMROY、MERGE,对于Innodb 存储引擎,语句的执行是由获得行锁的顺序决定的。MySQL 的默认的调度策略可用总结如下:

    1)写入操作优先于读取操作。

    2)对某张数据表的写入操作某一时刻只能发生一次,写入请求按照它们到达的次序来处理。

    3)对某张数据表的多个读取操作可以同时地进行。MySQL 提供了几个语句调节符,允许你修改它的调度策略:

    • LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE;
    • HIGH_PRIORITY关键字应用于SELECT和INSERT语句;
    • DELAYED关键字应用于INSERT和REPLACE语句。


           如果写入操作是一个 LOW_PRIORITY(低优先级)请求,那么系统就不会认为它的优先级高于读取操作。在这种情况下,如果写入者在等待的时候,第二个读取者到达了,那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候,才允许写入者开始操作。这种调度修改可能存在 LOW_PRIORITY写入操作永远被阻塞的情况。

    SELECT 查询的HIGH_PRIORITY(高优先级)关键字也类似。它允许SELECT 插入正在等待的写入操作之前,即使在正常情况下写入操作的优先级更高。另外一种影响是,高优先级的 SELECT 在正常的 SELECT 语句之前执行,因为这些语句会被写入操作阻塞。如果希望所有支持LOW_PRIORITY 选项的语句都默认地按照低优先级来处理,那么 请使用--low-priority-updates 选项来启动服务器。通过使用 INSERTHIGH_PRIORITY 来把 INSERT 语句提高到正常的写入优先级,可以消除该选项对单个INSERT语句的影响。


    四、查询条件优化

    1. 对于复杂的查询,可以使用中间临时表 暂存数据;


    2. 优化group by语句

    默认情况下,MySQL 会对GROUP BY分组的所有值进行排序,如 “GROUP BY col1,col2,....;” 查询的方法如同在查询中指定 “ORDER BY col1,col2,...;” 如果显式包括一个包含相同的列的 ORDER BY子句,MySQL 可以毫不减速地对它进行优化,尽管仍然进行排序。

    因此,如果查询包括 GROUP BY 但你并不想对分组的值进行排序,你可以指定 ORDER BY NULL禁止排序。例如:

    SELECT col1, col2, COUNT(*) FROM table GROUP BY col1, col2 ORDER BY NULL ;

    3. 优化join语句

    MySQL中可以通过子查询来使用 SELECT 语句来创建一个单列的查询结果,然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的 SQL 操作,同时也可以避免事务或者表锁死,并且写起来也很容易。但是,有些情况下,子查询可以被更有效率的连接(JOIN)..替代。


    例子:假设要将所有没有订单记录的用户取出来,可以用下面这个查询完成:

    SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo )

    如果使用连接(JOIN).. 来完成这个查询工作,速度将会有所提升。尤其是当 salesinfo表中对 CustomerID 建有索引的话,性能将会更好,查询如下:

    SELECT col1 FROM customerinfo 
       LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID 
          WHERE salesinfo.CustomerID IS NULL 

    连接(JOIN).. 之所以更有效率一些,是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。


    4. 优化union查询

    MySQL通过创建并填充临时表的方式来执行union查询。除非确实要消除重复的行,否则建议使用union all。原因在于如果没有all这个关键词,MySQL会给临时表加上distinct选项,这会导致对整个临时表的数据做唯一性校验,这样做的消耗相当高。

    高效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION ALL 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST'; 

    低效:

    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL1 = 10 
    
    UNION 
    
    SELECT COL1, COL2, COL3 FROM TABLE WHERE COL3= 'TEST';

    5.拆分复杂SQL为多个小SQL,避免大事务

    • 简单的SQL容易使用到MySQL的QUERY CACHE; 
    • 减少锁表时间特别是使用MyISAM存储引擎的表; 
    • 可以使用多核CPU。

    6. 使用truncate代替delete

    当删除全表中记录时,使用delete语句的操作会被记录到undo块中,删除记录也记录binlog,当确认需要删除全表时,会产生很大量的binlog并占用大量的undo数据块,此时既没有很好的效率也占用了大量的资源。

    使用truncate替代,不会记录可恢复的信息,数据不能被恢复。也因此使用truncate操作有其极少的资源占用与极快的时间。另外,使用truncate可以回收表的水位,使自增字段值归零。

    7. 使用合理的分页方式以提高分页效率

    使用合理的分页方式以提高分页效率 针对展现等分页需求,合适的分页方式能够提高分页的效率。

    案例1: 

    select * from t where thread_id = 10000 and deleted = 0 
       order by gmt_create asc limit 0, 15;


           上述例子通过一次性根据过滤条件取出所有字段进行排序返回。数据访问开销=索引IO+索引全部记录结果对应的表数据IO。因此,该种写法越翻到后面执行效率越差,时间越长,尤其表数据量很大的时候。

    适用场景:当中间结果集很小(10000行以下)或者查询条件复杂(指涉及多个不同查询字段或者多表连接)时适用。


    案例2: 

    select t.* from (select id from t where thread_id = 10000 and deleted = 0
       order by gmt_create asc limit 0, 15) a, t 
          where a.id = t.id; 

    上述例子必须满足t表主键是id列,且有覆盖索引secondary key:(thread_id, deleted, gmt_create)。通过先根据过滤条件利用覆盖索引取出主键id进行排序,再进行join操作取出其他字段。数据访问开销=索引IO+索引分页后结果(例子中是15行)对应的表数据IO。因此,该写法每次翻页消耗的资源和时间都基本相同,就像翻第一页一样。

    适用场景:当查询和排序字段(即where子句和order by子句涉及的字段)有对应覆盖索引时,且中间结果集很大的情况时适用。

    五、建表优化

    1、在表中建立索引,优先考虑where、order by使用到的字段。


    2、尽量使用数字型字段(如性别,男:1 女:2),若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。
    这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。


    3、查询数据量大的表 会造成查询缓慢。主要的原因是扫描行数过多。这个时候可以通过程序,分段分页进行查询,循环遍历,将结果合并处理进行展示。要查询100000到100050的数据,如下:

    SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID ASC) AS rowid,* 
       FROM infoTab)t WHERE t.rowid > 100000 AND t.rowid <= 100050

    4、用varchar/nvarchar 代替 char/nchar

    尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

    不要以为 NULL 不需要空间,比如:char(100) 型,在字段建立时,空间就固定了, 不管是否插入值(NULL也包含在内),都是占用 100个字符的空间的,如果是varchar这样的变长字段, null 不占用空间。

    一张照片背后的故事(自娱角)

     

    这是由蒋玉树先生所拍摄的照片《小店》
    在四川凉山
    母亲临时有事
    让自己的女儿帮忙看店

    突然天上下起了雪
    在这个只有一面墙的小店里
    女孩儿一边搓着手
    一边欣赏着美景

    这些MySQL文章你可能也会喜欢:

    《MySQL中特别实用的几种SQL语句送给大家》

    《SQL 查询语句先执行 SELECT?兄弟你认真的么?》

    《有意思,原来SQL中的NULL是这么回事儿》

    展开全文
  • PostgreSQL常用SQL优化技巧

    千次阅读 2022-01-07 16:10:06
    PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似,因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方,今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。 1...

    PostgreSQL的SQL优化技巧其实和大多数使用CBO优化器的数据库类似,因此一些常用的SQL优化改写技巧在PostgreSQL也是能够使用的。当然也会有一些不同的地方,今天我们来看看一些在PostgreSQL常用的SQL优化改写技巧。

    1、标量子查询与filter

    当一个查询在select和from之间,那么这种子查询就是标量子查询。实际应用中,很多人在写SQL时为了方便会写一堆标量子查询的SQL,在表数据不大时,一般并不会有什么影响,但当数据量较大时,往往会对性能造成巨大影响。

    因为标量子查询类似于一个天然的嵌套循环,而且驱动表固定为主表。如下所示:

    bill=# explain select empno,ename,sal,deptno,
    bill-# (select d.dname from dept d where d.deptno = e.deptno) as dname
    bill-# from emp e;
                              QUERY PLAN
    --------------------------------------------------------------
     Seq Scan on emp e  (cost=0.00..15.84 rows=14 width=64)
       SubPlan 1
         ->  Seq Scan on dept d  (cost=0.00..1.05 rows=1 width=9)
               Filter: (deptno = e.deptno)
    (4 rows)
    

    对于上面的SQL,emp表每输出一行数据,都要去dept表中全表扫描一遍。
    而我们都知道,嵌套循环的被驱动表的连接列必须包含在索引中,同理,标量子查询的表的连接列也必须包含在索引中。但是我们在实际写SQL时还是要避免使用标量子查询,否则主表返回大量数据时,子表得被多次遍历,从而对SQL性能产生巨大影响。

    那么对于标量子查询的SQL我们该怎么优化呢?最常用的就是改写成外连接,这样对于PostgreSQL的优化器而言可以根据实际情况去选择表的连接方式。这里需要注意的是,不能将标量子查询改成内连接,我们前面的例子中也可以看到,标量子查询实际是一个传值的过程,当主表传值给子表时,如果没有相应的值则会显示NULL,而如果使用内连接的话这部分数据就丢失了。

    因此,上面的标量子查询可以改写成:
    可以看到,优化器根据实际情况选择了更合适的hash join。

    bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
    bill-# from emp e
    bill-# left join dept d on (d.deptno = e.deptno);
                                QUERY PLAN
    -------------------------------------------------------------------
     Hash Left Join  (cost=1.09..2.31 rows=14 width=27)
       Hash Cond: (e.deptno = d.deptno)
       ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=18)
       ->  Hash  (cost=1.04..1.04 rows=4 width=13)
             ->  Seq Scan on dept d  (cost=0.00..1.04 rows=4 width=13)
    (5 rows)
    

    当主表连接列是外键,而子表的连接列是主键时,使用内连接也可以,因为外键自然不会存在NULL值。

    bill=# explain select e.empno,e.ename,e.sal,e.deptno,d.dname
    bill-# from emp e
    bill-# inner join dept d on (d.deptno = e.deptno);
                                QUERY PLAN
    -------------------------------------------------------------------
     Hash Join  (cost=1.09..2.31 rows=14 width=27)
       Hash Cond: (e.deptno = d.deptno)
       ->  Seq Scan on emp e  (cost=0.00..1.14 rows=14 width=18)
       ->  Hash  (cost=1.04..1.04 rows=4 width=13)
             ->  Seq Scan on dept d  (cost=0.00..1.04 rows=4 width=13)
    (5 rows)
    

    除了标量子查询外,往往filter也会产生类似的情况,因为在filter中驱动表也会被固定住,那么优化器可能会选择低效的执行计划。而对于PostgreSQL而言本身也不支持hint功能,如果错误的执行计划被固定,那么往往只能去改写SQL。

    这里说明下下filter,在PostgreSQL中filter主要有2种情况,一种是我们常见的where后面过滤数据的,这种一般不会产生什么性能问题,例如:

    bill=# explain select * from t where id < 10;
                          QUERY PLAN
    -------------------------------------------------------
     Seq Scan on t  (cost=0.00..16925.00 rows=100 width=4)
       Filter: (id < 10)
    (2 rows)
    

    而另一种就是filter中是一些表的连接条件,这种呢便是我们前面说的情况,往往需要去关注的,例如:

    bill=# explain select  exists (select 1 from t where t.id=n.id) from n;
                             QUERY PLAN
    -------------------------------------------------------------
     Seq Scan on n  (cost=0.00..169250145.00 rows=10000 width=1)
       SubPlan 1
         ->  Seq Scan on t  (cost=0.00..16925.00 rows=1 width=0)
               Filter: (id = n.id)
    (4 rows)
    

    那么哪些写法会容易产生filter呢?在PostgreSQL中当使用exists或者not exists时,或者子查询中有固话子查询的关键词,如union、union all、cube、rollup、limit等,那么执行计划往往容易产生filter。
    因此上面的SQL我们用in去替换exists进行改写:

    bill=# explain select id in (select id from t) from n;
                                   QUERY PLAN
    -------------------------------------------------------------------------
     Seq Scan on n  (cost=0.00..129160170.00 rows=10000 width=1)
       SubPlan 1
         ->  Materialize  (cost=0.00..23332.00 rows=1000000 width=4)
               ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
    (4 rows)
    

    除此之外,在PostgreSQL中我们更推荐使用= any的方式去改写该类SQL:

    bill=# explain select id = any(array(select id from t)) from n;
                                QUERY PLAN
    -------------------------------------------------------------------
     Seq Scan on n  (cost=14425.00..14695.00 rows=10000 width=1)
       InitPlan 1 (returns $0)
         ->  Seq Scan on t  (cost=0.00..14425.00 rows=1000000 width=4)
    (3 rows)
    

    当然这并不是说in的写法就一定比exists要好,只是相较于exists更不容易产生filter。这是为什么呢?因为如果子查询中包含我们上面提到的固化关键字时,子查询会被固化为一个整体,当采用exists写法时,如果子查询中有主表的连接列,那么便只能是主表通过连接列给子查询中的表传值,因此会选择filter。而使用in的写法,即使子查询被固化,但如果没有主表连接列的字段,那么便不会选择filter。

    2、视图合并

    不知道大家有没有遇到过类似下面的情况:

    select xxx from () t1, () t2 where t1.id = t2.id;
    

    明明t1和t2两个子查询单独执行都很快,但是放到一起速度却变得特别慢,这种情况往往就是视图合并所导致的。

    例如下面的SQL:
    我们按照SQL中的顺序来看应该是emp和dept两表先进行关联,然后再去和salgrade表关联。但执行计划中的顺序却变成了emp和salgrade表先关联,最后才去关联dept表。
    这说明发生了视图合并,即视图/子查询中的内容被拆开了。

    bill=# explain select a.*,c.grade
    bill-#     from (select ename,sal,a.deptno,b.dname
    bill(#         from emp a,dept b
    bill(#         where a.deptno = b.deptno) a,
    bill-#         salgrade c
    bill-#     where a.sal between c.losal and c.hisal;
                                     QUERY PLAN
    -----------------------------------------------------------------------------
     Hash Join  (cost=1.09..4.56 rows=8 width=27)
       Hash Cond: (a.deptno = b.deptno)
       ->  Nested Loop  (cost=0.00..3.43 rows=8 width=18)
             Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
             ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
             ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
                   ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
       ->  Hash  (cost=1.04..1.04 rows=4 width=13)
             ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
    (9 rows)
    

    从上面的例子可以看出,视图合并一般产生性能问题都是因为发生视图合并后表的连接顺序变化导致的。不过一般优化器这么做是为了帮我们选择更合适的表连接顺序,而当优化器选择了错误的连接顺序时,我们就有必要对SQL进行改写了。

    由于PostgreSQL中我们无法使用hint来让优化器禁止视图合并,所以我们便需要了解一些SQL改写的技巧。
    和前面的filter一样,当我们将视图/子查询固化后,那么便不能进行视图合并。因此上面的SQL我们可以改写为:
    加上group by后,子查询被固化,视图没有发生合并,emp和dept表先进行关联了。

    bill=# explain select a.*,c.grade
    bill-#     from (select ename,sal,a.deptno,b.dname
    bill(#         from emp a,dept b
    bill(#         where a.deptno = b.deptno group by ename,sal,a.deptno,b.dname) a,
    bill-#         salgrade c
    bill-#     where a.sal between c.losal and c.hisal;
                                      QUERY PLAN
    -------------------------------------------------------------------------------
     Nested Loop  (cost=2.45..5.02 rows=8 width=27)
       Join Filter: ((a.sal >= c.losal) AND (a.sal <= c.hisal))
       ->  HashAggregate  (cost=2.45..2.59 rows=14 width=23)
             Group Key: a.ename, a.sal, a.deptno, b.dname
             ->  Hash Join  (cost=1.09..2.31 rows=14 width=23)
                   Hash Cond: (a.deptno = b.deptno)
                   ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
                   ->  Hash  (cost=1.04..1.04 rows=4 width=13)
                         ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
       ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
             ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
    (11 rows)
    

    3、谓词推入

    说完视图合并,我们再来看看你视图不能合并时会出现的一种情况——谓词推入。即对于那些不能合并的视图,并且有谓词进行过滤,CBO会将谓词过滤条件推入到视图内,为了尽早的过滤掉无用的数据,从而提升性能。

    从CBO的角度来看,进行谓词推入自然是好的,因为可以提前过滤掉不需要的数据。但是如果推入的谓词是连接列的,那么可能导致表的join产生变化,SQL性能变得更差。

    如下SQL所示:
    外层的谓词d.deptno between c.losal and c.hisal推入到了视图里面。

    bill=# create or replace view v1 as select ename,sal,a.deptno,b.dname
    bill-#         from emp a,dept b
    bill-#         where a.deptno = b.deptno;
    CREATE VIEW
    
    bill=# explain select d.*,c.grade from v1 d,salgrade c
    bill-#     where d.deptno between c.losal and c.hisal;
                                     QUERY PLAN
    -----------------------------------------------------------------------------
     Hash Join  (cost=1.09..4.56 rows=8 width=27)
       Hash Cond: (a.deptno = b.deptno)
       ->  Nested Loop  (cost=0.00..3.43 rows=8 width=18)
             Join Filter: ((a.deptno >= c.losal) AND (a.deptno <= c.hisal))
             ->  Seq Scan on emp a  (cost=0.00..1.14 rows=14 width=14)
             ->  Materialize  (cost=0.00..1.07 rows=5 width=12)
                   ->  Seq Scan on salgrade c  (cost=0.00..1.05 rows=5 width=12)
       ->  Hash  (cost=1.04..1.04 rows=4 width=13)
             ->  Seq Scan on dept b  (cost=0.00..1.04 rows=4 width=13)
    (9 rows)
    

    那我们该如何防止谓词内推呢?在Oracle中可以通过关闭连接列的谓词推入的隐藏参数_push_join_predicate来实现,那么在PostgreSQL中又该如何实现呢?

    和上面类似,我们可以将视图固化来避免这种情况,但一般来说不建议将视图固化,因为大部分情况谓词推入大多数对性能是有好处的。例如当我们在视图中使用limit时会导致谓词无法推入,因此一般也不建议在视图中使用limit,为什么呢?因为如果谓词进行推入的话,limit取到的值可能就不同了,会对结果集产生影响,所以自然不能推入了,因为优化器的任何等价转换都是在不改变SQL结果的情况下才能进行的。

    展开全文
  • sql优化常用的几种方法,19种最有效的sql优化技巧 本文我们来谈谈项目中常用的MySQL优化方法,共19条,具体如下: 1、EXPLAIN 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1...
  • 主要介绍了SQLSERVER SQL性能优化技巧,需要的朋友可以参考下
  • ORACLE SQL性能优化技巧

    2018-06-16 11:35:43
    书写高质量的oracle sql,用表连接替换EXISTS,索引的技巧等等
  • 常用的8个SQl优化技巧

    2019-04-29 18:16:54
    下面列出一些常用的SQl优化技巧,感兴趣的朋友可以了解一下。 1、注意通配符中Like的使用 以下写法会造成全表的扫描,例如: select id,name from userinfo where name like ‘%name%’ 或者 select id,name ...
  • 数据库查询优化复盘-20条必备sql优化技巧

    万次阅读 多人点赞 2020-12-08 08:29:00
    长按识别下方二维码,即可"关注"公众号每天早晨,干货准时奉上!0、序言本文我们来谈谈项目中常用的 20 条 MySQL 优化方法,效率至少提高 3倍!具体如下:1、使⽤ ...
  • Hivesql常用优化技巧

    千次阅读 2021-11-04 21:01:34
    首先介绍下什么是HIve? 1.基于Hadoop的开源的数据仓库工具,...4.HiveSql和Mysql一样,都遵循着SQL的标准,因此它们很多语句都是一样的。 一、先复习下SQL的语句的结构 SELECT * (必须) FROM 表(数据源) WHERE
  • SQL性能优化技巧分享
  • 做MySQL优化,我们要善用EXPLAIN查看SQL执行计划。 下面来个简单的示例,标注(1、2、3、4、5)我们要重点关注的数据: type列,连接类型。一个好的SQL语句至少要达到range级别。杜绝出现all级别。 key列,使用到...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 59,921
精华内容 23,968
关键字:

sql优化技巧

友情链接: KAPIT_04.ZIP