我有这个SQL问题，希望获得您的支持。
我有以下表格结构，scores
| 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;
当我执行它以获得具有50k条记录的其余表的最终分数
student_id = 1时，性能非常慢，这需要970ms。这是
EXPLAIN+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+
| 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 |
+-----------------+-------------+
谢谢。
最佳答案
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)上的索引也将有所帮助。关于mysql - 使用自定义计算栏改进复杂的查询，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54229050/