Mysql大數據量查詢優化思路詳析

Posted on by

項目場景：

Mysql大表查詢優化，理論上千萬級別以下的數據量Mysql單表查詢性能處理都是可以的。

問題描述：

在我們線上環境中，出現瞭mysql幾千萬級別的日志查詢、幾百萬級別的黑名單庫查詢分頁查詢及條件查詢都慢的問題，針對Mysql表優化做瞭一些優化處理。

原因分析：
首先說一下日志查詢，在Mysql中如果索引加的比較合適，走索引情況下千萬級別查詢不會超過一秒，Mysql查詢的速度和檢索的數據條數有關。在Mybatis中，分頁查詢是先執行Count記錄總數，再執行limit a,b 的方式來進行的，而Mysql的Count計數方式是將所有的數據過濾一遍進行累加，因此當日志表數據過千萬時，統計一次就是十幾秒鐘的時間（這裡是服務器環境，本地情況下甚至是幾分鐘）。
limit a,b的方式也一樣，Mysql查詢時會先一條一條數到第a條，然後向後再數b條作為查詢結果，因此當起始行數越來越大時查詢同樣會變得很慢，也就是當你點第一頁時可能一下就查出來瞭，當你點最後一頁的時候可能幾十秒才能查出來。
黑名單庫查詢優化同理，也是需要通過條件優化。
在進行大批量數據落庫時，使用的Mybatis批量插入，發現當批次數據超過3000時速度會急劇變慢，這是一個Mybatis娘胎裡自帶的問題，也需要進行解決。

解決方案：

這裡隻簡單說明優化的幾個方向。

1. 千萬級別日志查詢的優化

  1. 首先說下日志查詢，重點是優化無條件是分頁查詢，在無條件時，不使用MyBatis的分頁插件，而是自己手寫一個分頁查詢，由於MySql的count耗時過長，我們先優化他。
  2. 優化Count：日志表的數據隻增，不會出現中間某條刪除，所以他的數據可以理解成是連續的，我們可以在內存中直接進行計數，記錄count總數，或者給表添加一個自增的ID字段，直接select max(id)就是總數量，這樣count查詢的效率會提升到毫秒級別。
  3. 自定義分頁查詢：分頁查詢中使用優化後的count記錄總數，然後使用（page - 1）* pageSize + 1公式計算出當前頁的最小ID，然後將limit a,b 的Sql語句改為where ID > 最小ID limit b的方式，這樣查詢就會走索引先將小於最小ID的數據過濾掉，再進行查詢，經過第二步和第三步的優化後分頁查詢效率縮短到瞭一秒內，並且不會隨著頁數的增長而變慢。
  4. 條件查詢：條件查詢隻能設置合適的索引，另外慎用like '%條件%‘的方式進行匹配查詢，這樣會導致索引失效全局檢索，模糊查詢盡量使用like '條件%' 的方式進行最左匹配，也可以使用explain+sql語句 的方式來查看sql語句的執行效率，是否走瞭所有啥的來針對性的優化，加好合適的索引、優化查詢語句後通常一千萬以內的數據查詢效率會在3秒內。

粘出自定義分頁查詢結果封裝：

 // 手動count
                Integer total = logPushService.queryBackCount(resMap);//查詢數量
                // 手動查詢結果
                List<InterceptInfo> ls = logPushService.queryBackByPage(resMap);
                PageInfo pageInfo = new PageInfo();
                pageInfo.setTotal(total);
                pageInfo.setPageSize(limit);
                pageInfo.setList(ls);
                pageInfo.setPageNum(pn);

2. 幾百萬黑名單庫的查詢優化

  • 黑名單庫查詢優化隻能通過加合適的索引和優化SQL語句來優化，百萬級別數據松松的在Mysql和Mybatis的承受范圍內，這裡是由於黑名單庫不是使用遞增的，有可能會增加也有可能會刪除，所以隻能使用優化索引和SQL的方式進行優化。
  • 另外，Mybatis框架提供瞭重寫分頁查詢count統計語句的方法，隻需要將count語句命名為查詢方法_COUNT即可，例如分頁查詢的語句方法是query,那麼重寫的統計方法即為query_COUNT

在這裡插入圖片描述 

 <select id="query_COUNT" parameterType="java.util.Map" resultType="java.lang.Long">
        SELECT
        count(0)
        from nms_intercept_info${map.week}
        where 1=1
        <if test="map.id!=null and map.id!='' ">
            AND id>#{map.id}
        </if>
        <if test="map.url!=null and map.url!='' ">
            AND spliturl=#{map.url}
        </if>
        <if test="map.startTime!=null and map.startTime!='' ">
            AND time <![CDATA[>=]]> #{map.startTime}
        </if>
        <if test="map.endTime!=null and map.endTime!='' ">
            AND time <![CDATA[<=]]> #{map.endTime}
        </if>
        <if test="map.type!=null and map.type!='' ">
            AND bigType = #{map.type}
        </if>
    </select>

3. Mybatis批量插入處理問題

Mybatis批量插入語句中的類集合大小不能超過五千，三千是最佳，這是測試出來的結果，考慮到的原因是Mybatis會將類做反射，這個太影響效率，因此批量插入時要註意這個，如果你能夠三千三千的批量處理就限制一下，不要讓每批數據超過3000，數據量過大時也可以使用異步非阻塞的方式來插入。
異步非阻塞代碼（隻是步驟樣例,存在代碼缺失）：

    // 執行全量HMD導入任務的線程池
    public final static ExecutorService importHasPool = Executors.newFixedThreadPool(10);
    public final static CompletionService<DoExcelResult> importHasPoolService = new ExecutorCompletionService<>(importHasPool);
    
   public synchronized DoExcelResult example() {
            // 開始執行導入
            // 寫到這裡面方法最後會自動關閉
            long startTime = System.currentTimeMillis();
            // 定義一個集合，記錄Callable的執行結果,Callable是帶返回值的Runable
            List<Future<DoExcelResult>> futures = new ArrayList<>();
            while ((str = reader.readLine()) != null) {
                if (list.size() > 5000) { // 5000插入一次
                    List<String> list1 = CollectionUtil.copyDepth(list);
                    list.clear();
                    // BlackInfoHasImportlCallable是實現瞭Callable接口的實現類，Callable是帶返回值的Runable
                    Future submit = SysThreadPoolCenter.importHasPoolService.submit(new BlackInfoHasImportlCallable(list1, blacklistInfoMapper));
                    futures.add(submit);
                }
            }
            
            // 等待執行結果
            for (Future<DoExcelResult> future : futures) {
                try {
                // 2. futrue.get時會獲取返回值，線程沒執行完畢就等待等待執行結果
                    DoExcelResult doExcelResult = future.get();
                    result.setSuccessNum(result.getSuccessNum() + doExcelResult.getSuccessNum());
                    result.setContinueNum(result.getContinueNum() + doExcelResult.getContinueNum());
                    result.setErrorNum(result.getErrorNum() + doExcelResult.getErrorNum());
                } catch (Exception e) {
                    log.error(e);
                }
            }
            // 循環結束代表所有線程執行完畢
            result.setTimeCon((System.currentTimeMillis() - startTime)/1000.0);
            BlacklistService.isDoing = false;
        } catch (Exception e) {
            BlacklistService.isDoing = false;
            log.error(e);
        }
        BlacklistService.isDoing = false;
        return result;
    }

限制每批3000條：

 if (ls.size() >= 3000) {
            //每次保存3000
            double sum = Math.ceil(ls.size() / 3000f);
            for (int i = 0; i < sum; i++) {
                total += blacklistDao.saveBatch(ls.subList(i * 3000, ((i + 1) * 3000) > ls.size() ? ls.size() : (i + 1) * 3000));
            }
        } else {
            total = blacklistDao.saveBatch(ls);
        }

如果你數據庫用的不是mysql，而是CK或者其他的大數據處理數據庫，批量插入可能要求每秒幾萬條幾十萬條，這時就不再適合使用Myabtis框架瞭，建議使用JDBC連接的方式，自己寫代碼拼接sql語句，再使用jdbc連接執行（使用線程池），效率上會快很多。

到此這篇關於Mysql大數據量查詢優化思路詳析的文章就介紹到這瞭,更多相關Mysql大數據量查詢優化內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：