如何在limit子句中使用较大偏移量加速MySQL查询?

也许您可以创建一个索引表,它提供一个与目标表中的键相关的顺序键。然后您可以将这个索引表连接到目标表,并使用WHERE子句更有效地获取所需的行。

#create table to store sequences
CREATE TABLE seq (
   seq_no int not null auto_increment,
   id int not null,
   primary key(seq_no),
   unique(id)
);

#create the sequence
TRUNCATE seq;
INSERT INTO seq (id) SELECT id FROM mytable ORDER BY id;

#now get 1000 rows from offset 1000000
SELECT mytable.* 
FROM mytable 
INNER JOIN seq USING(id)
WHERE seq.seq_no BETWEEN 1000000 AND 1000999;

在互联网上的某个地方有一篇关于如何最好地 行的选择 要显示的内容应该尽可能紧凑,因此:只有ID;生成完整的结果应该依次获取所需的所有数据。 仅用于选定的行 .

因此,SQL可能是这样的(未经测试,我不确定它是否真的会有任何好处):

select A.* from table A 
  inner join (select id from table order by whatever limit m, n) B
  on A.id = B.id
order by A.whatever

如果您的SQL引擎太原始,不允许使用这种类型的SQL语句,或者它没有改进任何东西,那么将这个语句分解为多个语句并将ID捕获到数据结构中可能是值得的。

更新 :我找到了我说的博客帖子:是杰夫·阿特伍德的。 "All Abstractions Are Failed Abstractions" 关于编码恐怖。

如果记录很大,则加载数据的速度可能较慢。如果对ID列进行了索引,那么只选择它将更快。然后,您可以使用IN子句对适当的ID执行第二个查询(或者可以使用第一个查询中的MIN和MAX ID来构造一个WHERE子句)。

慢:

SELECT * FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

快速:

SELECT id FROM table ORDER BY id DESC LIMIT 10 OFFSET 50000

SELECT * FROM table WHERE id IN (1,2,3...10)

如果您的表已经有了索引,我认为不需要创建单独的索引。如果是这样,则可以按此主键排序,然后使用该键的值单步执行:

SELECT * FROM myBigTable WHERE id > :OFFSET ORDER BY id ASC;

另一个优化将不是使用select*而是仅使用id,这样它就可以简单地读取索引,而不必再定位所有数据(减少IO开销)。如果您需要其他列中的一些,那么也许您可以将它们添加到索引中,以便使用主键读取它们(主键很可能保存在内存中,因此不需要进行光盘查找),尽管这不适用于所有情况,因此您必须进行播放。

我写了一篇更详细的文章:

http://www.4pmp.com/2010/02/scalable-mysql-avoid-offset-for-large-tables/

PaulDixon的答案确实是解决这个问题的一个解决方案,但是您必须维护序列表并确保没有行间隙。

如果这是可行的,一个更好的解决方案就是简单地确保原始表没有行间隙,并且从ID 1开始。然后使用ID获取行进行分页。

从表A中选择*,其中id>=1,id<=1000;
从表A中选择*,其中id>=1001,id<=2000;

等等…

我最近碰到这个问题。问题有两个部分需要解决。首先,我必须在FROM子句中使用一个内部select,它只对主键进行了限制和补偿:

$subQuery = DB::raw("( SELECT id FROM titles WHERE id BETWEEN {$startId} AND {$endId}  ORDER BY title ) as t");  

然后我可以使用它作为我查询的一部分:

'titles.id',
                            'title_eisbns_concat.eisbns_concat', 
                            'titles.pub_symbol', 
                            'titles.title', 
                            'titles.subtitle', 
                            'titles.contributor1', 
                            'titles.publisher', 
                            'titles.epub_date', 
                            'titles.ebook_price', 
                            'publisher_licenses.id as pub_license_id', 
                            'license_types.shortname',
                            $coversQuery
                        )
                        ->from($subQuery)
                        ->leftJoin('titles',  't.id',  '=', 'titles.id')
                        ->leftJoin('organizations', 'organizations.symbol', '=', 'titles.pub_symbol') 
                        ->leftJoin('title_eisbns_concat', 'titles.id', '=', 'title_eisbns_concat.title_id') 
                        ->leftJoin('publisher_licenses', 'publisher_licenses.org_id', '=', 'organizations.id') 
                        ->leftJoin('license_types', 'license_types.id', '=', 'publisher_licenses.license_type_id')

第一次创建这个查询时,我在MySQL中使用了偏移量和限制。这一切都很顺利,直到我通过第100页,然后偏移开始变得难以忍受的缓慢。在我的内部查询中将其更改为between可以加快任何页面的速度。我不知道为什么MySQL没有加快偏移速度,但在这两者之间似乎又卷土重来了。