存储方式对空间使用的影响和性能分析,该怎么解决

存储方式对空间使用的影响和性能分析
本帖最后由 TravyLee 于 2012-11-12 09:11:11 编辑


--从存储方式上比较聚集索引(clustered)和非聚集索引(nonclustered)
--&gt;&gt;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 毫秒。

*/

查询一执行计划图



[解决办法]

[解决办法]
板凳
[解决办法]
推荐一下 
[解决办法]
树锅趴在论坛里 挺消停的啊。
[解决办法]
lu guo
[解决办法]
刚睡醒
[解决办法]
 
 

[解决办法]

[解决办法]

[解决办法]

[解决办法]
不错啊!长度不够，回不了贴！
[解决办法]

[解决办法]

[解决办法]

[解决办法]
阿花和小爱中间的那个悲惨的#6不见了
[解决办法]
o大师。。膜拜
[解决办法]

[解决办法]
顺便说下 妹纸很漂亮 旁边那男的 猥琐点
[解决办法]
这么高深的都玩出来了?
[解决办法]

[解决办法]

[解决办法]
全是版主，管理员，呵呵
[解决办法]
好东西
[解决办法]
学习了，好多许多学习的东西
[解决办法]
虽然看不懂,担是还是好强大的样子
[解决办法]
不同版本的sql server会不会在内部处理实现上会有所不同？
[解决办法]
谢谢楼主，好东西
[解决办法]
好东西 .0..
[解决办法]
看了才知道，自己要学的东西还很多。。。
[解决办法]
路过
[解决办法]
都是水军惹的，要多多查水表啊 
 
我还不懂执行计划的深层意思
整好学习下
[解决办法]
1、这个文章的标题确切的说是各种索引的使用个，而不是存储结构的使用。存储结构侧重的是表的结构的设计，例如说某个字段的数据类型和长度，他们在数据库中的存储。建议看下存储引擎。
2、在测试的时候，每次应该清掉缓存，然后测试。不知楼主是否清理，文章没有说明。
3、
--SQL Server这种堆和树的存储方式决定了创建非聚集索引这种设计是一个既浪费空
--间，性能也不一定是最好的。前面的分析已经说明了它的浪费存储空间，接下来以
--一个最直观的查询来分析它的性能
说说楼主的观点：不同意 非聚集索引时浪费存储空间的说法。毕竟一个表上只能建立一个聚集索引，但是查询的条件可能很多，这样的话就需要在经常查询的列上来建立索引。如果在同一列上建立聚集索引和非聚集索引，然后在该列上查询，那么效率上 聚集索引有它得天独厚的优势，因为聚集索引的是包含在数据页上的，而非聚集索引时限在索引页上进行查找，然后找到相应数据航所在的位置，然后再到数据页上找数据，所以在mssql的查询有时建立索引，在某些查询时也可能不使用该索引。个人认为楼主的测试没有代表性。

应该在一个没有建立索引的和建立了非聚集索引的表上来测试非聚集索引的作用。


[解决办法]
分析得很透彻
[解决办法]

[解决办法]
SQL Server这种堆和树的存储方式决定了创建非聚集索引这种设计是一个既浪费空
--间，性能也不一定是最好的。

--》》似乎将非聚集索引批的一文不值。聚集索引性能上却是不一定最好，在有时查询的时候也可能不会用到建立的索引，但是建立非聚集索引时用最小代价来换取最大的利益的。
我建议看看技术内幕的存储引擎一本 ，上面有一张专门说的索引。相信对你会有所帮助
[解决办法]
太深了，看不懂

[解决办法]
感谢分享。果断拿分。
[解决办法]
hen you  a fa f a jfaljf 
[解决办法]
很有深度

存储方式对空间使用的影响和性能分析,

热点推荐