1.超过三个表禁止 join。【阿里巴巴JAVA开发手册】
2.需要 join 的字段,数据类型必须绝对一致;【阿里巴巴JAVA开发手册】
3.多表关联查询时,保证被关联的字段需要有索引,尽量选择NLJ算法。【阿里巴巴JAVA开发手册】
4.小表驱动大表,写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式,省去mysql优化器自己判断的时间
5.Join小结
5.1. 整体效率比较:INLJ > BNLJ > SNLJ
5.2. 永远用小结果集驱动大结果集(其本质就是减少外层循环的数据数量)(小的度量单位指的是:表行数*每行大小)。
# 推荐 t1只查询了一个字段,所以选择t1作为驱动表。straight_join:将straight_join 之前的表作为驱动表
select t1.b,t2.* from t1 straight_join t2 on(t1.b=t2.b)
where t2.id<=100;
#不推荐
select t1.b,t2.* from t2 straight_join t1 on(t1.b=t2.b)
where t2.id<=100;
5.3. 为被驱动表匹配的条件增加索引(减少内层表的循环匹配次数)
5.4. 增大join_buffer_size的大小(一次缓存的数据越多,那么内存表的扫描次数就越少)。
5.5. 减少驱动表不必要的字段查询(字段越少,join buffer所缓存的数据就越多)。
-------------------------------------------------------------------------------------------------------------------------
问题描述:现场的配变台账表查询超时,公司正常,表结构一致,数据量一致。通过code字段关联了cim_disttransformer和cim_disttransformermodel 。且查询都走了索引。
原因分析:cim_disttransformermodel 的 CODE 是 utf8_bin,而 cim_disttransformer 创建时并未指定字符编码。走的数据库默认编码。
现场的数据库编码默认是utf8mb4, default charset utf8mb4
公司的数据库编码默认是utf8。 default charset utf8 COLLATE utf8_general_ci
已知,utf8编码与utf8_bin编码字段关联时不需要强转,效率较快。而utf8mb4编码与utf8_bin字段进行关联的时候,需要强转效率很低。就会造成现场执行sql需要两分钟,公司需要1s的情况。
问题解决:修改现场数据库的默认编码为utf8,此操作只对后续新建的表生效,需要对已经创建的表进行排查,修改表的字符编码。