select count() from mr_exceptioninfo —-12235
    select count(    ) from mr_plan_param —19100
    问题一：此sql在压测时，达48秒（这是平台反馈的性能慢的主要sql语句。当时压测时，生成了大量数据，所以此sql执行时间达到48秒）。基于目前的上面数据量，执行时间在0.4秒左右。
    select T0.exception_info as exceptionInfo,T0.execute_date as executeDate,T3.cName as orgName,T4.code as planParamCode,T1.manufacturing_specification as materialManufacturingSpecification,T2.code as productCode,T2.name as productName
    from mr_exceptioninfo T0
    left join engineering_data.ed_material as T1 on T1.id=T0.material_id and T1.tenant_id=1692529864102144
    left join ugoods.product as T2 on T2.id=T1.product_id and T2.iDeleted=0 and T2.tenant_id=1692529864102144
    left join mr_plan_param as T4 on T4.id=T0.plan_param_id and T4.tenant_id=1692529864102144
    left join ugoods.v_orgs as T3 on T3.id=T0.org_id and T3.tenant_id=1692529864102144
    where (T0.org_id in (‘1696543411835136’) and T0.tenant_id=1692529864102144)
    order by (T4.code) desc,(T2.code) asc
    limit 0,20
    优化建议：
    1.sql结构修改为如下结构，可快速取得limit 20数据，减少order消耗，时间可提高20倍左右。
    select T0.exception_info as exceptionInfo,T0.execute_date as executeDate,T3.cName as orgName,T4.code as planParamCode,T1.manufacturing_specification as materialManufacturingSpecification,T2.code as productCode,T2.name as productName
    from mr_plan_param as T4
    inner join mr_exceptioninfo T0 on T4.id=T0.plan_param_id and T0.tenant_id=1692529864102144
    left join engineering_data.ed_material as T1 on T1.id=T0.material_id and T1.tenant_id=1692529864102144
    left join ugoods.product as T2 on T2.id=T1.product_id and T2.iDeleted=0 and T2.tenant_id=1692529864102144
    left join ugoods.v_orgs as T3 on T3.id=T0.org_id and T3.tenant_id=1692529864102144
    where (T4.org_id in (‘1696543411835136’) and T4.tenant_id=1692529864102144)
    order by (T4.code) desc
    limit 0,20
    2.补充索引后，效率会进一步提升
    create index ix_zg on mr_plan_param(tenant_id,org_id,code desc)
    3.由于业务上根据 (T4.code) desc,(T2.code) asc排序会导致效率低下，感觉从业务上根据异常产生的时间倒序更为合理。同时考虑默认查询指定时间（如1周）以内的异常，来进一步提高效率。开发和需求根据实际情况后续沟通优化处理。

    问题二：这是取记录总行数的慢sql。
    select count(1) as totalCount from (
    select 1 as num
    from mr_exceptioninfo T0
    left join engineering_data.ed_material as T1 on T1.id=T0.material_id and T1.tenant_id=1692529864102144
    left join ugoods.product as T2 on T2.id=T1.product_id and T2.iDeleted=0 and T2.tenant_id=1692529864102144
    left join mr_plan_param as T4 on T4.id=T0.plan_param_id and T4.tenant_id=1692529864102144
    left join ugoods.v_orgs as T3 on T3.id=T0.org_id and T3.tenant_id=1692529864102144
    where (T0.org_id in (‘1696543411835136’) and T0.tenant_id=1692529864102144)
    ) t
    —9476行
    优化建议：
    1.对于可预估记录数会超过100万行的统计，建议列表不再显示总记录数。此业务据开发讲，本身属于一个动态创建的过程（旧异常会删除掉），如果是此种情况，可以显示动态记录。
    2.从业务上考虑是否对于列表在显示时，默认有个时间范围的过滤条件，如默认显示1周（或1月）异常，用户再根据情况可查询半月或全部异常。
    3.生产环境考虑使用mysql8.0.14以后版本，对于表的查询可以启用并行查询，速度有明显提高。

    select count(1) as totalCount from (
    select 1 as num
    from mr_plan_param as T4
    inner join mr_exceptioninfo T0 on T4.id=T0.plan_param_id and T0.tenant_id=1692529864102144
    left join engineering_data.ed_material as T1 on T1.id=T0.material_id and T1.tenant_id=1692529864102144
    left join ugoods.product as T2 on T2.id=T1.product_id and T2.iDeleted=0 and T2.tenant_id=1692529864102144

    left join ugoods.v_orgs as T3 on T3.id=T0.org_id and T3.tenant_id=1692529864102144
    where (T4.org_id in (‘1696543411835136’) and T4.tenant_id=1692529864102144)
    ) t

    问题三：此sql数据量异常，根据条件查，根据>=4月1号查，有近100万数据，用时2分钟。
    select distinct T0.org_id as orgId,T0.material_id as materialId,T0.sku_id as skuId,T0.product_id as productId,T0.plan_property as planProperty,T0.origin_quantity as originQuantity,T0.suggest_plan_quantity as suggestPlanQuantity,T0.issued_quantity as issuedQuantity,T0.start_date as startDate,T0.finish_date as finishDate,T0.plan_type as planType,T0.status as status,T0.department_id as departmentId,T0.plan_staff_id as planStaffId,T0.purchase_staff_id as purchaseStaffId,T0.vendor_id as vendorId,T0.track_number as trackNumber,T0.first_source_autoid as firstsourceautoid,T0.first_source as firstsource,T0.first_upcode as firstupcode,T0.plan_param_id as planParamId,T0.association_guid as associationGUID,T0.remark as remark,T0.tenant_id as tenant,T0.create_time as createTime,T0.create_date as createDate,T0.modify_time as modifyTime,T0.modify_date as modifyDate,T0.creator as creator,T0.modifier as modifier,T0.creatorId as creatorId,T0.modifierId as modifierId,T0.locker as locker,T0.lock_time as lockTime,T0.lock_Date as lockDate,T0.code as code,T0.free1 as free1,T0.free2 as free2,T0.free3 as free3,T0.free4 as free4,T0.free5 as free5,T0.free6 as free6,T0.free7 as free7,T0.free8 as free8,T0.free9 as free9,T0.free10 as free10,T0.sourceid as sourceid,T0.sourceautoid as sourceautoid,T0.source as source,T0.upcode as upcode,T0.makerule_code as makeRuleCode,T0.id as id,T0.pubts as pubts
    from mr_plan_order T0
    where (T0.start_date>=’2020-05-15 00:00:00’ and T0.org_id in (‘1696543411835136’) and T0.tenant_id=1692529864102144)
    优化建议：
    1.开发看下此sql是做什么业务的，要从业务查避免此sql查出的数据超过2万行，如果是统计类的，建议用sql直接统计计算得出，如果是就是一个查询类，建议从时间上控制返回行数或分页。

    问题四：数据量异常，导致时间慢。
    select sum(T0.origin_quantity) as originQuantity,sum(T0.suggest_plan_quantity) as suggestPlanQuantity,sum(T0.issued_quantity) as issuedQuantity
    from mr_plan_order T0
    left join engineering_data.ed_material as T1 on T1.id=T0.material_id and T1.tenant_id=1692529864102144
    left join ugoods.product as T2 on T2.id=T1.product_id and T2.iDeleted=0 and T2.tenant_id=1692529864102144
    left join ugoods.productApplyRange as T3 on T3.productId=T2.id and (T3.orgId=T0.org_id) and T3.tenant_id=1692529864102144
    where (T0.pubts<=’2020-05-29 19:32:38’ and T0.suggest_plan_quantity>0 and T0.org_id in (‘1696543411835136’) and T0.tenant_id=1692529864102144)
    优化建议：
    1.根据时间等参数，控制数据量的统计范围。
    2.考虑添加（据说此业务操作较频繁，可以考虑添加覆盖索引来缓解）
    create index id_zg on mr_plan_order(tenant_id,pubts,org_id,suggest_plan_quantity,material_id) include(origin_quantity,issued_quantity)

    select count(*) ,suggest_plan_quantity from mr_plan_order group by suggest_plan_quantity order by suggest_plan_quantity desc