MySQL多层级树形结构表的搜索查询优化
业务中有思维导图的功能，涉及到大量的树形结构搜索、查询相关的功能，使用场景上查询量远高于增删改操作，记录一下当前的解决方案。
一、表结构
简化的表结构类似
代码语言：javascript复制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)
);二、当前解决方案
更新表结构：
代码语言：javascript复制-- 添加字段
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包含“搜索词”的记录
更新表后的查询方式：
代码语言：javascript复制-- 查询父级节点记录，获取到父级的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”的节点的所有父级
代码语言：javascript复制-- 获取当前节点
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);因为有缓存，所以都尽量使用的简单查询，不使用缓存可以使用子查询。
- MySQL多层级树形结构表的搜索查询优化
- 使用WordPress作为小程序后端——APPID有效性前置检查
- 使用WordPress作为小程序后端——小程序请求前置检查
- Windows rclone挂载sftp
- 迁移——从Electron迁移到Eclipse Theia
- 使用typescript开发chrome扩展
- use multiple simple queries or a join
- php: /usr/local/lib/libcurl.so.4: no version information available (required by php)
- how to improve the rank of search results in google
- SEO导航
