【转】如何优化LIMIT,OFFSET进行的分页？

6个答案

票 数 

• 梅晓华

  16 票

• 

  191

• 最佳答案

今天有朋友问起此类语句的优化，我大致给他介绍了下从SQL角度做简单的优化，至于应用程序方面咱暂时不考虑。
下面我来举一个简单的例子。
我的测试系统为标配DELL D630， XP系统。

考虑如下表结构：

  
  Table          Create Table                                                    
  
    -------------     ----------------------------------------------------------------
  
t_page_sample  CREATE TABLE `t_page_sample` (                                  
  
`id` int(10) unsigned NOT NULL,                               
  
`v_state` tinyint(1) NOT NULL DEFAULT '1',                    
  
`log_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',  
  
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,   
  
PRIMARY KEY (`id`)                                            
  
) ENGINE=MyISAM DEFAULT CHARSET=utf8
  

示例表的记录数：

  
  select count(*) from t_page_sample;
  

query result(1 records) count(*)
993098

下面我们来一步一步看看下面的这条语句：

  
  explain select sql_no_cache * from t_page_sample order by id asc limit 900001,20;
  

query result(1 records) id

  
  select_type      table       type    possible_keys    key     key_len     ref      rows     Extra 1         
  
SIMPLE       t_page_sample    ALL       (NULL)       (NULL)    (NULL)    (NULL)    993098   Using filesort
  

从上面可以看出，没有用到任何索引，扫描的行数为993098,而且用到了排序！

  
  select sql_no_cache * from t_page_sample order by id asc limit 900001,20;
  

(20 row(s)returned) (4688 ms taken)

那么我们怎么优化这条语句呢？
首先，我们想到的是索引。 在这条语句中，只有ID可能能用到索引，那么我们给优化器加一个暗示条件，让它用到索引。

  
  select sql_no_cache * from t_page_sample force index (primary) order by id asc limit 900001,20;
  

(20 row(s)returned) (9239 ms taken)

没想到用的时间竟然比不加索引还长。 看来这条路好像走不通了。
我们尝试着变化下语句如下：

  
  select * from t_page_sample
  
where id between
  
(select sql_no_cache id from t_page_sample order by id asc limit 900001,1)
  
and
  
(select sql_no_cache id from t_page_sample order by id asc limit 900020,1);
  

(20 row(s)returned) (625 ms taken)

哇，这个很不错，足足缩短了将近15倍！
那么还有优化的空间吗？
我们再次变化语句：

  
  select * from t_page_sample
  
where id >= ( select sql_no_cache id from t_page_sample order by id asc limit 900001,1)
  
limit 20;
  

(20 row(s)returned) (406 ms taken)

时间上又比上次的语句缩短了1/3。可喜可贺。

冯义军

13.03K

编辑于 2012-03-09

评论 (6) • 链接 • 2011-09-07

• 0
  非常感谢，给出了分页思路。 – fengming 2012-07-04
• 0
  select * from t where id>=(select * from t where ... limit START,1) limit LIMIT;学会了，多谢。但是为什么呢？不太理解。我不详细mysql会对不需要的那么多行的所有内容都扫描。 – MGOrange 2012-07-10
• 0
  最后一种方式，外层查询没用排序 – riverlet 2012-09-22
• 0
  后面两个查询，结果和前面的可能都不一样。
  625 ms那个，可能中间id号不是连续的，不一定是20个。
  存储顺序和id顺序可能不一致，外层都没排序的话，结果可能不同。 – yanyufei 1小时之前

显示更多隐藏的评论

• 小白

  4 票

• 

  1661

记得有人是用主键ID做偏移查找，但这种方法得保证ID的连贯完整

这个是使用了覆盖索引,见extra列:using index,不需要扫描表,只扫描索引就得到了id,所以非常快,
如果不能确定位置的情况下,可以这样:
select t.* from t_page_sample t
inner join(
select id from t_page_sample where order by id asc limit 900000,20)
) as lim using(id)

allen

982

编辑于 2012-03-09

评论 (2) • 链接 • 2011-08-30

• 0
  能给个相信的例子吗？或者给个链接参考一下？除了这种方式还有其它的方法吗？ – 小飞 2011-08-30
• 1
  @小飞 这里有个例子，我就不转了http://www.fuchaoqun.com/2009/04/efficient-pagination-using-mysql/ – 小白 2011-08-30

• 令狐葱

  3 票

• 

  4688

一般刚开始学SQL的时候，会这样写

  
  SELECT * FROM table ORDER BY id LIMIT 1000, 10;
  

但在数据达到百万级的时候，这样写会慢死

  
  SELECT * FROM table ORDER BY id LIMIT 1000000, 10;
  

也许耗费几十秒

网上很多优化的方法是这样的

  
  SELECT * FROM table WHERE id >= (SELECT id FROM table LIMIT 1000000, 1) LIMIT 10;
  

是的，速度提升到0.x秒了，看样子还行了
可是，还不是完美的！

以下这句才是完美的！

  
  SELECT * FROM table WHERE id BETWEEN 1000000 AND 1000010;
  

比上面那句，还要再快5至10倍

另外，如果需要查询 id 不是连续的一段，最佳的方法就是先找出 id ，然后用 in 查询

  
  SELECT * FROM table WHERE id IN(10000, 100000, 1000000...);
  

再分享一点
查询字段一较长字符串的时候，表设计时要为该字段多加一个字段,如，存储网址的字段
查询的时候，不要直接查询字符串，效率低下，应该查诡该字串的crc32或md5

评论 (0) • 链接 • 2012-08-31

• 李水祥

  1 票

• 

  1982

要是id加了索引的话。结果不是这样的呀！

  
  SELECT SQL_NO_CACHE id FROM video_normal WHERE 1=1 ORDER BY id DESC LIMIT 937600,20
  
(20 row(s)returned)
  
(562 ms taken)
  
SELECT id FROM video_normal WHERE id BETWEEN 
  
(SELECT SQL_NO_CACHE id FROM video_normal ORDER BY id DESC LIMIT 937620,1)
  
AND
  
(SELECT SQL_NO_CACHE id FROM video_normal ORDER BY id DESC LIMIT 937600,1)
  
(21 row(s)returned)
  
(1077 ms taken)
  

求正解！

小飞

6751

编辑于 2012-06-27

评论 (0) • 链接 • 2011-09-07

• 龙觉忠

  1 票

• 

  3249

说一个其他思路的解决办法：
为这张表建立一张索引表，存取内容表(如:mytable)的id

  
  CREATE TABLE seq (
     seq_no int not null auto_increment,
  
   id int not null,
  
   primary key(seq_no),
  
   unique(id)
  
);
  

  
TRUNCATE seq;
  
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;
  

  
SELECT mytable.* 
  
FROM mytable 
  
INNER JOIN seq USING(id)
  
WHERE seq.seq_no BETWEEN 100 AND 200;
  

评论 (0) • 链接 • 2012-08-07

• abbish

  1 票

• 

  61

我目前的做法是在数据库前增加了solr专门对数据做索引，所有的查询，分页，搜索都交给更专业干这事的solr处理，得到主键后用in去数据库拿资料或者配合orm拿对象

评论 (0) • 链接 • 7小时之前

摘自：http://www.dewen.org/q/20/