MySQL-如何优化LIMIT,OFFSET进行的分页?

MySQL-如何优化LIMIT,OFFSET进行的分页?

瑾兮 发布于 2017-04-28 字数 254 浏览 1201 回复 7

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

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(7

甜柠檬 2017-09-29 7 楼

记得有人是用主键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)

晚风撩人 2017-09-22 6 楼

要是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)

求正解!

偏爱自由 2017-09-12 5 楼

一般刚开始学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

浮生未歇 2017-09-02 4 楼

说一个其他思路的解决办法:
为这张表建立一张索引表,存取内容表(如: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;

甜柠檬 2017-09-02 3 楼

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

浮生未歇 2017-08-30 2 楼

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

清晨说ぺ晚安 2017-07-30 1 楼

我的做法是当offset值大于数据的一半时,反序
例如:
select count(*) from log
993098

当offset<993098/2的时候, select * from log order by id asc limit offset,1
而offset>993098/2, select * from log order by id desc limit offset,1
然后将结果反向输出