慢查优化子查询默认hashjoin导致全索引扫描

CREATE TABLE `customer_risk` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `customer_id` bigint(20) unsigned NOT NULL COMMENT '客户id,对应用户系统的account_id',
  `risk_item` smallint(2) NOT NULL COMMENT '风险项',
  `risk_type` smallint(2) NOT NULL COMMENT '风险类型',
  `risk_value` varchar(128) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '风险项对应的值,混合型',
  `reason` int(5) NOT NULL COMMENT '上报/解除原因',
  `relieve_reason` int(5) NOT NULL DEFAULT '0' COMMENT '解除原因',
  `report_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '上报操作备注',
  `review_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '审核操作备注',
  `relieve_remark` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '解除操作备注',
  `op_uid` bigint(20) unsigned NOT NULL COMMENT '操作员uid',
  `is_deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录是否被标记为删除',
  `status` tinyint(1) NOT NULL DEFAULT '0' COMMENT '状态: 待审核 0, 已审核 1, 审核拒绝 2',
  `ctime` bigint(20) NOT NULL COMMENT '记录创建时间',
  `utime` bigint(20) NOT NULL COMMENT '记录最后更新时间',
  `review_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '审核时间',
  `relieve_time` bigint(20) NOT NULL DEFAULT '0' COMMENT '手动解除时间',
  `order_ids` text COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT '涉及的订单id， 逗号分隔',
  `user_account_ids` text COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '涉及的用户id， 逗号分隔',
  `aes_risk_value` varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '' COMMENT 'aes_risk_value',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `valid_risk_value` (`is_deleted`,`status`,`risk_value`),
  KEY `cid_ri_d` (`customer_id`,`risk_item`),
  KEY `id_risk_value` (`risk_value`),
  KEY `idx_ctime` (`ctime`),
  KEY `idx_aes_risk_value` (`aes_risk_value`),
  KEY `idx_aes_risk_value_risk_type_risk_item` (`aes_risk_value`,`risk_type`,`risk_item`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci AUTO_INCREMENT=7876500 COMMENT='客户风险管理'