前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL多层级树形结构表的搜索查询优化

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

作者头像
ianzhi
发布2023-10-19 10:17:48
1.4K0
发布2023-10-19 10:17:48
举报
文章被收录于专栏:LNMP开发那些事

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);

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

本文参与?腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客?前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与?腾讯云自媒体同步曝光计划? ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • MySQL多层级树形结构表的搜索查询优化
  • 一、表结构
  • 二、当前解决方案
    • 1. 查询ID为“5”的节点的所有子级、孙子级中name包含“搜索词”的记录
      • 2. 查询ID为“5”的节点的所有父级
      相关产品与服务
      云数据库 MySQL
      腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
      领券
      问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
      http://www.vxiaotou.com