超级会员免费看
SQL Server 索引维护与抽象层构建全解析
在 SQL Server 的使用中，索引的维护和抽象层的构建是提升数据库性能和可维护性的关键环节。下面将详细介绍索引维护的相关要点以及如何构建抽象层。
索引维护
在 SQL Server 里，索引的重组和重建是重要的维护操作。重组默认是在线操作，无论版本如何，执行时能更有信心，因为过程中性能和可用性不会受显著影响。
那么何时该重建索引，何时该重组索引呢？一般来说，如果索引的碎片率低于 30%，重组可能就足够了；如果碎片率高于 30%，则需要重建索引以有效提升性能。不过，在实际操作中，无论碎片情况如何，定期重建索引有助于防止索引在不方便的时候出现碎片问题。
这些维护过程应该经常进行，以确保碎片不会成为主要问题。对于非常大的表，索引可能会严重碎片化，重建可能需要 24 小时或更长时间。这种情况下，可能需要在不同的日子对同一表的不同索引进行维护。同时，这也可能意味着需要从表中删除或存档数据，具体情况需根据实际判断。
从开发的角度来看，考虑索引维护有助于决定要实现多少索引，并平衡查询性能提升的好处与维护要求的弊端。
抽象层构建
当拥有一个完整的物理数据库且包含所有适当的索引后，构建抽象层是进一步完善数据库的重要步骤。
什么是抽象层
抽象层是一种隐藏过程功能复杂细节的方式。可以将其类比为汽车的操作界面，我们只需通过换挡和踩油门就能让汽车行驶，而无需了解发动机的复杂工作原理。
在计算机领域，抽象层通常以软件层的形式实现，用户或其他应用程序可以通过它访问底层对象的功能，而无需直接访问对象本身。常见的抽象层包括 Windows 硬件抽象层（HAL）、开放系统互连（OSI）模型和开放图形库（OpenGL）。
在 SQL Server 数据库中，抽象层旨在隐藏数据库架构的复杂性，包括表、关系、索引、列、数据类型等。它由视图、存储过程、用户定义函数和其他 SQL Server 对象组成。在理想情况下，代码和用户都不应直接访问物理表，所有操作都应通过抽象层进行。
为什么使用抽象层
使用抽象层有几个重要原因：
-
安全性
：正确设计的抽象层为数据库安全提供了更多选择。通过不允许直接访问表，可以避免数据更改或过度权限带来的问题。例如，对于包含敏感数据的
tbl_customer
表，如果直接授予客户服务员工读写权限，他们可能会看到和修改密码等敏感信息。而通过创建视图，可以限制他们只能看到所需的数据。
CREATE VIEW vw_customer_detail
AS
SELECT email
,customer_id
,firstname
,lastname
,homephone
,workphone
,mobilephone
FROM tbl_customer
此外，如果需要向表中添加敏感信息，使用抽象层可以避免用户自动获得对新数据的访问权限。只有在修改或创建视图时，新数据才会被暴露。
- 可扩展性和灵活性 ：可扩展性指的是根据未来需求轻松修改数据模型的能力，包括添加列、修改现有列或添加/删除整个表。灵活性则是指在不造成重大影响的情况下更改模型的能力。
抽象层允许对物理表进行几乎任何更改，而不会影响应用程序代码。如果所有应用程序和用户都通过视图读取数据并使用存储过程操作数据，只需适当更新这些对象即可。例如，将一个表拆分为两个表时，如果没有抽象层，所有涉及该表的代码都需要修改；而有了抽象层，只需修改几个存储过程和视图。
与修改应用程序代码相比，修改存储过程代码具有明显优势。存储过程代码不需要编译，只要输入和输出保持不变，即使在用户在线时进行更改，也不会被察觉。而且，修改应用程序代码通常需要重新编译并让所有用户更新或重新安装客户端应用程序，而数据库只有一个。
抽象层还提供了根据性能、规范化和可扩展性等原因进行更改的灵活性，未来的增长和变化在有抽象层的情况下通常会更加顺利。
抽象层与逻辑模型的关系
构建抽象层时，应使其更紧密地与逻辑模型相关联，而不是物理模型。这样可以让应用程序和用户以更有意义的方式与数据库交互。逻辑模型更用户友好，而物理模型是为了在 SQL Server 中存储数据而设置的。因此，应避免为每个表创建四个存储过程（插入、更新、选择和删除）的传统做法，而是创建与实体相关的存储过程，例如保存客户实体的过程，这些过程应考虑到客户实体可能存储在多个表中。
抽象层与面向对象编程的关系
面向对象编程使用对象来表示现实世界的事物，与实体的概念相似。对象具有属性和方法，方法可以与数据库操作相关联。例如，订单对象的
Save
方法可能需要将订单及其相关数据插入到 SQL Server 数据库中，这通常会调用存储过程。
在构建数据库时，应考虑对象模型与数据库的紧密集成。如果存在对象模型，可以将其作为构建抽象层的指南，因为应用程序最终会直接使用数据库，两者应紧密耦合。
实现抽象层
实现抽象层包括在数据库中创建对象，作为应用程序代码和底层核心数据库对象之间的中介。可以使用视图、存储过程和函数来创建易于访问的入口点。
-
视图
创建视图时，目标是根据用户需求以有意义的方式向最终用户显示实体。这可能涉及连接多个表或查询单个表但不显示所有行。例如，一个返回客户所有信息的视图是很好的，但如果还连接到地址表，每个客户可能会有多行地址信息，视图的使用者需要理解这一点。
创建视图时，还应考虑简化应用程序逻辑。如果应用程序经常需要获取客户的所有过去订单以输出账户摘要页面，那么一个连接客户表和订单表的视图可以节省时间并避免应用程序多次访问数据库。
同时，应避免使用
SELECT *
语法或没有值列表的
INSERT
语法，因为这些语法依赖于数据的特定顺序，可能会导致意外结果。
以下是查看客户数据的视图示例：
-- 客户信息视图
CREATE VIEW vw_customers
AS
SELECT
objid
,email
,customer_id
,firstname
,lastname
,homephone
,workphone
,mobilephone
FROM tbl_customer
-- 客户地址信息视图
CREATE VIEW vw_customer_addresses
AS
SELECT
address_objid = objid
,address_label
,addressline1
,addressline2
,city
,region
,zipcode
,customer_objid
FROM tbl_address
WHERE customer_objid IS NOT NULL
-
存储过程
创建存储过程时，应从它们要影响的实体角度考虑。可以制定标准来规范存储过程的创建方式、影响的实体以及潜在的数据操作。
一个常用的方法是为每个实体创建一个存储过程，该过程将更新或插入所有关联表中的数据。以下是一个保存客户实体的存储过程示例：
ALTER PROCEDURE prc_save_customer
@email varchar(50)
,@customer_id char(10)
,@firstname varchar(50)
,@lastname varchar(50)
,@homephone varchar(15)
,@workphone varchar(15)
,@mobilephone varchar(15)
,@addresses CustomerAddress ReadOnly
,@customer_objid int OUTPUT
AS
MERGE tbl_customer AS pri_customer
USING
(
SELECT customer_id = @customer_id
)
AS source_customer(customer_id)
ON
(
pri_customer.customer_id =
source_customer.customer_id
)
WHEN NOT MATCHED THEN
INSERT (email,
customer_id,
firstname,
lastname,
homephone,
workphone,
mobilephone)
VALUES (@email,
@customer_id,
@firstname,
@lastname,
@homephone,
@workphone,
@mobilephone)
WHEN MATCHED THEN
UPDATE
SET email=@email,
firstname=@firstname,
lastname=@lastname,
homephone=@homephone,
workphone=@workphone,
mobilephone=@mobilephone;
SELECT @customer_objid = objid
FROM tbl_customer
WHERE customer_id = @customer_id;
MERGE tbl_address AS current_addresses
USING
(
SELECT customer_objid = @customer_objid,
address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
is_deleted
FROM @addresses
)
AS source_addresses(customer_objid,
address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
is_deleted)
ON
(
current_addresses.address_label =
source_addresses.address_label
AND
current_addresses.customer_objid =
source_addresses.customer_objid
)
WHEN NOT MATCHED THEN
INSERT (address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
customer_objid)
VALUES (source_addresses.address_label,
source_addresses.addressline1,
source_addresses.addressline2,
source_addresses.city,
source_addresses.region,
source_addresses.country,
source_addresses.zipcode,
@customer_objid)
WHEN MATCHED AND source_addresses.is_deleted = 1
THEN DELETE
WHEN MATCHED THEN
UPDATE
SET address_label=source_addresses.address_label,
addressline1=source_addresses.addressline1,
addressline2=source_addresses.addressline2,
city=source_addresses.city,
region=source_addresses.region,
country=source_addresses.country,
zipcode=source_addresses.zipcode;
这个存储过程使用了 SQL Server 2008 中的
MERGE
语句，可以为每个表编写单个语句来判断记录是新的需要插入还是现有记录需要更新。同时，使用了用户定义的表数据类型作为表值参数，允许通过单个参数传递客户的多个地址。
总结
通过合理维护索引和构建抽象层，可以提升 SQL Server 数据库的性能、安全性、可扩展性和可维护性。在实际操作中，应根据具体情况选择合适的索引维护策略，并精心设计抽象层的视图和存储过程，以满足应用程序和用户的需求。
以下是一个简单的索引维护和抽象层构建的流程图：
graph LR
A[开始] --> B[索引维护]
B --> B1{碎片率 < 30%?}
B1 -- 是 --> B2[重组索引]
B1 -- 否 --> B3[重建索引]
B --> B4[定期重建索引]
B --> B5{大表索引重建时间长?}
B5 -- 是 --> B6[不同日子维护不同索引]
B5 -- 否 --> B7[正常维护]
A --> C[抽象层构建]
C --> C1[理解抽象层概念]
C1 --> C2[考虑安全性]
C2 --> C21[创建视图限制访问]
C1 --> C3[考虑可扩展性和灵活性]
C3 --> C31[通过视图和存储过程实现]
C1 --> C4[关联逻辑模型]
C4 --> C41[创建实体相关存储过程]
C1 --> C5[结合面向对象编程]
C5 --> C51[使用对象模型指导构建]
C --> C6[实现视图和存储过程]
C6 --> C61[创建视图]
C6 --> C62[创建存储过程]
B7 --> D[结束]
C62 --> D
以下是订单对象属性与订单实体属性的对应关系表：
| 属性（对象） | 属性（实体） |
| — | — |
| Order_Number | OrderNumber |
| Date | OrderDate |
| SubTot | SubTotal |
| SH | ShippingHandling |
| Tax | Tax |
| Order Total | Total |
| Address 1 | AddressLine1 |
| Address 2 | AddressLine2 |
| City | City |
| State | Region |
| Country | Country |
| Zip | ZipCode |
| Split | IsSplitShip |
| EmployeeID | EmployeeObjectID |
| CustomerID | CustomerObjectID |
| Shipping_Method | ShippingMethodsObjectID |
通过以上内容，希望能帮助你更好地理解和应用 SQL Server 中的索引维护和抽象层构建知识。在实际应用中，不断实践和优化将有助于提升数据库的整体性能和可维护性。
SQL Server 索引维护与抽象层构建全解析
索引维护与抽象层构建的实践要点
在实际操作中，除了了解索引维护和抽象层构建的基本概念和方法，还需要掌握一些实践要点，以确保数据库的高效运行。
索引维护的实践要点
-
监控索引碎片
：定期监控索引的碎片率是非常重要的。可以使用系统视图
sys.dm_db_index_physical_stats来获取索引的碎片信息。以下是一个简单的查询示例：
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent
FROM
sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips
JOIN
sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE
ips.avg_fragmentation_in_percent > 0;
这个查询将返回所有索引的碎片率信息，你可以根据碎片率来决定是否需要进行重组或重建操作。
-
自动化索引维护
：为了确保索引维护的及时性和一致性，可以使用 SQL Server Agent 来创建作业，定期执行索引重组或重建操作。以下是一个简单的作业步骤示例：
-- 重组索引
ALTER INDEX ALL ON YourTableName REORGANIZE;
-- 重建索引
ALTER INDEX ALL ON YourTableName REBUILD;
将上述代码添加到作业步骤中，并设置合适的执行计划，就可以实现自动化的索引维护。
抽象层构建的实践要点
-
视图的优化
：在创建视图时，除了避免使用
SELECT *语法，还可以考虑使用索引视图来提高查询性能。索引视图是一种预先计算并存储结果的视图，可以显著减少查询的执行时间。以下是一个创建索引视图的示例：
-- 创建视图
CREATE VIEW vw_IndexedView
WITH SCHEMABINDING
AS
SELECT
Column1,
Column2,
SUM(Column3) AS Total
FROM
dbo.YourTableName
GROUP BY
Column1,
Column2;
-- 创建索引
CREATE UNIQUE CLUSTERED INDEX idx_IndexedView ON vw_IndexedView (Column1, Column2);
-
存储过程的优化
：对于存储过程，除了使用
MERGE语句和表值参数，还可以考虑使用事务来确保数据的一致性。以下是一个添加事务的存储过程示例：
ALTER PROCEDURE prc_save_customer
@email varchar(50)
,@customer_id char(10)
,@firstname varchar(50)
,@lastname varchar(50)
,@homephone varchar(15)
,@workphone varchar(15)
,@mobilephone varchar(15)
,@addresses CustomerAddress ReadOnly
,@customer_objid int OUTPUT
AS
BEGIN TRANSACTION;
BEGIN TRY
MERGE tbl_customer AS pri_customer
USING
(
SELECT customer_id = @customer_id
)
AS source_customer(customer_id)
ON
(
pri_customer.customer_id =
source_customer.customer_id
)
WHEN NOT MATCHED THEN
INSERT (email,
customer_id,
firstname,
lastname,
homephone,
workphone,
mobilephone)
VALUES (@email,
@customer_id,
@firstname,
@lastname,
@homephone,
@workphone,
@mobilephone);
SELECT @customer_objid = objid
FROM tbl_customer
WHERE customer_id = @customer_id;
MERGE tbl_address AS current_addresses
USING
(
SELECT customer_objid = @customer_objid,
address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
is_deleted
FROM @addresses
)
AS source_addresses(customer_objid,
address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
is_deleted)
ON
(
current_addresses.address_label =
source_addresses.address_label
AND
current_addresses.customer_objid =
source_addresses.customer_objid
)
WHEN NOT MATCHED THEN
INSERT (address_label,
addressline1,
addressline2,
city,
region,
country,
zipcode,
customer_objid)
VALUES (source_addresses.address_label,
source_addresses.addressline1,
source_addresses.addressline2,
source_addresses.city,
source_addresses.region,
source_addresses.country,
source_addresses.zipcode,
@customer_objid)
WHEN MATCHED AND source_addresses.is_deleted = 1
THEN DELETE
WHEN MATCHED THEN
UPDATE
SET address_label=source_addresses.address_label,
addressline1=source_addresses.addressline1,
addressline2=source_addresses.addressline2,
city=source_addresses.city,
region=source_addresses.region,
country=source_addresses.country,
zipcode=source_addresses.zipcode;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
THROW;
END CATCH;
索引维护与抽象层构建的常见问题及解决方法
在实际应用中，可能会遇到一些常见的问题，以下是一些常见问题及解决方法。
索引维护常见问题
- 索引重建时间过长 ：对于非常大的表，索引重建可能需要很长时间，甚至会影响数据库的正常运行。解决方法是在非高峰时段进行索引重建，或者使用分区索引来减少重建的范围。
- 索引碎片率反复升高 ：如果索引碎片率在重建或重组后很快又升高，可能是因为数据的插入、更新和删除操作过于频繁。可以考虑优化应用程序的操作，或者增加索引的填充因子。
抽象层构建常见问题
- 视图性能问题 ：如果视图的查询性能不佳，可能是因为视图的定义过于复杂，或者没有使用合适的索引。可以通过优化视图的定义，或者创建索引视图来提高性能。
- 存储过程执行错误 ：存储过程执行错误可能是由于参数传递错误、数据类型不匹配等原因引起的。可以通过检查存储过程的代码和参数传递，以及使用错误处理机制来解决问题。
总结
通过合理的索引维护和抽象层构建，可以显著提升 SQL Server 数据库的性能、安全性、可扩展性和可维护性。在实际操作中，需要掌握索引维护和抽象层构建的基本方法和实践要点，及时解决常见问题，以确保数据库的高效运行。
以下是索引维护和抽象层构建的关键步骤总结列表：
1. 定期监控索引碎片率，根据碎片率选择重组或重建索引。
2. 使用 SQL Server Agent 自动化索引维护作业。
3. 理解抽象层的概念，考虑安全性、可扩展性和灵活性。
4. 创建视图时避免使用
SELECT *
语法，可考虑使用索引视图。
5. 创建存储过程时从实体角度考虑，使用
MERGE
语句和表值参数，添加事务确保数据一致性。
6. 及时解决索引维护和抽象层构建过程中遇到的常见问题。
以下是一个简单的问题解决流程图：
graph LR
A[遇到问题] --> B{问题类型?}
B -- 索引维护问题 --> C{具体问题?}
C -- 重建时间过长 --> C1[非高峰时段重建或使用分区索引]
C -- 碎片率反复升高 --> C2[优化应用操作或增加填充因子]
B -- 抽象层构建问题 --> D{具体问题?}
D -- 视图性能问题 --> D1[优化视图定义或创建索引视图]
D -- 存储过程执行错误 --> D2[检查代码和参数传递，使用错误处理机制]
C1 --> E[问题解决]
C2 --> E
D1 --> E
D2 --> E
通过以上内容，希望能帮助你全面掌握 SQL Server 索引维护和抽象层构建的知识和技能，在实际应用中能够灵活运用，提升数据库的整体性能和可维护性。
扫一扫
696
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
