物流轨迹查询&更新慢Sql优化背景系统快递物流轨迹对接了快递100，在通过快递100订阅单号物流轨迹之后，轨迹更新时

1. 背景

表结构数据如下:

create table kd100_track_record
(
    id                 bigint auto_increment comment '主键id'
        primary key,
    express_number     varchar(64)      not null comment '快递单号',
    carrier_code       varchar(32)      not null comment '快递100运输商编码',
    origin_content     json             not null comment '原始推送数据',
    localized_content  json             null comment '本地化后的推送数据',
    sign_flag          bit default b'0' not null comment '是否已签收 0:未签收，1:已签收',
    abort_flag         bit default b'0' not null comment '是否已停止推送0-未停止，1-已停止',
    latest_status      varchar(64)      null comment '最新推送状态',
    latest_status_code varchar(5)       null comment '物流状态code',
    create_time        datetime         null comment '创建时间',
    update_time        datetime         null comment '更新时间'
)
    comment '快递100轨迹推送记录';

create index idx_create_time_carrier_code_status_code
    on kd100_track_record (create_time, carrier_code, latest_status_code);

create index idx_express_number_carrier_code
    on kd100_track_record (express_number, carrier_code);

create index idx_status_code_create_time
    on kd100_track_record (create_time, latest_status_code);

近期阿里云arms上观测到了物流轨迹查询时出现慢sql：

select ... from kd100_track_record where express_number = '...';

系统快递物流轨迹对接了快递100，在通过快递100订阅单号物流轨迹之后，轨迹更新时会通过回调接口推送最新数据到系统。

回调接口处理逻辑：(1)如果该单号不存在则新增一条数据；(2)如果该单号存在则更新该条数据。

2.情况分析

对于快递100而言，推送频率经过观察elk日志上回调接口调用，发现是每隔一段时间批量推送类似于定时任务，推送数据都正常接收的情况下qps是几十(50-80)，如果出现异常情况，例如我方服务异常、网络不通等情况使得回调接口没能正确响应，数据会堆积在快递100导致qps激增，出现异常的情况下峰值在8000。

对于业务线而言，我方系统需要提供物流轨迹查询接口，该接口会查询该表当中数据，正常情况下，sql会直接通过物流单号走索引查询到该条数据不会存在慢sql性能问题。

基于此初步分析是由于update和select产生碰撞，update持有更新排他锁，而select被长时间挂起，无法查询到该条数据导致查询很快的sql变为一条慢sql。

再分析该update耗时很久的原因：

1.大字段：更新时，表中存在两个大json数据类型的字段，分别是origin_content和localized_content，每次推送数据，必然会更新这两个字段

2.update sql：review代码时，发现调用update时，使用的java bean元数据是数据库查询出来的对象，每次都会全量更新该条数据所有字段，由于表中的数据量比较大、更新频率高，导致每次更新时，可能会带来索引不必要的更新。

3.解决方案

针对以上问题，临时做出以下优化：

1.经过确认之后，origin_content属于冗余字段，未在业务上使用到，为了不影响线上数据库的正常运行，在代码中将该字段插入时设置为空json对象{}，更新时，不再维护该字段

2.在update时，调整更新的字段为变化的字段，索引中的字段如create_time、carrier_code等为不会产生变化的字段，取消更新这些索引字段，避免带来索引更新不必要的开销

3.调整索引结构：由于索引中，carrier_code以及latest_status_code两者为常量字段，其数据区分度较小，因此对索引调整为以下：

create index idx_create_time_carrier_code_status_code
    on kd100_track_record (create_time);

create index idx_express_number_carrier_code
    on kd100_track_record (express_number);

create index idx_status_code_create_time
    on kd100_track_record (create_time, express_number);

调整之后，索引字段的值，不会发生变化且区分度较高，符合索引建立的原则。

4.效果

经过上述优化之后，update更新时，对于表数据而言，减少了冗余字段更新带来的负担，对于索引而言，减少了索引字段，占用的磁盘空间减小，更新不会带来索引的变化，整体更新速度大幅提升，正常情况下arms未再出现慢查询sql语句。

5.未来改进方案

后续优化思路：在物流轨迹都能正常消费的情况下，无需在对回调接口进行优化，但是在极端情况下，例如网络异常、网关未将回调接口暴露时，会出现数据大量堆积，快递100会推送数据量达到qps10000，这种情况下，仍然会出现问题。后续可以考虑采用接入消息队列、线程池异步Async consume等手段异步消费推送的物流轨迹数据，回调接口直接返回成功，后续轨迹的处理更新逻辑通过线程池异步消费或者MQ发布订阅的方式。

6.除此之外

除了业务线调用接口，系统的erp端也存在分页查询该表功能，此处通过延迟查询关联id，减少回表查询方式提升了查询的效率，优化了sql。通常情况下，我们的分页查询语句是：

select column1,column2 from table where ... limit 10000,10;

在数据量比较大的情况下，会出现深分页的问题，此时MySql的执行逻辑如下：

通过索引找到100010条符合查询条件的记录id（走索引查询很快）
再通过100010个id回表查询这100010条记录（磁盘操作）--- 回表100010次
最后丢弃100000条数据，按分页条件给用户返回10条记录

通过延迟关联查询优化的sql为：

select column1,column2 from table inner a join (select id from table where ... limit 10000,10)b on a.id=b.id

mysql的执行逻辑

1.通过索引找到100010条符合查询条件的记录id（走索引覆盖查询很快） 2.在内存中按分页条件取10个记录id，然后回表查询这10条记录（磁盘操作）--- 回表10次 3.最后给用户返回10条记录优化点：延迟关联通过减少了大量的回表查询，大大的提升了sql查询效率

注意：以上两次查询都是在索引命中的情况下得出的id，如果索引失效（未命中），速度速度照样很慢

物流轨迹查询&更新 慢Sql优化