mysql数据库响应时间平均值sql

转载

编程艺术之光 2024-10-09 08:53:30

文章标签 mysql数据库响应时间平均值sql 数据库前端 ViewUI sql 文章分类 MySQL 数据库

背景：

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

拿单个奖品查询的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)三个列上有唯一索引, 如下图所示：

mysql数据库响应时间平均值sql_sql

在每个benefit_id对应的批次库存数量不是很大的情况下，查询应该是非常快的，如下图所示：

mysql数据库响应时间平均值sql_sql_02

从上图中可以看出，查询一次消耗的时间差不多在2ms左右，是一个典型的快sql查询，接下来问题来了：项目中真实的场景是，根据前端传进来的activityCode去解析出一批奖品的id，一个activityCode解析出奖品id的个数，少的情况有5-6个，平均有30个左右，最多有120左右，很显然，用单个奖品串行去查数据库是不能接受的，因为有的奖品的批次库存比较多，根据limit 1查询的时间比较长, 串行查20个大概需要150-250ms左右，如下所示:

mysql数据库响应时间平均值sql_mysql数据库响应时间平均值sql_03

这个时间响应是不能接受的，毕竟响应时间平均超过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的执行计划如下：

mysql数据库响应时间平均值sql_数据库_04

从执行计划可以看出，确实是走到索引, 查询效果如下：

mysql数据库响应时间平均值sql_mysql数据库响应时间平均值sql_05

小批次查询9个奖品的批次库存信息消耗5ms，这也是一个典型的快sql, 通过这次优化，查询响应时间可以控制在100ms左右

如果查询时间都能控制在100ms左右，也没什么问题，终于有一天，问题爆发了，有3个上万的库存奖品上线，批次库存量分别是： 10000， 10000， 50000，并且这三个奖品都是在同一个活动下面，小批次查询库存的sql如下：

mysql数据库响应时间平均值sql_数据库_06

小批量sql查询的消耗时间一下子跳到了200ms+, 当时人有点凝惑，这是怎么回事，还是先看一下这条SQL执行计划：

mysql数据库响应时间平均值sql_mysql数据库响应时间平均值sql_07

刚开始只注意到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查询数据库的表的速度非常非常的高效，就算多查几次，也不会有任何的性能问题

下面是优化前和优化后查询性能的对比：

优化前：

mysql数据库响应时间平均值sql_数据库_08

优化后：

mysql数据库响应时间平均值sql_前端_09

上线前后查询性能对比图如下：

mysql数据库响应时间平均值sql_数据库_10

数据库的平均响应时间提高了10倍以上， HSF服务响应时间绝大部分终于压制住在20ms以下；

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

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

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。