wordpress文档下载,网络优化报告,旅游网站建设经费预算,seo与网站优化一、系统介绍和问题描述
如何在Mysql中实现上亿数据的遍历查询#xff1f;先来介绍一下系统主角#xff1a;关注系统#xff0c;主要是维护京东用户和业务对象之前的关注关系#xff1b;并对外提供各种关系查询#xff0c;比如查询用户的关注商品或店铺列表#xff0c;查…一、系统介绍和问题描述
如何在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实现对于粉丝数量很少的关注对象查询接口性能还不错。但是随着关注对象的粉丝数量越来越多接口查询性能就会越来越慢。后来经过接口压测当业务对象粉丝列表数量达到几十万级别的时候查询页码数量越大查询耗时越多。limit深分页为什么会变慢这就和sql的执行计划有关了limit语句会先扫描offsetn行然后再丢弃掉前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;
•方案优点避免了数据量变大时页码查询深入的性能下降问题经过接口压测千万级数据量时前 N-1页查询耗时可以控制在几十毫秒内。
•方案缺点只能支持按照页码顺序查询不支持跳页而且仅能保证前 N-1 页的查询性能如果最后一页的表中行数量不满 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 可能会被删除可以设置一定的过期时间。最后优化后的查询流程如下
1.调用查询粉丝列表方法时首先查询缓存minId
2.如果缓存minId 为空则创建异步任务去执行select min(id) 查询表中的 minId,然后回写缓存该异步任务执行时间可能会很长可以单独设置超时时间。
3.如果缓存minId不为空则在查询sql中拼接查询条件id {minId}从而保证查询最后一页时不会超时。
但是在上述方案中如果表中的数据量达到上亿级别时第二步的异步获取minId任务还是会存在超时的风险从而导致查询最后一页粉丝列表出现超时。所以我们又引入了离线数据计算任务通过在大数据平台离线计算获取每个biz_content下的minId,然后将计算结果minId推送到缓存中。为了保证minId能够及时更新我们可以自由设置该离线任务的执行周期比如每周执行一次。通过大数据平台的离线计算minId从而大大减少了在查询粉丝列表时执行 select min(id的业务数据库压力。只有当缓存没有命中的时候才去执行 select min(id)通常这些缓存没有命中的 minId 也都是一些被离线任务遗漏的少量数据不会影响接口的整体查询性能。
•方案优点避免了数据量变大时页码查询深入的性能下降问题经过接口压测千万级数据量时从第一页到最后一页都控制在几十毫秒内。
•方案缺点只能支持按照页码顺序和主键ID倒序查询不支持跳页查询并且还需要依赖大数据平台离线计算和额外的缓存来存储 minId。
三、对SQL优化治理的思考
通过对以上三种方案的探索实践发现每一种方案都有自己的优缺点和它的适用场景我们不能脱离实际业务场景去谈方案的好坏。所以我们要结合实际的业务环境以及表中数据量的大小去综合考虑、权衡利弊然后找到更适合的技术方案。以下是总结的几条SQL优化建议
查询条件一定要有索引
索引主要分为两大类聚簇索引和非聚簇索引可以通过 explain 查看 sql 执行计划判断查询是否使用了索引。
聚簇索引 (clustered index)聚簇索引的叶子节点存储行记录InnoDB必须要有且只有一个聚簇索引
1.如果表定义了主键则主键索引就是聚簇索引
2.如果没有定义主键则第一个非空的唯一索引列是聚簇索引
3.如果没有唯一索引则创建一个隐藏的row-id列作为聚簇索引。主键索引查询非常快可以直接定位行记录。
非聚簇索引 secondary index)InnoDB非聚簇索引的叶子节点存储的是行记录的主键值而MyISAM叶子节点存储的是行指针。 通常情况下需要先遍历非聚簇索引获得聚簇索引的主键ID然后在遍历聚簇索引获取对应行记录。
正确使用索引防止索引失效
可以参考以下几点索引原则
1.最左前缀匹配原则mysql会一直向右匹配直到遇到范围查询、、between、like就停止匹配比如 a1 and b2 and c3 and d4 如果建立了a,b,c,d)顺序的索引d是用不到索引的如果建立a,b,d,c)的索引则都可以用到a、b、d的顺序可以任意调整。
2.和in可以乱序比如 a1 and b2 and c3 建立a,b,c)索引可以任意顺序mysql的查询优化器会帮助优化成索引可以识别的形式。
3.尽量选择区分度高德列作为索引区分度公式count(distinct col)/count(*)表示字段不重复的比例。
4.索引列不能使用函数或参与计算不能进行类型转换否则索引会失效。
5.尽量扩展索引不要新建索引。
减少查询字段避免回表查询
回表查询就是先定位主键值在根据主键值定位行记录需要扫描两遍索引。 解决方案只需要在一颗索引树上能够获取SQL所需要的所有列数据则无需回表查询速度更快。可以将要查询的字段建立到联合索引里去这就是索引覆盖。查询sql在进行explain解析时Extra字段为Using Index时则触发索引覆盖。没有触发索引覆盖发生了回表查询时Extra字段为Using Index condition。 作者京东零售交易研发 曹志飞 来源京东零售技术 转载请注明来源
