CREATE TABLE `table` (
`id` bigint(20) NOT NULL auto_increment,
'type' int(11),
'update' date
）
查询按type分组后，每组的最新20条数据
20 个解决方案
#1
这个我已经有2个实现了的sql了，但是效率太差，数据库里10w条数据，跑了400多ms
所以，希望找到其他效率较高的sql
所以，希望找到其他效率较高的sql
#2
select *
from `table` a
where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
#3
创建一下索引
create index idx_table_test on `table` ('type','update')
create index idx_table_test on `table` ('type','update')
#4
这个的效率是最低的，我得mysql直接卡在那里不动了
#5
建过了，已经，效率最高的是这个
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
这个花了400多ms
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
这个花了400多ms
#6
#2楼 的时间复杂度是 O^2
而你 #5楼 的时间复杂度是 n*O, 所以#5楼 的显然快。 但前提是你需要事先知道distinct type 的 ，这样就不是通过一条SQL语句来实现。 你可以在你的程序中或者在存储过程中来通过分步来实现。
而你 #5楼 的时间复杂度是 n*O, 所以#5楼 的显然快。 但前提是你需要事先知道distinct type 的 ，这样就不是通过一条SQL语句来实现。 你可以在你的程序中或者在存储过程中来通过分步来实现。
#7
type是已知的，而且就是固定10个type，这两个sql我都跑了，10w条数据，时间就是5#的快些，版主救救我呀，小女子感激不尽~~
#8
select * from table where type = 1 order by update desc limit 20
这个花了多久？ 40ms ?
explain select * from table where type = 1 order by update desc limit 20;
贴出来看一下结果是什么？
这个花了多久？ 40ms ?
explain select * from table where type = 1 order by update desc limit 20;
贴出来看一下结果是什么？
#9
你是指查一个类别的吗？
#10
47ms
#11
select * from table where type = 1 order by update desc limit 20
这个已经需要 47ms 了，你查10个自然需要 400 ms 左右。
如果还想看看有没有继续优化的可能，则需要看一下你的
explain select * from table where type = 1 order by update desc limit 20;
的结果
这个已经需要 47ms 了，你查10个自然需要 400 ms 左右。
如果还想看看有没有继续优化的可能，则需要看一下你的
explain select * from table where type = 1 order by update desc limit 20;
的结果
#12
id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
#13
Condition pushdown capability is not used by default. To enable it, you can start mysqld with the --engine-condition-pushdown option, or you can execute either of the following statements at runtime:
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;
前提是你把type和update索引去掉
如此设置
试试看
但是在5.1版本的mysql 只支持DNBCLUSTER引擎
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;
前提是你把type和update索引去掉
如此设置
试试看
但是在5.1版本的mysql 只支持DNBCLUSTER引擎
#14
id|select_type|table |type|possible_keys|key |key_len|ref |rows |Extra
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
有 Using filesort ?
show index from `table` 看一下，另外你的这个 explain 是 explain select * from table where type = 1 order by update desc limit 20; 吗？好象并不一样啊！
#15
感谢13楼，我得mysql 版本是5.0的，而且我用的是InnoDB引擎
#16
就是这样子的，帖子里发的，只是个例子表，但是，我用的表，也是这样的，很简单的表，只是数据多，
Key_name Seq_in_index Column_name
-------- ------ ------ ---------- -------
PRIMARY 1 id
a_t 1 active_time
active_h_type 1 active_type
Key_name Seq_in_index Column_name
-------- ------ ------ ---------- -------
PRIMARY 1 id
a_t 1 active_time
active_h_type 1 active_type
#17
你说过这个索引已经建了，不过从你的show index 上看不来出啊！
建议楼主尽可能提供真实的信息，否则别人很难仅靠猜测或不准确的信息来分析。
#18
哦，呵呵，我这个 active_time 就是对应 update，active_type 对应 type
谢谢，版主辛苦了~
我又加了个限制条件，让他少差了些数据，就快了，100多ms搞定
谢谢，版主辛苦了~
我又加了个限制条件，让他少差了些数据，就快了，100多ms搞定
#19
create index idx_table_test on `table` ('type','update')
你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t 1 active_time
active_h_type 1 active_type
你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t 1 active_time
active_h_type 1 active_type
#20
为了，表示谢意，特贴出其他几种方案以供参考，再次表示感谢
方案 1：（于版主同）
select * from `table` a where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
方案 2：
select * ,rank from (
select b.*,@rownum:=@rownum+1 ,
if(@btype=b.ype,@rank:=@rank+1,@rank:=1) as rank,
@btype:=b.type
from (
select * from table order by type asc, update desc)
b,(select @rownum :=0 ,@btype:=null,@rank:=0)a ) result where rank<20
方案3：
就是我在15楼说的
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
方案3 的限制是要知道type ，而且type的数量较少，而表中的纪录数较多
方案 1：（于版主同）
select * from `table` a where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
方案 2：
select * ,rank from (
select b.*,@rownum:=@rownum+1 ,
if(@btype=b.ype,@rank:=@rank+1,@rank:=1) as rank,
@btype:=b.type
from (
select * from table order by type asc, update desc)
b,(select @rownum :=0 ,@btype:=null,@rank:=0)a ) result where rank<20
方案3：
就是我在15楼说的
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
方案3 的限制是要知道type ，而且type的数量较少，而表中的纪录数较多
#21
#1
这个我已经有2个实现了的sql了，但是效率太差，数据库里10w条数据，跑了400多ms
所以，希望找到其他效率较高的sql
所以，希望找到其他效率较高的sql
#2
select *
from `table` a
where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
#3
创建一下索引
create index idx_table_test on `table` ('type','update')
create index idx_table_test on `table` ('type','update')
#4
这个的效率是最低的，我得mysql直接卡在那里不动了
#5
建过了，已经，效率最高的是这个
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
这个花了400多ms
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
这个花了400多ms
#6
#2楼 的时间复杂度是 O^2
而你 #5楼 的时间复杂度是 n*O, 所以#5楼 的显然快。 但前提是你需要事先知道distinct type 的 ，这样就不是通过一条SQL语句来实现。 你可以在你的程序中或者在存储过程中来通过分步来实现。
而你 #5楼 的时间复杂度是 n*O, 所以#5楼 的显然快。 但前提是你需要事先知道distinct type 的 ，这样就不是通过一条SQL语句来实现。 你可以在你的程序中或者在存储过程中来通过分步来实现。
#7
type是已知的，而且就是固定10个type，这两个sql我都跑了，10w条数据，时间就是5#的快些，版主救救我呀，小女子感激不尽~~
#8
select * from table where type = 1 order by update desc limit 20
这个花了多久？ 40ms ?
explain select * from table where type = 1 order by update desc limit 20;
贴出来看一下结果是什么？
这个花了多久？ 40ms ?
explain select * from table where type = 1 order by update desc limit 20;
贴出来看一下结果是什么？
#9
你是指查一个类别的吗？
#10
47ms
#11
select * from table where type = 1 order by update desc limit 20
这个已经需要 47ms 了，你查10个自然需要 400 ms 左右。
如果还想看看有没有继续优化的可能，则需要看一下你的
explain select * from table where type = 1 order by update desc limit 20;
的结果
这个已经需要 47ms 了，你查10个自然需要 400 ms 左右。
如果还想看看有没有继续优化的可能，则需要看一下你的
explain select * from table where type = 1 order by update desc limit 20;
的结果
#12
id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
#13
Condition pushdown capability is not used by default. To enable it, you can start mysqld with the --engine-condition-pushdown option, or you can execute either of the following statements at runtime:
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;
前提是你把type和update索引去掉
如此设置
试试看
但是在5.1版本的mysql 只支持DNBCLUSTER引擎
SET engine_condition_pushdown=ON;
SET engine_condition_pushdown=1;
前提是你把type和update索引去掉
如此设置
试试看
但是在5.1版本的mysql 只支持DNBCLUSTER引擎
#14
id|select_type|table |type|possible_keys|key |key_len|ref |rows |Extra
1 |SIMPLE |active_h |ref |active_h_type| active_h_type |5 |const |17616 |Using where; Using filesort
有 Using filesort ?
show index from `table` 看一下，另外你的这个 explain 是 explain select * from table where type = 1 order by update desc limit 20; 吗？好象并不一样啊！
#15
感谢13楼，我得mysql 版本是5.0的，而且我用的是InnoDB引擎
#16
就是这样子的，帖子里发的，只是个例子表，但是，我用的表，也是这样的，很简单的表，只是数据多，
Key_name Seq_in_index Column_name
-------- ------ ------ ---------- -------
PRIMARY 1 id
a_t 1 active_time
active_h_type 1 active_type
Key_name Seq_in_index Column_name
-------- ------ ------ ---------- -------
PRIMARY 1 id
a_t 1 active_time
active_h_type 1 active_type
#17
你说过这个索引已经建了，不过从你的show index 上看不来出啊！
建议楼主尽可能提供真实的信息，否则别人很难仅靠猜测或不准确的信息来分析。
#18
哦，呵呵，我这个 active_time 就是对应 update，active_type 对应 type
谢谢，版主辛苦了~
我又加了个限制条件，让他少差了些数据，就快了，100多ms搞定
谢谢，版主辛苦了~
我又加了个限制条件，让他少差了些数据，就快了，100多ms搞定
#19
create index idx_table_test on `table` ('type','update')
你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t 1 active_time
active_h_type 1 active_type
你的索引中根本没有这个复合索引，只有两个单独的索引！
a_t 1 active_time
active_h_type 1 active_type
#20
为了，表示谢意，特贴出其他几种方案以供参考，再次表示感谢
方案 1：（于版主同）
select * from `table` a where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
方案 2：
select * ,rank from (
select b.*,@rownum:=@rownum+1 ,
if(@btype=b.ype,@rank:=@rank+1,@rank:=1) as rank,
@btype:=b.type
from (
select * from table order by type asc, update desc)
b,(select @rownum :=0 ,@btype:=null,@rank:=0)a ) result where rank<20
方案3：
就是我在15楼说的
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
方案3 的限制是要知道type ，而且type的数量较少，而表中的纪录数较多
方案 1：（于版主同）
select * from `table` a where 20> (select count(*) from `table` where 'type'=a.'type' and 'update'>a.'update')
方案 2：
select * ,rank from (
select b.*,@rownum:=@rownum+1 ,
if(@btype=b.ype,@rank:=@rank+1,@rank:=1) as rank,
@btype:=b.type
from (
select * from table order by type asc, update desc)
b,(select @rownum :=0 ,@btype:=null,@rank:=0)a ) result where rank<20
方案3：
就是我在15楼说的
(select * from table where type = 1 order by update desc limit 20)
union all
(select * from table where type = 2 order by update desc limit 20)
union all
(select * from table where type = 3 order by update desc limit 20)
方案3 的限制是要知道type ，而且type的数量较少，而表中的纪录数较多