MySQL数据库代码语句优化实践---LIMIT语句的分页查询场景

一、分页查询的优化需求背景

  在互联网应用中，信息展示功能是个基本需求。在绝大多数应用中是必需的，并且需要多个展示页面。这些展示页面大多数都需要分页显示功能，这涉及到数据库的相关操作就是“分页查询”。可以看出分页查询这个应用场景是很普遍并且是十分高频的，页面的每次刷新都要进行一次分页查询。

  既然“分页查询”使用频次这么高，那么分页查询SQL语句的查询性能对MySQL数据库的性能影响就十分重要，尤其是对数据库并发性能的影响尤为关键。虽然利用页面缓存和数据缓存可以一定程度上提高性能，但是在数据更新和查询条件变化后还是要通过数据库查询。所以深度优化分页查询的SQL语句就十分必要，优化后对整个系统的性能提升将有巨大改变。

  下面我们以常见的文章列表页（适合各类信息列表页）为例，再现我的优化过程。在文章列表页的需求中，筛选通常是分全部、某个分类、某个作者（更多的也是同样原理就不赘述了）等，排序通常就是发布时间（或更新时间）。这个是最普遍的需求，我们就以这个需求为案例应用场景，实践SQL语句“LIMIT”的相关优化。

二、实验数据表结构及相关信息

实验主机配置：

  处理器：Intel Pentium G4560 @ 3.50GHz
  内存：4G DDR4
  硬盘：120G SSD
  系统：Windows7 旗舰版 64位

实验软件相关：

  MySQL版本：5.5.56
  MySQL配置（默认）：
    key_buffer_size = 16M
    max_allowed_packet = 1M
    table_open_cache = 256
    sort_buffer_size = 512K
    net_buffer_length = 8K
    read_buffer_size = 1M
    read_rnd_buffer_size = 4M
    myisam_sort_buffer_size = 32M
  操作软件：phpMyAdmin

实验数据表：

-- ----------------------------
-- Table structure for article
-- ----------------------------
DROP TABLE IF EXISTS `article`;
CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `class_id` int(11) NOT NULL COMMENT '文章分类ID',
  `author_id` int(11) NOT NULL COMMENT '作者ID',
  `up_time` int(11) NOT NULL COMMENT '更新时间',
  `title` varchar(60) NOT NULL COMMENT '文章标题',
  `subtitle` varchar(100) NOT NULL COMMENT '文章副标题',
  `outline` text NOT NULL COMMENT '文章概要',
  `content` text NOT NULL COMMENT '文章内容',
  PRIMARY KEY (`id`),
  KEY `class_id` (`class_id`,`author_id`,`up_time`)
  KEY `up_time` (`up_time`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='文章记录表';

  插入记录：一千万条

三、认识 LIMIT 偏移量参数对性能的影响

  分页查询中，LIMIT的使用是再平常不过了，但是LIMIT的参数确有两种情况，一种是不带偏移量，一种是带偏移量。下面我们以获取所有文章（不分类）的若干页为例，来看一下不同参数的SQL语句的性能变化（我们需要每页显示20条）。

1、取整个文章记录表中的前20条（省略偏移量参数）

SELECT * FROM `article` LIMIT 20

执行结果如下图（查询花费 0.0010秒）：

2、在文章记录表中，从首条开始，向后取20条

SELECT * FROM `article` LIMIT 0,20

执行结果如下图（查询花费 0.0010秒，去前面不带偏移量参数的花费时间相同）：

3、在文章记录表中，在10000条位置，向后取20条

SELECT * FROM `article` LIMIT 10000,20

执行结果如下图（查询花费 0.0140秒，较之前有增加）：

4、在文章记录表中，在5000000条位置，向后取20条

SELECT * FROM `article` LIMIT 5000000,20

执行结果如下图（查询花费 4.1970秒，较之前有增加）：

5、在文章记录表中，在9999980条位置，向后取20条

SELECT * FROM `article` LIMIT 9999980,20

执行结果如下图（查询花费 8.6050秒，较之前有增加）：

5、LIMIT偏移量实践小结

  通过上面的实践测试，可以看出，随着LIMIT偏移量参数的增大，查询花费时间会随之变长。当偏移量位置到一定数量级，查询花费时间就会十分慢，严重影响数据库性能，尤其是并发性能。如果有这种情况发生，单纯去扩充数据库服务器资源已经不能很好解决问题了。因为单用户体验扩充服务器也还是慢，多用户并发时若不优化语句，会大量耗费服务器资源，系统成本会浪费很多，甚至到企业不可承受的程度。

四、利用索引定位方式去掉LIMIT的偏移量参数

  上述测试之所以LIMIT偏移量参数影响数据库性能，是因为LIMIT使用偏移量参数后，就会以遍历的方式数过偏离量参数所描述的那么多行数据，这就利用不上索引，所以性能很低。下面我们给上述语句加上 WHERE 条件，利用索引字段进行定位，看效果如何。

1、在文章记录表中，在id为10000的位置，向后取20条

SELECT * FROM `article` WHERE id > 10000 LIMIT 20

执行结果如下图：

  查询花费 0.0010秒，相比使用LIMIT偏移量的 0.0140秒，时间明显缩短，与偏移量为0时消耗时间相同。

2、在文章记录表中，在id为5000000的位置，向后取20条

SELECT * FROM `article` WHERE id > 5000000 LIMIT 20

执行结果如下图：

  查询花费 0.0010秒，比使用LIMIT偏移量的 4.1970秒时间缩短太多了，与偏移量为0时消耗时间相同。

3、在文章记录表中，在id为9999980的位置，向后取20条

SELECT * FROM `article` WHERE id > 9999980 LIMIT 20

执行结果如下图：

  查询花费 0.0010秒，比使用LIMIT偏移量的 8.6050秒时间缩短太多了，与偏移量为0时消耗时间相同。

4、先使用索引定位，再使用LIMIT不带偏移量方式实践小结

  由于使用了 “WHERE id > xxxx” 条件，id 字段是主键索引，所以不需要全表遍历，通过索引可以快速定位到起始位置，然后通过 “LIMIT 20” 向后依次取出20条，花费时间就不会出现最开始那样随着起始位置变大，查询花费时间会明显变长的情况了。

  通过刚刚的测试我们还可以看到，起始位置的变化，花费时间几乎都没变化，都是与查询表最前面20条的时间是一样的。所以，这个方法是十分高效的，极大的提升了数据库性能，尤其是并发性能。

五、如何获得索引定位值

1、上一页与下一页数据

  上面的测试都是正序情况下的（以主键id正序排列），本页数据的 “底部id值” 就是下一页的索引定位值。假设当前页结果集的底部id值为9999960，那么获取下一页数据就可以使用下面的语句：

SELECT * FROM `article` WHERE id > 9999960 LIMIT 20

  这个语句直接得到了id值比当前页最后一个（9999960）大的正序排列的20条数据，这20条数据经过正序排列是紧挨着的，即使中间有id断号（数据删除或数据过滤）也不会影响取出20条这个数量。

  上一页数据的索引定位值，如果id保证是连续的，并没有过滤条件的情况下，本页数据的 “顶部id值-20(每页条数)-1” 就是上一页的索引定位值。但实际应用中，这总情况很少，如果有过删除或经过过滤的数据，id将不是连续的，那么用这个公式计算出来的就不准确了。其实我们可以依据 “顶部id值” 先倒序查询，再取结果集的 “底部id值”就是上一页数据的正序查询索引定位值，看下面的语句（假设当前页顶部 id值为9999941）：

SELECT id FROM `article` WHERE id < 9999941 ORDER BY id DESC LIMIT 21

  我们是为了找到该使用的id值，所以返回结果不要使用 *，使用 id，这样会是语句执行更快速。y因为最后的正序查询的条件是大于，所以倒序反向找定位id的时候，要在每页条数上加1，所以使用21这个数据。
  这个语句的执行结果得到了 “9999940…9999920” 结果集，底部id的 9999920 就刚好是正序查询使用的上一页索引定位值了。因为这个查询同样是使用的索引定位，所以花费时间显示仍然是 0.0010秒，非常快。那直接得到这个 9999920 该使用什么样的语句，看下面语句：

SELECT id
FROM (SELECT id FROM `article` WHERE id < 9999941 ORDER BY id DESC LIMIT 21) AS article_res 
ORDER BY id 
LIMIT 1

  上面语句使用了子查询，最终返回数据是81，花费时间仍然在0.0010秒内，既然这么高效，我们就继续组SQL语句，以得到完整的上一页数据：

SELECT * FROM `article`
WHERE id >
(SELECT id
FROM (SELECT id FROM `article` WHERE id < 9999941 ORDER BY id DESC LIMIT 21) AS article_res 
ORDER BY id 
LIMIT 1)
LIMIT 20

这条语句的执行结果截图如下：

  其实，在实际应用中，我们还可以利用逻辑代码的缓存，保存上一页的索引定位ID值，如果缓存中存在这个id，就直接使用，可以很大程度避免使用子查询。根据具体情况大家可以灵活运用。

  以上是正序查询的情况，如果需求是倒序查询，修改相关的值和条件及排序语句即可，这里不再赘述。

2、直接跳转到某一页

  假定每页需要显示20条数据，想直接查询第 499995 页的数据，该如何确定索引定位值呢？

  主键id（排序id）连续
  这种情况最简单，可以直接使用 499995*20 来计算。但局限是必须是排序id是连续的，也就是排序使用的id与条数是对应的，否则会不准确。

  主键id（排序id）不连续
  这是最常见的需求应用场景，如果要通过SQL语句得到 499995 页的定位索引就没那么轻松了。我们先使用普通的LIMIT用法获取一下数据，看花费时间是多少（499995*20=9999900）：

SELECT * FROM `article` LIMIT 9999900,20

  这条语句的执行结果（花费时间：8.5850秒）：

  下面我们使用铺盖索引的方式先获取定位id值，看是否可以缩短花费时间，先看下面的语句：

SELECT id FROM `article` LIMIT 9999900,1

  这条语句的执行结果（花费时间：1.2330秒，时间明显缩短好几倍）：

  既然效果明显，我们就继续组语句，取出实际要的20条数据，看效果如何，请看下面的语句：

SELECT * FROM `article`
WHERE id > (SELECT id FROM `article` LIMIT 9999900,1)
LIMIT 20

  这条语句的执行结果（花费时间：1.2350秒）：

  这个结果也是优化成功的，虽然没有上一页和下一页优化的那么惊人，但是相比8.5850秒，优化后的1.2350秒也是很显著的。主要花费时间消耗在获取索引定位值的LIMIT带偏移量的语句上。

3、利用缓存或新表记录索引定位值

  根据上面的测试结果，建议应用中除非特殊要求，否则尽量不要使用直接跳转到某一页这个功能。如果需要提供这个功能，除了刚才的优化方案，还可以使用逻辑代码的缓存，保存文章记录表所有分页的索引定位值。缓存的数据持久化在一个分页索引定位表中（也可以直接使用这个表，不用缓存），在增、删数据时更新分页索引定位表和缓存。这种方案速度最快，缺点是要使用缓存和多建一个分页索引定位表。
  下面是分页索引定位表的结构：

-- ----------------------------
-- Table structure for index_locate
-- ----------------------------
DROP TABLE IF EXISTS `index_locate`;
CREATE TABLE `index_locate` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID，也是页码',
  `first_locate` int(11) NOT NULL COMMENT '列表页第一条索引定位值',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='列表页索引定位值记录表';

  这张表里记录的每一页的第一条记录的索引定位值减去1的值（因为我们在查询数据的时候是大于或小于，所以要减去1），在新增文章记录或删除文章记录的时候同时更新这张表（增加与删除相比浏览查询，是十分低频的，所以多更新了这张表，对整体性能还是大大提升的）。

  有了这张表，我们就可以使用下面的语句快速查询到某一页的数据（假设要查询499999页的20条数据）：

SELECT * FROM `article`
WHERE id > (SELECT first_locate FROM `index_locate` WHERE id=499999)
LIMIT 20

  这条语句的执行结果（花费时间：0.0010秒）：

  可以看出这个优化效果十分理想。如果使用了分页索引定位表，上一页与下一页也可以使用这个方案，大家可以根据自己的实际情况，酌情选择使用那种方案。

六、经过分类过滤后排序的情况

  前面提到的都是直接使用主键id排序的情况，那么如果是获取某个分类或某个作者的文章，然后按照发布或更新时间排列，取出上一页或下一页以及直接跳转到某一页，会有什么不同吗？

  假设想获取分类id为18的第400000页的数据，看下面的SQL语句：

SELECT * FROM `article` WHERE class_id = 18 order by id LIMIT 400000,20

  上面这条语句时间花费262秒，可见无法实际应用。

  假设我们知道18分类按照时间（id与发布时间顺序是一致的，所以直接使用id即可）排序第400000页的第一条数据的id是6352948，我们把语句改写成下面这样：

SELECT * FROM `article` WHERE class_id = 18 AND id >=6352948 order by id LIMIT 20

  这条语句时间花费仅为0.0020秒，可见速度提升惊人。但是关键我们怎么知道这个索引定位值6352948呢？

  上一页与下一页的操作，使用前面的方法，加上 class_id 条件限制即可。

  直接跳转到某一页的情况，经过实践，我最终使用的是增加一张分页索引定位表（也可在上述该表增加一个分类过滤字段），花费时间则可以提升到0.0010秒。

  这种情况的另一个优化方案是分表，把不同的分类保存在不同的表中，这样就可以充分利用各记录表的主键id了。但是如果显示某个作者的所有文章的时候，就要多次查询了。要综合使用分页索引定位表效果更好。

七、常见问题答疑

1、UUID是否也适用这种优化方案？
  答：由于UUID使用的不是整型，无法使用大于或小于这样的条件，所以不适用这套方案。可以增加一个整型同顺序索引字段解决。

2、需求是按照时间排序的，能用这样优化方式吗？
  答：主键ID是自增的，所以顺序与发布时间是一致的，可以直接使用主键id，使用更新时间（发布时间也可）字段排序也可以，优化效果基本差不多，前提是时间字段要建索引。

3、我的表中没有主键id，怎么优化？
  答：首先，表中没有主键我建议还是要加上，无论是否用到。其次排序字段是整型并减了索引基本就可以使用这种优化方式，若没有这类字段，建议增加一个即可。

4、分页要计算总页数，查询中使用count()花费时间怎么优化？
  答：不要在每次请求中都count()，因为条数变化是低频的，建议增加一个统计表，保存总条数和各分类的条数。然后从统计表中获取统计值，在信息新增或删除的时候更新统计表即可，这个是低频的，整体优化还是高效的。

5、页面数量又可能变化，因为记录条数可能变化，是不是就不能这样优化了？
  答：可以，参见第4个问题和分类后过滤排序中的解决方法都可以。另外通常带整体页面刷新的时候重新统计也是可以的，不在上一页和下一页中统计就好了。