查看: 10659|回复: 15

【大话IT】表索引过多时删除非常慢

[复制链接]
求职 : 数据库开发
认证徽章
论坛徽章:
58
妮可·罗宾
日期:2017-04-29 10:55:21弗兰奇
日期:2018-08-31 20:09:41ITPUB18周年纪念章
日期:2019-03-12 14:03:4619周年集字徽章-周
日期:2019-09-29 10:43:34
发表于 2015-1-8 13:59 | 显示全部楼层 |阅读模式
公司的系统里有个表字段大概有40个左右,索引有20多个。。总的数据量大概90万,数据600M左右,索引1G左右
几千条数据的delete操作非常慢
查看执行计划:

tablespool

tablespool

发现很多维护索引的操作,其中有一个表假脱机(Eager Spool)的操作,网上只能找到索引假脱机的相关信息,表假脱机的信息非常少
初步怀疑是索引过多,导致删除数据时对索引维护的成本急剧升高
不知道大侠们有没有遇到过类似的问题

求职 : 数据库开发
认证徽章
论坛徽章:
58
妮可·罗宾
日期:2017-04-29 10:55:21弗兰奇
日期:2018-08-31 20:09:41ITPUB18周年纪念章
日期:2019-03-12 14:03:4619周年集字徽章-周
日期:2019-09-29 10:43:34
发表于 2015-1-8 14:00 | 显示全部楼层
写了个测试数据模拟相应环境:

drop table testtidx
go
drop table #txl
GO
CREATE TABLE testtidx(
        tid [uniqueidentifier] NOT NULL,
        cid [uniqueidentifier] NOT NULL,
        soid [uniqueidentifier] NOT NULL,
        pid1 [uniqueidentifier]  NULL default newid(),
        pid2 [uniqueidentifier]  NULL default newid(),
        pid3 [uniqueidentifier] NULL default newid(),
        pid4 [uniqueidentifier] NULL default newid(),
        pid5 [uniqueidentifier] NULL default newid(),
        pid6 [uniqueidentifier] NULL default newid(),
        pid7 [uniqueidentifier] NULL default newid(),
        pid8 [uniqueidentifier] NULL default newid(),
        price1 [money] default round(RAND()*100,0),
        price2 [money] default round(RAND()*100,0),
        df1 [int] NULL default 0, -- IsDel
        df2 [int] NULL default 10000,
        df3 [int] NULL  default round(RAND()*10000,0),
        df4 [int] NULL  default round(RAND()*10000,0),
        df5 [int] NULL default 10000,
        amt1 [decimal](20, 4) NULL,
        amt2 [decimal](20, 4) NULL,
        amt3 [decimal](20, 4) NULL,
        amt4 [decimal](20, 4) NULL,
        amt5 [decimal](20, 4) NULL,
        amt6 [decimal](20, 4) NULL,
        amt7 [decimal](20, 4) NULL,
        dt1 [datetime]  NULL default getdate() - round(RAND()*1000,0),
        dt2 [datetime]  NULL default GETDATE(),
        dt3 [datetime] NULL default GETDATE(),
        ext1 [varchar](100) NULL default newid(),
        ext2 [varchar](100) NULL default newid(),
        ext3 [varchar](100) NULL default newid(),
        ext4 [varchar](100) NULL default newid(),
        ext5 [varchar](200) NULL default newid(),
        ext6 [varchar](200) NULL default newid(),
        ext7 [varchar](500) NULL default newid(),
        ext8 [varchar](1000) NULL default newid(),
        ext9 [nvarchar](100) NULL default newid(),
        exta [nvarchar](100) NULL default newid(),
        extb [nvarchar](max) NULL default newid(),
        extc [nvarchar](max) NULL default newid(),
        CONSTRAINT [PK_testtidx] PRIMARY KEY CLUSTERED (tid ASC)
)
GO
create table #txl(rn int, nid [uniqueidentifier])
GO
with t as (
select 1 rn union all
select rn + 1 from t where rn < 6000
)
insert into #txl
select rn, NEWID()  from t
OPTION (MAXRECURSION 0)
GO
insert into testtidx(tid,cid,soid, amt1,amt2,amt3,amt4,amt5,amt6,amt7)
select newid(), a.nid,a.nid, rd,rd,rd,rd,rd,rd,rd
from (select rn, nid, RAND()*5000 rd from #txl where rn < 200) a,(select rn from #txl ) b
where b.rn > a.rn
GO
create index idx01_testtidx on testtidx(cid)
create index idx02_testtidx on testtidx(pid1,cid)
create index idx03_testtidx on testtidx(pid1)
create index idx04_testtidx on testtidx(cid,soid,pid1)
create index idx05_testtidx on testtidx(cid,soid,df1)
create index idx06_testtidx on testtidx(cid,soid,df1,df4)
create index idx07_testtidx on testtidx(cid,soid,ext9,df3,df1,pid2,exta)
create index idx08_testtidx on testtidx(cid,soid,ext9,exta,df1)
create index idx09_testtidx on testtidx(exta)
create index idx10_testtidx on testtidx(ext9)
create index idx11_testtidx on testtidx(ext2)
create index idx12_testtidx on testtidx(ext3)
create index idx13_testtidx on testtidx(ext4)
create index idx14_testtidx on testtidx(ext5)
create index idx15_testtidx on testtidx(ext6)
create index idx16_testtidx on testtidx(soid,df1)
create index idx17_testtidx on testtidx(soid,dt1)
create index idx18_testtidx on testtidx(soid,ext1)

-- ---------------------------------

delete from testtidx where cid = (select top 1 cid  from testtidx group by cid order by count(1) desc )

使用道具 举报

回复
求职 : 数据库开发
认证徽章
论坛徽章:
58
妮可·罗宾
日期:2017-04-29 10:55:21弗兰奇
日期:2018-08-31 20:09:41ITPUB18周年纪念章
日期:2019-03-12 14:03:4619周年集字徽章-周
日期:2019-09-29 10:43:34
发表于 2015-1-8 14:03 | 显示全部楼层
测试数据删除时的执行计划:
testtidx.jpg

使用道具 举报

回复
认证徽章
论坛徽章:
54
秀才
日期:2017-02-22 15:18:002015年新春福章
日期:2015-03-06 11:57:31懒羊羊
日期:2015-03-04 14:48:16马上有对象
日期:2014-10-24 17:37:552014年世界杯参赛球队: 比利时
日期:2014-08-05 11:35:382014年世界杯参赛球队: 阿根廷
日期:2014-07-15 10:49:33马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11路虎
日期:2014-01-02 12:55:56ITPUB社区12周年站庆徽章
日期:2013-10-08 15:00:34
发表于 2015-1-8 14:26 | 显示全部楼层
你就当每个索引是个小表喽,去理解删除所消耗的资源

使用道具 举报

回复
论坛徽章:
11
ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:442014年新春福章
日期:2014-02-18 16:41:11灰彻蛋
日期:2013-07-26 09:38:24灰彻蛋
日期:2013-07-01 13:12:272013年新春福章
日期:2013-02-25 14:51:24灰彻蛋
日期:2012-12-04 13:10:242012新春纪念徽章
日期:2012-01-04 11:49:54灰彻蛋
日期:2011-12-06 21:29:03祖国60周年纪念徽章
日期:2009-10-09 08:28:00奥运会纪念徽章:摔跤
日期:2008-08-03 07:23:35
发表于 2015-1-14 09:20 | 显示全部楼层
索引是不是太多了点, 一般的经验, OLTP类型数据库的表上, 不超过5个索引. 600MB数据, 1G的索引, 索引已经是数据一倍多大小了, 通常说有点多.

看看动态管理视图, 这些索引是不是都在用, 看看能否删除一些冗余或者重复的索引, 调整一下聚集索引. 或者是否表的结构设计有问题, 范式化不够.

以下是Eager Spool的说明.

The Eager Spool logical operator will consume the entire input, storing each row in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.
The Eager Spool operator will build its spool file eagerly. When the spool's parent operator asks for the first row, the spool operator will consume all rows from its input operator and store them in the spool.

使用道具 举报

回复
论坛徽章:
0
发表于 2015-1-16 14:57 | 显示全部楼层
看表名是产品信息表
1. 索引个数有点多吧,20个索引个人理解差不多就是20种不同的查询场景,这个有点疯狂
2. 一定要物理删除吗?

使用道具 举报

回复
求职 : 数据库开发
认证徽章
论坛徽章:
58
妮可·罗宾
日期:2017-04-29 10:55:21弗兰奇
日期:2018-08-31 20:09:41ITPUB18周年纪念章
日期:2019-03-12 14:03:4619周年集字徽章-周
日期:2019-09-29 10:43:34
发表于 2015-1-18 20:11 | 显示全部楼层
VJentle 发表于 2015-1-16 14:57
看表名是产品信息表
1. 索引个数有点多吧,20个索引个人理解差不多就是20种不同的查询场景,这个有点疯狂
...

是的,产品信息表
产品上的属性较多,且需求是根据大部分属性都可组合进行查询,
逻辑删除的话,update语句也会产生表假脱机

之前没有考虑重新规划索引
现在删除了几个“宽”索引,更改了聚集索引字段,效率提高一些。

对于之前的场景,不知道有没有方法让执行计划不做eager spool

使用道具 举报

回复
论坛徽章:
2
目光如炬
日期:2016-03-20 22:00:00火眼金睛
日期:2016-04-30 22:00:00
发表于 2015-1-20 10:18 | 显示全部楼层
先把delete 语句中的delete 换成select 看下执行计划,再确定是否因为索引过多问题导致的delete,,,然后就是 你这索引也太多了,,监控下 垃圾索引 能删的就删 能整合的就整合

使用道具 举报

回复
求职 : 数据库开发
认证徽章
论坛徽章:
58
妮可·罗宾
日期:2017-04-29 10:55:21弗兰奇
日期:2018-08-31 20:09:41ITPUB18周年纪念章
日期:2019-03-12 14:03:4619周年集字徽章-周
日期:2019-09-29 10:43:34
发表于 2015-1-20 12:20 | 显示全部楼层
kaodaxia 发表于 2015-1-20 10:18
先把delete 语句中的delete 换成select 看下执行计划,再确定是否因为索引过多问题导致的delete,,,然后就 ...

确定是索引多的问题,
删除若干个索引之后,删除几千条数据很快,delete中的查询效率没有问题

使用道具 举报

回复
认证徽章
论坛徽章:
6
优秀写手
日期:2014-08-06 06:00:132015年新春福章
日期:2015-03-04 14:55:132015年新春福章
日期:2015-03-06 11:59:47暖羊羊
日期:2015-07-01 09:29:48白羊座
日期:2016-02-19 09:32:40处女座
日期:2016-03-15 08:50:57
发表于 2015-1-20 17:20 | 显示全部楼层
请问下 这个是个什么工具 界面这样友好

使用道具 举报

回复

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

本版积分规则 发表回复

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