业务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。