Mysql语句优化思路之一

最新推荐文章于 2022-11-11 15:44:53 发布
最新推荐文章于 2022-11-11 15:44:53 发布
阅读量124 收藏
点赞数
文章标签： 数据库
原文链接：https://my.oschina.net/u/3352206/blog/1511159
版权

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

如：

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

 

 

 

转载于:https://my.oschina.net/u/3352206/blog/1511159

chengxiao3050
关注
Mysql优化sql语句的思路浅谈
12-14
如果突然交给你一个你完全不清楚的数据库让你进行优化，可能我们需要对sql语句进行优化，我们需要又怎么样的思路： 　1.首先我们要了解一下次库是以写为主还是读为主： 　通过show [session|global] status like '...
UNIX时间戳函数
qianshanL的博客
04-22 1608
UNIX_TIMESTAMP(date)若无参数调用，则返回一个Unix时间戳（‘1970-01-01 00:00:00’）作为无符号整数。 例1：使用UNIX_TIMESTAMP函数返回UNIX格式的时间戳，输入语句如下： mysql> select unix_timestamp(),unix_timestamp(now()),now(); 而FROM_UNIXTIME(date)函数...
mysql 语句优化的几种方式（总结）
热门推荐
隔壁老王呀的专栏
11-16 1万+
一、避免操作多余数据 1、使用where条件语句限制要查询的数据，避免返回多余的行。 2、 尽量避免select *，改使用select 列名，避免返回多余的列。 3、若插入数据过多，考虑批量插入。 4、尽量避免同时修改或删除过多数据。 5、尽量避免向客户端返回大数据量。 二、避免删库跑路 6、删除数据时，一定要加where语句。 三、where查询字句优化 7、避免在where 子句中的 “=” 左边进行内置函数、算术运算或其他表达式运算。 8、避免在 where 子句中使用 != 或 <> 操作符。 9
数据库设计---mysql语句心得-优化
weixin_43930851的博客
11-11 492
mysql
mysql 优化思路_Mysql 优化思路
weixin_42241168的博客
02-07 176
一. 整体思路首先需要使用 '慢查询日志功'能，去获取所有查询时间比较长的SQL语句 , 他会记录所有慢SQL的执行信息. 再利用自带的mysqldumpslow对 慢SQL文件进行分析排名, 找出查询时间慢的SQL.查看执行计划，查看有问题的SQL的执行计划针对查询慢的SQL语句进行优化使用 show profile/show profiles查看有问题的SQL的性能使用情况调整操作系统参数...
Mysql----sql语句优化思路
奇点
07-21 165
mysql的慢查询日志开启与问题定位 如何定位？？？ 如何开启慢查询日志与问题定位 第一步：查看是否已经开启了慢查询日志 第二步：开启慢查询日志 第三步：查看慢查询的时间临界值 第四步：设置慢查询的时间标准 1、查看是否已经开启了慢查询日志 mysql> show variables like 'slow%'; 2、开启 set global slow_query_lo...
MySQL全面优化思路1
08-03
MySQL全面优化是一个涉及多个层面的过程，包括硬件层优化、架构优化、事务与锁优化、索引优化、开发规范以及参数调整等。以下是对这些优化点的详细解释： 1. **硬件层优化**： - 网络配置：使用bonding技术实现...
mysql 分页优化解析
09-14
在描述中提到的优化思路主要分为以下几点： 1. **限制查询范围**：通过预设一个最大值，如`$max_pages*$count`，限制查询返回的记录数，防止一次性加载过多数据。这有助于减少数据库的负担，尤其是在处理大数据集时...
php+mysql查询优化简单实例
10-24
在描述中指出，文章会介绍SQL语句优化查询的技巧，这提示了我们对于数据库查询性能问题需要从多个维度去考量，比如查询语句的设计、索引的合理使用、查询条件的精确化等。而具体内容中，通过实际的例子来展示不同...
Mysql性能优化教程
04-10
### 常见案例及优化思路 在教程的这一部分，作者列举了多个优化案例，每个案例都提供了解决特定性能问题的思路和方法。比如，优化索引结构以减少I/O操作，调整查询语句以减少全表扫描，以及在写入操作中采用合适的...
mysql数据库优化方案（值得学习）
04-28
简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。 简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。 简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。 简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。 简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。 简单描述数据库优化方案，以及数据库一些常用的操作，包括一些简单的查询语句，函数使用，合适学习mysql的读者。
day12 mysql复杂sql语句编写
h496950806的专栏
04-06 154
SQL基本部分：1、数据库操作相关SQL ---- database创建数据库 create database 数据库名称; ------ 在sql后通过 character set 指定数据库本身字符集，如果没有指定将服务器默认* 服务器默认字符集 mysql安装目录/my.ini [mysqld] default-character-set 查看当前有哪些数据库 show database...
MySQL查询语句的优化思路和原则
weixin_39360124的博客
07-29 404
Query 语句的优化思路和原则主要提现在以下几个方面：1. 优化更需要优化的 Query2. 定位优化对象的性能瓶颈3. 明确的优化目标4. 从 Explain 入手5. 多使用 profile1、 开启 profiling 参数2、 执行 Query语句3、获取系统中保存的所有 Query 的 profile 概要信息4、针对单个 Query 获取详细的 profile 信息。6. 永远用小结果集驱动大的结果集7. 只取出自己需要的 Columns8. 仅仅使用最有效的过滤条件9. 尽可能避免复杂的 J
mysql sql优化思路_mysql优化sql语句的思路浅谈
weixin_33247358的博客
02-26 155
如果突然交给你一个你完全不清楚的数据库让你进行优化，可能我们需要对sql语句进行优化，我们需要又怎么样的思路：1.首先我们要了解一下次库是以写为主还是读为主：通过show [session|global] status like '%Com_%'查询当前数据库的状态，session是当前连接的，global是自从上次数据库启动后的。默认不写是session| Com_select | 2 ...
MySql中查询语句优化的简单思路
qq_28908085的博客
08-18 169
前言 要优化SQL，首先要找出那些执行速度慢的Sql语句。通过开启慢查询，可以实现。我们的CPU资源也比较紧张的时候，也完全可以在大部分时候关闭该功能，而只需要间断性的打开SlowQueryLog功能来定位可能存在的慢查询。 开启慢查询：set global slow_query_log = on 指定慢查询日志目录： set global slow_query_log_file=’/var/lib/mysql/slow.log’ 指定慢查询时间阈值：set global long_query_time
Mysql优化的基本思路和原则
大鹏
03-19 1251
在分析如何优化MySQL Query 之前，我们需要先了解一下Query 语句优化的基本思路和原则。一 般来说，Query 语句的优化思路和原则主要提现在以下几个方面：      1. 优化更需要优化的Query；      2. 定位优化对象的性能瓶颈；      3. 明确的优化目标；      4. 从Explain 入手；      5. 多使用profile      6.
Mysql语句查询,主要自己思路的理解
weixin_52366309的博客
12-27 400
提示：sql语句三表查询思路整理 整理思路 一、在写sql查询语句中,我们需要什么样思路 先要想我们需要得到哪个表的数据 我们有哪个表的数据 之间的关联关系是什么?最好把他们之间的架构图能清晰的展示出来 通过关系理清他们之间联系 这上面就是这五张表 需要通过user_id查询所对应的ss_module的模块信息 我先查简单一点的,也就是三表查询,其实就是两个表,然后多了一个中间表 先简单点,我们需要结果是通过ss_user表中user_id主键查询ss_role表的所有信息 SELECT r.*
mysql-常规优化思路
豆豆不在了
03-16 1216
操作系统优化 sysbench 工具 1.测试CPU性能 2.测试IO读写性能 3.测试事务性能 数据库系统参数优化 1.使用 show processlist命令长时间查看服务器负载情况 2.开启服务器慢查询开关 3.减少临时表使用，可以EXPLAIN 语法查看 extra 是否为 using temporary @如果group by 的列没有索引，会产生内部临时表
mysql 慢sql优化思路
最新发布
08-28
MySQL慢SQL优化通常涉及以下几个步骤： 1. **识别慢查询**：首先，通过`EXPLAIN`语句分析查询计划，查看哪些查询执行时间过长。MySQL的日志系统如General Log可以帮助追踪详细信息。 2. **查询优化**： - **索引...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值