explain的结果如下，求优化大师指点一下

Question

说明：主表数据量为100时查10条记录很快出结果，当70000时，死循环
SQL：如下SELECT 
    tbl_statuses.statuses_id,
    tbl_statuses.user_id create_uid,
    tbl_user.real_name create_user,
    tbl_statuses.tag_id,
    tbl_tag.name as tag_name,
    tbl_statuses.task_id,
    tbl_statuses.title,
    tbl_statuses.text,
    tbl_statuses.states,
    tbl_statuses.created_at,
    tbl_statuses.forward_uid,
    tbl_account.name as forward_user,
    tbl_statuses.forward_time,
    tbl_statuses.forward_statuses_id,
    tbl_statuses.original_statuses_id,
    tbl_statuses.type,
    tbl_mention.address as address,
    tbl_todo_statuses.istodo,
    tbl_forward_statuses.isforward,
    tbl_onlytosee_statuses.isonlytosee,
    IFNULL(tbl_statuses.final_receiver_ids,
            tbl_new_mention.final_receiver_ids) as final_receiver_ids,
    IFNULL(tbl_statuses.final_receiver_name,
            tbl_new_mention.final_receiver_name) as final_receiver_name
FROM
    tbl_statuses
        INNER JOIN
    (SELECT 
        IFNULL(tbl_statuses.original_statuses_id, tbl_statuses.statuses_id) AS statuses_id
    FROM
        tbl_statuses
    where
        tbl_statuses.states <> '4') as tbl_new_statuses ON tbl_new_statuses.statuses_id = tbl_statuses.statuses_id
        INNER JOIN
    (SELECT 
        tbl_mention.mention_id,
            tbl_mention.ref_id,
            tbl_mention.address,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_user_id) AS final_receiver_ids,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_real_name) AS final_receiver_name
    FROM
        tbl_mention
    GROUP BY tbl_mention.ref_id) AS tbl_mention ON tbl_statuses.statuses_id = tbl_mention.ref_id
        LEFT JOIN
    (SELECT 
        tbl_mention.mention_id,
            tbl_mention.ref_id,
            tbl_mention.address,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_user_id) AS final_receiver_ids,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_real_name) AS final_receiver_name
    FROM
        tbl_mention
    inner join tbl_statuses ON tbl_statuses.statuses_id = tbl_mention.ref_id
    where
        tbl_statuses.forward_uid is null or ((tbl_statuses.forward_uid is not null) and tbl_statuses.forward_statuses_id is not null)
    GROUP BY tbl_mention.ref_id) AS tbl_new_mention ON tbl_statuses.statuses_id = tbl_new_mention.ref_id
        LEFT JOIN
    (SELECT 
        tbl_statuses.statuses_id, 1 as istodo
    from
        tbl_statuses
    inner join (select 
        tbl_mention.ref_id,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_user_id) as receive_user_ids
    from
        tbl_mention
    group by tbl_mention.ref_id) tbl_mention ON tbl_mention.ref_id = tbl_statuses.statuses_id
    where
        (tbl_statuses.forward_uid is not null and tbl_statuses.final_receiver_ids like '111117%') or (tbl_statuses.forward_uid is null and tbl_mention.receive_user_ids like '%111117%')) as tbl_todo_statuses ON tbl_todo_statuses.statuses_id = tbl_statuses.statuses_id
        LEFT JOIN
    (SELECT 
        tbl_statuses.statuses_id, 1 as isforward
    from
        tbl_statuses
    where
        tbl_statuses.forward_uid is not null and tbl_statuses.forward_uid = 111117 and ((tbl_statuses.final_receiver_ids is not null and tbl_statuses.final_receiver_ids not like '%111117%') or (tbl_statuses.final_receiver_ids is null))) as tbl_forward_statuses ON tbl_forward_statuses.statuses_id = tbl_statuses.statuses_id
        LEFT JOIN
    (select 
        tbl_statuses.statuses_id, 1 as isonlytosee
    from
        tbl_statuses
    inner join (select 
        tbl_mention.ref_id,
            GROUP_CONCAT(DISTINCT tbl_mention.receive_user_id) as receive_user_ids
    from
        tbl_mention
    group by tbl_mention.ref_id) tbl_mention ON tbl_mention.ref_id = tbl_statuses.statuses_id
    where
        (tbl_statuses.forward_uid is not null and tbl_statuses.forward_uid != 111117 and tbl_statuses.final_receiver_ids not like '%111117%') or (tbl_statuses.forward_uid is null and tbl_mention.receive_user_ids not like '111117%')) as tbl_onlytosee_statuses ON tbl_onlytosee_statuses.statuses_id = tbl_statuses.statuses_id
        INNER JOIN
    tbl_user ON tbl_statuses.user_id = tbl_user.user_id
        INNER JOIN
    tbl_tag ON tbl_statuses.tag_id = tbl_tag.tag_id
        INNER JOIN
    tbl_user_view_list ON tbl_user_view_list.view_user_id = tbl_statuses.user_id
        LEFT JOIN
    tbl_account ON tbl_account.user_id = tbl_statuses.forward_uid
WHERE
    tbl_statuses.type = 1
GROUP BY tbl_statuses.statuses_id
ORDER BY tbl_statuses.created_at DESC
LIMIT 0 , 10
SQL性能优化

Answer 1

楼主写这么长的sql勇气可嘉啊 看的人必然崩溃

Answer 2

这个sql如此的复杂，是不是说明：数据库设计有问题？？？可以加一些冗余字段来减少连接表啊！！！

Answer 3

1.GROUP BY 是很耗时的操作，能尽量减少吗？
2.select 中的DISTINCT也是非常耗时的操作啊！！！
3.也可以采取中间表的思路。先把一部分数据整理好后，放到临时表，在对临时表处理，效果好！

Answer 4

explain 的结果请贴文本，不要贴图。 贴图是方便，但不方便别人阅读分析。

Answer 5

分步运行SQL语句，看看问题出在什么地方