37. SQL -- 页分裂如何解决，在查询中强制使用索引
页分裂：
创建聚集索引时，表格内的数据会按照索引的顺序存储在数据库的数据页面中，当新的数据行插入到数据表中，或更新表中的数据时，SQLServer 必须刷新数据在数据库中的存储位置，这样，就导致索引页中的数据存储方式改变，当页中数据已满的情况下，就将会创建一个新页，并将原有页中的一半数据放入新页中，以挪出空间给新的记录行使用。注：当页分裂的次数较多时，会影响效率.
导致页分裂原因：
当一个数据页达到了8K容量，如果此时发生插入或更新数据的操作，将导致页的分裂(又名页拆分)：
有聚集索引的情况下：聚集索引将被插入和更新的行指向特定的页，该页由聚集索引关键字决定；
只有堆的情况下：只要有空间就可以插入新的行，但是如果我们对行数据的更新需要更多的空间，以致大于当前页的可用空间，行就被移到新的页中，并且在原位置留下一个转发指针，指向被移动的新行，如果具有转发指针的行又被移动了，那么原来的指针将重新指向新的位置；
如果堆中有非聚集索引，那么尽管插入和更新操作在堆中不会发生页分裂，但是在非聚集索引上仍然产生页分裂。无论有无索引，大约一半的数据将保留在老页面，而另一半将放入新页面，并且新页面可能被分配到任何可用的页。所以，频繁页分裂，后果很严重，将使物理表产生大量数
据碎片，导致直接造成I/O 效率的急剧下降，最后，不得不停止SQLS 的运行并重建索引。
解决方法：设置适当的填充因子
填充因子(fillfactor)：
然而在―混沌之初‖，就可以在一定程度上避免不愉快出现，在创建索引时，可以为这个索引指定一个填充因子，以便在索引的每个叶级页面上保留一定百分比的空间，将来数据可以进行扩充和减少页分裂。填充因子是从0 到100 的百分比数值，设为100 时表示将数据页填满，只有当不会对数据进行更改时(例如只读表中)才用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中进行页分裂的需要，但这一操作需要占用更多的硬盘空间。
例如，当填充因子的值为50 时，数据库的读取性能会降低两倍。所以，只有在表中根据现有数据创建新索引，并且可以预见将来会对这些数据进行哪些更改时，设置填充因子才有意义。
填充因子（Fillfactor）:
指定 SQL Server存储索引数据时每个索引页的充满程度。用户指定的 fillfactor 取值范围从 1 到 100。
如果没有指定 fillfactor，则默认为0。
优点：减少了页面分裂的次数
缺点：增大了使用的存储空间
PAD_INDEX：
指定索引中间级中每个页上保持空闲的空间。PAD_INDEX 选项只有在指定了FILLFACTOR时才有用，因为PAD_INDEX 使用由 FILLFACTOR 所指定的百分比
语法：
CREATE NONCLUSTERED INDEX NI_TerminationReason_TerminationReason_DepartmentID
ON HumanResources.TerminationReason
(TerminationReason ASC, DepartmentID ASC)
WITH (PAD_INDEX=ON, FILLFACTOR=50)
语法格式：
Create index 索引名
On 表名（字段名)
With pad_index,fillfactor=n
注：各级索引页的填充程度为n
创建填充因子：
create index id_ha2_test
on tb_ha2(docno)
with pad_index ,fillfactor = 30
在创建填充因子和不创建填充因子占用磁盘空间对比：
A、 未创填充因子前的磁盘空间 ：
sp_spaceused tb_ha2
name rows reserved data index_size unused
tb_ha2 5000 904 KB 848 KB 16 KB 40 KB
B、 创建普通索引磁盘空间：
create index id_ha2_test
on tb_ha2 (part)
sp_spaceused tb_ha2
name rows reserved dataindex_size unused
tb_ha2 5000 1552 KB 848 KB608 KB 96 KB
C、 创建填充因子后的磁盘空间：
create index id_ha2_test
on tb_ha2(docno)
with pad_index ,fillfactor = 30
sp_spaceused tb_ha2
name rows reserved data index_size unused
tb_ha2 5000 1808 KB 848 KB 832 KB 128 KB
DBCC showcontig (tb_ha2):查询索引页分页情况
重建索引：
数据库 → 表 → 索引 → 右键 → 全部重新生成
命令：
USE [demo_db]
GO
ALTER INDEX [PK__tb_ha2__0638335133D4B598]
ON [dbo].[tb_ha2]
REBUILD PARTITION = ALL
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ONLINE = OFF,
SORT_IN_TEMPDB = OFF,
DATA_COMPRESSION = NONE
)
GO
USE [demo_db]
GO
ALTER INDEX [id_ha2_test]
ON [dbo].[tb_ha2]
REBUILD PARTITION = ALL
WITH
(
PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
ONLINE = OFF,
SORT_IN_TEMPDB = OFF,
DATA_COMPRESSION = NONE
)
GO
重建索引：可减少请求数据所需的面数来提高性能.
n 在执行INSERT、UPDATE、DELETE 语句之后，会使得索引数据散乱在数据库存储空间内。
n 重建索引以优化索引结构
n DBCCDBREINDEX (表名，索引名，填充因子）
n 注：可减少请求数据所需的页数来提高性能
dbcc dbreindex（表名、索引名、真充因子）
dbcc dbreindex (tb_ha2)
DBCC showcontig (tb_ha2)：查看索引分页情况：
当再次查入1000 条数据到tb_ha2 中：
insert into tb_ha2
values
(
LEFT (CONVERT(nvarchar(128),newid()),20),
left (convert(nvarchar(128),newid()),20)
)
go 1000
dbcc showcontig (tb_ha2)：查看索引分页情况
在查询中强制使用索引
SQL Server 使用查询优化器选择执行查询的最佳方式 ，包括选择要使用的索引，查询优化器以页I/O 为标准，尝试选出成本最低的方法 。SQL Server 中可以强制查询优化器选择某个特定的索引。强制查询优化器选择某个特定的索引。
语法
(INDEX=index_name)
select * from tb_ha2
with (index = id_ha2_test)
where PART = ‘0021E3E7-D397-4FFF-A‘
查看显示预估的执行计划：
筛选索引：
筛选索引是一种经过优化的非聚集索引，尤其适用于涵盖从定义完善的数据子集中选择数据的查询。筛选索引使用筛选谓词对表中的部分行进行索引。与全表索引相比，设计良好的筛选索引可以提高查询性能、减少索引维护开销并可降低索引存储开销。
DEMO:
AdventureWorks 数据库中有一个包含 2679 行的 Production.BillOfMaterials 表。
EndDate 列只有 199 行包含非NULL 值，其余 2480 行均包含 NULL。下面的筛选索引将涵盖这样的查询：返回在此索引中定义的列的查询，以及只选择EndDate 值不为NULL 的行的查询。
USE AdventureWorks;
GO
IF EXISTS --在创建索引前，先查询此索引名称是否存在.若存在，则删除此索引
(
SELECT name
FROM sys.indexes
WHERE name = N‘FIBillOfMaterialsWithEndDate‘
AND object_id = OBJECT_ID (N‘Production.BillOfMaterials‘))
DROP INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials
GO
CREATE NONCLUSTERED INDEX FIBillOfMaterialsWithEndDate
ON Production.BillOfMaterials (ComponentID, StartDate)
WHERE EndDate IS NOT NULL;
GO
本文出自 “Ricky's Blog” 博客，请务必保留此出处http://57388.blog.51cto.com/47388/1628996