索引 视图 事务 游标 锁 详细介绍

索引 视图 事务 游标 锁 详细介绍

索引 视图 游标 事务 锁

1、索引

索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

优点：

大大加快数据的检索速度;

创建唯一性索引，保证数据库表中每一行数据的唯一性;

加速表和表之间的连接;

在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

索引需要占物理空间占用物理空间过多会影响整个SQL Server性能。

当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

建立索引的原则：

（1） 主键的数据列一定要建立索引，因为主键可以加速定位到表中的某一行。

（2） 外键的数据列一定要建立索引，外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接速度。

（3） 对于经常查询的数据列最好建立索引。

索引的分类

普通索引：这是最基本的索引，它没有任何限制

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

主键索引（通过主键约束间接创建）：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

组合索引：在表中的多个列上创建的索引。组合索引中列的顺序是任意的，可以是相邻的列，也可以是不相邻的列。

理解索引：

（1）如果经常使用表中的某一列或某几列为条件进行查询，且表中的数据量比较大时，可以创建索引，以提高查询的速度。

（2）索引是与表关联的可选结构。

（3）通过有目的的创建索引，可以加快对表执行SELECT语句的速度。

（4）不管索引是否存在，都无需修改任何SQL语句的书写方式。索引只是一种快速访问数据的途径，它只影响查询执行的效率。

（5）可以使用CREATE INDEX命令在一列或若干列的组合上创建索引。

（6）创建索引时，将获取要创建索引的列，并对其进行排序。然后，将一个指针连同每一行的索引值存储起来，组成键值对（目录名和页码）。使用索引时，系统首先通过已排序的列值执行快速搜索，然后使用相关联的指针值来定位具有所要查找值的行。

（7）一旦创建了索引，SQL会自动维护和使用它们。

１

索引 视图 事务 游标 锁 详细介绍

（8）只要修改了数据，如添加新行、更新现有行或删除行， SQL都会自动更新索引。

（9）但是为表创建过多的索引会降低更新、删除以及插入的性能，因为SQL还必须更新与该表关联的索引。

2、视图 从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。 视图是一个虚拟表，其内容由查询定义。

概述:

视图以经过定制的方式显示来自一个或多个表的数据

视图是一种数据库对象，用户可以象查询普通表一样查询视图。

视图内其实没有存储任何数据，它只是对表的一个查询。

视图的定义保存在数据字典内。创建视图所基于的表为“基表”。

视图一经定义以后，就可以像表一样被查询、修改、删除和更新

优点、作用：

简化数据查询语句，减少带宽流量、优化后还可提高执行效率

通过重命名列，从另一个角度提供数据，使用户能从多角度看到同一数据

提高了数据的安全性，对不同的用户定义不同的视图，使用户只能看到与自己相关的数据。 提供了一定程度的逻辑独立性

提供了另外一种级别的表安全性

隐藏的数据的复杂性

缺点：

某些视图是不能更新数据的，union、group by、avg、sum、max、distinct等及连接表的视图。

视图定义服从下述限制：

SELECT语句不能包含FROM子句中的子查询。

SELECT语句不能引用系统或用户变量。

SELECT语句不能引用预处理语句参数。

在存储子程序内，定义不能引用子程序参数或局部变量。

在定义中引用的表或视图必须存在。但是，创建了视图后，能够舍弃定义引用的表或视图。要想检查视图定义是否存在这类问题，可使用CHECK TABLE语句。

在定义中不能引用TEMPORARY表，不能创建TEMPORARY视图。

在视图定义中命名的表必须已存在。

２

索引 视图 事务 游标 锁 详细介绍

不能将触发程序与视图关联在一起。

概述：

视图的使用与表一样，有增删改查四种操作，且语法也与表相同。

在视图上也可以使用修改数据的DML语句，如INSERT、UPDATE和DELETE可以统称为“通过视图更新数据”。

通过视图更新数据有如下限制：

视图更新操作： 可更新的视图：要通过视图更新基本表数据，必须保证视图是可更新视图，即可以在INSET、

UPDATE或DELETE等语句当中使用它们。对于可更新的视图，在视图中的行和基表中的行之

间必须具有一对一的关系。还有一些特定的其他结构，这类结构会使得视图不可更新。如果视

图包含下述结构中的任何一种，那么它就是不可更新的：

注意：当视图所依赖的基本表有多个时，不能向该视图插入数据，因为这将会影响多个基本表。 对INSERT语句还有一个限制：SELECT语句中必须包含FROM子句中指定表的所有不能为空的列。 聚合函数； DISTINCT关键字； GROUP BY子句； ORDER BY子句； HAVING子句； UNION运算符； 位于选择列表中的子查询； FROM子句中包含多个表； SELECT语句中引用了不可更新视图； 一次只能修改一个底层的基表 如果修改违反了基表的约束条件，则无法更新视图 如果视图中的列不是表中的原始列（如创建视图时使用了连接操作符、聚合函数等），则不能通过视图更新。

3、游标

游标（Cursor）是处理数据的一种方法，为了查看或者处理结果集中的数据，游标提供了在结果集中一次以行或者多行前进或向后浏览数据的能力。我们可以把游标当作一个指针，它可以指定结果中的任何位置，然后允许用户对指定位置的数据进行处理。

游标包含两个部分：游标结果集（定义该游标得SELECT语句返回的行的集合），游标位置（指向这个结果集某一行的当前指针）。

是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果。每个游标区都有一个名字。用户可以用SQL语句逐一从游标中获取记录，并赋给主变量，交由主语言进一步处理。

概述

游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。 ３

索引 视图 事务 游标 锁 详细介绍

如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

游标的特性：

READ ONLY 只读，只能取值而不能赋值；

NOT SCROOLABLE 不可回滚，只能顺序读取；

ASENSITIVE 敏感，不能在已经打开游标的表上执行update事务；

游标的优点：

1.允许程序对由查询语句Select返回的行集合中的每一行执行相同或不同的操作，而不是对整个行集合执行同一操作。

2.提供基于游标位置的表中的行进行删除和更新的能力。

3.游标实际上是作为面向数据库集合的数据库管理系统（DBMS）和面向行的程序设计之间的桥梁。

4、事务

被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就会失败，以后操作就会回滚到操作前状态，或者是上一个节点。为了确保要么执行，要么不执行，就可以使用事务。 是访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。事务通常由高级数据库操纵语言或编程语言（如SQL，C++或Java）书写的用户程序的执行所引起，并用形如begin transaction和end transaction语句（或函数调用）来界定。事务由事务开始(begin transaction)和事务结束(end transaction)之间执行的全体操作组成。一个事务可以是一条SQL语句，一组SQL语句或整个程序。

ACID特性：

原子性（atomicity）：一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，

要么都不做。就是说不允许事务部分的执行。即使因为故障而使事务不能完成，在rollback时也要消除对数据库的影响！

一致性（consistency）：事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致

性与原子性是密切相关的。就拿网上购物来说，你只有既让商品出库又让商品进入顾客的购物车才能构成事务。

隔离性（isolation）：一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的

数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。如果多个事务并发执行，应象各个事务独立执行一样！

持久性（durability）：持续性也称永久性（permanence），指一个事务一旦提交，它对数据库

中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。

４

索引 视图 事务 游标 锁 详细介绍

5、锁

在所有的DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

锁主要用于多用户环境下保证数据库完整性和一致性。

Microsoft SQL Server（以下简称SQL Server）作为一种中小型数据库管理系统，已经得到了广泛的应用，该系统更强调由系统来管理锁。在用户有SQL请求时，系统分析请求，自动在满足锁定条件和系统性能之间为数据库加上适当的锁，同时系统在运行期间常常自动进行优化处理，实行动态加锁。对于一般的用户而言，通过系统的自动锁定管理机制基本可以满足使用要求，但如果对数据安全、数据库完整性和一致性有特殊要求，就必须自己控制数据库的锁定和解锁，这就需要了解SQL Server的锁机制，掌握数据库锁定方法。

一、锁的多粒度性以及锁升级

数据库中的锁是指一种软件机制，用来指示某个用户（也即进程会话，下同）已经占用了某种资源，从而防止其他用户做出影响本用户的数据修改或导致数据库数据的非完整性和非一致性。这儿所谓资源，主要指用户可以操作的数据行、索引以及数据表等。根据资源的不同，锁有多粒度（multigranular）的概念，也就是指可以锁定的资源的层次。SQL Server中能够锁定的资源粒度包括：数据库、表、区域、页面、键值（指带有索引的行数据）、行标识符（RID，即表中的单行数据）。

采用多粒度锁的重要用途是用来支持并发操作和保证数据的完整性。SQL Server根据用户的请求，做出分析后自动给数据库加上合适的锁。假设某用户只操作一个表中的部分行数据，系统可能会只添加几个行锁（RID）或页面锁，这样可以尽可能多地支持多用户的并发操作。但是，如果用户事务中频繁对某个表中的多条记录操作，将导致对该表的许多记录行都加上了行级锁，数据库系统中锁的数目会急剧增加，这样就加重了系统负荷，影响系统性能。因此，在数据库系统中，一般都支持锁升级(lock escalation)。所谓锁升级是指调整锁的粒度，将多个低粒度的锁替换成少数的更高粒度的锁，以此来降低系统负荷。在SQL Server中当一个事务中的锁较多，达到锁升级门限时，系统自动将行级锁和页面锁升级为表级锁。特别值得注意的是，在SQL Server中，锁的升级门限以及锁升级是由系统自动来确定的，不需要用户设置。

二、锁的模式和兼容性

在数据库中加锁时，除了可以对不同的资源加锁，还可以使用不同程度的加锁方式，即锁有多种模式，SQL Server中锁模式包括：

1．共享锁

SQL Server中，共享锁用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。默认情况下，数据被读取后，SQL Server立即释放共享锁。例如，执行查询“SELECT * FROM my_table”时，首先锁定第一页，读取之后，释放对第一页的锁定，然后锁定第二页。这样，就允许在读操作过程中，修改未被锁定的第一页。但是，事务隔离级别连接选项设置和SELECT语句中的锁定设置都可以改变SQL Server的这种默认设置。例如，“ SELECT * FROM my_table HOLDLOCK”就要求在整个查询过程中，保持对表的锁定，直到查询完成才释放锁定。

2．修改锁

修改锁在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。因为使用共享锁时，修改数据的操作分为两步，首先获得一个共享锁，读取数据，然后将共享锁升级为独占锁，然后再执行修改操作。这样如果同时有两个或多个事务同时对一个事务申请了共享锁，在修改数据的时候，这些事务都要将共享锁升级为独占锁。这时，这些事务都不会释放共享锁而是一直等待对方 ５

索引 视图 事务 游标 锁 详细介绍

释放，这样就造成了死锁。如果一个数据在修改前直接申请修改锁，在数据修改的时候再升级为独占锁，就可以避免死锁。修改锁与共享锁是兼容的，也就是说一个资源用共享锁锁定后，允许再用修改锁锁定。

3．独占锁

独占锁是为修改数据而保留的。它所锁定的资源，其他事务不能读取也不能修改。独占锁不能和其他锁兼容。

4．结构锁

结构锁分为结构修改锁（Sch-M）和结构稳定锁（Sch-S）。执行表定义语言操作时，SQL Server采用Sch-M锁，编译查询时，SQL Server采用Sch-S锁。

5．意向锁

意向锁说明SQL Server有在资源的低层获得共享锁或独占锁的意向。例如，表级的共享意向锁说明事务意图将独占锁释放到表中的页或者行。意向锁又可以分为共享意向锁、独占意向锁和共享式独占意向锁。共享意向锁说明事务意图在共享意向锁所锁定的低层资源上放置共享锁来读取数据。独占意向锁说明事务意图在共享意向锁所锁定的低层资源上放置独占锁来修改数据。共享式独占锁说明事务允许其他事务使用共享锁来读取顶层资源，并意图在该资源低层上放置独占锁。

6．批量修改锁

批量复制数据时使用批量修改锁。可以通过表的TabLock提示或者使用系统存储过程sp_tableoption的“table lock on bulk load”选项设定批量修改锁。

另外，SQL Server命令语句操作会影响锁定的方式，语句的组合也同样能产生不同的锁定。

三、锁冲突及其防止办法

在数据库系统中，死锁是指多个用户（进程）分别锁定了一个资源，并又试图请求锁定对方已经锁定的资源，这就产生了一个锁定请求环，导致多个用户（进程）都处于等待对方释放所锁定资源的状态。 在SQL Server中，系统能够自动定期搜索和处理死锁问题。系统在每次搜索中标识所有等待锁定请求的进程会话，如果在下一次搜索中该被标识的进程仍处于等待状态，SQL Server就开始递归死锁搜索。 当搜索检测到锁定请求环时，系统将根据各进程会话的死锁优先级别来结束一个优先级最低的事务，此后，系统回滚该事务，并向该进程发出1205号错误信息。这样，其他事务就有可能继续运行了。死锁优先级的设置语句为：

SET DEADLOCK_PRIORITY { LOW | NORMAL}

其中LOW说明该进程会话的优先级较低，在出现死锁时，可以首先中断该进程的事务。另外，各进程中通过设置LOCK_TIMEOUT选项能够设置进程处于锁定请求状态的最长等待时间。该设置的语句： SET LOCK_TIMEOUT { timeout_period }

其中，timeout_period以毫秒为单位。

四、理解了死锁的概念，在应用程序中就可以采用下面的一些方法来尽量避免死锁了：

（1）合理安排表访问顺序。

（2）在事务中尽量避免用户干预，尽量使一个事务处理的任务少些。

（3）采用脏读技术。脏读由于不对被访问的表加锁，而避免了锁冲突。在客户机/服务器应用环境中，有些事务往往不允许读脏数据，但在特定的条件下，我们可以用脏读。

（4）数据访问时域离散法。数据访问时域离散法是指在客户机/服务器结构中，采取各种控制手段控制对数据库或数据库中的对象访问时间段。主要通过以下方式实现: 合理安排后台事务的执行时间，采用工作流对后台事务进行统一管理。工作流在管理任务时，一方面限制同一类任务的线程数（往往限制为1个）， ６

索引 视图 事务 游标 锁 详细介绍

防止资源过多占用; 另一方面合理安排不同任务执行时序、时间，尽量避免多个后台任务同时执行，另外， 避免在前台交易高峰时间运行后台任务。

（5）数据存储空间离散法。数据存储空间离散法是指采取各种手段，将逻辑上在一个表中的数据分散到若干离散的空间上去，以便改善对表的访问性能。主要通过以下方法实现: 第一，将大表按行或列分解为若干小表; 第二，按不同的用户群分解。

（6）使用尽可能低的隔离性级别。隔离性级别是指为保证数据库数据的完整性和一致性而使多用户事务隔离的程度，SQL92定义了4种隔离性级别：未提交读、提交读、可重复读和可串行。如果选择过高的隔离性级别，如可串行，虽然系统可以因实现更好隔离性而更大程度上保证数据的完整性和一致性，但各事务间冲突而死锁的机会大大增加，大大影响了系统性能。

（7）使用Bound Connections。Bound connections 允许两个或多个事务连接共享事务和锁，而且任何一个事务连接要申请锁如同另外一个事务要申请锁一样，因此可以允许这些事务共享数据而不会有加锁的冲突。

（8）考虑使用乐观锁定或使事务首先获得一个独占锁定。一个最常见的死锁情况发生在系列号生成器中，它们通常是这样编写的：

如果有两个用户在同时运行这一事务，他们都会得到共享锁定并保持它。当两个用户都试图得到keytab表的独占锁定时，就会进入死锁。为了避免这种情况的发生，应将上述事务重写成如下形式：

以这种方式改写后，只有一个事务能得到keytab的独占锁定，其他进程必须等到第一个事务的完成，这样虽增加了执行时间，但避免了死锁。

如果要求在一个事务中具有读取的可重复能力，就要考虑以这种方式来编写事务，以获得资源的独占锁定，然后再去读数据。例如，如果一个事务需要检索出titles表中所有书的平均价格，并保证在update被应用前，结果不会改变，优化器就会分配一个独占的表锁定。考虑如下的SQL代码：

在这个事务中，重要的是没有其他进程修改表中任何行的price，或者说在事务结束时检索的值与事务开始时检索的值不同。这里的where子句看起来很奇怪，但是不管你相信与否，这是迄今为止优化器所遇到的最完美有效的where子句，尽管计算出的结果总是false。当优化器处理此查询时，因为它找不到任何有效的SARG，它的查询规划就会强制使用一个独占锁定来进行表扫描。此事务执行时，where子句立即得到一个false值，于是不会执行实际上的扫描，但此进程仍得到了一个独占的表锁定。

因为此进程现在已有一个独占的表锁，所以可以保证没有其他事务会修改任何数据行，能进行重复读，且避免了由于holdlock所引起的潜在性死锁。但是，要避免死锁，不可能不付出代价。在使用表锁定来尽可能地减少死锁的同时，也增加了对表锁定的争用。因此，在实现这种方法之前，你需要权衡一下：避免 ７

索引 视图 事务 游标 锁 详细介绍

死锁是否比允许并发地对表进行访问更重要。

五、手工加锁

SQL Server系统中建议让系统自动管理锁，该系统会分析用户的SQL语句要求，自动为该请求加上合适的锁，而且在锁的数目太多时，系统会自动进行锁升级。如前所述，升级的门限由系统自动配置，并不需要用户配置。

在实际应用中，有时为了应用程序正确运行和保持数据的一致性，必须人为地给数据库的某个表加锁。比如，在某应用程序的一个事务操作中，需要根据一编号对几个数据表做统计操作，为保证统计数据时间的一致性和正确性，从统计第一个表开始到全部表结束，其他应用程序或事务不能再对这几个表写入数据，这个时候，该应用程序希望在从统计第一个数据表开始或在整个事务开始时能够由程序人为地（显式地）锁定这几个表，这就需要用到手工加锁（也称显式加锁）技术。

六、

在SQL Server 的SQL语句（SELECT、INSERT、DELETE、UPDATE）支持显式加锁。这4个语句在显式加锁的语法上类似，下面仅以SELECT语句为例给出语法：

SELECT FROM [ WITH ]

其中，指需要在该语句执行时添加在该表上的锁类型。所指定的锁类型有如下几种：

1．HOLDLOCK: 在该表上保持共享锁，直到整个事务结束，而不是在语句执行完立即释放所添加的锁。

2．NOLOCK：不添加共享锁和排它锁，当这个选项生效后，可能读到未提交读的数据或“脏数据”，这个选项仅仅应用于SELECT语句。

3． PAGLOCK：指定添加页面锁（否则通常可能添加表锁）。

4．READCOMMITTED：设置事务为读提交隔离性级别。

5．READPAST: 跳过已经加锁的数据行，这个选项将使事务读取数据时跳过那些已经被其他事务锁定的数据行，而不是阻塞直到其他事务释放锁，READPAST仅仅应用于READ COMMITTED隔离性级别下事务操作中的SELECT语句操作。

6．READUNCOMMITTED：等同于NOLOCK。

7．REPEATABLEREAD：设置事务为可重复读隔离性级别。

8．ROWLOCK：指定使用行级锁。

9．SERIALIZABLE：设置事务为可串行的隔离性级别。

10．TABLOCK：指定使用表级锁，而不是使用行级或页面级的锁，SQL Server在该语句执行完后释放这个锁，而如果同时指定了HOLDLOCK，该锁一直保持到这个事务结束。

11．TABLOCKX：指定在表上使用排它锁，这个锁可以阻止其他事务读或更新这个表的数据，直到这个语句或整个事务结束。

12． UPDLOCK ：指定在读表中数据时设置修改锁（update lock）而不是设置共享锁，该锁一直保持到这个语句或整个事务结束，使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。

由上可见，在SQL Server中可以灵活多样地为SQL语句显式加锁，若适当使用，我们完全可以完成一些程序的特殊要求，保证数据的一致性和完整性。对于一般使用者而言，了解锁机制并不意味着必须使用它。事实上，SQL Server建议让系统自动管理数据库中的锁，而且一些关于锁的设置选项也没有提供给用户和数据库管理人员，对于特殊用户，通过给数据库中的资源显式加锁，可以满足很高的数据一致性和可靠性要求，只是需要特别注意避免死锁现象的出现。

８

本文来源：https://www.bwwdw.com/article/fzw1.html