首页 ›存档› 技术 › 查看内容

优化MySQL中的分页

2018-3-30 13:00 |来自: 互联网 317 0

摘要: （点击上方公众号，可快速关注）英文：Robert Eisele 译者：Giraffe 链接：http://yemengying.com/2016/05/28/optimized-pagiantion-mysql/ 一道面试的问题，当MySQL表中有数据量很大的时候如何做分页。。。。 ...

（点击上方公众号，可快速关注）

英文：Robert Eisele

译者：Giraffe

链接：http://yemengying.com/2016/05/28/optimized-pagiantion-mysql/

一道面试的问题，当MySQL表中有数据量很大的时候如何做分页。。。。当时只知道在数据量很大的时候可以分表，但不知道不分表时可以怎么做。。。。唉，谁让代理商就那么几条数据，一个简单的limit，offset就完全hold住了(捂脸)。。。

很多应用往往只展示最新或最热门的几条记录，但为了旧记录仍然可访问，所以就需要个分页的导航栏。然而，如何通过MySQL更好的实现分页，始终是比较令人头疼的问题。虽然没有拿来就能用的解决办法，但了解数据库的底层或多或少有助于优化分页查询。

我们先从一个常用但性能很差的查询来看一看。

SELECT *

FROM city

ORDER BY id DESC

LIMIT 0, 15

这个查询耗时0.00sec。So，这个查询有什么问题呢？实际上，这个查询语句和参数都没有问题，因为它用到了下面表的主键，而且只读取15条记录。

CREATE TABLE city (

id int(10) unsigned NOT NULL AUTO_INCREMENT,

city varchar(128) NOT NULL,

PRIMARY KEY (id)

) ENGINE=InnoDB;

真正的问题在于offset(分页偏移量)很大的时候，像下面这样：

SELECT *

FROM city

ORDER BY id DESC

LIMIT 100000, 15;

上面的查询在有2M行记录时需要0.22sec，通过EXPLAIN查看SQL的执行计划可以发现该SQL检索了100015行，但最后只需要15行。大的分页偏移量会增加使用的数据，MySQL会将大量最终不会使用的数据加载到内存中。就算我们假设大部分网站的用户只访问前几页数据，但少量的大的分页偏移量的请求也会对整个系统造成危害。Facebook意识到了这一点，但Facebook并没有为了每秒可以处理更多的请求而去优化数据库，而是将重心放在将请求响应时间的方差变小。

对于分页请求，还有一个信息也很重要，就是总共的记录数。我们可以通过下面的查询很容易的获取总的记录数。

SELECT COUNT(*)

FROM city;

然而，上面的SQL在采用InnoDB为存储引擎时需要耗费9.28sec。一个不正确的优化是采用SQL_CALC_FOUND_ROWS,SQL_CALC_FOUND_ROWS可以在能够在分页查询时事先准备好符合条件的记录数，随后只要执行一句select FOUND_ROWS(); 就能获得总记录数。但是在大多数情况下，查询语句简短并不意味着性能的提高。不幸的是，这种分页查询方式在许多主流框架中都有用到，下面看看这个语句的查询性能。