MySQL大数据量分页查询要领及其优化

要领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 > (pageNum*10) LIMIT M
• 顺应场景: 合用于数据量多的环境(元组数上万)
• 缘故起因: 索引扫描,速率会很快. 有伴侣提出: 由于数据查询出来并不是凭证pk_id排序的，以是会有遗漏数据的环境，只能要领3

要领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,等候...).

要领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 <=> 

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字段是主键，天然就包括了默认的主键索引。此刻让我们看看操作包围索引的查询结果怎样。

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!