mysql - 使用自定义计算列改进复杂查询

Question

我遇到了这个 SQL 问题，希望得到您的支持。

我有以下表结构，分数

| student_id | x1 | x2 | x3 | y1 | y2 | z1 | z2 | z3 | z4 |
| ---------- | -- | -- | -- | -- | -- | -- | -- | -- | -- |
| 1          | 5  | 3  | 1  | 4  | 3  | 3  | 4  | 1  | 2  |
| 2          | 5  | 3  | 3  | 4  | 2  | 1  | 5  | 2  | 3  |
| 3          | 4  | 2  | 2  | 1  | 1  | 3  | 4  | 3  | 4  |
| 4          | 1  | 4  | 5  | 4  | 5  | 3  | 5  | 5  | 4  |

student_id 是PRIMARY_KEY。其他列 x1, x2... 的 TINYINT(1) 范围为 1..5(含)。

目标:

• 根据给定的 student_id 列表计算给定 student_id 的分数。
• 结果集应包含两列:student_id(排除给定的一列)和final_score。必须按 final_score DESC 排序。

计算学生 A 与学生 B 的 final_score 的公式。

• 给定:学生 A 和 B 的两条记录以及不同类别的分数列表。例如:类别 X 有 3 个问题，类别 Y 有 2 个问题，类别 Z 有 4 个问题。

• 首先，先计算每个类别的平均分。

  AVG_X = ( ABS(XA1 - XB1) + ABS(XA2 - XB2) + ABS(XA3 - XB3) )/3

  AVG_Y = ( ABS(YA1 - YB1) + ABS(YA2 - YB2) )/2

  AVG_Z = ( ABS(ZA1 - ZB1) + ABS(ZA2 - ZB2) + ABS(ZA3 - ZB3) + ABS(ZA4 - ZB4) )/4

其中:AVG是类别的平均值。 ABS就是获取绝对值。

• 最后，最终得分的计算方式为:

  FINAL_SCORE = 5 - ((AVG_X + AVG_Y + AVG_Z)/3)

基于此，我进行了以下 SQL 查询。

SELECT 
    f.student_id, 
    5 - ( avg_cate_x + avg_cate_y + avg_cate_z ) / 3 as final_score
FROM
(
    SELECT
        s.student_id,
        (
            ABS(s.x1 - u.x1) + ABS(s.x2 - u.x2) + ABS(s.x3 - u.x3)
        ) / 3 AS avg_cate_x,
        (
            ABS(s.y1 - u.y1) + ABS(s.y2 - u.y2)
        ) / 2 AS avg_cate_y,
        (
            ABS(s.z1 - u.z1) + ABS(s.z2 - u.z2) + ABS(s.z3 - u.z3) + ABS(s.z4 - u.z4)
        ) / 4 AS avg_cate_z,

    FROM scores AS s

    JOIN
    ( SELECT * FROM scores WHERE scores.student_id = 1 ) AS u
) AS f

ORDER by final_score DESC;

当我执行它来获得 student_id = 1 的最终分数(与具有 50k 记录的表的其余部分相比)时，性能非常慢，需要 970 毫秒。

这是解释

+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | scores | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using filesort        |
|  1 | SIMPLE      | s      | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL  |    4 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+

有什么办法可以改进这个查询吗？或者如果您有更好的想法，我真的很感激。

建议

@Muhammad Waheed:使用 INNER JOIN 而不是 JOIN。确实快了 12%。

这里是更新后的查询:

SELECT 
    agged.user_id, 
    5 - (
        avg_cate_06 + avg_cate_07 + avg_cate_08 + 
        avg_cate_09 + avg_cate_10 + avg_cate_11 + 
        avg_cate_12 
    ) / 7 as final_score
FROM
(
    SELECT
        s.user_id,
        (
            ABS(s.q1 - u.q1) + ABS(s.q2 - u.q2) + ABS(s.q3 - u.q3) + ABS(s.q4 - u.q4) + ABS(s.q5 - u.q5) +
            ABS(s.q6 - u.q6) + ABS(s.q7 - u.q7) + ABS(s.q8 - u.q8) + ABS(s.q9 - u.q9) + ABS(s.q10 - u.q10) +
            ABS(s.q11 - u.q11) + ABS(s.q12 - u.q12) + ABS(s.q13 - u.q13) + ABS(s.q14 - u.q14) + ABS(s.q15 - u.q15) +
            ABS(s.q16 - u.q16) + ABS(s.q17 - u.q17) + ABS(s.q18 - u.q18) + ABS(s.q19 - u.q19) + ABS(s.q20 - u.q20) 
        ) / 20 AS avg_cate_06,

        (
            ABS(s.q21 - u.q21) + ABS(s.q22 - u.q22) + ABS(s.q23 - u.q23) + ABS(s.q24 - u.q24) + ABS(s.q25 - u.q25) + 
            ABS(s.q26 - u.q26) + ABS(s.q27 - u.q27) + ABS(s.q28 - u.q28) + ABS(s.q29 - u.q29) + ABS(s.q30 - u.q30) + 
            ABS(s.q31 - u.q31) + ABS(s.q32 - u.q32) + ABS(s.q33 - u.q33) + ABS(s.q34 - u.q34) + ABS(s.q35 - u.q35) + 
            ABS(s.q36 - u.q36) + ABS(s.q37 - u.q37) + ABS(s.q38 - u.q38) + ABS(s.q39 - u.q39) + ABS(s.q40 - u.q40) + 
            ABS(s.q41 - u.q41) + ABS(s.q42 - u.q42) + ABS(s.q43 - u.q43) + ABS(s.q44 - u.q44) + ABS(s.q45 - u.q45) + 
            ABS(s.q46 - u.q46) + ABS(s.q47 - u.q47) + ABS(s.q48 - u.q48) + ABS(s.q49 - u.q49) + ABS(s.q50 - u.q50) + 
            ABS(s.q51 - u.q51) + ABS(s.q52 - u.q52) + ABS(s.q53 - u.q53) + ABS(s.q54 - u.q54) + ABS(s.q55 - u.q55) + 
            ABS(s.q56 - u.q56) + ABS(s.q57 - u.q57) + ABS(s.q58 - u.q58) + ABS(s.q59 - u.q59) + ABS(s.q60 - u.q60) + 
            ABS(s.q61 - u.q61)
        ) / 41 AS avg_cate_07,

        (
            ABS(s.q62 - u.q62) + ABS(s.q63 - u.q63) + ABS(s.q64 - u.q64) + ABS(s.q65 - u.q65) + ABS(s.q66 - u.q66) + 
            ABS(s.q67 - u.q67) + ABS(s.q68 - u.q68) + ABS(s.q69 - u.q69) + ABS(s.q70 - u.q70) + ABS(s.q71 - u.q71) + 
            ABS(s.q72 - u.q72) + ABS(s.q73 - u.q73) + ABS(s.q74 - u.q74) + ABS(s.q75 - u.q75)
        ) / 14 AS avg_cate_08,

        (
            ABS(s.q76 - u.q76) + ABS(s.q77 - u.q77) + ABS(s.q78 - u.q78) + ABS(s.q79 - u.q79) + ABS(s.q80 - u.q80) + 
            ABS(s.q81 - u.q81) + ABS(s.q82 - u.q82) + ABS(s.q83 - u.q83) + ABS(s.q84 - u.q84) + ABS(s.q85 - u.q85) + 
            ABS(s.q86 - u.q86) + ABS(s.q87 - u.q87) + ABS(s.q88 - u.q88) + ABS(s.q89 - u.q89) + ABS(s.q90 - u.q90) + 
            ABS(s.q91 - u.q91) + ABS(s.q92 - u.q92) + ABS(s.q93 - u.q93) + ABS(s.q94 - u.q94) + ABS(s.q95 - u.q95)
        ) / 20 AS avg_cate_09,

        (
            ABS(s.q96 - u.q96)   + ABS(s.q97 - u.q97)   + ABS(s.q98 - u.q98)   + ABS(s.q99 - u.q99)   + ABS(s.q100 - u.q100) + 
            ABS(s.q101 - u.q101) + ABS(s.q102 - u.q102) + ABS(s.q103 - u.q103) + ABS(s.q104 - u.q104) + ABS(s.q105 - u.q105) +
            ABS(s.q106 - u.q106) + ABS(s.q107 - u.q107) + ABS(s.q108 - u.q108) + ABS(s.q109 - u.q109) + ABS(s.q110 - u.q110) + 
            ABS(s.q111 - u.q111) + ABS(s.q112 - u.q112) + ABS(s.q113 - u.q113) + ABS(s.q114 - u.q114) + ABS(s.q115 - u.q115)
        ) / 20 AS avg_cate_10,

        (
            ABS(s.q116 - u.q116) + ABS(s.q117 - u.q117) + ABS(s.q118 - u.q118) + ABS(s.q119 - u.q119) + ABS(s.q120 - u.q120) + 
            ABS(s.q121 - u.q121) + ABS(s.q122 - u.q122) + ABS(s.q123 - u.q123) + ABS(s.q124 - u.q124) + ABS(s.q125 - u.q125) + 
            ABS(s.q126 - u.q126) + ABS(s.q127 - u.q127)
        ) / 12 AS avg_cate_11,

        (
            ABS(s.q128 - u.q128) + ABS(s.q129 - u.q129) + ABS(s.q130 - u.q130) + ABS(s.q131 - u.q131) + ABS(s.q132 - u.q132) + 
            ABS(s.q133 - u.q133) + ABS(s.q134 - u.q134) + ABS(s.q135 - u.q135) + ABS(s.q136 - u.q136) + ABS(s.q137 - u.q137) + 
            ABS(s.q138 - u.q138) + ABS(s.q139 - u.q139) + ABS(s.q140 - u.q140) + ABS(s.q141 - u.q141) + ABS(s.q142 - u.q142) + 
            ABS(s.q143 - u.q143) + ABS(s.q144 - u.q144) + ABS(s.q145 - u.q145) + ABS(s.q146 - u.q146) + ABS(s.q147 - u.q147) + 
            ABS(s.q148 - u.q148) + ABS(s.q149 - u.q149) + ABS(s.q150 - u.q150) + ABS(s.q151 - u.q151) + ABS(s.q152 - u.q152) + 
            ABS(s.q153 - u.q153) + ABS(s.q154 - u.q154) + ABS(s.q155 - u.q155) + ABS(s.q156 - u.q156) + ABS(s.q157 - u.q157)
        ) / 30 AS avg_cate_12

    FROM scores AS s

    INNER JOIN
        scores AS u ON u.user_id = 1 

) AS agged


ORDER by final_score DESC;

执行计划变为:

+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+----------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows  | filtered | Extra          |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+----------------+
|  1 | SIMPLE      | u     | NULL       | const | PRIMARY       | PRIMARY | 4       | const |     1 |   100.00 | Using filesort |
|  1 | SIMPLE      | s     | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  | 49999 |   100.00 | NULL           |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+-------+----------+----------------+

查询成本为:

mysql> SHOW STATUS LIKE 'Last_query_cost';
+-----------------+-------------+
| Variable_name   | Value       |
+-----------------+-------------+
| Last_query_cost | 5494.773878 |
+-----------------+-------------+

谢谢。

Answer 1

MySQL 实现子查询。因此，您可以尝试在不使用子查询的情况下编写此代码:

SELECT s.student_id,
       (ABS(s.x1 - u.x1) + ABS(s.x2 - u.x2) + ABS(s.x3 - u.x3)
       ) / 3 AS avg_cate_x,
       (ABS(s.y1 - u.y1) + ABS(s.y2 - u.y2)
       ) / 2 AS avg_cate_y,
       (ABS(s.z1 - u.z1) + ABS(s.z2 - u.z2) + ABS(s.z3 - u.z3) + ABS(s.z4 - u.z4)
       ) / 4 AS avg_cate_z,
       (5 - 
        (ABS(s.x1 - u.x1) + ABS(s.x2 - u.x2) + ABS(s.x3 - u.x3)
        ) / 3 +
        (ABS(s.y1 - u.y1) + ABS(s.y2 - u.y2)
        ) / 2 +
        (ABS(s.z1 - u.z1) + ABS(s.z2 - u.z2) + ABS(s.z3 - u.z3) + ABS(s.z4 - u.z4)
        ) / 4
       ) / 3 as final_score
FROM scores s JOIN
     scores u
     ON u.student_id = 1
ORDER by final_score DESC;

这不如使用子查询优雅，但您可能会看到性能改进。

此外，scores(student_id) 上的索引也会有所帮助。