树形结构表设计的思考

前言

我们在实际设计树形表设计中，经常会遇到上级名称拼接下级名称共同构成一个完整的名称这种情况。知道下级id，获取从上级开始到该id的完整链路。这个需要我们去思考这种树形结构如何去设计。如果设计比较好的情况下能够减少，查询数据库的次数和从数据库中抓取的数据量。

表的设计

我们这里用行政空间的表设计来进行举例

DROP TABLE IF EXISTS `sys_region`;

CREATE TABLE `sys_region` (
  `id` bigint(20) NOT NULL DEFAULT '0' COMMENT '编号',
  `pid` bigint(20) NOT NULL DEFAULT '0' COMMENT '父id',
  `level` tinyint(4) NOT NULL DEFAULT '0' COMMENT '级别',
  `name` varchar(30) NOT NULL DEFAULT '' COMMENT '名称',
  `region_code` varchar(10) NOT NULL DEFAULT '' COMMENT '区号',
  `tree_path` varchar(100) NOT NULL DEFAULT '' COMMENT '树结构',
  `full_name` varchar(100) NOT NULL DEFAULT '' COMMENT '全称',
  `short_name` varchar(100) NOT NULL DEFAULT '' COMMENT '短称',
  `first_letter` varchar(5) NOT NULL DEFAULT '' COMMENT '首字母',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `FK_30j67517pognnjqm3fkebeyyx` (`pid`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='地区';

插入部分数据

insert  into `sys_region`(`id`,`pid`,`level`,`name`,`region_code`,`tree_path`,`full_name`,`short_name`,`first_letter`) values (110000,0,1,'北京','',',','北京','北京','B'),(110100,110000,2,'北京市','010',',110000,','北京北京市','北京',''),(110101,110100,3,'东城区','010',',110000,110100,','北京北京市东城区','东城',''),(110102,110100,3,'西城区','010',',110000,110100,','北京北京市西城区','西城',''),(110105,110100,3,'朝阳区','010',',110000,110100,','北京北京市朝阳区','朝阳',''),(110106,110100,3,'丰台区','010',',110000,110100,','北京北京市丰台区','丰台',''),(110107,110100,3,'石景山区','010',',110000,110100,','北京北京市石景山区','石景山',''),(110108,110100,3,'海淀区','010',',110000,110100,','北京北京市海淀区','海淀',''),(110109,110100,3,'门头沟区','010',',110000,110100,','北京北京市门头沟区','门头沟',''),(110111,110100,3,'房山区','010',',110000,110100,','北京北京市房山区','房山',''),(110112,110100,3,'通州区','010',',110000,110100,','北京北京市通州区','通州',''),(110113,110100,3,'顺义区','010',',110000,110100,','北京北京市顺义区','顺义',''),(110114,110100,3,'昌平区','010',',110000,110100,','北京北京市昌平区','昌平',''),(110115,110100,3,'大兴区','010',',110000,110100,','北京北京市大兴区','大兴',''),(110116,110100,3,'怀柔区','010',',110000,110100,','北京北京市怀柔区','怀柔',''),(110117,110100,3,'平谷区','010',',110000,110100,','北京北京市平谷区','平谷',''),(110228,110100,3,'密云县','010',',110000,110100,','北京北京市密云县','密云',''),(110229,110100,3,'延庆县','010',',110000,110100,','北京北京市延庆县','延庆','')；

展示部分数据

我们在这里重新写一下我们需求：

需求：
我们想获取 完整名字 比如 北京市 东城区
我们会根据 1101111这个编码获取完整名 北京 北京市 房山区
我们可能会经常修改 name的值

基于上面需要我们的第一的方案是：

我们设置列id,pid,name 每次获取完整名称的时候，递归查询子节点
这种要么查询数据的次数比较多，要么一次全部查询所有数据出来涉及过高的io成本。

基于上面需要我们的第二的方案是：
我们设置列id,pid,name，totalName （存储完整上下级名称） 每次获取完整名称的时候，这个时候直接将数据查询出来。但我们在更改部分名称的时候，基本要改很多列，由于我们这个改动可能会稍微比较频繁，这样的成本还是比较高。

基于上面需要我们的第三种方案是：
我们设置列id,pid,name，treePath （存储下级id）每次获取完整名称的时候，我们获取该列。根据treePath 中的id获取上级所有列，这样减少对数据的查询次数和数量的io成本。修改名称的时候，我们也只用修改该列。另外这个我们还可以通过快速查询该链路下面所有子节点。通过like (,id,)