文档章节

Mysql语句优化思路之一

MarcoChang
 MarcoChang
发布于 2017/08/16 14:32
字数 1040
阅读 0
收藏 0
点赞 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
深圳
程序员
SQL-SQL优化-索引

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

掘金官方 ⋅ 2017/12/25 ⋅ 0

MySQL优化思路,以及解决方案

mysql优化索引和配置,以及慢查询分析 s首先基本的思路 1)性能瓶颈定位 使用show命令、 慢查询日志、 explain分析查询、 profiling分析查询、 2)索引及查询优化 3)配置优化 MySQL数据库常...

tty之星 ⋅ 2017/06/18 ⋅ 0

MySQL查看SQL语句执行效率

Explain命令在解决数据库性能上是第一推荐使用命令,大部分的性能问题可以通过此命令来简单的解决,Explain可以用来查看 SQL 语句的执行效 果,可以帮助选择更好的索引和优化查询语句,写出更...

兴趣e族 ⋅ 2017/03/09 ⋅ 0

【20180105】mysql日常优化一则

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

liuhuang9496 ⋅ 01/05 ⋅ 0

mysql--------大数据量分页sql语句优化

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

切切歆语 ⋅ 01/25 ⋅ 0

MySQL · 性能优化 · MySQL常见SQL错误用法

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

阿里云RDS-数据库内核组 ⋅ 2017/03/04 ⋅ 0

sql优化(二)

传送门 sql优化(一)http://my.oschina.net/zimingforever/blog/59515 今天突然发现昨天写的一个sql效率的博文上了OSC首页推荐。感谢国家。 明天又要开周会分享sql优化了。话说上次介绍这次...

王小明123 ⋅ 2012/06/06 ⋅ 0

MySQL深入07-查询缓存

MySQL查询执行流程 查询流程: 客户端发送一条查询给服务器; 服务器先检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果;否则,进入下一阶段; 服务器进行SQL解析、预处理,再由...

余二五 ⋅ 2017/11/23 ⋅ 0

Mysql 高负载排查思路

Mysql 高负载排查思路 发现问题 top命令 查看服务器负载,发现 mysql竟然百分之两百的cpu,引起Mysql 负载这么高的原因,估计是索引问题和某些变态SQL语句. 排查思路 1. 确定高负载的类型,top命...

huangzp168 ⋅ 2017/11/13 ⋅ 0

MySQL limit 优化,百万至千万级快速分页:复合索引

MySQL 性能到底能有多高?用了php半年多,真正如此深入的去思考这个问题还是从前天开始。有过痛苦有过绝望,到现在充满信心!MySql 这个数据库绝对是适合dba级的高手去玩的,一般做一点1万篇...

xrzs ⋅ 2012/12/19 ⋅ 2

没有更多内容

加载失败,请刷新页面

加载更多

下一页

Java Web如何操作Cookie的添加修改和删除

创建Cookie对象 Cookie cookie = new Cookie("id", "1"); 修改Cookie值 cookie.setValue("2"); 设置Cookie有效期和删除Cookie cookie.setMaxAge(24*60*60); // Cookie有效时间 co......

二营长意大利炮 ⋅ 今天 ⋅ 0

【每天一个JQuery特效】淡入淡出显示或隐藏窗口

我是JQuery新手爱好者,有时间就练练代码,防止手生,争取每天一个JQuery练习,在这个博客记录下学习的笔记。 本特效主要采用fadeIn()和fadeOut()方法显示淡入淡出的显示效果显示或隐藏元...

Rhymo-Wu ⋅ 今天 ⋅ 0

Spring JDBC使用方法

普通实现: 1、创建数据表customer。 可以使用任何数据库实现,在项目中要引入相应数据库驱动包并配置相应数据库连接。 2、创建Customer pojo。 Customer类的属性对应数据库的属性,除了为每...

霍淇滨 ⋅ 今天 ⋅ 0

Contos 7 安装Jenkins

Jenkins是一款能提高效率的软件,它能帮你把软件开发过程形成工作流,典型的工作流包括以下几个步骤 开发 提交 编译 测试 发布 有了Jenkins的帮助,在这5步中,除了第1步,后续的4步都是自动...

欧虞山 ⋅ 今天 ⋅ 0

revel

revel install go get github.com/revel/revelgo get github.com/revel/cmd create new app revel new git.oschina.net/zdglf/myapp run app revel run git.oschina.net/zdglf/myapp ot......

zdglf ⋅ 今天 ⋅ 0

49. Group Anagrams - LeetCode

Question 49. Group Anagrams Solution 思路:维护一个map,key是输入数组中的字符串(根据字符排好序) Java实现: public List<List<String>> groupAnagrams(String[] strs) { Map<Strin......

yysue ⋅ 今天 ⋅ 0

spring Email

使用spring发Email其实就是使用spring自己封装携带的一个javamail.JavaMailSenderImpl类而已。这个类可以当一个普通的java对象来使用,也可以通过把它配置变成spring Bean的方式然后注入使用...

BobwithB ⋅ 今天 ⋅ 0

spark 整理的一些知识

Spark 知识点 请描述spark RDD原理与特征? RDD全称是resilient distributed dataset(具有弹性的分布式数据集)。一个RDD仅仅是一个分布式的元素集合。在Spark中,所有工作都表示为创建新的...

tuoleisi77 ⋅ 今天 ⋅ 0

思考

时间一天天过感觉自己有在成长吗?最怕的是时光匆匆而过,自己没有收获!下面总结下最近自己的思考。 认识自己 认识另一个自己,人们常说要虚心听取别人意见和建议。然而人往往是很难做到的,...

hello_hp ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部