寫了一個分頁 SQL，因為粗心出了 Bug 造成了 OOM！
大家好，我是君哥。
最近上完線后，凌晨收到一個生產(chǎn)告警，一個 OOM 異常導(dǎo)致了服務(wù)重啟。今天來分享一下這個事故。
1.事故現(xiàn)場
事故的代碼邏輯并不復(fù)雜，從一個大概有 8 萬數(shù)據(jù)的表里面查出數(shù)據(jù)，匯總后對數(shù)據(jù)做處理。因為數(shù)據(jù)量有 8 萬，這里做了分頁查詢，每頁查詢 1000 條。這里貼一下代碼：這里我們假定這個表名叫 sql_bug。
private void testSQLBug(){
List<SQLBugData> sqlBugDatas = new ArrayList<>();
int pageSize = 1000;
int pageNumber = 0;
while (true){
List<SQLBugData> data = sqlBugDataMapper.queryData(pageSize, pageNumber);
if (CollectionUtils.isEmpty(data)){
break;
}
//過濾掉不符合條件的數(shù)據(jù)。
filterData(data);
sqlBugDatas.addAll(data);
if (data.size() <= pageSize){
break;
}
pageNumber++;
}
}下面我們看一下 sql，這里用 Oracle 數(shù)據(jù)庫，orm 框架用的 mybatis，sql 在 mapper.xml 文件中:
select * from sql_bug order by id
offset #{pageNumber} rows fetch first #{pageSize} rows only仔細看上面的 sql，我們可以看到 Offset 這個參數(shù)傳入了 pageNumber。那后果是什么呢？查詢語句每次偏移量加 1，表里有 8 萬條數(shù)據(jù)，相當(dāng)于得查詢 79000 次循環(huán)才能結(jié)束。sqlBugDatas 數(shù)據(jù)量一直累加，最終觸發(fā) OOM。
2.原因分析
開發(fā)同事為什么會出這個 bug 呢？原因是錯誤地把 offset 這個變量理解成是頁碼的偏移量。正確的寫法：
select * from sql_bug order by id
offset #{pageNumber}*#{pageSize} rows fetch first #{pageSize} rows only為什么測試沒有測出來呢？測試環(huán)境數(shù)據(jù)量比較小，并沒有出現(xiàn) OOM 的情況。
代碼 review 為什么沒有發(fā)現(xiàn)？由于交付的代碼量很大，做代碼 review 的同事主要關(guān)注點在業(yè)務(wù)邏輯的正確性上面，并沒有精力能看到這么細節(jié)的問題。
3.分頁寫法
使用 offset 進行分頁的寫法很常見，比如使用 MySQL 的 limit 語法，sql 如下：
select * from sql_bug order by id
limit #{pageNumber}*#{pageSize},#{pageSize}但是使用 Offset 進行分頁的寫法并不推薦，因為有深度分頁的性能問題，后面的頁耗時會越來越多。下圖是阿里開發(fā)手冊關(guān)于分頁場景的一個規(guī)范。
遵循這個規(guī)范，我們每次查詢的時候，不妨傳入一個主鍵 id。先改寫一下代碼：
private void testSQLBug(){
List<SQLBugData> sqlBugDatas = new ArrayList<>();
String id = null;
int pageSize = 0;
while (true){
List<SQLBugData> data = sqlBugDataMapper.queryData(id, pageSize);
if (CollectionUtils.isEmpty(data)){
break;
}
id = data.get(data.size()-1).getId();
//過濾掉不符合條件的數(shù)據(jù)。
filterData(data);
sqlBugDatas.addAll(data);
if (data.size() <= pageSize){
break;
}
}
}配合上面代碼，把 sql 也改寫一下，這里使用 mysql 語法：
select * from sql_bug
<if test="id != null">
where id <![CDATA[>]]> #{id}
</if>
order by id
limit #{pageSize}也可以使用 rownum 來控制，下面再改寫一下（Oracle 語法）：
select * from(
select * from sql_bug
<if test="id != null">
where id <![CDATA[>]]> #{id}
</if>
order by id
)
where rownum <![CDATA[<]]> #{pageSize} + 1我個人更推薦 rownum 寫法，原因有 2 個：
- 這種語法更容易理解，如果出問題的 sql 使用這個語法，大概率是不會出這個 bug 的；
- 國內(nèi)好多公司做信創(chuàng)改造，國產(chǎn)數(shù)據(jù)庫對這種語法支持更好一些。
4.總結(jié)
本文介紹了一個生產(chǎn)事故，并對事故和改進方法進行了分析，希望對你理解數(shù)據(jù)庫分頁有所幫助。
