千万级数据深分页查询SQL性能优化实践

青碧

一、系统介绍和问题描述

如何在Mysql中实现上亿数据的遍历查询？先来介绍一下系统主角：关注系统，主要是维护京东用户和业务对象之前的关注关系；并对外提供各种关系查询，比如查询用户的关注商品或店铺列表，查询用户是否关注了某个商品或店铺等。但是最近接到了一个新需求，要求提供查询关注对象的粉丝列表接口功能。该功能的难点就是关注对象的粉丝数量过多，不少店铺的粉丝数量都是千万级别，并且有些大V粉丝数量能够达到上亿级别。而这些粉丝列表数据目前全都存储在Mysql库中，然后通过业务对象ID进行分库分表，所有的粉丝列表数据分布在16个分片的256张表中。同时为了方便查询粉丝列表，同一个业务对象的所有粉丝都会路由到同一张表中，每个表的数据量都能够达到 2 亿+。
二、解决问题的思路和方法

数据库表结构示例如下：

1. CREATE TABLE follow_fans_[0-255]
2.   (
3.     id bigint(11) NOT NULL AUTO_INCREMENT COMMENT '自增id',
4.     biz_content   VARCHAR(50) DEFAULT NULL COMMENT '业务对象ID',
5.     source        VARCHAR(50) DEFAULT NULL COMMENT '来源',
6.     pin           VARCHAR(50) DEFAULT NULL COMMENT '用户pin',
7.     ext           VARCHAR(5000) DEFAULT NULL COMMENT '扩展信息',
8.     status        TINYINT(2) DEFAULT 1 COMMENT '状态，0是失效，1是正常',
9.     created_time  DATETIME DEFAULT NULL COMMENT '创建时间',
10.     modified_time DATETIME DEFAULT NULL COMMENT '修改时间',
11.     PRIMARY KEY(id),
12.     UNIQUE INDEX uniq_biz_content_pin (biz_content, pin)
13.   )
14.   ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8 COMMENT = '关注粉丝表';

复制代码

Limit实现

由于同一个业务对象的所有粉丝都保存到一张数据库表中，对于分页查询列表接口，首先想到的就是用limit实现，对于粉丝数量很少的关注对象，查询接口性能还不错。但是随着关注对象的粉丝数量越来越多，接口查询性能就会越来越慢。后来经过接口压测，当业务对象粉丝列表数量达到几十万级别的时候，查询页码数量越大，查询耗时越多。limit深分页为什么会变慢？这就和sql的执行计划有关了，limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。查询 sql 示例如下：

1. 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参考如下：

1. 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再次优化后参考如下：

1. 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 参考如下：

1. select min(id) from follow_fans_1 where biz_content = #{bizContent}

复制代码

由于表中数据量太大，每个表中总数据量都是上亿级别，导致第一步查询 minId就直接超时了，根本没有机会去执行第二步。但是考虑到上一个查询方案只有最后一页才会查询超时，前N-1页查询根本用不到 minId 作为区间限制。所以当表中数据量很大时，通常从第一页到最后一页查询之间会存在一定的时间差。我们就可以正好去利用这个时间差去异步查询minId，然后将查询出来的minId存储到缓存中，考虑到这个 minId 可能会被删除，可以设置一定的过期时间。最后优化后的查询流程如下：

• 调用查询粉丝列表方法时首先查询缓存minId；
  
• 如果缓存minId 为空，则创建异步任务去执行select min(id) 查询表中的 minId,然后回写缓存，该异步任务执行时间可能会很长，可以单独设置超时时间。
  
• 如果缓存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必须要有且只有一个聚簇索引：

• 如果表定义了主键，则主键索引就是聚簇索引；
  
• 如果没有定义主键，则第一个非空的唯一索引列是聚簇索引；
  
• 如果没有唯一索引，则创建一个隐藏的row-id列作为聚簇索引。主键索引查询非常快，可以直接定位行记录。
  

非聚簇索引 （secondary index)：InnoDB非聚簇索引的叶子节点存储的是行记录的主键值，而MyISAM叶子节点存储的是行指针。 通常情况下，需要先遍历非聚簇索引获得聚簇索引的主键ID，然后在遍历聚簇索引获取对应行记录。
正确使用索引，防止索引失效

可以参考以下几点索引原则：

• 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询（>、3 and d=4 ，如果建立了（a,b,c,d)顺序的索引，d是用不到索引的，如果建立（a,b,d,c)的索引则都可以用到，a、b、d的顺序可以任意调整。
  
• =和in可以乱序，比如 a=1 and b=2 and c=3 建立（a,b,c)索引可以任意顺序，mysql的查询优化器会帮助优化成索引可以识别的形式。
  
• 尽量选择区分度高德列作为索引，区分度公式count(distinct col)/count(*)，表示字段不重复的比例。
  
• 索引列不能使用函数或参与计算，不能进行类型转换，否则索引会失效。
  
• 尽量扩展索引，不要新建索引。
  

减少查询字段，避免回表查询

回表查询就是先定位主键值，在根据主键值定位行记录，需要扫描两遍索引。 解决方案：只需要在一颗索引树上能够获取SQL所需要的所有列数据，则无需回表查询，速度更快。可以将要查询的字段，建立到联合索引里去，这就是索引覆盖。查询sql在进行explain解析时，Extra字段为Using Index时，则触发索引覆盖。没有触发索引覆盖，发生了回表查询时，Extra字段为Using Index condition。

作者：京东零售 曹志飞
来源：京东云开发者社区 转载请注明来源

来源:https://www.cnblogs.com/jingdongkeji/p/17650823.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！