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 } ]
| 選項 | 語法 | 功能與建議值 |
|---|---|---|
| UNIQUE | CREATE UNIQUE INDEX ... | 強制索引鍵值唯一。視圖上的叢集索引**必須**為 UNIQUE。建立時檢查現有資料,若有重複則失敗 |
| CLUSTERED / NONCLUSTERED | CREATE 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_INDEX | WITH (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_KEY | WITH (IGNORE_DUP_KEY = ON) | **僅 UNIQUE 索引**。插入重複鍵值時:**忽略該行並警告**,而非報錯回滾整批。適用於 ETL 去重場景 |
| DROP_EXISTING | WITH (DROP_EXISTING = ON) | 重建索引時自動刪除同名舊索引,**保留權限/統計資訊**,單一陳述式完成原子化重建 |
| STATISTICS_NORECOMPUTE | WITH (STATISTICS_NORECOMPUTE = ON) | 禁止自動更新統計資訊。**慎用**,除非極大表手工控制統計收集時機 |
| SORT_IN_TEMPDB | WITH (SORT_IN_TEMPDB = ON) | 索引建立/重建時的排序作業在 **tempdb** 進行。減少使用者資料庫交易記錄、加快排序 (tempdb 通常在快速儲存)、需額外 tempdb 空間 |
| ONLINE | WITH (ONLINE = ON) | **Enterprise Edition 專屬**。重建期間允許並發 DML,透過版本存儲維護一致性。大表維護**必開** |
| DATA_COMPRESSION | WITH (DATA_COMPRESSION = PAGE | ROW) | 頁面/行壓縮。PAGE 壓縮率高但 CPU 稍增。大型索引建議 PAGE 壓縮,節省 40-60% 空間並減少 I/O |
| ON filegroup / tablespace | ON index_fg / TABLESPACE index_ts | 指定儲存位置。**非叢集索引強烈建議與資料表分離至不同 LUN/磁碟組** |
-- 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]; -- 平行建立
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; -- 獨立檔案群組/表空間
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;
CREATE UNIQUE NONCLUSTERED INDEX uk_orders_ext_ref
ON dbo.orders (external_reference)
WITH (IGNORE_DUP_KEY = ON, ONLINE = ON)
ON index_fg;
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;
-- 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;
EXPLAIN / SET STATISTICS IO, TIME ON 確認 Index Seek + Key Lookup / Covering Index-- Oracle
ALTER INDEX idx_name MONITORING USAGE;
-- SQL Server (Extended Events / Query Store 自動捕獲)
熟練掌握 CREATE INDEX 全選項語意,並依「負載特性 → 選項組合 → 儲存隔離 → 壓縮/線上/隱形 → 統計/驗證/監控」標準化流程執行,即能產出高效能、低維護、可演進的索引資產。