首页 星云 工具 资源 星选 资讯 热门工具
:

PDF转图片 完全免费 小红书视频下载 无水印 抖音视频下载 无水印 数字星空

数据库系列:巨量数据表的分页性能问题

编程知识
2024年07月24日 08:15

相关文章

数据库系列:MySQL慢查询分析和性能优化
数据库系列:MySQL索引优化总结(综合版)
数据库系列:高并发下的数据字段变更
数据库系列:覆盖索引和规避回表
数据库系列:数据库高可用及无损扩容
数据库系列:使用高区分度索引列提升性能
数据库系列:前缀索引和索引长度的取舍
数据库系列:MySQL引擎MyISAM和InnoDB的比较
数据库系列:InnoDB下实现高并发控制
数据库系列:事务的4种隔离级别
数据库系列:RR和RC下,快照读的区别
数据库系列:MySQL InnoDB锁机制介绍
数据库系列:MySQL不同操作分别用什么锁?
数据库系列:业内主流MySQL数据中间件梳理

1 背景

前段时间面试新员工,跟候选人沟通起来分页性能问题,正好之前遇到过这类问题,就拿出来再讨论下!

2 分析

分页性能问题,特别是在数据量大的情况下,是一个常见的问题。通常,当我们使用类似 LIMITOFFSET 的SQL语句进行分页时,性能问题尤其明显。这是因为随着 OFFSET 的增加,数据库需要跳过更多的行才能获取到需要的数据,这导致了查询时间的增加。

我们在查看前几页的时候,发现速度非常快,比如 limit 200,25,瞬间就出来了。但是越往后,速度就越慢,特别是百万条之后,卡到不行,那这个是什么原理呢。先看一下我们翻页翻到后面时,查询的sql是怎样的:

1 select * from t_name where c_name1='xxx' order by c_name2 limit 2000000,25;

这种查询的慢,其实是因为limit后面的偏移量太大导致的。比如像上面的 limit 2000000,25 ,这个等同于数据库要扫描出 2000025 条数据,然后再丢弃前面的 20000000 条数据,返回剩下25条数据给用户,这种取法明显不合理。

image

在《高性能MySQL》第六章:查询性能优化,对这个问题有过详细说明:

分页操作通常会使用limit加上偏移量的办法实现,同时再加上合适的order by子句。但这会出现一个常见问题:当偏移量非常大的时候,它会导致MySQL扫描大量不需要的行然后再抛弃掉。

3 优化

以下是一些优化分页性能的策略:
1. 使用索引+子查询优化
确保你的查询涉及的列(尤其是用于排序和过滤的列)都被索引,没有索引的列会导致数据库进行全表扫描,这会大大降低查询性能。
确保有索引之后,可以在索引树中找到开始位置的 id值,再根据找到的id值查询行数据。

[SQL]
SELECT a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id >= (select id from emp order by id limit 100,1)
order by a.id limit 25;
受影响的行: 0
时间: 0.106s

2. 使用更有效的分页技术
考虑使用基于游标或键的分页而不是基于 OFFSET 的分页。例如,如果你正在根据时间戳或ID排序,你可以记住上一页最后一个条目的时间戳或ID,并从那里开始下一页的查询。

记住上次查找结果的主键位置,避免使用偏移量 offset

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 100 order by a.id limit 25;
受影响的行: 0
时间: 0.001s

[SQL]
SELECT a.id,a.empno,a.empname,a.job,a.sal,b.depno,b.depname
from emp a left join dep b on a.depno = b.depno
where a.id > 4800000
order by a.id limit 25;
受影响的行: 0
时间: 0.000s

3. 减少返回的数据量
只选择需要的列,而不是使用 SELECT * , 减少数据量可以显著提高查询速度。
这个好理解,获取数据,越精简越好,千万别都fetch回来,MySQL准入规范也是这么定的。

4. 分区表
对于非常大的表,考虑使用分区技术。通过将数据分布到不同的分区,可以提高查询性能,因为查询可以在更小的数据集上操作。

5. 使用缓存
对于经常访问的页面,可以考虑使用缓存技术,如Redis或Memcached,来存储查询结果。这样,对于相同的查询请求,可以直接从缓存中获取结果,而不是每次都查询数据库。

6. 考虑物理设计
数据库的物理设计,如硬盘的速度和类型(SSD vs HDD),服务器的内存大小,也会影响查询性能。

4 总结

通过实施上述策略,你可以显著提高数据库分页的性能,尤其是在处理大量数据时,每种方法都有其适用场景,因此我们需要根据具体需求和数据库环境来选择合适的优化策略。

From:https://www.cnblogs.com/wzh2010/p/18030883
本文地址: http://shuzixingkong.net/article/356
0评论
提交 加载更多评论
其他文章 从零开始写 Docker(十九)---增加 cgroup v2 支持
本文为从零开始写 Docker 系列第十九篇,添加对 cgroup v2 的支持。 完整代码见:https://github.com/lixd/mydocker 欢迎 Star 推荐阅读以下文章对 docker 基本实现有一个大致认识: 核心原理:深入理解 Docker 核心原理:Namespace
从零开始写 Docker(十九)---增加 cgroup v2 支持 从零开始写 Docker(十九)---增加 cgroup v2 支持
Windows版本免费PyMol的安装
接上一篇介绍Linux下安装和使用免费版本的PyMol之后,这里再介绍一下Windows系统下的安装方法。同时在本文中列举了一些在PyMol中有可能使用到的脚本指令,例如设置球体模型的大小、设置表面模型的透明度、平移分子和批量执行脚本等操作。
Windows版本免费PyMol的安装 Windows版本免费PyMol的安装
文件系统(十):一文看懂 UBI 文件系统
liwen01 2024.07.21 前言 UBI (Unsorted Block Images)文件系统是一种用于裸 flash 的文件系统管理层。它是专为管理原始闪存设备而设计,特别适用于嵌入式系统。与 YAFFS2 和 JFFS2 不同的是,它可以提供整个 flash 空间
文件系统(十):一文看懂 UBI 文件系统 文件系统(十):一文看懂 UBI 文件系统 文件系统(十):一文看懂 UBI 文件系统
【干货】流量录制回放工具:jvm-sandbox-repeater
在软件开发和测试过程中,我们经常会遇到需要对网络请求进行录制和回放的需求,以便进行调试、测试和分析。为了模拟真实的用户请求,我们通常会使用各种流量录制回放工具来记录并重放网络请求。 其中,jvm-sandbox-repeater 是一款功能强大的流量录制回放工具,可以帮助我们轻松实现对网络请求的录制
【干货】流量录制回放工具:jvm-sandbox-repeater 【干货】流量录制回放工具:jvm-sandbox-repeater 【干货】流量录制回放工具:jvm-sandbox-repeater
DSCL:已开源,北京大学提出解耦对比损失 | AAAI 2024
监督对比损失(SCL)在视觉表征学习中很流行。但在长尾识别场景中,由于每类样本数量不平衡,对两类正样本进行同等对待会导致类内距离的优化出现偏差。此外,SCL忽略了负样本之间的相似关系的语义线索。为了提高长尾识别的性能,论文通过解耦训练目标来解决SCL的这两个问题,将SCL中的原正样本和增强正样本解耦
DSCL:已开源,北京大学提出解耦对比损失 | AAAI 2024 DSCL:已开源,北京大学提出解耦对比损失 | AAAI 2024 DSCL:已开源,北京大学提出解耦对比损失 | AAAI 2024
机器学习:详解多任务学习(Multi-task learning)
详解多任务学习 在迁移学习中,步骤是串行的,从任务\(A\)里学习只是然后迁移到任务\(B\)。在多任务学习中,是同时开始学习的,试图让单个神经网络同时做几件事情,然后希望这里每个任务都能帮到其他所有任务。 来看一个例子,假设在研发无人驾驶车辆,那么无人驾驶车可能需要同时检测不同的物体,比如检测行人
机器学习:详解多任务学习(Multi-task learning) 机器学习:详解多任务学习(Multi-task learning) 机器学习:详解多任务学习(Multi-task learning)
哥哥来救你了:从博客园发求救信说起
一.事件回放二.主要问题剖析三.对博客园团队的忠告 一.事件回放7 月 15 日,知名的经典博客站点 —— 博客园再次发布了求救信,大概的意思是说现在园子又到了生死攸关的时候,需要开发者开通会员来相救。我用红圈给大家标注了一些重点: 可以看出园子现在真的是很难了。。。不管怎么样,作为在博客园多年的作
哥哥来救你了:从博客园发求救信说起 哥哥来救你了:从博客园发求救信说起 哥哥来救你了:从博客园发求救信说起
辅助分类器生成对抗网络( Auxiliary Classifier Generative Adversarial Network,ACGAN)(附带pytorch代码)
ACGAN相对于CGAN使的判别器不仅可以判别真假,也可以判别类别 。通过对生成数据类别的判断,判别器可以更好地传递loss函数使得生成器能够更加准确地找到label对应的噪声分布。
辅助分类器生成对抗网络( Auxiliary Classifier Generative Adversarial Network,ACGAN)(附带pytorch代码) 辅助分类器生成对抗网络( Auxiliary Classifier Generative Adversarial Network,ACGAN)(附带pytorch代码) 辅助分类器生成对抗网络( Auxiliary Classifier Generative Adversarial Network,ACGAN)(附带pytorch代码)