表test有三列(a,b,c),100万条记录。(a,b)上有唯一索引,现在要查找对于每一个a,b的值最大的那条记录。有好几种方法。
1. select * from (select * from test order by b desc) as t group by a;速度最快的,但只有MySQL可以用。 2. select t1.* from test t1 left join test t2 on t1.a = t2.a and t1.b < t2.b where t2.b is null;因为用了联接,查询的行数比较多,所以比较慢。a的选择率越低,速度越慢。 3. select * from test where (a,b) in (select a,max(b) from test group by a);这个慢不是因为表扫描,而是因为对于test的每一行,select a,max(b) from test group by a都会做一遍,这个从profile的结果可以看出来,explain中select_type是DEPENDENT SUBQUERY应该是同样的意思。所以先建一个临时表可以提高速度, select t1.* from test t1 , (select a,max(b) as b from test group by a) as t2 where t1.a = t2.a and t1.b = t2.b这种方法,a的选择性越高则越慢 4. select * from test t1 where not exists (select * from test t2 where t1.a = t2.a and t1.b < t2.b)和2差不多 |
|
来自: wellbeing_wang > 《我的图书馆》