在实际项目开发中,sql查询中的连表查询和子查询用的是最多的,但是很多人对于in,exists,join的用法认识不足,随意运用,这种情况如果在大数据量查询时,会存在很大的隐患。
一.子查询(写法可以互换,但是得依据具体情况做相应选择)
MySQL中的in语句是把外表和内表作hash 连接,而exists语句是对外表作loop循环,每次loop循环再对内表进行查询。我们可能认为exists比in语句的效率要高,这种说法其实是不准确的,要区分一下几种情景:
- 如果查询的两个表大小相当,那么用in和exists差别不大。
- 如果两个表中一个较小,一个是大表,则子查询表大的用exists,子查询表小的用in。
- not in 和not exists:如果查询语句使用了not in,那么内外表都进行全表扫描,没有用到索引;而not extsts的子查询依然能用到表上的索引。所以无论那个表大,用not exists都比not in要快。
具体写法如下,两个关键字的写法很类似:
- 关键字in的写法
SELECT pm.status,count(pm.id) idNum,DATE_FORMAT(pm.start_time,'%Y-%m-%d') strFormat
FROM pl_manage pm
where pm.id in (
select bp.pm_id
from bl_programme bp
where bp.del_flag = '0'
)
AND pm.del_flag = '0'
GROUP BY pm.status
- 关键字exists的写法
SELECT pm.status,count(pm.id) idNum,DATE_FORMAT(pm.start_time,'%Y-%m-%d') strFormat
FROM pl_manage pm
where exists (
select plan_manage_id
from bl_programme bp
where bp.del_flag = '0'
and bp.pm_id = pm.id
)
AND pm.del_flag = '0'
GROUP BY pm.status
二.连表查询
- inner join 内连接,在两张表进行连接查询时,只保留两张表中完全匹配的结果集
- left join 在两张表进行连接查询时,会返回左表所有的行,即使在右表中没有匹配的记录。
- right join 在两张表进行连接查询时,会返回右表所有的行,即使在左表中没有匹配的记录。
具体写法如下:
SELECT pm.status,count(pm.id) idNum,DATE_FORMAT(pm.start_time,'%Y-%m-%d') strFormat
FROM pl_manage pm
inner join bl_programme bp
on bp.pm_id = pm.id and bp.del_flag = '0'
where pm.del_flag = '0'
GROUP BY pm.status
需要注意的是:
连接查询和子查询的用法是不能随意更换的,容易造成数据查询异常。