MySQL 無限級分類三大實現方案深度解析與優化

無限級分類是電商、CMS 等系統的核心需求。本文系統梳理遞歸法、路徑枚舉法、修改前序遍歷樹三大主流方案，從數據結構、查詢性能、寫入維護三個維度對比優劣，並給出選型建議。

方案一：遞歸算法（鄰接表模型）

表結構

CREATE TABLE category (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fid INT NOT NULL COMMENT '父級ID',
  title VARCHAR(50)
);

特點分析

• 優點：結構簡單，增刪改極其方便，符合範式設計
• 缺點：讀取需多次遞歸查詢，N+1 問題嚴重；超過 3-4 層性能急劇下降
• 適用場景：層級固定較淺（≤4 級）、讀寫比平衡的業務

方案二：路徑枚舉法（物化路徑）

表結構

CREATE TABLE category (
  id INT PRIMARY KEY AUTO_INCREMENT,
  path VARCHAR(255) COMMENT '如 1,3,6 表示所有祖先ID',
  title VARCHAR(50)
);

特點分析

• 優點：單次查詢可獲取完整路徑，LIKE '1,3%' 快速獲取子樹
• 缺點：字段長度限制深度；移動節點需批量更新所有後代 path，寫入開銷大；不符合第一範式
• 適用場景：樹結構相對穩定、讀多寫少、層級不超過 20 的業務

方案三：修改前序遍歷樹——推薦方案

核心原理

為每個節點分配 lft（左值）和 rgt（右值），通過區間包含關係表示樹結構：

• 祖先節點：lft < 目標.lft AND rgt > 目標.rgt
• 後代節點：lft > 目標.lft AND rgt < 目標.rgt
• 子樹範圍：lft BETWEEN 目標.lft AND 目標.rgt

表結構設計

CREATE TABLE category (
  id INT PRIMARY KEY AUTO_INCREMENT,
  type INT COMMENT '1文章 2產品 3下載',
  title VARCHAR(50),
  lft INT NOT NULL,
  rgt INT NOT NULL,
  lorder INT COMMENT '同級排序',
  create_time INT
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

關鍵操作實現

1. 一次性查詢完整樹結構

SELECT id, type, title, rgt 
FROM category 
WHERE lft >= :root_lft AND lft <= :root_rgt 
ORDER BY lft;

配合 PHP 陣列堆疊邏輯，單次查詢即可構建帶縮進的完整分類樹，無需遞歸。

2. 節點插入（存儲過程）

CREATE PROCEDURE category_insert_by_parent(
  IN pid INT, IN title VARCHAR(20), IN type INT, 
  IN l_order INT, IN pubtime INT
)
BEGIN
  DECLARE myLeft INT;
  SELECT lft INTO myLeft FROM category WHERE id = pid;
  UPDATE category SET rgt = rgt + 2 WHERE rgt > myLeft;
  UPDATE category SET lft = lft + 2 WHERE lft > myLeft;
  INSERT INTO category(type, title, lft, rgt, lorder, create_time)
  VALUES(type, title, myLeft + 1, myLeft + 2, l_order, pubtime);
  COMMIT;
END

3. 節點刪除（存儲過程）

CREATE PROCEDURE category_delete_by_key(IN id INT)
BEGIN
  SELECT @myLeft := lft, @myRight := rgt, @myWidth := rgt - lft + 1
  FROM category WHERE id = id;
  DELETE FROM category WHERE lft BETWEEN @myLeft AND @myRight;
  UPDATE category SET rgt = rgt - @myWidth WHERE rgt > @myRight;
  UPDATE category SET lft = lft - @myWidth WHERE lft > @myRight;
END

4. 節點移動

採用「先刪除後插入」策略，復用上述兩個存儲過程，邏輯清晰且不易出錯。

三大方案綜合對比

選型建議

1. 讀多寫少、層級深、需高性能查詢 → 首選修改前序遍歷樹
2. 寫入頻繁、層級淺（≤4 級）、團隊對遞歸熟悉 → 遞歸法
3. 需快速獲取麵包屑導航、樹結構相對固定 → 路徑枚舉法

實踐經驗：分類業務通常「讀多寫少」，修改前序遍歷樹雖然寫入較繁瑣，但通過存儲過程封裝後，維護成本可控，查詢性能優勢壓倒性，是大多數中大型項目的最優選擇。