Case：被驱动表没有使用索引导致性能差

问题

开发有一条insert sql 是定时任务，内容如下， 每天需要约执行5-10次，笔者从select部分开始跑了一会没有结果，就放弃了，效率很差.

INSERT INTO bs_sf_yd_flow_check (
  `merchantNo`,
  `sfBusinessId`,
  `sfMerOrderId`,
  `sfTradeTime`,
  `sfTradeAmount`,
  `sfDebitAmount`,
  `sfCreditAmount`,
  `sfBalance`,
  `sfAccNo`,
  `ydBusinessId`,
  `ydMerOrderId`,
  `ydTradeTime`,
  `ydTradeAmount`,
  `ydDebitAmount`,
  `ydCreditAmount`,
  `ydBalance`,
  `ydAccNo`,
  `tradeType`,
  `status`,
  `checkStatus`,
  `account_date`,
  `checkTime`,
  `createTime`,
  `updateTime`,
  `sfTradeType`
)
SELECT
  sf.merchantNo,
  sf.businessId,
  sf.merOrderId,
sf.completeTime,
  sf.tradeAmount,
  sf.debitAmount,
  sf.creditAmount,
  sf.balance,
  sf.accNo,
  mr.businessId,
  mr.localOrderId,
  mr.tradeTime,
  CASE
    WHEN mr.fromUserId = '116'
    THEN mr.amount * - 1
    ELSE mr.amount
  END AS ydTradeAmount,
  CASE
    WHEN mr.fromUserId = '116'
    THEN mr.amount
    WHEN mr.toUserId = '116'
    THEN 0
  END AS ydDebitAmount,
  CASE
    WHEN mr.fromUserId = '116'
    THEN 0
    WHEN mr.toUserId = '116'
    THEN mr.amount
  END AS ydCreditAmount,
  mr.accountBalance,
  IFNULL(
    CASE
      WHEN mr.fromUserId = '116'
      THEN mr.fromUserId
      WHEN mr.toUserId = '116'
      THEN mr.toUserId
    END,
    ''
  ) AS ydAccNo,
  mr.tradeType,
  0,
  CASE
    WHEN ABS(sf.tradeAmount) = mr.amount
    THEN 3
    WHEN mr.amount IS NULL
    THEN 6
    ELSE 5
  END AS checkStatus,
  sf.tradeTime,
  NOW(),
  NOW(),
  NOW(),
  sf.tradeType
FROM
  bs_sf_flow sf  
  LEFT JOIN money_record mr
    ON mr.bussflowno = sf.merOrderId
    AND sf.tradeTime = DATE_FORMAT(mr.tradeTime, '%Y-%m-%d')
    AND mr.ischeck = 1
    AND (
      mr.fromUserId = '116'
      OR mr.toUserId = '116'
    )
WHERE sf.tradeTime = '20161212'
  AND sf.accNo = '00620000000010269449'

两个表的表结构如下

SF：
CREATE TABLE `bs_sf_flow` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `businessId` varchar(40) NOT NULL COMMENT '账户流水号',
  `merchantNo` varchar(40) NOT NULL COMMENT '商户号',
  `merOrderId` varchar(40) NOT NULL COMMENT '商户订单号',
  `completeTime` datetime DEFAULT NULL COMMENT '订单完成时间',
  `tradeAmount` decimal(14,2) DEFAULT NULL COMMENT '交易金额',
  `debitAmount` decimal(14,2) DEFAULT NULL COMMENT '借方发生额',
  `creditAmount` decimal(14,2) DEFAULT NULL COMMENT '贷方发生额',
  `balance` decimal(14,2) DEFAULT NULL COMMENT '虚拟账户余额',
  `accNo` varchar(40) NOT NULL COMMENT '虚拟账户账号',
  `tradeType` varchar(40) NOT NULL COMMENT '业务类型',
  `status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '状态0:初始',
  `createTime` datetime NOT NULL COMMENT '创建时间',
  `updateTime` datetime DEFAULT NULL COMMENT '更新时间',
  `merPlatAcctAlias` varchar(32) DEFAULT NULL COMMENT '商户平台收款账户别名,平台开立的账户账号别名，当商户开立多个账户时，必输',
  `merPlatAcctNo` varchar(80) DEFAULT NULL COMMENT '平台账户别名对应的账号',
  `tradeTime` date NOT NULL COMMENT '账务日期',
  PRIMARY KEY (`id`),
  KEY `idx_merOrderId` (`merOrderId`),
  KEY `idx_tradeTime` (`tradeTime`,`accNo`)
) ENGINE=InnoDB AUTO_INCREMENT=1502748 DEFAULT CHARSET=utf8 COMMENT='三方流水表';


MR：
CREATE TABLE `money_record` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `toUserId` int(10) unsigned DEFAULT NULL COMMENT '资金汇入者的userid',
  `toUserNickname` varchar(100) DEFAULT NULL COMMENT '资金汇入者的昵称',
  `fromUserId` int(10) unsigned DEFAULT NULL COMMENT '资金汇出者的userid',
  `fromUserNickname` varchar(100) DEFAULT NULL COMMENT '资金汇出者的昵称',
  `amount` decimal(14,2) NOT NULL COMMENT '交易金额金额',
  `accountBalance` decimal(14,2) NOT NULL COMMENT '此交易完成后的账户余额',
  `businessId` varchar(50) NOT NULL DEFAULT '' COMMENT '关联的业务id，如充值订单号，提现批次号等',
  `tradeType` smallint(5) unsigned NOT NULL COMMENT '资金来往类型，小于1000的都是汇入，即资金增加；大于1000的都是汇出，即资金减少。详情见CommonDef中TRADE_TYPE_*',
  `tradeTime` datetime NOT NULL COMMENT '交易发生时间',
  `tradeChannel` smallint(5) unsigned DEFAULT NULL COMMENT '交易渠道',
  `tradeComment` varchar(300) DEFAULT NULL COMMENT '交易备注',
  `loanId` int(10) unsigned DEFAULT NULL COMMENT '关联的loanId',
  `loanTitle` varchar(64) DEFAULT NULL COMMENT '标名称',
  `loanPortraitPath` varchar(64) DEFAULT NULL COMMENT '标头像图片的路径',
  `bussflowno` varchar(128) DEFAULT NULL COMMENT '三方订单号',
  `localOrderId` varchar(128) DEFAULT NULL COMMENT '本地业务id',
  `ischeck` smallint(2) DEFAULT '1' COMMENT '是否与三方对账，0不对，1对',
  `froMaccBalance` decimal(14,2) DEFAULT NULL COMMENT '转出账户交易后余额',
  `toAccBalance` decimal(14,2) DEFAULT NULL COMMENT '转入账户处理后余额',
  `batchNo` varchar(32) DEFAULT NULL COMMENT '交易批次号',
  `originUserId` int(10) DEFAULT '0' COMMENT '交易来源用户',
  `updateTime` datetime DEFAULT NULL COMMENT '更新时间',
  `projectId` varchar(32) DEFAULT NULL COMMENT '项目ID',
  `tradePlatformType` smallint(5) unsigned DEFAULT '0' COMMENT '流水平台类型0：三方；10：托管；20：三方至托管；30：托管至三方；',
  PRIMARY KEY (`id`),
  KEY `fk_money_record_toUserId` (`toUserId`),
  KEY `idx_money_record_fromUserId` (`fromUserId`),
  KEY `idx_money_record_tradeTime` (`tradeTime`),
  KEY `idx_money_record_businessId` (`businessId`),
  KEY `idx_tradeType` (`tradeType`,`loanId`),
  KEY `idx_updateTime` (`updateTime`,`amount`)
) ENGINE=InnoDB AUTO_INCREMENT=7166457 DEFAULT CHARSET=utf8

分析处理

单独看select部分的执行计划如下，可以发现被驱动表mr使用的是mr.fromUserId和mr.toUserId列上的索引，但是这两个索引作为删选条件并不好，rows有150多万行；

最好的情况是使用连接条件on mr.bussflowno = sf.merOrderId中mr.bussflowno列的索引，所以建议在mr.bussflowno上创建一个普通索引。

创建索引之后的执行计划如下，rows变为1，type从index_merge变成等值查询reg，只需0.2s便跑出select结果。

总结

被驱动表的连接条件on后面一定要有索引；

驱动表where后面的列最后要有筛选条件比较好的索引；