萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

CREATE INDEX 語法全解析:選項、檔案群組、填充因子與最佳實踐

CREATE INDEX 是建立索引的標準 SQL 命令,支援豐富選項控制索引物理特性、儲存位置、建立行為。本文以 SQL Server / Oracle 通用語法為基礎,詳細解析各選項語意、適用場景與最佳實踐配置。

標準語法骨架

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON { table | view } ( column [ ASC | DESC ] [ ,...n ] )
[ INCLUDE ( column [ ,...n ] ) ]           -- 覆蓋索引包含欄位 (SQL Server)
[ WHERE  ]                -- 部分索引 / 過濾索引 (SQL Server 2008+, PostgreSQL, Oracle 12c+)
[ WITH (  [ ,...n ] ) ]
[ ON { filegroup | tablespace } ]

核心選項詳細解析

選項語法功能與建議值
UNIQUECREATE UNIQUE INDEX ...強制索引鍵值唯一。視圖上的叢集索引**必須**為 UNIQUE。建立時檢查現有資料,若有重複則失敗
CLUSTERED / NONCLUSTEREDCREATE CLUSTERED INDEX ...叢集索引:資料物理順序 = 索引順序,葉子即資料。**每表僅一個**。非叢集:預設值,葉子存 Rowid/主鍵指標
排序方向col ASC | DESC單列索引無影響;複合索引配合 ORDER BY a ASC, b DESC 可避免排序。預設 ASC
INCLUDE (覆蓋欄位)INCLUDE (col1, col2)**僅非叢集索引**。將非鍵欄位存入葉子節點,實現 **覆蓋索引 (Covering Index)**,免回表。不計入鍵長度限制 (900 bytes),不支援排序/搜尋
WHERE (過濾/部分索引)WHERE status = 'ACTIVE'僅索引滿足條件的行。**大幅縮小索引體積、減少維護成本**、提升查詢精準度。適用於高度傾斜資料 (如 90% 歷史狀態、10% 活躍狀態)
PAD_INDEXWITH (PAD_INDEX = ON)將 FILLFACTOR 套用至**中間頁面** (非葉子)。預設 OFF。大量插入環境建議 ON
FILLFACTOR (SQL Server) / PCTFREE (Oracle)WITH (FILLFACTOR = 80)**葉子頁面預留空間百分比 (0-100)**。
• 遞增鍵 (Identity/Sequence):**100 (0)**
• 隨機鍵 (UUID):**70-80 (20-30)**
• 頻繁 UPDATE 導致行增長:**70-80 (20-30)**
• 唯讀/批次載入:**100 (0)**
IGNORE_DUP_KEYWITH (IGNORE_DUP_KEY = ON)**僅 UNIQUE 索引**。插入重複鍵值時:**忽略該行並警告**,而非報錯回滾整批。適用於 ETL 去重場景
DROP_EXISTINGWITH (DROP_EXISTING = ON)重建索引時自動刪除同名舊索引,**保留權限/統計資訊**,單一陳述式完成原子化重建
STATISTICS_NORECOMPUTEWITH (STATISTICS_NORECOMPUTE = ON)禁止自動更新統計資訊。**慎用**,除非極大表手工控制統計收集時機
SORT_IN_TEMPDBWITH (SORT_IN_TEMPDB = ON)索引建立/重建時的排序作業在 **tempdb** 進行。減少使用者資料庫交易記錄、加快排序 (tempdb 通常在快速儲存)、需額外 tempdb 空間
ONLINEWITH (ONLINE = ON)**Enterprise Edition 專屬**。重建期間允許並發 DML,透過版本存儲維護一致性。大表維護**必開**
DATA_COMPRESSIONWITH (DATA_COMPRESSION = PAGE | ROW)頁面/行壓縮。PAGE 壓縮率高但 CPU 稍增。大型索引建議 PAGE 壓縮,節省 40-60% 空間並減少 I/O
ON filegroup / tablespaceON index_fg / TABLESPACE index_ts指定儲存位置。**非叢集索引強烈建議與資料表分離至不同 LUN/磁碟組**

Oracle 特有語法差異

-- Oracle 無 CLUSTERED/NONCLUSTERED 關鍵字
-- 叢集索引 = Index-Organized Table (IOT) 或主鍵索引
-- 非叢集索引 = 標準 B-tree 索引

CREATE [UNIQUE] INDEX idx_name ON table_name (col1, col2 DESC)
  [TABLESPACE ts_name]
  [PCTFREE 20]
  [INITRANS 2 MAXTRANS 255]
  [STORAGE (INITIAL 1M NEXT 1M PCTINCREASE 0)]
  [COMPRESS [ADVANCED LOW | HIGH]]  -- 11g+ Advanced Compression
  [INVISIBLE]                        -- 11g+ 隱形索引
  [PARALLEL n];                      -- 平行建立

最佳實踐範本

場景 1:OLTP 核心表複合索引 (隨機插入、高併發)

CREATE NONCLUSTERED INDEX idx_orders_cust_status
ON dbo.orders (customer_id, order_status)
INCLUDE (order_date, total_amount)
WITH (
    FILLFACTOR = 80,
    ONLINE = ON,
    DATA_COMPRESSION = PAGE
)
ON index_fg;  -- 獨立檔案群組/表空間

場景 2:歷史資料表過濾索引 (僅索引近期活躍資料)

CREATE NONCLUSTERED INDEX idx_orders_recent
ON dbo.orders (customer_id, order_date)
WHERE order_date >= DATEADD(MONTH, -6, GETDATE())  -- 僅近 6 個月
WITH (FILLFACTOR = 90, ONLINE = ON)
ON index_fg;

場景 3:唯一業務鍵 (含 IGNORE_DUP_KEY 容錯)

CREATE UNIQUE NONCLUSTERED INDEX uk_orders_ext_ref
ON dbo.orders (external_reference)
WITH (IGNORE_DUP_KEY = ON, ONLINE = ON)
ON index_fg;

場景 4:Oracle 函數索引 + 壓縮 + 隱形測試

CREATE INDEX idx_emp_upper_name ON emp(UPPER(ename))
  TABLESPACE index_ts
  PCTFREE 10
  COMPRESS ADVANCED LOW
  INVISIBLE
  PARALLEL 4;

-- 驗證無誤後
ALTER INDEX idx_emp_upper_name VISIBLE;

建立後必做三件事

  1. 收集統計資訊:
    -- Oracle
    EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', cascade=>TRUE, method_opt=>'FOR ALL COLUMNS SIZE AUTO');
    
    -- SQL Server
    UPDATE STATISTICS dbo.emp WITH FULLSCAN;
  2. 驗證執行計畫:EXPLAIN / SET STATISTICS IO, TIME ON 確認 Index Seek + Key Lookup / Covering Index
  3. 啟用使用監控:
    -- Oracle
    ALTER INDEX idx_name MONITORING USAGE;
    
    -- SQL Server (Extended Events / Query Store 自動捕獲)

熟練掌握 CREATE INDEX 全選項語意,並依「負載特性 → 選項組合 → 儲存隔離 → 壓縮/線上/隱形 → 統計/驗證/監控」標準化流程執行,即能產出高效能、低維護、可演進的索引資產。

copyright © 萬盛學電腦網 all rights reserved