优化mysql检索left join + 条件检索

优化mysql检索left join + 条件检索

优化前：

	select tar.id, tar.access_time accessTime, tar.open_door_type openDoorType, tar.is_passed isPassed,
    	tar.device_serial_number deviceSerialNumber, tar.device_address deviceAddress, tar.in_out_flag inOutFlag,
    	tar.department_name departmentName,tar.ctime ctime,
    	tp.id personId, tp.code personCode, tp.name personName, tp.id_card_number idCardNumber, tp.extend_content extendContent, tp.gender, tp.ic_card_number icCardNumber
    from t_access_record tar
    left join t_person tp on tar.person_id= tp.id
    order by tar.access_time desc
	limit 140000, 20

耗时：700ms

优化后：

	select tar.id, tar.access_time accessTime, tar.open_door_type openDoorType, tar.is_passed isPassed,
    	tar.device_serial_number deviceSerialNumber, tar.device_address deviceAddress, tar.in_out_flag inOutFlag,
    	tar.department_name departmentName,tar.ctime ctime,
    	tp.id personId, tp.code personCode, tp.name personName, tp.id_card_number idCardNumber, tp.extend_content extendContent, tp.gender, tp.ic_card_number icCardNumber
    from t_access_record tar
    left join t_person tp on tar.person_id= tp.id
    INNER JOIN (SELECT ta.id FROM t_access_record ta
			LEFT JOIN t_person tp on ta.person_id = tp.id
    		order by ta.access_time desc limit 140000, 20) tarid
    ON tarid.id = tar.id

耗时：439ms

主要是减小回表的数据，先通过条件检索出记录ID，再获取具体的数据。

进一步优化

在mybatis中利用条件判断，当检索人员条件为空的时候，不外联人员表，如下

select tar.id, tar.access_time accessTime, tar.open_door_type openDoorType, tar.is_passed isPassed,
    tar.device_serial_number deviceSerialNumber, tar.device_address deviceAddress, tar.in_out_flag inOutFlag,
    tar.department_name departmentName,tar.ctime ctime,
    tp.id personId, tp.code personCode, tp.name personName, tp.id_card_number idCardNumber, tp.extend_content extendContent, tp.gender, tp.ic_card_number icCardNumber
    from t_access_record tar
    left join t_person tp on tar.person_id= tp.id
    INNER JOIN (SELECT ta.id FROM t_access_record ta
    		<if test="personName != null">
    			LEFT JOIN t_person tp on ta.person_id = tp.id
    		</if>
    		where 1=1
    		<if test="personName != null">
    			and tp.name = #{personName}
    		</if>
    order by ta.access_time desc limit 140000, 20) tarid
    ON tarid.id = tar.id

当personName = null的时候，检索条件中不外联person表，耗时：44ms！

备注

记得在把左联字段添加为索引，否则光连接就很慢了。
以上三种方法只有在limit n 比较大的时候效果才比较明显，如果 limit 1，20的话三种方式都差不多。