东莞做一个企业网站,一个人做网站建设需掌握,狼雨的网站,腾讯云 门户网站建设译者 汤健 沃趣科技数据库技术专家出品 沃趣科技在本系列的前一期文章中#xff0c;我制作了一些图#xff0c;突出显示了按表扫描执行大量删除操作和按索引范围扫描执行大量删除之间的主要区别。根据所涉及的数据模式#xff0c;选择正确的策略可能对随机I/Os的数量、生… 译者 汤健 · 沃趣科技数据库技术专家出品 沃趣科技在本系列的前一期文章中我制作了一些图突出显示了按表扫描执行大量删除操作和按索引范围扫描执行大量删除之间的主要区别。根据所涉及的数据模式选择正确的策略可能对随机I/Os的数量、生成的undo的数量和排序所需的CPU数量产生显著影响——所有这些都可能影响执行删除所需的时间。然而这个简单的演示跟生产环境当中相比生产环境当中这个情况更为复杂。所以如果你面临着一项艰巨的任务你需要仔细考虑如何对真正代表你要处理的系统的东西进行建模。实际上有两种不同的情况这一点很重要。*当你在处理一个非常大的一次性任务时你需要在第一时间就把它做好一些关键性的特殊情况不要发现的太迟——尤其是如果你不允许把生产系统离线来完成这个任务任务而且你的工作期限很紧的话。*当你有一份常规的、但不经常发生的、非常大的工作时有必要了解一下哪些看起来不相关的小操作可能对运行时产生很大影响;而且了解下一次升级可能会出现什么问题是值得的这样您就可以预先解决任何问题。当然后者的一个简单例子是我对12c的简短评论以及它通过索引快速全扫描来驱动删除的能力-这一功能在早期版本的Oracle中无法运行。在我的小示例中一个测试将其执行计划从11g的索引全扫描更改为12c的索引快速全扫描完成所需的时间是原来的两倍。继续想一想——当你试图通过索引范围扫描来删除Oracle中的表或者索引时您能想到多少事情这可能会产生怎样的影响?对于一个繁忙的系统这个建议听起来不错。有时候你会发现一个长时间运行的DML语句在运行时速度非常慢因为事实上它涉及到数据中最近的部分因此会受到当前变化的影响;从这一点来看Oracle发现它必须读取undo段来获取undo数据这使得创建与读取一致的数据块版本成为可能-它需要这样做以便它可以检查当前和读取一致的版本的块同意哪些行应该删除。我做的一个例子是通过“date_open”索引删除数据-因此如何强制索引进行降序范围扫描以便首先检查最新的数据在它有很多(或任何)时间遭受其他DML的附带损害之前?有一个非常快捷的方法可以检验这个想法的有效性。所以我们要做的就是检查排序的行数和删除的行数我们就能知道优化是否发生了。我的测试数据集有1000000行和4个索引(主键client_ref、date_open和date_closed索引)所以在最好的情况下我应该看到:“sort (rows)” 4 *行被删除。下面是我做的一个测试的总结我想知道会发生什么delete /* index_desc(t1 t1_pk) */ from t1 where id 5e65000000 rows deleted.Name Value ---- -----sorts (rows) 29我们删除了500万行并(有效地)没有排序。当我们按降序遍历索引时优化根本不适用—我确实检查了执行计划是否显示了我所指定的“索引范围递减扫描”。create index t1_dt_open on t1(date_open desc) nologging tablespace test_8k_assm_2;delete /* index(t1 t1_dt_open) */ from t1 where date_open add_months(sysdate, -60);4999999 rows deleted.Name Value---- -----sorts (rows) 20,003,449在副作用很小的范围内“sort (rows)” 4 *已删除的行:所以可以使用降序索引先尝试删除较新的数据——这很好作为一个通用特性来记住可能很有用。让我们想象一下其他可能出错的情况。*我在这个表上定义了一个主键——但是你可以使约束延迟或者您可以简单地创建一个非惟一索引来保护惟一(或主键)约束。如果我们试图通过主键索引删除会产生什么影响?*如果我们要考虑约束我们可能要考虑外键约束的影响——我们有一个client_ref列在生产系统中它可能是对clients表的外键引用。让我们创建这个表并添加外键约束。*当我们使事情变得更困难时——有一个众所周知的特性将数组处理转换为“逐行”处理——触发器。如果我们向表中添加行级触发器会产生什么效果?什么类型的触发器(在之前/之后、插入/更新/删除)有什么区别吗?以下是一些结果-首先主键约束的非唯一索引alter table t1 drop primary key;alter table t1 add constraint t1_pk primary key(id)deferrable initially immediateusing index nologging tablespace test_8k_assm_2;delete /* index(t1 t1_pk) */ from t1 where id 5e6;5000000 rows deleted.Name Value---- -----sorts (rows) 15,000,004在这个例子中Oracle将我的主键索引设置为非惟一作为约束可延迟的副作用但是即使约束不可延迟并且您只是将索引创建为非惟一其效果也是一样的。统计数据告诉我们我们已经将优化应用于四个索引中的三个——快速检查一下v$segment_statistics就会发现它是主键索引没有进行特殊处理它受到了超过500万个“db块更改”的影响。在这一点上有必要快速检查一下看看通过其他索引驱动是否会改变这种情况——但是不会这是惟一约束与非惟一索引结合的副作用。其次当大表是“子表”时引用完整性的影响create table t2 ( client_id, client_name) asselect distinct client_ref, rpad(x,100,x)from t1;alter table t2 add constraint t2_pk primary key(client_id);alter table t1 modify client_ref not null;alter table t1 add constraint t1_fk_t2 foreign key (client_ref) references t2(client_id);5000000 rows deleted.Name Value---- -----sorts (rows) 15,002,849我们已经排序了大约1500万行而通常我们需要排序2000万行.同样我们可以检查v$segment_statistics来找出哪个索引遭受了500万的损失“db block changes”你可能不会对“外键”索引被逐行维护而感到惊讶-我们可能会猜测这是某种先发制人的代码使得Oracle必须处理“外键锁定”威胁。我们通过主键删除这个特定测试的后续操作是考虑如果我们通过外键索引本身删除或者甚至将约束修改为“on delete cascade”并删除一些父行将会发生什么。通过client_ref在t1上驱动delete仍然优化了其他三个索引但是当您试图利用“on delete cascade”机制时这个技巧根本没有机会产生大规模的效果。在幕后你会发现这样的事情delete from TEST_USER.T1where CLIENT_REF :1call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 3 0.00 0.00 0 0 0 0Execute 3000 5.23 15.37 69349 9238 428052 32510Fetch 0 0.00 0.00 0 0 0 0------- ------ -------- ---------- ---------- ---------- ---------- ----------total 3003 5.23 15.37 69349 9238 428052 32510Rows (1st) Rows (avg) Rows (max) Row Source Operation---------- ---------- ---------- --------------------------------------------------- 0 0 0 DELETE T1 (cr3 pr22 pw0 time9672 us) 7 8 11 INDEX RANGE SCAN T1_CLIENT (cr3 pr0 pw0 time125 us cost3 size594 card22)(object id 150589)This output the consequence of a bulk delete of 3,000 rows from t2 – because of the “on delete cascade”, the delete operated row by row on t2 and for each row Oracle executed a delete statement against t1.这个输出是t2批量删除3000行的结果——由于“on delete cascade”delete在t2上逐行操作对于每一行Oracle都对t1执行一条delete语句。从技术上讲基于数组的优化是有效的由于索引范围扫描它给我们带来了一点好处但是数据的分散性是如此之大以至于每次调用几乎没有给我们带来任何好处。在某个阶段我们将不得不进一步探索这种父/子的关系。最后是触发器。众所周知行级触发器可以将数组处理转换为单行处理——Oracle的索引维护优化也会发生同样的事情吗create or replace trigger t1_brdbefore delete on t1for each rowbegin null;end;/delete /* index(t1 t1_pk) */ from t1 where id 5e6;5000000 rows deleted.Name Value---- -----sorts (rows) 2,639优化完全消失了。同样的事情也会发生在“为每一行删除后”触发器上但是如果触发器是insert或update(行级)触发器则不会发生这种情况。值得一提的是索引优化也发生在索引列的值发生变化的更新上(请参阅本文)因此留给感兴趣的读者一个练习看看哪些(如果有的话)触发器类型允许优化在数组更新后继续存在。| 译者简介汤健·沃趣科技数据库技术专家沃趣科技数据库工程师多年Oracle数据库从业经验深入理解Oracle数据库结构体系现主要参与公司一体机产品安装、测试、优化并负责电信行业数据库以及系统运维。相关链接MySQL 一个让你怀疑人生的hang死现象MySQL 执行DDL语句 hang住了怎么办?MySQL行级别并行复制能并行应用多少个binlog groupbinlog server还是不可靠吗MySQL binlog基于时间点恢复数据失败是什么鬼开源监控系统Prometheus的前世今生prometheus监控多个MySQL实例MySQL问题两则Kubernetes scheduler学习笔记大数据量删除的思考(三)大数据量删除的思考(二)大数据量删除的思考(一)统计信息记录表|全方位认识 mysql 系统库数据库对象信息记录表|全方位认识 mysql 系统库访问权限控制系统|全方位认识 mysql 系统库权限系统表 | 全方位认识 mysql 系统库Oracle RAC Cache Fusion 系列十四Oracle RAC CR Server Part OneOracle RAC Cache Fusion 系列十三PCM资源访问Oracle RAC Cache Fusion 系列十二Oracle RAC Enqueues And Lock Part 3Oracle RAC Cache Fusion 系列十一Oracle RAC Enqueues And Lock Part 2Oracle RAC Cache Fusion 系列十Oracle RAC Enqueues And Lock Part 1更多干货欢迎来撩~
