自由职业者-梳理与分析

You are not logged in. Any changes you make will be marked as anonymous.

<h2>一、现状与问题</h2>痛点：自由职业者实名与非实名的在多端分页查询和导出，存在接口响应缓慢的问题原因：业务耦合太重（连表过多）<h2>二、自由职业者现有相关表结构</h2><h3>1.基础表 customer - 自由职业者基本信息表 customer_group - 自由职业者分组表 customer_identity - 自由职业者身份关系表 2. 关联关系表 company_customer - 自由职业者与企业关系表 agent_customer - 自由职业者与代理商关系表 server_customer - 自由职业者与服务商关系表 customer_duty - 自由职业者与任务关系表 customer_phone - 自由职业者通讯手机号关系表 customer_bank - 自由职业者银行卡关系表（企业维度） customer_alipay - 自由职业者支付宝关系表（企业维度） customer_pay_account - 自由职业者收款账号关系表(自由职业者维度)</h3><img class="confluence-embedded-image" draggable="false" width="900" src="/download/attachments/31294129/image-2025-12-2_10-2-35.png?version=1&amp;modificationDate=1764640954383&amp;api=v2" data-image-src="/download/attachments/31294129/image-2025-12-2_10-2-35.png?version=1&amp;modificationDate=1764640954383&amp;api=v2" data-unresolved-comment-count="0" data-linked-resource-id="31294132" data-linked-resource-version="1" data-linked-resource-type="attachment" data-linked-resource-default-alias="image-2025-12-2_10-2-35.png" data-base-url="https://confluence.zuirenli.cn" data-linked-resource-content-type="image/png" data-linked-resource-container-id="31294129" data-linked-resource-container-version="8" title="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_10-2-35.png" data-location="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_10-2-35.png" data-image-height="861" data-image-width="935"> <h2>三、结合日志与代码进行分析</h2><h3>1.页面截图</h3><img class="confluence-embedded-image" draggable="false" width="1313" src="/download/attachments/31294129/image-2025-12-2_13-50-4.png?version=1&amp;modificationDate=1764654603972&amp;api=v2" data-image-src="/download/attachments/31294129/image-2025-12-2_13-50-4.png?version=1&amp;modificationDate=1764654603972&amp;api=v2" data-unresolved-comment-count="0" data-linked-resource-id="31294138" data-linked-resource-version="1" data-linked-resource-type="attachment" data-linked-resource-default-alias="image-2025-12-2_13-50-4.png" data-base-url="https://confluence.zuirenli.cn" data-linked-resource-content-type="image/png" data-linked-resource-container-id="31294129" data-linked-resource-container-version="8" title="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-50-4.png" data-location="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-50-4.png" data-image-height="726" data-image-width="1893"><img class="confluence-embedded-image" draggable="false" height="27" src="/download/attachments/31294129/image-2025-12-2_13-48-14.png?version=1&amp;modificationDate=1764654493850&amp;api=v2" data-image-src="/download/attachments/31294129/image-2025-12-2_13-48-14.png?version=1&amp;modificationDate=1764654493850&amp;api=v2" data-unresolved-comment-count="0" data-linked-resource-id="31294136" data-linked-resource-version="1" data-linked-resource-type="attachment" data-linked-resource-default-alias="image-2025-12-2_13-48-14.png" data-base-url="https://confluence.zuirenli.cn" data-linked-resource-content-type="image/png" data-linked-resource-container-id="31294129" data-linked-resource-container-version="8" title="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-48-14.png" data-location="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-48-14.png" data-image-height="27" data-image-width="1313"><h3>2.日志分析</h3><img class="confluence-embedded-image" draggable="false" height="167" src="/download/attachments/31294129/image-2025-12-2_13-48-36.png?version=1&amp;modificationDate=1764654515953&amp;api=v2" data-image-src="/download/attachments/31294129/image-2025-12-2_13-48-36.png?version=1&amp;modificationDate=1764654515953&amp;api=v2" data-unresolved-comment-count="0" data-linked-resource-id="31294137" data-linked-resource-version="1" data-linked-resource-type="attachment" data-linked-resource-default-alias="image-2025-12-2_13-48-36.png" data-base-url="https://confluence.zuirenli.cn" data-linked-resource-content-type="image/png" data-linked-resource-container-id="31294129" data-linked-resource-container-version="8" title="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-48-36.png" data-location="留言板 > 自由职业者-梳理与分析 > image-2025-12-2_13-48-36.png" data-image-height="167" data-image-width="1773"><h3>3.定位到慢sql语句，以企业端和运营端语句为例</h3>企业端：<pre>&lt;select id=&quot;selectForCompany&quot; resultMap=&quot;CustomerResult&quot;&gt; 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 &lt;where&gt; c.deleted = 0 &lt;if test=&quot;param.idNos != null and param.idNos.size &gt; 0&quot;&gt; and c.id_no in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.idNos&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; &lt;/if&gt; &lt;if test=&quot;param.companyIds != null and param.companyIds.size &gt; 0&quot;&gt; and cc.company_id in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.companyIds&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; &lt;/if&gt; &lt;if test=&quot;param.serverIds != null and param.serverIds.size &gt; 0&quot;&gt; and sc.server_id in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.serverIds&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; &lt;/if&gt; &lt;/where&gt; group by c.id_no order by c.customer_no desc &lt;/select&gt; 涉及的表如下： customer - 自由职业者主表 company_customer - 自由职业者与企业关系表 customer_group - 自由职业者分组表 customer_phone - 自由职业者通讯手机号关系表 customer_bank - 自由职业者银行卡关系表 customer_alipay - 自由职业者支付宝关系表 customer_duty - 自由职业者与任务关系表 tax_source_config - 税源配置表 server_customer - 自由职业者与服务商关系表 server - 服务商表 总共涉及10张表，通过LEFT JOIN关联查询，为企业端提供自由职业者的信息，包括分组信息、联系方式、支付账户、任务类型和税源状态等。 运营端： &lt;select id=&quot;selectForSaas&quot; resultMap=&quot;CustomerResult&quot;&gt; 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 &lt;where&gt; c.deleted = 0 &lt;if test=&quot;param.idNos != null and param.idNos.size &gt; 0&quot;&gt; and c.id_no in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.idNos&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; &lt;/if&gt; &lt;if test=&quot;param.companyIds != null and param.companyIds.size &gt; 0&quot;&gt; and (cc.company_id in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.companyIds&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; or cc.company_id is null) &lt;/if&gt; &lt;if test=&quot;param.topAgentIds != null and param.topAgentIds.size &gt; 0&quot;&gt; and ac.top_agent_id in &lt;foreach item=&quot;item&quot; index=&quot;index&quot; collection=&quot;param.topAgentIds&quot; open=&quot;(&quot; separator=&quot;,&quot; close=&quot;)&quot;&gt; #{item} &lt;/foreach&gt; &lt;/if&gt; &lt;/where&gt; group by c.id_no) tmp left join customer c on c.id_no = tmp.id_no order by c.customer_no desc &lt;/select&gt; 涉及的表如下： customer - 自由职业者主表 company_customer - 自由职业者与企业关系表 company - 企业表 customer_duty - 自由职业者与任务关系表 customer_phone - 自由职业者通讯手机号关系表 customer_bank - 自由职业者银行卡关系表 customer_alipay - 自由职业者支付宝关系表 agent_customer - 自由职业者与代理商关系表 agent - 代理商表 server_customer - 自由职业者与服务商关系表 server - 服务商表 总共涉及11张表，通过LEFT JOIN关联查询，获取自由职业者的信息，包括关联的企业、服务商、代理商、联系方式、收款账户和任务等信息。 </pre><h2 class="header-iWP5WJ auto-hide-last-sibling-br" style="margin-left: 0.0px;">四、优化方案</h2><h3 class="header-iWP5WJ auto-hide-last-sibling-br">一、数据库层面优化</h3><h4 class="header-iWP5WJ auto-hide-last-sibling-br" style="margin-left: 0.0px;">1. 表结构整合</h4><ul class="auto-hide-last-sibling-br"><li style="list-style-type: disc;">收款账号表合并：将 <code>customer_bank</code>（银行收款账号表）与 <code>customer_alipay</code>（支付宝收款账号表）合并为 <code>customer_receive_account</code> 统一收款账号表，减少表关联次数，提升查询效率。</li><li style="list-style-type: disc;">关系表通用化：整合 <code>company_customer</code>、<code>agent_customer</code>、<code>server_customer</code> 三张关系表为一张通用关系表（如 <code>customer_relation</code>），通过新增 <code>relation_type</code> 字段（枚举值：COMPANY/AGENT/SERVER）区分关系类型，简化数据模型，降低多表关联复杂度。</li></ul><h4 class="header-iWP5WJ auto-hide-last-sibling-br">2. 索引优化</h4>针对高频查询场景，为核心字段补充索引，减少全表扫描,目前在线上环境已经加上必须的索引，目前的查询时间已经在索引的帮助下有一定减少，索引优化前30S+。<h3 class="header-iWP5WJ auto-hide-last-sibling-br">二、代码实现层优化</h3>采用先查询自由职业者主表.后异步关联的查询逻辑，通过异步线程并行查询收款账号表（<code>customer_receive_account</code>）、通用关系表（<code>customer_relation</code>）等关联数据，避免同步多表 JOIN 导致的查询阻塞,降低多表耦合与数据处理压力。<h3>三、业务层面优化</h3><h4>1. 无效业务字段屏蔽</h4>因海南业务已暂停，前端展示与后端查询中均隐藏海南相关业务字段，不再执行海南业务相关的数据查询逻辑，减少无效数据处理开销。<h4 class="header-iWP5WJ auto-hide-last-sibling-br">2. 列表字段精简</h4>遵循列表展示核心信息，详情展示更多信息的原则：<ul class="auto-hide-last-sibling-br"><li style="list-style-type: disc;">列表页仅保留关键字段，减少数据传输量与渲染压力；</li><li style="list-style-type: disc;">非关键字段统一在详情页按需展示。</li></ul><h3 class="header-iWP5WJ auto-hide-last-sibling-br">四、导出功能优化</h3>基于上述数据库与代码层优化基础，针对导出场景补充异步能力：由于导出功能需获取全量数据，随着业务增长数据量会持续扩大，同步导出易出现超时、内存溢出等问题，可以采用下载提交至消息队列，后台消费者生成至下载中心的方案。<pre> </pre>