自由职业者-梳理与分析

一、现状与问题

痛点：自由职业者实名与非实名的在多端分页查询和导出，存在接口响应缓慢的问题

原因：业务耦合太重（连表过多）

二、自由职业者现有相关表结构

1.基础表
customer - 自由职业者基本信息表
customer_group - 自由职业者分组表
customer_identity - 自由职业者身份关系表
2. 关联关系表
company_customer - 自由职业者与企业关系表
agent_customer - 自由职业者与代理商关系表
server_customer - 自由职业者与服务商关系表
customer_duty - 自由职业者与任务关系表
customer_phone - 自由职业者通讯手机号关系表
customer_bank - 自由职业者银行卡关系表（企业维度）
customer_alipay - 自由职业者支付宝关系表（企业维度）
customer_pay_account - 自由职业者收款账号关系表(自由职业者维度)

三、结合日志与代码进行分析

1.页面截图

2.日志分析

3.定位到慢sql语句，以企业端和运营端语句为例

企业端：

<select id="selectForCompany" resultMap="CustomerResult">
    select c.*, cc.group_id, cg.group_name,
    group_concat(distinct s.id) as server_ids,
    group_concat(distinct s.server_name) as server_names,
    group_concat(distinct cp.phone) as contact_phones,
    group_concat(DISTINCT CASE WHEN cd.duty_type = 0 THEN '众包灵工' WHEN cd.duty_type = 1 THEN '撮合灵工' END
    SEPARATOR ',') as duty_types,
    group_concat(DISTINCT cpa.bank_card SEPARATOR ',')         as bank_card,
    group_concat(DISTINCT cpaa.alipay_account SEPARATOR ',')         as alipay_account,
    IF(tsc.business_id is not null, 1, 0) as hn_tax_status
    from customer c
    left join company_customer cc on cc.id_no = c.id_no and cc.deleted = 0
    left join customer_group cg on cg.id = cc.group_id and cg.deleted = 0
    left join customer_phone cp on cp.id_no = c.id_no and cp.deleted = 0 and cp.company_id = cc.company_id
    left join customer_bank cpa on cpa.id_no = c.id_no and cpa.deleted = 0
    left join customer_alipay cpaa on cpaa.id_no = c.id_no and cpa.deleted = 0
    left join customer_duty cd on cd.id_no = c.id_no and cd.deleted = 0 and cd.company_id = cc.company_id
    left join tax_source_config tsc on tsc.business_id = cc.company_id and tsc.deleted = 0
    left join server_customer sc on sc.id_no = c.id_no and sc.deleted = 0 and sc.company_id = cc.company_id
    left join server s on s.id = sc.server_id and s.deleted = 0
    <where>
        c.deleted = 0
        <if test="param.idNos != null and param.idNos.size > 0">
            and c.id_no in
            <foreach item="item" index="index" collection="param.idNos" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="param.companyIds != null and param.companyIds.size > 0">
            and cc.company_id in
            <foreach item="item" index="index" collection="param.companyIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="param.serverIds != null and param.serverIds.size > 0">
            and sc.server_id in
            <foreach item="item" index="index" collection="param.serverIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </where>
    group by c.id_no
    order by c.customer_no desc
</select>

涉及的表如下：
customer - 自由职业者主表
company_customer - 自由职业者与企业关系表
customer_group - 自由职业者分组表
customer_phone - 自由职业者通讯手机号关系表
customer_bank - 自由职业者银行卡关系表
customer_alipay - 自由职业者支付宝关系表
customer_duty - 自由职业者与任务关系表
tax_source_config - 税源配置表
server_customer - 自由职业者与服务商关系表
server - 服务商表
总共涉及10张表，通过LEFT JOIN关联查询，为企业端提供自由职业者的信息，包括分组信息、联系方式、支付账户、任务类型和税源状态等。


运营端：
<select id="selectForSaas" resultMap="CustomerResult">
    select c.*,
           tmp.server_ids,
           tmp.server_names,
           tmp.company_names,
           tmp.top_agent_names,
           tmp.contact_phones,
           tmp.bank_card,
            tmp.alipay_account,
           tmp.duty_types
    from
    (select c.id_no,
            group_concat(distinct s.id)             as server_ids,
            group_concat(distinct s.server_name)    as server_names,
            group_concat(distinct com.company_name) as company_names,
            group_concat(distinct a.agent_name)     as top_agent_names,
            group_concat(distinct cp.phone)         as contact_phones,
            group_concat(DISTINCT cpa.bank_card SEPARATOR ',')         as bank_card,
            group_concat(DISTINCT cpaa.alipay_account SEPARATOR ',')         as alipay_account,
            group_concat(DISTINCT
                         CASE WHEN cd.duty_type = 0 THEN '众包灵工' WHEN cd.duty_type = 1 THEN '撮合灵工' END
                         SEPARATOR ',')             as duty_types
     from customer c
              left join company_customer cc on cc.id_no = c.id_no and cc.deleted = 0
              left join company com on com.id = cc.company_id and com.deleted = 0
                left join customer_duty cd on cd.id_no = c.id_no and cd.deleted = 0 and cd.company_id = cc.company_id
              left join customer_phone cp on cp.id_no = c.id_no and cp.deleted = 0 and cp.company_id = cc.company_id
                left join customer_bank cpa on cpa.id_no = c.id_no and cpa.deleted = 0
                left join customer_alipay cpaa on cpaa.id_no = c.id_no and cpa.deleted = 0
              left join agent_customer ac on ac.id_no = c.id_no and ac.deleted = 0
              left join agent a on a.id = ac.top_agent_id and a.deleted = 0
              left join server_customer sc
                        on sc.id_no = c.id_no and sc.deleted = 0 and sc.company_id = cc.company_id
              left join server s on s.id = sc.server_id and s.deleted = 0
    <where>
        c.deleted = 0
        <if test="param.idNos != null and param.idNos.size > 0">
            and c.id_no in
            <foreach item="item" index="index" collection="param.idNos" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
        <if test="param.companyIds != null and param.companyIds.size > 0">
            and (cc.company_id in
            <foreach item="item" index="index" collection="param.companyIds" open="(" separator="," close=")">
                #{item}
            </foreach>
            or cc.company_id is null)
        </if>
        <if test="param.topAgentIds != null and param.topAgentIds.size > 0">
            and ac.top_agent_id in
            <foreach item="item" index="index" collection="param.topAgentIds" open="(" separator="," close=")">
                #{item}
            </foreach>
        </if>
    </where>
    group by c.id_no) tmp
        left join customer c on c.id_no = tmp.id_no
    order by c.customer_no desc
</select>

涉及的表如下：
customer - 自由职业者主表
company_customer - 自由职业者与企业关系表
company - 企业表
customer_duty - 自由职业者与任务关系表
customer_phone - 自由职业者通讯手机号关系表
customer_bank - 自由职业者银行卡关系表
customer_alipay - 自由职业者支付宝关系表
agent_customer - 自由职业者与代理商关系表
agent - 代理商表
server_customer - 自由职业者与服务商关系表
server - 服务商表
总共涉及11张表，通过LEFT JOIN关联查询，获取自由职业者的信息，包括关联的企业、服务商、代理商、联系方式、收款账户和任务等信息。

四、优化方案

一、数据库层面优化

1. 表结构整合

• 收款账号表合并：将 customer_bank（银行收款账号表）与 customer_alipay（支付宝收款账号表）合并为 customer_receive_account 统一收款账号表，减少表关联次数，提升查询效率。
• 关系表通用化：整合 company_customer、agent_customer、server_customer 三张关系表为一张通用关系表（如 customer_relation），通过新增 relation_type 字段（枚举值：COMPANY/AGENT/SERVER）区分关系类型，简化数据模型，降低多表关联复杂度。

2. 索引优化

针对高频查询场景，为核心字段补充索引，减少全表扫描,目前在线上环境已经加上必须的索引，目前的查询时间已经在索引的帮助下有一定减少，索引优化前30S+。

二、代码实现层优化

采用先查询自由职业者主表.后异步关联的查询逻辑，通过异步线程并行查询收款账号表（customer_receive_account）、通用关系表（customer_relation）等关联数据，避免同步多表 JOIN 导致的查询阻塞,降低多表耦合与数据处理压力。

三、业务层面优化

1. 无效业务字段屏蔽

因海南业务已暂停，前端展示与后端查询中均隐藏海南相关业务字段，不再执行海南业务相关的数据查询逻辑，减少无效数据处理开销。

2. 列表字段精简

遵循列表展示核心信息，详情展示更多信息的原则：

• 列表页仅保留关键字段，减少数据传输量与渲染压力；
• 非关键字段统一在详情页按需展示。

四、导出功能优化

基于上述数据库与代码层优化基础，针对导出场景补充异步能力：由于导出功能需获取全量数据，随着业务增长数据量会持续扩大，同步导出易出现超时、内存溢出等问题，可以采用下载提交至消息队列，后台消费者生成至下载中心的方案。