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.tradeTypeFROM  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后面的列最后要有筛选条件比较好的索引；