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

定位需優化語句

根據工具 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)
);