我怎样才能加快与大MySQL查询LIMIT子句中的偏移？(How can I speed up a

我得到的性能问题时， LIMIT荷兰国际集团一个MySQL SELECT有大的偏移：

SELECT * FROM table LIMIT m, n;

如果偏移m是，比方说，大于100万，操作非常缓慢。

我确实有使用limit m, n ; 我不能用类似id > 1,000,000 limit n 。

如何优化这一说法有更好的表现？

Answer 1:

也许你可以创建一个索引表，它提供与你的目标表中的键顺序键。 然后你就可以加入这个索引表到目标表，并使用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;

Answer 2:

有一个博客张贴的地方在互联网上你应该如何最好让行的选择 ，显示应尽可能，从而为紧凑：刚才的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语句，或者不提高什么，一线希望，或许值得打破这种单一语句转换成多个语句并捕获到IDS的数据结构。

更新 ：我发现博客文章我说的是：这是杰夫·阿特伍德的“所有的抽象是失败的抽象”的编码恐怖。

Answer 3:

如果记录是大型，缓慢可能加载数据到来。 如果ID列索引，那么就选择它会快很多。 然后，您可以做第二次查询与相应的ID的IN子句（或者可以使用制定从第一个查询的最小和最大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)

Answer 4:

我不认为有任何需要创建一个单独的索引，如果你的表已经有一个。 如果是这样，那么你可以通过这个主键顺序，然后使用密钥的值步：

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/

Answer 5:

保罗·迪克森的答案确实是解决问题的办法，但你必须保持序列表，并确保没有行空白。

如果这是可行的，更好的解决方案是简单地确保原始表没有行的差距，并从ID 1开始然后抓住采用了分页ID行。

SELECT * FROM表阿WHERE ID> = 1个ID <= 1000;
SELECT * FROM表阿WHERE ID> = 1001和ID <= 2000;

等等...

Answer 6:

我最近遇到这个问题。 问题是两个部分来解决。 首先，我不得不使用一个内部的选择在这做了我的限制，并仅在主键抵消了我，我的FROM子句：

$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中OFFSET和限制。 这工作得很好，直到我过去的第100页则偏移开始变得不能忍受缓慢。 改变这种在我的内心查询BETWEEN加快它的任何页面。 我不知道为什么MySQL有没有加快了偏移，但似乎卷轴回之间。

文章来源: How can I speed up a MySQL query with a large offset in the LIMIT clause?