本文目的为对比mysql递归树两种查询方式效率。
工具/原料
-
--创建表
DROP TABLE IF EXISTS `t_areainfo`;
CREATE TABLE `t_areainfo` (
`id` int(11) NOT '0' AUTO_INCREMENT,
`level` int(11) DEFAULT '0',
`name` varchar(255) DEFAULT '0',
`parentId` int(11) DEFAULT '0',
`status` int(11) DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65 DEFAULT CHARSET=utf8; -
--初始数据
INSERT INTO `t_areainfo` VALUES ('1', '0', '中国', '0', '0');
INSERT INTO `t_areainfo` VALUES ('2', '0', '华北区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('3', '0', '华南区', '1', '0');
INSERT INTO `t_areainfo` VALUES ('4', '0', '北京', '2', '0');
INSERT INTO `t_areainfo` VALUES ('5', '0', '海淀区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('6', '0', '丰台区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('7', '0', '朝阳区', '4', '0');
INSERT INTO `t_areainfo` VALUES ('8', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('9', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('10', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('11', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('12', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('13', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('14', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('15', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('16', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('17', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('18', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('19', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('20', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('21', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('22', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('23', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('24', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('25', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('26', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('27', '0', '北京XX区1', '4', '0');
INSERT INTO `t_areainfo` VALUES ('28', '0', '北京XX区2', '4', '0');
INSERT INTO `t_areainfo` VALUES ('29', '0', '北京XX区3', '4', '0');
INSERT INTO `t_areainfo` VALUES ('30', '0', '北京XX区4', '4', '0');
INSERT INTO `t_areainfo` VALUES ('31', '0', '北京XX区5', '4', '0');
INSERT INTO `t_areainfo` VALUES ('32', '0', '北京XX区6', '4', '0');
INSERT INTO `t_areainfo` VALUES ('33', '0', '北京XX区7', '4', '0');
INSERT INTO `t_areainfo` VALUES ('34', '0', '北京XX区8', '4', '0');
INSERT INTO `t_areainfo` VALUES ('35', '0', '北京XX区9', '4', '0');
INSERT INTO `t_areainfo` VALUES ('36', '0', '北京XX区10', '4', '0');
INSERT INTO `t_areainfo` VALUES ('37', '0', '北京XX区11', '4', '0');
INSERT INTO `t_areainfo` VALUES ('38', '0', '北京XX区12', '4', '0');
INSERT INTO `t_areainfo` VALUES ('39', '0', '北京XX区13', '4', '0');
INSERT INTO `t_areainfo` VALUES ('40', '0', '北京XX区14', '4', '0');
INSERT INTO `t_areainfo` VALUES ('41', '0', '北京XX区15', '4', '0');
INSERT INTO `t_areainfo` VALUES ('42', '0', '北京XX区16', '4', '0');
INSERT INTO `t_areainfo` VALUES ('43', '0', '北京XX区17', '4', '0');
INSERT INTO `t_areainfo` VALUES ('44', '0', '北京XX区18', '4', '0');
INSERT INTO `t_areainfo` VALUES ('45', '0', '北京XX区19', '4', '0');
INSERT INTO `t_areainfo` VALUES ('46', '0', 'xx省1', '1', '0');
INSERT INTO `t_areainfo` VALUES ('47', '0', 'xx省2', '1', '0');
INSERT INTO `t_areainfo` VALUES ('48', '0', 'xx省3', '1', '0');
INSERT INTO `t_areainfo` VALUES ('49', '0', 'xx省4', '1', '0');
INSERT INTO `t_areainfo` VALUES ('50', '0', 'xx省5', '1', '0');
INSERT INTO `t_areainfo` VALUES ('51', '0', 'xx省6', '1', '0');
INSERT INTO `t_areainfo` VALUES ('52', '0', 'xx省7', '1', '0');
INSERT INTO `t_areainfo` VALUES ('53', '0', 'xx省8', '1', '0');
INSERT INTO `t_areainfo` VALUES ('54', '0', 'xx省9', '1', '0');
INSERT INTO `t_areainfo` VALUES ('55', '0', 'xx省10', '1', '0');
INSERT INTO `t_areainfo` VALUES ('56', '0', 'xx省11', '1', '0');
INSERT INTO `t_areainfo` VALUES ('57', '0', 'xx省12', '1', '0');
INSERT INTO `t_areainfo` VALUES ('58', '0', 'xx省13', '1', '0');
INSERT INTO `t_areainfo` VALUES ('59', '0', 'xx省14', '1', '0');
INSERT INTO `t_areainfo` VALUES ('60', '0', 'xx省15', '1', '0');
INSERT INTO `t_areainfo` VALUES ('61', '0', 'xx省16', '1', '0');
INSERT INTO `t_areainfo` VALUES ('62', '0', 'xx省17', '1', '0');
INSERT INTO `t_areainfo` VALUES ('63', '0', 'xx省18', '1', '0');
INSERT INTO `t_areainfo` VALUES ('64', '0', 'xx省19', '1', '0');
方式一：采用function获取所有子节点的id
-
--查询传入areaId及其以下所有子节点
DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
CREATE FUNCTION `queryChildrenAreaInfo` (areaId INT)
RETURNS VARCHAR(4000)
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '$';
SET sTempChd = cast(areaId as char);
WHILE sTempChd is not NULL DO
SET sTemp = CONCAT(sTemp,',',sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId,sTempChd)>0;
END WHILE;
return sTemp;
END;
-
--调用方式
select queryChildrenAreaInfo(1);
select * from t_areainfo where FIND_IN_SET(id, queryChildrenAreaInfo(1));
END
方式二：采用临时表和存储过程完成
-
-- 创建存储过程
drop PROCEDURE showChildList;
CREATE PROCEDURE showChildList (IN rootId INT)
BEGIN
CREATE TEMPORARY TABLE
IF NOT EXISTS tmpList (
sno INT PRIMARY KEY auto_increment,
id INT,
depth INT
);
DELETE FROM tmpList;
CALL createChildList (rootId, 0);
SELECT tmpList.*, t_areainfo.* FROM tmpList, t_areainfo
WHERE
tmpList.id = t_areainfo.id
ORDER BY
tmpList.sno;
END;
drop PROCEDURE createChildList;
CREATE PROCEDURE createChildList (IN rootId INT, IN nDepth INT)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE b INT;
DECLARE cur1 CURSOR FOR SELECT id FROM t_areainfo WHERE parentId = rootId;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;
INSERT INTO tmpList VALUES (NULL, rootId, nDepth);
OPEN cur1;
FETCH cur1 INTO b;
WHILE done = 0 DO
CALL createChildList (b, nDepth + 1);
FETCH cur1 INTO b;
END WHILE;
CLOSE cur1;
END;
-
-- 调用方式
call showChildList(1);
END
两种方式对比：
-
--简易程度
首先我们可以通过sql语句就可以看的出，方式二的代码量差不多是方式一的两倍，而且又是临时表又是游标的，极易出错。
--效率对比
可以通过图片可以看到，同样的查询结果，方式一仅仅需要0.044s既可以完成查询，而方式二则需要1.525s,效率远远低于方式一。
END
注意事项
执行方式二是系统出报错，错误原因是因为没有指定控制递归调用层数上线，可以通过利用系统参数 max_sp_recursion_depth 来控制递归调用的层数上限。
相关推荐
### 两种MySQL递归Tree查询效率分析 #### 一、背景与目的 在数据库操作中，经常需要处理具有层级结构的数据。例如，在处理组织结构、文件系统或是地区划分时，通常会采用递归的方式来查询这些层级关系。MySQL作为...
MySQL 递归查询实现方法 MySQL 递归查询是指在 MySQL 中实现类似 Oracle Hierarchical Queries 的功能，用于查询...这些方法可以用于实现 MySQL 递归查询，每种方法都有其优缺点，选择哪种方法取决于具体的应用场景。
从MySQL 8.0开始，引入了`WITH RECURSIVE`子句，这是一种标准的SQL语法，专门用于执行递归查询。它允许在单个查询中定义一个临时的、可递归的表，这极大地简化了处理层次结构数据的过程。 ```sql WITH RECURSIVE ...
通过上述分析，我们可以看出MySQL递归查询结合Java代码能够高效地构建出树形结构。这种方法不仅适用于MySQL，对于其他支持递归查询的数据库也同样适用。在实际项目开发中，合理利用递归查询能够极大地简化对层次结构...
这里，我们将深入探讨如何使用递归查询来构建菜单树，并特别关注在MySQL和Oracle这两种广泛使用的数据库系统中的实现。 首先，我们要理解什么是递归查询。递归查询是一种在数据库中处理层次数据的方法，它通过自身...
在提供的`mysql递归调用获取树节点（子树）.doc`文档中，应该详细解释了这个过程，包括如何创建和执行存储过程，以及如何使用提供的测试数据。确保查看文档以获取完整的步骤和示例，以便在实际项目中成功应用这些...
本文将深入探讨如何通过递归查询来解决这类问题，并着重讲解使用`WITH`语句来实现递归查询的方法，适用于多种数据库系统，如MySQL、PostgreSQL、SQL Server等。 一、理解递归查询 递归查询是一种在数据库中遍历层级...
在 MySQL 中，实现递归层次查询（父子查询）是一种复杂的操作，特别是当我们需要查询某个节点下的所有节点或节点上的所有父节点时。 Oracle 中有 Hierarchical Queries 可以通过 CONNECT BY 来查询，但是，在 MySQL ...
在“ssm+mysql+easyui-tree”项目中，MyBatis可能会用于与MySQL数据库交互，执行查询省市区数据的SQL语句，如获取所有省份、根据省份获取城市，以及根据城市获取区县的SQL。 EasyUI是一套基于jQuery的UI库，提供了...
在MySQL中，构建和操作树形结构数据是一个常见的需求，特别是在...这种方法虽然效率可能不如其他更高级的数据结构（如NL-Tree或Closure Table），但对于小型数据集和简单查询，它提供了一种直观且易于理解的解决方案。
这里我们将探讨如何通过一条SQL语句来实现MySQL的递归查询。 首先，我们需要一个包含层级关系的表格，例如名为`treenodes`的表格，包含字段`id`（节点ID）、`nodename`（节点名称）和`pid`（父节点ID）。为了演示...
在IT行业中，动态树结构（Dynamic Tree）是一种常见的数据展示方式，尤其在Web应用中用于组织和导航数据。本示例“jsp+mysql+servlet动态tree生成简单Demo”结合了Java Web开发中的三大核心技术：JSP（JavaServer ...
本篇文章将详细讲解如何利用Java和MySQL递归地实现拼接树形JSON列表的方法。 首先，我们需要理解问题的整体思路。在数据库中，我们可以将每个分类（或节点）存储为一个记录，包含ID、父ID（PID）以及名称等字段。...
在MySQL中，构建和操作树形结构数据是一个常见的需求，特别是在组织层次结构、菜单...在实际应用中，可以考虑使用预计算的连接表或更高效的算法，如 nested set model 或 Adjacency List Model 来优化树的查询效率。
MySQL8的递归查询是通过Common Table Expressions (CTE)实现的，CTE是一种临时结果集，可以被后续的查询引用。递归查询在处理层次结构数据时特别有用，如树形结构。在MySQL 8.0版本以后，引入了递归查询的语法，使得...
因此在这里采用类似递归的方法对菜单的所有子节点进行查询。 准备 创建menu表： CREATE TABLE `menu` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '菜单id', `parent_id` int(11) DEFAULT NULL COMMENT '父...
这种方法虽然直观，但在处理大型树结构时可能效率较低，因为每次递归都需要进行数据库查询。在实际应用中，可以考虑使用其他更高效的方法，如预计算的路径编码、自连接查询等。 总的来说，这个例子展示了如何在...
本文将深入探讨如何在MySQL中查询树形结构的全部子项列表，结合提供的`MySql_Link_Function.sql`文件，我们将探讨一种有效的方法来实现这一功能。 首先，树形结构在数据库中的存储通常采用自引用的方式，即每个节点...
在处理这类数据时，递归是一种非常有效的方法。本篇文章将详细介绍如何在Java中使用递归来实现从父节点获取树的所有子节点。 首先，我们需要定义一个树节点类，通常包含两个属性：节点值和子节点列表。例如： ```...
【MySQL与JsTree结合创建树形视图】 在IT领域，构建用户友好的Web界面时，树形结构常用于展示层级关系的数据，如文件系统、组织结构等。本项目利用MySQL数据库存储数据，并通过ASP.NET的C#语言，将数据转化为Json...