您好,登錄后才能下訂單哦!
問題
開發(fā)有一條insert SQL 是定時任務(wù),內(nèi)容如下, 每天需要約執(zhí)行5-10次,筆者從select部分開始跑了一會沒有結(jié)果,就放棄了,效率很差.
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'
兩個表的表結(jié)構(gòu)如下
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 '借方發(fā)生額', `creditAmount` decimal(14,2) DEFAULT NULL COMMENT '貸方發(fā)生額', `balance` decimal(14,2) DEFAULT NULL COMMENT '虛擬賬戶余額', `accNo` varchar(40) NOT NULL COMMENT '虛擬賬戶賬號', `tradeType` varchar(40) NOT NULL COMMENT '業(yè)務(wù)類型', `status` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT '狀態(tài)0:初始', `createTime` datetime NOT NULL COMMENT '創(chuàng)建時間', `updateTime` datetime DEFAULT NULL COMMENT '更新時間', `merPlatAcctAlias` varchar(32) DEFAULT NULL COMMENT '商戶平臺收款賬戶別名,平臺開立的賬戶賬號別名,當(dāng)商戶開立多個賬戶時,必輸', `merPlatAcctNo` varchar(80) DEFAULT NULL COMMENT '平臺賬戶別名對應(yīng)的賬號', `tradeTime` date NOT NULL COMMENT '賬務(wù)日期', 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 '關(guān)聯(lián)的業(yè)務(wù)id,如充值訂單號,提現(xiàn)批次號等', `tradeType` smallint(5) unsigned NOT NULL COMMENT '資金來往類型,小于1000的都是匯入,即資金增加;大于1000的都是匯出,即資金減少。詳情見CommonDef中TRADE_TYPE_*', `tradeTime` datetime NOT NULL COMMENT '交易發(fā)生時間', `tradeChannel` smallint(5) unsigned DEFAULT NULL COMMENT '交易渠道', `tradeComment` varchar(300) DEFAULT NULL COMMENT '交易備注', `loanId` int(10) unsigned DEFAULT NULL COMMENT '關(guān)聯(lián)的loanId', `loanTitle` varchar(64) DEFAULT NULL COMMENT '標(biāo)名稱', `loanPortraitPath` varchar(64) DEFAULT NULL COMMENT '標(biāo)頭像圖片的路徑', `bussflowno` varchar(128) DEFAULT NULL COMMENT '三方訂單號', `localOrderId` varchar(128) DEFAULT NULL COMMENT '本地業(yè)務(wù)id', `ischeck` smallint(2) DEFAULT '1' COMMENT '是否與三方對賬,0不對,1對', `fromAccBalance` decimal(14,2) DEFAULT NULL COMMENT '轉(zhuǎn)出賬戶交易后余額', `toAccBalance` decimal(14,2) DEFAULT NULL COMMENT '轉(zhuǎn)入賬戶處理后余額', `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部分的執(zhí)行計劃如下,可以發(fā)現(xiàn)被驅(qū)動表mr使用的是mr.fromUserId和mr.toUserId列上的索引,但是這兩個索引作為刪選條件并不好,rows有150多萬行;
最好的情況是使用連接條件on mr.bussflowno = sf.merOrderId中mr.bussflowno列的索引,所以建議在mr.bussflowno上創(chuàng)建一個普通索引。
創(chuàng)建索引之后的執(zhí)行計劃如下,rows變?yōu)?,type從index_merge變成等值查詢reg,只需0.2s便跑出select結(jié)果。
總結(jié)
被驅(qū)動表的連接條件on后面一定要有索引;
驅(qū)動表where后面的列最后要有篩選條件比較好的索引;
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。