大家好，我是君哥。

最近上完線后，凌晨收到一個(gè)生產(chǎn)告警，一個(gè) OOM 異常導(dǎo)致了服務(wù)重啟。今天來(lái)分享一下這個(gè)事故。

1.事故現(xiàn)場(chǎng)

事故的代碼邏輯并不復(fù)雜，從一個(gè)大概有 8 萬(wàn)數(shù)據(jù)的表里面查出數(shù)據(jù)，匯總后對(duì)數(shù)據(jù)做處理。因?yàn)閿?shù)據(jù)量有 8 萬(wàn)，這里做了分頁(yè)查詢(xún)，每頁(yè)查詢(xún) 1000 條。這里貼一下代碼：這里我們假定這個(gè)表名叫 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;
  }
  //過(guò)濾掉不符合條件的數(shù)據(jù)。
  filterData(data);
  sqlBugDatas.addAll(data);
  if (data.size() <= pageSize){
   break;
  }
  pageNumber++;
 }
}

下面我們看一下 sql，這里用 Oracle 數(shù)據(jù)庫(kù)，orm 框架用的 mybatis，sql 在 mapper.xml 文件中:

select * from sql_bug order by id
offset #{pageNumber} rows fetch first #{pageSize} rows only

仔細(xì)看上面的 sql，我們可以看到 Offset 這個(gè)參數(shù)傳入了 pageNumber。那后果是什么呢？查詢(xún)語(yǔ)句每次偏移量加 1，表里有 8 萬(wàn)條數(shù)據(jù)，相當(dāng)于得查詢(xún) 79000 次循環(huán)才能結(jié)束。sqlBugDatas 數(shù)據(jù)量一直累加，最終觸發(fā) OOM。

2.原因分析

開(kāi)發(fā)同事為什么會(huì)出這個(gè) bug 呢？原因是錯(cuò)誤地把 offset 這個(gè)變量理解成是頁(yè)碼的偏移量。正確的寫(xiě)法：

select * from sql_bug order by id
offset #{pageNumber}*#{pageSize} rows fetch first #{pageSize} rows only

為什么測(cè)試沒(méi)有測(cè)出來(lái)呢？測(cè)試環(huán)境數(shù)據(jù)量比較小，并沒(méi)有出現(xiàn) OOM 的情況。

代碼 review 為什么沒(méi)有發(fā)現(xiàn)？由于交付的代碼量很大，做代碼 review 的同事主要關(guān)注點(diǎn)在業(yè)務(wù)邏輯的正確性上面，并沒(méi)有精力能看到這么細(xì)節(jié)的問(wèn)題。

3.分頁(yè)寫(xiě)法

使用 offset 進(jìn)行分頁(yè)的寫(xiě)法很常見(jiàn)，比如使用 MySQL 的 limit 語(yǔ)法，sql 如下：

select * from sql_bug order by id
limit #{pageNumber}*#{pageSize},#{pageSize}

但是使用 Offset 進(jìn)行分頁(yè)的寫(xiě)法并不推薦，因?yàn)橛猩疃确猪?yè)的性能問(wèn)題，后面的頁(yè)耗時(shí)會(huì)越來(lái)越多。下圖是阿里開(kāi)發(fā)手冊(cè)關(guān)于分頁(yè)場(chǎng)景的一個(gè)規(guī)范。

遵循這個(gè)規(guī)范，我們每次查詢(xún)的時(shí)候，不妨傳入一個(gè)主鍵 id。先改寫(xiě)一下代碼：

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();
  //過(guò)濾掉不符合條件的數(shù)據(jù)。
  filterData(data);
  sqlBugDatas.addAll(data);
  if (data.size() <= pageSize){
   break;
  }
 }
}

配合上面代碼，把 sql 也改寫(xiě)一下，這里使用 mysql 語(yǔ)法：

select * from sql_bug 
<if test="id != null">            
 where id <![CDATA[>]]> #{id}
</if>   
order by id
limit #{pageSize}

也可以使用 rownum 來(lái)控制，下面再改寫(xiě)一下（Oracle 語(yǔ)法）：

select * from(
 select * from sql_bug 
 <if test="id != null">            
  where id <![CDATA[>]]> #{id}
 </if>   
 order by id
)
where rownum <![CDATA[<]]> #{pageSize} + 1

我個(gè)人更推薦 rownum 寫(xiě)法，原因有 2 個(gè)：

• 這種語(yǔ)法更容易理解，如果出問(wèn)題的 sql 使用這個(gè)語(yǔ)法，大概率是不會(huì)出這個(gè) bug 的；
• 國(guó)內(nèi)好多公司做信創(chuàng)改造，國(guó)產(chǎn)數(shù)據(jù)庫(kù)對(duì)這種語(yǔ)法支持更好一些。

4.總結(jié)

本文介紹了一個(gè)生產(chǎn)事故，并對(duì)事故和改進(jìn)方法進(jìn)行了分析，希望對(duì)你理解數(shù)據(jù)庫(kù)分頁(yè)有所幫助。