中等 25 mins

實戰範例 05：為訂單資料表建立時間加上索引

在 SQLite 資料庫的 orders 表中的 created_at 欄位上加上索引，以優化分頁查詢的效能。

使用指令 /opsx:propose/opsx:apply/opsx:archive

#資料庫變更#索引優化#Migration#後端開發

實戰背景

當系統中的訂單資料量逐漸增加到數萬、甚至數十萬筆時，若是沒有對查詢欄位進行優化，前端後台在載入「訂單列表（預設依時間排序）」時，就會面臨嚴重的查詢延遲 (Query Latency)。這是因為資料庫必須進行全表掃描 (Full Table Scan) 才能完成排序與分頁。

本範例中，我們將透過建立遷移 (Migration) 腳本，為 SQLite 資料庫中的 orders 資料表的 created_at 欄位建立索引，大幅提升查詢效能。我們將使用 快速開發工作流 (Quick Flow) 來進行此項任務。

規格定義

本次變更計畫的 Capability 為 database-schema。我們預計在規格增量 (Spec Delta) 中定義以下驗收標準：

## ADDED Requirements

### Requirement: Database query index
The system database SHALL maintain an index on the orders table's creation timestamp to optimize list query performance.

#### Scenario: Order query indexing
- **WHEN** user requests the latest orders list ordered by created_at desc
- **THEN** the query SHALL utilize the database index and respond in under 50 milliseconds

端到端 6 輪 AI 協作紀錄

以下是 You 與 AI 助手的完整對話歷史。

── 第 1 輪：建立計畫與提案 ──

You:

/opsx:propose "為 orders 資料表的 created_at 欄位建立 index 以優化排序查詢效能"

AI:
好的，已建立變更計畫 add-orders-created-at-index：
- 建立 proposal.md: 說明 Why 與 What。
- 建立 specs/database-schema/spec.md: 新增建立時間索引及查詢耗時限制的驗收情境 (Scenario)。
- 建立 tasks.md: 規劃了以下 3 個實作任務：
  - 1.1 撰寫 SQL 遷移腳本以新增索引
  - 1.2 於本地執行 Migration
  - 1.3 撰寫效能基準測試 (Benchmark Test) 驗證效能
規劃就緒！請執行 /opsx:apply 開始實作。

── 第 2 輪：審查與微調提案 ──

You:

我們在設計中要加上 EXPLAIN QUERY PLAN 來驗證是否真的使用了這個 index，請更新設計與 tasks.md。

AI:
好的，我已透過 /opsx:update 更新文件：
- 更新 design.md：規劃使用 EXPLAIN QUERY PLAN 語法，驗證查詢計劃 (Query Plan) 是否呈現 USING INDEX idx_orders_created_at。
- 更新 tasks.md：在任務 1.3 中加入「執行 EXPLAIN 檢查」的子任務。
一致性更新完成。現在您可以執行 /opsx:apply 了。

── 第 3 輪：實作任務 1 (撰寫 SQL 遷移) ──

You:
```
/opsx:apply
```
AI:
Implementing: add-orders-created-at-index (schema: spec-driven)

正在處理任務 1/3：撰寫 SQL 遷移腳本以新增索引。

我已在專案中建立遷移腳本 src/db/migrations/002_add_index_to_orders.sql：
```
-- Up
CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at DESC);

-- Down
DROP INDEX IF EXISTS idx_orders_created_at;
```
✓ 任務已完成。已將 tasks.md 中的任務 1.1 標記為完成。

準備好進入任務 1.2「執行 Migration」了嗎？

── 第 4 輪：實作任務 2 (執行 Migration) ──

You:
```
繼續執行 Migration。
```
AI:

Implementing: add-orders-created-at-index

正在處理任務 2/3：於本地執行 Migration。

我已呼叫本地的遷移指令，成功對 SQLite 資料庫執行了該 SQL 腳本。索引 idx_orders_created_at 已經成功建立。

✓ 任務已完成。已將任務 1.2 標記為完成。

剩下最後一個任務：撰寫效能基準測試與查詢計畫驗證。是否繼續？

── 第 5 輪：實作任務 3 (測試與驗證) ──

You:
```
繼續實作測試驗證。
```
AI:
Implementing: add-orders-created-at-index

正在處理任務 3/3：撰寫效能基準測試驗證效能。

我已在 tests/db_performance.test.ts 中實作了測試。
- 測試插入了 10,000 筆隨機訂單資料。
- 執行 EXPLAIN QUERY PLAN SELECT * FROM orders ORDER BY created_at DESC LIMIT 20;。
- 驗證結果顯示 SEARCH orders USING INDEX idx_orders_created_at。
- 查詢平均耗時由 120ms 降至 2.4ms，遠低於規格要求的 50ms。
✓ 任務已完成。已將任務 1.3 標記為完成。

3/3 任務已全部完成！是否開始進行驗證與歸檔？

── 第 6 輪：驗證與歸檔 ──

You:
```
執行 verify 並將變更歸檔。
```
AI:
好的，我為您執行 /opsx:verify 與 /opsx:archive：
1. 驗證成功：查詢效能與 EXPLAIN QUERY PLAN 結果完全符合 Scenario: Order query indexing 的要求。
2. 智慧同步：已將暫存規格合併至主規格 openspec/specs/database-schema/spec.md。
3. 清理歸檔：已將計畫資料夾移動至 openspec/changes/archive/2026-07-14-add-orders-created-at-index/。
Archive Complete

變更計畫已成功關閉與歸檔！工作目錄目前非常乾淨。

程式碼與設計亮點

DESC 排序索引優化：由於訂單查詢大多是「最新優先（created_at DESC）」，因此在建立索引時使用 DESC 排序，使資料在庫內以預期排序存儲，查詢時可直接順序掃描，效能達到最佳。
無損復原 (Rollback)：提供 Down 腳本，確保在需要回滾時能安全移除索引。
EXPLAIN 自動化檢查：在測試中寫死 EXPLAIN 檢查，能有效防範未來其他人修改資料表結構時意外讓此索引失效。