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后面的列最后要有篩選條件比較好的索引；