wpdev
e8731c5119
Anti-EAV Lint + Quality Gate / anti-eav-lint (pull_request) Successful in 18s
Tests / PHP Lint (pull_request) Successful in 59s
Tests / PHPCS (pull_request) Successful in 30s
Tests / PHPStan (pull_request) Successful in 1m12s
Tests / Unit Tests (pull_request) Successful in 47s
Tests / Integration Tests (pull_request) Successful in 57s
- CHANGELOG / readme 1.1.0: flat-first routing for flat-sorted searches (tmdo_router_flat_first) and the all-NULL flat row left after deleting an entity - ADR-004: NULL sync never inserts a row; flat-first rule; front-end p50 from the R-2.1 acceptance run - BENCHMARK-SCALE: front-end table from the acceptance run (flat tables backfilled) and the join-order finding behind PR-062c - Verify: composer phpcs (0) - PR: PR-064b
110 lines
10 KiB
Markdown
110 lines
10 KiB
Markdown
# 規模效能基準：原生 postmeta vs Hot 表 vs Entity 表
|
||
|
||
**量測日期**：2026-09-25 **分支**：`hardening/2.0` **目標規模**：vendor 100,000、listing 1,000,000
|
||
|
||
## 如何重現
|
||
|
||
```bash
|
||
# DB 帳密沿用整合測試的 TMDO_TEST_DB_* 環境變數；資料庫名稱必須以 _bench 結尾
|
||
TMDO_TEST_DB_NAME=wp_wpdo_bench php tests/bench/seed.php --vendors=100000 --listings=1000000 # 約 7.5 分鐘
|
||
TMDO_TEST_DB_NAME=wp_wpdo_bench php tests/bench/run.php --iterations=20 # 約 20 分鐘
|
||
```
|
||
|
||
## 環境與資料
|
||
|
||
| 項目 | 值 |
|
||
|---|---|
|
||
| 資料庫 | MariaDB 10.11.14（本機，InnoDB 預設設定） |
|
||
| 主機 | 8 核、31 GB RAM、NVMe |
|
||
| 資料 | 100,000 vendor（users + hp_vendor posts）、1,000,000 listing（90% publish） |
|
||
| 原生 meta | postmeta 9,549,796 筆（951 MB）、usermeta 600,000 筆 |
|
||
| Entity 表 | `wpdo_post_hp_listing_core` 1,000,000 筆（379 MB） |
|
||
| Hot 表 | `wpdo_hot_hp_listing` 1,000,000 筆（229 MB） |
|
||
|
||
SQL 的形狀與實際程式一致：原生是 WP_Query 的 meta_query（每個條件一個 INNER JOIN、數值用 CAST、`SQL_CALC_FOUND_ROWS`），Hot 是 `TMDO_Query_Router`，Entity 是 `TMDO_Post_Query_Router`。每個情境 20 次，另有 3 次暖機不計入，參數隨機但可重現（`--seed=7`）。寫入在交易內執行後 rollback。
|
||
|
||
> 20 次取樣下 p95 等於最大值，僅供參考。
|
||
|
||
## 結果
|
||
|
||
| 情境 | 形式 | p50 ms | p95 ms | 結果筆數 | 使用索引 |
|
||
|---|---|---:|---:|---:|---|
|
||
| 1. 價格區間搜尋（命中約 20%） | 原生 | **14,695** | 14,890 | 180,292 | postmeta:post_id |
|
||
| | Hot | 558 | 3,605 | 180,329 | h:ALL |
|
||
| | Entity | 652 | 3,817 | 180,300 | e:ALL |
|
||
| 2. 價格區間 + verified=1 | 原生 | **17,094** | 17,250 | 54,197 | postmeta:post_id ×2 |
|
||
| | Hot | 277 | 299 | 54,109 | `idx_hp_verified_hp_price`（複合） |
|
||
| | Entity | 337 | 340 | 54,180 | e:ALL |
|
||
| 3. 某 vendor 名下的 listing | `post_parent`（HivePress 實際做法） | 0.19 | 0.25 | 7 | posts:post_parent |
|
||
| | 原生 meta `hp_vendor` | **14,432** | 14,599 | 8 | postmeta:post_id |
|
||
| | Hot | **3,405** | 3,651 | 10 | 無可用索引 |
|
||
| | Entity | 0.28 | 0.33 | 8 | `idx_hp_vendor` |
|
||
| 4. 單筆 listing 讀全部 meta | 原生 | 0.12 | 0.15 | 9 列 | postmeta:post_id |
|
||
| | Hot / Entity | 0.09 | 0.11–0.14 | 1 列 | 主鍵 |
|
||
| 5. 列表頁預載 20 筆 meta | 原生 | 0.59 | 0.61 | 181 列 | postmeta:post_id |
|
||
| | Hot / Entity | 0.21–0.22 | 0.23–0.26 | 20 列 | 主鍵 |
|
||
| 6. 單一 vendor 讀使用者 meta | 原生 | 0.11 | 0.14 | 6 列 | usermeta:user_id |
|
||
| | Entity | 0.08 | 0.11 | 1 列 | 主鍵 |
|
||
| 7. 寫入：更新一筆價格 | 原生 | 0.12 | 0.16 | — | — |
|
||
| | Entity | 0.10 | 0.13 | — | — |
|
||
| | dual_write（兩邊都寫） | 0.22 | 0.26 | — | — |
|
||
|
||
## 結論
|
||
|
||
1. **原生 meta_query 在百萬規模下無法使用。** 任何以 meta 為條件的搜尋都要 14–17 秒，扁平化是必要的，不是錦上添花。
|
||
2. **扁平表的價值集中在「搜尋與篩選」。** 單筆或批次讀 meta（情境 4–6）三種形式都在 1 ms 內，差距可以忽略，而且實務上還有 object cache。
|
||
3. **寬範圍搜尋仍有瓶頸（情境 1，p50 約 0.6 秒、p95 約 3.7 秒）。** 命中 18 萬筆時，優化器選擇全表掃描；再加上 `SQL_CALC_FOUND_ROWS` 要算總數、`ORDER BY post_date` 要排序，還要 JOIN 回 `posts` 過濾狀態。p50 和 p95 差很多，是因為不同的價格區間讓優化器選了不同的執行計畫。
|
||
- **PR-028**：post 群組表可存 `post_status`、`post_date` 副本與 `(post_status, post_date)` 索引（`wp tmdo post-status-sync`）。1M 列實測：加欄位 0.05 秒（instant）、建索引 5.3 秒、分批（1,000 列）回填 32 秒，之後與 `posts` 0 筆不一致。欄位 collation 必須跟 `posts.post_status` 相同，否則比對會出現 illegal mix of collations。查詢改寫在 PR-029。
|
||
- **PR-029**：副本 ready 且無 NULL 時，單一 post type、明確狀態、依日期排序的搜尋改為 INNER JOIN、`post_status IN (…)`、`ORDER BY` 副本 `post_date`、不 GROUP BY（bench 變體 `entity+copy`，`--iterations=5`）。仍帶 `SQL_CALC_FOUND_ROWS` 時：情境 1 p50 670→770 ms、**p95 3,739→773 ms**；情境 2 p50 356→448 ms（變慢，因為算總數仍要掃完所有符合列）。拿掉 `SQL_CALC_FOUND_ROWS` 後同形狀情境 1 只要 **8 ms**（走 `idx_post_status_date`、LIMIT 提早結束）→ 完整效益要搭配 PR-030。
|
||
- **PR-030**：路由後的搜尋不再帶 `SQL_CALC_FOUND_ROWS`；總數改由同一查詢的 `COUNT(*)` 回答，存在物件快取，key 綁 `last_changed(posts)`（核心存文、Hook Bus 扁平寫入都會推進）。1M 實測：快取命中時整頁 **8 ms**（原 694 / 363 ms）；快取失效後第一次為頁面 8 ms + COUNT 608 / 344 ms，與原本相當。任何 post 寫入都讓全部計數失效（WP last_changed 語意），寫入頻繁的站命中率較低。
|
||
4. **Hot 表的索引規則有漏洞。** `add_covering_indexes()` 只替 decimal、tinyint，以及名稱以 `_time` 結尾的 bigint 建索引，所以 `hp_vendor` 沒有索引，情境 3 要 3.4 秒，比 Entity 慢約 12,000 倍。
|
||
5. **Entity 表只有單欄索引。** 情境 2 輸給有複合索引的 Hot 表（337 ms 對 277 ms）。
|
||
- **PR-027 之後**：群組可用 `register_group( ..., array( 'indexes' => ... ) )` 宣告複合索引。在 1M 列的 Entity 表加上 `cidx_verified_price (hp_verified, hp_price)` 花 8.1 秒；重跑情境 2（`--iterations=10`），Entity p50 從 337 ms 降到 **278 ms**（p95 285 ms），Hot 是 269 ms（p95 280 ms），差距在量測誤差內。執行計畫改走 `e:cidx_verified_price`。
|
||
6. **dual_write 的寫入成本約為兩倍（0.22 ms 對 0.12 ms）**，絕對值很小，不是瓶頸。
|
||
7. **Hot 表和 Entity 表儲存了同一份 listing 資料**（合計約 608 MB），兩者功能重疊。
|
||
|
||
## 前台與寫入鏈實測（dev36，Release 2.1，2026-09-26）
|
||
|
||
上面的數字是純 SQL。這一節是真實站台（dev36：HivePress 1.7.31 + Marketplace、WooCommerce 11.1.2、Redis object cache、listing 約 10 萬筆）上的整頁與整條寫入鏈，決策背景見 `adr-004-search-index-mode-for-hp-wc.md`。
|
||
|
||
**前台搜尋**（`tests/live/tools/fe.sh`：每個 URL 5 次取中位數，`X-S36-Ms` = 整個請求到 `template_redirect`）。「routed」是 post 模式 shadow_read + 核心 2.1 router + HivePress AddOn 0.2.0；「1.0.x」是整合測試當時的 router（報告 `notes/tmdo-integration-test-dev36-2026-09-26.md`）。
|
||
|
||
| URL | 原生 ms | 1.0.x ms | 2.1 routed ms | ids 與原生相同 |
|
||
|---|---:|---:|---:|---|
|
||
| listing 價格區間 | 614 | 496 | **323** | 是 |
|
||
| listing 價格區間 + `_sort=price__asc` | 975 | 1,062 | **435** | 是 |
|
||
| listing 時薪區間 + `_sort=hourly_rate__desc` | 850 | — | **367** | 是 |
|
||
| listing `_sort=rating__desc` | 1,697 | 1,720 | **866** | 是 |
|
||
| listing 關鍵字 + 價格區間 | 754 | — | 764 | 是 |
|
||
| vendor 時薪區間 | 102 | — | 97 | 是 |
|
||
| WC 商店價格篩選 / 價格排序 | 94 / 111 | 88–94 | 109 / 88 | 是（WC 用 lookup 表，不經 router） |
|
||
|
||
（R-2.1 驗收，扁平表已從原生回填、含 PR-062c；兩輪量測見 `notes/tmdo-acceptance-2.1-2026-09-26.md`。）
|
||
|
||
- HivePress 搜尋仍帶 tax_query（排除一個 term），WordPress 因此加上 `GROUP BY posts.ID`，排序需要 temporary + filesort；這是目前 311–428 ms 的主要成本，尚未達到 R-2.1 的 300 ms 目標。
|
||
- PR-062b 之前，routed 搜尋因 `posts_request` 改寫而失去 WordPress 的 split query，改撈整列 `posts.*`：同一條 SQL 撈 `*` 要 590 ms，只撈 ID 為 312 ms。
|
||
- PR-062c 之前，依扁平欄位排序的 routed 查詢由 `wp_posts`（type / status 索引，約 56k 列）驅動、逐筆查扁平表再以 temporary table 排序；拿掉 GROUP BY 或只換 tie-break 都無效（約 300 ms），改由扁平表驅動並把 tie-break 改讀 `post_id` 後只剩單表 filesort：`meta_value_num` 263→147 ms、具名排序 250→142 ms、HP 時薪篩選 + 排序 300→36 ms（只計 SQL）。依日期排序的搜尋由扁平表驅動反而變慢（74→137 ms），所以 flat-first 只用在扁平欄位排序。
|
||
- 量測 gotcha：symlink 切換（`plugins/` → `plugins-dev`）後，PHP-FPM 的 opcache（`revalidate_path=0`）仍執行舊目標的程式，WP-CLI 不受影響；前台數字要先確認實際載入的檔案。
|
||
|
||
**WP_Query**（`tests/live/tools/qbench.php`，`fields=ids`，20 筆一頁）：價格區間 296→76 ms、價格 + verified 360→53 ms、rating ≥ 4.5 248→33 ms、rate + `_thumbnail_id` EXISTS 24→9 ms；OR、多 post type、無 type 數值比較等維持原生，結果與原生相同。
|
||
|
||
**寫入鏈開銷**（單一程序，disabled 與 dual_write 交替兩輪取平均）：
|
||
|
||
| 寫入 | disabled | dual_write | 增加 |
|
||
|---|---:|---:|---:|
|
||
| HP listing | 754 ms / 348 q | 976 ms / 434 q | +29% |
|
||
| WC product | 215 ms / 129 q | 489 ms / 206 q | +127% |
|
||
| vendor（user + post） | 444 ms / 162 q | 630 ms / 230 q | +42% |
|
||
|
||
WC product 的成本來自 `wc_product` 群組，Release 2.2 移除 WC 扁平化後消失。
|
||
|
||
## 優化候選（待使用者確認後排入 backlog）
|
||
|
||
| # | 候選 | 針對 | 預期效果 |
|
||
|---|---|---|---|
|
||
| O-1 | Entity 表把 `post_status`、`post_date` 一併存進來，並建 `(hp_status, post_date)` 等複合索引，讓搜尋不必 JOIN 回 `posts` 排序 | 情境 1、2 | 寬範圍搜尋改為走索引排序，避免 filesort |
|
||
| O-2 | Entity 表支援群組層級的複合索引宣告，例如 `(hp_verified, hp_price)`、`(hp_featured, hp_price)` | 情境 2 | 追上或超過 Hot 表 |
|
||
| O-3 | Hot 表的索引規則補上外鍵型 bigint 欄位（例如 `hp_vendor`） | 情境 3 | 3.4 秒降到 1 ms 以下 |
|
||
| O-4 | 提供總數快取，或讓路由後的查詢可以不跑 `SQL_CALC_FOUND_ROWS`（MySQL 8 已棄用） | 情境 1、2 | 省去每次搜尋都要算出全部符合列的成本 |
|
||
| O-5 | 評估 Hot 表與 Entity 表的整併，避免同一份資料存兩份 | 容量、寫入 | 省下約 229 MB 並減少一次寫入 |
|