CREATE UNIQUE INDEX / PRIMARY KEY / UNIQUE CONSTRAINT 物理層面強制資料唯一,比應用層檢查可靠萬倍。ORDER BY indexed_col、GROUP BY indexed_col 直接輸出有序流,省略昂貴的 Sort (記憶體/TempDB 溢寫風險)。Index_ROI = (Query_Frequency × Query_Speedup_Factor × Business_Value_Per_Query)
/ (Write_Frequency × Write_Overhead_Per_Index
+ Storage_Cost_GB_Month × Index_Size_GB
+ Maintenance_Window_Cost_Per_Hour × Maintenance_Hours)
user_id, order_no, phone, email, device_idcustomer_id, product_id, order_id (雙邊建索引)create_time, update_time, score (複合索引尾部)category_id, status, regionINCLUDE (name, email, address)TEXT, NTEXT, IMAGE, VARCHAR(MAX), VARBINARY(MAX), XML, JSON → 不進 B+ 樹,無法建立標準索引 (需 Full-Text / XML / JSON 專用索引)last_login_time, view_count, balance (余額查詢走 PK 覆蓋即可) → 寫入放大嚴重-- ❌ OFFSET 1000000 ROWS FETCH NEXT 20 → 掃描 100 萬行丟棄
-- ✅ 遊標分頁 (Keyset Pagination) - 需唯一遞增鍵
WHERE id > @last_seen_id ORDER BY id LIMIT 20; -- 索引 Seek 直接定位
-- ✅ 覆蓋索引 + 延遲關聯
SELECT t.* FROM (SELECT id FROM Orders WHERE status=1 ORDER BY id OFFSET 1000000 ROWS FETCH NEXT 20) t
JOIN Orders o ON o.id = t.id; -- 內層只掃索引葉層,外層 20 次 Key Lookup
-- 所有查詢帶 tenant_id + is_deleted=0
CREATE INDEX IX_Orders_Tenant_Status_Time ON Orders (tenant_id, is_deleted, status, create_time)
INCLUDE (customer_id, amount);
-- Filtered Index (SQL Server/PG/Oracle) 針對未刪除資料
CREATE INDEX IX_Orders_Active ON Orders (tenant_id, status, create_time)
WHERE is_deleted = 0; -- 索引體積縮減 90%+,維護成本同比降低
(log_date, id) 而非單純 id → 範圍查詢連續、分區對齊最好的索引策略,是建立最少的索引,解決最多的效能問題。
user_seeks+scans+lookups = 0 AND user_updates > 0 持續 30 天 → DROP把索引當作「稀缺資源」來預算分配,每個索引都要有明確的業務正當性、效能收益量化、維護成本核算。這才是資深 DBA 與初學者的分水嶺。