问题
开发有一条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后面的列最后要有筛选条件比较好的索引;
亿速云「云服务器」,即开即用、新一代英特尔至强铂金CPU、三副本存储NVMe SSD云盘,价格低至29元/月。点击查看>>
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。