sql优化问题，left jion 多表，查询速度慢

一步天涯 2015-07-02 11:27:44

是mysql数据库

SELECT ao.VISIT_TIME,ao.BRANCH_ID,

				ao.ORDER_ID,

				(case ao.FROM_SYS WHEN '1' then '呼叫中心' when '2' then '移动终端' when '3' then '网站' end) as FROM_SYS,

				ao.ORDER_CODE,

				dij.NAME,

				did.tyleclasssname TYPE_CLASS,

				dic.statusname STATUS,

				diw.COMPANY_NAME,

				diw.MEMBER_NO,diz.huiyuantype MEMBER_TYPE,

				diw.CUST_NAME,diw.INBOUND_PHONE,

				diw.RESERVE_PHONE,

				diw.PHONE_THREE,

				diw.PHONE_FOUR,

				diw.PHONE_FIVE,

				dia.fuwuquyu AODR_CODE,

				diw.ADDR_INFO,

				ao.BRANCH_NAME,

				date_format(ao.CREATE_TIME,'%Y-%m-%d') CREATE_TIME,

				dif.pingpaibigname BIG_CATEGORY_CODE,

				die.pingpaismallname SMALL_CATEGORY_CODE,

				dig.pingpainame BRAND_CODE,

				ao.SPECIFICATIONS_CODE,

				ao.SYMPTOM,tom.MEMO,

				dik.repeirName REPAIR_NAME,

				date_format(ao.SERVICE_TIME,'%Y-%m-%d') SERVICE_TIME,

				ao.GUARANTEE_PERIOD,

				cast(ao.DOOR_FEE as char) DOOR_FEE,cast(ao.CHECK_FEE as char) CHECK_FEE,cast(ao.REPAIR_FEE as char) REPAIR_FEE,cast(ao.PARTS_FEE as char) PARTS_FEE,cast(ao.OTHER_FEE as char) OTHER_FEE,cast(ao.FINISH_FEE as char) FINISH_FEE

					FROM t_appliances_order ao

					LEFT JOIN (select p.item_value item_value1,p.item_name statusname from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'orderStatus') t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dic on dic.item_value1 = ao.`STATUS`

					LEFT JOIN (select p.item_value item_value2,p.item_name tyleclasssname from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'statementMode') t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) did on did.item_value2 = ao.TYPE_CLASS

					LEFT JOIN (select p.dic_item_id dic_item_id1,p.item_name pingpaibigname from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'domesticAppliance')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dif on dif.dic_item_id1 = ao.BIG_CATEGORY_CODE

					LEFT JOIN (select p.dic_item_id dic_item_id2,p.item_name pingpaismallname from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'domesticAppliance')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) die on die.dic_item_id2 = ao.SMALL_CATEGORY_CODE

					LEFT JOIN (select p.item_value item_value3,p.item_name pingpainame from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'brand')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dig on dig.item_value3 = ao.BRAND_CODE

					LEFT JOIN (select p.item_value item_value4,p.item_name typename from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'orderType')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dih on dih.item_value4 = ao.TYPE

					LEFT JOIN (select p.item_value item_value5,p.item_name dealreasonname from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'complaintReason')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dii on dii.item_value5 = ao.DEAL_REASON

					LEFT JOIN (SELECT p.*,ua.user_account_id USER_ACCOUNT_ID,ua.name NAME FROM t_person_info p LEFT JOIN (SELECT person_id,name,user_account_id,status FROM plat_user_account) ua ON ua.PERSON_ID = p.PERSON_ID WHERE p.PERSON_TYPE =  '1' AND ua.STATUS = '1') dij ON dij.USER_ACCOUNT_ID =ao.create_uid

					LEFT JOIN (SELECT pi.REAL_NAME repeirName,pi.PERSON_ID person_id1 FROM t_person_info pi) dik ON dik.person_id1=ao.REPAIR_UID

					LEFT JOIN (SELECT icr.* FROM t_input_cust_record icr) diw ON ((diw.ORDER_ID=ao.ORDER_ID and ao.type=1) or (diw.ORDER_ID=ao.BIND_ORDER_ID and ao.type = '2'))

					LEFT JOIN (select p.item_value item_value6,p.item_name huiyuantype from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'membershipCard')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) diz on diz.item_value6 = diw.MEMBER_TYPE_CODE

					LEFT JOIN (select p.item_value item_value7,p.item_name fuwuquyu from plat_dict_item p LEFT JOIN (SELECT td.dic_id from plat_dict td where td.dic_code = 'serviceArea')t ON t.dic_id=p.dic_id where  p.dic_id = t.dic_id) dia on dia.item_value7 = diw.addr_code

					LEFT JOIN (

					SELECT m.order_id,GROUP_CONCAT(CONCAT(m.MEMO,DATE_FORMAT(m.CREATE_TIME,'%Y-%m-%d %H:%i:%s'),(CASE u.account_type WHEN 3 THEN b.ANOTHER_NAME ELSE IFNULL(u.name,'') END))) MEMO from t_order_memo m 

					LEFT JOIN plat_user_account u on m.CREATE_UID = u.user_account_id 

					LEFT JOIN t_branch b on u.PERSON_ID = b.BRANCH_ID

					GROUP BY m.order_id) tom on tom.ORDER_ID = ao.ORDER_ID

					where ao.CREATE_TIME >= '${startDate}'

					AND ao.CREATE_TIME 《= '${endDate}'

					ORDER BY ao.CREATE_TIME	DESC