存储方式对空间使用的影响和性能分析
本帖最后由 TravyLee 于 2012-11-12 09:11:11 编辑
--从存储方式上比较聚集索引(clustered)和非聚集索引(nonclustered)
-->>TravyLee生成测试数据:
--以下所有表格中的数据量均为19329条(需要注意这并不是一个大数据量的表)
--那么我们现在在创建一张表ORDERS_C，结构砼前两张表一样，不使用任何索引，使用堆来存储
IF OBJECT_ID('ORDERS_C') IS NOT NULL
DROP TABLE ORDERS_C
GO
CREATE TABLE ORDERS_C(
ID INT IDENTITY(1,1),
UserId VARCHAR(5) NOT NULL,
OrdersId VARCHAR(8) NOT NULL,
CreateDate DATETIME,
ProductsId VARCHAR(5) DEFAULT('A0001'),
ProductName VARCHAR(255) DEFAULT('小F姐姐'),
Amounts INT,Others VARCHAR(255) DEFAULT('无')
) ON [PRIMARY]
GO
INSERT ORDERS_C(UserId,OrdersId,CreateDate,Amounts)
SELECT UserId,OrdersId,CreateDate,Amounts FROM ORDERS
GO
DBCC SHOWCONTIG(ORDERS_C)
/*
DBCC SHOWCONTIG 正在扫描 'ORDERS_C' 表...
表: 'ORDERS_C' (39671189)；索引 ID: 0，数据库 ID: 1
已执行 TABLE 级别的扫描。
- 扫描页数................................: 185
- 扫描区数..............................: 27
- 区切换次数..............................: 26
- 每个区的平均页数........................: 6.9
- 扫描密度 [最佳计数:实际计数].......: 88.89% [24:27]
- 区扫描碎片 ..................: 11.11%
- 每页的平均可用字节数.....................: 62.5
- 平均页密度(满).....................: 99.23%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--从上述结果中可以发现这个表格使用了185个页面 27个区
--创建一个带有聚集索引的ORDERS_A表，数据内容同orders_c一样
IF OBJECT_ID('ORDERS_A') IS NOT NULL
DROP TABLE ORDERS_A
GO
CREATE TABLE ORDERS_A(
ID INT IDENTITY(1,1),
UserId VARCHAR(5) NOT NULL,
OrdersId VARCHAR(8) NOT NULL,
CreateDate DATETIME,
ProductsId VARCHAR(5) DEFAULT('A0001'),
ProductName VARCHAR(255) DEFAULT('小F姐姐'),
Amounts INT,Others VARCHAR(255) DEFAULT('无'),
CONSTRAINT PK_UserId_OrdersId_A PRIMARY KEY CLUSTERED
(
UserId ASC,
OrdersId ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT ORDERS_A(UserId,OrdersId,CreateDate,Amounts)
SELECT UserId,OrdersId,CreateDate,Amounts FROM ORDERS
--使用DBCC SHOWCONTIG命令查看这个表的存储空间情况
DBCC SHOWCONTIG(ORDERS_A)WITH ALL_INDEXES
/*
DBCC SHOWCONTIG 正在扫描 'ORDERS_A' 表...
表: 'ORDERS_A' (103671417)；索引 ID: 1，数据库 ID: 1
已执行 TABLE 级别的扫描。
- 扫描页数................................: 185
- 扫描区数..............................: 26
- 区切换次数..............................: 25
- 每个区的平均页数........................: 7.1
- 扫描密度 [最佳计数:实际计数].......: 92.31% [24:26]
- 逻辑扫描碎片 ..................: 2.70%
- 区扫描碎片 ..................: 7.69%
- 每页的平均可用字节数.....................: 62.5
- 平均页密度(满).....................: 99.23%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--从上述结果中可以发现这个表格使用了185个页面 26个区
--下面我创建同样结构的一个表,但是主键列在非聚集索引上,他的存储结构是一个堆加B树
IF OBJECT_ID('ORDERS_B') IS NOT NULL
DROP TABLE ORDERS_B
GO
CREATE TABLE ORDERS_B(
ID INT IDENTITY(1,1),
UserId VARCHAR(5) NOT NULL,
OrdersId VARCHAR(8) NOT NULL,
CreateDate DATETIME,
ProductsId VARCHAR(5) DEFAULT('A0001'),
ProductName VARCHAR(255) DEFAULT('小F姐姐'),
Amounts INT,Others VARCHAR(255) DEFAULT('无'),
CONSTRAINT PK_UserId_OrdersId_B PRIMARY KEY NONCLUSTERED
(
UserId ASC,
OrdersId ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT ORDERS_B(UserId,OrdersId,CreateDate,Amounts)
SELECT UserId,OrdersId,CreateDate,Amounts FROM ORDERS
--使用DBCC SHOWCONTIG命令查看这个表的存储空间情况
DBCC SHOWCONTIG(ORDERS_B) WITH ALL_INDEXES
/*
DBCC SHOWCONTIG 正在扫描 'ORDERS_B' 表...
表: 'ORDERS_B' (183671702)；索引 ID: 0，数据库 ID: 1
已执行 TABLE 级别的扫描。
- 扫描页数................................: 185
- 扫描区数..............................: 29
- 区切换次数..............................: 28
- 每个区的平均页数........................: 6.4
- 扫描密度 [最佳计数:实际计数].......: 82.76% [24:29]
- 区扫描碎片 ..................: 55.17%
- 每页的平均可用字节数.....................: 62.5
- 平均页密度(满).....................: 99.23%
DBCC SHOWCONTIG 正在扫描 'ORDERS_B' 表...
表: 'ORDERS_B' (183671702)；索引 ID: 2，数据库 ID: 1
已执行 LEAF 级别的扫描。
- 扫描页数................................: 103
- 扫描区数..............................: 19
- 区切换次数..............................: 18
- 每个区的平均页数........................: 5.4
- 扫描密度 [最佳计数:实际计数].......: 68.42% [13:19]
- 逻辑扫描碎片 ..................: 6.80%
- 区扫描碎片 ..................: 78.95%
- 每页的平均可用字节数.....................: 47.4
- 平均页密度(满).....................: 99.41%
DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
--从上面的结果可以看出表格ORDERS_B使用页面数量=185+103=288 使用区数量=29+19=48
--下面我把对这三张表进行DBCC SHOWCONTIG操作后的数据进行以下汇总对比
/*
-------------------------------------
存储方式-使用页面数量-使用区数量
-------------------------------------
没有任何索引-185-27
-------------------------------------
有聚集索引-185-26
-------------------------------------
有非聚集索引-288-48
-------------------------------------
*/
--从对这个表格反映出来的数据对比我们能够更直观的发现
--建立聚集索引并没有增加表格的空间的大小
--而创建非聚集索引则增加了不小的空间
--那么这三种存储方式在执行查询的时候效率究竟如何呢？接下来我们来看一看
--首先提出一种观点:当一个表格经常发生变化时，如果在这张表格上建立聚集索引，
--会容易遇到页拆分。所以建立聚集索引会影响到性能。基于这种考虑，很多数据库
--设计者不愿意在表格上建立聚集索引。但是一张不见索引的表性能又不能接受，所
--以他们又加上一个非聚集索引，以期得到好的性能
--SQL Server这种堆和树的存储方式决定了创建非聚集索引这种设计是一个既浪费空
--间，性能也不一定是最好的。前面的分析已经说明了它的浪费存储空间，接下来以
--一个最直观的查询来分析它的性能
SELECT * FROM ORDERS_C--无任何索引
SELECT * FROM ORDERS_A--有聚集索引
SELECT * FROM ORDERS_B--又费聚集索引
--下面我们筛选出userid=1001 and OrdersId=0112321的订单
--查询一
SELECT
a.ID,
a.UserId,
b.UserName,
a.OrdersId,
a.CreateDate,
a.ProductName,
a.Amounts
FROM
ORDERS_C a
inner join
USERS b
ON
a.UserId=b.UserId
where
a.UserId='10018' and OrdersId='0118198'--无任何索引
/*
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。 4
表 'ORDERS_C'。扫描计数 1，逻辑读取 185 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'USERS'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 15 毫秒，占用时间 = 7 毫秒。
*/
--查询二
SELECT
a.ID,
a.UserId,
b.UserName,
a.OrdersId,
a.CreateDate,
a.ProductName,
a.Amounts
FROM
ORDERS_A a
inner join
USERS b
ON
a.UserId=b.UserId
where
a.UserId='10018' and OrdersId='0118198'--有聚集索引
/*
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
表 'USERS'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ORDERS_A'。扫描计数 0，逻辑读取 2 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/
--查询三
SELECT
a.ID,
a.UserId,
b.UserName,
a.OrdersId,
a.CreateDate,
a.ProductName,
a.Amounts
FROM
ORDERS_B a
inner join
USERS b
ON
a.UserId=b.UserId
where
a.UserId='10018' and OrdersId='0118198'--有非聚集索引
/*
SQL Server 分析和编译时间:
CPU 时间 = 0 毫秒，占用时间 = 3 毫秒。
表 'USERS'。扫描计数 1，逻辑读取 1 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ORDERS_B'。扫描计数 0，逻辑读取 3 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
SQL Server 执行时间:
CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
*/
GO
--结合上诉结果和执行计划图的分析不难看出:
--不加任何索引的查询CPU开销和I/O开销明显多于加有索引的
--而聚集索引和非聚集索引比较起来性能上存在差距
--聚集索引的语句执行的逻辑读取次数少于非聚集索引查询的时候的逻辑读取次数
--再看执行计划上的cpu开销和i/o开销，聚集索引少于非聚集索引的
--以上仅仅是一个查询的验证，还可以用插入,update,delete验证
--索引会影响到insert的性能，那么这两种索引谁对性能的损失更
--大呢？有兴趣可以测试一下。
邪恶的以为。。。
[其他解释]
我建议看看技术内幕的存储引擎一本 ，上面有一张专门说的索引。相信对你会有所帮助
[其他解释]
hen you a fa f a jfaljf
[其他解释]
太深了，看不懂
[其他解释]
感谢分享。果断拿分。
[其他解释]
学习。学习。
[其他解释]
路过............................的
[其他解释]
谢谢楼主了 好东西啊
[其他解释]
matlab真的很强大，谢谢
[其他解释]
路过
[其他解释]
有点看不懂呦！
[其他解释]
太深了
[其他解释]
板凳
[其他解释]
lu guo
[其他解释]
全是版主，管理员，呵呵
[其他解释]
该回复于2012-11-10 15:53:32被管理员删除
[其他解释]
学习了，好多许多学习的东西
[其他解释]
不同版本的sql server会不会在内部处理实现上会有所不同？
[其他解释]
虽然看不懂,担是还是好强大的样子
[其他解释]
该回复于2012-11-11 16:14:48被管理员删除
[其他解释]
该回复于2012-11-11 22:55:07被管理员删除
[其他解释]
谢谢楼主，好东西
[其他解释]
都是水军惹的，要多多查水表啊
我还不懂执行计划的深层意思
整好学习下
[其他解释]
路过
[其他解释]
