logo标签列表

如何高效地构建一个二维 MySQL 查询结构？

mysqlsqldatabaseperformance
4

我有一个MySQL数据库，包含以下表和字段：

  • 学生（id）
  • 班级（id）
  • 成绩 (id、student_id、class_id、grade)

学生表和班级表都以id为索引（主键）。成绩表以id、student_id、class_id和grade为索引。

我需要构建一个查询，根据班级ID，列出所有其他班级及在其他班级中得分更高的学生人数。

基本上，给定成绩表中的以下数据：

student_id | class_id | grade
--------------------------------------
1          | 1        | 87
1          | 2        | 91
1          | 3        | 75
2          | 1        | 68
2          | 2        | 95
2          | 3        | 84
3          | 1        | 76
3          | 2        | 88
3          | 3        | 71

使用类别ID 1 进行查询应该返回：
class_id | total
-------------------
2        | 3
3        | 1

理想情况下，我希望这个程序可以在几秒钟内执行完毕，因为我希望它成为网页界面的一部分。

我的问题是，在我的数据库中，我有超过1300个班级和160,000名学生。我的成绩表格有近1500万行，因此查询需要很长时间才能执行。

以下是我尝试过的方法以及每个查询所需的时间：

-- I manually stopped execution after 2 hours
SELECT    c.id, COUNT(*) AS total
FROM      classes c
              INNER JOIN grades a ON a.class_id = c.id
              INNER JOIN grades b ON b.grade < a.grade AND
                  a.student_id = b.student_id AND
                  b.class_id = 1
WHERE     c.id != 1 AND
GROUP BY  c.id

-- I manually stopped execution after 20 minutes
SELECT    c.id,
          (
              SELECT    COUNT(*) 
              FROM      grades g 
              WHERE     g.class_id = c.id AND g.grade > (
                            SELECT   grade 
                            FROM     grades 
                            WHERE    student_id = g.student_id AND 
                                     class_id = 1
                        )
          ) AS total
FROM      classes c
WHERE     c.id != 1;

-- 1 min 12 sec
CREATE TEMPORARY TABLE temp_blah (student_id INT(11) PRIMARY KEY, grade INT);
INSERT INTO temp_blah SELECT student_id, grade FROM grades WHERE class_id = 1;
SELECT    o.id,
          ( 
              SELECT    COUNT(*)
              FROM      grades g
                            INNER JOIN temp_blah t ON g.student_id = t.student_id
              WHERE     g.class_id = c.id AND t.grade < g.grade
          ) AS total
FROM      classes c
WHERE     c.id != 1;

-- Same thing but with joins instead of a subquery - 1 min 54 sec
SELECT    c.id,
          COUNT(*) AS total
FROM      classes c
              INNER JOIN grades g ON c.id = p.class_id
              INNER JOIN temp_blah t ON g.student_id = t.student_id
WHERE     c.id != 1
GROUP BY  c.id;

我也考虑过创建一个二维表，以学生为行，以班级为列，但我发现有两个问题：
  • MySQL实现了最大列数（4096）和最大行大小（以字节为单位），这种方法可能会超出限制
  • 我想不出一个好的方法来查询该结构以获取所需结果

我还考虑将这些计算作为后台作业执行并将结果存储在某个地方，但是为了信息保持最新（必须如此），每次创建或更新学生、班级或成绩记录时都需要重新计算它们。

是否有人知道构建此查询的更有效方法？

编辑：创建表语句：

CREATE TABLE `classes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1331 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

CREATE TABLE `students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=160803 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

CREATE TABLE `grades` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `class_id` int(11) DEFAULT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_grades_on_student_id` (`student_id`),
  KEY `index_grades_on_class_id` (`class_id`),
  KEY `index_grades_on_grade` (`grade`)
) ENGINE=InnoDB AUTO_INCREMENT=15507698 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci$$

以下是最高效查询（1分12秒）的explain输出：

id | select_type        | table | type   | possible_keys                                                             | key                      | key_len | ref               | rows   | extra 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1  | PRIMARY            | c     | range  | PRIMARY                                                                   | PRIMARY                  | 4       |                   | 683    | Using where; Using index
2  | DEPENDENT SUBQUERY | g     | ref    | index_grades_on_student_id,index_grades_on_class_id,index_grades_on_grade | index_grades_on_class_id | 5       | mydb.c.id         | 830393 | Using where
2  | DEPENDENT SUBQUERY | t     | eq_ref | PRIMARY                                                                   | PRIMARY                  | 4       | mydb.g.student_id | 1      | Using where

另一条编辑 - 解释 sgeddes 建议的输出：

+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key  | key_len | ref  | rows     | Extra                                        |
+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL | NULL    | NULL | 14953992 | Using where; Using temporary; Using filesort |
|  2 | DERIVED     | <derived3> | system | NULL          | NULL | NULL    | NULL |        1 | Using filesort                               |
|  2 | DERIVED     | G          | ALL    | NULL          | NULL | NULL    | NULL | 15115388 |                                              |
|  3 | DERIVED     | NULL       | NULL   | NULL          | NULL | NULL    | NULL |     NULL | No tables used                               |
+----+-------------+------------+--------+---------------+------+---------+------+----------+----------------------------------------------+
1
你应该认真规范化你的数据库，重复的列是一种反模式。 - Ja͢ck
我有超过1300个班级和160,000名学生。这就是数据库的用途，也是为什么要使用MySQL而不是Excel的原因。;-) 如果您的查询速度慢，请检查索引。 - Oliver
你是否运行了 EXPLAIN 来查看它是否正在使用索引？ - Barmar
1
真的吗？那么Class 1，Class 2和Class 3是什么？这是重复的。 - Ja͢ck
2
你能否创建一个带有一些样本数据的 SQLFiddle？ - Barmar
显示剩余10条评论
2个回答
3

我认为你可以使用 SUM 和 CASE 来完成这个任务：

SELECT C.Id,
  SUM(
    CASE 
    WHEN G.Grade > C2.Grade THEN 1 ELSE 0 
    END
  ) 
FROM Class C
  INNER JOIN Grade G ON C.Id = G.Class_Id
  LEFT JOIN (
      SELECT Grade, Student_Id, Class_Id
      FROM Class
        JOIN Grade ON Class.Id = Grade.Class_Id
      WHERE Class.Id = 1
    ) C2 ON G.Student_Id = C2.Student_Id
WHERE C.Id <> 1
GROUP BY C.Id

示例演示

--编辑--

回应您的评论，这里有另一种尝试，应该会更快：

SELECT 
  Class_Id, 
  SUM(CASE WHEN Grade > minGrade THEN 1 ELSE 0 END)
FROM 
(
  SELECT 
    Student_Id,
    @classToCheck:=
      IF(G.Class_Id = 1, Grade, @classToCheck) minGrade ,
    Class_Id,
    Grade
  FROM Grade G
    JOIN (SELECT @classToCheck:= 0) t
  ORDER BY Student_Id, IF(Class_Id = 1, 0, 1)
  ) t
WHERE  Class_Id <> 1
GROUP BY Class_ID

还有更多样例可供参考。

嗨@sgeddes，感谢您的回复。不幸的是，那并没有起作用：我在5分钟后停止了执行。总的来说，我认为任何试图将成绩表与自身连接的操作最终都会花费很长时间。 - Cam Price-Austin
1
@pricj004 -- 没问题。我已经编辑了我的答案，包括我认为应该是一个更快的解决方案。祝你好运！ - sgeddes
快多了！43秒。肯定有进展。我已经编辑了问题，以显示您查询的解释输出。 - Cam Price-Austin
@pricj004 -- 很高兴能帮到你。不确定我能想到更快的解决方案 :D - sgeddes
0

你能否在原始数据上尝试一下这个呢！它只有一个连接 :)

select
  final.class_id, count(*) as total
from
  (
    select * from   
      (select student_id as p_student_id, grade as p_grade from table1 where class_id = 1) as partial
    inner join table1 on table1.student_id = partial.p_student_id
    where table1.class_id <> 1 and table1.grade > partial.p_grade    
  ) as final
 group by
  final.class_id;

sqlfiddle链接

@pricj004 试一下吧。可能也很慢，想不出更好的方法了。 :) - jurgenreza
嗨@jurgenreza，感谢您的建议。这不错-在我的机器上花了1分46秒，如果我用temp_blah（我创建的临时索引表）替换部分表格，速度会更快（1分37秒）。 - Cam Price-Austin
@pricj004 很酷，希望能有所帮助。 - jurgenreza
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接