问题描述
我在 LIMIT 使用大偏移量的 mysql SELECT 时遇到性能问题:
I'm getting performance problems when LIMITing a mysql SELECT with a large offset:
SELECT * FROM table LIMIT m, n;
如果偏移量 m 大于 1,000,000，则操作非常慢.
If the offset m is, say, larger than 1,000,000, the operation is very slow.
我必须使用 limit m, n;我不能使用诸如 id > 之类的东西.1,000,000 个限制 n.
I do have to use limit m, n; I can't use something like id > 1,000,000 limit n.
如何优化此语句以获得更好的性能?
How can I optimize this statement for better performance?
推荐答案
也许您可以创建一个索引表，它提供与目标表中的键相关的顺序键.然后，您可以将此索引表连接到目标表，并使用 where 子句更有效地获取所需的行.
Perhaps you could create an indexing table which provides a sequential key relating to the key in your target table. Then you can join this indexing table to your target table and use a where clause to more efficiently get the rows you want.
#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;
这篇关于如何在 LIMIT 子句中使用大偏移量加速 MySQL 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！