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

0 0

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

使用Limit+Offset结合的方式是很常见的分页办法，但是随着offset值不断的增大，效率会越来越低，据我了解，limit offset 需要从头开始进行扫描，对于limit 10 offset 100000的情况，需要扫描前面的100000条记录，我的理解是否正确？有没有其它更好的方式来进行分页？

mysql php Lamp/Lnmp

12 years, 3 months ago

涛哥！救我！

涛哥！救我！ 12 years, 3 months ago

今天有朋友问起此类语句的优化，我大致给他介绍了下从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。可喜可贺。

answered 12 years, 3 months ago

xuelabi

xuelabi answered 12 years, 3 months ago