在雲端 Mac 驗收 iOS SQLite：複合索引、查詢計畫與掃描預算

客戶的訂單清單在測試資料庫裡瞬間開啟，匯入歷史資料後卻明顯變慢。接案工作室在雲端 Mac 上交付多個專案時，應先以固定資料驗收查詢計畫，而不是反覆換機器計時：前者有助於找出缺少的索引，後者則容易把快取與負載波動誤認為最佳化成果。本文以一條訂單查詢，建立從命令列預檢到 iOS 實際連線複核的流程。

一、先固定查詢契約，再討論索引

先約定清單只取某位客戶已完成的訂單，依建立時間由新到舊排序，時間相同時再依編號遞減排序，每次最多回傳二十筆。測試必須同時檢查篩選條件、順序與筆數，不能只看執行時間。

準備三組參數：有訂單的客戶、不存在的客戶，以及訂單高度集中的客戶。少量且分布均勻的資料適合驗證邏輯，卻無法代表真實業務分布；正式環境中，少數大客戶可能就占了大部分資料。

記錄 sqlite_version()、資料表結構、索引定義，以及統計資訊的產生方式。Mac 內建的命令列工具與應用程式使用的引擎可能不同，尤其是專案自行整合 SQLite 時，不能把兩者的查詢計畫視為同一份驗收結論。

查詢計畫是最佳化器的選擇，不是穩定的文字介面。不要把整段輸出逐字相同，當成跨版本驗收的通過條件。

二、建立不會碰到客戶資料庫的測試夾具

在終端機執行以下指令碼。它會建立獨立的暫存目錄，不會連線到正式環境的資料庫；資料編號固定，重複執行可得到相同的邏輯內容。SQL 檔案與資料庫放在一起，方便交付時重新執行。

set -eu
work="$(mktemp -d)"
cat > "$work/check.sql" <<'SQL'
.bail on
SELECT sqlite_version();
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  status TEXT NOT NULL,
  created_at INTEGER NOT NULL
);
WITH RECURSIVE seq(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM seq WHERE x < 10000
)
INSERT INTO orders
SELECT x, x % 100,
       CASE WHEN x % 3 = 0 THEN 'done' ELSE 'open' END,
       x / 10
FROM seq;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
CREATE INDEX orders_customer_status_time
ON orders(customer_id, status, created_at DESC, id DESC);
ANALYZE;
EXPLAIN QUERY PLAN
SELECT id, created_at FROM orders
WHERE customer_id = 7 AND status = 'done'
ORDER BY created_at DESC, id DESC LIMIT 20;
SQL
sqlite3 "$work/fixture.sqlite" < "$work/check.sql"
printf '%s\n' "$work"

先儲存終端機輸出，再執行實際查詢並檢查結果。這裡刻意讓多筆資料具有相同的建立時間，以驗證第二排序鍵確實生效。若只驗證最新一筆，就會漏掉相同時間資料的排序不穩定問題。

三、讀懂存取路徑，不背計畫字串

沒有索引時，通常會看到訂單資料表掃描，以及為排序建立的暫存結構。建立索引後，要檢查是否依客戶與狀態查找，以及是否仍需額外排序。具體措辭會隨 SQLite 版本改變，不應假設輸出永遠相同。

複合索引先放等值條件，再接排序欄位，目的是讓引擎沿著目標範圍讀取前二十筆。這個索引也涵蓋了本例查詢需要的所有欄位；若日後加入大型欄位，就可能不再是覆蓋索引，應重新驗收，而不是繼續引用舊報告。

不要看到 SCAN 就判定失敗：依序掃描覆蓋索引，與逐列篩選大型資料表並不是同一回事。反過來說，出現 SEARCH 也不保證工作量小，選擇性低的條件仍可能讀取大量資料。

索引適合用於高頻查詢的清單，不適合毫無區別地加在所有欄位上。索引會增加寫入與儲存成本；提交修改時，也要執行新增、更新與刪除測試案例，確認讀取最佳化沒有超出交付預算。

四、用實際連線檢查掃描預算

通過命令列檢查後，將測試夾具匯入應用程式的測試環境，並透過專案實際使用的資料庫封裝，執行同一條參數化查詢。繫結參數的型別必須一致，查詢結果也必須讀取到結束，不能取到第一筆就停止計數。

若能存取底層連線，執行前先用 sqlite3_stmt_status 將 SQLITE_STMTSTATUS_FULLSCAN_STEP 歸零，執行後再讀取；另外透過 SQLITE_STMTSTATUS_SORT 查看排序操作次數。前者統計與全資料表掃描相關的步數，後者不是執行時間，也不是參與排序的資料筆數。

這兩個計數都為零，也不代表查詢沒有成本。查詢仍可能進行大量索引存取；可另外記錄 SQLITE_STMTSTATUS_VM_STEP，搭配回傳筆數一起判斷。若封裝沒有開放陳述式控制代碼，應先增加測試轉接層，不要解析業務日誌中的執行時間，拿來冒充掃描統計。

預算應以固定測試夾具經審核的基準值為依據，不要直接套用通用門檻。每次計數前都要重設，避免重複使用 prepared statement 時，把上次執行的數值也累加進來。

五、將資料分布變化納入交付檢查

驗收至少保留兩套資料庫：均勻分布的資料庫用來檢查基本存取路徑，偏斜分布的資料庫則用來模擬大客戶。兩套都要檢查二十筆上限、排序穩定性、空結果，以及不同的狀態參數。

統計資訊會影響查詢計畫。應分別記錄新建資料庫與執行 ANALYZE 後的結果；如果產品執行時不會產生統計資訊，就不能只驗收具備統計資訊的理想狀態。也不要為了得到漂亮的計畫而強制指定索引，掩蓋最佳化器的實際選擇。

最後，將 SQL、引擎版本、資料分布說明、索引定義、查詢計畫與計數結果一併歸檔。出現新的迴歸問題時，先比較這些證據，再看執行時間。即使工作室更換雲端 Mac，或專案交付延期，仍能依據同一份測試夾具說明：究竟是修改改變了存取路徑，還是只是剛好遇上已暖機的快取。

常見問題