错误： Aggregate query has too many rows -> 不需要用子查询

最新推荐文章于 2024-11-14 20:46:30 发布
最新推荐文章于 2024-11-14 20:46:30 发布
阅读量138 收藏
点赞数
本文探讨了一个具体的Salesforce平台上的SOQL查询错误，即当查询返回过多记录时无法直接分配的问题。通过一个错误示例，文章解释了如何避免使用直接赋值的方式处理大量数据，并建议采用循环来逐条处理查询结果，以确保系统的稳定运行。

摘要生成于 C知道 ，由 DeepSeek-R1 满血版支持， 前往体验 >

Aggregate query has too many rows for direct assignment, use FOR loop

 

 

错误的代码：

 if (query == '' || query == null){
   query = 'SELECT id, name, ownerid, Planned_Visits__c,  Actual_Visits__c, Visits_last_Year__c, ' +
     '  (SELECT AccountId, id, Type, ActivityDate, Visit_Report__c ' +
     '   FROM events WHERE AccountId != null and Type != null and ActivityDate != null and Type = \'Sales Visit\' and Visit_Report__c = TRUE AND  ) ' +
     'FROM Account where owner.IsActive = true ' +
     //'     AND id IN(\'001P000000ZKblDIAT\', \'001P000000ZKboRIAT\', \'001P000000ZKd55IAD\', \'001P000000ZKd5KIAT\', \'001P000000ZKd5UIAT\')' +
     '';
  }

2022-09-07 mysql8-对IN操作符-优化为EXISTS
悟世之路
09-07 775
mysql8对于IN操作符, 存在使用EXISTS进行优化的策略。本文分析mysql8是如何实现此优化的。
Mysql学习总结（15）——Mysql错误码大全
科技D人生
05-06 3125
B.1. 服务器错误代码和消息 服务器错误信息来自下述源文件： ·         错误消息信息列在share/errmsg.txt文件中。“%d”和“%s”分别代表编号和字符串，显示时，它们将被消息值取代。 ·         错误值列在share/errmsg.txt文件中，用于生成include/mysqld_error.h和include/mysqld_ername.h 
使用子查询好不好？
minwang593的专栏
10-10 2124
使用子查询好不好？针对这个问题，本人旗帜鲜明地回答：不好，原因如下：       n  SQL语言的子查询在大部分情况下，原理上等同于多表连接操作。因此，oracle优化器在解析(parse)阶段，会尽可能将子查询转化为多表连接操作。因此，开发人员书写子查询语句，很多情况下多此一举。 n  子查询书写方式导致SQL语句冗长，可读性下降。本人见过的好几屏幕的SQL语句，几乎都包含大量的子查
为什么MySQL不推荐使用子查询和join
Java笔记虾
03-14 992
来源：cnblogs.com/liboware/p/12740901.html1.对于mysql，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，...
Linux中MySQL异常Packet for query is too large
老象的专栏
04-05 431
找到配置文件/etc/my.cnf 并添加以下语句： max_allowed_packet=20M 修改完之后重启服务： service mysqld restart 通过语句查询： SHOW VARIABLES LIKE '%max_allowed_packet%' ...
System.QueryException: Aggregate query has too many rows for direct assignment, use FOR loop
Debug yourself!
11-14 637
使用FOR循环处理大型聚合查询结果。通过考虑LIMIT和在必要时使用批处理，确保查询结果的大小是可管理的。
大数据常见错误解决方案（转载）
defang0693的博客
02-24 3324
1、用./bin/spark-shell启动spark时遇到异常：java.net.BindException: Can't assign requested address: Service 'sparkDriver' failed after 16 retries! 解决方法：add export SPARK_LOCAL_IP="127.0.0.1" to spark-env....
MySQL常用错误码
An丶的博客
03-23 2095
错误：1000 SQLSTATE: HY000 (ER_HASHCHK) 消息：hashchk错误：1001 SQLSTATE: HY000 (ER_NISAMCHK) 消息：isamchk错误：1002 SQLSTATE: HY000 (ER_NO) 消息：NO错误：1003 SQLSTATE: HY000 (ER_YES) 消息：YES错误：1004 SQLSTATE: HY000 (ER_C...
1Z0-051考试笔记
wlbzy的专栏
11-16 6999
ORACLE 如何建立表外键 例：学生表student (id, name , sex )     成绩表score (id ,math )   如何创建表，要求 有主键，有约束 解:   CREATE TABLE STUDENT(ID CHAR(10), NAME VARCHAR(8),SEX CHAR(1));     ALTER TABLE STUDENT ADD CONS
Oracle事件诊断列表
热门推荐
10-13 1万+
oracle事件诊断列表，从0到20000，并输出到文件oracle.txt中。 SET linesize 120 SET feedback off SET SERVEROUTPUT ON spool "d:\oracl.txt" DECLARE err_msg VARCHAR2(120); BEGIN dbms_output.enable(1000000); FOR err
PL/pgSQL初学之路（三）
Daisy_Wang777的博客
10-29 2535
本系列为本人初学plpgsql语言的笔记，记录自己在实现算法、参考他人代码时遇到的简单语法。
为什么MySQL不推荐使用子查询和JOIN
Java搜索工程技术栈
02-24 1178
1.对于mysql，不推荐使用子查询和join是因为本身join的效率就是硬伤，一旦数据量很大效率就很难保证，强烈推荐分别根据索引单表取数据，然后在程序里面做join，merge数据。 2.子查询就更别用了，效率太差，执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程。 3.如果是JOIN的话，它是走嵌套查询的。小表驱动大表，且通过索引字段进行关联。如果表记录比较少的话，还是OK的。大的话业务逻辑中可以控制处理。
多表查询
sjwl0526的博客
09-09 1685
多表查询 概述 基础语法： select ​ 列名列表 from ​ 表名列表 where… 多表查询的分类 内连接查询： 隐式内查询： 使用where消除无用的数据 显式内查询 外链接查询： 子查询：
hbase build Too many unapproved licenses
bupt041137的专栏
12-30 288
  [ERROR] Failed to execute goal org.apache.rat:apache-rat-plugin:0.8:check (default) on project hbase: Too many unapproved licenses: 2856 -> [Help 1]   在target/rat.txt下面寻找哪些文件前面打了问号，去掉这些文件！ ...
mysql不用子查询查最大值_mysql 两种子查询的区别 from和 where ,分组查询最大值
weixin_39866741的博客
02-07 362
前置：mysql 环境create table goods (goods_id mediumint(8) unsigned primary key auto_increment,goods_name varchar(120) not null default '',cat_id smallint(5) unsigned not null default '0',brand_id smallint(...
Sort (cost=50966332402.09..50966332727.83 rows=130297 width=2263) Sort Key: b.modify_time DESC NULLS LAST -> Nested Loop Left Join (cost=3219.70..50966193960.42 rows=130297 width=2263) -> Nested Loop Left Join (cost=3219.28..50962082798.25 rows=130297 width=1137) Join Filter: ((a.storyid)::text = (desensitize_config.storyid)::text) -> Merge Left Join (cost=1002.71..50826114488.91 rows=130297 width=1095) Merge Cond: ((b.story_id)::text = (a.storyid)::text) -> Nested Loop Semi Join (cost=1002.29..50825843973.85 rows=130297 width=394) Join Filter: ((b.operator_code)::text = (regexp_split_to_table('U91000001,UVCN100003566,U1002513975,U14500001'::text, ','::text))) -> Gather Merge (cost=1002.29..50819699064.53 rows=446196 width=394) Workers Planned: 2 -> Merge Left Join (cost=2.26..50819646562.42 rows=185915 width=394) Merge Cond: ((b.story_id)::text = (main.story_id)::text) Filter: (CASE WHEN (main.story_id IS NULL) THEN '0'::text WHEN (main.set_time > CURRENT_TIMESTAMP) THEN '1'::text ELSE '2'::text END <> '2'::text) -> Parallel Index Scan using storyedit_storyinfo_pkey1 on storyedit_storyinfo b (cost=0.55..50819646073.62 rows=194768 width=240) Filter: ((publish = ANY ('{0,1}'::bigint[])) AND (story_type = ANY ('{1,3}'::numeric[])) AND ((author_code)::text <> '684233758342959'::text) AND (NOT (SubPlan 3))) SubPlan 3 -> Materialize (cost=0.00..181020.10 rows=5862873 width=16) -> Seq Scan on threecloud_developer d (cost=0.00..123077.73 rows=5862873 width=16) -> Sort (cost=1.71..1.77 rows=22 width=154) Sort Key: main.story_id -> Seq Scan on story_sunset_main main (cost=0.00..1.22 rows=22 width=154) -> Materialize (cost=0.00..20.02 rows=1000 width=32) -> ProjectSet (cost=0.00..5.02 rows=1000 width=32) -> Result (cost=0.00..0.01 rows=1 width=0) -> Index Scan using story_published_pkey on story_published a (cost=0.42..269613.73 rows=162357 width=701) -> Materialize (cost=2216.57..3955.75 rows=69567 width=42) -> HashAggregate (cost=2216.57..2912.24 rows=69567 width=42) Group Key: desensitize_config.storyid -> Seq Scan on desensitize_config (cost=0.00..2037.06 rows=71806 width=42) -> Index Scan using story_counttimes_pkey on story_counttimes (cost=0.42..5.37 rows=1 width=74) Index Cond: ((story_id)::text = (a.storyid)::text) SubPlan 1 -> Limit (cost=9.15..13.16 rows=1 width=42) -> Bitmap Heap Scan on story_collection c (cost=9.15..13.16 rows=1 width=42) Recheck Cond: (((storyid)::text = (b.story_id)::text) AND ((usercode)::text = '684233758342959'::text)) -> BitmapAnd (cost=9.15..9.15 rows=1 width=0) -> Bitmap Index Scan on pk_sc_storyid_0502 (cost=0.00..4.44 rows=3 width=0) Index Cond: ((storyid)::text = (b.story_id)::text) -> Bitmap Index Scan on pk_sc_usercode_0502 (cost=0.00..4.46 rows=6 width=0) Index Cond: ((usercode)::text = '684233758342959'::text) SubPlan 2 -> Limit (cost=9.00..13.01 rows=1 width=42) -> Bitmap Heap Scan on story_likes c_1 (cost=9.00..13.01 rows=1 width=42) Recheck Cond: (((usercode)::text = '684233758342959'::text) AND ((storyid)::text = (b.story_id)::text)) -> BitmapAnd (cost=9.00..9.00 rows=1 width=0) -> Bitmap Index Scan on pk_sl_usercode_0502 (cost=0.00..4.31 rows=3 width=0) Index Cond: ((usercode)::text = '684233758342959'::text) -> Bitmap Index Scan on pk_sl_storyid_0502 (cost=0.00..4.43 rows=3 width=0) Index Cond: ((storyid)::text = (b.story_id)::text)
最新发布
07-24
<think>我们正在处理一个关于PostgreSQL查询计划优化的问题，特别是排序（Sort）和嵌套循环左连接（Nested Loop Left Join）操作。用户希望理解并优化这些操作。 首先，我们需要理解这些操作在PostgreSQL中是如何工作的，以及为什么优化器会选择它们。然后，我们可以讨论如何优化这些操作。 ### 1. 排序（Sort）操作 排序操作通常出现在以下情况： - 使用`ORDER BY`子句 - 为归并连接（Merge Join）准备数据 - 为窗口函数（Window Functions）准备数据 - 使用`DISTINCT`或`GROUP BY`（有时，如果数据已经排序，可能不需要显式排序） 排序是一个昂贵的操作，尤其是当数据量很大时。在PostgreSQL中，排序操作可能会使用磁盘空间（如果工作内存不足），这会导致性能下降。 #### 优化排序操作的方法： - **增加工作内存（work_mem）**：通过增加`work_mem`参数，可以让排序在内存中完成，避免使用磁盘。但是，设置过高可能会导致内存压力。 - **使用索引**：如果排序的列上有索引（如B-tree索引），那么数据库可能直接使用索引的顺序，从而避免显式排序。 - **减少排序的数据量**：通过`WHERE`条件过滤掉不需要的行，或者只选择必要的列，以减少排序的数据量。 - **使用LIMIT**：如果查询包含`LIMIT`子句，优化器可能会选择使用能够避免完全排序的查询计划（例如，使用索引扫描）。 ### 2. 嵌套循环左连接（Nested Loop Left Join） 嵌套循环连接的工作方式是：对于外层表的每一行，在内层表中查找匹配的行。左连接意味着即使内层表没有匹配，也要返回外层表的所有行。 嵌套循环连接在以下情况效率较高： - 外层表很小 - 内层表有高效的索引（特别是连接条件上的索引） 但是，如果外层表很大，或者内层表没有索引，嵌套循环连接可能会非常慢（时间复杂度为O(n*m)）。 #### 优化嵌套循环左连接的方法： - **确保内层表有索引**：在连接条件的列上创建索引，这样内层表的查找可以快速完成。 - **减少外层表的大小**：通过过滤条件减少外层表的行数。 - **考虑使用其他连接类型**：如果嵌套循环连接效率低下，可以考虑使用哈希连接（Hash Join）或归并连接（Merge Join）。但是，这通常需要重写查询或调整配置参数（如`enable_nestloop`）来影响优化器的选择。 ### 结合排序和嵌套循环左连接的优化 如果查询计划中同时出现了排序和嵌套循环左连接，我们需要分析： 1. 排序操作是否必须？是否可以通过索引避免？ 2. 嵌套循环左连接是否高效？内层表是否有索引？ ### 示例分析 假设我们有如下查询： ```sql SELECT * FROM orders LEFT JOIN order_details ON orders.id = order_details.order_id WHERE orders.date > '2020-01-01' ORDER BY orders.date; ``` 可能的优化： 1. 在`orders.date`上创建索引，这样可以利用索引过滤（WHERE条件）和排序（ORDER BY）。这样可能避免显式排序操作。 2. 在`order_details.order_id`上创建索引，以加速嵌套循环连接的内层表查找。 ### 调整优化器行为 PostgreSQL的优化器基于成本模型。我们可以通过调整成本参数（如`random_page_cost`、`cpu_tuple_cost`等）或设置特定开关（如`enable_nestloop`、`enable_sort`）来影响优化器的选择。但是，这些调整需要谨慎，最好在测试环境中进行。 此外，使用`EXPLAIN`和`EXPLAIN ANALYZE`命令分析查询计划是优化的关键步骤。 ### 相关问题 根据用户的问题，我们可以生成以下相关问题： 1. 如何阅读和理解PostgreSQL的查询执行计划？ 2. 在什么情况下嵌套循环连接比哈希连接或归并连接更有效？ 3. 如何确定是否应该为排序操作增加`work_mem`？ 4. 如何创建合适的索引以优化连接和排序操作？ 5. 如何调整PostgreSQL的配置参数以优化查询性能？ ### 总结 优化排序和嵌套循环左连接操作的关键在于： - 通过索引避免不必要的排序 - 确保嵌套循环连接的内层表有高效的索引 - 调整内存参数（如`work_mem`）以支持排序操作 - 分析查询计划，理解优化器的选择 通过以上方法，我们可以显著提升包含排序和嵌套循环左连接操作的查询性能。 §§相关问题§§ 1. 如何为PostgreSQL的排序操作选择合适的work_mem大小？ 2. 嵌套循环连接在哪些场景下是最优的选择？ 3. 如何通过索引优化来避免排序操作？ 4. 在PostgreSQL中，如何强制优化器选择其他连接方法（如哈希连接）？ 5. 如何分析一个查询执行计划，并识别性能瓶颈？</think>### PostgreSQL 查询计划优化：排序与嵌套循环左连接 #### 一、排序操作（Sort）的优化原理 排序操作在以下场景触发： - `ORDER BY` 子句显式排序 - 窗口函数（如 `ROW_NUMBER()`）需要排序 - `DISTINCT`/`GROUP BY`（当哈希聚合不可用时） - 归并连接（Merge Join）的预处理 **优化策略：** 1. **索引避免显式排序** 在排序字段创建 B-tree 索引可使优化器直接使用索引顺序： ```sql CREATE INDEX idx_orders_date ON orders(order_date); -- 使 ORDER BY order_date 免排序 ``` 2. **调整内存参数** ```sql SET work_mem = '64MB'; -- 增加排序内存（避免磁盘临时文件） ``` *建议：通过 `EXPLAIN ANALYZE` 观察 `Sort Method`，若出现 `external merge Disk` 则需增加 `work_mem`*[^1]。 3. **LIMIT 优化** 结合 `LIMIT` 时，使用索引扫描可避免全表排序： ```sql SELECT * FROM orders ORDER BY order_date LIMIT 10; -- 需索引支持 ``` 4. **分区表优化** 对分区表排序时，确保 `enable_partitionwise_aggregate=on` 以并行排序子分区。 --- #### 二、嵌套循环左连接（Nested Loop Left Join）优化 嵌套循环连接的工作逻辑： ```mermaid graph LR A[外层表驱动] --> B[遍历每行] B --> C[内层表索引扫描] C --> D[返回匹配行] ``` **性能瓶颈：** - 外层表行数过多（$O(N \times M)$ 复杂度） - 内层表无高效索引 **优化方案：** 1. **内层表索引优化** ```sql CREATE INDEX idx_order_details_order_id ON order_details(order_id); -- 内层表必须索引 ``` *验证：执行计划应出现 `Index Scan` 而非 `Seq Scan`*[^2]。 2. **减少外层表数据集** ```sql SELECT * FROM (SELECT * FROM orders WHERE status='shipped') AS filtered_orders -- 先过滤 LEFT JOIN order_details ON ... ``` 3. **参数调优** ```sql SET random_page_cost = 1.1; -- SSD 环境降低随机读成本 SET enable_nestloop = ON; -- 确保嵌套循环可用 ``` 4. **替代连接方式** 当数据量较大时，考虑强制使用哈希连接： ```sql SET enable_hashjoin = ON; SET enable_mergejoin = OFF; ``` --- #### 三、联合优化案例 **问题查询：** ```sql EXPLAIN ANALYZE SELECT o.order_id, c.name, SUM(od.amount) FROM orders o LEFT JOIN order_details od ON o.order_id = od.order_id LEFT JOIN customers c ON o.cust_id = c.cust_id WHERE o.order_date > '2023-01-01' GROUP BY o.order_id, c.name ORDER BY SUM(od.amount) DESC; ``` **优化步骤：** 1. **索引创建** ```sql CREATE INDEX idx_orders_date ON orders(order_date); CREATE INDEX idx_order_details_order_id ON order_details(order_id); CREATE INDEX idx_customers_id ON customers(cust_id); ``` 2. **改写查询** ```sql WITH filtered_orders AS ( SELECT * FROM orders WHERE order_date > '2023-01-01' -- 提前过滤 ) SELECT fo.order_id, c.name, SUM(od.amount) FROM filtered_orders fo LEFT JOIN order_details od ON fo.order_id = od.order_id -- 索引扫描 LEFT JOIN customers c ON fo.cust_id = c.cust_id -- 索引扫描 GROUP BY fo.order_id, c.name ORDER BY SUM(od.amount) DESC; ``` 3. **参数调整** ```sql SET work_mem = '128MB'; -- 支持排序和哈希聚合 SET max_parallel_workers_per_gather = 4; -- 启用并行 ``` --- #### 四、执行计划关键诊断点 1. **排序诊断** ```bash Sort (cost=2874.28..2891.75 rows=6988 width=48) Sort Key: (sum(od.amount)) DESC Sort Method: quicksort Memory: 5MB --> 若显示 disk 需优化 ``` 2. **连接诊断** ```bash Nested Loop Left Join (cost=0.85..125.76 rows=98 width=48) Join Filter: (o.cust_id = c.cust_id) -> Index Scan using idx_orders_date... --> 理想状态 -> Seq Scan on customers c --> 危险信号（需索引） ``` --- #### 五、进阶优化工具 1. **扩展插件** ```sql CREATE EXTENSION pg_stat_statements; -- 识别高成本排序/连接 CREATE EXTENSION hypopg; -- 虚拟索引测试 ``` 2. **优化器提示**（需 pg_hint_plan） ```sql /*+ NestLoop(od c) Leading(o od c) */ SELECT ... -- 强制连接顺序 ``` > 关键原则：**通过索引将随机访问转化为顺序访问，通过过滤减少参与运算的数据量**。实际优化需结合 `EXPLAIN (ANALYZE, BUFFERS)` 数据验证[^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值