百万级就得几分钟，这子查询怎优化？

会稽梁山伯 2020-01-02 01:40:30
select

(
select count(tli.overspeed)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.overspeed > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as overspeed,

(
select count(tli.hurriedlySpeedUp)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.hurriedlySpeedUp > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as hurriedlySpeedUp,

(
select count(tli.hurriedlySpeedDown)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.hurriedlySpeedDown > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as hurriedlySpeedDown,

(
select count(tli.fatigue)
from
TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.fatigue > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as fatigue,

(
select count(tli.leftLight)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.leftLight > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as leftLight,

(
select count(tli.rightLight)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.rightLight > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as rightLight,

(select count(tli.farLight)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.farLight > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as farLight,

(select count(tli.nearLight)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.nearLight > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as nearLight,

(select count(tli.brake)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.brake > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as brake,

(case when(
select sum(tli.timeInterval)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
)> 0 then (
select sum(tli.timeInterval)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) else 0 end) as totalTime,

(case when(
select sum(tli.timeInterval)
from TerminalLocationInfo tli
where HOUR(tli.actionTime) < 5
and tli.truckId = tk.id
and tli.driverId = d.id
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
)> 0 then (
select sum(tli.timeInterval)
from TerminalLocationInfo tli
where HOUR(tli.actionTime) < 5
and tli.truckId = tk.id
and tli.driverId = d.id
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) else 0 end) as totalNightTime,

(
select count(tli.night)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and HOUR(tli.actionTime) < 5
and tli.driverId = d.id
and tli.night > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as night,

(
select count(tli.acc)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and tli.acc > 0
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as acc,

(
select count(tli.id)
from TerminalLocationInfo tli
where tli.truckId = tk.id
and tli.driverId = d.id
and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
) as totalCount
from
Truck tk
left join TerminalLocationInfo TLI on TLI.truckId = tk.id
left join Company C on C.id = tk.companyId
left join Driver d on d.id = TLI.driverId
where
TLI.actionTime >= "2018-01-01 00:00:00"
and

TLI.actionTime <= "2018-01-01 23:59:59"
and

TLI.actionTime >= "2019-01-01"

group by tk.id,d.id
order by TLI.actionTime
...全文
77 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
会稽梁山伯 2020-01-03
  • 打赏
  • 举报
 回复
引用 6 楼 吉普赛的歌 的回复:
报表的常见的几个优化方向： 1. 加索引; 2. 中间结果用临时表缓存; 3. 每天凌晨生成结果表，用户查询时只查结果表，以空间换时间。
对，这几个都是有试过，主要还是sql语句的优化，子查询还得尽量的少。而且，这个其实就是进行统计做结果表。
会稽梁山伯 2020-01-03
  • 打赏
  • 举报
 回复
引用 5 楼 砸死牛顿的苹果 的回复:

TLI.actionTime <= "2018-01-01 23:59:59"
  and
TLI.actionTime >= "2019-01-01"
你确定有结果吗？
这个只是临时改了下数值。
会稽梁山伯 2020-01-03
  • 打赏
  • 举报
 回复
引用 2 楼 文盲老顾 的回复:
select sum(case when tli.overspeed > 0 then 1 else 0 end) as overspeed
	,sum(case when tli.hurriedlySpeedUp > 0 then 1 else 0 end) as hurriedlySpeedUp
	,sum(case when tli.hurriedlySpeedDown > 0 then 1 else 0 end) as hurriedlySpeedDown
	,sum(case when tli.fatigue > 0 then 1 else 0 end) as fatigue
	,sum(case when tli.leftLight > 0 then 1 else 0 end) as leftLight
	,sum(case when tli.rightLight > 0 then 1 else 0 end) as rightLight
	,sum(case when tli.farLight > 0 then 1 else 0 end) as farLight
	,sum(case when tli.nearLight > 0 then 1 else 0 end) as nearLight
	,sum(case when tli.brake > 0 then 1 else 0 end) as brake
from
Truck tk
left join TerminalLocationInfo  tli
on tli.truckId = tk.id and tli.driverId = d.id and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
left join    Company C                   on    C.id = tk.companyId
left join    Driver d                    on    d.id = TLI.driverId
where TLI.actionTime >= "2018-01-01 00:00:00"
and TLI.actionTime <= "2018-01-01 23:59:59"
and TLI.actionTime >= "2019-01-01"
group by tk.id,d.id
order by TLI.actionTime
后边的case when 部分没仔细看，大概就这么个意思 由于你每个 count，都从新执行了一个子查询，那效率肯定很低了，你既然在外边已经有了left join，那么直接sum(case when 条件符合 then 1 else 0 end)肯定比你执行子查询快
对，2楼老顾这样是行的。重点还是子查询导致，之前其实有这样写过，但数据不正确，可能是写法不对。
吉普赛的歌 2020-01-02
  • 打赏
  • 举报
 回复
报表的常见的几个优化方向： 1. 加索引; 2. 中间结果用临时表缓存; 3. 每天凌晨生成结果表，用户查询时只查结果表，以空间换时间。
砸死牛顿的苹果 2020-01-02
  • 打赏
  • 举报
 回复 


TLI.actionTime <= "2018-01-01 23:59:59"

  and

TLI.actionTime >= "2019-01-01"

你确定有结果吗？
文盲老顾 2020-01-02
  • 打赏
  • 举报
 回复 

-- 一个小例子
select companyId  -- 企业ID
,count(0) as total  -- 产品总数
,sum(case when isOnline=1 and isDelete=0 then 1 else 0 end) as online_products -- 已上线产品总数
,sum(case when isDelete=1 then 1 else 0 end) as deleted_products    -- 已删除产品总数
,sum(case when isOnline=0 and isDelete=0 then 1 else 0 end) as unchecked_products  -- 未审核产品总数
from products
group by companyId
文盲老顾 2020-01-02
  • 打赏
  • 举报
 回复
现在的问题就是，子查询和外边的关联查询的条件是否一致，如果不一致，那么在最外层的条件里，再套个子查询，统计好了之后再用外层条件
文盲老顾 2020-01-02
  • 打赏
  • 举报
 回复 
select sum(case when tli.overspeed > 0 then 1 else 0 end) as overspeed
	,sum(case when tli.hurriedlySpeedUp > 0 then 1 else 0 end) as hurriedlySpeedUp
	,sum(case when tli.hurriedlySpeedDown > 0 then 1 else 0 end) as hurriedlySpeedDown
	,sum(case when tli.fatigue > 0 then 1 else 0 end) as fatigue
	,sum(case when tli.leftLight > 0 then 1 else 0 end) as leftLight
	,sum(case when tli.rightLight > 0 then 1 else 0 end) as rightLight
	,sum(case when tli.farLight > 0 then 1 else 0 end) as farLight
	,sum(case when tli.nearLight > 0 then 1 else 0 end) as nearLight
	,sum(case when tli.brake > 0 then 1 else 0 end) as brake
from
Truck tk
left join TerminalLocationInfo  tli
on tli.truckId = tk.id and tli.driverId = d.id and str_to_date(tli.actionTime,'%Y-%m-%d') = str_to_date(TLI.actionTime,'%Y-%m-%d')
left join    Company C                   on    C.id = tk.companyId
left join    Driver d                    on    d.id = TLI.driverId
where TLI.actionTime >= "2018-01-01 00:00:00"
and TLI.actionTime <= "2018-01-01 23:59:59"
and TLI.actionTime >= "2019-01-01"
group by tk.id,d.id
order by TLI.actionTime
后边的case when 部分没仔细看，大概就这么个意思 由于你每个 count，都从新执行了一个子查询，那效率肯定很低了，你既然在外边已经有了left join，那么直接sum(case when 条件符合 then 1 else 0 end)肯定比你执行子查询快
sxq129601 2020-01-02
  • 打赏
  • 举报
 回复
搞个报表库吧
MySQL：百万级数据查询优化
在大数据时代，企业业务系统产生的数据量呈指数级增长，当 MySQL 数据库中的数据规模达到百万级时，传统的查询方式往往会导致性能瓶颈，出现查询缓慢、系统响应延迟等问题，严重影响业务正常运行。复杂的查询语句，如多表关联查询、子查询等，在处理百万级数据时，会产生大量的中间结果集，占用过多的内存和磁盘 I/O 资源。例如，在电商系统中，统计某类商品的销售情况，需要关联订单表、订单详情表和商品表进行查询，若查询语句未优化，可能会耗尽服务器内存，导致数据库响应缓慢，甚至服务崩溃。
MYSQL百万级数据分页查询优化实战
MYSQL大数据量分页查询优化 一、记一次mysql分页查询优化 最近项目中，需要将公司老的订单日志数据迁移到新的ElasticSearch统一日志存储，我们老日志数据是分库分表存储在mysql数据库中(按天分表)，单表数据量在500w左右，本人就写了一个小程序负责mysql到es的数据迁移，功能其实很简单，但其中出现了一些没有考虑到的问题，比如查询的效率问题，在此做下记录。老日志数据如下 数...
hive子查询sql效率优化
今天写了个sql跑了好久，老大过来一看指点下，多用子查询，多用where，然后在join， 提高十多倍，神奇了。想了想，还是老大经验丰富。 如果先join，就全表扫描，然后最后where最后筛选，比较耗时。 如果用子查询，就可以利用where过滤不相关的字段，不但增加了map 数量，还减少了数据量。 以下是我的sql 对比： SELECT from_unixtime(cast(a.t...
百万级数据库优化方案--Oracle 性能优化总结
1.对查询优化，尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。复制最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据库.备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。复制复制。
mysql子查询多字段_（mysql）多个字段需要子查询，求优化
问个问题，(MySQL)A表的字端有code_name,code_id,code_field,另一表B表有很多属性，例如国籍，籍贯等等，国籍子查询查法为select code_name form A表 where code_id=B表.gj_id and code_field = ‘gj’。现在遇到的困难时子查询太慢了，而且除了国籍外还有籍贯等属性，如果都去用子查询的话，一次估计几分钟。这种情况下...
应用实例

27,580

社区成员

68,544

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章