当前位置：首页 → 问答吧 → 优化问题急

优化问题急

时间：2011-11-09

来源：互联网

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

就是第一步那个应该怎么优化呢

作者: utpcb 发布时间: 2011-11-09

explain

select 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 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

作者: utpcb 发布时间: 2011-11-09

要是把m.c1<(n.c1-100000000) 改为 m.c1=(n.c1-100000000)执行计划一样的只要500毫秒之前的要4秒！这个数据大概300W的我想要的效果是 1秒钟出来有谁有好的方法啊
explain

select 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

作者: utpcb 发布时间: 2011-11-09

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 一下

作者: wwwwb 发布时间: 2011-11-09

单独的我已经运行过超级块400毫秒以内

作者: utpcb 发布时间: 2011-11-09

(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查的话就慢了

作者: utpcb 发布时间: 2011-11-09

show index from ST1401_main;

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

作者: ACMAIN_CHM 发布时间: 2011-11-09

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

作者: wwwwb 发布时间: 2011-11-09

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

作者: Inspect2008 发布时间: 2011-11-09

给这个时间加上索引再看看

作者: Inspect2008 发布时间: 2011-11-09

这个索引创建了没了？

create index xxx on ST1401_main(c5,co,c1)

作者: ACMAIN_CHM 发布时间: 2011-11-09

创建索引后，改成如下试试。
SQL code


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

作者: ACMAIN_CHM 发布时间: 2011-11-09

索引都建好了萨你们说的临时表我直接就已经玩过了撒！就是不行撒　头大撒

作者: utpcb 发布时间: 2011-11-09

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

作者: ACMAIN_CHM 发布时间: 2011-11-09

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秒钟出来有谁有好的方法啊
！

作者: utpcb 发布时间: 2011-11-09

引用 12 楼 utpcb 的回复:
索引都建好了萨你们说的临时表我直接就已经玩过了撒！就是不行撒　头大撒

你的临时表怎么建立的，相应的索引加上没有

ST1401_main：去掉C5、C1的索引

作者: wwwwb 发布时间: 2011-11-09

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

作者: ACMAIN_CHM 发布时间: 2011-11-09

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

作者: zhaoyun0209 发布时间: 2011-11-09

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

作者: utpcb 发布时间: 2011-11-09

索引我可以强制指定的就是那个<变成= 也就快了我要的是一个月前的最大值哦怎么要4秒钟

作者: utpcb 发布时间: 2011-11-09

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

作者: ACMAIN_CHM 发布时间: 2011-11-09

前面贴错了不好意思

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

作者: utpcb 发布时间: 2011-11-09

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

试一下。

作者: ACMAIN_CHM 发布时间: 2011-11-09

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

作者: utpcb 发布时间: 2011-11-09

主要是试一下STRAIGHT_JOIN

作者: ACMAIN_CHM 发布时间: 2011-11-09

也咩用我试过啊

作者: utpcb 发布时间: 2011-11-09