1. 业务场景:A,B两表之间通过一个字段关联,比如B表依赖于A表的主键id字段,此时需要以A表为主表进行查询,同时关联查询出B表中的字段数据。SQL语句很简单,如下所示
SELECT A.id, A.nickname, A.avatar, A.tag, B.start_date, B.end_date, B.monitor_status, B.operate_user_id, B.operate_user_name, B.data_update_time FROM A left join B on A.id = B.user_id limit 10
问题发生的时候,A表只有15万数据,B表只有5000数据,但是执行这条语句消耗的时间基本都在90秒以上,导致业务接口访问超时,完全不可用。
先简单解释一下左(右)连接关联查询的执行逻辑:可以简单理解为,先遍历主表A表的每一行数据,遍历每一行数据的同时,取出这一行中的关联字段,再去B表中执行一遍查询。左连接在逻辑上是有一个主从表之分的,左边的是主表,右边的是从表,右连接查询相反。
知道了左连接查询的执行逻辑后,就大概知道了问题原因了,首先explain一下SQL语句,果然,B表没有走任何索引查询,检查发现B表在user_id关联字段上没有添加索引,导致关联查询B表走的全表查询。
而这导致的问题就是:A表每查询一行数据,就会在B表里全表查询一次,也就是相当于遍历了每一行B表数据,那么最后的执行的查询次数相当于A表数据行数*B表数据行数(可以理解为两层for循环遍历查询),就当前的表数据来看,需要大概7.5亿次(150000*5000)查询操作,MySQL肯定顶不住的。
2. 解决办法
解决方法很简单,就是添加索引即可,注意,这个索引必须添加在从表的关联字段上,添加索引之后,在B表中的查询就会采用索引查询,查询速度直接提升到0.0029s,从90秒到0.0029秒,这效果贼强,通过explain分析SQL语句,从原来的B表全表扫描变为每次只需要查询1行数据(explain分析估算的,另外B表数据量较少,如果B表数据量较大,可能就不一定是1了),所以即使是关联查询,速度实际上与单表查询无异。
建议:不要随便用关联查询,或者在关联查询之前,最好是能有一些过滤条件(where条件或通过子查询)先限制一下数据范围,然后在关联查询,一定一定要在关联字段上添加索引。绝对不要在两个表同时走全表查询,MySQL绝对会查询爆炸。