作者：京东零售 曹志飞。

一、体系介绍和问题描绘。

如安在Mysql中完成上亿数据的遍历查询？先来介绍一下体系主角：重视体系，首要是保护京东用户和事务目标之前的重视联系；并对外供给各种联系查询，比方查询用户的重视。产品。或店肆列表，查询用户是否重视了某个产品或店肆等。可是最近接到了一个新需求，要求供给查询重视目标的粉丝列表。接口。功用。该功用的难点便是重视目标的粉丝数量过多，不少店肆的粉丝数量都是千万等级，而且有些大V粉丝数量能够到达上亿等级。而这些粉丝列表数据现在全都存储在Mysql库中，然后经过事务目标ID进行分库分表，一切的粉丝列表数据散布在16个分片的256张表中。一起为了便利查询粉丝列表，同一个事务目标的一切粉丝都会路由到同一张表中，每个表的数据量都能够到达 2 亿+。

二、解决问题的思路和办法。

数据库表结构示例如下：

CREATE TABLE follow_fans_[0-255]  (    id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',    biz_con。te。nt   VARCHAR(50) DEFAULT NULL COMMENT '事务目标ID',    source        VARCHAR(50) DEFAULT NULL COMMENT '来历',    pi。n           VARCHAR(50) DEFAULT NULL COMMENT '用户pin',    ext           VARCHAR(5000) DEFAULT NULL COMMENT '扩展。信息。',    status。        TI。NYINT(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 = u。tf。8 COMMENT = '重视粉丝表';

Limit完成。

因为同一个事务目标的一切粉丝都保存到一张数据库表中，关于分页查询列表接口，首要想到的便是用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}。 or。der 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优化主张：

查询条件必定要有。索引。

索引首要分为两大类，聚簇索引和非聚簇索引，能够经过 expl。ai。n 检查 sql 履行计划判别查询是否运用了索引。

聚簇索引 (clustered index)：聚簇索引的叶子节点存储行记载，InnoDB有必要要有且只要一个聚簇索引：

1.假如表界说了主键，则主键索引便是聚簇索引；

2.假如没有界说主键，则第一个非空的仅有索引列是聚簇索引；

3.假如没有仅有索引，则创立一个躲藏的row-id列作为聚簇索引。主键索引查询非常快，能够直接定位行记载。

非聚簇索引 （secondary index)：InnoDB非聚簇索引的叶子节点存储的是行记载的主键值，而MyISAM叶子节点存储的是行指针。 一般状况下，需求先遍历非聚簇索引取得聚簇索引的主键ID，然后在遍历聚簇索引获取对应行记载。

正确运用索引，防止索引失效。

能够参阅以下几点索引准则：

1.最左前缀匹配准则，mysql会一向向右匹配直到遇到规模查询（>、。<、between、like）就停止匹配，比如 a=1 and b=2 and c>3 and d=4 ，假如树立了（a,b,c,d)次序的索引，d是用不到索引的，假如树立（a,b,d,c)的索引则都能够用到，a、b、d的次序能够恣意调整。

2.=和in能够乱序，比方 a=1 and b=2 and c=3 树立（a,b,c)索引能够恣意次序，mysql的查询优化器会协助优化成索引能够辨认的方式。

3.尽量挑选区分度高德列作为索引，区分度公式count(distinct col)/count(*)，表明字段不重复的份额。

4.索引列不能运用函数或参加核算，不能进行类型转化，不然索引会失效。

5.尽量扩展索引，不要新建索引。

削减查询字段，防止回表查询。

回表查询便是先定位主键值，在依据主键值定位行记载，需求扫描两遍索引。 解决计划：只需求在一颗索引树上能够获取SQL所需求的一切列数据，则无需回表查询，速度更快。能够即将查询的字段，树立到联合索引里去，这便是索引掩盖。查询sql在进行explain解析时，Extra字段为Using Index时，则触发索引掩盖。没有触发索引掩盖，发生了回表查询时，Extra字段为Using Index condition。

审阅修改 黄宇。

内容来源：https://postapi.nlsngoisaoviet.com/app/app-1/tai game b52,http://chatbotjud.saude.mg.gov.br/app-1/futmax-gratis

(责任编辑：女性)