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

Question

两张表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 ;
 求优化！！！！！

Answer 1

运行效果如何？report201209表上有什么索引？SQL执行计划如何？

Answer 2

reportDate普通索引
channelID, dataType, reportDate, param 四个做个一个普通索引

Answer 3

AND b.modelID IN 
换成 AND （b.modelID IN （） or b.modelID IN （）） 试试呢

Answer 4

对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最后注意观察执行计划，是否正确使用了联合索引，此外最好能把执行计划贴出来看看。

Answer 5

比原来快了差不多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

Answer 6

用的Index对么？你这里显示是用了： Index_reportDate，是我说的那个复合索引么？对了，你这个是啥数据库？

Answer 7

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

Answer 8

哥们，我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'  -- 复合索引的第一过滤字段

Answer 9

OK。看来你是在生产环境上测试，其实最好是有个测试环境。另外，下次测试建议日期用个范围来试试看，4ms的对比性太弱了。AND a.reportDate >='2012-09-01' AND a.reportDate <='2012-09-10'

Answer 10

explain  sql语句把结果发出来看看

Answer 11

大神严重了，最近一直没有找到合适的机会，怕被逮啊，哈哈。现在好了 拿到数据了。
  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

Answer 12

如果做区间查询， 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，使其可以不用增加新的索引。

Answer 13

基本上不可能，你这个索引的顺序，dataType 在前面；如果是在最后面，还有点希望。因为核心的问题是：GROUP BY reportDate, a.param （显然你的GroupBy是不能换掉的）如果你们已有索引顺序是：channelID, reportDate, param, dataType 可能还可以兼用。但是不推荐随意修改原有索引，因为怕有别的地方恰好需要另一种顺序才能生效。
可以跟你领导探讨下，看怎么做更合适。

Answer 14

效果很多，现在又两张分月表b7、b8，，b8是加了你的索引的。
就上你改造过的sql：b8的执行时间0.089ms，b7是4.164ms，大体就是在这个范围
效果还是很明显的。

Answer 15

修正下，考虑到因为有GroupBy，所以即便你们的索引是这样也不行。GroupBy的字段，必须在索引最前面，而且GroupBy字段顺序跟索引字段顺序必须保持一致。也就是可以这样：reportDate, param, dataType, channelID
但估计这样肯定会对该索引原来的作用发生较大影响，所以还是别考虑重用索引了。