寫了一個分頁 SQL，因為粗心出了 Bug 造成了 OOM！
大家好，我是君哥。
最近上完線后，凌晨收到一個生產告警，一個 OOM 異常導致了服務重啟。今天來分享一下這個事故。
1.事故現場
事故的代碼邏輯并不復雜，從一個大概有 8 萬數據的表里面查出數據，匯總后對數據做處理。因為數據量有 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;
}
//過濾掉不符合條件的數據。
filterData(data);
sqlBugDatas.addAll(data);
if (data.size() <= pageSize){
break;
}
pageNumber++;
}
}下面我們看一下 sql，這里用 Oracle 數據庫，orm 框架用的 mybatis，sql 在 mapper.xml 文件中:
select * from sql_bug order by id
offset #{pageNumber} rows fetch first #{pageSize} rows only仔細看上面的 sql，我們可以看到 Offset 這個參數傳入了 pageNumber。那后果是什么呢？查詢語句每次偏移量加 1，表里有 8 萬條數據，相當于得查詢 79000 次循環才能結束。sqlBugDatas 數據量一直累加，最終觸發 OOM。
2.原因分析
開發同事為什么會出這個 bug 呢？原因是錯誤地把 offset 這個變量理解成是頁碼的偏移量。正確的寫法：
select * from sql_bug order by id
offset #{pageNumber}*#{pageSize} rows fetch first #{pageSize} rows only為什么測試沒有測出來呢？測試環境數據量比較小，并沒有出現 OOM 的情況。
代碼 review 為什么沒有發現？由于交付的代碼量很大，做代碼 review 的同事主要關注點在業務邏輯的正確性上面，并沒有精力能看到這么細節的問題。
3.分頁寫法
使用 offset 進行分頁的寫法很常見，比如使用 MySQL 的 limit 語法，sql 如下：
select * from sql_bug order by id
limit #{pageNumber}*#{pageSize},#{pageSize}但是使用 Offset 進行分頁的寫法并不推薦，因為有深度分頁的性能問題，后面的頁耗時會越來越多。下圖是阿里開發手冊關于分頁場景的一個規范。
遵循這個規范，我們每次查詢的時候，不妨傳入一個主鍵 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();
//過濾掉不符合條件的數據。
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 的；
- 國內好多公司做信創改造，國產數據庫對這種語法支持更好一些。
4.總結
本文介紹了一個生產事故，并對事故和改進方法進行了分析，希望對你理解數據庫分頁有所幫助。
