关于java:MySQL-百万级数据怎么做分页查询

作者：大神养成记 \
起源：https://www.cnblogs.com/genin…

办法 1: 间接应用数据库提供的 SQL 语句

• 语句款式: MySQL 中, 可用如下办法: SELECT * FROM 表名称 LIMIT M,N
• 适应场景: 实用于数据量较少的状况 (元组百 / 千级)
• 起因 / 毛病: 全表扫描, 速度会很慢 且 有的数据库后果集返回不稳固 (如某次返回 1,2,3, 另外的一次返回 2,1,3). Limit 限度的是从后果集的 M 地位处取出 N 条输入, 其余摈弃.

办法 2: 建设主键或惟一索引, 利用索引 (假如每页 10 条)

• 语句款式: MySQL 中, 可用如下办法: SELECT FROM 表名称 WHERE id_pk > (pageNum10) LIMIT M
• 适应场景: 实用于数据量多的状况 (元组数上万)
• 起因: 索引扫描, 速度会很快. 有敌人提出: 因为数据查问进去并不是依照 pk_id 排序的，所以会有漏掉数据的状况，只能办法 3

办法 3: 基于索引再排序

• 语句款式: MySQL 中, 可用如下办法: SELECT FROM 表名称 WHERE id_pk > (pageNum10) ORDER BY id_pk ASC LIMIT M
• 适应场景: 实用于数据量多的状况 (元组数上万). 最好 ORDER BY 后的列对象是主键或惟一所以, 使得 ORDERBY 操作能利用索引被打消但后果集是稳固的 (稳固的含意, 参见办法 1)
• 起因: 索引扫描, 速度会很快. 但 MySQL 的排序操作, 只有 ASC 没有 DESC(DESC 是假的, 将来会做真正的 DESC, 期待 …).

办法 4: 基于索引应用 prepare

第一个问号示意 pageNum，第二个？示意每页元组数

• 语句款式: MySQL 中, 可用如下办法: PREPARE stmt_name FROM SELECT FROM 表名称 WHERE id_pk > (？？) ORDER BY id_pk ASC LIMIT M
• 适应场景: 大数据量
• 起因: 索引扫描, 速度会很快. prepare 语句又比个别的查问语句快一点。

办法 5: 利用 MySQL 反对 ORDER 操作能够利用索引疾速定位局部元组, 防止全表扫描

比方: 读第 1000 到 1019 行元组 (pk 是主键 / 惟一键).

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

办法 6: 利用 ” 子查问 / 连贯 + 索引 ” 疾速定位元组的地位, 而后再读取元组.

比方 (id 是主键 / 惟一键, 蓝色字体时变量)

利用子查问示例:

SELECT * FROM your_table WHERE id <= 
(SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize ORDER BY id desc 
LIMIT $pagesize

利用连贯示例:

SELECT * FROM your_table AS t1 
JOIN (SELECT id FROM your_table ORDER BY id desc LIMIT ($page-1)*$pagesize AS t2 
WHERE t1.id <= t2.id ORDER BY t1.id desc LIMIT $pagesize;

mysql 大数据量应用 limit 分页，随着页码的增大，查问效率越低下。

测试试验

1. 间接用 limit start, count 分页语句，也是我程序中用的办法：

select * from product limit start, count

当起始页较小时，查问没有性能问题，咱们别离看下从 10，100，1000，10000 开始分页的执行工夫（每页取 20 条）。

如下：

select * from product limit 10, 20   0.016 秒
select * from product limit 100, 20   0.016 秒
select * from product limit 1000, 20   0.047 秒
select * from product limit 10000, 20   0.094 秒 

咱们曾经看出随着起始记录的减少，工夫也随着增大，这阐明分页语句 limit 跟起始页码是有很大关系的，那么咱们把起始记录改为 40w 看下（也就是记录的个别左右）

select * from product limit 400000, 20   3.229 秒 

再看咱们取最初一页记录的工夫

select * from product limit 866613, 20   37.44 秒 

像这种分页最大的页码页显然这种工夫是无法忍受的。

从中咱们也能总结出两件事件：

1. limit 语句的查问工夫与起始记录的地位成正比
2. mysql 的 limit 语句是很不便，然而对记录很多的表并不适宜间接应用。

2. 对 limit 分页问题的性能优化办法

利用表的笼罩索引来减速分页查问

咱们都晓得，利用了索引查问的语句中如果只蕴含了那个索引列（笼罩索引），那么这种状况会查问很快。

因为利用索引查找有优化算法，且数据就在查问索引下面，不必再去找相干的数据地址了，这样节俭了很多工夫。另外 Mysql 中也有相干的索引缓存，在并发高的时候利用缓存就成果更好了。

在咱们的例子中，咱们晓得 id 字段是主键，天然就蕴含了默认的主键索引。当初让咱们看看利用笼罩索引的查问成果如何。

这次咱们之间查问最初一页的数据（利用笼罩索引，只蕴含 id 列），如下：

select id from product limit 866613, 20 0.2 秒 

绝对于查问了所有列的 37.44 秒，晋升了大略 100 多倍的速度

那么如果咱们也要查问所有列，有两种办法，一种是 id>= 的模式，另一种就是利用 join，看下理论状况：

SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20

查问工夫为 0.2 秒！

另一种写法

SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id

查问工夫也很短！

3. 复合索引优化办法

MySql 性能到底能有多高？MySql 这个数据库相对是适宜 dba 级的高手去玩的，个别做一点 1 万篇新闻的小型零碎怎么写都能够，用 xx 框架能够实现疾速开发。可是数据量到了 10 万，百万至千万，他的性能还能那么高吗？一点小小的失误，可能造成整个零碎的改写，甚至更本零碎无奈失常运行！好了，不那么多废话了。

用事实谈话，看例子：

数据表 collect (id, title ,info ,vtype) 就这 4 个字段，其中 title 用定长，info 用 text, id 是逐步，vtype 是 tinyint，vtype 是索引。这是一个根本的新闻零碎的简略模型。当初往里面填充数据，填充 10 万篇新闻。最初 collect 为 10 万条记录，数据库表占用硬 1.6G。

OK , 看上面这条 sql 语句：

select id,title from collect limit 1000,10;

很快；基本上 0.01 秒就 OK，再看上面的

select id,title from collect limit 90000,10;

从 9 万条开始分页，后果？

8- 9 秒实现，my god 哪出问题了？其实要优化这条数据，网上找失去答案。看上面一条语句:

select id from collect order by id limit 90000,10;

很快，0.04 秒就 OK。为什么？因为用了 id 主键做索引当然快。网上的改法是：

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

这就是用了 id 做索引的后果。可是问题简单那么一点点，就完了。看上面的语句

select id from collect where vtype=1 order by id limit 90000,10; 很慢，用了 8 - 9 秒！

到了这里我置信很多人会和我一样，有解体感觉！vtype 做了索引了啊？怎么会慢呢？vtype 做了索引是不错，你间接

select id from collect where vtype=1 limit 1000,10;

是很快的，基本上 0.05 秒，可是进步 90 倍，从 9 万开始，那就是 0.05*90=4.5 秒的速度了。和测试后果 8 - 9 秒到了一个数量级。

从这里开始有人提出了分表的思路，这个和 dis #cuz 论坛是一样的思路。思路如下：

建一个索引表：t (id,title,vtype) 并设置成定长，而后做分页，分页出后果再到 collect 外面去找 info。是否可行呢？试验下就晓得了。

10 万条记录到 t(id,title,vtype) 里，数据表大小 20M 左右。用

select id from t where vtype=1 order by id limit 90000,10;

很快了。基本上 0.1-0.2 秒能够跑完。为什么会这样呢？我猜测是因为 collect 数据太多，所以分页要跑很长的路。limit 齐全和数据表的大小无关的。其实这样做还是全表扫描，只是因为数据量小，只有 10 万才快。OK，来个疯狂的试验，加到 100 万条，测试性能。加了 10 倍的数据，马上 t 表就到了 200 多 M，而且是定长。还是方才的查问语句，工夫是 0.1-0.2 秒实现！分表性能没问题？

错！因为咱们的 limit 还是 9 万，所以快。给个大的，90 万开始

select id from t where vtype=1 order by id limit 900000,10;

看看后果，工夫是 1 - 2 秒！why ?

分表了工夫还是这么长，十分之郁闷！有人说定长会进步 limit 的性能，开始我也认为，因为一条记录的长度是固定的，mysql 应该能够算出 90 万的地位才对啊？可是咱们高估了 mysql 的智能，他不是商务数据库，事实证明定长和非定长对 limit 影响不大？怪不得有人说 discuz 到了 100 万条记录就会很慢，我置信这是真的，这个和数据库设计无关！

难道 MySQL 无奈冲破 100 万的限度吗？？？到了 100 万的分页就真的到了极限？

答案是：NO 为什么冲破不了 100 万是因为不会设计 mysql 造成的。上面介绍非分表法，来个疯狂的测试！一张表搞定 100 万记录，并且 10G 数据库，如何疾速分页！

好了，咱们的测试又回到 collect 表，开始测试论断是：

30 万数据，用分表法可行，超过 30 万他的速度会慢道你无法忍受！当然如果用分表 + 我这种办法，那是相对完满的。然而用了我这种办法后，不必分表也能够完满解决！

答案就是： 复合索引 ！有一次设计 mysql 索引的时候，无心中发现索引名字能够任取，能够抉择几个字段进来，这有什么用呢？

开始的

select id from collect order by id limit 90000,10;

这么快就是因为走了索引，可是如果加了 where 就不走索引了。抱着试试看的想法加了 search(vtype,id) 这样的索引。

而后测试

select id from collect where vtype=1 limit 90000,10;

十分快！0.04 秒实现！

再测试:

select id ,title from collect where vtype=1 limit 90000,10;

十分遗憾，8- 9 秒，没走 search 索引！

再测试：search(id,vtype)，还是 select id 这个语句，也十分遗憾，0.5 秒。

综上：如果对于有 where 条件，又想走索引用 limit 的，必须设计一个索引，将 where 放第一位，limit 用到的主键放第 2 位，而且只能 select 主键！

完满解决了分页问题了。能够疾速返回 id 就有心愿优化 limit，按这样的逻辑，百万级的 limit 应该在 0.0x 秒就能够分完。看来 mysql 语句的优化和索引时十分重要的！

近期热文举荐：

1.1,000+ 道 Java 面试题及答案整顿 (2021 最新版)

2. 终于靠开源我的项目弄到 IntelliJ IDEA 激活码了，真香！

3. 阿里 Mock 工具正式开源，干掉市面上所有 Mock 工具！

4.Spring Cloud 2020.0.0 正式公布，全新颠覆性版本！

5.《Java 开发手册（嵩山版）》最新公布，速速下载！

感觉不错，别忘了顺手点赞 + 转发哦！