最近优化了两个sql查询耗时导致页面查询time out的问题，简单记录下

1.索引不当导致查询超时

前端查询页面优化，日期查询框默认选中三个月的时间范围(之前页面时间查询框没有默认值，之所以让页面这样处理是为了让SQL走优化后的索引)，发布之后，查询仍然报超时错误，

查看APM接口调用链：

复制上图中的SQL，explain执行计划，发现没有走指定的索引(idx_create_dt_link_status on xxx_audit_order(`create_dt`, `inspection_link`, `inspection_audit_status`);)，控制台执行sql耗时依然是6秒左右。本地数据库中插入生产上表的同量级数据(400万，大批量插入表数据详见另一篇文章)

索引结构和生产一致，执行查询，耗时基本一致（问题其实已经很明确：执行器没有按照我们创建的索引去执行，因为生产中我们禁用force index(index_name)这种命令，所以本地验证安全之后，才会申请发布生产）。删掉冗余不用的索引：
drop index idx_link_status on xxx_audit_order;
再次查看sql的explain，执行器已经选择我们设定的索引执行，控制台执行sql，耗时1秒，页面查询已经正常

2.大数据量下join关联3张表查询，导致超时

线上有这样一个SQL(mybatis动态sql在满足业务给定的条件拼接之后的形式)，如下：

select distinct(o.order_no), o.* from xxx_sale_order o
   left join xxx_order_product p on o.order_no = p.order_no
            left join xxx_product_item item on o.order_no = item.after_sale_order_no    
where o.execute_channel = 1 
and (p.product_no = 'foo' or item.product_no = 'bar' )
order by o.id desc;
1
2
3
4
5
6

线上数据有300万，可以想象这条sql执行的会有多慢，控制台执行耗时7秒多，导致页面根本无法使用。大家都知道，join在表数据量大的时候查询效率会很差，那么怎么优化呢？一般来说，对join链接查询我们都会在业务层代码中进行转换，比如把需要关联的某张表先查询出该表的结果集，将join该表转换为where条件中的in(...)。这里业务层先查询表xxx_order_product 和 xxx_product_item ，比如：

public List<String> getXXXListByProductNo(final String productNo) {
        if (StringUtils.isBlank(productNo)) {
            return Collections.emptyList();
        }

        List<String> asoNoList = mapper.selectByProductNo(productNo);
        List<String> xxxOrderNos = productService.getAsoNoListByProductNo(productNo);

        if (CollectionUtils.isNotEmpty(xxxOrderNos)) {
            asoNoList.addAll(xxxOrderNos);
        }

        if (CollectionUtils.isEmpty(asoNoList)) {
            return Collections.emptyList();
        }

        return asoNoList.stream().distinct().limit(100).collect(Collectors.toList());
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

这样一来，原来三表关联，最后就转换为单表的where条件查询，sql如下：

select o.* from xxx_order o
  where o.execute_channel = 1 
        and o.order_no in('foo','bar'...)
order by o.id desc;

1
2
3
4
5

查询时间大幅提升，问题解决
PS:
join关联查询时，关联键一定要用驱动表进行全链关联，否则即使各表的关联字段都有索引，也不会走索引，原因很简单，因为此时会产生临时表，结果就是filesort，比如下面的sql，查询效率很差：

select  o.* from xxx_sale_order o
   left join xxx_order_product p on o.order_no = p.order_no
            left join xxx_product_item item on p.id = item.product_ref_id   
where o.execute_channel = 1  and ... 
order by o.id desc;```

1
2
3
4
5
6