当前位置：首页 > news >正文

Sql Server 索引性能优化分析以及分表

news 来源：原创 2025/8/5 3:22:55

定位需优化语句

根据工具 skywking 或者开启慢查询日志找到慢sql 的语句
根据 ==执行过程 ==来判断慢的原因
row filter 指标看查了多少数据比例多少
type 看下是单表还是 join联表
比如执行步骤多没索引

优化方向

减少执行次数
索引没索引考虑加索引加索引尽量选择 int 字节小或者字符串如果能控制就固定比如 name varchar（20就够的话就不要100）这样索引页存储索引值多因为涉及到页的概念非聚集索引尽量覆盖索引减少RID 回表的情况
加了索引的话考虑是否失效
有索引使用性能还是不行考虑一下是不是IO 频率次数多引起的比如页碎片行溢出
性能还是不行的话就考虑 =分表分库减少这个IO频率

索引设置层面优化点

索引设置所以最好就是int 如果是 varchar 固定大小时间索引也要固定大小
索引失效情况

sql 语句不妥用了 or 另一字段没有索引需要or的字段也要有索引
用了 like %在前面没有索引
可以覆盖索引 select * from table where id like ‘1%’ 改为 select id from table where id like ‘1%’ 用id 覆盖索引
或者 %写后面
用函数
联合索引主次顺序次的不走索引
字段可否为空与 is not null 有关系没设置写的话不走索引

页、区

页（Page）——数据存储的“书页”
定义
页是 SQL Server 中数据存储的最小单位，类似于一本书中的一页纸。每个页固定大小为 8KB（8,192字节），所有数据（包括表数据、索引、系统信息）均以页为单位存储和读取。
页的结构
每个页由三部分组成（见图1）：
页头（96字节）：存储系统信息（如页码、页类型、可用空间等）。
数据区（约8,000字节）：存放实际数据行或索引条目。
行偏移表（行指针）：记录每行数据的起始位置，类似书页的“页码索引”，从页尾向前倒序排列。
类比：想象一页纸，页头是标题和作者信息，数据区是正文内容，行偏移表是每段文字在页中的位置索引。
页的类型
数据页：存储用户表的具体数据行（如 varchar、int 等普通列）。
索引页：存储索引结构（如 B+ 树的中间节点），帮助快速定位数据。
特殊页：如管理空间分配的 GAM/SGAM 页、记录大文本的 Text/Image 页等。
区（Extent）——管理页的“章节”
定义
区是 8个连续页的集合（共64KB），用于高效管理页的分配。相当于将多个书页装订成章，避免零散存储。
区的类型
混合区：区内的8个页可分配给不同对象（如表、索引）。新创建的小表默认使用混合区，节省空间。
统一区：区内的8个页专属于同一个对象。当表或索引增长到8页以上时，自动升级为统一区，提高连续读写效率。
类比：混合区像共享办公室，多个租户共用；统一区像独立办公室，仅供一家公司使用

索引页与数据页

数据页（Data Page）——存放“书的内容” 也就是表中行数据存储
存储实际数据行，例如：

CREATE TABLE Students (ID INT, Name VARCHAR(50), Age INT);| 页头 | ID=1, Name="Alice", Age=20 | ID=2, Name="Bob", Age=22 | ... | 行偏移表 |

行溢出：如果某行数据太大（超过 8060B），部分内容会存到其他页（类似书太厚，分上下册）。

索引页（Index Page）——存放“书的目录” 也就是表设置的聚集索引或者非聚集索引

2.1 聚集索引（主键索引）
叶子节点是数据页：数据按主键顺序物理存放（类似书按编号顺序排列）。
非叶子节点是索引页：存储键值范围和子页指针（类似目录的章节导航）。

也就是说索引页然后找到叶子数据页然后根据行数直接找到数据

 [索引页（根）]/      |      \
[索引页（中间）] ... [索引页（中间）]/     |     \             ...
[数据页] [数据页] [数据页]

2.2 非聚集索引（普通索引）
叶子节点指向数据页：存储索引键值 + 行定位符（RID 或主键值），回表查询数据（类似书末的索引表，标注“主题→页码”）。

也就是说索引页找到叶子页这些都是索引页的内容，然后通过聚集索引建或RID 回表找到数据
也就是说非聚集索引过程多了一步聚集索引建或RID 回表

        [索引页（根）]/     |     \
[索引页（中间）] ... [索引页（中间）]/    |    \           ...
[叶子页（存RID）] ... [叶子页（存RID）]

总结
页是存储的基本单位（8KB），区是管理页的集合（8页=64KB）。
数据页直接存数据，索引页加速查询，两者通过B+树和指针协作

页碎片行溢出区分配策略

在使用的过程中肯定会有数据操作，那么增删改查势必会对页中的数据存储有一定影响。
数据少了空间没用完，数据多了空间不够用就会出现以下这些情况。

碎片（Fragmentation）
碎片分为内部碎片和外部碎片，影响查询性能和存储效率。

内部碎片
定义：页内未充分利用的空闲空间（如删除数据后留下的空白）。
影响：浪费存储空间，增加读取时的 I/O 操作（需扫描更多页）。
示例：若某页实际存储数据仅占 50%，剩余空间无法被其他数据利用。
优化：通过 ALTER INDEX REBUILD 或 REORGANIZE 整理页内空间。
外部碎片
定义：页在磁盘上物理不连续（由分页操作导致）。
触发场景：插入或更新数据时原页空间不足，新页与旧页物理分离。
影响：范围查询需跨区读取，增加磁头移动和 I/O 开销。
优化：使用 ALTER INDEX REBUILD 重建索引，或调整填充因子减少分页频率。

填充因子（Fill Factor）

作用：控制页初始填充比例（默认 100%），预留空间减少分页。
设置建议：
读写比 > 100:1 → 100%填充（减少查询 I/O）。
写频繁 → 50%-70%填充（减少分页）。
读写平衡 → 80%-90%填充。

行溢出（Row Overflow）
触发条件
变长列（如 varchar(max)）数据超过页容量（8KB）。
行总大小（含系统信息）超过 8060 字节。
存储机制

行溢出指针：原页中保留 24 字节指针，指向溢出页（存储实际数据）。

结构示例：
原页中记录包含溢出指针（如 0x70170000 表示溢出数据长度 6000 字节）。
溢出页独立存储数据，与原页通过 RID（行标识符）关联。
性能影响
增加随机 I/O（需读取多个页）。
建议优化表结构，避免频繁行溢出（如拆分大字段或使用 LOB 类型）。
四、区分配策略（Extent Allocation）

区的类型

混合区：区内的 8 个页分配给不同对象（适合小表初始分配）。
统一区：区内的 8 页专属于同一对象（适用于大表或索引）。
分配规则
默认策略：
新对象（<8页）使用混合区（节省空间）。
对象增长到 8 页后，转为统一区（提升连续读写性能）。
管理机制：
GAM（全局分配映射表）：标记区是否已分配（0=已分配，1=可用）。
SGAM（共享全局分配映射表）：标记混合区是否有空闲页（1=有可用页）。
优化场景
小表频繁插入：混合区减少空间浪费。
大表范围查询：统一区提高顺序 I/O 效率。
总结
页是存储基础单元，碎片分为内部（页内空间浪费）和外部（页物理不连续），通过重建索引或调整填充因子优化。
行溢出通过指针链接溢出数据，需权衡表设计避免性能损耗。
区分配策略通过混合区与统一区的动态切换，平衡空间利用与 I/O 效率。
实际应用中需结合 sys.dm_db_index_physical_stats 监控碎片，按业务场景调整策略

关键优化点
页碎片：频繁增删数据会导致页不连续，增加磁盘I/O。可通过重建索引调整空间 ALTER INDEX REBUILD 整理。
行溢出：变长列（如 varchar(max)）超过8KB时会拆分到其他页，需谨慎设计表结构。
区分配策略：小表优先使用混合区，大表使用统一区提升性能。
。
设计数据库时需关注页/区分配策略和碎片问题，以优化性能

SQL Server RID 回表详解

回表是针对表没有聚集索引的情况下，非聚集索引怎么去寻找数据的，如果有聚集索引，就不用通过RID回表直接去跟着聚集索引建流程去数据页找数据

什么是 RID 回表？
RID（Row Identifier）：当表没有聚集索引（堆表）时，SQL Server 为每一行数据分配的唯一物理地址，格式为 (文件号:页号:槽号)。
回表（Bookmark Lookup）：通过非聚集索引找到 RID 后，根据 RID 定位到数据页获取完整数据行的过程。
类比理解：
想象一本书的末尾有一个“关键词索引”，每个关键词后面标注了对应的页码（类似 RID）。回表就像根据页码翻到正文页面读取完整内容。
为什么需要回表？
非聚集索引的局限性：
非聚集索引的叶子节点只存储索引键值和 RID（或聚集索引键），不包含其他列的数据。
若查询需要返回非索引列，必须通过 RID 回表读取数据页
示例：
表结构：

CREATE TABLE Students (StudentID INT PRIMARY KEY NONCLUSTERED,  -- 非聚集主键Name VARCHAR(50),Age INT
);
SELECT Name, Age FROM Students WHERE StudentID = 100;
非聚集索引 StudentID 的叶子节点只有 StudentID 和 RID。为了获取 Name 和 Age，必须通过 RID 回表到数据页

如何判断 SQL Server 索引页和数据页的使用情况以及是否需要分表

通过系统视图和 DMV 获取索引页数量
核心工具：sys.dm_db_index_physical_stats
该动态管理视图直接提供索引的物理存储信息，包括索引页数量、碎片率、层级等。

SELECTOBJECT_NAME(object_id) AS TableName,index_type_desc AS IndexType,index_level AS IndexLevel,page_count AS TotalPages,record_count AS TotalRows,avg_fragmentation_in_percent AS Fragmentation
FROMsys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHEREindex_type_desc IN ('CLUSTERED', 'NONCLUSTERED');

关键字段解释：
index_type_desc：区分聚集索引（CLUSTERED）和非聚集索引（NONCLUSTERED）。
index_level：索引层级（0为叶级，>0为非叶级）。
page_count：当前层级的页数。
avg_fragmentation_in_percent:碎片率

聚集索引的叶级页（index_level=0）是数据页，非叶级页（如根、中间节点）是索引页；非聚集索引的所有页均为索引页。

统计索引页总数
聚集索引：总索引页数 = 根页数（index_level=最高层级） + 中间层级页数（index_level>0）。
非聚集索引：总索引页数 = 所有层级的 page_count 之和（包括叶级和非叶级）。

单表总数据量

SELECT SUM(p.rows) AS TotalRows,SUM(au.total_pages) * 8 / 1024 AS TotalSpaceMB  -- 总空间（MB）
FROM sys.partitions p
INNER JOIN sys.allocation_units au ON p.hobt_id = au.container_id
WHERE p.object_id = OBJECT_ID('YourTableName');

索引页数量的存储原理

聚集索引的索引页
结构：B+树结构，叶级是数据页，非叶级是索引页（存储键值和指针）。
计算示例：
假设根页（1页）指向中间层（7页），中间层指向叶级数据页（4000页），则总索引页数为 1+7=8 页，数据页为 4000 页。
唯一性影响：唯一聚集索引的索引页更少，而非唯一聚集索引可能因添加 uniqueifier 列增加页数。
非聚集索引的索引页
结构：独立于数据页，叶级存储键值 + 书签（聚集索引键或堆表 RID），非叶级存储导航键值。
页数计算：总索引页数 = 根页 + 中间层页 + 叶级页。
例如：某非聚集索引根页 1 页、中间层 7 页、叶级 179 页，总索引页数为 1+7+179=187 页。

实际案例与优化建议

案例：索引页分布分析
聚集索引：若某表总页数为 4020 页，其中数据页 4009 页，索引页（根+中间层）为 11 页。
非聚集索引：若覆盖索引（包含查询所需列）的叶级页数占比高，可能减少回表操作。
优化建议
监控碎片：高碎片率（如 avg_fragmentation_in_percent > 30%）需重建索引以减少页数。
填充因子调整：写频繁的表可降低填充因子（如 70%），预留空间减少页分裂

分表

判断是否需分表的依据

数据量阈值
常规场景：
单表数据行数 > 5000万或存储空间 > 500GB，考虑分表。
索引页占比过高（如索引页占总页数 >40%），需优化索引或分表。
高并发场景：
频繁出现页锁竞争（通过 sys.dm_tran_locks 监控），需水平分表。
性能指标
查询延迟：关键查询响应时间超过业务容忍阈值（如 >1秒）。
I/O 瓶颈：通过 sys.dm_io_virtual_file_stats 发现数据文件读写延迟高。
维护成本
索引维护时间：重建索引耗时过长（如 >1小时），影响业务可用性。
备份/恢复时间：单表过大导致备份窗口无法接受。

分表策略选择

水平分表（按行拆分）
适用场景：数据按时间、地域、哈希等逻辑可分割。
实现方式：
按时间分表：Order_2023、Order_2024。
按哈希分表：对主键哈希取模，分散到多个表

-- 创建分表（按年份）
CREATE TABLE Orders_2023 (OrderID INT PRIMARY KEY,OrderDate DATETIME,CustomerID INT,...
) ON FileGroup2023;CREATE TABLE Orders_2024 (OrderID INT PRIMARY KEY,OrderDate DATETIME,CustomerID INT,...
) ON FileGroup2024;-- 使用视图统一访问
CREATE VIEW AllOrders AS
SELECT * FROM Orders_2023
UNION ALL
SELECT * FROM Orders_2024;

垂直分表（按列拆分）
适用场景：表包含大量不常访问的列（如 BLOB 字段）。
实现方式：
将高频访问列（如 ID, Name）拆分到主表。
低频列（如 Description, Logs）拆分到扩展表

-- 主表（高频字段）
CREATE TABLE Users (UserID INT PRIMARY KEY,UserName VARCHAR(50),Email VARCHAR(100)
);-- 扩展表（低频字段）
CREATE TABLE UserDetails (UserID INT PRIMARY KEY,Address NVARCHAR(200),ProfileText TEXT,FOREIGN KEY (UserID) REFERENCES Users(UserID)
);

分表后的优化建议

分区方案（Partitioning）
使用 SQL Server 内置分区功能，按范围或列表分区，避免手动分表。
支持跨分区查询，简化管理。
应用层路由
在应用代码中根据分表键（如时间、用户ID）路由到具体表。
统一查询接口
使用视图或存储过程封装分表细节，对上层透明

在这里插入图片描述