MySQL分页的方法及优化

在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候我们就需要用到mysql为我们提供的分页功能了。分页在MySQL中使用的是LIMIT关键字，其用法如下：

SELECT * FROM TABLE LIMIT [OFFSET,] ROWS | ROWS OFFSET OFFSET

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。日常使用中，其格式一般为：limit 当前页码*页面容量-1 , 页面容量。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量，不能是负数。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

#返回第6到15条数据SELECT * FROM testinnodb LIMIT 5,10;#返回前十条记录，相当于LIMIT 0,10SELECT * FROM testinnodb LIMIT 10;#返回第十条数据SELECT * FROM testinnodb LIMIT 9,1;

上面就是MySQL中LIMIT的基本用法，下面分析一下其在实际使用中的效率。
测试表为testinnodb，建表语句如下：

CREATE TABLE yzc.testinnodb (  id int(10) UNSIGNED NOT NULL AUTO_INCREMENT,  time datetime DEFAULT NULL,  type int(10) NOT NULL,  title varchar(12345) NOT NULL DEFAULT '',  PRIMARY KEY (id))ENGINE = INNODB

向其中插入150W条数据。
首先测试从其中取出15条数据，偏移量分别设为0.13W、1.3W、13W、130W后其时间分别为多少

SELECT * FROM testinnodb LIMIT 1300,15;------时间: 0.003sSELECT * FROM testinnodb LIMIT 13000,15;------时间: 0.015sSELECT * FROM testinnodb LIMIT 130000,15;------时间: 0.109sSELECT * FROM testinnodb LIMIT 1300000,15;------时间: 1.603s

多次运行，其平均时间大致如上，通过以上的测试，可以发现LIMIT语句在偏移量比较小的时候效率比较好，但是当偏移量比较大的时候，其性能就会急剧下降。
对于LIMIT M,N来说，其中M为偏移量，N为需要取的数据的行数，其意思是扫描前面的M+N行，然后扔掉前M行，返回剩下的N行，这在一个高并发的应用里，如果M很大，每次查询需要扫描超过数万行，性能肯定大打折扣。
查看上面任一语句的执行计划，如下：

可以很明显的发现，上面的所有语句都是全表扫描，并且未使用任何的索引

---

常见的分页优化方法：

还是对testinnodb表测试，假设每页数据为500条，总共有3000页，查询其中的第100页的下一页，也就是ID为50001-50500的记录。下面语句的执行时间都是在数据库中多次运行取时间的平均值。

方法一: 直接使用数据库提供的SQL语句
语句样式: MySQL中,可用如下方法:

SELECT * FROM testinnodb LIMIT 50000,500;------时间: 0.029s

适应场景: 适用于数据量较少的情况(元组百/千级)
缺点: 全表扫描,速度会很慢。
执行计划：

方法二: 基于索引再排序
语句样式: MySQL中,可用如下方法:

SELECT * FROM testinnodb WHERE id >(100*500) ORDER BY id ASC LIMIT 500;------时间: 0.002s

适应场景: 适用于数据量多的情况(元组数上万). ORDER BY后的列对象是主键或唯一，所以,使得ORDER BY操作能利用索引被消除
原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC).
执行计划：

方法三： MySQL自带的BETWEEN…AND… 语句查询优化

SELECT * FROM testinnodb WHERE id BETWEEN 50000 AND 50500;------时间: 0.006s

执行计划：
.

方法四： 如果需要查询 id 不是连续的一段，最佳的方法就是先找出 id ，然后用 in 查询
原理： MySQL 的索引对于in语句同样是有效的

SELECT * FROM testinnodb WHERE id IN(10000, 100000, 1000000...);

方法五: 利用”子查询/JOIN+索引”快速定位元组的位置,然后再读取元组
子查询示例:
原理：先找出第一条数据，然后大于等于这条数据的id的就是要获取的数据，减少了无用的IO操作，只对需要的数据执行SELECT *操作
缺点：数据必须是连续的，否则可能需要先查询第一行所在的位置
子查询：

SELECT * FROM testinnodb WHERE id >= (SELECT id FROM testinnodb LIMIT 50000,1) LIMIT 500;------时间: 0.018s

执行计划如下：

JOIN联接两个表:

SELECT * FROM testinnodb INNER JOIN(SELECT id FROM testinnodb ORDER BY id LIMIT 50000, 500) AS t USING (id);   ------时间: 0.020s

执行计划：

方法六：存储过程类
原因: 存储过程中的SQL都是预先编译过的，这样子把操作封装在服务器，相对更快一些。

方法七: 反向查找优化法
原理：当偏移超过一半记录数的时候，先用排序，这样偏移就反转了
LIMIT偏移算法：

正向查找： (当前页 - 1) * 页长度反向查找： 总记录 - 当前页 * 页长度

缺点： ORDER BY优化比较麻烦，要增加索引，索引影响数据的修改效率，并且要知道总记录数 ，偏移大于数据的一半
实例：
查询其中的第2001页的下一页，也就是ID为1000001-1000500的记录。
直接查找：

SELECT * FROM testinnodb LIMIT 1000000,500;------时间: 1.168s

反向查找：

SELECT * FROM testinnodb ORDER BY id DESC LIMIT 499500,500;------时间: 0.722s

网上有人写使用 SQL_CALC_FOUND_ROWS。 没有道理，勿模仿

---

其它：包含排序操作的分页需求
解决思路： 建立合适的索引，先找出对应的主键，在根据主键查找需要的数据，减少不必要的IO操作。
假设现在的查找需求为，跟据type升序排序，查找第100页。
创建的索引为：

ALTER TABLE testinnodb ADD INDEX idx_type(type) USING BTREE;

直接查找：

SELECT * FROM testinnodb ORDER BY type LIMIT 50000,500;无索引：1.686s      有索引：1.581s

直接操作时，在有无索引来那个中条件下，虽然有优化，但是好像还是不太理想。
借鉴上面的方法，继续优化：

JOIN：SELECT * FROM testinnodb JOIN (SELECT id FROM testinnodb ORDER BY type LIMIT 50000,500) AS t USING(id);无索引：1.510s      有索引：0.016s   子查询：SELECT * FROM testinnodb WHERE id >= (SELECT id FROM testinnodb ORDER BY type LIMIT 50000,1) LIMIT 500;无索引：1.506s      有索引：0.017s

现在的执行效率就可以达到我们的需求了。

---

实际工作中对于LIMIT的需求

实际编码的过程中，我们经常会遇到类似于：“上一页 1 2 3 4 5 6 7 8 9 … 下一页”这样的翻页需求。这个时候我们又应该如何优化查询呢？
我们已经知道limit 10000,500的意思扫描满足条件的10500行，扔掉前面的10000行，返回最后的500行，大量的时间都浪费在前面的10000行上。所以对于上面的需求，如果LIMIT M,N不可避免的话，要优化效率，只有尽可能的让M小一些。
还是对testinnodb表测试，假设每页数据为500条，总共有3000页，假设当前是第10页，当前页的最大ID是5000，最小ID是4501。
当前的第10页的SQL如下：

SELECT * FROM testinnodb WHERE id >=4501 ORDER BY id ASC LIMIT 0,500;

比如要跳到第9页，SQL语句可以这样写：

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id DESC LIMIT 0,500;

比如要跳到第8页，SQL语句可以这样写：

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id desc LIMIT 500,500;

比如要跳到第7页，SQL语句可以这样写：

SELECT * FROM testinnodb WHERE id <4501 ORDER BY id desc LIMIT 1000,500;

跳转到第11页：

SELECT * FROM testinnodb WHERE id >5000 ORDER BY id ASC LIMIT 0,500;

跳转到第12页：

SELECT * FROM testinnodb WHERE id >5000 ORDER BY id ASC LIMIT 500,500;

原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话M值相对较小，大大减少扫描的行数。其实传统的LIMIT M，N，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。

---

MYSQL的FOUND_ROWS()函数

Mysql FOUND_ROWS() 函数结合SQL_CALC_FOUND_ROWS在SELECT中可以得到两个结果：
1. 得到Limit的内容
2. 得到去除Limit以后所有行数

SELECT语句中经常可能用LIMIT限制返回行数。有时候可能想要知道如果没有LIMIT会返回多少行，但又不想再执行一次相同语句。那么，在SELECT查询中包含SQL_CALC_FOUND_ROWS选项，然后执行FOUND_ROWS()就可以了：

    select SQL_CALC_FOUND_ROWS * FROM tbl_name WHERE id > 100 LIMIT 10;    SELECT FOUND_ROWS();

其中SQL_CALC_FOUND_ROWS 告诉Mysql将sql所处理的行数记录下来，FOUND_ROWS() 则取到了这个纪录。 虽然也是两个语句，但是只执行了一次主查询，所以效率比原来要高很多。

1. 如果在前一条语句中使用SQL_CALC_FOUND_ROWS选项，FOUND_ROWS()将返回第一条语句没有LIMIT时返回的行数。
2. 如果在前一条语句中没有使用SQL_CALC_FOUND_ROWS选项，FOUND_ROWS()将返回前一条语句实际返回的行数。
   如果使用 SELECT SQL_CALC_FOUND_ROWS，MySQL必须计算所有结果集的行数。尽管这样，总比再执行一次不使用LIMIT的查询要快多了吧，因为那样结果集要返回客户端的。