麦田技术博客

在系统中需要进行分页的时候，我们通常会使用Limit加上偏移量的方法实现，问题来了，在偏移量非常大的时候，例如可能是limit 10000,10这样的查询，这时MySQL需要查询10010条记录然后只返回最后的10条，前面10000条记录都被抛弃，造成效率低下。

优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖查询，而不是查询所有的列，然后根据需要做一个关联操作再返回所需的列，对于偏移量很大的时候，这样做的效率会有很大提升

现TEST表中有1000000(百万)条数据添加索引，不然查询速度很慢

ALTER TABLE TEST ADD PRIMARY KEY (id) 

1、offset比较小的时候：

SELECT * FROM test LIMIT 100,10; 

多次运行，时间在0.001左右

SELECT * FROM test WHERE id >= (  
  SELECT id FROM test ORDER BY id LIMIT 100,1  
) LIMIT 10

多次运行，时间也基本在0 ~ 0.001之间。所以，在offset较小的时候，直接使用limit效率会高点！

2、offset比较大的时候

SELECT * FROM test LIMIT 900000,10;

多次运行，时间在0.712左右

SELECT * FROM test WHERE id >= (  
  SELECT id FROM test ORDER BY id LIMIT 900000,1  
) LIMIT 10

多次运行，时间在0.475左右，所以，offset较大时，使用后者效率增高！这是用了id做索引的结果。

除了上面的子查询，也可以使用表连接(INNER JOIN)进行优化

SELECT * FROM test INNER JOIN ( SELECT id FROM test  ORDER BY id  LIMIT 900000,10) t2 USING (id)

效率和子查询差不多

最新文章