MySQL树形表查询优化

背景

在项目中可能会有这么一个场景

一个代理可以有很多子代理，然后子代理又有子代理，业务要求父代理能看到所有子代理的数据
层级数量不做控制，理论上可以有无限的层级（这里不去考虑法律法规限制）

• 如何查询出一个父代理名下所有的子代理列表
• 如何统计出一个父代理名下所有子代理的日订单数据

这是一个典型的树形结构，结合上述两个问题，让我们先来建立一张代理商表 与 订单表

DROP TABLE IF EXISTS `group`;
CREATE TABLE `group`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_name` varchar(70) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '代理商名称',
  `parent_id` bigint NOT NULL DEFAULT 0 COMMENT '上级代理id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `oss_group_parent_id_index`(`parent_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COMMENT = '代理商表' ROW_FORMAT = DYNAMIC;

DROP TABLE IF EXISTS `order`;
CREATE TABLE `order`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `order_no` varchar(32)  NOT NULL COMMENT '订单号',
  `agent_id` bigint NULL DEFAULT 1 COMMENT '代理商Id',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_agent_id`(`agent_id` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COMMENT = '订单表' ROW_FORMAT = DYNAMIC;

实战开始

查询某代理名下所有子代理

解决方案

• 因为前面有提到业务代理可以有无限的层级，那么就不能用 inner join 的硬编码方式来查询

这种硬编码方式极不推荐，若后续业务变更将会非常的痛苦，不给自己留下技术债

• 那么在我们java代码中是否能通过递归的方式循环查询MySQL，从而将代理商整个层级结构捞出

此方式是可以实现的，但是性能较差，也不是很推荐

• 思考MySQL是否有提供 递归函数 来处理此场景呢？

# 在MySQL8.0，引入了 with recursive，下面我们来看看相关语法
with recursive temp as (
select * from group p where id= 2
 union all
select t.* from group t inner join temp on temp.id = t.parent_id
)
select id,group_name from temp order by temp.id

但这种方式也有个弊端，那就是有的项目上还没用上MySQL8.0，那该如何处理呢？请接着看第四个通用方案

• 为 group 表新增一个 id_path 字段，用来记录当前代理商的层级关系

# 举例：现有代理ID 1、2，他们之间关系为：2是1的下级
# 那么代理商ID为1的 id_path 为0.   代理商ID为2的 id_path 为0.1.（注：id_path 也可将自身id也拼接上）
# 最新表结构如下
DROP TABLE IF EXISTS `group`;
CREATE TABLE `group`  (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `group_name` varchar(70) CHARACTER SET utf8mb4 NOT NULL DEFAULT '' COMMENT '代理商名称',
  `parent_id` bigint NOT NULL DEFAULT 0 COMMENT '上级代理id',
  `id_path` varchar(190) CHARACTER SET utf8mb4 NULL DEFAULT '0.' COMMENT '从当前节点到根节点的id路径',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `oss_group_parent_id_index`(`parent_id` ASC) USING BTREE,
  INDEX `idx_oss_group_id_path`(`id_path` ASC) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COMMENT = '代理商表' ROW_FORMAT = DYNAMIC;

# 查询代理商ID 1 名下所有子代理
select * from oss_group where id_path like '0.1.%';

看到这里，第一个问题大家没有什么疑问了，那我们继续看第二个问题

统计某代理名下所有子代理的交易笔数

背景：group表五千多，order表千万出头，现需要查一位名下有三百多代理在某一个月的总订单数

解决方案

• 采用 IN (subGroupList)

这种方案挺常见，在数据量少的时候可以采用，但是量一旦大了以后呢，性能变差 且 通过Mybatis拼接完SQL以后可能会超长（而通过多线程分批后聚合编码又较为复杂）

SELECT
    count( id ) orderTotal     
FROM
    order
WHERE
    agent_id IN ( #{三百多个ID} )
    AND pay_rev_time BETWEEN '2024-03-01 00:00:00' 
    AND '2024-03-31 23:59:59' ;
    
# 最终执行耗时：23.9s  24.5s  24.6s

• 采用MySQL8.0提供的 with recursive 递归查询

这种方式本质上还是通过 agent_id in 一大堆，优点：可以避免SQL超长从而只能分批查询

WITH recursive temp AS (
    SELECT
    id 
    FROM
    oss_group p 
    WHERE
    id = #{根ID} UNION ALL
    SELECT
    t.id 
    FROM
    oss_group t
    INNER JOIN temp ON temp.id = t.parent_id 
    ) SELECT
    count( id ) orderTotal 
FROM
    order_rent
WHERE
    agent_id IN ( SELECT id FROM temp ORDER BY temp.id )
    AND pay_rev_time BETWEEN '2024-03-01 00:00:00' 
    AND '2024-03-31 23:59:59' ;
    
# 最终执行耗时：22.6s  22.9s  22.8s

• 采用 id_path 查询

SELECT
    count( t.id ) orderTotal
FROM
    order_rent t
    INNER JOIN oss_group gr ON t.agent_id = gr.id 
WHERE
    ( gr.id_path LIKE concat('根代理id_path','%') ) 
    AND t.pay_rev_time BETWEEN '2024-03-01 00:00:00' 
    AND '2024-03-31 23:59:59' 

# 最终执行耗时：19.9s  19.76s  19.86s

为了让测试结果凸出差异化 并且 保证公正性，故在测试中以上SQL都走的不是最佳索引（日报表定时任务优化历程），并且在每次SQL执行都间隔一段时间 且 执行了多次其他全表扫描的SQL来刷缓存

结语

根据上述两个问题引出了几种解决方案，我个人比较推崇的是：id_path 方案来处理两个问题。
因为其他方案本质都是通过 IN 来查询数据，虽然还可以进一步对 IN 方案进行优化(例如：业务上多线程分批IN，EXISTS等)，但还是略逊一筹

参考文章

https://blog.csdn.net/weixin_57535055/article/details/132783354