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

2023-11-13 14:03:41 浏览数 (1)

Tech 导读 分页查询在数据库中是一种很常见的应用场景，一般都可以使用limit语句快速实现。但是随着表数据的增长，limit查询性能也会越来越慢。

01

系统介绍和问题描述

在今年的敏捷团队建设中，我通过Suite执行器实现了一键自动化单元测试。Juint除了Suite执行器还有哪些执行器呢？由此我的Runner探索之旅开始了！

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

02

解决问题的思路和方法

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。

数据库表结构示例如下：

代码语言：javascript复制
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 = '关注粉丝表';

2.1 Limit实现

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

代码语言：javascript复制
select  id,biz_content,pin FROM follow_fans_1 where biz_content = #{bizContent} order by id desc limit 10, 10;
  • 方案优点：实现简单，支持跳页查询。
  • 方案缺点：数据量变大时，随着查询页码的深入，查询性能越来越差。

2.2 标签记录法

Limit深分页问题的本质原因就是：偏移量（offset）越大，mysql就会扫描越多的行，然后再抛弃掉，这样就导致查询性能的下降。所以可以采用标签记录法，就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。具体做法方式是，查询粉丝列表中按照自增主键ID倒序查询，查询结果中返回主键ID，然后查询入参中增加maxId参数，该参数需要透传上一次请求粉丝列表中最后一条记录主键ID，第一次查询时可以为空，但是需要查询下一页时就必传。最后根据查询时返回的行数是否等于 10 来判断整个查询是否可以结束。优化后的查询sql参考如下：

代码语言：javascript复制
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 条时，引擎不知道何时终止查询，只能遍历全表，所以当表中数据量很大时，还是会出现超时情况。

2.3 区间限制法

‍‍

标签记录法最后一页查询超时就是因为不知道何时终止查询，所以可以提供一个区间限制范围来告诉引擎查询到此结束。

查询sql再次优化后参考如下：

代码语言：javascript复制
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 参考如下：

代码语言：javascript复制
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。

03

对SQL优化治理的思考

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目标页面展示到屏幕。

通过对以上三种方案的探索实践，可以发现每一种方案都有自己的优缺点和它的适用场景，但不能脱离实际业务场景去谈方案的好坏。所以要结合实际的业务环境以及表中数据量的大小去综合考虑、权衡利弊，然后找到更适合的技术方案。以下是总结的几条SQL优化建议：

3.1 查询条件一定要有索引

索引主要分为两大类，聚簇索引和非聚簇索引，可以通过 explain 查看 sql 执行计划判断查询是否使用了索引。

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

1.如果表定义了主键，则主键索引就是聚簇索引；

2.如果没有定义主键，则第一个非空的唯一索引列是聚簇索引；

3.如果没有唯一索引，则创建一个隐藏的row-id列作为聚簇索引。主键索引查询非常快，可以直接定位行记录。

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

3.2 正确使用索引，防止索引失效

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

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.尽量扩展索引，不要新建索引。

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

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

04

总结

理解，首先 MCube 会依据模板缓存状态判断是否需要网络获取最新模板，当获取到模板后进行模板加载，加载阶段会将产物转换为视图树的结构，转换完成后将通过表达式引擎解析表达式并取得正确的值，通过事件解析引擎解析用户自定义事件并完成事件的绑定，完成解析赋值以及事件绑定后进行视图的渲染，最终将目

本文通过结合实际系统案例，详细介绍了分页查询的优化历程，一步步从最简单的limit分页实现，到最后满足千万级表数据的分页查询探索实现，并介绍每种技术方案的优缺点，希望可以帮助读者去选择适合自己的技术方案。

推荐阅读

搜狗输入法双击输入框崩溃问题

LangChain：打造自己的LLM应用

移动端APP组件化架构实践

JaCoCo助您毁灭线上僵尸代

打造SAAS化服务的会员徽章体系，可以作为标准的产品化方案统一对外输出。结合现有平台的通用能力，实现会员行为全路径覆盖，并能结合企业自身业务特点，规划相应的会员精准营销活动，提升会员忠诚度和业务的持续增长。

底层能力：维护用户基础数据、行为数据建模、用户画像分析、精准营销策略的制定

▪功能支撑：会员成长体系、等级计算策略、权益体系、营销底层能力支持

▪用户活跃：会员关怀、用户触达、活跃活动、业务线交叉获客、拉新促活

‍

‍

sql 分页 实践 数据 性能优化

0 人点赞