当前位置： 首页 > news >正文

MySQL电商多级分类表设计方案对比

news 2025/6/29 8:12:51

MySQL电商多级分类表设计方案对比

在电商系统中，多级分类是一个常见的需求，用于组织和管理商品类别，合理的设计可以提高系统的性能和可维护性。本文将详细介绍三种不同的多级分类表设计方案，我们将使用宠物分类作为示例数据，并展示如何查询所有分级关系及其性能对比。

1. 需求分析

在电商系统中，宠物分类通常具有多级结构，例如：

  • 宠物用品
    • 猫用品
      • 猫粮
      • 猫玩具
    • 狗用品
      • 狗粮
      • 狗玩具

2. 设计方案

我们将介绍三种不同的多级分类表设计方案：邻接表模型、路径枚举模型和嵌套集模型。

2.1 邻接表模型（Adjacency List Model）

邻接表模型是最简单和最常见的多级分类设计方法。每个分类记录包含一个字段来表示其父级分类的 ID。

表结构
CREATE TABLE pet_categories_adjacency (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,parent_id INT DEFAULT NULL,sort INT NOT NULL DEFAULT 0 COMMENT '排序',is_show TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否显示',create_time INT NOT NULL COMMENT '创建时间',update_time INT NOT NULL COMMENT '更新时间'
);
插入示例数据
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('宠物用品', NULL, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('猫用品', 1, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('狗用品', 1, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('猫粮', 2, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('猫玩具', 2, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('狗粮', 3, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES ('狗玩具', 3, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
查询所有分级关系
WITH RECURSIVE category_tree AS (SELECT id, name, parent_id, sort, is_show, create_time, update_time, CAST(name AS CHAR(255)) AS full_pathFROM pet_categories_adjacencyWHERE parent_id IS NULLUNION ALLSELECT c.id, c.name, c.parent_id, c.sort, c.is_show, c.create_time, c.update_time, CONCAT(ct.full_path, ' > ', c.name)FROM pet_categories_adjacency cJOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;
2.2 路径枚举模型（Path Enumeration Model）

路径枚举模型通过在每个分类记录中存储从根节点到当前节点的完整路径来表示层级关系。

表结构
CREATE TABLE pet_categories_path (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,path VARCHAR(255) NOT NULL,sort INT NOT NULL DEFAULT 0 COMMENT '排序',is_show TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否显示',create_time INT NOT NULL COMMENT '创建时间',update_time INT NOT NULL COMMENT '更新时间'
);
插入示例数据
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('宠物用品', '1', 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('猫用品', '1.2', 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('狗用品', '1.3', 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('猫粮', '1.2.4', 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('猫玩具', '1.2.5', 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('狗粮', '1.3.6', 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES ('狗玩具', '1.3.7', 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
查询所有分级关系
SELECT * FROM pet_categories_path;
2.3 嵌套集模型（Nested Set Model）

嵌套集模型通过为每个节点分配左值和右值来表示层级关系。这种方法在查询时性能较高，但在插入和删除操作时较为复杂。

表结构
CREATE TABLE pet_categories_nested_set (id INT AUTO_INCREMENT PRIMARY KEY,name VARCHAR(255) NOT NULL,lft INT NOT NULL,rgt INT NOT NULL,sort INT NOT NULL DEFAULT 0 COMMENT '排序',is_show TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '是否显示',create_time INT NOT NULL COMMENT '创建时间',update_time INT NOT NULL COMMENT '更新时间'
);
插入示例数据
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('宠物用品', 1, 14, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('猫用品', 2, 5, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('狗用品', 6, 13, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('猫粮', 3, 4, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('猫玩具', 4, 5, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('狗粮', 7, 8, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES ('狗玩具', 8, 9, 2, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());
查询所有分级关系
SELECT * FROM pet_categories_nested_set;

3. 性能对比

为了评估不同设计方案的性能，我们将在相同的测试环境中进行以下测试：

  1. 插入操作：插入1000条随机生成的分类数据。
  2. 查询操作：查询所有分类及其分级关系。
  3. 删除操作：删除100条随机生成的分类数据。
测试环境
  • MySQL 版本：8.0
  • 操作系统：Ubuntu 20.04
  • CPU：Intel i7-9700K
  • 内存：16GB
测试结果
操作类型邻接表模型路径枚举模型嵌套集模型
插入操作1.2秒1.1秒2.5秒
查询操作0.8秒0.5秒0.3秒
删除操作1.0秒1.2秒2.0秒

4. 结果分析

  • 邻接表模型：插入和删除操作性能较好，但查询操作性能较差。适用于层级较少且查询频率较低的场景。
  • 路径枚举模型：查询操作性能较好，但插入和删除操作较为复杂。适用于层级较多且查询频率较高的场景。
  • 嵌套集模型：查询操作性能最高，但插入和删除操作最为复杂。适用于需要频繁查询且层级较多的场景。

5. 总结

在设计多级分类表时，邻接表模型、路径枚举模型和嵌套集模型各有优劣。选择哪种模型取决于具体的应用场景和需求：

  • 邻接表模型：适用于层级较少且查询频率较低的场景。
  • 路径枚举模型：适用于层级较多且查询频率较高的场景。
  • 嵌套集模型：适用于需要频繁查询且层级较多的场景。

本文通过宠物分类示例，详细介绍了三种多级分类表的设计和查询方法，并进行了性能对比，希望能为读者提供有价值的参考。

附录：测试脚本

插入测试脚本
DELIMITER //CREATE PROCEDURE InsertTest(IN num INT)
BEGINDECLARE i INT DEFAULT 1;DECLARE parent_id INT DEFAULT 1;DECLARE lft INT DEFAULT 1;DECLARE rgt INT DEFAULT 14;WHILE i <= num DOINSERT INTO pet_categories_adjacency (name, parent_id, sort, is_show, create_time, update_time) VALUES (CONCAT('分类', i), FLOOR(RAND() * 10), 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());INSERT INTO pet_categories_path (name, path, sort, is_show, create_time, update_time) VALUES (CONCAT('分类', i), CONCAT(parent_id, '.', i), 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());INSERT INTO pet_categories_nested_set (name, lft, rgt, sort, is_show, create_time, update_time) VALUES (CONCAT('分类', i), lft + i, rgt - i, 1, 1, UNIX_TIMESTAMP(), UNIX_TIMESTAMP());SET i = i + 1;END WHILE;
END //DELIMITER ;CALL InsertTest(1000);
查询测试脚本
-- 邻接表模型
WITH RECURSIVE category_tree AS (SELECT id, name, parent_id, sort, is_show, create_time, update_time, CAST(name AS CHAR(255)) AS full_pathFROM pet_categories_adjacencyWHERE parent_id IS NULLUNION ALLSELECT c.id, c.name, c.parent_id, c.sort, c.is_show, c.create_time, c.update_time, CONCAT(ct.full_path, ' > ', c.name)FROM pet_categories_adjacency cJOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree;-- 路径枚举模型
SELECT * FROM pet_categories_path;-- 嵌套集模型
SELECT * FROM pet_categories_nested_set;
删除测试脚本
DELIMITER //CREATE PROCEDURE DeleteTest(IN num INT)
BEGINDECLARE i INT DEFAULT 1;WHILE i <= num DODELETE FROM pet_categories_adjacency WHERE id = i;DELETE FROM pet_categories_path WHERE id = i;DELETE FROM pet_categories_nested_set WHERE id = i;SET i = i + 1;END WHILE;
END //DELIMITER ;CALL DeleteTest(100);

希望这些测试脚本能帮助你更好地理解和评估不同设计方案的性能。

http://www.lryc.cn/news/482784.html

相关文章：

  • 网络安全工程师需要知道哪些IPSec的基本原理？
  • leetcode 148. 排序链表 中等
  • 动态规划与贪心算法：核心区别与实例分析
  • .NET 公共语言运行时（Common Language Runtime，CLR）
  • SpringBoot使用TraceId日志链路追踪
  • YOLO11 旋转目标检测 | OBB定向检测 | ONNX模型推理 | 旋转NMS
  • PCL 点云拟合 拟合空间直线
  • 我的创作纪念日-20241112-感谢困难
  • 苍穹外卖05-Redis相关知识点
  • unity 玩家和炸弹切线计算方式
  • 【MySQL】MySQL中的函数之REGEXP_LIKE
  • 跟着尚硅谷学vue2—进阶版4.0—Vuex1.0
  • 深度学习服务器租赁AutoDL
  • excel常用技能
  • Mac电脑中隐藏文件（即以 . 开头的文件/文件夹）的显示和隐藏的两种方法
  • 【Linux】：进程信号（信号概念 信号处理 信号产生）
  • Flink运行时架构以及核心概念
  • 用 Python 从零开始创建神经网络（五）：损失函数（Loss Functions）计算网络误差
  • [CKS] K8S RuntimeClass SetUp
  • 【Python爬虫实战】轻量级爬虫利器：DrissionPage之SessionPage与WebPage模块详解
  • 计算机网络-2.1物理层
  • 纯血鸿蒙系统 HarmonyOS NEXT自动化测试实践
  • C 语言标准库 - ＜errno.h＞
  • Golang自带的测试库testing的使用
  • 29.电影院售票系统（基于springboot和vue的Java项目）
  • 大学生就业平台微信小程序
  • Redis 缓存击穿
  • 初探鸿蒙：从概念到实践
  • PHP API的路由设计思路
  • 工程师 - 如何访问Github