- Java锁的逻辑(结合对象头和ObjectMonitor)
- 还在用饼状图?来瞧瞧这些炫酷的百分比可视化新图形(附代码实现)⛵
- 自动注册实体类到EntityFrameworkCore上下文,并适配ABP及ABPVNext
- 基于Sklearn机器学习代码实战
如何在Mysql中实现上亿数据的遍历查询?先来介绍一下系统主角:关注系统,主要是维护京东用户和业务对象之前的关注关系;并对外提供各种关系查询,比如查询用户的关注商品或店铺列表,查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求,要求提供查询关注对象的粉丝列表接口功能。该功能的难点就是关注对象的粉丝数量过多,不少店铺的粉丝数量都是千万级别,并且有些大V粉丝数量能够达到上亿级别。而这些粉丝列表数据目前全都存储在Mysql库中,然后通过业务对象ID进行分库分表,所有的粉丝列表数据分布在16个分片的256张表中。同时为了方便查询粉丝列表,同一个业务对象的所有粉丝都会路由到同一张表中,每个表的数据量都能够达到 2 亿+.
数据库表结构示例如下:
CREATE TABLE follow_fans_[0-255]
(
id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
biz_content VARCHAR(50) DEFAULT NULL COMMENT '业务对象ID',
source VARCHAR(50) DEFAULT NULL COMMENT '来源',
pin VARCHAR(50) DEFAULT NULL COMMENT '用户pin',
ext VARCHAR(5000) DEFAULT NULL COMMENT '扩展信息',
status TINYINT(2) DEFAULT 1 COMMENT '状态,0是失效,1是正常',
created_time DATETIME DEFAULT NULL COMMENT '创建时间',
modified_time DATETIME DEFAULT NULL COMMENT '修改时间',
PRIMARY KEY(id),
UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)
)
ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '关注粉丝表';
由于同一个业务对象的所有粉丝都保存到一张数据库表中,对于分页查询列表接口,首先想到的就是用limit实现,对于粉丝数量很少的关注对象,查询接口性能还不错。但是随着关注对象的粉丝数量越来越多,接口查询性能就会越来越慢。后来经过接口压测,当业务对象粉丝列表数量达到几十万级别的时候,查询页码数量越大,查询耗时越多。limit深分页为什么会变慢?这就和sql的执行计划有关了,limit语句会先扫描offset+n行,然后再丢弃掉前offset行,返回后n行数据。也就是说 limit 100000,10 ,就会扫描100010行,而 limit 0,10 ,只扫描10行。查询 sql 示例如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} order by id desc limit 10, 10;
Limit深分页问题的本质原因就是:偏移量(offset)越大,mysql就会扫描越多的行,然后再抛弃掉,这样就导致查询性能的下降。所以我们可以采用标签记录法,就是标记一下上次查询到哪一条了,下次再来查的时候,从该条开始往下扫描。具体做法方式是,查询粉丝列表中按照自增主键ID倒序查询,查询结果中返回主键ID,然后查询入参中增加maxId参数,该参数需要透传上一次请求粉丝列表中最后一条记录主键ID,第一次查询时可以为空,但是需要查询下一页时就必传。最后根据查询时返回的行数是否等于 10 来判断整个查询是否可以结束。优化后的查询sql参考如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} order by id desc limit 10;
标签记录法最后一页查询超时就是因为不知道何时终止查询,所以我们可以提供一个区间限制范围来告诉引擎查询到此结束.
查询sql再次优化后参考如下:
select id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} and id < #{lastId} and id >={minId} order by id desc limit 10;
由于查询时需要带上 minId 参数,所以在执行查询粉丝列表之前,我们就需要先把 minId 查询出来,查询 sql 参考如下:
select min(id) from follow_fans_1 where biz_content = #{bizContent}
由于表中数据量太大,每个表中总数据量都是上亿级别,导致第一步查询 minId就直接超时了,根本没有机会去执行第二步。但是考虑到上一个查询方案只有最后一页才会查询超时,前N-1页查询根本用不到 minId 作为区间限制。所以当表中数据量很大时,通常从第一页到最后一页查询之间会存在一定的时间差。我们就可以正好去利用这个时间差去异步查询minId,然后将查询出来的minId存储到缓存中,考虑到这个 minId 可能会被删除,可以设置一定的过期时间。最后优化后的查询流程如下:
但是在上述方案中,如果表中的数据量达到上亿级别时,第二步的异步获取minId任务还是会存在超时的风险,从而导致查询最后一页粉丝列表出现超时。所以我们又引入了离线数据计算任务,通过在大数据平台离线计算获取每个biz_content下的minId,然后将计算结果minId推送到缓存中。为了保证minId能够及时更新,我们可以自由设置该离线任务的执行周期,比如每周执行一次。通过大数据平台的离线计算minId,从而大大减少了在查询粉丝列表时执行 select min(id)的业务数据库压力。只有当缓存没有命中的时候才去执行 select min(id),通常这些缓存没有命中的 minId 也都是一些被离线任务遗漏的少量数据,不会影响接口的整体查询性能.
通过对以上三种方案的探索实践,发现每一种方案都有自己的优缺点和它的适用场景,我们不能脱离实际业务场景去谈方案的好坏。所以我们要结合实际的业务环境以及表中数据量的大小去综合考虑、权衡利弊,然后找到更适合的技术方案。以下是总结的几条SQL优化建议:
索引主要分为两大类,聚簇索引和非聚簇索引,可以通过 explain 查看 sql 执行计划判断查询是否使用了索引.
聚簇索引 (clustered index):聚簇索引的叶子节点存储行记录,InnoDB必须要有且只有一个聚簇索引:
非聚簇索引 (secondary index):InnoDB非聚簇索引的叶子节点存储的是行记录的主键值,而MyISAM叶子节点存储的是行指针。 通常情况下,需要先遍历非聚簇索引获得聚簇索引的主键ID,然后在遍历聚簇索引获取对应行记录.
可以参考以下几点索引原则:
回表查询就是先定位主键值,在根据主键值定位行记录,需要扫描两遍索引。 解决方案:只需要在一颗索引树上能够获取SQL所需要的所有列数据,则无需回表查询,速度更快。可以将要查询的字段,建立到联合索引里去,这就是索引覆盖。查询sql在进行explain解析时,Extra字段为Using Index时,则触发索引覆盖。没有触发索引覆盖,发生了回表查询时,Extra字段为Using Index condition.
作者:京东零售 曹志飞 。
来源:京东云开发者社区 转载请注明来源 。
最后此篇关于千万级数据深分页查询SQL性能优化实践的文章就讲到这里了,如果你想了解更多关于千万级数据深分页查询SQL性能优化实践的内容请搜索CFSDN的文章或继续浏览相关文章,希望大家以后支持我的博客! 。
本文分享自华为云社区《大模型LLM之分布式训练》,作者: 码上开花_Lancer。 随着语言模型参数量和所需训练数据量的急速增长,单个机器上有限的资源已无法满足大语言模型训练的要求。需要设计分布式训
本文分享自华为云社区《五大基础算法--动态规划法》,作者: 大金(内蒙的)。 一、基本概念 动态规划法,和分治法极其相似。区别就是,在求解子问题时,会保存该子问题的解,后面的子问题求解时,可以直接拿来
pip install scp pip install pexpect 测试代码: import os import stat import paramiko # 用于调用scp命令 def s
我目前正在实现“ token ”REST 服务。 token 只是一个字符串,由一些参数构建而成,然后经过哈希处理并在一定时间后过期。 我想在我的 REST 服务中有一个可以验证 token 的端点,
打开软删除后,我在客户端上添加一条记录,推送,删除添加的记录推送,然后尝试使用与初始记录相同的主键添加新记录(然后推送),我得到一个异常(exception)。 EntityDomainManager
打开软删除后,我在客户端上添加一条记录,推送,删除添加的记录推送,然后尝试使用与初始记录相同的主键添加新记录(然后推送),我得到一个异常(exception)。 EntityDomainManager
我有一个应用程序,每 x 秒接收一次天气信息。我想将此数据保存到 XML 文件中。 我应该为每个天气通知创建一个新的 XML 文件,还是将每个通知附加到同一个 XML 文件中?我不确定 XML 标准的
我猜我们大多数人都必须在某个时候处理这个问题,所以我想我会问这个问题。 当您的 BLL 中有很多集合并且您发现自己一遍又一遍地编写相同的旧内联(匿名)谓词时,显然有必要进行封装,但实现封装的最佳方
我有一些 c# 代码已经运行了一段时间了..我不得不说,虽然我了解 OO 原则的基础知识,但显然有不止一种方法可以给猫剥皮(尽管我讨厌那个短语!)。 因此,我有一个基本抽象类作为基本数据服务类,如下所
我设计了一个 SQL 数据库系统(使用 Postgre),我有一个问题,即创建一个关系/引用的常见做法是什么,这种关系/引用即使在引用的对象被删除时也能持续存在。 比如有一个UserORM,还有Act
我们的目标是搜索用户输入的字符串并计算在其中找到多少元音。不幸的是我被困在这里,有什么帮助吗? def numVowels(s): vowels= "AEIOUaeiou" if s
我有一个适用于我的“items”int 数组的旋转函数。下面的代码完成了它,除了我不必要地传输值。我正在努力实现“就地”轮换。我的意思是 ptrs 会递增或递减,而不是从数组中获取值。我需要通过这种方
我有一个 json 存储在我的应用程序文档文件夹中,我需要在我的所有 View 中使用它。我正在加载 json 并将其添加到每个 View 中的 NSMutableArray。但现在我了解到,我可以将
我用 C++ 开始了一个项目。这种语言的内存管理对我来说是新的。 我过去常常使用 new () 创建对象,然后传递指针,虽然它可以工作,但调试起来很痛苦,人们看到代码时会用有趣的眼神看着我。我为它没有
已结束。 这个问题是 off-topic .它目前不接受答案。 想要改进这个问题? Update the question所以它是on-topic堆栈溢出。 关闭 10 年前。 Improve thi
保持类松散耦合是编写易于理解、修改和调试的代码的一个重要方面——我明白这一点。然而,作为一个新手,几乎任何时候我都会超越我所苦苦挣扎的最简单的例子。 我或多或少地了解如何将字符串、整数和简单数据类型封
我发现我需要编写大量重复代码,因为我无法从其他 Controller 调用函数。例如,这里新闻提要内容在我的代码中重复,我对一个 Controller 做一些特定的事情,然后需要像这样加载我的新闻提要
假设需要一种数字数据类型,其允许值在指定范围内。更具体地说,假设要定义一个整数类型,其最小值为0,最大值为5000。这种情况在很多情况下都会出现,例如在对数据库数据类型,XSD数据类型进行建模时。 在
假设我想循环整个数组来访问每个元素。使用 for 循环、for...in 循环或 for...of 循环是 JavaScript 开发人员的标准做法吗? 例如: var myArray = ["app
我有一个旧的 SL4/ria 应用程序,我希望用 Breeze 取代它。我有一个关于内存使用和缓存的问题。我的应用程序加载工作列表(一个典型的用户可以访问大约 1,000 个这些工作)。此外,还有很多
我是一名优秀的程序员,十分优秀!