为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：
【TiDB 版本】
Release Version: v4.0.0-alpha-206-gc1d553653
Git Commit Hash: c1d5536530c02d49943d98594fed048b8960c49b
Git Branch: master
UTC Build Time: 2019-09-09 05:14:45
GoVersion: go version go1.12 linux/amd64
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
【问题描述】
复现方式：
a表：
CREATE TABLE xjsc_order_detail_wide_v1 (
order_detail_id decimal(19,0) NOT NULL COMMENT ‘订单明细id’,
order_detail_order_detail_no varchar(64) DEFAULT NULL COMMENT ‘订单明细号’,
order_detail_order_type varchar(64) DEFAULT NULL COMMENT ‘订单明细业态’,
order_detail_buyer_acc_type varchar(64) DEFAULT NULL COMMENT ‘用户类型’,
order_detail_order_sum decimal(11,2) DEFAULT NULL COMMENT ‘订单最终金额’,
order_detail_order_info varchar(256) DEFAULT NULL COMMENT ‘订单明细’,
order_detail_check_status char(1) DEFAULT NULL COMMENT ‘核销状态’,
order_detail_price_settle decimal(11,2) DEFAULT NULL COMMENT ‘产品结算价’,
order_detail_price_show decimal(11,2) DEFAULT NULL COMMENT ‘产品门市价’,
order_detail_price decimal(11,2) DEFAULT NULL COMMENT ‘产品售卖价’,
order_detail_point decimal(11,0) DEFAULT NULL COMMENT ‘单产品积分’,
order_detail_goods_depend_id decimal(11,0) DEFAULT NULL COMMENT ‘产品所属业态id’,
order_detail_goods_id decimal(11,0) DEFAULT NULL COMMENT ‘产品id’,
order_detail_model_code varchar(64) DEFAULT NULL COMMENT ‘产品编码’,
order_detail_goods_code varchar(64) DEFAULT NULL COMMENT ‘产品码唯一编码’,
order_detail_goods_type varchar(64) DEFAULT NULL COMMENT ‘产品类型’,
order_detail_stock_model varchar(64) DEFAULT NULL COMMENT ‘库存模式’,
order_detail_amount decimal(11,0) DEFAULT NULL COMMENT ‘购买数量’,
order_detail_check_amount decimal(11,0) DEFAULT NULL COMMENT ‘检票数量’,
order_detail_refund_amount decimal(11,0) DEFAULT NULL COMMENT ‘部分退单数量’,
order_detail_left_amount decimal(11,0) DEFAULT NULL COMMENT ‘检票退单后剩余数量’,
order_detail_start_date datetime DEFAULT NULL COMMENT ‘有效开始时间’,
order_detail_end_date datetime DEFAULT NULL COMMENT ‘有效结束时间’,
order_detail_confirm_status varchar(64) DEFAULT NULL COMMENT ‘订单明细确认状态’,
order_detail_pay_sum decimal(11,2) DEFAULT NULL COMMENT ‘支付金额’,
order_detail_settle_sum decimal(11,2) DEFAULT NULL COMMENT ‘订单结算金额’,
order_detail_coupon_sum decimal(11,2) DEFAULT NULL COMMENT ‘优惠金额’,
order_detail_coupon_code varchar(64) DEFAULT NULL COMMENT ‘优惠券’,
order_detail_coupon_check_code varchar(64) DEFAULT NULL COMMENT ‘优惠券核销码’,
order_detail_distributor_id decimal(11,0) DEFAULT NULL COMMENT ‘分销商id’,
order_detail_distributor_name varchar(64) DEFAULT NULL COMMENT ‘分销商名称’,
order_detail_supplier_id decimal(11,0) DEFAULT NULL COMMENT ‘供应商id’,
order_detail_supplier_name varchar(64) DEFAULT NULL COMMENT ‘供应商名称’,
order_detail_create_by varchar(64) DEFAULT NULL COMMENT ‘创建人’,
order_detail_create_time datetime DEFAULT NULL COMMENT ‘创建时间’,
order_detail_modify_by varchar(64) DEFAULT NULL COMMENT ‘修改人’,
order_detail_modify_time datetime DEFAULT NULL COMMENT ‘修改时间’,
order_detail_deleted char(1) DEFAULT NULL COMMENT ‘删除标志’,
order_detail_goods_depend_code varchar(64) DEFAULT NULL COMMENT ‘产品业态编码’,
order_detail_refund_status varchar(64) DEFAULT NULL COMMENT ‘退款流程状态’,
order_detail_refund_audit varchar(64) DEFAULT NULL COMMENT ‘退款审核状态’,
order_detail_refund_way varchar(64) DEFAULT NULL COMMENT ‘退款方式’,
order_detail_channel_code varchar(64) DEFAULT NULL COMMENT ‘渠道码’,
order_detail_detail_type decimal(11,0) DEFAULT NULL COMMENT ‘针对线路订单detail类型’,
order_detail_ota_order_detail_id decimal(11,0) DEFAULT NULL COMMENT ‘ota订单明细id’,
order_detail_confirm_time_out datetime DEFAULT NULL COMMENT ‘明细失效时间’,
order_info_id decimal(11,0) DEFAULT NULL COMMENT ‘订单id’,
order_info_order_pay_id decimal(11,0) DEFAULT NULL COMMENT ‘支付订单id’,
order_info_advance_detail_code varchar(64) DEFAULT NULL COMMENT ‘预售详情编码’,
order_info_pay_order_no varchar(64) DEFAULT NULL COMMENT ‘支付订单号’,
order_info_account_type varchar(64) DEFAULT NULL COMMENT ‘下单账户类型’,
order_info_order_no varchar(64) DEFAULT NULL COMMENT ‘订单号’,
order_info_external_order_no varchar(64) DEFAULT NULL COMMENT ‘外部订单号’,
order_info_order_type varchar(64) DEFAULT NULL COMMENT ‘订单业态’,
order_info_bus_type varchar(64) DEFAULT NULL COMMENT ‘B端/C端’,
order_info_way_type varchar(64) DEFAULT NULL COMMENT ‘订单来源’,
order_info_pay_type varchar(64) DEFAULT NULL COMMENT ‘支付方式’,
order_info_sales_type varchar(64) DEFAULT NULL COMMENT ‘销售类型’,
order_info_marketing_type varchar(64) DEFAULT NULL COMMENT ‘活动类型’,
order_info_team_type varchar(64) DEFAULT NULL COMMENT ‘团队类型’,
order_info_pay_status char(1) DEFAULT NULL COMMENT ‘支付状态’,
order_info_pay_time datetime DEFAULT NULL COMMENT ‘支付时间’,
order_info_pay_form varchar(64) DEFAULT NULL COMMENT ‘支付类型’,
order_info_order_sum decimal(11,2) DEFAULT NULL COMMENT ‘订单金额’,
order_info_pay_sum decimal(11,2) DEFAULT NULL COMMENT ‘支付金额’,
order_info_final_pay_sum decimal(11,2) DEFAULT NULL COMMENT ‘订单最终金额’,
order_info_settle_sum decimal(11,2) DEFAULT NULL COMMENT ‘订单结算金额’,
order_info_postage decimal(11,2) DEFAULT NULL COMMENT ‘邮费’,
order_info_price decimal(11,2) DEFAULT NULL COMMENT ‘产品单价’,
order_info_amount decimal(11,0) DEFAULT NULL COMMENT ‘购买数量’,
order_info_coupon_sum decimal(11,2) DEFAULT NULL COMMENT ‘优惠金额’,
order_info_coupon_code varchar(64) DEFAULT NULL COMMENT ‘优惠劵’,
order_info_coupon_check_code varchar(64) DEFAULT NULL COMMENT ‘优惠券核销码’,
order_info_order_status varchar(64) DEFAULT NULL COMMENT ‘订单状态’,
order_info_group_status varchar(64) DEFAULT NULL COMMENT ‘平图案状态’,
order_info_group_id decimal(11,0) DEFAULT NULL COMMENT ‘店铺id’,
order_info_check_status varchar(64) DEFAULT NULL COMMENT ‘核销状态’,
order_info_confirm_status varchar(64) DEFAULT NULL COMMENT ‘二次确认状态’,
order_info_order_info varchar(256) DEFAULT NULL COMMENT ‘订单信息’,
order_info_remark varchar(1024) DEFAULT NULL COMMENT ‘备注’,
order_info_start_time datetime DEFAULT NULL COMMENT ‘开始时间 （游玩时间，入驻时间）’,
order_info_end_time datetime DEFAULT NULL COMMENT ‘结束时间 （离店时间）’,
order_info_goods_name varchar(256) DEFAULT NULL COMMENT ‘产品码名称’,
order_info_goods_code varchar(64) DEFAULT NULL COMMENT ‘产品码编码’,
order_info_model_code varchar(64) DEFAULT NULL COMMENT ‘产品码唯一编码’,
order_info_nick_name varchar(256) DEFAULT NULL COMMENT ‘别名’,
order_info_link_credential_type varchar(64) DEFAULT NULL COMMENT ‘订单联系人证件类型’,
order_info_link_idcard varchar(64) DEFAULT NULL COMMENT ‘订单联系人证件证号’,
order_info_link_name varchar(64) DEFAULT NULL COMMENT ‘订单联系人名称’,
order_info_link_mobile varchar(64) DEFAULT NULL COMMENT ‘订单联系人手机号码’,
order_info_link_addr varchar(255) DEFAULT NULL COMMENT ‘订单联系人地址’,
order_info_buyer_id bigint(64) DEFAULT NULL COMMENT ‘买家用户ID’,
order_info_buyer_name varchar(64) DEFAULT NULL COMMENT ‘买家用户名称’,
order_info_distributor_id decimal(11,0) DEFAULT NULL COMMENT ‘分销商ID’,
order_info_distributor_name varchar(64) DEFAULT NULL COMMENT ‘分销商名称’,
order_info_operator_id bigint(64) DEFAULT NULL COMMENT ‘操作员id’,
order_info_operator_name varchar(64) DEFAULT NULL COMMENT ‘操作员名’,
order_info_supplier_id decimal(11,0) DEFAULT NULL COMMENT ‘供应商ID’,
order_info_supplier_name varchar(64) DEFAULT NULL COMMENT ‘供应商名’,
order_info_merchant_code varchar(64) DEFAULT NULL COMMENT ‘店铺编码’,
order_info_merchant_name varchar(64) DEFAULT NULL COMMENT ‘店铺名称’,
order_info_merchant_owner_id decimal(11,0) DEFAULT NULL COMMENT ‘店铺所有人ID’,
order_info_merchant_info_id decimal(11,0) DEFAULT NULL COMMENT ‘店铺id’,
order_info_main_user_info_id decimal(11,0) DEFAULT NULL COMMENT ‘主体id’,
order_info_create_by varchar(64) DEFAULT NULL COMMENT ‘创建人’,
order_info_create_time datetime DEFAULT NULL COMMENT ‘创建时间’,
order_info_modify_by varchar(64) DEFAULT NULL COMMENT ‘修改人’,
order_info_modify_time datetime DEFAULT NULL COMMENT ‘修改时间’,
order_info_deleted varchar(64) DEFAULT NULL COMMENT ‘删除标志’,
order_info_client_deleted varchar(64) DEFAULT NULL COMMENT ‘C端删除标志’,
order_info_check_no varchar(64) DEFAULT NULL COMMENT ‘检票号’,
order_info_promote_code varchar(64) DEFAULT NULL COMMENT ‘全员营销推广码’,
order_info_sync_type varchar(64) DEFAULT NULL COMMENT ‘同步状态’,
order_info_order_out_time datetime DEFAULT NULL,
order_info_spec_param varchar(256) DEFAULT NULL COMMENT ‘商品规格阐述’,
order_info_product_channel varchar(64) DEFAULT NULL COMMENT ‘产品来源渠道’,
order_info_one_code varchar(64) DEFAULT NULL COMMENT ‘是否一票一码’,
order_info_gateway_trans_no varchar(64) DEFAULT NULL COMMENT ‘网关交易号（第三方交易号）’,
user_auth_corp_name varchar(64) DEFAULT NULL COMMENT ‘企业名称’,
cost_price decimal(11,2) DEFAULT NULL COMMENT ‘成本价’,
play_time varchar(64) DEFAULT NULL COMMENT ‘演出时间’,
goods_name varchar(256) DEFAULT NULL COMMENT ‘产品中心产品名称’,
goods_deleted varchar(64) DEFAULT NULL COMMENT ‘是否删除’,
goods_enabled varchar(64) DEFAULT NULL COMMENT ‘是否上架’,
goods_create_time datetime DEFAULT NULL COMMENT ‘产品创建时间’,
goods_modify_time datetime DEFAULT NULL COMMENT ‘产品修改时间’,
resource_code varchar(64) DEFAULT NULL COMMENT ‘产品资源码’,
resource_name varchar(128) DEFAULT NULL COMMENT ‘产品资源名称’,
zyb_park_id decimal(11,0) DEFAULT NULL COMMENT ‘智游宝景区id’,
resource_deleted varchar(64) DEFAULT NULL COMMENT ‘资源是否删除’,
resource_enabled varchar(64) DEFAULT NULL COMMENT ‘资源是否上架’,
resource_create_time datetime DEFAULT NULL COMMENT ‘资源创建时间’,
resource_modify_time datetime DEFAULT NULL COMMENT ‘资源修改时间’,
product_tab_tab_type varchar(64) DEFAULT NULL COMMENT ‘产品标记’,
product_tab_deleted varchar(64) DEFAULT NULL COMMENT ‘产品标记是否删除’,
confluent__last_updated timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (order_detail_id),
KEY idx_create_time_user_info_id (order_detail_create_time,order_info_main_user_info_id),
KEY idx_order_no (order_info_order_no),
KEY idx_pay_order_no (order_info_pay_order_no),
KEY idx_order_detail_no (order_detail_order_detail_no),
KEY idx_order_detail_modify_time (order_detail_modify_time),
KEY idx_confluent__last_updated (confluent__last_updated),
KEY idx_main_user_info_id_oi_create_time (order_info_main_user_info_id,order_info_create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
b表：
CREATE TABLE lots_corp (
id bigint(20) NOT NULL,
corp_name text DEFAULT NULL,
user_info_id bigint(20) DEFAULT NULL,
area_name text DEFAULT NULL,
merchant_info_id bigint(20) DEFAULT NULL,
core_scenic_area text DEFAULT NULL,
platform_abbreviation text DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
关联查询：
explain analyze
SELECT distinct od.order_info_main_user_info_id,
lcx.user_info_id
FROM dwd.xjsc_order_detail_wide_v1 od
JOIN lots_mid.lots_corp lcx
on od.order_info_main_user_info_id = lcx.user_info_id
WHERE od.order_detail_deleted = ‘F’
and od.order_info_pay_status = ‘1’
and od.order_info_bus_type in (‘B’, ‘C’)
and od.order_info_deleted = ‘F’
and od.order_info_create_time >= ‘2021-02-01’
AND od.order_info_create_time < date_add(‘2021-02-01’, interval 1 day)
这种情况下走indexMergeJoin关联条件失效，没有查出数据
explain analyze
SELECT distinct od.order_info_main_user_info_id,
lcx.user_info_id
FROM dwd.xjsc_order_detail_wide_v1 od
JOIN lots_mid.lots_corp lcx
on od.order_info_main_user_info_id = lcx.user_info_id
WHERE od.order_detail_deleted = ‘F’
and od.order_info_pay_status = ‘1’
and od.order_info_bus_type in (‘B’, ‘C’)
and od.order_info_deleted = ‘F’
and od.order_detail_create_time >= ‘2021-02-01’
AND od.order_detail_create_time < date_add(‘2021-02-01’, interval 1 day)
这种情况下走hashJoin，有数据了
索引idx_create_time_user_info_id是准备删除的，但是删除以后走idx_main_user_info_id_oi_create_time索引出现indexMergeJoin就会没有数据
若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。
