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

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

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

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

解決方案：

這里只簡(jiǎn)單說(shuō)明優(yōu)化的幾個(gè)方向。

1. 千萬(wàn)級(jí)別日志查詢的優(yōu)化

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

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

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

2. 幾百萬(wàn)黑名單庫(kù)的查詢優(yōu)化

黑名單庫(kù)查詢優(yōu)化只能通過(guò)加合適的索引和優(yōu)化SQL語(yǔ)句來(lái)優(yōu)化，百萬(wàn)級(jí)別數(shù)據(jù)松松的在Mysql和Mybatis的承受范圍內(nèi)，這里是由于黑名單庫(kù)不是使用遞增的，有可能會(huì)增加也有可能會(huì)刪除，所以只能使用優(yōu)化索引和SQL的方式進(jìn)行優(yōu)化。
另外，Mybatis框架提供了重寫分頁(yè)查詢count統(tǒng)計(jì)語(yǔ)句的方法，只需要將count語(yǔ)句命名為查詢方法_COUNT即可，例如分頁(yè)查詢的語(yǔ)句方法是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批量插入處理問(wèn)題

Mybatis批量插入語(yǔ)句中的類集合大小不能超過(guò)五千，三千是最佳，這是測(cè)試出來(lái)的結(jié)果，考慮到的原因是Mybatis會(huì)將類做反射，這個(gè)太影響效率，因此批量插入時(shí)要注意這個(gè)，如果你能夠三千三千的批量處理就限制一下，不要讓每批數(shù)據(jù)超過(guò)3000，數(shù)據(jù)量過(guò)大時(shí)也可以使用異步非阻塞的方式來(lái)插入。
異步非阻塞代碼（只是步驟樣例,存在代碼缺失）：

? ? // 執(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)入
? ? ? ? ? ? // 寫到這里面方法最后會(huì)自動(dòng)關(guān)閉
? ? ? ? ? ? long startTime = System.currentTimeMillis();
? ? ? ? ? ? // 定義一個(gè)集合，記錄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時(shí)會(huì)獲取返回值，線程沒執(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ù)庫(kù)用的不是mysql，而是CK或者其他的大數(shù)據(jù)處理數(shù)據(jù)庫(kù)，批量插入可能要求每秒幾萬(wàn)條幾十萬(wàn)條，這時(shí)就不再適合使用Myabtis框架了，建議使用JDBC連接的方式，自己寫代碼拼接sql語(yǔ)句，再使用jdbc連接執(zhí)行（使用線程池），效率上會(huì)快很多。

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

您可能感興趣的文章: