查看: 11753|回复: 21

[性能调整] 【大话IT】求救!delete where 删除数据慢

[复制链接]
论坛徽章:
0
发表于 2015-2-22 11:25 | 显示全部楼层 |阅读模式
大过年的先给各位朋友拜年了!
情况是这样,我本人oracle水平一般,项目原为mysql开发,现转换到oracle,没想到出问题了,

描叙:测试库为oracle 10G默认安装,项目有一个表为新闻信息主表,排序啊这些字段都放在这里,索引比较多,真实生产库数据量应该只是几百W级别的,现加了有100W测试数据,有个功能为delete from mian where xxx=aa and id>12345

没想到在oracle下,这个delete操作执行非常慢!!!!删除1W数据都要很长时间,希望各位oracle高手帮分析下如何才能优化,无论是从来sql语句入手或者oracle调优本身都可以

btw:我之前拜读过论坛上不少帖子,好象有建议创建其他表后转移数据等方法,但我这个表是主表,有业务读写不能随便停,所以这个方法恐怕不好办。
认证徽章
论坛徽章:
8
2009新春纪念徽章
日期:2009-01-04 14:52:28祖国60周年纪念徽章
日期:2009-10-09 08:28:002010新春纪念徽章
日期:2010-03-01 11:07:24ITPUB9周年纪念徽章
日期:2010-10-08 09:32:25ITPUB十周年纪念徽章
日期:2011-11-01 16:23:262013年新春福章
日期:2013-02-25 14:51:24沸羊羊
日期:2015-03-04 14:51:522015年新春福章
日期:2015-03-06 11:57:31
发表于 2015-2-22 20:30 | 显示全部楼层
其实,就删除数据来说,和数据库没多大关系,起码mysql和oracle没那么大区别,具体楼主的问题出在哪里,这不好说,如果只有这个操作慢,还是怀疑sql级别的问题,比如:执行计划,可以贴出来大家一起看看。

使用道具 举报

回复
论坛徽章:
11
喜羊羊
日期:2015-02-02 10:22:26暖羊羊
日期:2015-03-18 09:30:06喜羊羊
日期:2015-03-18 09:30:06美羊羊
日期:2015-03-18 09:30:06懒羊羊
日期:2015-03-18 09:30:06沸羊羊
日期:2015-03-18 09:30:06慢羊羊
日期:2015-03-18 09:30:06喜羊羊
日期:2015-03-10 14:01:432015年新春福章
日期:2015-03-06 11:58:18美羊羊
日期:2015-03-04 14:52:28
发表于 2015-2-23 09:20 | 显示全部楼层
如果你的表使用索引,你可以关闭索引或删除。删除后再重建。

另一个法子就是parallel DML

alter session enable parallel dml;, delete /*+ parallel */ ...;

如果可以根据索引rowid删除,会比你利用ID > xxxxx更快。

如果你有兴趣,也可以研究下FORALL及BULK COLLECT的功能。

http://www.toadworld.com/platfor ... t-with-queries.aspx

使用道具 举报

回复
论坛徽章:
188
红宝石
日期:2014-05-09 08:24:37萤石
日期:2014-01-03 10:25:39奥运会纪念徽章:羽毛球
日期:2008-07-01 10:46:06奥运会纪念徽章:马术
日期:2008-07-07 17:43:24奥运会纪念徽章:射箭
日期:2008-07-25 18:07:39奥运会纪念徽章:皮划艇激流回旋
日期:2008-07-30 10:02:57奥运会纪念徽章:花样游泳
日期:2008-09-26 13:02:43奥运会纪念徽章:排球
日期:2008-12-03 11:23:272010新春纪念徽章
日期:2010-01-04 08:33:082010年世界杯参赛球队:澳大利亚
日期:2010-02-26 11:08:44
发表于 2015-2-23 12:03 | 显示全部楼层
delete from mian where xxx=aa and id>12345
会删除多少,占整个表的百分比是多少。

一般很大的话,业务可以停,可以采用ctas模式。
不行,可能要考虑分区,按照xxx来分区。

使用道具 举报

回复
论坛徽章:
3
懒羊羊
日期:2015-03-04 14:52:112015年新春福章
日期:2015-03-06 11:58:18天蝎座
日期:2016-05-14 12:00:32
发表于 2015-2-23 13:12 | 显示全部楼层
TABLE 設計錯誤, 無關db 是Oracle or ..., 如果不知道要如何區分可以使用, 多翻翻 TABLE PARTITION 文章.
建議你不要使用 parallel DML 方式, 除非你很懂.
建議你不要亂建 index 方式刪除, 保證你可以刪除3小時都還刪除不完.

既使時體資料5億筆資料, 要處理幾萬筆 delete or insert or update  ... 都可以保證幾秒就可以完成.

範例:
CREATE TABLE mian_new1
(
xxx,
id,
...
)
PARTITION BY HASH  (xxx)
(
  PARTITION mian_new1
);
xxx 必須是 group 型態, 資料不可是超過 2 萬筆以上.

不知道要如何分, 可以使用 select xxx , count(*) from main group by xxx;
假設是 select xxx , id, count(*) from main group by xxx,id;  比較合理是1千筆或是1百筆, 這要看你的機器一次可以處理極限.

CREATE TABLE mian_new2
(
xxx,
id,
...
)
PARTITION BY HASH  (xxx,id)
(
  PARTITION mian_new2
);

你可以先簡單實驗就可以知道.
1. 先建立一個 new TABLE PARTITION , 範例 main_new
2. 在空檔時間使用 insert into main_new select * from main; 先了解insert 會花掉多少時間.
3. 在使用 delete from main_new where xxx=aa and id>12345, 會花掉多少時間.
4. 確定以上方法可以, 再找時間停機制換. 如過年期間停機轉換.
5. 轉換方式很多, 看你設計方式, 不能停機我們都是採用假日交易量少的時間.
    都是先將前一天資料轉換好, 切換時間都是晚間22:00pm - 23:00pm 將原本 main table rename  main_old
    再將新的 main_new reanme main
    再將當天資料 insert into main select * from main_old where 今天資料 or 有異動資料, 這樣就可以轉換好.
    再 count main , main_old 是否一致
    再 使用小程式筆對 main , main_old 資料是否一致 (for loop ...)
    確定一週沒問題就會將 main_old  drop 掉.

建議你就先實驗機上玩一玩你就會了解 TABLE PARTITION 好處.
切錯 TABLE PARTITION 也會快很多, 但不能保證是最快.

你可以多建立幾個 TABLE PARTITION 實驗看看就會知道了.

CREATE TABLE mian_new3
(
xxx,
id,
group_id,
...
)
PARTITION BY HASH  (group_id)
(
  PARTITION mian_new3
);

我家裡實驗機有12台, 所以我可以很明確知道在不同等級機器上, 那種方式是最有效率.
如果你是用 VM 方式是驗不出什的效能.

祝你好運

使用道具 举报

回复
认证徽章
论坛徽章:
40
2014年新春福章
日期:2014-02-18 16:43:09喜羊羊
日期:2015-05-18 16:24:25慢羊羊
日期:2015-06-12 13:08:22暖羊羊
日期:2015-07-02 16:06:20暖羊羊
日期:2015-07-06 16:28:55狮子座
日期:2015-07-29 17:14:43摩羯座
日期:2015-09-02 13:58:47白羊座
日期:2015-09-08 10:39:06天枰座
日期:2015-09-17 21:41:53摩羯座
日期:2015-10-29 21:07:02
发表于 2015-2-25 10:31 | 显示全部楼层
select count(id) from mian where xxx=aa and id>12345;
的结果是多少?

如果该数量占总量的比例较大,而且表上还有很多索引的情况下,删除会是比较慢的。个人建议的方法:
方法一、先失效表上的所有索引,然后执行删除,完成后,再重建索引。(会对业务有影响,主要是慢,但可用。)
方法二、把要保留的数据复制到一中间表,截断表,再失效表上的所有索引,然后把中间表上的数据插回,最后重建索引。(业务要停,但几百万的记录,应该不会太长时间。为保险起见,可在测试环境上先行演练。)
方法三:在以上两种方法的基础上,在各操作步骤上启用并行DML。通常情况下,会进一步缩短操作时间。

使用道具 举报

回复
认证徽章
论坛徽章:
67
现任管理团队成员
日期:2012-06-02 02:10:00ITPUB元老
日期:2012-09-12 14:06:14ITPUB社区千里马徽章
日期:2013-06-09 10:15:34季节之章:冬
日期:2012-09-04 11:05:30季节之章:春
日期:2012-09-05 09:20:36优秀写手
日期:2013-12-18 09:29:09马上有房
日期:2014-04-10 13:35:362014年新春福章
日期:2014-04-14 09:54:08马上有车
日期:2014-02-28 16:43:13马上加薪
日期:2014-02-19 11:55:14
发表于 2015-2-25 10:56 | 显示全部楼层
先看执行计划啊,瞎猜没啥意思

使用道具 举报

回复
论坛徽章:
48
NBA季后赛之星
日期:2014-10-19 19:51:33蓝锆石
日期:2014-10-19 19:51:33指数菠菜纪念章
日期:2014-10-19 19:52:33指数菠菜纪念章
日期:2014-10-19 19:52:33指数菠菜纪念章
日期:2014-10-19 19:52:33指数菠菜纪念章
日期:2014-10-19 19:52:33问答徽章
日期:2014-04-15 10:41:44优秀写手
日期:2014-07-24 06:00:11保时捷
日期:2014-10-19 19:51:33三菱
日期:2014-10-19 19:51:33
发表于 2015-2-25 11:44 | 显示全部楼层
taomct 发表于 2015-2-23 13:12
TABLE 設計錯誤, 無關db 是Oracle or ..., 如果不知道要如何區分可以使用, 多翻翻 TABLE PARTITION 文章.
...

我家裡實驗機有12台...

---------------------
牛人啊

使用道具 举报

回复
论坛徽章:
0
 楼主| 发表于 2015-2-25 20:53 | 显示全部楼层
本帖最后由 swuswu 于 2015-2-25 20:55 编辑

感谢各位朋友的回复,正学习各位的意见,有几点反馈下

1。查看了原mysql的删除业务java部分代码,发现不是我写的 >12333 这样的直接删除,而是每次删除500数据,然后立即提交删除操作事务,再如此继续,我现在在oracle版本的java部分也按分批一笔一小笔删除了这样做了,勉强能接受,所以我要把执行计划贴一下,有全表扫描。。。而由于这个表是核心业务表,索引停止来删除,恐怕不太好办

2。执行计划:目前单表测试只加到80万笔数据而已

    delete from CONTENT_MAIN_INFO where classId=34567 and contentId>1173374,删除了有2000行数据,用时10.157s
    xxxsd.png










使用道具 举报

回复
论坛徽章:
11
喜羊羊
日期:2015-02-02 10:22:26暖羊羊
日期:2015-03-18 09:30:06喜羊羊
日期:2015-03-18 09:30:06美羊羊
日期:2015-03-18 09:30:06懒羊羊
日期:2015-03-18 09:30:06沸羊羊
日期:2015-03-18 09:30:06慢羊羊
日期:2015-03-18 09:30:06喜羊羊
日期:2015-03-10 14:01:432015年新春福章
日期:2015-03-06 11:58:18美羊羊
日期:2015-03-04 14:52:28
发表于 2015-2-26 10:03 | 显示全部楼层
swuswu 发表于 2015-2-25 20:53
感谢各位朋友的回复,正学习各位的意见,有几点反馈下

1。查看了原mysql的删除业务java部分代码,发现不 ...

你这么做是对的。每500行commit一次减少对memory的需求也减少row lock等等的问题。如果你经常删除及增加数据,最好是线上重建索引。

DBMS_REDEFINITION.CAN_REDEF_TABLE能够让你知道有没有需要重建。

之前我检查了下分区表的影响。跟我的同事讨论后觉得你的coding会需要变化很多。比如需要PK及需要增加分区的分辨(partion)

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表