原SQLselect rcv_idmm_id rcvIdmmId ,work_item_id workItemId,crm_order_id crmOrderId,work_order_id workOrderId, service_no serviceNo,id_no idNo,group_id groupId,order_type orderType,op_time opTime, prpty_name_value prptyNameValue,step_id ,stepId,,ret_info retInfo,remarks from ( select rcv_idmm_id ,work_item_id,crm_order_id,work_order_id,service_no,id_no,group_id,order_type,op_time,prpty_name_value,step_id,ret_idmm_id,ret_info,remarks from cms_worktask_his_202111 union all select rcv_idmm_id ,work_item_id,crm_order_id,work_order_id,service_no,id_no,group_id,order_type,op_time,prpty_name_value,step_id,ret_idmm_id,ret_info,remarks from cms_worktask_his_202112 ) u WHERE u.crm_order_id = '17211942';
执行完以后需要7秒钟,数据在cms_worktask_his_202111表中,单独查询只需要0.3秒,
第一种:不推荐,将多余的列名去掉,只查询需要的列名,我只查询一个列的数据,降到2秒多,
select crm_order_id from ( select crm_order_id from cms_worktask_his_202111 union all select crm_order_id from cms_worktask_his_202112 ) u WHERE u.crm_order_id = '17211942';
第二种:推荐,将where条件放在每一个子查询中,和单表查询差不多,只需要0.4秒,如下:
select rcv_idmm_id rcvIdmmId ,work_item_id workItemId,crm_order_id crmOrderId,work_order_id workOrderId, service_no serviceNo,id_no idNo,group_id groupId,order_type orderType,op_time opTime, prpty_name_value prptyNameValue,step_id stepId,ret_info retInfo,remarks from ( select rcv_idmm_id ,work_item_id,crm_order_id,work_order_id,service_no,id_no,group_id,order_type,op_time,prpty_name_value,step_id,ret_idmm_id,ret_info,remarks from cms_worktask_his_202111 WHERE crm_order_id = '1721315942' union all select rcv_idmm_id ,work_item_id,crm_order_id,work_order_id,service_no,id_no,group_id,order_type,op_time,prpty_name_value,step_id,ret_idmm_id,ret_info,remarks from cms_worktask_his_202112 WHERE crm_order_id = '17211942' ) u