Shulou(Shulou.com)06/01 Report--

problem

The development has an insert SQL is a scheduled task, the content is as follows, need to execute about 5-10 times a day, the author starts from the select part to run for a while without results, then give up, the efficiency is very poor.

INSERT INTO bs_sf_yd_flow_check (`pragantNo`, `sfBusinessId`, `sfMerOrderId`, `sfTradeTime`, `sfTradeAmount`, `sfDebitAmount`, `sfCreditAmount`, `sfBalance`, `sfAccNo`, `ydBusinessId`, `ydMerderId`, `ydTradeTime`, `ydTradeAmount`, `ydTradeAmount`, `ydCreditAmount`, `ydCreditAmount`, `ydBalance`, `ydAccNo`, `tradeType`, `status`, `checkStatus`, `account_ date`, `checkTime`, Time`, update`, sTydeTrape`), sf.businessId, sf.tradeAmount, 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,'% Ymuri% mmi% d') AND mr.ischeck = 1 AND (mr.fromUserId = '116' OR mr.toUserId =' 116') WHERE sf.tradeTime = '20161212' AND sf.accNo =' 006200000010269449'

The table structure of the two tables is as follows

SF:CREATE TABLE `business int (10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'key', `businessId` varchar (40) NOT NULL COMMENT 'account serial number', `merchantNo` varchar (40) NOT NULL COMMENT 'merchant number', `merOrderId` varchar (40) NOT NULL COMMENT 'merchant order number', `completeTime` datetime DEFAULT NULL COMMENT 'order fulfillment time', `tradeAmount` decimal (14J 2) DEFAULT NULL COMMENT 'transaction amount', `debitAmount` decimal (14L2) DEFAULT NULL COMMENT 'debit amount' `creditAmount` decimal (14recover2) DEFAULT NULL COMMENT 'credit occurrence', `balance` decimal (14Power2) DEFAULT NULL COMMENT 'virtual account balance', `accNo` varchar (40) NOT NULL COMMENT 'virtual account account', `tradeType` varchar (40) NOT NULL COMMENT 'business type', `status` smallint (5) unsigned NOT NULL DEFAULT'0' COMMENT 'status 0: initial', `createTime`creation time', `updateTime` datetime DEFAULT NULL COMMENT 'update time' `merPlatAcctAlias` varchar (32) DEFAULT NULL COMMENT 'merchant platform collection account alias, alias of the account account opened by the platform When merchants open multiple accounts, they must enter', `merPlatAcctNo` varchar (80) DEFAULT NULL COMMENT 'platform account aliases corresponding account', `tradeTime`account date', PRIMARY KEY (`id`), KEY `idx_ merOrderId` (`merOrderId`), KEY `idx_ tradeTime` (`tradeTime`, `accNo`)) ENGINE=InnoDB AUTO_INCREMENT=1502748 DEFAULT CHARSET=utf8 COMMENT=' tripartite flow meter' MR:CREATE TABLE `UserNickord` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT, `toUserId` int (10) unsigned DEFAULT NULL COMMENT 'userid', `toUserNickname` varchar (100) DEFAULT NULL COMMENT' nickname of the fund inporter, `fromUserId`int (10) unsigned DEFAULT NULL COMMENT 'userid', `fromUserNickname` varchar of the fund remitter `fromUserNickname` varchar (10) DEFAULT NULL COMMENT' nickname of the fund remitter, `amount `UserNickname` NOT NULL COMMENT 'transaction amount' `accountBalance` decimal (14recover2) NOT NULL COMMENT 'account balance after completion of this transaction', `businessId` varchar (50) NOT NULL DEFAULT''COMMENT' associated business id For example, the recharge order number, withdrawal batch number, etc., `tradeType` smallint (5) unsigned NOT NULL COMMENT 'fund flow type, those less than 1000 are remitted, that is, the capital increases. Those greater than 1000 are remitted, that is, a decrease in funds. For more information, please see TRADE_TYPE_*', `tradeTime` datetime NOT NULL COMMENT 'transaction time', `tradeChannel` smallint (5) unsigned DEFAULT NULL COMMENT 'transaction Channel', `tradeComment` varchar (300) DEFAULT NULL COMMENT 'transaction remarks', `loanId` int (10) DEFAULT NULL COMMENT 'associated loanId', `loanTitle`varchar (64) DEFAULT NULL COMMENT' title', `loanPortraitPath` varchar (64) DEFAULT NULL COMMENT 'header image path', `bussflowno` varchar (128l) DEFAULT NULL COMMENT 'tripartite order number'. `localOrderId` varchar (128) DEFAULT NULL COMMENT 'whether the local business id', `ischeck` smallint (2) DEFAULT' 1' COMMENT'is reconciled with the three parties 0 is not correct 1 pair of', `fromAccBalance` decimal (14Power2) DEFAULT NULL COMMENT 'balance after transferring out of account', `toAccBalance` decimal (14Power2) DEFAULT NULL COMMENT 'transferred balance after account processing', `batchNo` varchar (32) DEFAULT NULL COMMENT 'transaction batch number', `trading UserId` int (10) DEFAULT'0' COMMENT 'transaction source user', `updateTime`datetime DEFAULT NULL COMMENT 'update time', `projectId` varchar (32) DEFAULT NULL COMMENT 'project ID' `tradePlatformType` smallint (5) unsigned DEFAULT'0' COMMENT 'pipeline platform type 0: tripartite 10: hosting; 20: three to hosting; 30: hosting to three parties;', PRIMARY KEY (`id`), KEY `fk_money_record_ toUserId` (`toUserId`), KEY `idx_money_record_ fromUserId` (`fromUserId`), KEY `tradeTime` (`tradeTime`), KEY `idx_money_record_ businessId` (`tradebusinessId`), KEY `idx_ tradeType` (`tradeType`, `loanId`), KEY `idx_ updateTime` (`updateTime`, `amount) ENGINE=InnoDB AUTO_INCREMENT=7166457 DEFAULT CHARSET=utf8

Analysis and treatment

Looking at the execution plan of the select section alone, we can find that the driven table mr uses the indexes on mr.fromUserId and mr.toUserId columns, but these two indexes are not good conditions for deletion. Rows has more than 1.5 million rows.

The best case is to use the index of the mr.bussflowno column in the join condition on mr.bussflowno = sf.merOrderId, so it is recommended that you create a normal index on mr.bussflowno.

The execution plan after creating the index is as follows: rows changes from index_merge to equivalent query reg, and it takes only 0.2 seconds to get the select result.

Summary

The join condition on of the driven table must be followed by an index

Finally, the columns after the driver table where should have an index with better filtering conditions.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.