快捷導(dǎo)航

Mysql大數(shù)據(jù)量查詢優(yōu)化思路詳析

更新時間：2022年01月26日 11:03:02 作者：老司機(jī)張師傅?

這篇文章主要介紹了Mysql大數(shù)據(jù)量查詢優(yōu)化思路，Mysql大表查詢優(yōu)化，理論上千萬級別以下的數(shù)據(jù)量Mysql單表查詢性能處理都是可以的。下文我們就來看看具體得思路解析

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

解決方案：

這里只簡單說明優(yōu)化的幾個方向。

1. 千萬級別日志查詢的優(yōu)化

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

粘出自定義分頁查詢結(jié)果封裝：

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

2. 幾百萬黑名單庫的查詢優(yōu)化

黑名單庫查詢優(yōu)化只能通過加合適的索引和優(yōu)化SQL語句來優(yōu)化，百萬級別數(shù)據(jù)松松的在Mysql和Mybatis的承受范圍內(nèi)，這里是由于黑名單庫不是使用遞增的，有可能會增加也有可能會刪除，所以只能使用優(yōu)化索引和SQL的方式進(jìn)行優(yōu)化。
另外，Mybatis框架提供了重寫分頁查詢count統(tǒng)計(jì)語句的方法，只需要將count語句命名為查詢方法_COUNT即可，例如分頁查詢的語句方法是query,那么重寫的統(tǒng)計(jì)方法即為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批量插入語句中的類集合大小不能超過五千，三千是最佳，這是測試出來的結(jié)果，考慮到的原因是Mybatis會將類做反射，這個太影響效率，因此批量插入時要注意這個，如果你能夠三千三千的批量處理就限制一下，不要讓每批數(shù)據(jù)超過3000，數(shù)據(jù)量過大時也可以使用異步非阻塞的方式來插入。
異步非阻塞代碼（只是步驟樣例,存在代碼缺失）：

? ? // 執(zhí)行全量HMD導(dǎo)入任務(wù)的線程池
? ? public final static ExecutorService importHasPool = Executors.newFixedThreadPool(10);
? ? public final static CompletionService<DoExcelResult> importHasPoolService = new ExecutorCompletionService<>(importHasPool);
? ??
? ?public synchronized DoExcelResult example() {
? ? ? ? ? ? // 開始執(zhí)行導(dǎo)入
? ? ? ? ? ? // 寫到這里面方法最后會自動關(guān)閉
? ? ? ? ? ? long startTime = System.currentTimeMillis();
? ? ? ? ? ? // 定義一個集合，記錄Callable的執(zhí)行結(jié)果,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是實(shí)現(xiàn)了Callable接口的實(shí)現(xiàn)類，Callable是帶返回值的Runable
? ? ? ? ? ? ? ? ? ? Future submit = SysThreadPoolCenter.importHasPoolService.submit(new BlackInfoHasImportlCallable(list1, blacklistInfoMapper));
? ? ? ? ? ? ? ? ? ? futures.add(submit);
? ? ? ? ? ? ? ? }
? ? ? ? ? ? }
? ? ? ? ? ??
? ? ? ? ? ? // 等待執(zhí)行結(jié)果
? ? ? ? ? ? for (Future<DoExcelResult> future : futures) {
? ? ? ? ? ? ? ? try {
? ? ? ? ? ? ? ? // 2. futrue.get時會獲取返回值，線程沒執(zhí)行完畢就等待等待執(zhí)行結(jié)果
? ? ? ? ? ? ? ? ? ? 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);
? ? ? ? ? ? ? ? }
? ? ? ? ? ? }
? ? ? ? ? ? // 循環(huán)結(jié)束代表所有線程執(zhí)行完畢
? ? ? ? ? ? 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);
? ? ? ? }

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

到此這篇關(guān)于Mysql大數(shù)據(jù)量查詢優(yōu)化思路詳析的文章就介紹到這了,更多相關(guān)Mysql大數(shù)據(jù)量查詢優(yōu)化內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: