CH08_事务、视图和索引

CH08_事务、视图和索引

本章目标

• 使用事务保证操作数据的完整性
• 掌握如何创建并使用视图
• 掌握如何创建并使用索引

数据库事务

什么是数据库事务？

数据库事务（Database Transaction）是数据库操作的基本单位，它是一组一起执行的数据库操作，这些操作要么全部成功，要么全部失败，以确保数据库的完整性和一致性。事务的主要目的是确保数据库从一个一致的状态转变到另一个一致的状态。

事务的特点

事务具有以下四个基本属性，通常被称为ACID属性：

• 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改要么全部执行，要么全部不执行。
• 一致性（Consistency）：事务必须使数据库从一个一致性状态转变到另一个一致性状态。一致性与业务有关，比如转账业务，不管如何转账，两个账户的总金额不变。
• 隔离性（Isolation）：多个事务并发执行时，一个事务的操作不应影响其他事务。隔离性主要是通过锁机制实现的。
• 持久性（Durability）：一旦事务提交，则其结果永久保存在数据库中。即使系统崩溃，重新启动后数据库还能恢复到事务成功结束时的状态。

事务的模式

• 显示事务

  显示事务就是用户使用T-SQL 明确定义事务的开始(begin transaction) 和提交 (commit transaction) 或回滚事务(rollback transaction)。

• 隐式事务

  隐式事务是指当事务提交或回滚后， SQL Server 自动开始事务。因此，隐式事务不需要使用 begin transaction 显示开始，只需直接提交事务或回滚事务的T-SQL 语句即可。

  使用时，需要设置 set implicit_transaction on 语句，将隐式事务模式打开，下一个语句会启动一个新的事物，再下一个语句又将启动一个新事物。

• 自动提交事务

  自动提交事务是一种能够自动执行并能自动回滚事务，这种方式是 T-SQL 的默认事务方式。例如在删除一个表记录的时候，如果这条记录有住外检关系的时候，删除就会受到主外键约束的影响，那么这个删除就会取消。

事务的级别

隔离级别是控制并发事务执行的级别，它指定一个事务对其他事务的影响程度。SQL Server 支持四种隔离级别，从最低级别到最高级别分别是：

1. 读未提交 (Read Uncommitted): 允许事务读取其他事务未提交的数据。这是最低的隔离级别，可能导致脏读、不可重复读和幻读。
2. 读提交 (Read Committed): 允许事务读取其他已经提交的事务的数据。这是许多数据库系统的默认隔离级别，可以避免脏读，但仍可能出现不可重复读和幻读。
3. 可重复读 (Repeatable Read): 保证在同一事务中多次读取相同数据时，其结果保持一致。可以防止脏读和不可重复读，但仍可能出现幻读。
4. 串行化 (Serializable): 提供最高的隔离级别，确保每个事务都完全独立运行，防止脏读、不可重复读和幻读。然而，串行化级别可能导致性能下降，因为它限制了并发性。

设置事务的隔离级别：

set transaction isolation level xxx

事务处理

事务的主要操作包括：

• 开始事务（BEGIN TRANSACTION 或 START TRANSACTION）

  开始事务，@@trancount 全局变量用来记录事务的数目值加1，可以用@@error 全局变量记录执行中的错误信息，如果没有错误可以直接提交事务，有错误可以回滚。

• 提交事务（COMMIT）

  回滚事务，表示一个隐式或显示的事物的结束，对数据库所做的修改正式生效。并将@@trancount 的值减1.

• 回滚事务（ROLLBACK）

  回滚事务，执行rollback tran 语句后，数据会回到begin tran 时候的状态。

事务流程语法

-- 设置会话的事务隔离级别(可选)
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;  
  
-- 开始事务  
BEGIN TRANSACTION;  
  
-- 执行事务中的操作  
-- ...  
  
-- 提交或回滚事务  
COMMIT; -- 或 ROLLBACK;

以下是一个简单的数据库事务案例，以银行转账为例：

1. 场景描述
   假设有两个账户A和B，账户A有2000元，账户B有1000元。现在，用户希望从账户A转账200元到账户B。

2. 原子性
   转账操作应该是一个原子操作，即要么全部完成，要么全部不做。

3. 一致性
   转账操作后，账户A的余额应该是1800元，账户B的余额应该是1200元。

4. 隔离性
   在转账操作执行期间，其他事务或用户不应该看到中间状态，例如账户A减少了200元但账户B还没有增加。

5. 持久性
   一旦转账操作完成，即使系统崩溃或重启，转账的结果也应该被保留。

6. 实现步骤
   开始事务：首先，我们需要开始一个新的事务。
   检查账户A余额：在事务中，首先检查账户A的余额是否足够进行转账。
   减少账户A余额：如果账户A的余额足够，那么减少账户A的余额。
   增加账户B余额：接着，增加账户B的余额。
   提交事务：如果上述所有步骤都成功执行，那么提交事务，使更改生效。
   错误处理：如果在任何步骤中发生错误（例如，账户A的余额不足），则回滚事务，撤销所有更改。

--设置事务级别(可选)
set transaction isolation level repeatable read

--开始事务
begin transaction

--定义变量,记录错误量
declare @cnt int
set @cnt=0

--语句1
update Account set Balance-=200 where AccountNumber='A1002'
set @cnt+=@@ERROR

--语句2
update Account set Balance+=200 where AccountNumber='A1001'
set @cnt+=@@ERROR

if @cnt>0
begin
	--回滚事务
	rollback transaction
	print('转账失败')
end
else
begin
	--提交事务
	commit tran
	print '转账成功'
end

注意事项

• 在执行转账操作前，我们检查了账户A的余额是否足够。
• 如果转账成功，我们提交事务，使更改生效。
• 如果转账失败（例如，账户A余额不足），我们回滚事务，撤销所有更改。
• 使用BEGIN TRY … END TRY和BEGIN CATCH … END CATCH块可以捕获并处理可能出现的错误。
• 在BEGIN CATCH块中，我们检查事务是否仍在活动状态（@@TRANCOUNT > 0），如果是，则回滚事务。

这个案例演示了如何在SQL Server中使用事务来确保数据库操作的完整性和一致性。在实际应用中，转账操作可能涉及更多的复杂性和安全性考虑，例如验证账户信息、防止重复转账等。

数据库视图

什么是数据库视图？

数据库视图（Database View）是数据库管理系统（DBMS）中的一个重要概念，它提供了一种虚拟的表，用于封装复杂的查询逻辑或数据的子集。视图并不实际存储数据，而是基于一个或多个表的数据生成的。你可以像查询普通表一样查询视图，但是视图中的数据是动态生成的，会随着基础表数据的改变而改变。

视图的优势：

• 简化复杂性：视图可以隐藏复杂的数据结构和关系，为用户提供一个简化的数据模型。
• 安全性：通过视图，可以限制用户访问基础表中的数据。例如，可以创建一个只显示特定列的视图，而不显示敏感信息。
• 逻辑独立性：当基础表的结构发生变化时，只要视图的定义不变，应用程序代码就无需修改。
• 数据抽象：视图提供了一种数据抽象机制，允许用户只看到他们感兴趣的数据，而不需要知道数据的实际存储方式。
• 复用性：相同的视图可以被多个用户或应用程序重复使用，减少了重复编写查询的需求。

创建视图

创建视图的基本语法如下：

CREATE VIEW view_name AS  
SELECT column1, column2, ...  
FROM table_name  
WHERE condition;

案例：假设有一个student表，我们想要创建一个只显示学员姓名、家庭住址及出身日期的视图

CREATE VIEW student_names_and_address AS
SELECT StudentName,Address,BornDate   
FROM Student;

查询视图

SELECT * FROM student_names_and_address

更新视图

并非所有的视图都是可更新的。只有满足特定条件的视图才能被更新（即允许插入、更新和删除操作）。这些条件包括：

• 视图必须基于单个表。
• 视图定义中不能包含分组（GROUP BY）、聚合函数（如SUM、AVG等）、DISTINCT关键字或HAVING子句。
• 视图定义中选择的列必须来自于基础表中的一个单一列或列的组合。
• 视图定义中不能包含子查询。

删除视图

如果不再需要视图，可以使用DROP VIEW语句删除它：

DROP VIEW view_name;

视图是数据库设计中非常有用的工具，它们可以帮助简化复杂的查询，提高数据的安全性，并提供了一种逻辑上隔离数据的方式。然而，使用视图时需要注意它们的限制和可更新性，以确保它们能够满足应用程序的需求。

数据库索引

什么是数据库索引？

​ 数据库索引是一种数据结构，用于帮助快速查询数据库中的数据。它是对数据库表中一列或多列的值进行排序的一种结构，相当于图书的目录，能加快数据库的查询速度。索引的实现通常使用二叉树、B树、B+树等数据结构。

​ 索引的主要目的是加快检索表中数据的速度，即能协助信息搜索者尽快找到符合限制条件的记录ID的辅助数据结构。通过在表的特定列上创建索引，数据库系统可以不必扫描整个表来查找相关信息，而是直接定位到索引中的特定值，从而快速找到对应的数据行。

​ 数据库索引分为聚簇索引和非聚簇索引两种。聚簇索引是按照数据存放的物理位置为顺序的，它决定了表中数据的物理存储顺序，一个表只能有一个聚簇索引。非聚簇索引独立于表数据，一个表可以有多个非聚簇索引，它有一个指向表数据的指针，其叶节点包含指向聚集索引或表数据的指针。

​ 请注意，虽然索引可以大大提高查询性能，但它们也会占用额外的磁盘空间，并且在插入、删除和更新数据时可能需要额外的维护成本。因此，在设计数据库和创建索引时，需要权衡查询性能和存储及维护成本之间的关系。

索引的类型

SQL Server 提供了多种索引类型，用于提高查询性能。以下是 SQL Server 中常见的索引类型：

• 聚集索引 (Clustered Index)
  • 聚集索引决定了表中数据的物理存储顺序。
  • 一个表只能有一个聚集索引。
  • 聚集索引的键值决定了数据行的物理存储顺序。
  • 聚集索引的叶节点包含行数据。
• 非聚集索引 (Non-Clustered Index)
  • 非聚集索引独立于表数据，并且有一个指向表数据的指针。
  • 一个表可以有多个非聚集索引。
  • 非聚集索引的叶节点包含指向聚集索引或表数据的指针。
• 唯一索引 (Unique Index)
  • 无论是聚集还是非聚集索引，都可以是唯一的。
  • 唯一索引确保索引的键值中不会有重复的值。
• 过滤索引 (Filtered Index)
  • SQL Server 2012 引入了过滤索引的概念。
  • 过滤索引仅包含满足特定条件的行。
  • 这可以帮助减少索引的大小，并提高某些查询的性能。
• 包含列索引 (Included Columns)
  • 在非聚集索引中，除了键列之外，还可以包含非键列。
  • 这允许查询访问索引中的额外列，而无需访问表数据。
• 列存储索引 (Columnstore Index)
  • SQL Server 2012 引入了列存储索引。
  • 它特别适用于数据仓库和分析工作负载，因为它允许数据以列的形式存储和压缩。
  • 列存储索引通常用于读密集型操作，如聚合查询。
• 空间索引 (Spatial Index)
  • 空间索引用于存储和管理空间数据。
  • 它允许对空间数据进行高效的查询和搜索，如查找某个地理区域内的所有点。
• XML 索引
  • XML 索引允许对存储在 XML 列中的数据执行高效的查询。

创建索引

创建索引通常使用 CREATE INDEX 语句。具体语法因数据库而异，但通常遵循以下模式：

CREATE INDEX index_name  
ON table_name (column1, column2, ...);

案例：

--给姓名列添加索引
create index ix_name
on student(studentname)

删除索引

删除索引通常使用 DROP INDEX 语句。语法如下：

DROP INDEX index_name  
ON table_name;

修改索引

直接修改索引不是所有数据库系统都支持的操作。通常，如果您需要更改索引，您需要先删除旧的索引，然后创建一个新的索引。但是，某些数据库（如 SQL Server）提供了重建索引的功能，可以重新组织索引的物理结构以提高性能。

ALTER INDEX index_name  
ON table_name  
REBUILD;

注意事项

• 性能考虑：创建和删除索引会影响数据库性能，特别是在大型数据库中。因此，最好在数据库维护期间执行这些操作，并通知相关利益相关者。
• 索引选择：不是所有列都需要索引。过多的索引会占用大量磁盘空间，并可能降低写操作的性能。通常，您应该在经常用于查询条件（特别是 WHERE、JOIN 和 ORDER BY 子句）的列上创建索引。
• 索引维护：随着时间的推移，索引可能会变得碎片化，从而影响性能。定期检查和重建索引是一个好习惯。
• 数据库系统差异：虽然大多数数据库系统都支持基本的索引操作，但具体的语法和功能可能会有所不同。因此，建议查阅您所使用的数据库系统的官方文档以获取详细信息。

在设计索引时，重要的是要考虑到查询的模式、数据的更新频率、磁盘空间和其他相关因素。不恰当的索引设计可能会导致性能下降或浪费存储空间。因此，定期分析查询性能并进行索引优化是很重要的。

索引的优点

索引在数据库中具有以下显著的优点：

• 提高查询速度：这是创建索引的最主要原因。索引可以显著加速数据的检索速度，特别是在大型数据集中查询时，能够显著节省时间。
• 减少数据的扫描量：通过索引，数据库可以只扫描必要的行，而不是整个表，这有助于减少I/O操作，从而提高查询效率。
• 保证数据的唯一性：通过创建唯一性索引，可以确保数据库表中每一行数据的唯一性，这对于避免重复数据的插入非常重要。
• 支持数据的参考完整性：索引可以帮助实现数据的参考完整性，特别是在表与表之间的连接操作中，索引可以加速连接过程。
• 提高数据的排序和分组速度：在使用分组和排序子句进行数据检索时，索引可以显著减少查询中分组和排序的时间。
• 优化系统性能：通过使用索引，数据库可以在查询过程中使用优化隐藏器，进一步提高系统的性能。

索引的缺点

索引虽然可以带来很多优点，如提高查询速度、保证数据唯一性等，但它们也存在一些缺点。以下是一些常见的索引的缺点：

• 占用额外的存储空间：索引本身需要占用一定的物理存储空间。对于大型数据库，索引可能会占用相当大的空间，这可能会增加存储成本和管理复杂性。
• 增加数据维护成本：每当表中的数据发生变化（如插入、删除或更新操作），相关的索引也需要进行更新。这会增加数据维护的成本，并可能降低数据修改操作的速度。
• 插入、删除和更新操作的性能开销：由于索引需要随着数据的变更而更新，因此在执行这些操作时，索引可能会成为性能瓶颈。特别是在大量数据变更的情况下，索引的维护成本可能会很高。
• 增加查询优化器的复杂性：虽然索引可以加速查询，但查询优化器需要花费更多的时间和资源来确定何时使用索引以及如何使用它们。在某些情况下，查询优化器可能无法做出最佳决策，导致性能下降。
• 可能导致锁竞争：在高并发环境中，多个事务可能同时尝试修改同一索引。这可能导致锁竞争，从而增加系统的响应时间并降低并发性能。
• 索引的选择和维护需要专业知识：正确地选择和使用索引需要数据库管理员或开发人员具备相关的专业知识和经验。不恰当的索引设计或管理可能导致性能问题或资源浪费。

索引的使用建议

• 选择适当的索引列：应该为经常用于查询条件的列创建索引，特别是那些出现在WHERE子句、JOIN操作和ORDER BY子句中的列。

• 避免过度索引：过多的索引会占用大量存储空间，并增加插入、更新和删除操作的开销。应该根据实际需求和数据量来权衡索引的数量。

• 定期维护索引：数据库系统提供了索引重建和优化的功能，可以定期执行这些操作来保持索引的性能。

• 监控索引性能：应该定期监控索引的使用情况和性能，以便及时发现问题并进行调整。

总之，数据库索引是提高查询性能的重要工具，但也需要谨慎使用和维护。正确的索引设计可以显著提高数据库的性能和响应速度。

课后作业

员或开发人员具备相关的专业知识和经验。不恰当的索引设计或管理可能导致性能问题或资源浪费。

索引的使用建议

• 选择适当的索引列：应该为经常用于查询条件的列创建索引，特别是那些出现在WHERE子句、JOIN操作和ORDER BY子句中的列。

• 避免过度索引：过多的索引会占用大量存储空间，并增加插入、更新和删除操作的开销。应该根据实际需求和数据量来权衡索引的数量。

• 定期维护索引：数据库系统提供了索引重建和优化的功能，可以定期执行这些操作来保持索引的性能。

• 监控索引性能：应该定期监控索引的使用情况和性能，以便及时发现问题并进行调整。

总之，数据库索引是提高查询性能的重要工具，但也需要谨慎使用和维护。正确的索引设计可以显著提高数据库的性能和响应速度。

课后作业