优化问题急

Question

select m.c5 c5,m.co co,max(m.c1) c1 from ST1401_main m join (
select c5,co, CAST(date_add(max(c1),interval -1 month)  AS UNSIGNED) c1 ,max(c1) from ST1401_main 
where c5>=20111231000000 and c5<=20121231000000 group by c5,co  ) n on m.c5=n.c5 and m.co=n.co and
m.c1<n.c1 group by m.c5,m.co
1 PRIMARY <derived2> ALL \N \N \N \N 4686 Using temporary; Using filesort
1 PRIMARY m ref PRIMARY,C1,C5,C5COC1 C5COC1 107 n.c5,n.co 296 Using where; Using index
2 DERIVED ST1401_main range C5,C5COC1 C5COC1 107 \N 6055 Using where; Using index for group-by就是第一步那个应该 怎么优化呢

Answer 1

explainselect m.c5 c5,m.co co,max(m.c1) c1 from ST1401_main m join (select c5,co,max(c1) c1 from ST1401_main 
where c5>=20111231000000 and c5<=20121231000000 group by c5,co) n on m.c5=n.c5 and m.co=n.co and
m.c1<(n.c1-100000000) group by m.c5,m.co1 PRIMARY <derived2> ALL \N \N \N \N 4686 Using temporary; Using filesort
1 PRIMARY m ref PRIMARY,C1,C5,C5COC1 C5COC1 107 n.c5,n.co 296 Using where; Using index
2 DERIVED ST1401_main range C5,C5COC1 C5COC1 107 \N 6055 Using where; Using index for group-by

Answer 2

要是把m.c1<(n.c1-100000000) 改为 m.c1=(n.c1-100000000)执行计划一样的 只要500毫秒 之前的要4秒！ 这个数据大概300W的 我想要的效果是 1秒钟出来 有谁有好的方法啊
 explainselect m.c5 c5,m.co co,max(m.c1) c1 from ST1401_main m join (select c5,co,max(c1) c1 from ST1401_main 
where c5>=20111231000000 and c5<=20121231000000 group by c5,co) n on m.c5=n.c5 and m.co=n.co and
m.c1=(n.c1-100000000) group by m.c5,m.co
1 PRIMARY <derived2> ALL \N \N \N \N 4686 Using temporary; Using filesort
1 PRIMARY m ref PRIMARY,C1,C5,C5COC1 C5COC1 116 n.c5,n.co,func 1 Using where; Using index
2 DERIVED ST1401_main range C5,C5COC1 C5COC1 107 \N 6055 Using where; Using index for group-by

Answer 3

select c5,co, CAST(date_add(max(c1),interval -1 month) AS UNSIGNED) c1 ,max(c1) from ST1401_main  
where c5>=20111231000000 and c5<=20121231000000 group by c5,co
单独运行上述语句，速度如何，EXPLAIN 一下

Answer 4

(4686 row(s) returned)
Execution Time : 00:00:00:015
Transfer Time  : 00:00:00:235
Total Time     : 00:00:00:250该加的索引我已经加过！  但是应该是查出结果 应该放入到一个临时的derived2中间表  然后这这个中间的和ST1401_main查的话就慢了

Answer 5

show index from ST1401_main;贴出来看一下，
另外你的语句想实现的功能是什么？

Answer 6

ST1401_main：c5、co、c1上建立复合索引没有
建立临时表，如LSB，字段c5,co，c1，AA，在c5、co、c1上建立复合索引，
TRUNCATE LSB;
INSERT INTO LSB select c5,co, CAST(date_add(max(c1),interval -1 month) AS UNSIGNED) c1 ,max(c1) from ST1401_main  
where c5>=20111231000000 and c5<=20121231000000 group by c5,co
再运行下述语句试试
select m.c5 c5,m.co co,max(m.c1) c1 from ST1401_main m join LSB n on m.c5=n.c5 and m.co=n.co and
m.c1<n.c1 group by m.c5,m.co

Answer 7

20121231000000 这个存储的是时间吧, 为什么不用unix_time() 用 int 存储？

Answer 8

这个索引创建了没了？create index xxx on ST1401_main(c5,co,c1)

Answer 9

创建索引后，改成如下试试。select m.c5 ,m.co ,max(m.c1)  
from ST1401_main m join (
select c5,co, max(c1) - interval 1 month as c1 
from ST1401_main  
where c5>=20111231000000 and c5<=20121231000000
group by c5,co  
) n 
on m.c5=n.c5 
and m.co=n.co 
and m.c1<n.c1 
group by m.c5,m.co

Answer 10

show index 
show create table 都贴出来以供分析。

Answer 11

ST1401_main CREATE TABLE `ST1401_main` (
  `CV` bigint(20) DEFAULT NULL,
  `CKey` char(32) COLLATE utf8_bin DEFAULT NULL,
  `CO` varchar(32) COLLATE utf8_bin NOT NULL DEFAULT '',
  `C1` bigint(20) DEFAULT NULL,
  `C2` int(10) unsigned DEFAULT NULL,
  `C3` bigint(20) DEFAULT NULL,
  `C4` int(10) unsigned DEFAULT NULL,
  `C5` bigint(20) DEFAULT NULL,
  `C6` double DEFAULT NULL,
  `C7` double DEFAULT NULL,
  `C8` double DEFAULT NULL,
  `C9` double DEFAULT NULL,
  `C10` double DEFAULT NULL,
  `C11` double DEFAULT NULL,
  `C12` double DEFAULT NULL,
  `C13` double DEFAULT NULL,
  `C14` double DEFAULT NULL,
  `C15` double DEFAULT NULL,
  `C16` double DEFAULT NULL,
  `C17` double DEFAULT NULL,
  `C18` double DEFAULT NULL,
  `C19` int(10) unsigned DEFAULT NULL,
  `C20` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`CO`,`C20`),
  KEY `CV` (`CV`),
  KEY `C1` (`C1`),
  KEY `C2` (`C2`),
  KEY `C5` (`C5`),
  KEY `C19` (`C19`),
  KEY `C5COC1` (`C5`,`CO`,`C1`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin
你们有没有注意到要是把m.c1<(n.c1-100000000) 改为 m.c1=(n.c1-100000000)执行计划一样的 只要500毫秒 之前的要4秒！ 这个数据大概300W的 我想要的效果是 1秒钟出来 有谁有好的方法啊
 ！

Answer 12

你的临时表怎么建立的，相应的索引加上没有ST1401_main：去掉C5、C1的索引

Answer 13

你的 show index   呢？怎么没有贴出来？

Answer 14

KEY `CV` (`CV`),
  KEY `C1` (`C1`),
  KEY `C2` (`C2`),
  KEY `C5` (`C5`),
  KEY `C19` (`C19`),
  KEY `C5COC1` (`C5`,`CO`,`C1`)
---------
这几个是他的key吧

Answer 15

 SHOW INDEX FROM  ST41010_main
ST41010_main 0 PRIMARY 1 CO A 1 \N \N BTREE
ST41010_main 0 PRIMARY 2 C10 A 35 \N \N BTREE
ST41010_main 1 CV 1 CV A 35 \N \N YES BTREE
ST41010_main 1 C1 1 C1 A 35 \N \N YES BTREE
ST41010_main 1 C2 1 C2 A 35 \N \N YES BTREE
ST41010_main 1 C9 1 C9 A 2 20 \N YES BTREE

Answer 16

你贴出的INDEX中并没有 `C5COC1` (`C5`,`CO`,`C1`) 的索引啊？ 你贴完整了吗？

Answer 17

前面贴错了不好意思 ST1401_main 0 PRIMARY 1 CO A \N \N \N BTREE
ST1401_main 0 PRIMARY 2 C20 A 3703760 \N \N BTREE
ST1401_main 1 CV 1 CV A 3703760 \N \N YES BTREE
ST1401_main 1 C1 1 C1 A 690 \N \N YES BTREE
ST1401_main 1 C2 1 C2 A 4 \N \N YES BTREE
ST1401_main 1 C5 1 C5 A 7 \N \N YES BTREE
ST1401_main 1 C19 1 C19 A 1 \N \N YES BTREE
ST1401_main 1 C5COC1 1 C5 A 7 \N \N YES BTREE
ST1401_main 1 C5COC1 2 CO A 12512 \N \N BTREE
ST1401_main 1 C5COC1 3 C1 A 3703760 \N \N YES BTREE

Answer 18

select STRAIGHT_JOIN m.c5 ,m.co ,max(m.c1)  
from (
    select c5,co, max(c1) - interval 1 month as c1 
    from ST1401_main  
    where c5>=20111231000000 and c5<=20121231000000
    group by c5,co  
) n inner join ST1401_main m 
on m.c5=n.c5 
and m.co=n.co 
and m.c1<n.c1 
group by m.c5,m.co
试一下。

Answer 19

之前也试过哦还有 CAST(date_add(max(c1),interval -1 month)  AS UNSIGNED)  一定要用这样的 不然类型不对查不出数据！ 

Answer 20

建议提供你的数据，这样别人可以直接在自己机器上进行调试分析。 你可以直接将目录中的`ST1401_main` .* 的三个文件打包上传。可以上传到 http://www.access911.net/csdn

Answer 21

select m.c5 c5,m.co co,max(m.c1) c1 from ST1401_main m join (
select c5,co, CAST(date_add(max(c1),interval -1 month) AS UNSIGNED) c1 ,max(c1) from ST1401_main 
where c5>=20111231000000 and c5<=20121231000000 group by c5,co ) n on m.c5=n.c5 and m.co=n.co and
m.c1<n.c1 group by m.c5,m.co
1 PRIMARY <derived2> ALL \N \N \N \N 4686 Using temporary; Using filesort
1 PRIMARY m ref PRIMARY,C1,C5,C5COC1 C5COC1 107 n.c5,n.co 296 Using where; Using index
2 DERIVED ST1401_main range C5,C5COC1 C5COC1 107 \N 6055 Using where; Using index for group-by就是第一步那个应该 怎么优化呢第一步：
从你的需求看，可以考虑借助索引覆盖的技巧解决，即创建索引：
ALTER TABLE ST1401_main ADD INDEX idx_c5_co_c1(c5,co,c1);第二步：
SET GLOBAL JOIN_BUFFER_SIZE=1024*1024*16;
SET GLOBAL TMP_BUFFER_SIZE=1024*1024*64;
SET GLOBAL HEAP_BUFFER_SIZE=1024*1024*64;SET GLOBAL READ_BUFFER_SIZE=1024*1024*2;
#****************************************#
MySQL技术及运维自动化网：www.mysqlops.com新浪微博账号：http://weibo.com/mysqlops
#****************************************#