业务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 在业务中使用LIMIT限制SQL返回行数是很常见的事情,但如果不知道其中可能的坑或者说真正执行逻辑,就可能会使SQL执行非常慢,严重影响性能。
相关文章
- C# Expression 树转化为SQL与语句(二)--解决参数问题
- asp.net, c# 查询今天的当天所有数据 sql语句 用于SQLServer(mssql)
- EXEC执行动态的SQL语句
- Sql Server中存储过程中输入和输出参数(简单实例,一看就懂)
- SQL中用一句sql语句将数据库中的两列值进行交换
- SQL数据分析:sqlzoo官网学习select,where,order by,limit,聚合函数,having,常用函数,窗口函数,表链接,子查询
- SQL学习笔记5-SQL高级处理
- DB2常用sql命令
- DBCC DBREINDEX重建索引提高SQL Server性能
- SQL Server SQL性能优化之--数据库在“简单”参数化模式下,自动参数化SQL带来的问题
- Sql Server 优化 SQL 查询:如何写出高性能SQL语句
- sql server 数据库优化--显示执行计划
- sql语句的优化分析
- SQL Server调优系列基础篇
- sql 转换日期格式 只保留月份和日期
- Sql Server数据库监听 c#代码
- SQL Server 2008中的CDC(Change Data Capture)功能使用及释疑
- SQL Server--获取磁盘空间使用情况
- 【网络安全】面试中常见问题--sql注入篇
- Sql--order by、desc降序、top
- SQL Server 2008收缩日志文件--dbcc shrinkfile参数说明
- 什么是SQL Server2019大数据群集?