業務SQL那些事–慎用LIMIT

在業務中使用LIMIT限制SQL返回行數是很常見的事情，但如果不知道其中可能的坑或者說真正執行邏輯，就可能會使SQL執行非常慢，嚴重影響效能。

LIMIT OFFSET, ROW_COUNT實現分頁

業務反映一條SQL執行非常慢。簡單分析，例如下面的schema與SQL(演示databae:PostgreSQL)：

create table t(c1 varchar(20) primary key, c2 int);

select * from t where c1 > `20150224` and c1 < `20160706` and c2 > 1 and c2 <500000000 order by c1 offset $offset limit 5000;(limit $offset, 5000)

其中offset的從0開始，5000遞增，最大可以到200W。SQL執行時間就會隨著offset的值增加而增加，最終達到業務不可承受的程度。

這條SQL因為主鍵有序所以省去了order by的SORT，但SQL訪問表的時候依然至少需要訪問$offset + 5000行資料，掃描行數隨著offset增加而增加。而且這是至少需要訪問的資料量，那麼不難理解為什麼SQL會隨著offset變大而變慢。

業務是用這條語句實現分頁功能，其分頁的order c1就是表的主鍵。所以對於這個查詢條件可能會訪問大量資料的SQL應該記錄last_id來實現分頁。改為如下SQL，last_id初始值為`20150224`，然後每次獲取資料後記錄最後一行的c1作為下次的last_id。

select * from t where c1 > $last_id and c1 < `20160706` and c2 > 1 and c2 <500000000 order by c1 limit 5000;

LIMIT ROW_COUNT會效能差

業務遇到一條包含有LIMIT 0, 15的SQL執行時間超過預期。簡單分析，schema與SQL如下：

create table t(c1 int, c2 int, c3 int, c4 int, primary key(c1));

create index t_c2_c4_c3 on t(c2, c4, c3);

select * from t where c4 = 1 and c3 <> 9 and c2 > 1 and c2 < 1000000 order by c3, c2 offset 0 limit 15;(limit 0, 15)

業務雖然建立了索引，同時在索引字首上有約束條件，但是由於滿足約束條件的行非常多，同時order by的column不是索引ordering的字首，所以table層依然需要訪問所有滿足索引條件的行，同時在過濾後進行SORT操作。Plan如下：

test=# explain verbose select * from t where c4 = 1 and c3 <> 9 and c2 > 1 and c2 < 1000000 order by c3, c2 offset 0 limit 15;
+----------------------------------------------------------------------------------------+
| QUERY PLAN |
+----------------------------------------------------------------------------------------+
| Limit  (cost=8.29..8.29 rows=1 width=16)   |
|   Output: c1, c2, c3, c4   |
|   ->  Sort  (cost=8.29..8.29 rows=1 width=16)  |
|       Output: c1, c2, c3, c4 |
|       Sort Key: t.c3, t.c2   |
|       ->  Index Scan using t_c2_c4_c3 on public.t  (cost=0.15..8.28 rows=1 width=16) |
|           Output: c1, c2, c3, c4   |
|           Index Cond: ((t.c2 > 1) AND (t.c2 < 1000000) AND (t.c4 = 1)) |
|           Filter: (t.c3 <> 9)  |
+----------------------------------------------------------------------------------------+

和業務方瞭解後，c3的值只有3個（0,1,9）,即c3 <> 9可以改寫為 c3 in (0,1)。同時由於c4是定值，考慮到其他SQL對c4列的使用，決定讓業務建立index(c4, c3, c2)。在PostgreSQL中如下：

test=# explain verbose select * from t where c4 = 1 and c3 in(0,1)
 and c2 > 1 and c2 < 1000000 order by c3, c2 offset 0 limit 15;
+----------------------------------------------------------------------------------+
| QUERY PLAN   |
+----------------------------------------------------------------------------------+
| Limit  (cost=0.15..8.28 rows=1 width=16) |
|   Output: c1, c2, c3, c4 |
|   ->  Index Scan using t_c4_c3_c2 on public.t  (cost=0.15..8.28 rows=1 width=16) |
|       Output: c1, c2, c3, c4   |
|       Index Cond: ((t.c4 = 1) AND (t.c2 > 1) AND (t.c2 < 1000000)) |
|       Filter: (t.c3 = ANY (`{0,1}`::integer[]))|
+----------------------------------------------------------------------------------+

省去了SORT的代價，同時TABLE只需要找到索引上滿足約束條件的15行資料。

不過比較遺憾演示的PostgreSQL沒有能利用filter: c3 in (0,1)條件對(c4,c3,c2)生成兩個查詢範圍(1,0,1)~(1,0,1000000)和(1,1,1)~(1,1,1000000)，即”C4″=1 AND (“C3″=0 OR “C3″=1) AND “C2”>1 AND “C2″<1000000。