天貓權益平台如何10倍的提升資料庫查詢響應時間

背景：

上周雙旦項目上線完成，在上線過程中，遇到了一系統的問題，我個人這邊主要遇到的問題是：前端查詢獎品的相關資訊，包括獎品的庫存資訊，這一塊大概的需求場景是這樣的：一個真實的獎品有多個虛拟的批次庫存資訊，每個批次庫存資訊由一個資格數來指定有允許有多少個人來抽獎，隻有指定的人數滿足以後，才可以開獎，開獎完成之後，才能進入一下批次庫存資訊進行抽獎活動。

拿單個獎品查詢的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優仳器的原理知識，建議大家有空看一下李海翔老師的資料庫方面的巨作<<資料庫查詢優化器藝術>>

天貓權益平台如何10倍的提升資料庫查詢響應時間

背景：

方案一：

方案二：

1. 添加一個switch開發，配置上大庫存獎品的id，因為大庫存獎品我們通過資料庫查詢出來是已知的：

2. 在每個機器上添加一個本地的定時任務, 這個任務定時的從資料庫撈取最近10min新生效的批次庫存資訊

3.在查詢大商品庫存時的主要代碼如下：

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述