文档章节

Mysql语句优化思路之一

MarcoChang
 MarcoChang
发布于 2017/08/16 14:32
字数 1040
阅读 0
收藏 0

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

如:

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

 

 

 

© 著作权归作者所有

共有 人打赏支持
MarcoChang
粉丝 0
博文 8
码字总数 4255
作品 0
深圳
程序员
mysql 开发进阶篇系列 5 SQL 优化

一. 使用sql提示 sql 提示(sql hint)是优化数据库的一个重要手段, 是在sql语句中加入一些人为的提示来达到优化操作的目的。   1.1 use index     在查询语句中表名的后面,添加use ind...

花阴偷移
07/23
0
0
SQL-SQL优化-索引

图文并茂详解 SQL JOIN Join 是关系型数据库系统的重要操作之一,一般关系型数据库中包含的常用 Join:内联接、外联接和交叉联接等。如果我们想在两个或以上的表获取其中从一个表中的行与另一...

掘金官方
2017/12/25
0
0
MySQL · 性能优化 · MySQL常见SQL错误用法

前言 MySQL在2016年仍然保持强劲的数据库流行度增长趋势。越来越多的客户将自己的应用建立在MySQL数据库之上,甚至是从Oracle迁移到MySQL上来。但也存在部分客户在使用MySQL数据库的过程中遇...

阿里云RDS-数据库内核组
2017/03/04
0
0
【20180105】mysql日常优化一则

导读:在日常的MySQL的SQL语句优化工作中,总会遇到了各种各样的问题。今天就是遇到了一个比较诡异的问题,在这里记录下来方便自己的记忆。 MySQL版本信息: MySQL 5.6.38 SQL语句(其中的关键...

liuhuang9496
01/05
0
0
mysql--------大数据量分页sql语句优化

分页程序原理很简单,这里就不多说了,本篇文章主要说的是在数据表记录量比较大的情况下,如何将分页SQL做到更优化,让MySQL执行的更快的方法。 一般的情况下,我们的分页SQL语句是这样的: ...

切切歆语
01/25
0
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

新工作与老项目

新的工作不知不觉的干了一个多月了。怎么说呢,跟想象中的差别不少,本来想的能进来跟大公司的同事能有很多交流,能在团队中跟大牛学习更快。结果公司的这个项目上只有两个程序员,项目是十年...

zypy333
14分钟前
0
0
mysql 在windows的安装

mysql 在windows的安装。 mysql64位的server的下载地址是: https://dev.mysql.com/downloads/mysql/ 使用的是5.7版本。 下载安装包,解压至D:\mysql\mysql-5.7.23-winx64\ 在D:\mysql\mysq...

lxzh504
26分钟前
1
0
云技术、大数据(hadoop)入门常见问题回答

当我们学习一门新技术的时候,我们总是产生各种各样的问题,这些问题整理出来,包括该 1.如何学习hadoop? 2.hadoop常见问题? 3.还有hbase、hive安装使用等? 你知道搭建hadoop平台需要些什...

董黎明
26分钟前
1
0
小程序自定义底部tab

场景 1.tabBar是在内页而非首页,这时就不得不自定义一个tabBar了 2.自定义风格 3.子页数量超过5个,得到更多了tab 4.改变点击tab默认事件,比如出登录界面,或者弹出上拉子菜单等 步骤 1.照...

萤火的萤火
32分钟前
1
0
shell炫技

1.为脚本添加“--help” #!/bin/shif [ ${#@} -ne 0 ] && [ "${@#"--help"}" = "" ]; then printf -- '...help...\n'; exit 0;fi; 2.输出字体添加颜色 https://misc.flogisoft.com......

HJCui
32分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部