MySQL多层级树形结构表的搜索查询优化

ianzhi

发布于 2023-10-19 10:17:48

5970

发布于 2023-10-19 10:17:48

文章被收录于专栏：LNMP开发那些事LNMP开发那些事

MySQL多层级树形结构表的搜索查询优化

业务中有思维导图的功能，涉及到大量的树形结构搜索、查询相关的功能，使用场景上查询量远高于增删改操作，记录一下当前的解决方案。

一、表结构

简化的表结构类似

create table nodes (
  id int primary key auto_increment,
  name varchar(255) not null default '' comment '节点名称',
  parent_id int not null default 0 comment '上级节点',

  index nodes_parent_id_index (parent_id),
  index nodes_name_index (name)
);

二、当前解决方案

更新表结构：

-- 添加字段
alter table nodes add column path text not null comment '节点路径';

-- 创建索引
create index nodes_path_index on nodes(path);

-- 更新历史数据
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');

-- 插入更新后执行
update nodes current
left join nodes parent on current.parent_id = parent.id
set path = ifnull(concat(parent.path, ',', current.parent_id), '0');
where current.id = 198;

-- 级联删除
delete from nodes where id = 198;
delete from nodes where (path like '0,5,198,%' and parent_id = 198);

1. 查询ID为“5”的节点的所有子级、孙子级中name包含“搜索词”的记录

更新表后的查询方式：

-- 查询父级节点记录，获取到父级的path
select * from nodes where id = 5;

-- 通过父级path进行模糊查询
select * from nodes where (parent_id = 5 or path like '0,5,%') and name like '%搜索词%';

可以创建一个触发器，在插入、修改数据时，更新子级的path。

2. 查询ID为“5”的节点的所有父级

-- 获取当前节点
select * from nodes where id = 5;

-- 使用当前节点的path查询所有父级
select * from nodes where find_in_set(id, '0,5');

-- 或者也可以使用in
select * from nodes where id in (5);

因为有缓存，所以都尽量使用的简单查询，不使用缓存可以使用子查询。