索引是資料庫效能的靈魂。不懂原理,就無法解釋:為什麼有索引查詢還慢?為什麼不走索引掃描反而更快?為什麼寫入效能隨索引增加而下降?本文以 SQL Server 為例 (原理通用於 InnoDB、Oracle、PostgreSQL),從存儲層層層剖析。
sys.system_internals_allocation_units 可查 IAM 鏈| 特性 | 堆表 | 叢集索引表 |
|---|---|---|
| 資料順序 | 無序 (插入位置由 PFS/IAM 決定) | 按叢集鍵邏輯有序 (物理盡量有序) |
| 定位方式 | RID (File:Page:Slot) | 叢集鍵值 (Key Lookup 用) |
| 非叢集索引葉層 | 存 RID | 存叢集鍵值 |
| 頁分裂 | 較少 (插入尾部) | 隨機鍵頻繁分裂 |
+------------------ 索引頁面 (8KB) ------------------+ | Page Header (96 bytes) | | - Page ID, Object ID, Index ID, LSN, ... | | - Prev/Next Page Pointers (雙向鏈表) | | - Free Space Offset, Slot Array Offset | +-------------------------------------------------+ | Slot Array (每槽 2 bytes,從頁尾向前增長) | | [0] → Offset 96 (Key: 100, Child: Page 101) | | [1] → Offset 120 (Key: 200, Child: Page 102) | | [2] → Offset 144 (Key: 300, Child: Page 103) | | ... | +-------------------------------------------------+ | Free Space (插入新記錄向前擴展) | +-------------------------------------------------+ | Data Rows (索引記錄:Key + Row Locator) | | Key: 索引鍵值 (含 Included Columns) | | Row Locator: RID (堆) 或 Clustered Key (叢集) | +-------------------------------------------------+
sysindexes.root 指向-- 查詢:SELECT * FROM Orders WHERE OrderID = 50000;
-- 叢集索引 CX_Orders_OrderID (OrderID)
1. 讀取 Root Page (Level 2)
Keys: [10000, 30000, 50000, 80000] → 50000 >= 50000 走第 4 個指針 → Page 204
2. 讀取 Intermediate Page (Level 1) - Page 204
Keys: [40000, 45000, 50000, 55000] → 50000 >= 50000 走第 4 個指針 → Page 305
3. 讀取 Leaf Page (Level 0) - Page 305
Keys: [49990, 49995, 50000, 50005] → 定位 Slot 2 → 讀取完整行資料 (含所有列)
總計:3 次邏輯讀 (若熱頁在記憶體則 0 物理 I/O)
CEILING(LOG_Fanout(Rows)),Fanout = 頁面容納鍵值數 (通常 100-500)| 場景 | 建議 Fill Factor | 理由 |
|---|---|---|
| 唯一遞增鍵 (Identity, Sequence) | 100 (或 0 = 默認 100) | 永遠插入尾部,無分裂 |
| 隨機鍵 (UUID, 複合鍵) | 70-90 | 預留空間減少分裂頻率 |
| 大量更新導致行長度增長 | 70-80 | 預留前向擴展空間 |
| 唯讀/極少寫入 | 100 | 最大化空間利用率 |
ALTER INDEX IX_Orders_CustomerID ON Orders REBUILD WITH (FILLFACTOR = 80, ONLINE = ON);
WHERE col = value 返回行數-- 1. 索引碎片與頁面密度
SELECT
OBJECT_NAME(ips.object_id) AS TableName,
i.name AS IndexName,
ips.index_type_desc,
ips.avg_fragmentation_in_percent,
ips.avg_page_space_used_in_percent,
ips.page_count,
ips.record_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;
-- 2. 缺失索引建議 (優化器記錄的遺憾)
SELECT
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS improvement_measure,
mid.statement AS TableName,
mid.equality_columns, mid.inequality_columns, mid.included_columns,
migs.user_seeks, migs.user_scans
FROM sys.dm_db_missing_index_group_stats migs
JOIN sys.dm_db_missing_index_groups mig ON migs.group_handle = mig.index_group_handle
JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE mid.database_id = DB_ID()
ORDER BY improvement_measure DESC;
-- 3. 未使用索引 (維護成本 > 查詢收益)
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates,
s.last_user_seek, s.last_user_scan, s.last_user_lookup
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
AND s.user_seeks + s.user_scans + s.user_lookups = 0
AND s.user_updates > 0
ORDER BY s.user_updates DESC;
理解這些原理,您就能從「聽說要加索引」進化為「基於存儲結構、查詢模式、寫入特性做精準索引設計」。