sql优化，求助，在线等！！

gudaobeige 2012-09-05 05:15:14
两张表report201209，clientinfo 关联查询；clientinfo 400条记录，report201209 千万级别的。



SELECT reportDate,a.param,b.model,b.price,SUM( CASE WHEN a.dataType like 'FB%' THEN a.dataCount ELSE 0 END) as flightcount, b.hotelprice,SUM( CASE WHEN a.dataType like 'HB%' THEN a.dataCount ELSE 0 END) as hotelcount

      FROM report201209 a, clientinfo b 

      WHERE a.param = b.modelID AND (b.startTime='' or a.reportDate>=b.startTime) AND (a.reportDate<=b.endTime or b.endTime='') 

      AND dataType IN ('FB_OHDI', 'FB_NHDI', 'FB_NBDI', 'FB_OBDI', 'FB_OD2I', 'FB_ND2I', 'FB_OFCI', 'FB_NFCI', 'FB_OOTI', 'FB_NOTI' ,'FB_OHDD', 'FB_NHDD', 'FB_NBDD', 'FB_OBDD', 'FB_OD2D', 'FB_ND2D', 'FB_OFCD', 'FB_NFCD', 'FB_OOTD', 'FB_NOTD', 'HB_O', 'HB_N')

      AND b.modelID IN ('114115','530la','93113','ap_1296d1901f0','ap_1298c9526fb','ap_12a13771275','ap_12e8f8ae6bd','ap_12ee0af6c22','ap_12f5357cf8e','ap_12f76efa302','ap_12f76f32e82','ap_1300138fdd9','ap_13349c8a7d7','ap_136f38d49a6','ap_1372560e926','auto4f5da71a','auto_4eaa7303','auto_4eba55f3','auto_4eba55fa','auto_4eba5603','auto_4eba5621','auto_4eba574d','auto_4eba5754','auto_4eba575c','auto_4efebc19','auto_4efebc27','auto_4f052092','auto_4f0e8e48','auto_4f0e8fe3','auto_4f4def9f','auto_4f4df13d','auto_4f599da1','auto_4f5da71a','auto_4f5qd994','auto_4f97c52d','auto_4fb227b3','auto_5003eaf3','auto_50191b2c','auto_50191b2d','auto_50191b2e','auto_50191b2f','auto_50191b30','auto_50191b31','auto_50191b32','auto_50191b33','auto_50191b34','auto_50191b35','auto_50191b36','auto_50191b37','auto_50191b38','auto_50191b39','auto_50191b3a','auto_50191b3b','auto_50191b3c','auto_50191b3d','auto_50191b3e','auto_50191b3f','auto_50191b40','auto_50191b41','auto_50191b42','auto_50191b43','auto_50191b44','auto_50191b45','auto_50191b46','auto_50191b47','auto_50191b48','auto_50191b49','auto_50191b4a','auto_50191b4b','auto_50191b4c','auto_50191b4d','auto_50191b4e','auto_50191b4f','auto_50191b50','auto_50191b51','auto_50191b52','auto_50191b53','auto_50191b54','auto_50191b55','auto_50191b56','auto_50191b57','auto_50191b58','auto_50191b59','auto_50191b5a','auto_50191b5b','auto_50191b5c','auto_50191b5d','auto_50191b5e','auto_50191c20','auto_50191c21','auto_50191c22','auto_50191c23','auto_50191c24','auto_50191c25','auto_50191c26','auto_501f8251','auto_5024d019','auto_5024d02a','auto_503aeb86','auto_503aeb89','auto_503aeb8b','auto_503aeb8d','auto_503aeb8f','auto_503aeb91','auto_503aeb93','auto_503aeb95','auto_503aeb97','auto_503aeb99','auto_503b4632','auto_503b4633','auto_503b4634','auto_503b4635','auto_503b46f2','auto_503b46f3','au_hao90','au_ie189','au_ok1616','dfly=1122','dfly=1122b','dfly=114115','dfly=114115a','dfly=114FA1','dfly=114fa2','dfly=114fa3','dfly=123wa ','dfly=2345a','dfly=2345a1','dfly=2345a2','dfly=256cc','dfly=256cc1','dfly=568b','dfly=5w','dfly=776la4','dfly=776la5','dfly=776la6','dfly=776la7','dfly=8420','dfly=8420a','dfly=8420b','dfly=88488','dfly=88488a','dfly=88488d','dfly=91ni','dfly=91nia','dfly=92so','dfly=92so1','dfly=92so2','dfly=93113','dfly=93113a','dfly=93113c','dfly=9384c ','dfly=bebc','dfly=bebc4','dfly=bebc5','dfly=bebc6','dfly=bebc7','dfly=bebc8','dfly=bebc9','dfly=fedfe','dfly=fedfe1','dfly=fg001','dfly=fhoa','dfly=fhoa1','dfly=ibbc','dfly=ie189a','dfly=jiangmin','dfly=jiangmin1','dfly=jiangmin2','dfly=jjol','dfly=jjol4','dfly=jjol5','dfly=ok1616','dfly=pc123','dfly=taobao','dfly=taobao,dfly=1122b','dfly=tg1234','dfly=tg1234a','dfly=tg1234b','dfly=vvbooo2','dfly=woso','dh=jj','gjjp=93113e','hao90sou','jjol') 

      AND a.reportDate ='2012-09-01' GROUP BY reportDate,a.param ORDER BY reportDate,a.param ;



求优化！！！！！
...全文
249 22 打赏 收藏 转发到动态 举报
写回复
用AI写文章
22 条回复
切换为时间正序
请发表友善的回复…
发表回复
MiceRice 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 的回复:]
如果你们已有索引顺序是：channelID, reportDate, param, dataType 可能还可以兼用。
[/Quote]

修正下，考虑到因为有GroupBy，所以即便你们的索引是这样也不行。

GroupBy的字段，必须在索引最前面，而且GroupBy字段顺序跟索引字段顺序必须保持一致。

也就是可以这样：reportDate, param, dataType, channelID
但估计这样肯定会对该索引原来的作用发生较大影响，所以还是别考虑重用索引了。
gudaobeige 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 18 楼 的回复:]
这个是做了我建议的联合索引么？

看来有点效果？
[/Quote]

效果很多，现在又两张分月表b7、b8，，b8是加了你的索引的。
就上你改造过的sql：b8的执行时间0.089ms，b7是4.164ms，大体就是在这个范围
效果还是很明显的。
MiceRice 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 17 楼 的回复:]
我们现在已有的索引是这个：channelID, dataType, reportDate, param
神啊，能不能根据这个索引去改造下原来的sql，使其可以不用增加新的索引。
[/Quote]

基本上不可能，你这个索引的顺序，dataType 在前面；如果是在最后面，还有点希望。

因为核心的问题是：GROUP BY reportDate, a.param （显然你的GroupBy是不能换掉的）

如果你们已有索引顺序是：channelID, reportDate, param, dataType 可能还可以兼用。

但是不推荐随意修改原有索引，因为怕有别的地方恰好需要另一种顺序才能生效。


可以跟你领导探讨下，看怎么做更合适。
gudaobeige 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 的回复:]
楼主啊，在线等的优化贴，就准备太监了么？
[/Quote]

神啊，给介绍点着方面的资料呗。
MiceRice 2012-09-11
  • 打赏
  • 举报
 回复
这个是做了我建议的联合索引么？

看来有点效果？
gudaobeige 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 的回复:]
楼主啊，在线等的优化贴，就准备太监了么？
[/Quote]

如果做区间查询， AND a.reportDate >= '2012-08-01' AND a.reportDate <= '2012-08-31' 



1	SIMPLE	b	ALL					112	Using where; Using temporary; Using filesort

1	SIMPLE	a	ALL	Index_reportDate,Index_query				36466422	Using where; Using join buffer


神啊，差别很大啊？？

冒生命危险加的索引啊。哈哈哈哈哈哈

我们现在已有的索引是这个：channelID, dataType, reportDate, param
神啊，能不能根据这个索引去改造下原来的sql，使其可以不用增加新的索引。
gudaobeige 2012-09-11
  • 打赏
  • 举报
 回复
[Quote=引用 15 楼 的回复:]
楼主啊，在线等的优化贴，就准备太监了么？
[/Quote]

大神严重了，最近一直没有找到合适的机会，怕被逮啊，哈哈。现在好了 拿到数据了。



  1	SIMPLE	a	ref	Index_reportDate,Index_query	Index_query	472	const,const,const	1	Using where

1	SIMPLE	b	ALL					112	Using where


耗时：时间: 0.011ms

MiceRice 2012-09-10
  • 打赏
  • 举报
 回复
楼主啊，在线等的优化贴，就准备太监了么？
MiceRice 2012-09-06
  • 打赏
  • 举报
 回复
对SQL做了轻微修改：
SELECT reportDate,a.param,b.model,b.price,
SUM(CASE WHEN a.dataType like 'FB%' THEN a.dataCount ELSE 0 END) as flightcount,
b.hotelprice,
SUM( CASE WHEN a.dataType like 'HB%' THEN a.dataCount ELSE 0 END) as hotelcount
FROM report201209 a, clientinfo b
WHERE a.param = b.modelID
AND (b.startTime='' or a.reportDate>=b.startTime)
AND (a.reportDate<=b.endTime or b.endTime='')
AND a.dataType IN ('FB_OHDI', ..., 'HB_N')
AND a.param IN ('114115','530la','93113',...,'jjol')
AND a.reportDate ='2012-09-01'
GROUP BY reportDate, a.param;
[/code]

因为B表很小，所以主要还是考虑优化A表。

A表建立联合索引，注意顺序不能随意修改：reportDate, param, dataType
B表建立单字段索引：modelID

最后注意观察执行计划，是否正确使用了联合索引，此外最好能把执行计划贴出来看看。
菖蒲老先生 2012-09-06
  • 打赏
  • 举报
 回复
AND b.modelID IN
换成 AND （b.modelID IN （。。。） or b.modelID IN （。。。）） 试试呢。。。
gudaobeige 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 1 楼 的回复:]
运行效果如何？

report201209表上有什么索引？

SQL执行计划如何？
[/Quote]

reportDate普通索引
channelID, dataType, reportDate, param 四个做个一个普通索引
小绵羊 2012-09-06
  • 打赏
  • 举报
 回复
explain sql语句把结果发出来看看
MiceRice 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 的回复:]
晚上我测试下吧，现在不能啊。哪几张表不能随便加索引。
[/Quote]

OK。看来你是在生产环境上测试，其实最好是有个测试环境。

另外，下次测试建议日期用个范围来试试看，4ms的对比性太弱了。

AND a.reportDate >='2012-09-01' AND a.reportDate <='2012-09-10'
gudaobeige 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 的回复:]
哥们，我6楼建议你加的复合索引：
A表建立联合索引，注意顺序不能修改：reportDate, param, dataType

没建么？


其实这是分析过你语句要求的：
GROUP BY reportDate, a.param; -- 所以复合索引顺序为 reportDate, param，这样过滤后的结果可以直接用于分组，不需要进行重排序
AND a.dataType IN……
[/Quote]

晚上我测试下吧，现在不能啊。哪几张表不能随便加索引。

MiceRice 2012-09-06
  • 打赏
  • 举报
 回复
哥们，我6楼建议你加的复合索引：
A表建立联合索引，注意顺序不能修改：reportDate, param, dataType

没建么？


其实这是分析过你语句要求的：
GROUP BY reportDate, a.param; -- 所以复合索引顺序为 reportDate, param，这样过滤后的结果可以直接用于分组，不需要进行重排序
AND a.dataType IN ('FB_OHDI', ..., 'HB_N') -- -- 复合索引的第三过滤字段
AND a.param IN ('114115','530la','93113',...,'jjol') -- 复合索引的第二过滤字段
AND a.reportDate ='2012-09-01' -- 复合索引的第一过滤字段
gudaobeige 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
用的Index对么？你这里显示是用了： Index_reportDate，是我说的那个复合索引么？

对了，你这个是啥数据库？
[/Quote]

补充一句，耗时太长了，4+ms，接受不了，这还是查询的一天的数据，如果多日数据就无语啦
gudaobeige 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 8 楼 的回复:]
用的Index对么？你这里显示是用了： Index_reportDate，是我说的那个复合索引么？

对了，你这个是啥数据库？
[/Quote]

不是符合索引，是对reportData加到索引。
复合索引是：index ——3， 字段加在了：channelID, dataType, reportDate, param

mysql 数据库
MiceRice 2012-09-06
  • 打赏
  • 举报
 回复
用的Index对么？你这里显示是用了： Index_reportDate，是我说的那个复合索引么？

对了，你这个是啥数据库？
gudaobeige 2012-09-06
  • 打赏
  • 举报
 回复
[Quote=引用 6 楼 的回复:]
对SQL做了轻微修改：
SELECT reportDate,a.param,b.model,b.price,
SUM(CASE WHEN a.dataType like 'FB%' THEN a.dataCount ELSE 0 END) as flightcount,
b.hotelprice,
SUM( CASE WHEN a.dataType like 'HB%' THE……
[/Quote]
比原来快了差不多0.7ms，



1	SIMPLE	a	ref	Index_reportDate	Index_reportDate	32	const	1712586	Using where; Using temporary; Using filesort

1	SIMPLE	b	ALL					272	Using where; Using join buffer


tanjianlin88 2012-09-05
  • 打赏
  • 举报
 回复
in 的性能很 差的，建议采用内连接将表连接起来处理，否则碰到千万级表，直接歇菜吧
加载更多回复（1）
kettle迁移示例
kettle数据迁移示例。从mysql数据库表迁移到oracle数据库表。
使用AI优化慢SQL，开发秒变DBA
慢 SQL 经常会让应用程序响应变慢，轻者影响用户体验，严重的时候可能会导致服务不可用。如果，每次遇到慢 SQL 都求助于 DBA，一方面效率很低，另一方面也会很没面子。所以，我们一起来看看如何使用AI能力给出超越一般DBA的 SQL 优化建议。NineData（）面向每个开发人员提供了免费的 SQL 开发功能，同时也提供了免费的 AI SQL 优化功能。我们看看，如何使用 NineData 的 AI 功能完成 SQL 优化。
领导催我优化SQL语句，我求助了ChatGPT。这是ChatGPT给出的建议，你们觉得靠谱吗
作为一个程序员，无论在面试还是工作中，优化SQL都是绕不过去的难题。 为啥？工作之后才会明白，随着公司的业务量增多，SQL的执行效率对程系统运行效率的影响逐渐增大，相对于改造代码，优化SQL语句是正本最低的方式，所以部门老大才会经常要求程序员再优化一下SQL代码。 如何优化SQL语句呢？这个问题，从网上搜索的答案是五花八门，总感觉太散抓不住重点。听说ChatGPT挺聪明的，看看它能给出什么答案。
SQL的解析和优化的原理：一条sql 执行过程是什么？
SQL的解析和优化的原理：一条sql 执行过程是什么？
Sql 优化 后感
又谈到这个话题了，这个折磨了我1个来月的事情，唉，公司新做项目的报表全部由我包揽，财务的业务的，共计50多张，有简单的，有困难的，相对简单的就不说了，比较困难的，是指，取数困难，计算困难，查询优化困难，上面那两个，多跟业务沟通沟通还是可以在内部消化的，但是查询优化这个，确实是不行，为啥呢，人家业务同事连数据都查询不到，你拿啥跟人家谈呢， 本公司使用的是报表，就那种直接放SQL语句然后设置+配置的那种，将SQL语句存在到数据库中，前台通过table查询，这个时候，前台查询超过1分钟，页面将查不出来数据，介于财
Java EE

67,541

社区成员

225,857

社区内容

发帖
与我相关
我的任务
社区描述
J2EE只是Java企业应用。我们需要一个跨J2SE/WEB/EJB的微容器，保护我们的业务核心组件（中间件），以延续它的生命力，而不是依赖J2SE/J2EE版本。
社区管理员
  • Java EE
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章