关于数据库:千万级数据深分页查询SQL性能优化实践-京东云技术团队

一、零碎介绍和问题形容

如何在 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 语句会先扫描 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;

• 计划长处：防止了数据质变大时，页码查问深刻的性能降落问题；通过接口压测，千万级数据量时，前 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）就进行匹配，比方 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。

作者：京东批发 曹志飞

起源：京东云开发者社区 转载请注明起源