Mysql语句优化思路之一
Mysql语句优化思路之一
MarcoZhang 发表于4个月前
Mysql语句优化思路之一
  • 发表于 4个月前
  • 阅读 0
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

当主表数据多,且需要关联多个表时,先对主表数据进行筛选,再关联其他表。

如:

SELECT 
		t1.parkName,
		t1.carNoIn,
		t1.carNo,
		t1.pass,
		CASE WHEN t1.inTime='1970-01-01 08:00:00' THEN '' ELSE t1.inTime END AS inTime,
		t1.channelInName,
		t1.userInName,
		t1.reginName,
		t1.outTime,
		t1.channelOutName,
		t1.userOutName,
		CASE WHEN t1.inTime='1970-01-01 08:00:00' THEN '' ELSE IFNULL(CONCAT(t1.lTime,' 分钟'),'') END AS lTime,
		t1.carTypeName,
		t1.price,
		t1.pay,
		t1.weChat,
		t1.aliPay,
		t1.exception,
		t1.carInfoId,
		t1.carTypeId,
		t1.inImage,
		t1.outImage
	from (
		SELECT
			IFNULL(p.`name`, '') AS parkName,
			IFNULL(po.car_no_in, '') AS carNoIn,
			IFNULL(po.car_no, '') AS carNo,
			IF(unix_timestamp(now())>ci.etime,'(过)','') as pass,
			IFNULL(FROM_UNIXTIME(LEFT (po.in_time, 10),'%Y-%m-%d %H:%i:%S'),'') AS inTime,
			IFNULL(cin.`name`, '') AS channelInName,
			IFNULL(uin.`name`,'') AS userInName,
			IFNULL(r.`name`, '') AS reginName,
			IFNULL(FROM_UNIXTIME(LEFT (po.out_time, 10),'%Y-%m-%d %H:%i:%S'),'') AS outTime,
			IFNULL(cout.`name`, '') AS channelOutName,
			IFNULL(uout.`name`,'') AS userOutName,
			TIMESTAMPDIFF(MINUTE,FROM_UNIXTIME(LEFT (po.in_time, 10),'%Y-%m-%d %H:%i:%S'),FROM_UNIXTIME(LEFT (po.out_time, 10),'%Y-%m-%d %H:%i:%S')) AS lTime,
			IFNULL(ct.`name`,'临时车') AS carTypeName,
			IFNULL(CONCAT(FORMAT(po.price/100,2),' 元'),'') AS price,
			IFNULL(IF(po.pay != 0, CONCAT(FORMAT(po.pay/100,2),' 元'),'-'),'') AS pay,
			IFNULL(IF(po.pay_type = 2, CONCAT(FORMAT(po.app_pay/100,2),' 元'), '-'),'') AS weChat,
			IFNULL(IF(po.pay_type = 3, CONCAT(FORMAT(po.app_pay/100,2),' 元'), '-'),'') AS aliPay,
			IFNULL(po.exception,'') AS exception,
			IFNULL(ci.car_info_id,'') AS carInfoId,
			IFNULL(ct.car_type_id,'') AS carTypeId,
			IFNULL(po.inImage,'') AS inImage,
			IFNULL(po.outImage,'') AS outImage
		FROM
			`park_out` po
		LEFT JOIN park p ON p.park_id = po.park_id
		LEFT JOIN channel cin ON cin.channel_id = po.channel_id_in
		LEFT JOIN `user` uin ON uin.user_id = po.user_id_in
		LEFT JOIN channel_regin cr ON cr.channel_id = po.channel_id_in
		LEFT JOIN regin r ON r.regin_id = cr.regin_id
		LEFT JOIN channel cout ON cout.channel_id = po.channel_id_out
		LEFT JOIN `user` uout ON uout.user_id = po.user_id_out
		LEFT JOIN car_info ci ON ci.car_no = po.car_no
		LEFT JOIN car_type ct ON ct.car_type_id = ci.car_type_id
		WHERE 1=1
			<if test="parkId != null and parkId != ''" >
				AND po.park_id = #{parkId,jdbcType=INTEGER}
			</if>
			<if test="carNoIn != null and carNoIn != ''" >
				<![CDATA[AND po.car_no_in like concat('%',#{carNoIn,jdbcType=VARCHAR},'%')]]>
			</if>
			<if test="carNo != null and carNo != ''" >
				<![CDATA[AND po.car_no like concat('%',#{carNo,jdbcType=VARCHAR},'%')]]>
			</if>
			<if test="inTime1 != null and inTime1 != ''" >
				<![CDATA[AND po.in_time >= #{inTime1,jdbcType=BIGINT}]]>
			</if>
			<if test="inTime2 != null and inTime2 != ''" >
				<![CDATA[AND po.in_time <= #{inTime2,jdbcType=BIGINT}]]>
			</if>
			<if test="channelIdIn != null and channelIdIn != ''" >
				AND po.channel_id_in = #{channelIdIn,jdbcType=BIGINT}
			</if>
			<if test="userIdIn != null and userIdIn != ''" >
				AND po.user_id_in = #{userIdIn,jdbcType=BIGINT}
			</if>
			<if test="outTime1 != null and outTime1 != ''" >
				<![CDATA[AND po.out_time >= #{outTime1,jdbcType=BIGINT}]]>
			</if>
			<if test="outTime2 != null and outTime2 != ''" >
				<![CDATA[AND po.out_time <= #{outTime2,jdbcType=BIGINT}]]>
			</if>
			<if test="channelIdOut != null and channelIdOut != ''" >
				AND po.channel_id_out = #{channelIdOut,jdbcType=BIGINT}
			</if>
			<if test="userIdOut != null and userIdOut != ''" >
				AND po.user_id_out = #{userIdOut,jdbcType=BIGINT}
			</if>
			<if test="flag != null and flag != ''">
				AND po.flag = #{flag,jdbcType=INTEGER}
			</if>
		order by po.out_time desc
	) AS t1 
	WHERE 1=1
		<if test="lTime1 != null and lTime1 != ''" >
			<![CDATA[AND t1.lTime >= #{lTime1,jdbcType=BIGINT}]]>
		</if>
		<if test="lTime2 != null and lTime2 != ''" >
			<![CDATA[AND t1.lTime <= #{lTime2,jdbcType=BIGINT}]]>
		</if>
		<if test="startPos != null">
			limit #{startPos,jdbcType=INTEGER}, #{pageSize,jdbcType=INTEGER}
		</if>

以上是以前写的sb旧语句,可进一步改成以下语句,速度明显提升。

SELECT
		IFNULL(p.`name`, '') AS parkName,
		IFNULL(po.car_no_in, '') AS carNoIn,
		IFNULL(po.car_no, '') AS carNo,
		IF(unix_timestamp(now())>ci.etime,'(过)','') as pass,
		if(po.in_time =0,'',IFNULL(FROM_UNIXTIME(LEFT (po.in_time, 10),'%Y-%m-%d %H:%i:%S'),'')) AS inTime,
		IFNULL(cin.`name`, '') AS channelInName,
		IFNULL(uin.`name`,'') AS userInName,
		IFNULL(r.`name`, '') AS reginName,
		IFNULL(FROM_UNIXTIME(LEFT (po.out_time, 10),'%Y-%m-%d %H:%i:%S'),'') AS outTime,
		IFNULL(cout.`name`, '') AS channelOutName,
		IFNULL(uout.`name`,'') AS userOutName,
		if(po.lTime = 0, '', concat(po.lTime,' 分钟')) as lTime,
		IFNULL(ct.`name`,'临时车') AS carTypeName,
		IFNULL(CONCAT(FORMAT(po.price/100,2),' 元'),'') AS price,
		IFNULL(IF(po.pay != 0, CONCAT(FORMAT(po.pay/100,2),' 元'),'-'),'') AS pay,
		IFNULL(IF(po.pay_type = 2, CONCAT(FORMAT(po.app_pay/100,2),' 元'), '-'),'') AS weChat,
		IFNULL(IF(po.pay_type = 3, CONCAT(FORMAT(po.app_pay/100,2),' 元'), '-'),'') AS aliPay,
		IFNULL(po.exception,'') AS exception,
		IFNULL(ci.car_info_id,'') AS carInfoId,
		IFNULL(ct.car_type_id,'') AS carTypeId,
		IFNULL(po.inImage,'') AS inImage,
		IFNULL(po.outImage,'') AS outImage
	FROM(
		select 
			*
		from (
			SELECT 	
				*,
				if(po.in_time=0, 0, TIMESTAMPDIFF(MINUTE,FROM_UNIXTIME(LEFT (po.in_time, 10),'%Y-%m-%d %H:%i:%S'),FROM_UNIXTIME(LEFT (po.out_time, 10),'%Y-%m-%d %H:%i:%S'))) AS lTime
			FROM `park_out` as po) as po
		where 1 = 1
			<if test="parkId != null and parkId != ''" >
				AND po.park_id = #{parkId,jdbcType=INTEGER}
			</if>
			<if test="carNoIn != null and carNoIn != ''" >
				<![CDATA[AND po.car_no_in like concat('%',#{carNoIn,jdbcType=VARCHAR},'%')]]>
			</if>
			<if test="carNo != null and carNo != ''" >
				<![CDATA[AND po.car_no like concat('%',#{carNo,jdbcType=VARCHAR},'%')]]>
			</if>
			<if test="inTime1 != null and inTime1 != ''" >
				<![CDATA[AND po.in_time >= #{inTime1,jdbcType=BIGINT}]]>
			</if>
			<if test="inTime2 != null and inTime2 != ''" >
				<![CDATA[AND po.in_time <= #{inTime2,jdbcType=BIGINT}]]>
			</if>
			<if test="channelIdIn != null and channelIdIn != ''" >
				AND po.channel_id_in = #{channelIdIn,jdbcType=BIGINT}
			</if>
			<if test="userIdIn != null and userIdIn != ''" >
				AND po.user_id_in = #{userIdIn,jdbcType=BIGINT}
			</if>
			<if test="outTime1 != null and outTime1 != ''" >
				<![CDATA[AND po.out_time >= #{outTime1,jdbcType=BIGINT}]]>
			</if>
			<if test="outTime2 != null and outTime2 != ''" >
				<![CDATA[AND po.out_time <= #{outTime2,jdbcType=BIGINT}]]>
			</if>
			<if test="channelIdOut != null and channelIdOut != ''" >
				AND po.channel_id_out = #{channelIdOut,jdbcType=BIGINT}
			</if>
			<if test="userIdOut != null and userIdOut != ''" >
				AND po.user_id_out = #{userIdOut,jdbcType=BIGINT}
			</if>
			<if test="flag != null and flag != ''">
				AND po.flag = #{flag,jdbcType=INTEGER}
			</if>
			<if test="lTime1 != null and lTime1 != ''" >
				<![CDATA[AND po.lTime >= #{lTime1,jdbcType=BIGINT}]]>
			</if>
			<if test="lTime2 != null and lTime2 != ''" >
				<![CDATA[AND po.lTime <= #{lTime2,jdbcType=BIGINT}]]>
			</if>
		order by out_time desc
			<if test="startPos != null">
				limit #{startPos,jdbcType=INTEGER}, #{pageSize,jdbcType=INTEGER}
			</if>
		) as po
	LEFT JOIN park p ON p.park_id = po.park_id
	LEFT JOIN channel cin ON cin.channel_id = po.channel_id_in
	LEFT JOIN `user` uin ON uin.user_id = po.user_id_in
	LEFT JOIN channel_regin cr ON cr.channel_id = po.channel_id_in
	LEFT JOIN regin r ON r.regin_id = cr.regin_id
	LEFT JOIN channel cout ON cout.channel_id = po.channel_id_out
	LEFT JOIN `user` uout ON uout.user_id = po.user_id_out
	LEFT JOIN car_info ci ON ci.car_no = po.car_no
	LEFT JOIN car_type ct ON ct.car_type_id = ci.car_type_id

 

 

 

共有 人打赏支持
粉丝 0
博文 8
码字总数 4255
×
MarcoZhang
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: