概述

昨天介绍了Oracle分页实现方案，那么，mysql又是如何实现分页呢？

参考官网：https://dev.mysql.com/doc/refman/5.7/en/select.html

mysql分页实现

MySQL中实现分页查询：在数据量较小的情况下可使用limit查询来实现分页查询，在数据量大的情况下使用建立主键或唯一索引来实现，另外可通过order by对其排序。

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants, with these exceptions:

• Within prepared statements, LIMIT parameters can be specified using ? placeholder markers.
• Within stored programs, LIMIT parameters can be specified using integer-valued routine parameters or local variables.

1、limit分页实现

先看一下limit语法

SELECT * FROM TABLE
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

LIMIT子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT接受一个或两个数字参数。参数必须是一个整数常量。
如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)。

1.1、传统实现方式

一般情况下，客户端通过传递 pageNo（页码）、pageSize（每页条数）两个参数去分页查询数据库中的数据，在数据量较小（元组百/千级）时使用 MySQL自带的 limit 来解决这个问题

--pageNo：页码
--pagesize:每页显示的条数
select * from table limit (pageNo-1)*pageSize,pageSize;

1.2、建立主键或者唯一索引(高效)

在数据量较小的时候简单的使用 limit 进行数据分页在性能上面不会有明显的缓慢，但是数据量达到了 万级到百万级 sql语句的性能将会影响数据的返回。这时需要利用主键或者唯一索引进行数据分页；

--pageNo：页码
--pagesize:每页显示的条数
--假设主键或者唯一索引为 t_id
select * from table where t_id > (pageNo-1)*pageSize limit pageSize; 

1.3、基于数据再排序

当需要返回的信息为顺序或者倒序时，对上面的语句基于数据再排序。order by ASC/DESC 顺序或倒序 默认为顺序

 select * from table where t_id > (pageNo-1)*pageSize order by t_id limit pageSize; 

2、查询显示行号（实现类似Oracle数据库的ROWNUM()）

Oracle中有专门的rownum()显示行号的函数，而MySQL没有专门的显示行号函数，但可以通过用@rownum自定义变量显示行号。

一般实现过程如下：

SELECT 
  (@rownum := @rownum + 1) AS rownum,
  t.* 
FROM
  table t,
  (SELECT @rownum := 0) AS rn

3、实例演示

3.1、环境准备

CREATE TABLE t (
	EMPNO BIGINT ( 4 ) NOT NULL,
	ENAME VARCHAR ( 10 ),
	JOB VARCHAR ( 9 ),
	MGR BIGINT ( 4 ),
	HIREDATE date,
	SAL BIGINT ( 10 ),
	COMM BIGINT ( 10 ),
	DEPTNO BIGINT ( 2 ),
	PRIMARY KEY ( `EMPNO` ) 
) ENGINE = INNODB;
INSERT INTO t VALUES ('7369', 'SMITH', 'CLERK', '7902', '1980-12-17', '800', NULL, '20');
INSERT INTO t VALUES ('7499', 'ALLEN', 'SALESMAN', '7698', '1981-02-20', '1600', '300', '30');
INSERT INTO t VALUES ('7521', 'WARD', 'SALESMAN', '7698', '1981-02-22', '1250', '500', '30');
INSERT INTO t VALUES ('7566', 'JONES', 'MANAGER', '7839', '1981-04-02', '2975', NULL, '20');
INSERT INTO t VALUES ('7654', 'MARTIN', 'SALESMAN', '7698', '1981-09-28', '1250', '1400', '30');
INSERT INTO t VALUES ('7698', 'BLAKE', 'MANAGER', '7839', '1981-05-01', '2850', NULL, '30');
INSERT INTO t VALUES ('7782', 'CLARK', 'MANAGER', '7839', '1981-06-09', '2450', NULL, '10');
INSERT INTO t VALUES ('7788', 'SCOTT', 'ANALYST', '7566', '1987-04-19', '3000', NULL, '20');
INSERT INTO t VALUES ('7839', 'KING', 'PRESIDENT', NULL, '1981-11-17', '5000', NULL, '10');
INSERT INTO t VALUES ('7844', 'TURNER', 'SALESMAN', '7698', '1981-09-08', '1500', '0', '30');
INSERT INTO t VALUES ('7876', 'ADAMS', 'CLERK', '7788', '1987-05-23', '1100', NULL, '20');
INSERT INTO t VALUES ('7900', 'JAMES', 'CLERK', '7698', '1981-12-03', '950', NULL, '30');
INSERT INTO t VALUES ('7902', 'FORD', 'ANALYST', '7566', '1981-12-03', '3000', NULL, '20');
INSERT INTO t VALUES ('7934', 'MILLER', 'CLERK', '7782', '1982-01-23', '1300', NULL, '10');
commit;

3.2、limit分页

--查询第一页，每页显示5条数据
select * from t order by empno desc limit (1-1)*5,5;

--查询第二页，每页显示4条数据
select * from t order by empno desc limit (2-1)*4,4; 

3.3、查询显示行号

--查询第二页，每页显示4条数据,并在第一列加上行号
select (@rownum := @rownum + 1) AS rownum,t.* from t,
  (SELECT @rownum := 0) AS rn 
order by t.empno desc 
limit 4,4; 

觉得有用的朋友多帮忙转发哦！后面会分享更多devops和DBA方面的内容，感兴趣的朋友可以关注下~