求助 SQL 优化问题

utpcb 2011-09-26 11:06:54

select a.CO CO,C3,C12,C13,a.C1 from (select CO,max(C1) C1 from T1 group by CO) a join T1 b on a.CO=b.CO and a.C1=b.C1
为什么这样语句在MSSQL 很快不到一秒在MYSQL要10几秒呢数据不多菜几万条求解决

...全文

118 13 打赏收藏转发到动态举报

写回复

用AI写文章

13 条回复

切换为时间正序

请发表友善的回复…

发表回复

utpcb 2011-09-26

打赏
举报

1 PRIMARY <derived2> ALL \N \N \N \N 2278
1 PRIMARY b ref PRIMARY,C1 C1 8 a.C1 33 Using where
2 DERIVED ST22015_main ALL \N \N \N \N 39097 Using where; Using temporary; Using filesort

执行计划是这样的

utpcb 2011-09-26

打赏
举报

[Quote=引用 1 楼 wwwwa 的回复:]

select * from T1 a where not exists(select 1 from t1 where a.co=co and a.c1<c1)
在C0、C1上建立复合索引试试
[/Quote]
这个索引我有建立哦

utpcb 2011-09-26

打赏
举报

[Quote=引用 2 楼 acmain_chm 的回复:]

select CO,C3,C12,C13,(select max(C1) from T1 where CO=b.CO) as C1
from T1 b
[/Quote]
你这样就变掉了啊我取的是最大的一条而已你这种是所有的都取出来

rucypli 2011-09-26

打赏
举报

优化方法不一样走的路径就不一样

ACMAIN_CHM 2011-09-26

打赏
举报

select CO,C3,C12,C13,(select max(C1) from T1 where CO=b.CO) as C1
from T1 b

WWWWA 2011-09-26

打赏
举报

select * from T1 a where not exists(select 1 from t1 where a.co=co and a.c1<c1)
在C0、C1上建立复合索引试试

wwwwb 2011-09-26

打赏
举报

检查是否有复合索引C0、C1

ACMAIN_CHM 2011-09-26

打赏
举报

create index xxx on t1 (CO,.C1);

utpcb 2011-09-26

打赏
举报

放心好索引都有的
t1 0 PRIMARY 1 CO A \N \N \N BTREE
t1 0 PRIMARY 2 C21 A 4379108 \N \N BTREE
t1 1 CV 1 CV A \N \N \N YES BTREE
t1 1 C1 1 C1 A \N \N \N YES BTREE
t1 1 C2 1 C2 A \N \N \N YES BTREE
t1 1 C3 1 C3 A \N \N \N YES BTREE
t1 1 C21 1 C21 A \N \N \N BTREE

wwwwb 2011-09-26

打赏
举报

SHOW INDEX FROM tbl_name

utpcb 2011-09-26

打赏
举报

1 PRIMARY b ALL \N \N \N \N 4342708 Using where
2 DEPENDENT SUBQUERY a ref PRIMARY,C1 PRIMARY 98 dzhst2.b.CO 43427 Using where
第二中方法

1 PRIMARY <derived2> ALL \N \N \N \N 2285
1 PRIMARY b ref PRIMARY,C1 C1 9 a.C1 3635 Using where
2 DERIVED t1 ALL \N \N \N \N 4341108 Using temporary

块郁闷死了

wwwwb 2011-09-26

打赏
举报

EXPLAIN select * from T1 a where not exists(select 1 from t1 where a.co=co and a.c1<c1)
EXPLAIN select a.CO CO,C3,C12,C13,a.C1 from (select CO,max(C1) C1 from T1 group by CO) a join T1 b on a.CO=b.CO and a.C1=b.C1

贴结果

ACMAIN_CHM 2011-09-26