TIDB4.0 同一SQL在不同时刻,执行计划不一样,走了不同索引(生产问题,望支持)

为提高效率,请提供以下信息,问题描述清晰能够更快得到解决:

【TiDB 版本】4.0.4

【问题描述】同一sql在不同时刻走了不同索引,二者耗时相差千倍。
详细表结构及SQL见下文。表数量2600w+。
同一SQL,在不同时刻,走不同索引的两种情况如下:
情况一:走了索引,om_pay_time_list,耗时80ms。
情况二:走了索引,o2o_order_index,耗时105s。

问题:是什么原因导致了同一sql在不同时刻走了不同索引,从而导致耗时相差千倍。

CREATE TABLE order_main (
ID bigint(65) NOT NULL,
ORDER_NO varchar(20) DEFAULT NULL COMMENT ‘OMS订单号’,
ORDER_NO_P varchar(20) DEFAULT NULL COMMENT ‘拆单的母orderNo’,
ALIAS_ORDER_NO varchar(64) DEFAULT NULL,
ORDER_SOURCE varchar(32) DEFAULT NULL COMMENT ‘浪莎:LS,百胜:BS,微信前端:WX,导购APP:DG’,
ORDER_TYPE varchar(32) DEFAULT NULL COMMENT ‘普通订单(GENERAL) ,预售订单(RESERVATION),问题单据(),缺货单据,周期购(PERIOD)’,
FINISH_TIME datetime(6) DEFAULT NULL COMMENT ‘完成时间’,
MEMBER_NO varchar(32) DEFAULT NULL COMMENT ‘会员当前对应rowid\r\n’,
CUSTOMER_NAME varchar(32) DEFAULT NULL COMMENT ‘会员姓名’,
CUSTOMER_PHONE varchar(16) DEFAULT NULL COMMENT ‘会员手机号码’,
ORDER_TIME datetime(6) DEFAULT NULL COMMENT ‘订单产生时间’,
TRANSPORT_FEE decimal(20,2) DEFAULT NULL COMMENT ‘运费总额 | 物流费用’,
TOTAL_PRODUCT_PRICE decimal(20,2) DEFAULT NULL COMMENT ‘商品总价,折前’,
DISCOUNT_TRANSPORT decimal(20,2) DEFAULT NULL COMMENT ‘运费优惠’,
DISCOUNT_TOTAL decimal(20,2) DEFAULT NULL COMMENT ‘总价折扣优惠总金额(订单级折扣优惠+单品级折扣优惠)’,
TOTAL_PROMO decimal(20,2) DEFAULT NULL COMMENT ‘订单用券金额’,
TOTAL_PIONT decimal(20,2) DEFAULT NULL COMMENT ‘订单用积分’,
TOTAL_PIONT_AMOUNT decimal(20,2) DEFAULT NULL COMMENT ‘订单用积分抵扣金额’,
TOTAL_PAY_AMOUNT decimal(20,2) DEFAULT NULL COMMENT ’ 支付表中支付金额的汇总。实际支付总计 = 商品总价+运费-运费优惠-折扣优惠’,
TOTAL_GIVE_POINTS int(11) DEFAULT NULL COMMENT ‘赠送总积分’,
CONFIRMER_NO varchar(32) DEFAULT NULL COMMENT ‘审核人编码’,
CONFIRMER_NAME varchar(32) DEFAULT NULL COMMENT ‘审核人姓名’,
CONFIRM_TIME datetime(6) DEFAULT NULL COMMENT ‘审核时间’,
STATUS_CONFIRM varchar(20) DEFAULT NULL COMMENT ‘审核状态’,
STATUS_PAY varchar(32) DEFAULT NULL COMMENT ‘待支付,货到付款待支付,已支付,货到付款已支付’,
STATUS_TOTAL varchar(32) DEFAULT NULL COMMENT ‘总状态’,
CLIENT_SERVICE_REMARK varchar(500) DEFAULT NULL COMMENT ‘客服备注’,
REMARK varchar(500) DEFAULT NULL COMMENT ‘备注’,
ORDER_CATEGORY varchar(32) DEFAULT NULL COMMENT ‘订单大类,销售,退货(已收已付),换货送新(出库),换货拖旧(意向),拒收’,
ORDER_RELATED_ORIGIN varchar(32) DEFAULT NULL COMMENT ‘退/换货关联的原单号,换货关联的最近换货记录或原单’,
BILL_TYPE int(11) DEFAULT NULL COMMENT ‘单据类型 1:正向订单 ; -1:逆向订单’,
IS_DELETED int(11) DEFAULT 0,
CREATED_BY varchar(64) DEFAULT NULL,
UPDATED_BY varchar(64) DEFAULT NULL,
DATE_CREATED datetime(6) DEFAULT NULL,
DATE_UPDATED datetime(6) DEFAULT NULL,
CLIENT_REMARK varchar(500) DEFAULT NULL COMMENT ‘顾客备注’,
CHGOUT_ORDER_NO varchar(20) DEFAULT NULL COMMENT ‘换货意向单所产生的出库单’,
SALESCLERK_NO varchar(64) DEFAULT NULL COMMENT ‘导购编号’,
MEMBER_SHOPPING_GUIDE varchar(64) DEFAULT NULL COMMENT ‘会员归属导购’,
SALE_STORE_CODE varchar(64) DEFAULT NULL COMMENT ‘销售门店code’,
SALE_STORE_NAME varchar(64) DEFAULT NULL COMMENT ‘销售门店名称’,
SALE_COMPANY_CODE varchar(64) DEFAULT NULL COMMENT ‘销售门店归属公司’,
SALE_COMPANY_NAME varchar(128) DEFAULT NULL COMMENT ‘销售门店归属公司名称’,
SHIP_STORE_CODE varchar(64) DEFAULT NULL COMMENT ‘(发货)(收货)门店’,
SHIP_COMPANY_CODE varchar(64) DEFAULT NULL COMMENT ‘(发货)门店归属公司’,
SHIP_COMPANY_NAME varchar(128) DEFAULT NULL COMMENT ‘(发货)门店归属公司名称’,
SHIP_STORE_NAME varchar(64) DEFAULT NULL COMMENT ‘发货门店名称’,
PERFORM_STORE_CODE varchar(64) DEFAULT NULL COMMENT ‘业绩归属门店’,
SALESCLERK_PERFORM decimal(20,2) DEFAULT NULL COMMENT ‘销售员业绩’,
BALANCE_DATE datetime DEFAULT NULL COMMENT ‘结算日期’,
TOTAL_PRODUCT_COUNT int(11) DEFAULT NULL COMMENT ‘商品总数’,
SEND_SAP varchar(4) DEFAULT ‘0’ COMMENT '是否同步至sap(定时任务使用) 0:未同步 1:已同步 ',
REGION_CODE varchar(255) DEFAULT NULL COMMENT ‘区域编码’,
PAY_TIME datetime DEFAULT NULL COMMENT ‘支付时间’,
COMPENSATION_TOTAL_AMOUNT decimal(20,2) DEFAULT NULL COMMENT ‘补差金额(行补差价总金额)’,
COMPENSATION_TOTAL_GIVE_POINT decimal(20,2) DEFAULT NULL COMMENT ‘赠送积分(行使用积分总金额)’,
CANCEL_TIME datetime DEFAULT NULL COMMENT ‘取消时间’,
REFUSE_REASON varchar(500) DEFAULT NULL,
REFUSE_URL varchar(500) DEFAULT NULL,
CHG_ORDER_NO varchar(20) DEFAULT NULL COMMENT ‘换货出库单关联逆向单号’,
TOTAL_VOUCHER decimal(20,2) DEFAULT NULL COMMENT ‘订单用代金券’,
STORE_CODE varchar(12) DEFAULT NULL,
COMPANY_CODE varchar(4) DEFAULT NULL,
ZONE_CODE varchar(10) DEFAULT NULL,
PERFORMANCE_SAP varchar(2) DEFAULT ‘0’ COMMENT '是否同步至sap(定时任务使用) 0:未同步 1:已同步 ',
SALE_STORE_ADDR varchar(300) DEFAULT NULL COMMENT ‘门店地址’,
TOTAL_CALCULATE_AMOUNT decimal(20,2) DEFAULT NULL COMMENT ‘结算价格’,
IS_SUPERVISION varchar(5) DEFAULT NULL COMMENT ‘是否是门店督导退单’,
PRODUCT_STATUS int(1) DEFAULT NULL COMMENT ‘货物状态 0 未收到货 1已收到货’,
IS_EXCHANGE varchar(5) DEFAULT NULL,
PERFORM_STORE_NAME varchar(64) DEFAULT NULL COMMENT ‘绩效门店名称’,
SALE_STORE_PHONE varchar(16) DEFAULT NULL COMMENT ‘门店联系方式’,
TOTAL_OUT_COUPON decimal(20,2) DEFAULT NULL COMMENT ‘外部优惠券’,
PRIMARY_CHANNEL varchar(32) DEFAULT NULL COMMENT ‘一级渠道’,
SECONDARY_CHANNEL varchar(32) DEFAULT NULL COMMENT ‘二级渠道’,
IS_OMNI_CHANNEL int(3) DEFAULT NULL COMMENT ‘是否为全渠道订单,0否,1是’,
DISTRIBUTE_TYPE varchar(32) DEFAULT NULL,
LEAD_GUIDE_CODE varchar(32) DEFAULT NULL COMMENT ‘分享导购’,
BIND_STORE_CODE varchar(32) DEFAULT NULL COMMENT ‘绑定门店编码,微信专属’,
BIND_STORE_NAME varchar(32) DEFAULT NULL COMMENT ‘绑定门店名称,微信专属’,
SALE_COMPANY_BAND_CODE varchar(32) DEFAULT NULL COMMENT ‘销售门店所属分公司品牌’,
WX_TYPE varchar(4) DEFAULT NULL COMMENT ‘总店:0,旗舰店:1,子商城:2’,
ACTIVITY_INVENTORY varchar(4) DEFAULT NULL COMMENT ‘直播设置,活动库存:1开启 0关闭’,
AUTO_SOURCING varchar(4) DEFAULT NULL COMMENT ‘直播设置,自动寻源:1开启 0关闭’,
LIVE_ROOM varchar(64) DEFAULT NULL COMMENT ‘直播设置,直播房间ID’,
ORDERED varchar(3) DEFAULT NULL COMMENT ‘1:以前下过单,0:没有下过单’,
PRIMARY KEY (ID),
KEY om_orderNo (ORDER_NO),
KEY sale_store_code_index (SALE_STORE_CODE),
KEY balance_date_index (BALANCE_DATE),
KEY perform_store_code_index (PERFORM_STORE_CODE),
KEY order_no_p_index (ORDER_NO_P),
KEY om_pay_time_list (STATUS_PAY,PERFORM_STORE_CODE,PAY_TIME),
KEY IN_S_O (STATUS_TOTAL,ORDER_SOURCE),
KEY DATE_CREATED_index (DATE_CREATED),
KEY Order_Related_Origin_index (ORDER_RELATED_ORIGIN),
KEY index_SHIP_STORE_CODE (STATUS_TOTAL,ORDER_CATEGORY,SHIP_STORE_CODE),
KEY om_sale_no (STATUS_TOTAL,ORDER_CATEGORY,PERFORM_STORE_CODE),
KEY member_no_index (MEMBER_NO),
KEY o2o_order_index (ORDER_SOURCE,STATUS_PAY,IS_OMNI_CHANNEL)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT=‘订单头,包括实体商品订单头,虚拟商品订单头共用’;

查询SQL:
SELECT
ORDER_NO
FROM
order_main om
WHERE
om.STATUS_PAY = ‘0420’
AND om.PERFORM_STORE_CODE = ‘D497’
AND om.SHIP_STORE_CODE = ‘D497’
AND date_format( BALANCE_DATE, ‘%Y%m%d’ ) = ‘20210420’
AND ( om.SEND_SAP = 0 OR om.SEND_SAP IS NULL )
AND om.IS_DELETED != 1
AND om.ORDER_SOURCE = ‘POS’;


若提问为性能优化、故障排查类问题,请下载脚本运行。终端输出的打印结果,请务必全选并复制粘贴上传。

先收集一下执行计划,analyze table xxx

这个是异常情况下的慢日志,执行计划没有截图。

上面的 explain analyze 是 收集完 统计信息之后的结果还是未收集前的?(看起来执行时间是ok 的)

慢日志看起来,扫描及处理的 key 的数量很多,还是建议收集一下统计信息(这里奇怪的地方是:你同样的条件数量好像差距有点大,慢日志中应该有当时的执行计划吧,v4版本应该有了的,你可以看下当时的执行计划)

explain是正常的执行结果,慢日志里是异常的执行结果。sql是完全一样的。慢日志中关闭了打印执行计划,日志太大了。

异常情况复现了,走了另外一个索引。

  1. 使用 SPM 绑定 https://docs.pingcap.com/zh/tidb/stable/sql-plan-management#执行计划管理-spm
  2. 使用 hint 强制走索引 https://docs.pingcap.com/zh/tidb/stable/optimizer-hints#optimizer-hints
  3. 升级到高版本,统计信息这里都在不断优化,会降低出现的概率。在新版本如果还有问题,可以看下表的统计信息是否健康。或者这个表的数据量变化很频繁,导致统计信息不准。