mysql语句性能优化-使用case when 代替count(*)

---

前言

负责运维的项目有个页面加载特别慢，打开差不多要2分钟，但是页面数据并不算多，只有几百条。为了把页面加载速度慢的问题解决，通过查看后台日志，发现有一条慢sql，里面写的内容又长又臭。

---

一、问题SQL

 SELECT
        cnooc.*
    t3.project_name as project_name,
        t3.`year` as year,
        t3.award_name as award_name,
        t3.award_id as award_id,
        t3.major_code as major_code,
        t3.major_value as major_value,
        t1.awardTypeValue as awardTypeValue,
        t1.application_level as application_level,
        t1.average_score as initial_evaluation_score,
        t1.final_evaluation_level as final_evaluation_level,
        t1.create_unit_value as create_unit_value,
        t1.id as id,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        istjjt = '1'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS istjjt,
        ( ( SELECT count( * ) FROM yk_score WHERE final_evaluation_level = '特等奖' AND project_name = t3.project_name AND STATUS = 1 AND judges_level = 1 ) * 4 + ( SELECT count( * ) FROM yk_score WHERE final_evaluation_level = '一等奖' AND project_name = t3.project_name AND STATUS = 1 AND judges_level = 1 ) * 3+ ( SELECT count( * ) FROM yk_score WHERE final_evaluation_level = '二等奖' AND project_name = t3.project_name AND STATUS = 1 AND judges_level = 1 ) * 2 + ( SELECT count( * ) FROM yk_score WHERE final_evaluation_level = '三等奖' AND project_name = t3.project_name AND STATUS = 1 AND judges_level = 1 )
        ) / ( SELECT count( * ) FROM yk_score WHERE project_name = t3.project_name AND STATUS = 1 AND judges_level = 1 ) AS dpf,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        final_evaluation_level = '特等奖'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS grand_prize,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        final_evaluation_level = '一等奖'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS first_prize,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        final_evaluation_level = '二等奖'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS second_prize,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        final_evaluation_level = '三等奖'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS third_prize,
        (
        SELECT
        count(*)
        FROM
        yk_score
        WHERE
        final_evaluation_level = '不推荐'
        AND
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS no_prize,
        (
        SELECT
        count(*)
  yk_score
        WHERE
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) AS total_ticket,
        (
        select
        SUM(final_evaluation_score)/count(*)
        from
        yk_score
        where
        project_name = t3.project_name
        AND
        status = 1
        AND
        judges_level = 1
        ) as average_score
        FROM
        yk_score t3
        INNER JOIN
        yk_cnooc_progress t1
        ON
        t3.award_declaration_id = t1.id
        AND
        t1.review_link = 2
 ) as cnooc
 order by dpf desc,first_prize desc,second_prize desc,third_prize desc,initial_evaluation_score desc
    </select>

二、修改后SQL

代码如下（示例）：

 SELECT
        cnooc.* ,
        (	grand_prize * 4 + first_prize * 3+ second_prize* 2 +third_prize ) / (grand_prize+first_prize+second_prize+third_prize+no_prize) AS dpf,
        (grand_prize+first_prize+second_prize+third_prize+no_prize) AS total_ticket
        FROM
        (
        SELECT
        t1.average_score AS initial_evaluation_score,
        t1.id AS id,
        t1.year AS year,
        ( SELECT count( * ) FROM yk_scorecur WHERE istjjt = '1' AND project_name = t3.project_name ) AS istjjt,
        count(case when(final_evaluation_level="特等奖") then 1 else null end)as grand_prize ,
        count(case when(final_evaluation_level="一等奖") then 1 else null end)as first_prize,
        count(case when(final_evaluation_level="二等奖") then 1 else null end)as second_prize,
        count(case when(final_evaluation_level="三等奖") then 1 else null end)as third_prize,
        count(case when(final_evaluation_level="不推荐") then 1 else null end)as no_prize
        FROM
        yk_scorecur t3
        INNER JOIN vw_cnooc_progresscur t1 ON t3.award_declaration_id = t1.id
        GROUP BY
        t3.project_name
        ) AS cnooc
        <include refid="common.search_template"></include>
        order by dpf desc,first_prize desc,second_prize desc,third_prize desc,initial_evaluation_score desc

总结

修改后，sql 由原来的2分钟，变成了0.3秒。对于sql语句，尽量少用多个select count(*) 的语句。如果能用count case went 可以大大加快速度。