POSTGRESQL SQL 语句案例，一场由LIMIT 1 引发的“奇怪异像”

最新推荐文章于 2024-05-06 23:50:01 发布
最新推荐文章于 2024-05-06 23:50:01 发布
阅读量799 收藏
点赞数
文章标签： postgresql sql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/liuhuayang/article/details/129360137
版权

开头还是介绍一下群，如果感兴趣polardb ,mongodb ,mysql ,postgresql ,redis 等有问题，有需求都可以加群群内有各大数据库行业大咖，CTO，可以解决你的问题。加群请联系 liuaustin3 ，在新加的朋友会分到2群。

fdb35d07a21c46d1442e506c97735e9d.png

最近一段工作很少优化SQL ，实际

上7-8年前的确有一段疯狂优化的“美好时光”。 最近一个同事提出一个问题，他的一个POSTGRESQL 的SQL 在运行中因为客户的需要，将语句添加limit 1 ，但是在添加完毕后，整体语句运行时间超过原有的语句少则9倍，多则20多倍，从不到2秒，变成了23秒。

下面是语句的修改版，不少部分已经改名了。

SELECT

bizzz.* 

FROM

(

SELECT

1 AS "bizzzType",

os.NAME "deFromName",

gb.title "ticketName",

gb.market_price "marketPrice",

gb.price price,

gb.discount_rate * 100 "discountRate",

gb.income_money "ticketIncomeMoney",

gb.div_ticket_name "divTicketName",

btpg.ticket_count "ticketCount",

btpg.pay_money "payMoney",

( gb.market_price - gb.price ) * btpg.ticket_count "discMoney",

CASE 

WHEN btpg.ticket_sale * btpg.ticket_count > btpg.pay_money 

THEN

btpg.pay_money 

ELSE btpg.ticket_sale * btpg.ticket_count 

END "incomeMoney",

shop.ID AS "shopId",

shop.NAME AS "shopName",

btpg.no_give_change "noGiveChange",

btpg.bs_id "bsId",

bb.code "bsCode",

btpg.ticket_code "ticketCode",

btpg.create_time "useTime",

bb.settle_bizzz_date "settlebizzzDate",

COALESCE ( btpg.income_overchange, 0 ) "incomeOverChange" ,

pw_detail_id as "ticketRowId"

FROM

(SELECT

bs_id,

ticket_count,

ticket_sale,

ticket_value,

pay_money,

payc_id,

ticket_id,

create_shop_id,

pw_detail_id,

no_give_change,

create_time,

REPLACE ( REPLACE ( REPLACE ( REPLACE ( ticket_code_serials, '["', '' ), '"]', '' ), '"', '' ), ',', ';' ) AS ticket_code,

income_overchange 

FROM  bizzz_ts_pw_gb 

WHERE create_shop_id IN ( SELECT ID FROM dbi_shop WHERE center_id = 83726 AND manage_type_id IN ( 301, 302, 304 ) ) 

AND modify_time >= '2023-03-02 00:00:00' 

AND modify_time <'2023-03-03 00:00:00' 

AND delflg = 0 

AND settle_state = 1 

) btpg

INNER JOIN (SELECT ID,code,settle_bizzz_date  FROM

bizzz_bs 

WHERE

create_shop_id IN ( SELECT ID FROM dbi_shop WHERE center_id = 83726 AND manage_type_id IN ( 301, 302, 304 ) ) AND settle_time >='2023-03-02 00:00:00'  AND settle_time < '2023-03-03 00:00:00'AND STATE = 1 

AND delflg = 0 ) bb ON btpg.bs_id = bb.

ID

INNER JOIN ( SELECT ID FROM arch_pay c WHERE payc_type_id = 509 ) ap ON btpg.payc_id = ap.ID

LEFT JOIN ( SELECT deal_id, belong_shop_id, title, market_price, price, discount_rate, income_money, synchron_time, div_ticket_name 

FROM o2o_wuu_gb_ticket 

WHERE create_shop_id = 83726 ) gb ON btpg.ticket_id = gb.deal_id AND btpg.create_shop_id = gb.belong_shop_id

INNER JOIN ( SELECT deal_id, belong_shop_id, MAX ( synchron_time ) synchron_time 

FROM o2o_wuu_gb_ticket 

WHERE create_shop_id = 83726 GROUP BY belong_shop_id, deal_id ) gbTicketFilter ON gb.deal_id = gbTicketFilter.deal_id 

AND gb.belong_shop_id = gbTicketFilter.belong_shop_id AND gb.synchron_time = gbTicketFilter.synchron_time

INNER JOIN ( SELECT payc_id, NAME FROM arch_o2o_seller ) os ON os.payc_id = btpg.payc_id

LEFT JOIN dbi_shop shop ON shop.ID = btpg.create_shop_id 

) bizzz 

ORDER BY

"shopName","ticketName","marketPrice","ticketRowId";

 limit 1;

首先比对执行计划,虽然仅仅是一个limit 的添加但是整体的执行计划都改变了，

3c72236aad842f6f8c639d662063ca88.png

改变发现1 

在没有添加LIMIT 1的情况下，整体的语句的查询中是没有 大量的seq scan ,基本上整体整体的语句中对于数据处理都是在index scan.

改变发现2 

在添加LIMIT 1 后，整体的语句的执行顺序，与原先的顺序不同了，不添加limit  1  ，首先处理了语句中最大的表，由于最大的表的数据过滤的条件多，所以对于排除数据起到了相关的提前过滤的作用。

而添加了limit 1后，整体的语句处理的顺序和语句撰写的从上到下的语句关联的顺序基本一致，导致处理从小表开始进行预先处理。最终导致小表驱动大表的情况。

改变发现3

在不添加LIMIT 1 的情况下，整体上层的 语句中的计算部分使用中由于，执行顺序的问题，让大表的数据过滤后，在被上层使用，减少了计算的数据量，而反观添加了LIMIT 1 后的语句，计算中过滤的行较多。导致计算成本升高。

sort  的部分中的语句执行的整体计划的顺序，与加入了limit 的整体计划的顺序是错位的。

SORT 没有limit 的部分，中的驱动表与驱动表之间是通过index 关联的方式进行的处理。

fc39cb5fbca225e68cb931fad95c3926.png

limit   而在末尾加入了limit 后，整体的执行计划，驱动表和被驱动表的位置互换了，通知对于驱动表的执行的方式变为了扫描方式。

fc42f5c3c726f922781c10803bbcddbf.png

由于语句是否的复杂，如果要非常的明白的分析出来，则需要很长的时间，实际上抓住了两点

1 复杂的语句如果使用了limit 1，需要注意在POSTGRESQL 13中的语句是否还能通过优化器，优先判断对数据进行大表过滤，由于使用LIMIT 1 这样的语句，导致语句优化和执行系统对于提取的数据的有序性判断过于复杂，导致优化器，按照语句的撰写方式进行了数据的执行，保证提取数据的优秀和有效性，同时多个不同表的字段最终进行排序，加重了执行分析器的负担，导致执行分析器躺平，做了保守的执行计划的操作。

2  业务逻辑是否需要对于LIMIT 1 的语句进行排序的分析，这点非常有必要，在语句的执行中大部分语句的撰写尤其类似这样OLAP 很重的语句一般都带有排序，但是如果只是在结果中取一个结果  limit 1 则是否有必要进行排序这点非常有必要进行确认。一般根据语句的逻辑，是没有必要进行排序在LIMIT 1，因为你是随机取和顺序是无关的。

这个语句实际上最后优化的手段就是去掉ORDER BY ，最终去掉后比原先的同样的条件，执行的效率提高了  62倍，在 150毫秒左右就将结果计算出来,同时还有一个因素是如果你在撰写语句的时候带有LIMIT 1 则POSTGRESQL 的优化器会优先选择计算成本中，第一个启动成本较低的执行计划而不是整体成本较低的执行计划，所以建议在一些语句中，考虑业务的需求的情况下，分析是否有必要进行排序，慎用在复杂语句中的排序导致的执行效率低下的问题。

另外这里还有一个在使用LIMIT 1 后导致的PG执行计划的倾向性的问题，你造吗 ！

181c7c700dd245e4a153a218c53b2f9b.png

48f93969e31d1cd5c1b10670235784bb.png

4a1c4d3562afd623df693ebce9b3ff2b.png

Austindatabases
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
PostgreSQL 10 基础课程第三章 常用SQL语句Li
11-01
PostgreSQL 10 基础课程第三章 常用SQL语句Limit限制查询结果
PostgreSQL 神奇的limit
Focus on PostgreSQL
12-23 3336
最近碰到这样一个SQL引发的性能问题，SQL内容大致如下： SELECT * FROM t1 WHERE id = 999 AND (case $1 WHEN 'true' THEN info = $2 ELSE info = $3 end) limit 1; 开发反应这条SQL加上limit 1之后过了一段时间从原先的索引扫描变成了全表扫描，一个简单的limit 1为何会产生这样的影响，我只取一条数据不是应该更快了吗？ 下面我们就从这条SQL开始说起。 首先我们先看下这个表结构，
不同数据库分页 限制第一条postgreSql、mysql、sqlServer、Oracle总结
wuyuanshun的博客
09-17 317
不同数据库分页 限制第一条postgreSql、mysql、sqlServer、Oracle总结 postgreSql 后加 limit 1 OFFSET 0 select * from table where name='aaa' limit 1 OFFSET 0 mysql 后加 limit 1 select * from table where name='aaa' limit 1 sqlServer 查询条件前加 top 1 select top 1 * from table Oracle
mysql limit 分页的用法及注意要点
12-16
mysql limit 分页的用法及注意事项： 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须 是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回 记录行的最大数目。初始记录行的偏移量是 0(而不是 1):为与PostgreSQL兼
详解MySQL的limit用法和分页查询语句的性能分析
12-16
limit用法 在我们使用查询语句的时候，经常要返回前几条或者中间某几行数据，这个时候怎么办呢？不用担心，mysql已经为我们提供了这样一个功能。 SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)： 为了与 PostgreSQL 兼容，MySQL 也支持句法
PostgreSQL limit 语法
qq_35987023的博客
10-11 2965
其中，SELECT 语句用于指定要查询的列和数据表，LIMIT 用于指定查询结果的行数。如果不指定 LIMIT，则会返回所有满足条件的行。需要注意的是，在使用 LIMIT 时，应避免仅按照默认排序顺序进行查询，否则可能会得到不稳定的结果。如果需要按照指定的排序顺序进行查询，应首先使用 ORDER BY 子句明确指定排序规则。除了可以指定一个整数值外，LIMIT 还可以使用一个 OFFSET 参数指定查询结果的起始位置。例如，LIMIT 10 OFFSET 5 表示从第 6 行开始，返回 10 行结果。
postgresql和mysql中的limit使用方法
刘大猫
03-03 5178
文章目录区别举例说明MySQL 中PostgreSQL 中 区别 在msyql中，limit使用如下 select *from mytable limit a,b 其中：a为起始索引，从0开始，b为获取数据长度 在postgresql中，limit使用如下 select *from mytable limit a offset b 其中：b为起始索引，a为获取数据长度 其中：mysql使用逗号"，"，而pgsql使用offset 举例说明 MySQL 中 如果只给定一个参数，表示记录数。 mysq
Postgresql limit sql性能分析
MyySophia的博客
04-20 1582
问题描述 分析 表的信息 估算cost start-up cost run cost 执行计划 实际执行时间 从内核视角来分析 解决方案 写在最后 Coding过程中经常会写SQL语句，有时写的SQL出现慢查询而被DBA鄙视。 今天我们一起从使用者，DBA，内核开发三个不同角度来分析和解决一个SQL性能问题，来重新认识逻辑优化与物理优化。 ..
sql优化limit 1
m0_71117928的博客
09-19 125
sql查询优化
PostgreSQL Limit分页写法
reading-conference-writing
12-30 4906
介绍PostgreSQL的Limit分页用法，并与MySQL做简单对比
MySQL查询优化:LIMIT 1避免全表扫描提高查询效率
01-21
在某些情况下，如果明知道查询结果只有一个，SQL语句中使用LIMIT 1会提高查询效率。 例如下面的用户表(主键id,邮箱,密码)： 代码如下: create table t_user( id int primary key auto_increment, email varchar(255), password varchar(255) ); 每个用户的email是唯一的，如果用户使用email作为用户名登陆的话，就需要查询出email对应的一条记录。 SELECT * FROM t_user WHERE email=?; 上面的语句实现了查询email对应的一条用户信息，但是由于ema
PostgreSQL LIMIT 子句
12-16
PostgreSQL 中的 limit 子句用于限制 SELECT 语句中查询的数据的数量。 语法 带有 LIMIT 子句的 SELECT 语句的基本语法如下： SELECT column1, column2, columnN FROM table_name LIMIT [no of rows] 下面是 LIMIT ...
sqlserver 分页_四类数据库分页实现方案总结之PG分页实现
weixin_39928993的博客
11-28 351
概述前面介绍了Oracle、mysql、sqlserver的分页实现方案了，所以今天主要介绍一下关于PG的分页实现。传统的精准分页(能获取到总页数并且任意跳转页码)在需要符合条件的记录总数目,不过pg在这个地方是一个大坑,pg的count查询性能不行(目测不如mysql,sqlserver,mongodb)。pg官网wiki给了个count estimate(https://wiki.postgr...
什么时候适合用 Limit(1) 呢？
C_x_330的博客
10-21 652
什么时候适合用 Limit(1) 呢？
SQL优化——limit1
qq_45979629的博客
11-15 1169
还在用 select count(*) from xx where xx=?吗
postgres LIMIT 1后COUNT（*）给出1个以上的结果
zjc348的博客
04-28 853
SELECT COUNT(*) as sum FROM data WHERE aa < 100 LIMIT 1; sum -------- 1160 (1 row) 我预期结果为1或0，但结果为1160 原因： Postgres读取所有行 FROM data WHERE aa < 100 Postgres然后将count(*)它们 Postgres然后LIMIT是 count ...
sql语句中limit的使用
qq_48651829的博客
11-29 959
sql中LIMIT的用法。
PostgreSQL LIMIT和OFFSET
热门推荐
林胖的专栏
12-04 1万+
PostgreSQL中LIMIT和OFFSET关键字 LIMIT和OFFSET允许你只检索查询剩余部分产生的行的一部分： LIMIT : 限制取多少条数据。 OFFSET : 跳过多少条数据然后取后续数据。 LIMIT 和 OFFSET 关键字在查询时可以单独使用也可以组合使用， 需要注意的是，无论怎么用，都需要先确定排序方式，否则没有多少意义。例如： 取库存移动的前10条记录： SELEC...
OpenNJet下载安装及入门实战教程
最新发布
在下马农的碎碎念
05-06 647
OpenNJet作为一款优秀的云原生应用引擎，具备轻量、快速开发、易于集成等优点，即减少了资源占用，部署简单，又能使应用在系统上流畅运行。此外，不仅100%兼容Nginx，还实现了NGINX 云原生功能增强、安全加固和代码重构，利用动态加载机制可以实现不同的产品形态，如Web服务器、流媒体服务器、负载均衡、代理(Proxy)、应用中间件、API网关、消息队列等产品形态等等。
postgresql中的sql语句group by和order by先执行哪一个
07-15
在 PostgreSQL 中，SQL 语句中的 `GROUP BY` 和 `ORDER BY` 子句是按照以下顺序执行的： 1. `FROM` 子句：指定要查询的表或视图。 2. `WHERE` 子句：筛选行。 3. `GROUP BY` 子句：按照指定的列对结果进行分组。 4. `HAVING` 子句：筛选分组后的结果。 5. `SELECT` 子句：选择要返回的列。 6. `ORDER BY` 子句：按照指定的列对结果进行排序。 7. `LIMIT` 子句：限制返回结果的数量。 所以，在执行顺序上，`GROUP BY` 子句会在 `ORDER BY` 子句之前执行。首先，数据将按照 `GROUP BY` 中指定的列进行分组，然后在每个分组内部进行排序。最后，整个结果集将按照 `ORDER BY` 中指定的列进行排序。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值