【工作随手记】mysql优化之1

原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并不符合业务场景。