接口性能优化评审文档

ALTER TABLE `jz_product_invest_record` ADD INDEX ix_pir_productNo (`productNo`);

SELECT   r.applyNo,  r.id,  r.investAmount,  r.payDate AS buyDate,  IFNULL(r.investSource, ‘pc‘) AS investSource,  CONCAT(    LEFT(u.userName, 3),    ‘******‘,    RIGHT(u.userName, 2)  ) AS userName,  r.investmentClient,-- 快奖  (SELECT     prizeStatus   FROM    `jz_product_invest_speed` a   WHERE a.`productNo` = r.`productNo`     AND a.applyNo = r.`applyNo`     AND a.medalType = ‘first‘) AS isFirst,--     豪奖  (SELECT     prizeStatus   FROM    `jz_product_invest_speed` a   WHERE a.`productNo` = r.`productNo`     AND a.applyNo = r.`applyNo`     AND a.medalType = ‘wealthy‘) AS isWealthy,-- 准奖  (SELECT     prizeStatus   FROM    `jz_product_invest_speed` a   WHERE a.`productNo` = r.`productNo`     AND a.applyNo = r.`applyNo`     AND a.medalType = ‘last‘) AS isLast,--     当前最高  (SELECT     ‘Y‘   FROM    DUAL   WHERE r.`applyNo` =     (SELECT       jpir.`applyNo`     FROM      jz_product_invest_record jpir     WHERE jpir.`productNo` = r.`productNo`       AND jpir.`investStatus` IN (‘PAYED‘, ‘CHECKED‘, ‘SETTLED‘)     ORDER BY jpir.`investAmount` DESC,      jpir.`payDate` ASC     LIMIT 0, 1)     AND EXISTS     (SELECT       1     FROM      jz_product_invest_speed     WHERE productNo = ‘SLJ2016060500000233‘      AND medalType = ‘wealthy‘       AND prizeStatus = ‘INIT‘)) AS isCurrentMax FROM  jz_product_invest_record r   LEFT JOIN jz_user u     ON r.uid = u.id WHERE r.investStatus IN (‘PAYED‘, ‘CHECKED‘, ‘SETTLED‘)   AND r.productNo = ‘SLJ2016060500000233‘ORDER BY isWealthy DESC,  isCurrentMax DESC,  isFirst DESC,  isLast DESC,  r.payDate DESC 

SELECT   r.applyNo,  r.id,  r.investAmount,  r.payDate AS buyDate,  IFNULL(r.investSource, ‘pc‘) AS investSource,  CONCAT(    LEFT(u.userName, 3),    ‘******‘,    RIGHT(u.userName, 2)  ) AS userName,  r.investmentClient,  -- 快奖  (    IF(      p.medalType = ‘first‘,      p.`prizeStatus`,      NULL    )  ) AS isFirst,  -- 豪奖  (    IF(      p.medalType = ‘wealthy‘,      p.`prizeStatus`,      NULL    )  ) AS isWealthy,  -- 准奖  (    IF(      p.medalType = ‘last‘,      p.`prizeStatus`,      NULL    )  ) AS isLast,  -- 当前最高  (SELECT     ‘Y‘   FROM    DUAL   WHERE r.`applyNo` =     (SELECT       jpir.`applyNo`     FROM      jz_product_invest_record jpir     WHERE jpir.`productNo` = ‘SLJ2016060500000233‘       AND jpir.`investStatus` IN (‘PAYED‘, ‘CHECKED‘, ‘SETTLED‘)     ORDER BY jpir.`investAmount` DESC,      jpir.`payDate` ASC     LIMIT 0, 1)     AND EXISTS     (SELECT       1     FROM      jz_product_invest_speed     WHERE productNo = ‘SLJ2016060500000233‘       AND medalType = ‘wealthy‘       AND prizeStatus = ‘INIT‘)) AS isCurrentMax FROM  jz_product_invest_record r   LEFT JOIN jz_user u     ON r.uid = u.id   LEFT JOIN     (SELECT       pis.`applyNo`,      pis.`prizeStatus`,      pis.`medalType`     FROM      `jz_product_invest_speed` pis     WHERE pis.`productNo` = ‘SLJ2016060500000233‘) p     ON p.applyNo = r.`applyNo` WHERE r.investStatus IN (‘PAYED‘, ‘CHECKED‘, ‘SETTLED‘)   AND r.productNo = ‘SLJ2016060500000233‘ ORDER BY isWealthy DESC,  isCurrentMax DESC,  isFirst DESC,  isLast DESC,  r.payDate DESC 

ALTER TABLE `jz_file_business_rl` ADD INDEX ix_fbr_BustypeBusid(`businessType`,`businessId`);

ALTER TABLE `jz_activity_bo_cake_record` ADD INDEX ix_abcr_cakeId(`cakeId`);ALTER TABLE `jz_activity_prize_record` ADD INDEX ix_apr_openId(`openId`);

SELECT   w.`headImgUrl`,  w.`openId`,  r.`diceResult`,  r.`prizeDesc`,  r.`prizeNo`,  pr.`openId`,  pr.`receiveStatus`,  w.nickName AS `name`,  IF(    pr.`receiveStatus` = ‘0‘     AND pr.`openId` = ‘oVVT_swNma3fBoaRKqgWUwIKmvIo‘,    ‘true‘,    ‘false‘  ) AS unclaimed FROM  jz_activity_bo_cake_record r   INNER JOIN jz_wechat_user_info w     ON w.`openId` = r.`openId`   LEFT JOIN `jz_activity_prize_record` pr     ON pr.`openId` = r.`openId` WHERE cakeId = ‘2016091400000453‘   AND pr.`sourceId` = r.`id`   AND pr.`activityType` = ‘ZQHD_BOCAKE‘ 

SELECT   wui.`headImgUrl`,  wui.`openId`,  r.`diceResult`,  r.`prizeDesc`,  r.`prizeNo`,  pr.`receiveStatus`,  wui.nickName AS `name`,  IF(    pr.`receiveStatus` = ‘0‘,    ‘true‘,    ‘false‘  ) AS unclaimed FROM  jz_activity_bo_cake_record r,  jz_wechat_user_info wui,  `jz_activity_prize_record` prWHERE r.cakeId = ‘2016091400000453‘   AND r.`openId` = wui.`openId`   AND r.`openId` = pr.`openId`   AND r.`id` = pr.`sourceId`   AND pr.`activityType` = ‘ZQHD_BOCAKE‘