接口性能优化评审文档
接口地址：api/productInvest/queryProductInvestRecord
jz_product_invest_record添加索引：
ALTER TABLE `jz_product_invest_record` ADD INDEX ix_pir_productNo (`productNo`);
原SQL：
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
现SQL：
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
数据行数为113行,未考虑索引的情况下：
接口地址：/api/product/queryProductDetail
ALTER TABLE `jz_file_business_rl` ADD INDEX ix_fbr_BustypeBusid(`businessType`,`businessId`);
接口地址：/api/boCake/getGameInfo
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`);
原SQL：
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‘
现SQL：
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` pr
WHERE r.cakeId = ‘2016091400000453‘
AND r.`openId` = wui.`openId`
AND r.`openId` = pr.`openId`
AND r.`id` = pr.`sourceId`
AND pr.`activityType` = ‘ZQHD_BOCAKE‘
原文：http://www.cnblogs.com/caogenit/p/5889068.html