简而言之:一个包含超过1600万条记录的表[大小为2GB] . 使用ORDER BY * primary_key时,使用SELECT的LIMIT偏移越高,查询变得越慢
所以
SELECT * FROM large ORDER BY `id` LIMIT 0, 30
远远不及
SELECT * FROM large ORDER BY `id` LIMIT 10000, 30
这也只能订购30条记录 . 所以这不是ORDER BY的开销 .
现在,当获取最新的30行时,大约需要180秒 . 如何优化该简单查询?
5 回答
通常情况下,较高的偏移会减慢查询速度,因为查询需要计算第一个
OFFSET + LIMIT
记录(并且只占其中的LIMIT
) . 该值越高,查询运行的时间越长 .查询无法直接进入
OFFSET
,因为首先,记录的长度可能不同,其次,删除的记录可能存在间隙 . 它需要检查并统计每条记录 .假设
id
是MyISAM
表的PRIMARY KEY
,您可以使用此技巧加快速度:看到这篇文章:
我自己也有同样的问题 . 鉴于您想要收集大量此数据而不是特定的30集,您可能正在运行循环并将偏移量增加30 .
所以你可以做的是:
保存一组数据的最后一个ID(30)(例如lastId = 530)
添加条件
WHERE id > lastId limit 0,30
所以你总是可以有一个ZERO偏移量 . 性能改进会令您惊讶 .
MySQL无法直接转到第10000条记录(或者建议的第80000字节),因为它不能假设它是打包/排序的(或者它具有1到10000的连续值) . 虽然实际上可能是这种方式,但MySQL不能假设没有漏洞/间隙/删除的ID .
因此,正如鲍勃指出的那样,MySQL必须获取10000行(或在
id
上遍历索引的第10000个条目)才能找到返回的30 .EDIT :说明我的观点
请注意,虽然
会很慢(呃),
如果没有丢失
id
(即间隙),则会快速(呃)并返回相同的结果 .两个查询的耗时部分是从表中检索行 . 从逻辑上讲,在
LIMIT 0, 30
版本中,只需要检索30行 . 在LIMIT 10000, 30
版本中,将评估10000行并返回30行 . 可以在我的数据读取过程中进行一些优化,但请考虑以下内容:如果在查询中有WHERE子句怎么办?引擎必须返回所有符合条件的行,然后对数据进行排序,最后得到30行 .
还要考虑在ORDER BY序列中不处理行的情况 . 必须对所有符合条件的行进行排序,以确定要返回的行 .
我发现了一个有趣的例子来优化SELECT查询ORDER BY id LIMIT X,Y . 我有3500万行,所以花了2分钟才能找到一系列行 .
这是诀窍:
只需将WHERE与你获得的最后一个id增加很多性能 . 对我来说这是2分钟到1秒:)
其他有趣的技巧:http://www.iheavy.com/2013/06/19/3-ways-to-optimize-for-paging-in-mysql/
它也适用于字符串