业务SQL那些事--慎用LIMIT
来源:互联网 发布:java语言培训价格 编辑:程序博客网 时间:2024/06/05 05:17
业务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。
- 业务SQL那些事--慎用LIMIT
- mysql中limit慎用
- 关键业务慎用linux!
- sql limit
- sql limit
- SQL-limit
- Sql的那些事
- PL/SQL那些事
- sql那些事
- 好文章分享--《组件化与业务拆分那些事》
- App组件化与业务拆分那些事
- sql的limit
- SQL中的Limit
- (转)sql 的limit
- SQL中的Limit
- sql语句中的limit
- sql的limit用法
- sql LIMIT子句
- MySQL
- Android 设计模式 笔记 - 责任链模式
- ATL模型转换技术详解
- 2016.07.19回顾
- mysql order by 优化 |order by 索引的应用
- 业务SQL那些事--慎用LIMIT
- Odd Even Linked List
- 隐含马尔可夫模型
- java异常
- python学习——async/await
- hdu 5419(数学期望)
- 【PHP开发】curl的使用
- MySQL随机获取数据的方法,支持大数据量
- 彩色图像的反转