文档章节

Mysql语句优化思路之一

MarcoChang
 MarcoChang
发布于 2017/08/16 14:32
字数 1040
阅读 18
收藏 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
深圳
程序员
私信 提问
加载中

评论(0)

mysql 开发进阶篇系列 5 SQL 优化

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

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

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

掘金官方
2017/12/25
0
0
mysql 开发进阶篇系列 5 SQL 优化(表优化)

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

osc_w2v6ws8d
2018/07/23
4
0
MYSQL之not in优化方法:left join

MYSQL之not in优化方法:left join Author:飘易 Source:飘易 正 文: 有一个项目,mysql 语句采用了not in,结果某些页面打开需要40多秒,排查sql语句后,发现是采用了 not in 语法导致全表...

osc_80dzmuml
2019/01/02
3
0
5分钟轻松应对 MySQL DBA 面试

作为DBA,今后可能都会遇到误删数据、服务器宕机,以及各种疑难故障问题,这些状况就跟家常便饭一样 这个时节,大家都在忙着找工作,我们就来总结一下MySQL DBA相关的技术面试题。 01 问题1...

osc_p8erz1zn
2019/03/04
2
0

没有更多内容

加载失败,请刷新页面

加载更多

kafka重要概念与集群重点配置详解

重要概念 broker 一个broker就是一个kafka实例,负责接收、转发、存储消息,kafka集群就是由多个broker组成。 topic kafka的topic是一个逻辑概念,就是对消息分组、分类,便于区分处理不同业...

trayvon
46分钟前
42
0
在树莓派里搭建 Lighttpd 服务器

Lighttpd 像 Ngnix 一样,是被设计运行在低内存,低 CPU 负载的设备上,它们都非常适合在树莓派上运行。 本文将介绍如何在树莓派上运行基本配置的 Lighttpd ,以及如何与 PHP-FRM 一起使用。...

良许Linux
46分钟前
21
0
Service Mesh 高可用在企业级生产中的实践 | 线上直播回顾

Service Mesh Virtual Meetup 是 ServiceMesher 社区和 CNCF 联合主办的线上系列直播。本期为 Service Mesh Virtual Meetup#1 ,邀请了四位来自不同公司的嘉宾,从不同角度展开了 Service Me...

SOFAStack
55分钟前
37
0
word转pdf软件有哪些?word转pdf软件怎么操作?

虽说日常生活中,很多人写报告写策划都依然会使用word程序,但是严格来说,word却并非是唯一常用的办公软件,就比如说pdf,就越来越受年轻人的欢迎了,那么经常用电脑办公的你是否知道,其实...

开源86
今天
39
0
Java创建对象的过程(类实例化)

1.检查类是否被加载。 当虚拟机遇到new指令后,会先去常量池检查有没有该类的符号引用,并且检查这个类有没有进行加载、解析、初始化过,没有就先执行类加载过程。 2.为对象分配内存空间*。 ...

曦鱼violet
今天
26
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部