SQL Server ->> DISABLE索引后插入更新数据再REBUILD索引 和 保留索引直接插入更新数据的性能差异
2024-10-14 19:22:05
之前对于“DISABLE索引后插入更新数据再REBUILD索引 和 保留索引直接插入更新数据的性能差异”这两种方法一直认为其实应该差不多，因为无论如何索引最后都需要被维护，只不过是个时间顺序先后的问题，结果今天做了一个实验之后大吃一惊，原来这种“DISABLE索引后插入更新数据再REBUILD索引”确实可以在性能上取得不错的提升。当然了，前提假设是数据插入量达到一个级别，比如我的例子就是用了100万行的数据插入。感觉对于两者底层下SQL Server数据库引擎到底干了那些活确实还需要找时间找些技术丛书来研究下。
我的实验环境是：Microsoft SQL Server 2012 Enterprise Edition
下面session 1用了41秒，而session 2用了19秒
USE [Test]
GO /****** Object: Table [dbo].[Table_1] Script Date: 9/16/2015 8:39:07 PM ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE TABLE [dbo].[Table_1](
[col1] [int] NOT NULL,
[col2] [datetime] NOT NULL,
[col3] [nvarchar](100) NULL,
[col4] [float] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] GO /****** Object: Index [NonClusteredIndex-20150916-152116] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152116] ON [dbo].[Table_1]
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO /****** Object: Index [NonClusteredIndex-20150916-152132] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152132] ON [dbo].[Table_1]
(
[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO SET ANSI_PADDING ON GO /****** Object: Index [NonClusteredIndex-20150916-152140] Script Date: 9/16/2015 8:39:07 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152140] ON [dbo].[Table_1]
(
[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [DF_Table_1_col2] DEFAULT (getdate()) FOR [col2]
GO /****** Object: Table [dbo].[Table_2] Script Date: 9/16/2015 8:39:23 PM ******/
SET ANSI_NULLS ON
GO SET QUOTED_IDENTIFIER ON
GO CREATE TABLE [dbo].[Table_2](
[col1] [int] NOT NULL,
[col2] [datetime] NOT NULL CONSTRAINT [DF_Table_2_col2] DEFAULT (getdate()),
[col3] [nvarchar](100) NULL,
[col4] [float] NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] GO /****** Object: Index [NonClusteredIndex-20150916-152202] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2]
(
[col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
GO /****** Object: Index [NonClusteredIndex-20150916-152210] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2]
(
[col2] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
GO SET ANSI_PADDING ON GO /****** Object: Index [NonClusteredIndex-20150916-152218] Script Date: 9/16/2015 8:39:23 PM ******/
CREATE NONCLUSTERED INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2]
(
[col3] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE
GO --SESSION 1 insert [dbo].[Table_1](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers] --SESSION 2
ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] DISABLE
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] DISABLE insert [dbo].[Table_2](col1, col3, col4)
select [ID], REPLICATE('A',80), 123456.789
from [dbo].[Numbers] ALTER INDEX [NonClusteredIndex-20150916-152202] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152210] ON [dbo].[Table_2] REBUILD
ALTER INDEX [NonClusteredIndex-20150916-152218] ON [dbo].[Table_2] REBUILD
最新文章
- 【算法杂谈】Miller-Rabin素性测试算法
- docker-6 管理工具
- 删除linux系统服务
- HTML5之拖拽(兼容IE和非IE)
- CF Two Buttons (BFS)
- Redis可视化工具Redis Desktop Manager使用
- [LeetCode] 344 Reverse String && 541 Reverse String II
- 机器学习基石：13 Hazard of Overfitting
- Sky（dart）语言介绍-android学习之旅（十）
- TCP模型及其重点协议总结
- Day5_模块与包（import）（form......import....）
- CSS float的相关图文详解（一）
- centos下安装Loadrunner
- tomcat部署maven web项目
- luogu2296 [NOIp2014]寻找道路 (bfs)
- []如何在Windows 10中更改文件夹背景颜色
- linux信号量(转载)
- 新一代数据库TiDB在美团的实践
- [状态机]嵌入式设计模式：有限状态自动机的C语言实现
- [POJ] Financial Management