在阿里巴巴开发手册中关于索引规约中，第二部分如下写到：超过三个表禁止join？那么条件有限的查询如何查询？

图片

结合实际场景及线上响应我们进行一次分析，首先如果三表关联，每个表的索引，关联条件都能100%命中索引，没有索引失效场景，我相信是不会出现大问题的，但既然三表关联，要么是设计数据库场景考虑缺失，可设计冗余字段进行关联，要么是业务场景条件有限，无论是哪种情况，对已经成熟的表进行重新设计其成本都是很大的，如何在已有的条件下进行SQL优化呢？

对于新需求的设计场景是根据已有条件查询订单中所有的品去调用rpc,一期设计是不需要细化到品级别的，只需要客户级别，所以一期的代码结构如下

if(CollectionUtils.isEmpty(request.getCustomerCodeList()) || StringUtils.isEmpty(request.getNodeCode())){
return new ArrayList<>();
}
CommonResponse<Object> response = rpc(request.getCustomerCodeList(), request.getNodeCode());
List<ComplaintInitiativeConfigRes> data = response.getData();

    List<Object> responseList = new ArrayList<>();
    if(CollectionUtils.isNotEmpty(data)){
        responseList = data.stream().map(r -> {
            Object response = new Object();
            BeanUtils.copyProperties(r, response);
            return response;
        }).collect(Collectors.toList());
    }
    return responseList;

随着需求的改变，细化到品级别后，发现现有的条件并不能直接查询其结果集，但关联表是可以的，所以SQL设计如下

select
DISTINCT c.x
from
A a
left join B b on a.x=b.x and …
left join C c on b.x=c.x and …
where
…
注意：A.B.C三张表均是大表

上线后初期是稳定的，但上线4天后接连有响应超时，且占用CPU超高情况，进而导致网关响应超时，线上一位用户反馈系统错误。

图片

当天响应超时报警67次，负载过高报警超过20次，当即决定要在业务高峰期前进行改造。在《高性能MySql》中重构查询的方式章节如下,对查询的优化除索引外，不近要考虑结果集还要考虑查询的复杂性，对于多次单表明确命中索引是由于多表join的。

图片

改动如下：

f(CollectionUtils.isEmpty(request.getCustomerCodeList()) || StringUtils.isEmpty(request.getNodeCode())){
return new ArrayList<>();
}
select * from A where 此语句命中索引
根据此语句返回结果业务判断后
进行
select * from B where 此语句命中索引

    select * from  C where 此语句命中索引
    CommonResponse<Object> response = rpc(request.getCustomerCodeList(), request.getNodeCode());
    List<ComplaintInitiativeConfigRes> data = response.getData();

    List<Object> responseList = new ArrayList<>();
    if(CollectionUtils.isNotEmpty(data)){
        responseList = data.stream().map(r -> {
            Object response = new Object();
            BeanUtils.copyProperties(r, response);
            return response;
        }).collect(Collectors.toList());
    }
    return responseList;

将循环内表查询提到外层，将大表join改为多次单表命中索引查询，对rpc接口调用查询条件过滤，对日期范围精进，对scope进行缩小。上线后在高峰期稳定运行。

禁止SQL查询中超过三张表Join

在大型系统中,为了保证SQL查询高效,通常需要对SQL语句进行限制。其中一项常见要求就是禁止 Join 过多表。
excessively joining tables in a SQL query往往会导致性能问题。
因此,一个比较常见的做法是:禁止SQL语句 Join 超过3个表。

为什么需要限制Join表数?

• Join操作效率低。多个表Join成查询,数据库需要对多表数据进行Cartesian product(笛卡尔积),成本很高。
• 过多Join可能会导致临时表临时文件过大,影响IO操作。
• 多表Join增加了SQL语句的复杂性,难以维护。
  总的来说,避免 excessively joining tables 能够:
• 提高SQL执行效率
• 降低IO pressure
• 提升系统整体性能
• 防止不必要的复杂SQL查询

实现Join表数限制

要限制SQL Join的表数,可以采用以下两种方式:

使用数据库自身的限制

一些数据库(如SQL Server)自身提供了限制JOIN的功能。
比如在SQL Server中,可以使用 MAX_JOIN_CARDINALITY を设置JOIN语句的最大表数。
sql
– 限制最大Join表数为3
SET MAX_JOIN_CARDINALITY 3
然后在查询中就会检查JOIN的表数是否超过3。

在应用层实现限制

也可以在应用代码层面对SQL语句进行检查,禁止超过规定的Join数量。
比如:

• 在写SQL的地方增加限制。
• 在提交SQL到数据库前,进行语法解析,检查Join表数。
• 提交SQL前,使用正则匹配join关键词的次数。
  应用层实现相对灵活,但需要额外开发。

综合来说

为了保证 SQL 查询高效,我们需要在一定程度上限制SQL过多Join表的情况。
具体可采用以下两种方法:
-利用数据库自身提供的限制Join功能(如 SQL Server)。

• 在应用层对SQL语句进行Join表数检查,在提交数据库前抛出异常。
  两种方式均可达到限制Join表数的效果。
  另外,在系统设计之初,我们也需要避免 Table / Class 设计过于臃肿,导致Join表数过多。
  只有规范的数据库设计和合理的SQL限制,才能保证系统长期健壮。