一张千万级别数据的表想做分页,如何优化?

介绍

当进行分页时,MySQL 并不是跳过 offset 行,而是取 offset+N 行,然后放弃前 offset 行,返回 N 行。例如 limit 10000, 20。mysql排序取出10020条数据后,仅返回20条数据,查询和排序的代价都很高。那当 offset 特别大的时候,效率就非常的低下,所以我们要对sql进行改写

使用书签

用书签记录上次取数据的位置,过滤掉部分数据

如下面语句

SELECT id, name, description FROM film ORDER BY name LIMIT 1000, 10;

可以改为

SELECT id, name, description FROM film WHERE name > 'begin' ORDER BY name LIMIT 10;

name为上次分页后的最大值,注意这种场景只适用于不存在重复值的场景。

延迟关联

延迟关联:通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据

SELECT id, name, description FROM film ORDER BY name LIMIT 100,5;

id是主键值,title上面有索引。这样每次查询的时候,会先从title索引列上找到id值,然后回表,查询到所有的数据。可以看到有很多回表其实是没有必要的。完全可以先从title索引上找到id(注意只查询id是不会回表的,因为非聚集索引上包含的值为索引列值和主键值,相当于从索引上能拿到所有的列值,就没必要再回表了),然后再关联一次表,获取所有的数据

因此可以改为

SELECT film.id, name, description FROM film 
JOIN (SELECT id from film ORDER BY name LIMIT 100,5) temp
ON film.id = temp.id

倒序查询

假如查询倒数最后一页,offset可能回非常大

SELECT id, name, description FROM film ORDER BY name LIMIT 100000, 10;

改成倒序分页,效率是不是快多了?

SELECT id, name, description FROM film ORDER BY name DESC LIMIT 10;

欢迎关注

在这里插入图片描述

参考博客

[1]https://juejin.im/post/5bf229c6518825651713cdb0
[2]https://dbaplus.cn/news-155-1531-1.html
[3]https://www.cnblogs.com/songwenjie/p/9563763.html

©️2020 CSDN 皮肤主题: 深蓝海洋 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值