天猫权益平台如何10倍的提升数据库查询响应时间

  1. 云栖社区>
  2. 博客列表>
  3. 正文

天猫权益平台如何10倍的提升数据库查询响应时间

友德 2018-07-11 09:38:14 浏览878 评论0

摘要: 背景: 上周双旦项目上线完成,在上线过程中,遇到了一系统的问题,我个人这边主要遇到的问题是: 前端查询奖品的相关信息,包括奖品的库存信息,这一块大概的需求场景是这样的: 一个真实的奖品有多个虚拟的批次库存信息,每个批次库存信息由一个资格数来指定有允许有多少个人来抽奖,只有指定的人数满足以后,才可以开奖,开奖完成之后,才能进入一下批次库存信息进行抽奖活动。

背景:

上周双旦项目上线完成,在上线过程中,遇到了一系统的问题,我个人这边主要遇到的问题是: 前端查询奖品的相关信息,包括奖品的库存信息,这一块大概的需求场景是这样的: 一个真实的奖品有多个虚拟的批次库存信息,每个批次库存信息由一个资格数来指定有允许有多少个人来抽奖,只有指定的人数满足以后,才可以开奖,开奖完成之后,才能进入一下批次库存信息进行抽奖活动。

拿单个奖品查询的sql来说,sql是这样的:

select
        total as total,
        bestow as bestow,
        benefit_id as benefitId,
        sub_type_id as subTypeId,
        id as id
from
        bp_inventory
where
        benefit_id = #{benefitId} and
        type=#{type} and
        total > bestow 
order by id limit 1

即查询指定奖品当前正在消费的批次库存
bp_inventory在(benefit_id, type, sub_type_id)三个列上有唯一索引, 如下图所示:
image.png

在每个benefit_id对应的批次库存数量不是很大的情况下,查询应该是非常快的,如下图所示:
image.png
从上图中可以看出,查询一次消耗的时间差不多在2ms左右,是一个典型的快sql查询,接下来问题来了: 项目中真实的场景是,根据前端传进来的activityCode去解析出一批奖品的id,一个activityCode解析出奖品id的个数,少的情况有5-6个,平均有30个左右,最多有120左右,很显然,用单个奖品串行去查数据库是不能接受的,因为有的奖品的批次库存比较多,根据limit 1查询的时间比较长, 串行查20个大概需要150-250ms左右,如下所示:
image.png
这个时间响应是不能接受的,毕竟响应时间平均超过30ms,用户就可以有明显慢的感觉,在这种情况下一次用户的首页的请求,查询120个奖品需要消耗的时间大概在400ms+,为了优化这个响应时间,首先想到一个最简单的优化办法,用group by语法小批量并行异步查询数据库, sql如下:

 select
          total as total,
          bestow as bestow,
          benefit_id as benefitId,
          sub_type_id as subTypeId,
          id as id
        from
            bp_inventory
        WHERE
            benefit_id in
        <foreach collection="benefitIds" index="index" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
        and type=#{type} and total > bestow  group by benefit_id
    </select>

sql的执行计划如下:
image.png
从执行计划可以看出,确实是走到索引, 查询效果如下:
image.png
小批次查询9个奖品的批次库存信息消耗5ms,这也是一个典型的快sql, 通过这次优化,查询响应时间可以控制在100ms左右
如果查询时间都能控制在100ms左右,也没什么问题,终于有一天,问题爆发了,有3个上万的库存奖品上线,批次库存量分别是: 10000, 10000, 50000,并且这三个奖品都是在同一个活动下面,小批次查询库存的sql如下:
image.png
小批量sql查询的消耗时间一下子跳到了200ms+, 当时人有点凝惑,这是怎么回事,还是先看一下这条SQL执行计划:
image.png
刚开始只注意到possible_keys 和 key这两列,是走到(benefit_id, type, sub_type_id)这个索引,仔细分析发现extra这一列不太一样,mysql提示是using where,显然实际上mysql执行这条sql语句是没有走到索引,而是用全表扫描的方式,进一步分析发现,这三个批次库厚的总数相加在7w,而整个表的的总数据行数是18w左右,显然, 已经超过索引区分度30%的上限,因此mysql在物理查询优化阶段才会去用全表扫描的方式去查询,问题原因终于定位到了,下面就来想方案来解决这个大库存查询慢的问题。

即然大库存用小指量查询的速度比较慢,会导致有大量的慢性sql问题,

方案一:

首先想到的方案就是把这些大库存的奖品全部隔离,大库存的奖品单独查询,小库存的奖品小用指量查询,这个方法最快,上线后,线上情况马上稳定 ,但这带来一个问题,遇到大库存时,单条查询的时间依然比较慢,基本上都在100ms+以上,这个不能接受,只能继续优化;

方案二:

用mysql二级索引的方式不能从根本上解决大库存奖品查询慢的问题,但是我们业务场景是每个奖品的批次库存是根据id从小到大依次消费,有这个业务上的约束条件之后,就好处理了,能不能把大库存奖品的所有有效的批次库存信息的id存储在每台机器本地,根据id去查询大库存奖品当前有效的库存信息,这个方案看起来比较靠谱,即结合了实际的业务场景,又充分利用了数据库的一级索引,方案定来以后,说干就干,经过3个小时紧张的奋战,这个方案终于在预发上验证通过了,主要的思路如下:

1. 添加一个switch开发,配置上大库存奖品的id,因为大库存奖品我们通过数据库查询出来是已知的:

@AppSwitch(des = "2018双旦项目大的benefitId", level = Switch.Level.p2)
public static Set<Long> new_year_2018_big_benefitIds = new HashSet<Long>();

2. 在每个机器上添加一个本地的定时任务, 这个任务定时的从数据库捞取最近10min新生效的批次库存信息

 /**
     * 权益对应的锁
  */
private ConcurrentMap<Long, Object>  benefitIdLocks = new ConcurrentHashMap<Long, Object>();

    /**
     * 权益批次inventory信息
     */
private ConcurrentHashMap<Long, BenefitItem> benefitIdsMap = new ConcurrentHashMap<Long, BenefitItem>(32);

 /**
  * 每个奖品对应的批次库存统计信息的内部类定义
 */
private class BenefitItem {

        public List<Long> ids;

        public Long nextMinId;

        public BenefitItem() { }

        public BenefitItem(List<Long> ids, Long nextMinId) {
            this.ids = ids;
            this.nextMinId = nextMinId;
        }
  }

 //定时任务
 scheduledExecutorService = Executors.newSingleThreadScheduledExecutor();
 // 10分钟执行一次
 scheduledExecutorService.scheduleAtFixedRate(new Runnable() {
       @Override
       public void run() {
                // 初始化权益
                for (Long benefitId : new_year_2018_big_benefitIds) {
                    benefitIdLocks.putIfAbsent(benefitId, new Object());
                }
                for (Long benefitId : benefitIdLocks.keySet()) {
                    if (!new_year_2018_big_benefitIds.contains(benefitId)) {
                        benefitIdLocks.remove(benefitId);
                    }
                }
                // 权益对应的有效库存
           for (Long benefitId : new_year_2018_big_benefitIds) {
                    try {
                        BenefitItem benefitItem = benefitIdsMap.get(benefitId);
                        if (benefitItem == null) {
                            benefitItem = new BenefitItem(new LinkedList<Long>(), 0L);
                            benefitIdsMap.put(benefitId, benefitItem);
                        }
                        Long nextMinId = benefitItem.nextMinId;
                        List<Long> currentIds = benefitRepository.batchQueryInventoryIdByBenefitIdOfpage(benefitId, SPECIAL_TYPE, nextMinId, 500);
                        while (currentIds != null && currentIds.size() > 0) {
                            synchronized (benefitIdLocks.get(benefitId)) {
                                benefitItem.ids.addAll(currentIds);
                            }
                            nextMinId = benefitItem.nextMinId = currentIds.get(currentIds.size() - 1);
                            currentIds = benefitRepository.batchQueryInventoryIdByBenefitIdOfpage(benefitId, SPECIAL_TYPE, nextMidId, 500);
                        }
                    } catch (Exception e) {
                        logger.error("scheduleWithFixedDelay failed e=", e);
                    }
                }
                for (Long benefitId : benefitIdsMap.keySet()) {
                    if (!new_year_2018_big_benefitIds.contains(benefitId)) {
                        benefitIdsMap.remove(benefitId);
                    }
                }
            }
 }, 0, 10, TimeUnit.MINUTES);

每次查询都会记录下当前最大的nextMinId,下次查询时从这个nextMinId开始查询,这也是一条非常高效的sql,因此不会对数据库造成任何的压力,查询sql如下:

 select
       id as id
 from
            bp_inventory
WHERE
            benefit_id = #{benefitId} and type=#{type} and id > #{minId} and total > bestow
order by id
limit  #{pageSize};

3.在查询大商品库存时的主要代码如下:

private BenefitInventoryVO processForSingle(Long benefitId) {

        BenefitInventoryVO  benefitInventoryVO = null;
        try {
            if (benefitIdLocks.containsKey(benefitId)) {
                Long inventoryId = null;
                if (benefitIdsMap.get(benefitId).ids.size() > 0) {
                    inventoryId = benefitIdsMap.get(benefitId).ids.get(0);
                }
                // 防止已经消费完成
                if (inventoryId != null) {
                    benefitInventoryVO = benefitRepository.queryInventoryDOById(inventoryId);
                }
                //  一般情况只有2-3次,可以优化查询次数,第二次可以多查几条,直接判断
                while ( benefitInventoryVO != null &&
                         benefitInventoryVO.getTotal() != null &&
                         benefitInventoryVO.getTotal() > 0 &&
                         benefitInventoryVO.getTotal().equals(benefitInventoryVO.getBestow()) ) {  // 本批次已经消费完成

                    if (benefitIdsMap.get(benefitId).ids.size() > 0) {
                        synchronized (benefitIdLocks.get(benefitId)) {
                            // 删除第一个
                            if (benefitIdsMap.get(benefitId).ids.size() > 0) {
                                benefitIdsMap.get(benefitId).ids.remove(0);
                            }
                        }
                    }
                    if (benefitIdsMap.get(benefitId).ids.size() > 0) {
                        inventoryId = benefitIdsMap.get(benefitId).ids.get(0);
                    } else {
                        inventoryId = null;
                    }
                    // 库存已经用完了
                    if (inventoryId  == null) {
                        break;
                    }
                    // 查数据库
                    benefitInventoryVO = benefitRepository.queryInventoryDOById(inventoryId);
                }
            } else {
                // 条件查询数据库
                benefitInventoryVO = benefitRepository.singleQueryInventoryDTO(benefitId, SPECIAL_TYPE);
            }
            if (benefitInventoryVO == null) {
                benefitInventoryVO = BenefitInventoryVO.of(0, 0, 0L, benefitId);
            }
            return benefitInventoryVO;
        } catch (Exception e) {
            logger.error("processForSinle error benefitId={}, e={}",benefitId, e);
            return BenefitInventoryVO.of(0, 0, 0L, benefitId);
        }
    }

思想很简单,如果当前id对应的批次库存已经消费完成了,就从列表取下一批,直到找到有效的为止,一般情况下是2-3次根据id查询,因为根据主键id查询数据库的表的速度非常非常的高效,就算多查几次,也不会有任何的性能问题
下面是优化前和优化后查询性能的对比:
优化前:
image.png
优化后:
image.png
上线前后查询性能对比图如下:
image.png
数据库的平均响应时间提高了10倍以上, HSF服务响应时间绝大部分终于压制住在20ms以下;

因写作时间比较仓促,有些地方写得不是很详细,有问题线下单独沟通。
其实核心的地方,大家只需要注意这几地方:

  1. mysql的数据根据索引的区分度超过30%, 默认不是会走索引的,如果要强制走索引,可以用force index语法来实现,不过不建议这么用,因为在这种情况下,用索引查询也会很慢性;
  2. 遇到了区分度超过30%的数据,要根据实际情况来分析解析,例如用canal就是一个不错的解决方案,也很方便;
  3. 遇到慢sql,不要慌乱,要定到具体的慢sql,以及相关的原因,对症下药,实在不行,说明数据模型上有问题,需要重新评估原来的数据模型;
    4.平时多积累一下mysql优仳器的原理知识,建议大家有空看一下李海翔老师的数据库方面的巨作<<数据库查询优化器艺术>>

image.png

【云栖快讯】阿里云栖开发者沙龙(Java技术专场)火热来袭!快来报名参与吧!  详情请点击

网友评论

友德
文章10篇 | 关注16
关注
Node.js 性能平台(Node.js Performance Platform)是面向中... 查看详情
快速、完全托管的TB/PB级数据仓库解决方案,向用户提供了完善的数据导入方案以及多种经典的分... 查看详情
提供海量、安全和高可靠的云存储服务。RESTful API的平台无关性,容量和处理能力的弹性... 查看详情
为您提供简单高效、处理能力可弹性伸缩的计算服务,帮助您快速构建更稳定、安全的应用,提升运维效... 查看详情
双12

双12