背景：
上周双旦项目上线完成，在上线过程中，遇到了一系统的问题，我个人这边主要遇到的问题是： 前端查询奖品的相关信息，包括奖品的库存信息，这一块大概的需求场景是这样的： 一个真实的奖品有多个虚拟的批次库存信息，每个批次库存信息由一个资格数来指定有允许有多少个人来抽奖，只有指定的人数满足以后，才可以开奖，开奖完成之后，才能进入一下批次库存信息进行抽奖活动。
拿单个奖品查询的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)三个列上有唯一索引, 如下图所示：
在每个benefit_id对应的批次库存数量不是很大的情况下，查询应该是非常快的，如下图所示：
从上图中可以看出，查询一次消耗的时间差不多在2ms左右，是一个典型的快sql查询，接下来问题来了： 项目中真实的场景是，根据前端传进来的activityCode去解析出一批奖品的id，一个activityCode解析出奖品id的个数，少的情况有5-6个，平均有30个左右，最多有120左右，很显然，用单个奖品串行去查数据库是不能接受的，因为有的奖品的批次库存比较多，根据limit 1查询的时间比较长, 串行查20个大概需要150-250ms左右，如下所示:
这个时间响应是不能接受的，毕竟响应时间平均超过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的执行计划如下：
从执行计划可以看出，确实是走到索引, 查询效果如下：
小批次查询9个奖品的批次库存信息消耗5ms，这也是一个典型的快sql, 通过这次优化，查询响应时间可以控制在100ms左右
如果查询时间都能控制在100ms左右，也没什么问题，终于有一天，问题爆发了，有3个上万的库存奖品上线，批次库存量分别是： 10000， 10000， 50000，并且这三个奖品都是在同一个活动下面，小批次查询库存的sql如下： 
小批量sql查询的消耗时间一下子跳到了200ms+, 当时人有点凝惑，这是怎么回事，还是先看一下这条SQL执行计划：
刚开始只注意到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查询数据库的表的速度非常非常的高效，就算多查几次，也不会有任何的性能问题
下面是优化前和优化后查询性能的对比：
优化前：
优化后：
上线前后查询性能对比图如下：
数据库的平均响应时间提高了10倍以上， HSF服务响应时间绝大部分终于压制住在20ms以下；
因写作时间比较仓促，有些地方写得不是很详细，有问题线下单独沟通。
其实核心的地方，大家只需要注意这几地方：
- mysql的数据根据索引的区分度超过30%, 默认不是会走索引的，如果要强制走索引，可以用force index语法来实现，不过不建议这么用，因为在这种情况下，用索引查询也会很慢性；
- 遇到了区分度超过30%的数据，要根据实际情况来分析解析，例如用canal就是一个不错的解决方案，也很方便；
- 遇到慢sql，不要慌乱，要定到具体的慢sql，以及相关的原因，对症下药，实在不行，说明数据模型上有问题，需要重新评估原来的数据模型；
4.平时多积累一下mysql优仳器的原理知识，建议大家有空看一下李海翔老师的数据库方面的巨作<<数据库查询优化器艺术>>
