首页 > 试题广场 >

微服务架构下的深层依赖链路漏洞影响面分析

[编程题]微服务架构下的深层依赖链路漏洞影响面分析
  • 热度指数：408 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解


背景：
在现代大型互联网公司的微服务架构中，服务之间的调用关系错综复杂，往往会形成庞大的调用网（有向图）。近期安全团队发现核心的“支付网关（Payment_Gateway）”服务存在一个底层安全漏洞。为了评估漏洞的影响面，我们需要查出所有直接或间接调用过该服务的下游微服务。为了排除历史废弃链路的干扰，我们仅关注在2025年内新建立的活跃依赖关系。

表结构和字段说明：

  • 表名：services（微服务信息表）
    • service_id：整数类型，微服务的唯一标识ID，主键
    • service_name：字符串类型，微服务的英文名称
    • owner_team：字符串类型，微服务所属的研发团队名称
  • 表名：service_dependencies（微服务依赖关系表）
    • caller_service_id：整数类型，主调服务ID（即发起调用的服务/依赖方）
    • callee_service_id：整数类型，被调服务ID（即接收调用的服务/被依赖方）
    • first_call_date：日期类型，这两项服务首次建立调用关系并产生流量的日期

3. 问题

请编写一条 MySQL 查询语句，查询所有直接或间接依赖于服务名为'Payment_Gateway'的微服务，仅统计依赖建立日期（first_call_date）在2025年（包含2025全年）的调用关系。

要求返回以下字段：

  • service_id：主调服务ID
  • service_name：主调服务名称
  • dependency_depth：依赖深度（直接调用Payment_Gateway的深度为1，间接调用依次递增）
  • dependency_path：依赖路径（格式为Payment_Gateway->中间服务名->...->主调服务名）

排序规则：

结果须按依赖深度（dependency_depth）升序排列；若深度相同，则按主调服务ID（service_id）升序排列；若主调服务ID也相同（说明该服务通过多条不同路径依赖了目标服务），则按依赖路径（dependency_path）的字典序升序排列。

4. 示例数据表

services（微服务信息表）

service_id service_name owner_team
100 Payment_Gateway Core_Pay
101 Order_Service Trade_Biz
102 User_Wallet_Service Asset_Biz
103 E_Commerce_BFF Frontend_Arch
104 Analytics_Job Data_Tech
105 Legacy_Report Data_Tech

service_dependencies（微服务依赖关系表）

caller_service_id callee_service_id first_call_date
101 100 2025-01-15
102 100 2025-02-20
103 101 2025-03-10
104 102 2025-04-05
105 103 2024-12-01

5. 示例数据查询结果表

(注意：主调服务 Legacy_Report (105) 因为依赖建立日期是 2024 年，所以该分支的递归被截断，不出现在结果中)

service_id service_name dependency_depth dependency_path
101 Order_Service 1 Payment_Gateway->Order_Service
102 User_Wallet_Service 1 Payment_Gateway->User_Wallet_Service
103 E_Commerce_BFF 2 Payment_Gateway->Order_Service->E_Commerce_BFF
104 Analytics_Job 2 Payment_Gateway->User_Wallet_Service->Analytics_Job


示例1

输入

-- 创建表结构（仅保留逻辑关联，无强制外键约束）
DROP TABLE IF EXISTS service_dependencies;
DROP TABLE IF EXISTS services;

CREATE TABLE services (
    service_id INT PRIMARY KEY,
    service_name VARCHAR(100),
    owner_team VARCHAR(50)
);

CREATE TABLE service_dependencies (
    caller_service_id INT,
    callee_service_id INT,
    first_call_date DATE
);

-- 插入示例数据
INSERT INTO services (service_id, service_name, owner_team) VALUES
(100, 'Payment_Gateway', 'Core_Pay'),
(101, 'Order_Service', 'Trade_Biz'),
(102, 'User_Wallet_Service', 'Asset_Biz'),
(103, 'E_Commerce_BFF', 'Frontend_Arch'),
(104, 'Analytics_Job', 'Data_Tech'),
(105, 'Legacy_Report', 'Data_Tech');

INSERT INTO service_dependencies (caller_service_id, callee_service_id, first_call_date) VALUES
(101, 100, '2025-01-15'),  -- Order 直接调用 Payment (深度1)
(102, 100, '2025-02-20'),  -- Wallet 直接调用 Payment (深度1)
(103, 101, '2025-03-10'),  -- BFF 调用 Order (深度2)
(104, 102, '2025-04-05'),  -- Analytics 调用 Wallet (深度2)
(105, 103, '2024-12-01');  -- Legacy 调用 BFF，但在2024年，不应计入

输出

service_id|service_name|dependency_depth|dependency_path
101|Order_Service|1|Payment_Gateway->Order_Service
102|User_Wallet_Service|1|Payment_Gateway->User_Wallet_Service
103|E_Commerce_BFF|2|Payment_Gateway->Order_Service->E_Commerce_BFF
104|Analytics_Job|2|Payment_Gateway->User_Wallet_Service->Analytics_Job
算法知识视频讲解
添加回答
Mysql
胖咪是个宝头像 胖咪是个宝
with recursive t1 as(
select service_id, service_name, 0 as dependency_depth, service_name as dependency_path
from services
where service_name = 'Payment_Gateway'
union all 
select s.service_id, s.service_name, 
dependency_depth + 1 as dependency_depth,
concat(dependency_path, '->', s.service_name) dependency_path
from t1 
join service_dependencies sd on t1.service_id = sd.callee_service_id and year(first_call_date) = '2025'
join services s on s.service_id = sd.caller_service_id
)
select * from t1 
where dependency_depth > 0
order by dependency_depth, service_id, dependency_path;
发表于 2026-03-16 19:53:46 回复(1)
lueluezi头像 lueluezi
WITH RECURSIVE path AS (
SELECT
    callee_service_id AS service_id,
    callee_service_name AS service_name,
    0 AS dependency_depth,
    callee_service_name AS dependency_path
FROM (
SELECT
    sd.caller_service_id,
    s1.service_name AS caller_service_name,
    sd.callee_service_id,
    s2.service_name AS callee_service_name
FROM service_dependencies sd
LEFT JOIN services s1 ON sd.caller_service_id = s1.service_id
LEFT JOIN services s2 ON sd.callee_service_id = s2.service_id
WHERE YEAR(sd.first_call_date) = 2025
) t0
WHERE callee_service_name = 'Payment_Gateway'

UNION ALL

SELECT
    t0.caller_service_id AS service_id,
    t0.caller_service_name AS service_name,
    p.dependency_depth + 1 AS dependency_depth,
    CONCAT(p.dependency_path, '->',t0.caller_service_name) AS dependency_path
FROM (
SELECT
    sd.caller_service_id,
    s1.service_name AS caller_service_name,
    sd.callee_service_id,
    s2.service_name AS callee_service_name
FROM service_dependencies sd
LEFT JOIN services s1 ON sd.caller_service_id = s1.service_id
LEFT JOIN services s2 ON sd.callee_service_id = s2.service_id
WHERE YEAR(sd.first_call_date) = 2025
) t0
JOIN path p
ON t0.callee_service_id = p.service_id
)
SELECT DISTINCT *
FROM path
WHERE dependency_depth > 0
发表于 2026-03-14 10:02:22 回复(0)
虫哇头像 虫哇
学到了，这个迭代cte用法，但是还需要练习

with recursive t1 as (
    select service_id, service_name, 0 as dependency_depth,
    service_name as dependency_path
    from services s
    where service_name = 'Payment_Gateway'

    union all

    select s.service_id, s.service_name, 
    (dependency_depth + 1) as dependency_depth, 
    concat(dependency_path, '->', s.service_name) as dependency_path
    from t1
    join service_dependencies sd on sd.callee_service_id = t1.service_id
    join services s on sd.caller_service_id = s.service_id
    where year(first_call_date) = 2025
)

select * from t1 
where dependency_depth > 0
order by dependency_depth, service_id, dependency_path
发表于 2026-03-12 14:41:35 回复(0)
牛客783772350号头像 牛客783772350号
with recursive service_temp as (
    select c.service_id,c.service_name,1 as dependency_depth,
    concat('Payment_Gateway->',c.service_name) as dependency_path
    from service_dependencies as a
    inner join services as b on a.callee_service_id = b.service_id
    inner join services as c on a.caller_service_id = c.service_id
    where date_format(a.first_call_date,'%Y-%m-%d')=2025 and b.service_name ='Payment_Gateway'
    union all
    select c.service_id,c.service_name,a.dependency_depth +1 as dependency,
    concat(a.dependency_path,'->',c.service_name) as dependency_path
    from service_temp as a
    inner join service_dependencies as b on a.service_id =b.callee_service_id
    inner join services as c on b.caller_service_id = c.service_id
    where date_format(b.first_call_date,'%Y-%m-%d')=2025
)
select *
from service_temp
order by dependency_depth,service_id,dependency_path
发表于 2026-03-05 12:07:16 回复(0)
Lovnly头像 Lovnly 
-- 迭代CTE
with recursive temp as (
    select service_id,service_name,0 as dependency_depth,service_name as dependency_path
    from services
    where service_name='Payment_Gateway'

    union all

    select sd.caller_service_id,s.service_name,dependency_depth+1,concat(dependency_path,'->',s.service_name)
    from temp t
    inner join service_dependencies sd on sd.callee_service_id=t.service_id
    inner join services s on caller_service_id=s.service_id
    where year(first_call_date)='2025'
)

select service_id,service_name,dependency_depth,dependency_path
from temp
where dependency_depth>0
order by dependency_depth asc,service_id asc,dependency_path asc
之前没接触过迭代CTE，感谢大佬分享，不会做的朋友了解一下迭代CTE可能就会了
发表于 2026-03-04 21:27:47 回复(0)
牛客194200828号头像 牛客194200828号 
with recursive temp1 as (
    select service_id,service_name,0 dependency_depth,service_name dependency_path
    from services where service_name = 'Payment_Gateway'

    union all 

    select s.service_id,s.service_name,dependency_depth+1 dependency_depth,concat(dependency_path,'->',s.service_name) dependency_path 
    from temp1 t1 
    join service_dependencies sd on sd.callee_service_id = t1.service_id and year(first_call_date)=2025
    join services s on sd.caller_service_id = s.service_id 
)


select * from temp1 where dependency_depth>0 order by dependency_depth,service_id,dependency_path

发表于 2026-03-04 14:38:51 回复(0)
数分菜鸟求职ing头像 数分菜鸟求职ing 
# 错误代码
select caller_service_id service_id,s1.service_name,
if(s2.service_name='Payment_Gateway',1,2) dependency_depth,
if(if(s2.service_name='Payment_Gateway',1,2)=1,concat('Payment_Gateway','->',s1.service_name),concat('Payment_Gateway','->',s2.service_name,'->',s1.service_name)) dependency_path
from service_dependencies sd left join services s1 on sd.caller_service_id = s1.service_id
left join services s2 on sd.callee_service_id = s2.service_id 
where year(first_call_date)=2025
order by dependency_depth,s1.service_id,dependency_path

# 抄的大佬正确代码
with RECURSIVE temp as (
    # 1、Payment_Gateway作为被调用，找到主调服务
    select
    s2.service_id,
    s2.service_name,
    1 as dependency_depth,
    concat('Payment_Gateway->', s2.service_name) as dependency_path
    from service_dependencies sp
    join services s on sp.callee_service_id = s.service_id # 关联被调用方（Payment_Gateway
    join services s2 on sp.caller_service_id = s2.service_id # 关联主调用方（直接依赖）
    where year(first_call_date) = 2025
    and s.service_name = 'Payment_Gateway' # 被调用方是 Payment_Gateway

    union all 

    # 上层作为被调服务，找到本层的主调服务
    select 
    s2.service_id as service_id,
    s2.service_name,
    t.dependency_depth + 1,
    concat(t.dependency_path, '->', s2.service_name)
    from temp t 
    join service_dependencies sp on sp.callee_service_id = t.service_id # 上一层的主调用服务 → 本层的被调用服务
    join services s2 on sp.caller_service_id = s2.service_id # 关联本层的主调用服务
    where year(first_call_date) = 2025
)

select * from temp
order by dependency_depth,service_id,dependency_path
;

发表于 2026-03-02 15:05:57 回复(0)
提交观点

问题信息

难度：
7条回答 75浏览

热门推荐

相关试题

微服务架构下的深层依赖链路漏洞影响面分析

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号