原SQL:
SELECT
p.id,
p.NAME,
p.idcard,
p.phone,
p.plate,
p.FAMILY_NO
FROM
t_person_info p
WHERE
p.id IN ( SELECT id FROM t_person_info WHERE FAMILY_NO = ( SELECT FAMILY_NO FROM t_person_info WHERE id = '1561155127086411776' ) )
OR p.id = '1561155127086411776'
执行计划:
耗时:
2.4秒
优化思路:
将人员的直接查出来,只有1条数据作为驱动表,再用户口号去关联原表。
优化后的SQL:
SELECT
p.id,
p.NAME,
p.idcard,
p.phone,
p.plate,
p.FAMILY_NO
FROM
t_person_info p
right JOIN (select * from t_person_info where id = '1561155127086411776') p2 on p.FAMILY_NO = p2.FAMILY_NO
执行计划
耗时:
0.03秒
成功避免了两个子查询以及79万表的全表扫描。
结论:
永远不要轻易使用【in子查询】,由于in子查询总是以外层查询的table作为驱动表,所以如果想用in子查询的话,一定要将外层查询的结果集降下来,降低io次数,降低nested loop循环次数,即:永远用小结果集驱动大的结果集。
之前的SQL就是以大表t_person_info作为驱动表进行nested loop,该表数据越大,效率越慢。
如sql
SELECT
p.id,
p.NAME,
p.idcard,
p.phone,
p.plate,
p.FAMILY_NO
FROM
t_person_info p
WHERE
p.id IN ( SELECT id FROM t_person_info WHERE FAMILY_NO = '11697717')
and p.id = '1561155127086411776'
mysql优化器自动谓词下推,等价于:
SELECT
p.id,
p.NAME,
p.idcard,
p.phone,
p.plate,
p.FAMILY_NO
FROM
(select * from t_person_info where id = '1561155127086411776') p
WHERE
p.id IN ( SELECT id FROM t_person_info WHERE FAMILY_NO = '11697717')
这样驱动表结果集降到最低,效率也就提高了。这里只是举例,这条SQL并不符合业务场景。