业务SQL那些事--慎用LIMIT

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

如果给出了一个限制计数，那么会返回数量不超过该限制的行（但可能更少些，因为查询本身可能生成的行数就比较少）。
SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了上面这样一个功能。
SQL数据库查询LIMIT 数据分页 SQL的语句LIMIT 约束SELECT 查询数据包表时，使得SQL查询语句返回指定的记录数（行数）。