mysql调优-大数据量分页

---

---

mysql分页一般用法

SELECT * FROM art_works LIMIT 10 OFFSET 0;

实际测试：本数据表拥有90万+的作品数据

• 实际测试截图
  
  从上述截图不难看出,随着offset偏移量的不断增加,获取结果的时间也会随之翻倍增加;

使用limit 与 offset 造成获取结果变慢的原因?

• 造成获取结果时间延长的原因是因为在于offset偏移量!
• limit+offset的原理是limit闲置返回结果的数量,offset控制跳过指定条数的数据开始获取结果;
• 例如select* from users limit 10 offset 200代表的是跳过前面200条数据开始获取后面的10条数据;那么他在运行时会获取200+10=210条数据→然后再抛弃前面的200条数据→返回后10条数据;当offset偏移量不断增大到后,需要获取数据的总量也跟着不断增大,所以返回结果等待的时间会变得越来越长;

如何解决?

• 核心思想是利用索引

    比如利用表id的主键自增索引,因为主键自增id具有唯一性操作起来相对方便简单
  

• 例子:SELECT * FROM art_works WHERE id >=281524 LIMIT 10;#偏移量相差10W

  
  从上图可以看出如果利用主键id的索引去查数据获取数据的速度就有了明显的提升!

用法延伸

情况1:UI设计稿上是上下页的情况

当设计稿是"上一页","下一页"或"下拉加载"的情况,我能在查询数据可的时候可以要求前端开发工程师把数据列表中最后一条数据的id在下次请求接口时返回后端

SELECT * FROM art_works WHERE id >=? LIMIT 10;

情况2:UI设计稿上使用数字分页的情况

当前端页面使用数字分页时

我们可以利用子查询先获取id,然后再重复情况1的操作

<?php
$page = 9;
$limit = 10;
$offset = ($page - 1) * $limit;//结果=80
//sql语句
$sql = "SELECT * FROM art_works WHERE id >=(SELECT id FROM art_works LIMIT 1 OFFSET {$offset} ) LIMIT {$limit}";

sql语句如下

SELECT * FROM art_works WHERE id >=(SELECT id FROM art_works LIMIT 1 OFFSET 90000) LIMIT 10;

情况3:UI设计稿依然采用页码分页,但需要where条件筛选

sql示例:

//需求检索作家id=18586的数据,并分成每页10条
$page = 1;
$limit = 10;
$offset = ($page - 1) * $limit;//结果=0
$artist_id  = 18586;//作家id
//sql语句
$sql = "SELECT * FROM art_works WHERE id >=(SELECT id FROM art_works WHERE artist_id = {$artist_id} LIMIT 1 OFFSET {$offset}) AND artist_id = {$artist_id} LIMIT {$limit};";

演示sql语句:
SELECT * FROM art_works WHERE id >=(SELECT id FROM art_works WHERE artist_id = 18586 LIMIT 1 OFFSET 0) AND artist_id = 18586 LIMIT 10;

总结

1. 利用索引跳过不必要的分页数据可以加快结果返回的速度;
2. 如果UI设计稿可以避免使用页码的展现形式,因为页面还需要动态获取count(*)总条数,增加了资源的消耗和增加了时间;
3. 前端尽量使用[上页][下页]的分页展现形式,前端向后端返回数据列表中的最后一条数据的主键索引id;
4. 如遇需要where条件筛选的情况时,记得主where和子句where的查找条件要相同,否则会导致获取数据不正确的问题;
5. 尽量利用主键id进行分页查找,因为主键id具有唯一性,顺序性,连贯性(不一定);
6. 如不能利用主键id进行分页的,如利用**"创建时间"字段**此类不具有唯一性的字段进行分页的,要考虑如果该字段有重复值时,要使用offset控制偏移量;

END;