SQL优化

语句如下， 执行时间为1.5s， 去掉`f_credit_levels` (`t_users`.`id`) AS `num` 执行时间为0.7s(f_credit_levels上查询用户等级的函数)

SELECT SQL_CALC_FOUND_ROWS
`t_users`.`id` AS `id`,
`f_credit_levels` (`t_users`.`id`) AS `num`,
`t_users`.`name` AS `name`,
`t_users`.`time` AS `register_time`,
`t_users`.`credit_score` AS `score`,
`t_users`.`email` AS `email`,
`t_users`.`mobile` AS `mobile`,
`t_users`.`mobile1` AS `mobile1`,
`t_users`.`mobile2` AS `mobile2`,
`t_users`.`recommend_user_id` AS `recommend_user_id`,
`t_invests`.`time` AS `invest_time`,
(
SELECT
`u`.`name` AS `name`
FROM
`t_users` `u`
WHERE
(
`u`.`id` = `t_users`.`recommend_user_id`
)
) AS `recommend_user_name`,
`t_users`.`is_email_verified` AS `is_activation`,
`t_users`.`is_blacklist` AS `is_blacklist`,
(
(
`t_users`.`balance` + `t_users`.`freeze`
) + `t_users`.`balance2`
) AS `user_amount`,
`t_users`.`last_login_time` AS `last_login_time`,
`t_users`.`is_allow_login` AS `is_allow_login`,
(
SELECT
ifnull(
sum(`t_user_details`.`amount`),
0
) AS `recharge_amount`
FROM
(
`t_user_details`
JOIN `t_user_detail_types` ON (
(
`t_user_details`.`operation` = `t_user_detail_types`.`id`
)
)
)
WHERE
(
(
`t_user_details`.`user_id` = `t_users`.`id`
)
AND (
`t_user_detail_types`.`id` IN (1, 2, 3)
)
)
) AS `recharge_amount`,
count(`t_invests`.`bid_id`) AS `invest_count`,
ifnull(
sum(`t_invests`.`amount`),
0
) AS `invest_amount`,
(
SELECT
count(0) AS `bid_count`
FROM
`t_bids`
WHERE
(
`t_bids`.`user_id` = `t_users`.`id`
)
) AS `bid_count`,
(
SELECT
ifnull(sum(`t_bids`.`amount`), 0) AS `bid_count`
FROM
`t_bids`
WHERE
(
`t_bids`.`user_id` = `t_users`.`id`
)
) AS `bid_amount`,
(
SELECT
count(0) AS `audit_item_count`
FROM
`t_user_audit_items`
WHERE
(
(
`t_users`.`id` = `t_user_audit_items`.`user_id`
)
AND (
`t_user_audit_items`.`status` = 2
)
)
) AS `audit_item_count`,
(
SELECT
ifnull(
sum(
(
(
`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`
) + `t_bill_invests`.`overdue_fine`
)
),
0
) AS `receive_amount`
FROM
`t_bill_invests`
WHERE
(
(
`t_bill_invests`.`user_id` = `t_users`.`id`
)
AND (
`t_bill_invests`.`status` IN (-(1) ,-(2))
)
)
) AS `receive_amount`
FROM
`t_users`
LEFT JOIN `t_invests` ON `t_invests`.`user_id` = `t_users`.`id`
WHERE
1 = 1
GROUP BY
`t_users`.`id`
HAVING
1 = 1
ORDER BY
register_time DESC

优化后只需0.1s, 优化将t_invests的外连接改成了派生表v_invests再做连接

SELECT SQL_CALC_FOUND_ROWS
`t_users`.`id` AS `id`,
`t_users`.`name` AS `name`,
`t_users`.`time` AS `register_time`,
`t_users`.`credit_score` AS `score`,
`t_users`.`email` AS `email`,
`t_users`.`mobile` AS `mobile`,
`t_users`.`mobile1` AS `mobile1`,
`t_users`.`mobile2` AS `mobile2`,
`t_users`.`recommend_user_id` AS `recommend_user_id`,
(
SELECT
ifnull(
sum(`t_user_details`.`amount`),
0
) AS `recharge_amount`
FROM
(
`t_user_details`
JOIN `t_user_detail_types` ON (
(
`t_user_details`.`operation` = `t_user_detail_types`.`id`
)
)
)
WHERE
(
(
`t_user_details`.`user_id` = `t_users`.`id`
)
AND (
`t_user_detail_types`.`id` IN (1, 2, 3)
)
)
) AS `recharge_amount`,
(
SELECT
`u`.`name` AS `name`
FROM
`t_users` `u`
WHERE
(
`u`.`id` = `t_users`.`recommend_user_id`
)
) AS `recommend_user_name`,
`t_users`.`is_email_verified` AS `is_activation`,
`t_users`.`is_blacklist` AS `is_blacklist`,
(
(
`t_users`.`balance` + `t_users`.`freeze`
) + `t_users`.`balance2`
) AS `user_amount`,
`t_users`.`last_login_time` AS `last_login_time`,
`t_users`.`is_allow_login` AS `is_allow_login`,
`v_invests`.time,
`v_invests`.invest_amount,
`v_invests`.invest_count,
(
SELECT
count(0) AS `audit_item_count`
FROM
`t_user_audit_items`
WHERE
(
(
`t_users`.`id` = `t_user_audit_items`.`user_id`
)
AND (
`t_user_audit_items`.`status` = 2
)
)
) AS `audit_item_count`,
(
SELECT
count(0) AS `bid_count`
FROM
`t_bids`
WHERE
(
`t_bids`.`user_id` = `t_users`.`id`
)
) AS `bid_count`,
(
SELECT
ifnull(sum(`t_bids`.`amount`), 0) AS `bid_count`
FROM
`t_bids`
WHERE
(
`t_bids`.`user_id` = `t_users`.`id`
)
) AS `bid_amount`,
(
SELECT
ifnull(
sum(
(
(
`t_bill_invests`.`receive_corpus` + `t_bill_invests`.`receive_interest`
) + `t_bill_invests`.`overdue_fine`
)
),
0
) AS `receive_amount`
FROM
`t_bill_invests`
WHERE
(
(
`t_bill_invests`.`user_id` = `t_users`.`id`
)
AND (
`t_bill_invests`.`status` IN (-(1) ,-(2))
)
)
) AS `receive_amount`
FROM
`t_users`
LEFT JOIN (
SELECT
user_id,
time,
count(`t_invests`.`bid_id`) AS `invest_count`,
ifnull(
sum(`t_invests`.`amount`),
0
) AS `invest_amount`
FROM
`t_invests`
GROUP BY
user_id
) `v_invests` ON `v_invests`.`user_id` = `t_users`.`id`
WHERE
1 = 1
GROUP BY
`t_users`.`id`
HAVING
1 = 1
ORDER BY
register_time DESC

 派生表v_invests是不会走索引的， 所以最好不要使用， 最后我在t_invests表的user_id上面建立了索引， 查询速度提高到了0.08s