如何给Large Delete操作提速近千倍？(五)

最新推荐文章于 2025-12-18 18:23:53 发布
原创 最新推荐文章于 2025-12-18 18:23:53 发布 · 2.9k 阅读 · 2 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#delete #sql #integer #优化 #测试 #作业

博客围绕SQL批量作业优化展开，第四次优化使用FORALL改写拆分DELETE操作，应用SELECT BULK COLLECT INTO方法批量绑定。经不同数量记录删除测试，发现批量删除虽能解决一定问题，但操作数组过大时执行结果会受影响，进而引出第五次优化思路。

 

1.1. 第四次优化——使用FORALL 处理批量作业

上面我们已经介绍了批量作业的一些概念，这里不再赘述。

这次优化的思想主要是使用FORALL的思路来改写“第三次优化——拆分DELETE操作”，具体的修改如下：

SQL> l

  1  create or replace procedure del_hubei_SSR_forall

  2  as

  3       type ridArray is table of rowid index by binary_integer;

  4       type dtArray is table of varchar2(50) index by binary_integer;

  5

  6       v_rowid ridArray;

  7       v_mid_to_delete dtArray;

  8

  9  begin

 10       select mid,rowid bulk collect into v_mid_to_delete,v_rowid from temp_mid_hubei where rownum<11;

 11

 12      forall i in 1 .. v_mid_to_delete.COUNT

 13           delete from SSR where mid = v_mid_to_delete(i);

 14

 15             DBMS_OUTPUT.PUT_LINE('Full Finished!!!');

 16             DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_mid_to_delete.COUNT)||' records deleted from hubei_SSR   !!!');

 17

 18       forall i in 1 .. v_rowid.COUNT

 19           delete from temp_mid_hubei where rowid = v_rowid(i);

 20

 21             DBMS_OUTPUT.PUT_LINE('Full Finished!!!');

 22             DBMS_OUTPUT.PUT_LINE('Totally '||to_char(v_rowid.COUNT)||' records deleted from temp_mid_hubei   !!!');

 23

 24* end;

SQL> /

 

Procedure created.

 

Elapsed: 00:00:00.07

SQL>

SQL>  exec del_hubei_SSR_forall;

Full Finished!!!

Totally 10 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 10 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.16

SQL>

 

 

这里应用了上面我们说过的SELECT BULK COLLECT INTO 方法来进行批量绑定，根据以10条记录为一组的测试结果表名，速度比较理想，于是再测1000条记录的情况：

 

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 1000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 100 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:00.66

SQL>

 

 

测试结果还是比较稳定，于是再测10000和100000条记录的情况：

 

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 10000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 10000 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:00:04.07

SQL>

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 100000 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 100000 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:03:51.29

SQL>

 

这里我们注意到，完成删除10000记录需要4秒中，而完成100000条记录的删除需要将近4分钟，也就是说，从删除10000条记录到删除100000条记录，所需要的操作时间已经出现了比较大的倍数关系。

 

下面再测试一下50万条记录的情况：

SQL> exec del_hubei_SSR_forall;

Full Finished!!!

Totally 509555 records deleted from hubei_SSR   !!!

Full Finished!!!

Totally 509555 records deleted from temp_mid_hubei   !!!

 

PL/SQL procedure successfully completed.

 

Elapsed: 00:39:04.41

SQL>

 

这里，删除500000条记录已经需要将近40分钟了！

到这里，基本上可以得出结论，使用批量删除虽然可以解决一定的问题，但是如果需要操作的数组太大，那么执行结果会打打折扣，这个是不难理解的。

于是，第五次优化的思路就产生了：

1)        分段处理

2)        拆分操作

3)        批量绑定

 

 

mysql5.7性能提升一百倍调优宝典
打造全国最全的AI Agent开发知识领域的博客
05-06 1万+
全文中一共有常用的（事实上你如果花1-2周阅读、理解、自己动手设一下后是需要这么多参数的）76个参数，我是按照： 每一个参数干吗？ 在某些典型硬件配置下的db上参数该设多少？ 设会怎么样？ 不设会怎么样？ 有什么坑如何填坑？ 有些参数怎么算、算法又如何 这种style来写的，具体请看文档！ 一千个DBA就有一千种配置方式! 大家一定记得不要轻易去看网上，要看只看官网！网上很多博客都...
如何给Large Delete操作提速近千倍？(一)
热门推荐
lunar的小铺
06-27 1万+
本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。1.   背景描述1.1. 任务描述这个任务是需要从一系列大表中清理3个省的大批过时数据，具体的清理过程简单的说就是：首先根据不同的miscid的值创建不同的临时表，类似于： CREATE TABLE temp_midASSELECT mid FROM ssr WHERE SUBSTR(ssid,1,7) IN(SELECT prefixn
如何给Large Delete操作提速近千倍？(六)
lunar的小铺
06-27 3495
 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。1.1. 第五次优化——使用FORALL ＋ 原子级操作这一次的优化过程已经很清晰了，主要是综合了前面几次优化的思想，将他们的优点集中使用，即：1)        分段处理：将整个大的事务分割为以10000行为单位的小事务，其中，10000这个基数来自于上面使用批量测试时的结果（
如何给Large Delete操作提速近千倍
10-20
如何给Large Delete操作提速近千倍
如何给Large Delete操作提速近千倍？(七)
lunar的小铺
06-27 3657
本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。  1.1. 总结       从最初几乎每次都因为抱ORA-01555错误而无法完成的一个DELETE语句，到删除100万记录需要20多个小时，再发展到现在的删除90万记录只需要不到3分钟，速度提高了近千倍，这里主要的思想是：1，  避开繁琐的查询条件（即避开了庞大到上百万个元素的in-list）2，  使用原子级的处理方式（使用两个原子
如何给Large Delete操作提速近千倍？(四)
lunar的小铺
06-27 2917
 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。1.1. 第三次优化——拆分DELETE操作首先，我尝试从临时表 temp_mid_hubei 中取出一行记录：SQL> select mid ,rowid from  temp_mid_hubei where rownum=1;  MID            ROWID---
如何给Large Delete操作提速近千倍？(二)
lunar的小铺
06-27 3471
 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。 本文已经发表在ITPUB优化技术丛书，未经许可，不得转载。1.   优化过程详解1.1. 第一次优化——处理庞大的IN-LIST操作回到我们最初的需求，首先看看这个删除工作的基本语句，请注意这个IN 操作，由于这个TEMP_MID_HUBEI 表有130多万行记录，这将是一个多么庞大的IN-LIST!  DELETE from SSF 
ESP32串口解析性能瓶颈定位：字符串处理到JSON解码的4倍提速方案
!...# 1. ESP32串口通信中的性能瓶颈现象分析 在ESP32的串口通信应用中，高波特率（如115200及以上）下数据吞吐不稳、CPU占用率异常升高等问题频现。常见表现为接收缓冲区溢出、任务调度延迟或看门狗复位，尤其在处理...
dbt + Snowflake 增量实践：利用MERGE语句优化变更数据处理，提速70%实测案例
在今天，当一个电商平台每秒产生上千笔订单、一款 App 每分钟涌入数万用户行为事件时——你还敢用“全量跑一遍”来更新你的客户维度表吗？ 我们早已经告别了那种“下班前点一下 ETL，第二天早上看结果”的时代。...
oracle张大鹏,Oracle数据库性能优化
weixin_39554290的博客
04-05 237
目录第一篇 优化工具篇第 1 章 DBA 优化之路1.1 学习的建议1.2 工具推荐1.3 关于操作系统方面的建议1.4 关于Oracle初始化参数的调整1.5 关于Statspack的若干建议1.6 关于logmnr在调优中的运用1.7 关于materialized view在调优中的运用1.8 关于Stored Outline在SQL优化中的运用1.9 用dbms_profiler调优存储过程...
LARGE DELETE快速删除
06-28 184
看了ITPUB出的9I优化中《如何给Large Delete操作提速近千倍？》，今天做了个测试。需求：删除表serviceinfo中sellername重复的记录，LOGINID为主键服务器：IBM336 1G内存(少了点)数据...
海外开发者实践分享：用 MoonBit 开发 SQLC 插件（其二）
最新发布
m0_74743788的博客
12-18 484
第一篇的链接：https://zenn.dev/4245ryomt/articles/9680434dc60c0c本文中使用的moon。
从 Oracle 到 SQL Server：金仓数据库迁移落地与社区支撑指南
稻草人
12-13 6370
金仓数据库KingbaseES在信创产业中展现出强劲竞争力，特别是在Oracle迁移适配方面表现突出。其内置迁移工具支持99%以上PL/SQL语法兼容，可视化操作实现全量迁移成功率超99%，相比达梦等产品改造量减少15%-20%。在政务和金融行业实践中，金仓能实现存储过程零改造迁移，并发量达10万TPS，与Oracle性能持平。针对SQLServer迁移，其XML函数和时间日期处理与源库保持100%行为一致性。金仓社区提供丰富迁移案例库和测试工具包，显著降低企业转型难度，是信创数据库国产化的优选方案。
MyBatis基础入门《十二》批量操作优化：高效插入/更新万级数据，告别慢 SQL！
小二丶的博客
12-15 964
本文详细介绍了MyBatis批量操作的三种实现方案及性能对比。针对大数据量场景，推荐使用SqlSession的BatchExecutor模式，通过JDBC的addBatch()实现高效批量写入；对于中小批量数据，可使用XML中的foreach标签构建单条INSERT语句；同时分析了Spring事务批量的局限性。文章还提供了生产环境最佳实践，包括分批处理、数据库调优等建议，并通过实测数据展示了不同方案的性能差异。最后解答了常见问题并给出场景化推荐方案，帮助开发者应对海量数据写入挑战。
oracle 数据库巡检 sql
zhangyongze_z的博客
12-18 68
【代码】oracle 数据库巡检 sql。
nl2sql技术实现自动sql生成之Spring AI Alibaba Nl2sql
疾风sxp的博客
12-15 959
上一节我们在介绍了自然语言转sql的基本思路，今天我们通过实操来解决这一技术实现，通过Spring AI Alibaba开源的nl2sql框架进行这块技术的讲解。
MyBatis基础入门《十六》企业级插件实战：基于 MyBatis Interceptor 实现 SQL 审计、慢查询监控与数据脱敏
小二丶的博客
12-15 1133
本文介绍了如何利用MyBatis插件机制构建企业级安全架构，实现SQL审计、慢查询监控、数据脱敏和SQL注入防护四大核心功能。通过Interceptor拦截器，可以在不修改业务代码的前提下，统一增强MyBatis的安全性与可观测性。文章详细讲解了四种插件的实现方案：审计日志插件记录关键操作、慢查询监控捕获性能问题、数据脱敏保护敏感信息、SQL注入防护拦截高危语句。同时强调了性能优化策略，如异步处理、缓存优化等。
智能体+MCP+NL2SQL构建一个智能数据分析应用（一）
12-14 968
本文介绍了如何利用Dify智能体和MCP工具构建基于数据库的查询问答系统。首先以PostgreSQL数据库为例，展示了从GitHub获取音乐商店示例数据并创建数据库的过程。然后详细说明了两种数据库结构描述方式：SQL格式和M-Schema规范。接着演示了如何使用fastmcp库构建安全的MCP查询服务，该服务通过安全校验后执行SQL查询。最后展示了在Dify中配置MCP服务并创建智能体工作流的完整流程，通过实际问答测试验证了系统能够准确地将自然语言问题转换为SQL查询并返回结果。该系统支持从简单到复杂的各类
python datasets包 没有 LargeList该如何解决呢？
09-29
<think>我们正在解决的是Python中datasets包没有LargeList类型的问题。根据引用[3]，字典（或类似数据结构）在有序性和内存占用方面可能存在局限，但我们的问题集中在特定数据类型LargeList上。 由于引用[1]中展示的数据集结构使用了标准的列表类型（'features'是字符串列表），而引用[2]提到下载数据集时可能只下载了元数据，这可能是导致我们无法获取完整数据（包括LargeList）的原因。 解决方案思路： 1. 检查是否确实需要LargeList类型，或者是否可以使用其他方式处理大型列表（例如分块处理）。 2. 确保正确下载了数据集，因为引用[2]提到只下载元数据文件可能导致数据不完整。 3. 如果datasets包本身不支持LargeList，考虑转换数据类型或使用其他支持大型列表的数据结构。 具体步骤： 步骤1：确认数据集下载完整 使用datasets.load_dataset函数时，确保下载的是完整数据集，而不仅仅是元数据。可以尝试重新下载数据集。 步骤2：检查特征类型 在引用[1]中，数据集的特征是字符串列表（如['id','summary','article']），并没有出现LargeList类型。因此，问题可能是用户的数据集有特殊需求，而标准datasets包的特征类型不支持LargeList。 步骤3：自定义特征类型（如果必要） 如果数据集中确实包含大型列表（例如，一个特征是非常长的整数列表或浮点数列表），我们可以尝试使用datasets.Features自定义特征类型。但是，目前datasets包可能没有显式的LargeList类型。我们可以尝试使用以下替代方案： - 使用`datasets.Sequence`来定义可变长度的列表。例如，对于整数列表，可以定义为`datasets.Sequence(datasets.int64)`。 - 如果列表非常大，考虑将数据存储为磁盘上的文件，并在数据集中存储文件路径，然后按需加载。 步骤4：使用其他格式存储大型列表 如果单个特征的数据量过大（例如一个特征包含数百万个元素），那么将其作为单个特征存储可能不合适。考虑将大型列表拆分成多个特征，或者使用一种更高效的文件格式（如HDF5）来存储，然后在数据集中存储文件路径。 步骤5：扩展datasets包的功能（高级） 如果上述方法都不行，可以考虑扩展datasets包，添加对LargeList类型的支持。这需要修改datasets包的源码，实现一个新的`FeatureType`。但这需要深入理解datasets包的结构。 根据引用[2]中的信息，确保使用正确的工具（如modelscope cli或git）下载完整的数据集，并检查是否有版本兼容性问题。 示例代码：如何定义Sequence特征 ```python from datasets import Dataset, Features, Sequence, Value # 假设我们需要一个包含大型整数列表的数据集 features = Features({ 'id': Value('string'), 'large_list': Sequence(Value('int64')), }) # 创建数据集 data = { 'id': ['1', '2'], 'large_list': [[1,2,3,4,5], [10,20,30,40,50,60,70]], # 注意：这里每个列表长度可以不同 } dataset = Dataset.from_dict(data, features=features) ``` 如果列表非常大，上述方法可能导致内存问题。因此，在创建数据集时，我们可以考虑使用生成器来逐步提供数据，避免一次性加载所有数据。 另外，考虑使用`datasets.Dataset.from_generator`方法，它允许动态生成每个样本，从而节省内存。 总结：解决datasets包没有LargeList的方法主要是使用`Sequence`特征类型，并确保数据以流式或生成器方式加载以避免内存问题。同时，确保数据集下载完整。 如果问题仍然存在，可能需要联系datasets包的开发者或寻求其他替代方案。 相关问题： 1. 如何检查datasets包中数据集是否完整下载？ 2. 如何使用datasets.Sequence定义可变长度特征？ 3. 如何处理超大型数据集以避免内存溢出？ 4. 如何扩展datasets包以支持新的特征类型？ </think>### 解决 Python datasets 包缺少 LargeList 类型的方法 在 Python 的 `datasets` 包（Hugging Face Datasets 库）中确实没有内置的 `LargeList` 数据类型，但可以通过以下方法解决： #### 1. **使用 Sequence 类型替代（推荐）** `datasets` 包提供 `Sequence` 特性类型，专门用于处理可变长列表数据（包括大型列表）： ```python from datasets import Dataset, Features, Sequence, Value # 定义包含大型列表的数据集结构 features = Features({ 'id': Value('string'), 'large_data': Sequence(Value('int64')) # 替代 LargeList }) # 创建数据集实例 dataset = Dataset.from_dict({ 'id': ['item1', 'item2'], 'large_data': [[1, 2, 3, ...], [4, 5, 6, ...]] # 支持长列表 }, features=features) ``` - **优势**：内存高效，支持惰性加载 - **原理**：`Sequence` 类型在后台使用 Apache Arrow 的 `ListArray`，可处理百万级元素的列表[^1] #### 2. **数据类型转换技巧** 若从其他格式（如 Parquet）导入数据时遇到类型不兼容： ```python from datasets import Dataset import pandas as pd df = pd.read_parquet('data.parquet') df['large_list'] = df['large_list'].apply(lambda x: list(x)) # 强制转为 Python list dataset = Dataset.from_pandas(df) ``` #### 3. **文件存储优化** 当列表极大（GB 级）时，建议拆分存储： ```python features = Features({ 'id': Value('string'), 'data_path': Value('string') # 存储二进制文件路径 }) def process_example(example): # 按需从磁盘加载大型数据 with open(example['data_path'], 'rb') as f: large_data = pickle.load(f) return large_data ``` #### 4. **升级和版本兼容性** 确保环境兼容（引用[2]的兼容性问题）： ```bash pip install datasets>=2.0.0 --upgrade # 新版优化了大型数据支持 ``` #### 5. **替代方案** 若仍遇瓶颈，可考虑： - 使用 `vaex` 或 `dask` 处理超大型数据集 - 采用分块加载： ```python dataset = load_dataset('my_data', streaming=True) # 流式加载 for chunk in dataset.iter(batch_size=1000): process(chunk) ``` ### 关键注意事项 1. 内存管理：`datasets` 默认使用内存映射文件，但建议监控内存使用 2. 类型验证：通过 `dataset.features` 检查数据类型是否正确转换 3. 性能优化：设置 `keep_in_memory=False` 避免OOM错误 > 关于字典类型的内存局限（如引用[3]所述），`Sequence` 通过 Apache Arrow 的列式存储规避了传统 Python 字典的内存开销问题，特别适合大型列表数据[^3]。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值