MySQL 流式查詢的奧秘與應(yīng)用解析
很久沒寫開篇了，針對大數(shù)據(jù)采集分析和導(dǎo)出等功能，我們必須在內(nèi)存和性能上做好折中，這其中筆者最愛的就是流式查詢，而本文將基于多個(gè)角度針對流式查詢這技能進(jìn)行深入的分析和演示，希望對你有幫助。
詳解流式查詢
1. 關(guān)于IOPS和數(shù)據(jù)吞吐量
為了保證后文講解的流暢，我們這里對幾個(gè)比較重要的性能指標(biāo)進(jìn)行簡單的科普，對于服務(wù)器系統(tǒng)層面，IOPS(Input/Output Per Second)磁盤每秒的讀寫次數(shù)，一般以每秒輸入輸出量為單位進(jìn)行衡量。而吞吐量更多的是反應(yīng)的是每秒處理的IO請求，兩者關(guān)系我們可以通過如下兩個(gè)場景了解一下差異：
- 假設(shè)我們讀取1000個(gè)1kb的數(shù)據(jù)，耗時(shí)10s，那么這個(gè)服務(wù)器的數(shù)據(jù)吞吐量100KB/s，IOPS就是100，這種場景更追求IOPS。
- 假設(shè)我們只有1個(gè)請求去讀取10M的文件，耗時(shí)0.2s，那么這個(gè)服務(wù)器的數(shù)據(jù)吞吐量就是50MB/s，IOPS為5，這個(gè)服務(wù)器就更偏向于吞吐量。
2. MySQL常見的幾種查詢
日常針對大表數(shù)據(jù)采集導(dǎo)出的功能，我們一般會(huì)采用一下幾種方案：
- 一次性全量導(dǎo)出
- 使用分頁查詢
- 使用游標(biāo)查詢
- 流式查詢
我們先來說說全量查詢，這種方案本質(zhì)原理就是一次性將結(jié)果集從MySQL服務(wù)端寫到客戶端程序上，針對大表數(shù)據(jù)檢索，如果我們的程序沒有足夠的堆內(nèi)存空間，存在內(nèi)存溢出的風(fēng)險(xiǎn)：
為了解決OOM問題，我們會(huì)考慮通過分頁查詢的方式，通過分批處理完成批量數(shù)據(jù)檢索導(dǎo)出的工作，這種方式雖然很好的節(jié)約了堆內(nèi)存空間，但這種方案在代碼實(shí)現(xiàn)層面就已經(jīng)非常復(fù)雜了，開發(fā)者必須考慮：
- 分頁計(jì)算(這一步就涉及數(shù)據(jù)掃描，開銷大)
- 基于分頁評估每次分頁大小
- 基于頁數(shù)進(jìn)行循環(huán)查詢
- 查詢SQL需要針對深分頁問題進(jìn)行優(yōu)化
這種方案相較于前者雖然節(jié)省了堆內(nèi)存空間且可以一定程度上避免頻繁的Full GC，對于開發(fā)者整體素質(zhì)要求較高，并且這種方案在性能表現(xiàn)上也不是很出色：
所以為了避免在開發(fā)層面進(jìn)行手動(dòng)分頁實(shí)現(xiàn)的復(fù)雜度，我們就想到通過游標(biāo)法進(jìn)行查詢，游標(biāo)也就是cursor，這種查詢方式要求客戶端一次性指明fetchSize，然后服務(wù)端每次都基于給定的fetchSize將數(shù)據(jù)寫給客戶端，直到客戶端將所有數(shù)據(jù)都處理完成。
需要了解的是游標(biāo)查詢這種方案考慮到客戶端未知的處理效率，為保證服務(wù)端能夠一次性將fetch的數(shù)據(jù)寫回到客戶端，MySQL服務(wù)端會(huì)為了這個(gè)查詢建立一個(gè)臨時(shí)空間來緩存數(shù)據(jù)，在極端情況下因?yàn)檫@些問題：
- IOPS飆升
- 磁盤空間飆升(因?yàn)榕R時(shí)空間無法在緩存中容納，寫入到文件中)
- fetch設(shè)置過大，SQL查詢經(jīng)常處于阻塞等待IO數(shù)據(jù)的情況
最后我們就來說說本文的重點(diǎn)——流式查詢，當(dāng)客戶端向服務(wù)端發(fā)送SQL請求后，流式查詢會(huì)得到一個(gè)迭代器，客戶端不斷通過ResultSet.next()獲取下一條數(shù)據(jù)，服務(wù)端會(huì)按照客戶端接受速率并基于迭代器的偏移量逐步寫入到網(wǎng)絡(luò)buffer中讓客戶端讀取，這種方式很好的解決游標(biāo)查詢逐批次緩存的問題。 但需要注意的是這種方案和上述游標(biāo)查詢一樣，會(huì)因?yàn)閿?shù)據(jù)量的問題，使得連接長時(shí)間被當(dāng)前線程持有：
3. 流式查詢使用示例
接下來筆者就以常見的ORM框架Mybatis演示一下如何使用流式查詢，假設(shè)我們需要查詢一張user表，對應(yīng)的我們基于Options注解給出當(dāng)前這個(gè)查詢信息告知查詢r(jià)esultSetType 為只讀，并且指明fetchSize 為MIN_VALUE。同時(shí)，看到筆者在方法上給出了一個(gè)ResultHandler，這個(gè)處理用于處理流式查詢響應(yīng)結(jié)果后的回調(diào)處理：
@Select("select * from user ")
@Options(resultSetType = ResultSetType.FORWARD_ONLY, fetchSize = Integer.MIN_VALUE)
@ResultType(User.class)
void selectListByStream(ResultHandler<User> handler);需要補(bǔ)充說明的是上述三個(gè)配置都必須明確按照要求進(jìn)行配置，原因是在mybatis在執(zhí)行SQL查詢時(shí)，StatementImpl會(huì)通過createStreamingResultSet判斷這三個(gè)參數(shù)是否符合要求，只有明確符合要求返回true，后續(xù)的結(jié)果集才能被創(chuàng)建為ResultsetRowsStreaming：
protected boolean createStreamingResultSet() {
return ((this.query.getResultType() == Type.FORWARD_ONLY) && (this.resultSetConcurrency == java.sql.ResultSet.CONCUR_READ_ONLY)
&& (this.query.getResultFetchSize() == Integer.MIN_VALUE));
}對應(yīng)的我們也給出最后的使用示例，這里筆者用lambda精簡了一下ResultHandler的聲明，每當(dāng)我們收到流式響應(yīng)數(shù)據(jù)后，直接獲取user并自增一下原子類：
userMapper.selectListByStream(resultContext -> {
User user = resultContext.getResultObject();
//執(zhí)行業(yè)務(wù)操作......
});4. 幾種常見操作性能壓測
簡單介紹了一下流式查詢的基礎(chǔ)配置和使用之后，我們不妨針對上述方案進(jìn)行性能和內(nèi)存使用情況壓測，首先筆者已經(jīng)準(zhǔn)備了100w條數(shù)據(jù)并將堆內(nèi)存設(shè)置為512M：
-Xmx512m -Xms512m我們先給出一個(gè)基于全量查詢的導(dǎo)出寫入到本地txt文件：
try (BufferedWriter outputStream = FileUtil.getWriter("F://tmp/userData.txt", Charset.defaultCharset(), false)) {
//全量查詢
List<User> userList = userMapper.selectList(Wrappers.emptyWrapper());
//遍歷寫入本地文件
userList.forEach(user -> {
try {
outputStream.write(JSONUtil.toJsonStr(user) + "\r\n");
count.getAndIncrement();
} catch (IOException e) {
throw new RuntimeException(e);
}
});
} catch (Exception e) {
throw new RuntimeException(e);
}最終100w數(shù)據(jù)導(dǎo)出跑了大約20s:
save count:1000000 cost:20382ms我們通過jstat指令查看堆內(nèi)存使用情況，觸發(fā)了6次full gc，整體回收花費(fèi)了6s，性能表現(xiàn)非常差勁：
S0C S1C S0U S1U EC EU OC OU MC MU CCSC CCSU YGC YGCT FGC FGCT GCT
512.0 512.0 0.0 64.0 173568.0 68832.5 349696.0 194931.1 44032.0 41197.8 5888.0 5351.0 95 0.614 13 6.255 6.869然后就是分頁查詢，可以看到筆者這里并沒有針對深分頁問題進(jìn)行優(yōu)化，明確頁數(shù)和分批數(shù)后直接進(jìn)行分批查詢導(dǎo)出了：
//定位總數(shù)
Long c = userMapper.selectCount(Wrappers.emptyWrapper());
//計(jì)算頁數(shù)
int size = 1_0000;
long pageSize = c % size == 0 ? c / size : c / size + 1;
//分頁查詢
try (BufferedWriter outputStream = FileUtil.getWriter("F://tmp/userData.txt", Charset.defaultCharset(), false)) {
for (int i = 1; i <= pageSize; i++) {
PageHelper.startPage(i, size, false);
userMapper.selectList(Wrappers.emptyWrapper()).stream()
.forEach(u -> {
try {
outputStream.write(JSONUtil.toJsonStr(u) + "\r\n");
} catch (IOException e) {
throw new RuntimeException(e);
}
count.incrementAndGet();
});
}
} catch (Exception e) {
throw new RuntimeException(e);
}可以看到這段代碼內(nèi)存使用情況比較穩(wěn)定，但是耗時(shí)大約29s:
jstat -gc 21524
S0C S1C S0U S1U EC EU OC OU MC MU CCSC CCSU YGC YGCT FGC FGCT GCT
3584.0 3584.0 0.0 1831.1 167424.0 64921.0 349696.0 22386.0 44288.0 41324.7 5888.0 5362.6 81 0.283 2 0.072 0.355最后我們給出流式查詢的代碼拉取數(shù)據(jù)并寫入本地文件的示例：
try (BufferedWriter outputStream = FileUtil.getWriter("F://tmp/userData.txt", Charset.defaultCharset(), false)) {
//流式查詢
userMapper.selectListByStream(res -> {
//定位對象
User user = res.getResultObject();
try {
//寫入文件
outputStream.write(JSONUtil.toJsonStr(user) + "\r\n");
} catch (IOException e) {
throw new RuntimeException(e);
}
count.incrementAndGet();
});
} catch (Exception e) {
throw new RuntimeException(e);
}最終整體耗時(shí)14s，使用jstat查看gc情況也是非常穩(wěn)定：
S0C S1C S0U S1U EC EU OC OU MC MU CCSC CCSU YGC YGCT FGC FGCT GCT
512.0 512.0 0.0 96.0 173568.0 128136.7 349696.0 21416.8 43392.0 40499.1 5760.0 5264.3 79 0.158 2 0.057 0.215使用jvisualvm也可以看到堆內(nèi)存使用情況非常穩(wěn)定，流式查詢在內(nèi)存使用和查詢效率上做了很好的平衡：
5. 流式查詢使用注意事項(xiàng)
流式查詢在使用過程中當(dāng)前客戶端連接會(huì)持有本次查詢的ResultSet，如果沒有沒有將這個(gè)ResultSet關(guān)閉將會(huì)影響其他查詢使用。
在數(shù)據(jù)量較大的情況下，流式查詢會(huì)長時(shí)間持有當(dāng)前數(shù)據(jù)庫連接，所以還是可能存在網(wǎng)絡(luò)擁塞的風(fēng)險(xiǎn)。
