IT数码 购物 网址 头条 软件 日历 阅读 图书馆
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
图片批量下载器
↓批量下载图片,美女图库↓
图片自动播放器
↓图片自动播放器↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁
 
   -> 大数据 -> SQL - MySQL深分页 -> 正文阅读

[大数据]SQL - MySQL深分页

一、MySQL深分页问题

我们在日常开发中,查询数据量比较大的时候,后端基本都会通过前端,移动端传过来的页码,每页数据行数,通过SQL中的 limit 进行分页,如果查询页数比较小的时候,不会出现太大问题,但是如果查询页码比较大的时候,性能就会出现急剧下降瓶颈

如:

假设有一个千万量级的表,取1到10条数据

select column_name1,column_name2... from table limit 0,10;
select column_name1,column_name2... from table limit 1000,10;

?这两条语句查询时间应该在毫秒级完成

select column_name1,column_name2... from table limit 1000000,10;

这条语句执行之间在秒级完成,查询效率低下,还可能导致接口超时

使用?select column_name1,column_name2... from table_name表名 limit offset, rows 的情况下直接?limit 1000000,10 扫描的是约100万条数据,并且是需要回表100W次,也就是说?部分性能都耗在随机访问上,到头来只?到10条数据(总共取1000010条数据只留10条记录)

这种查询的慢,其实是因为 limit 后面的偏移量太大导致的

1、limit 语法解读

limit用于数据的分页查询,也会用于数据的截取,limit的用法:

SELECT column_name1,column_name2... FROM table_name表名 LIMIT offset,rows

或

SELECT column_name1,column_name2... FROM table_name表名 LIMIT rows OFFSET offset  

注:

table_name :表名

column_name:字段名?

  • 第一种:SELECT * FROM table LIMIT offset, rows # 常用形式

    -- 从0开始,截取5条记录,即检索行为1到5
    SELECT column_name1,column_name2... FROM table_name表名 limit 0,5
    -- 注意: 关键字limit后面的两个参与用逗号分割
    
  • 第二种:SELECT * FROM table LIMIT rows OFFSET offset

    -- 从0开始,截取5条记录,即检索行为1到5
    SELECT column_name1,column_name2... FROM table_name表名 limit 5 offset 0
    -- 注意: 使用limit和offset两个关键字,并且各带一个参数,中间没有逗号分割
    
  • 第三种:SELECT * FROM table LIMIT rows

    -- 截取记录的前五行数据,可以理解为offset的默认值为0
    SELECT column_name1,column_name2... FROM table_name表名 limit 5

2、回表

回表,顾名思义就是回到表中,也就是先通过普通索引扫描出数据所在的行,再通过行主键ID?取出索引中未包含的数据。所以回表的产生也是需要一定条件的,如果一次索引查询就能获得所有的select?记录就不需要回表,如果select?所需获得列中有其他的非索引列,就会发生回表动作。即基于非主键索引的查询需要多扫描一棵索引树

主键索引树的叶子节点直接就是我们要查询的整行数据,而非主键索引的叶子节点是主键的值,查到主键的值以后,还需要再通过主键的值再进行一次查询

回表,简单说就是mysql内部需要经过两次查询

第一次先索引扫描,然后再通过主键去取索引中未能提供的数据

create `table` tb_name(
    `id` int(11) not null auto_increment ,?
    `k` int(11) default '0' ,
    `name` varchar(16),
    primary key(id)
    index (k)
)engine=InnoDB;

我们提取id=500这一行的全部数据,这里通过主键id定位到这一行,然后返回数据

select * from T where ID=500;
+-----+---+-------+
| id ?| k | name ?|
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

这里我们先通过普通索引,搜索 k 索引树,得到 ID 的值为 500,再到 ID 索引树搜索一次。这个过程即为回表

select * from T where k=5;
+-----+---+-------+
| id ?| k | name ?|
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+

二、优化方案

(一)模仿百度、谷歌方案(前端业务控制)

类似于分段。我们给每次只能翻100页、超过一百页的需要重新加载后面的100页。这样就解决了每次加载数量数据大 速度慢的问题了?

这种方式比较简单粗暴,就是不允许查看这么靠后的数据

(二)索引覆盖 + 子查询

根据主键 id,在上面建了索引,先在索引树中找到开始位置的 id 值,再根据找到的 id 值查询行数据

SELECT 
    id,name,age 
FROM
    t_user user
WHERE
    user.id = (select MIN(id) from t_user where age = #{age})
SELECT 
    id,name,age 
FROM 
    t_user 
WHERE 
    id >= (SELECT id FROM t_user order by id LIMIT 80000,1) 
LIMIT 10

(三)起始位置重定义(记录每次取出的最大id, 然后where id > 最大id

这种方法适用于:除了主键ID等离散型字段外,也适用连续型字段datetime等
最大id由前端分页 pageNum 和 pageIndex 计算出来

select * from table_name Where id > 最大id limit 10000, 10;
  大数据 最新文章
实现Kafka至少消费一次
亚马逊云科技:还在苦于ETL?Zero ETL的时代
初探MapReduce
【SpringBoot框架篇】32.基于注解+redis实现
Elasticsearch:如何减少 Elasticsearch 集
Go redis操作
Redis面试题
专题五 Redis高并发场景
基于GBase8s和Calcite的多数据源查询
Redis——底层数据结构原理
上一篇文章      下一篇文章      查看所有文章
加:2022-12-25 11:16:02  更:2022-12-25 11:18:36 
 
开发: C++知识库 Java知识库 JavaScript Python PHP知识库 人工智能 区块链 大数据 移动开发 嵌入式 开发工具 数据结构与算法 开发测试 游戏开发 网络协议 系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑 笔记本 显卡 显示器 固态硬盘 硬盘 耳机 手机 iphone vivo oppo 小米 华为 单反 装机 图拉丁

360图书馆 购物 三丰科技 阅读网 日历 万年历 2024年12日历 -2024/12/25 2:52:57-

图片自动播放器
↓图片自动播放器↓
TxT小说阅读器
↓语音阅读,小说下载,古典文学↓
一键清除垃圾
↓轻轻一点,清除系统垃圾↓
图片批量下载器
↓批量下载图片,美女图库↓
  网站联系: qq:121756557 email:121756557@qq.com  IT数码
数据统计