萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 多級分類儲存方案對決:遞歸遞歸 vs 左右值(Nested Set)

方案一:遞歸遞歸模型

遞歸遞歸表結構

CREATE TABLE category (
  category_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  parent_id INT DEFAULT 0,
  KEY idx_parent (parent_id)
);

查詢子樹(已知層級上限 4 層)

SELECT t1.name AS lev1, t2.name AS lev2, t3.name AS lev3, t4.name AS lev4
FROM category t1
LEFT JOIN category t2 ON t2.parent_id = t1.category_id
LEFT JOIN category t3 ON t3.parent_id = t2.category_id
LEFT JOIN category t4 ON t4.parent_id = t3.category_id
WHERE t1.name = 'ELECTRONICS';

缺點:層級不固定時需遞歸查詢或應用層遞歸,深度大時效能差;查詢「某頂級分類下所有商品」需多次 JOIN 或 UNION。

方案二:左右值模型

左右值示意

表結構

CREATE TABLE category (
  category_id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  lft INT NOT NULL,
  rgt INT NOT NULL,
  parent_id INT DEFAULT 0,  -- 保留父節點便於向上遍歷
  KEY idx_lft_rgt (lft, rgt)
);

左右值計算規則(深度優先遍歷編號)

編號規則

  • 根節點 lft=1, rgt=2*N(N 為總節點數)。
  • 子節點區間嚴格包含於父節點區間內。
  • 葉節點 rgt = lft + 1。

高效查詢範例

-- 取得節點 5 的所有子孫(含自身)
SELECT * FROM category WHERE lft > 5 AND rgt < 20;

-- 取得節點 5 的所有祖先
SELECT * FROM category WHERE lft < 5 AND rgt > 20 ORDER BY lft;

-- 統計節點 5 直系子節點數
SELECT COUNT(*) FROM category WHERE parent_id = 5;

兩方案對比總結

維度遞歸遞歸左右值
結構簡單度⭐⭐⭐⭐⭐⭐⭐⭐
查詢子樹效能⭐⭐(深度大時差)⭐⭐⭐⭐⭐(單次範圍查詢)
插入/移動節點⭐⭐⭐⭐⭐(只改 parent_id)⭐(需重算所有後續節點 lft/rgt)
刪除節點⭐⭐⭐⭐⭐(同插入,需重算)
適用場景分類樹較穩定、深度不固定讀多寫少、固定深度、需高效子樹查詢

實務建議

  1. 讀多寫少、層級固定:左右值模型(如電商三級分類)。
  2. 頻繁調整分類樹:遞歸遞歸 + 應用層快取全樹 JSON,或引入 path 欄位(物化路徑 1/5/12/)支援 LIKE '1/5/%' 查詢。
  3. MySQL 8.0+:支援 WITH RECURSIVE CTE,遞歸遞歸查詢單條 SQL 解決,無需左右值。
copyright © 萬盛學電腦網 all rights reserved