MySql如何实现分页

分页需求:

客户端通过传递start(页码),limit(每页显示的条数)两个参数去分页查询数据库表中的数据,那我们知道MySql数据库提供了分页的函数limit m,n,但是该函数的用法和我们的需求不一样,所以就需要我们根据实际情况去改写适合我们自己的分页语句,具体的分析如下:

比如:

查询第1条到第10条的数据的sql是:select * from table limit 0,10;   ->对应我们的需求就是查询第一页的数据:select * from table limit (1-1)*10,10;

查询第10条到第20条的数据的sql是:select * from table limit 10,20;  ->对应我们的需求就是查询第二页的数据:select * from table limit (2-1)*10,10;

查询第20条到第30条的数据的sql是:select * from table limit 20,30;  ->对应我们的需求就是查询第三页的数据:select * from table limit (3-1)*10,10;

mysql查看字符编码

1
2
3
4
5
6
7
mysql -uroot -proot
 
/s
 
create database user character set gbk;
 
show variables like'%char%';

三种MySQL分页的方法示例:

1、limit m,n分页语句:

1
2
select * from dept order by deptno desc limit 3,3;
select * from dept order by deptno desc limit m,n;

limit 3,3的意思扫描满足条件的3+3行,撇去前面的3行,返回最后的3行,那么问题来了,如果是limit 200000,200,需要扫描200200行,如果在一个高并发的应用里,每次查询需要扫描超过20W行,效率十分低下。

2、limit m语句:

1
2
 select * from dept where deptno >10 order by deptno asc limit n;//下一页
 select * from dept where deptno <60 order by deptno desc limit n//上一页

这种方式不管翻多少页只需要扫描n条数据。

 

3、方法2 虽然扫描的数据量少了,但是在某些需要跳转到多少也得时候就无法实现,这时还是需要用到方法1,既然不能避免,那么我们可以考虑尽量减小m的值,因此我们可以给这条语句加上一个条件限制。是的每次扫描不用从第一条开始。这样就能尽量减少扫描的数据量。

例如:每页10条数据,当前是第10页,当前条目ID的最大值是109,最小值是100.
那么跳到第9页:

1
select * from dept where deptno<100 order by desc limit 0,10;

那么跳到第8页:

1
select * from dept where deptno<100 order by desc limit 10,10;

那么跳到第11页:

1
select * from dept where deptno>109 order by asc limit 0,10;

那么跳到第11页:

1
select * from dept where deptno>109 order by asc limit 10,10;

不同场景下应该选用哪些MySQL优化?

一、直接使用数据库提供的SQL语句

---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 LIMIT M,N。

---适应场景: 适用于数据量较少的情况(元组百/千级)。

---原因/缺点: 全表扫描,速度会很慢 且 有的数据库结果集返回不稳定(如某次返回1,2,3,另外的一次返回2,1,3)。Limit限制的是从结果集的M位置处取出N条输出,其余抛弃。

二、建立主键或唯一索引, 利用索引(假设每页10条)

---语句样式: MySQL中,可用如下方法:

1
SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) LIMIT M。

---适应场景: 适用于数据量多的情况(元组数上万)。

---原因: 索引扫描,速度会很快。有朋友提出因为数据查询出来并不是按照pk_id排序的,所以会有漏掉数据的情况,只能方法3。

三、基于索引再排序

---语句样式: MySQL中,可用如下方法: SELECT * FROM 表名称 WHERE id_pk > (pageNum*10) ORDER BY id_pk ASC LIMIT M。

---适应场景: 适用于数据量多的情况(元组数上万). 最好ORDER  BY后的列对象是主键或唯一所以,使得ORDERBY操作能利用索引被消除但结果集是稳定的(稳定的含义,参见方法1)。

---原因: 索引扫描,速度会很快. 但MySQL的排序操作,只有ASC没有DESC(DESC是假的,未来会做真正的DESC,期待)。

四、基于索引使用prepare(第一个问号表示pageNum,第二个?表示每页元组数)

---语句样式: MySQL中,可用如下方法:

1
2
PREPARE stmt_name FROM SELECT * FROM 表名称 WHERE id_pk > (*) ORDER BY id_pk 
ASC LIMIT M。

---适应场景: 大数据量。

---原因: 索引扫描,速度会很快. prepare语句又比一般的查询语句快一点。

五、利用MySQL支持ORDER操作可以利用索引快速定位部分元组,避免全表扫描

---比如: 读第1000到1019行元组(pk是主键/唯一键)。

1
---SELECT * FROM your_table WHERE pk>=1000 ORDER BY pk ASC LIMIT 0,20

六、利用"子查询/连接+索引"快速定位元组的位置,然后再读取元组. 道理同方法5

---如(id是主键/唯一键,蓝色字体时变量):

利用子查询示例:

1
2
3
4
SELECT* FROMyour_table WHEREid <=
(SELECTid FROMyour_table ORDER
BYid descLIMIT ($page-1)*$pagesize ORDERBYid desc
LIMIT $pagesize

利用连接示例:

1
2
3
4
5
SELECT* FROMyour_table ASt1
JOIN(SELECTid FROMyour_table ORDERBY
id descLIMIT ($page-1)*$pagesize ASt2
WHERE
t1.id <= t2.id ORDERBYt1.id descLIMIT $pagesize;

七、存储过程类(最好融合上述方法5/6)

---语句样式: 不再给出

---适应场景: 大数据量.  作者推荐的方法

---原因: 把操作封装在服务器,相对更快一些。

八、反面方法

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

基本上,可以推广到所有数据库,道理是一样的。但方法5未必能推广到其他数据库,推广的前提是,其他数据库支持ORDER BY操作可以利用索引直接完成排序。

数据量大时,MySQL分页优化的示例及原则

数据库里面的数据由于长期的堆积,导致数据量不断的上升,而后台的系统每次进行分页查询的时候,效率都会降低很多。后来查看了一下之后,发现此时的分页原理主要是采用了传统的物理分页 limit n,m 的方式。

为了方便演示,我特意创建了以下几张表进行实例演练:

表分别是商品表,用户表,用户选购商品记录表:

goods user g_u

三张表的关系比较简单,user的id和goods里面的id合并生成关联数据,存储在了g_u里面。三张数据库表的设计如下所示:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE `goods` (
  `id` int(11) NOT NULL,
  `name` varchar(60) COLLATE utf8_unicode_ci NOT NULL,
  `price` decimal(6,1) NOT NULL,
  `des` varchar(40) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `age` tinyint(3) NOT NULL,
  `sex` tinyint(1) NOT NULL COMMENT '年龄',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
 
CREATE TABLE `g_u` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `g_id` int(11) NOT NULL COMMENT '商品id',
  `u_id` int(11) NOT NULL COMMENT '用户id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2800001 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

这个模拟的应用场景非常简单,用户和商品之间的关系维持在了一对多的关联中。为了方便进行后续的测试,我用jmeter批量创建了1900000条测试数据,模拟一次百万级的数据查询场景。

假设现在需求里面有这样的一个业务场景,需要我们对购买记录表里面的数据进行分页查询,那么对于常规的分页查询操作,常人会想到的方式可能是通过下述的语句:

1
SELECT * from g_u as gu ORDER BY id limit 1850000,100

测试一下发现,查询的时间为:

百万级数据,分页如何处理?

当我们搜索的数据越靠后边的时候,搜索的速度就会越低下,因此这个时候,适当的创建索引就显得比较重要了。

首先我们来做一次explain的sql检测,检测结果为如下所示:

百万级数据,分页如何处理?

由于我们查询的时候,使用的是根据主键索引id进行排序,因此查询的时候key一项为PRIMARY。

1
 SELECT * FROM g_u WHERE id >=(SELECT id FROM g_u LIMIT 1850000,1) ORDER BY id  LIMIT 100

此时查询有了一些许的提升,但是依旧查询缓慢

百万级数据,分页如何处理?

通过explain执行计划分析结果可见:

百万级数据,分页如何处理?

子查询用到了索引,外部查询用到了where的辅助索引

这个时候我们不妨可以试下通过利用主键id来提升我们的查询效率:

1
SELECT * FROM g_u as gu WHERE gu.id>($firstId+$pageSize*$pageSize)  limit 100

查询的时间一下子大大缩短了许多:

百万级数据,分页如何处理?

通过explain分析一下该sql:

百万级数据,分页如何处理?

这里面,sql在运行的时候借助了主键索引的帮助,因此效率大大提升了。

但是这个时候,可能你会有这么一个疑惑。如果说数据的索引不是连续的该如何处理分页时候每页数据的完整性和一致性?

这里不妨可以试试另外的一种思路,通过建立一张第三方的表g_u_index表,将原本乱序的id存储在g_u_index中,在g_u_index一表中,我们可以通过该表有序的g_u_index.id来对应原本相应的无序的g_u.id。建表的sql语句如下所示:

1
2
3
4
5
6
CREATE TABLE `g_u_index` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `index` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_id_index` (`id`,`index`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1900024 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ps: 可以为id和index两者建立一套复合索引,提升查询的效率。

这里我们需要保证一点就是,g_u表中插入的数据顺序需要和g_u_index表中插入的顺序是一致的。然后查询分页指定的index时候可以这么来查:

1
 SELECT g_u_index.index FROM g_u_index  WHERE id=($firstId+$pageSize*$pageSize)  limit 1

通过执行explain分析后,结果变成如下所示:

百万级数据,分页如何处理?

查询时间为:0.001s

百万级数据,分页如何处理?

有了第三方表的帮助下,此时分页的sql优化可以调整为以下这种方式:

1
2
3
SELECT * FROM g_u as gu where gu.id>(
SELECT g_u_index.index FROM g_u_index  WHERE id=($firstId+$pageSize*$pageSize) limit 1
) limit 100

通过构建了第三方表之后,数据的查询时间一下子大大缩减了:

百万级数据,分页如何处理?

查询的时候为了更加人性化,通常不需要显示这些无意义的id,需要的是商品名称和用户姓名,假设我们还是只采用最原始的无第三方表的方式进行查询的话,效率会比较底下:

1
2
3
SELECT gu.id,goods.`name`,`user`.username FROM g_u as gu ,goods ,`user` 
where goods.id=gu.g_id AND `user`.id=gu.u_id 
ORDER BY id limit 1500000,1000

结果:

百万级数据,分页如何处理?

因此如果借助了第三方表查询的话,sql可以调整成下方这种类型:

1
2
3
4
5
6
SELECT goods.`name`,`user`.username FROM g_u as gu ,goods ,`user` 
where goods.id=gu.g_id AND `user`.id=gu.u_id 
and 
gu.id>=(
SELECT g_u_index.index FROM g_u_index  WHERE id=(9+1000*1900) limit 1
) limit 100

查询的时间会大大减少:

百万级数据,分页如何处理?

通过explain执行计划分析之后,结果如下:

百万级数据,分页如何处理?

在实际的业务场景中,一张原来就有上百万数据的表要做出这样的id拆分,并且同步到第三方表的确实不太容易,这里推荐一种思路,可以借助阿里的中间件canal来实现对于数据库日志的订阅,然后自定义进行数据的同步操作。

对于sql的优化需要结合实际的业务需求来开展,总的来说,这部分还是需要有一定的实战演练才能变强。

常用的sql优化技巧小结:

1.数据量大的时候,应尽量避免全表扫描,应考虑在 where及 order by 涉及的列上建立索引,建索引可以大大加快数据的检索速度。

2.适当的使用Explain可以对sql进行相应的深入分析。

3.当只要一行数据时使用LIMIT 1。

4.在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使用,并且应尽可能的让字段顺序与索引顺序相一致。

5.不要在 where子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

6.适当的时候采用覆盖索引可以提高查询的效率。

 

附MySQL规范建议

基本设计规范
1. 所有表必须使用Innodb存储引擎
2. 数据库和表的字符集统一使用UTF8
3. 所有表和字段都需要添加注释
4. 尽量控制单表数据量的大小,建议控制在500万以内。
5. 谨慎使用Mysql分区表
6. 尽量做到冷热数据分离,减小表的宽度
7. 禁止在表中建立预留字段
8. 禁止在数据库中存储图片,文件等大的二进制数据
9. 禁止在线上做数据库压力测试
10. 禁止从开发环境,测试环境直接连接生成环境数据库
字段设计规范
1. 优先选择符合存储需要的最小的数据类型
2. 避免使用TEXT、BLOB数据类型,最常见的TEXT类型可以存储64k的数据
3. 避免使用ENUM类型
4. 尽可能把所有列定义为NOT NULL
5. 使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间
6. 同财务相关的金额类数据必须使用decimal类型
索引设计规范
1. 限制每张表上的索引数量,建议单张表索引不超过5个
2. 禁止给表中的每一列都建立单独的索引
3. 每个Innodb表必须有个主键
SQL开发规范
1. 建议使用预编译语句进行数据库操作
2. 避免数据类型的隐式转换
3. 充分利用表上已经存在的索引
4. 数据库设计时,应该要对以后扩展进行考虑
5. 程序连接不同的数据库使用不同的账号,进制跨库查询
6. 禁止使用SELECT * 必须使用SELECT <字段列表> 查询
7. 禁止使用不含字段列表的INSERT语句
8. 避免使用子查询,可以把子查询优化为join操作
9. 避免使用JOIN关联太多的表
10. 减少同数据库的交互次数
11. 对应同一列进行or判断时,使用in代替or
12. 禁止使用order by rand() 进行随机排序
13. WHERE从句中禁止对列进行函数转换和计算
14. 在明显不会有重复值时使用UNION ALL 而不是UNION