`
changyangzhw053
  • 浏览: 11397 次
最近访客 更多访客>>
文章分类
社区版块
存档分类
最新评论

从性能的角度谈SQL Server聚集索引键的选择

 
阅读更多

具体:http://www.verydemo.com/demo_c155_i1328.html

简介

....................................................................

 

聚集索引所在的列或列的组合最好是唯一的

    这个原因需要从数据的存放原理来谈。在SQL Server中,数据的存放方式并不是以行(Row)为单位,而是以页为单位。因此,在查找数据时,SQL Server查找的最小单位实际上是页。也就是说即使你只查找一行很小的数据,SQL Server也会将整个页查找出来,放到缓冲池中。

    每一个页的大小是8K。每个页都会有一个对于SQL Server来说的物理地址。这个地址的写法是 文件号:页号(理解文件号需要你对文件和文件组有所了解).比如第一个文件的第50页。则页号为1:50。当表没有聚集索引时,表中的数据页是以堆(Heap)进行存放的,在页的基础上,SQL Server通过一个额外的行号来唯一确定每一行,这也就是传说中的RID。RID是文件号:页号:行号来进行表示的,假设这一行在前面所说的页中的第5行,则RID表示为1:50:5,如图1所示。

   

    图1.RID的示例

  

    从RID的概念来看,RID不仅仅是SQL Server唯一确定每一行的依据,也是存放行的存放位置。当页通过堆(Heap)进行组织时,页很少进行移动。

    而当表上建立聚集索引时,表中的页按照B树进行组织。此时,SQL Server寻找行不再是按RID进行查找,转而使用了关键字,也就是聚集索引的列作为关键字进行查找。假设图1的表中,我们设置DepartmentID列作为聚集索引列。则B树的非叶子节点的行中只包含了DepartmentID和指向下一层节点的书签(BookMark)。

    而当我们创建的聚集索引的值不唯一时,SQL Server则无法仅仅通过聚集索引列(也就是关键字)唯一确定一行。此时,为了实现对每一行的唯一区分,则需要SQL Server为相同值的聚集索引列生成一个额外的标识信息进行区分,这也就是所谓的uniquifiers。而使用了uniquifier后,对性能产生的影响分为如下两部分:

  •     SQL Server必须在插入或者更新时对现在数据进行判断是否和现有的键重复,如果重复,则需要生成uniquifier,这个是一笔额外开销。
  •     因为需要对相同值的键添加额外的uniquifier来区分,因此键的大小被额外的增加了。因此无论是叶子节点和非叶子节点,都需要更多的页进行存储。从而还影响到了非聚集索引,使得非聚集索引的书签列变大,从而使得非聚集索引也需要更多的页进行存储。

    下面我们进行测试,创建一个测试表,创建聚集索引。插入10万条测试数据,其中每2条一重复,如图2所示。

   

    图2.插入数据的测试代码

    

   此时,我们来查看这个表所占的页数,如图3所示。

   

    图3.插入重复键后10万数据占了359页

 

    我们再次插入10万不重复的数据,如图4所示。

   

    图4.插入10万不重复的建的代码

 

    此时,所占页数缩减为335页,如图5所示。

   

    图5.插入不重复键后缩减为335页

 

     因此,推荐聚集索引所在列使用唯一键。

 

最好使用窄列或窄列组合作为聚集索引列

    这个道理和上面减少页的原理一样,窄列使得键的大小变小。使得聚集索引的非叶子节点减少,而非聚集索引的书签变小,从而叶子节点页变得更少。最终提高了性能。

 

使用值很少变动的列或列的组合作为聚集索引列

    在前面我们知道。当为表创建聚集索引后。SQL Server按照键查找行。因为在B数中,数据是有序的,所以当聚集索引键发生改变时,不仅仅需要改变值本身,还需要改变这个键所在行的位置(RID),因此有可能使得行从一页移动到另一页。从而达到有序。因此会带来如下问题:

  •     行从一页移动到另一页,这个操作是需要开销的,不仅如此,这个操作还可能影响到其他行,使得其他行也需要移动位置,有可能产生分页
  •     行在页之间的移动会产生索引碎片
  •     键的改变会影响到非聚集索引,使得非聚集索引的书签也需要改变,这又是一笔额外的开销

     这也就是为什么很多表创建一列与数据本身无关的列作为主键比如AdventureWorks数据库中的Person.Address表,使用AddressID这个和数据本身无关的列作为聚集索引列,如图6所示。而使用AddressLine1作为主键的话,员工地址的变动则可能造成上面列表的问题。

   

    图6.创建和数据本身无关的一列作为聚集索引列

 

最好使用自增列作为聚集索引列

    这个建议也同样推荐创建一个和数据本身无关的自增列作为聚集索引列。我们知道,如果新添加进来的数据如果聚集索引列需要插入当前有序的B树中,则需要移动其它的行来给新插入的行腾出位置。因此可能会造成分页和索引碎片。同样的,还会造成修改非聚集索引的额外负担。而使用自增列,新行的插入则会大大的减少分页和碎片。

   最近我碰到过一个情况。一个表每隔几个月性能就奇慢无比,初步查看是由于有大量的索引碎片。可是每隔几个月重建一次索引让我无比厌烦。最终我发现,问题是由于当时设计数据库的人员将聚集索引建在了GUID上,而GUID是随机生成的,则可能插入到表的任何位置,从而大大增加了碎片的数量。因此造成上面这种情况。

 

总结

    本文简单介绍了SQL Server存储的原理和应该规避的几种聚集索引建立情况,但这仅仅是从性能的角度来谈聚集索引的选择。对于聚集索引的选择,还是需要全面的考虑进行决定。

分享到:
评论

相关推荐

    asp代码ASP家教信息管理系统(源代码+论文)

    asp代码ASP家教信息管理系统(源代码+论文)本资源系百度网盘分享地址

    基于ssm高校毕业选题管理系统.zip

    基于ssm高校毕业选题管理系统.zip

    基于旷视研究院领先的深度学习算法,提供满足多业务场景的预训练模型.zip

    人工智能毕业设计&课程设计

    tensorflow_model_optimization-0.1.3.dev0-py2.py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    tensorflow_model_analysis-0.15.0-py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    粒子群算法.docx 粒子群算法(Particle Swarm Optimization,PSO)是一种优化算法,受到鸟群或鱼

    粒子群算法 粒子群算法(Particle Swarm Optimization,PSO)是一种优化算法,受到鸟群或鱼群等群体行为的启发。该算法通过模拟群体中个体之间的合作和竞争来搜索最优解。粒子群算法通常用于解决连续优化问题。 ### 工作原理: 1. **初始化**:随机生成一群粒子(也称为个体),每个粒子代表搜索空间中的一个解,并随机初始化其位置和速度。 2. **评估**:根据每个粒子的位置,计算其对应的适应度值(目标函数值)。 3. **更新**:根据个体最优和全局最优的情况,更新每个粒子的速度和位置。粒子会根据自己历史最好的位置以及整个群体历史最好的位置进行调整,以期望更好的搜索方向。 4. **迭代**:重复评估和更新步骤,直到满足停止条件(如达到最大迭代次数、目标函数值足够接近最优解等)。 ### 主要参数: - 粒子数量(Population Size):群体中粒子的数量,通常越大越容易找到全局最优解,但计算成本也会增加。 - 惯性权重(Inertia Weight):控制粒子运动的惯性,平衡局部搜索和全局搜索能力。通常随着迭代次数增加而逐渐减小。

    20210327 AI-for-Drug-Discovery-2020.pdf

    20210327 AI-for-Drug-Discovery-2020

    tensorflow_model_optimization-0.1.2-py2.py3-none-any.whl

    Python库是一组预先编写的代码模块,旨在帮助开发者实现特定的编程任务,无需从零开始编写代码。这些库可以包括各种功能,如数学运算、文件操作、数据分析和网络编程等。Python社区提供了大量的第三方库,如NumPy、Pandas和Requests,极大地丰富了Python的应用领域,从数据科学到Web开发。Python库的丰富性是Python成为最受欢迎的编程语言之一的关键原因之一。这些库不仅为初学者提供了快速入门的途径,而且为经验丰富的开发者提供了强大的工具,以高效率、高质量地完成复杂任务。例如,Matplotlib和Seaborn库在数据可视化领域内非常受欢迎,它们提供了广泛的工具和技术,可以创建高度定制化的图表和图形,帮助数据科学家和分析师在数据探索和结果展示中更有效地传达信息。

    Linux创建虚拟机的步骤

    Linux创建虚拟机的步骤

    基于SpringBoot的校园二手书交易管理系统设计源码

    这是一个基于SpringBoot开发的校园二手书交易管理系统,使用Java语言,包含102个文件。主要文件类型包括39个Java源文件、23个HTML文件、10个PNG图片文件、9个XML文件、9个JavaScript文件、4个CSS文件、2个Markdown文档、2个JPG图片文件、1个gitignore文件和1个SVG文件。该项目简洁易用,采用的技术经典,非常适合Java项目入门学习和企业级Java开发熟悉,提供了二手书交易管理、用户认证、数据统计等功能,旨在为校园内的二手书交易提供一个便捷、安全的平台。

    基于SSM的旅游管理系统.zip

    基于SSM的旅游管理系统.zip

    基于ssm框架网络财务设计与实现.zip

    基于ssm框架网络财务设计与实现.zip

    三菱PLC例程源码PLC同变频器通讯程序3

    三菱PLC例程源码PLC同变频器通讯程序3本资源系百度网盘分享地址

    基于ssm+jsp网上茶叶销售平台.zip

    基于ssm+jsp网上茶叶销售平台.zip

    通信专业毕业设计(论文)-企业网通信方案设计

    随着网络和科学技术的飞速发展,网络建设作为信息化建设的基础,也越来越受到企业的重视,网络结构和网络信息安全都是企业信息化建设中需要解决的重要问题。 本设计出于对众宇通讯公司长期稳定发展的考虑,针对公司的现状和发展需求,为公司设计了一个稳定的、相对安全的、可扩展并且可以支撑必要的网络应用的网络结构。在此次设计中,主要的运用到的技术与实现功能有:(1)汇聚交换机上使用DHCP技术,使各个接入层设备可自动获取相应的IP地址,也避免了IP地址的冲突;(2)运用VRRP技术,增强网络的连续性和稳定性,实现多链路备份冗余和网关备份冗余;(3)运用MSTP技术,将不同的VLAN与相应实例捆绑,避免了网络环路和广播风暴的产生;(4)通过防火墙技术,实现了企业内部与外部网络之间的信息交互安全。除此之外,还进行了VLAN的划分,端口安全设置,ACL访问限制,NAT地址转换,使用OSPF协议、静态路由等网络配置。 本论文基于华为ENSP仿真模拟软件,充分考虑到了整个公司网络今后的实用性、安全性以及可扩展性。利用所学的相关知识和网络技术,对众宇通讯公司的网络进行模拟设计。此设计根据三层网络结构来搭建网络拓扑,

    Gromacs中文手册5.0.2.pdf

    Gromacs中文手册5.0.2

    三菱PLC例程源码八层以下货梯通用程序(奥菱达)

    三菱PLC例程源码八层以下货梯通用程序(奥菱达)本资源系百度网盘分享地址

    seg.v

    seg.v

    ftqqzx.zip

    ftqqzx.zip

    基于tensorflow深度学习的中文机器阅读理解-完形填空.zip

    人工智能毕业设计&课程设计

Global site tag (gtag.js) - Google Analytics