优化数据库结构

Question

我正在为我们的VLE开发一个奖励系统，它使用三种不同的技术-- JavaScript用于大多数客户端/显示处理，PHP用于与数据库进行通信，MySQL用于数据库本身。

我附上了我的“交易”表的三张截图。它的结构，几个例子记录和概述它的细节。

前提是工作人员奖励学生行为良好等等。这可能意味着30名学生的班级一次得到分数。工作人员每周有300个积分的限制，目前大约有85名工作人员正在使用该系统(这可能会增加)。

就我目前的做法而言，每个“交易”都有一个"Giver_ID“(工作人员奖励积分的成员)、一个"Recipient_ID”(学生获得分数)、一个类别和一个理由。这样，每次工作人员发布30分时，我都会将30行放到数据库中。

这似乎很早就起作用了，但在三个星期内，我已经有超过12,000个事务在数据库中。

在这一点上，它变得更加复杂。在“分配点数”页面(另一个附有的屏幕截图)上，当教师单击他们的班级或搜索单个学生时，我想要显示学生的分数。目前在我的系统上执行此操作的唯一方法是执行"SELECT * FROM 'transactions'“操作，并使用以下JS将所有信息放入数组中：

var Points = { "Recipient_ID" : "0", "Points" : "0" };

function getPoints (data) {
    for (var i = 0; i < data.length; i++) {
        if (Points[data[i].Recipient_ID]) {
            Points[data[i].Recipient_ID] = parseInt(Points[data[i].Recipient_ID]) + parseInt(data[i].Points);
        } else {
            Points[data[i].Recipient_ID] = data[i].Points;
        }
    }
}

在内部登录到系统时，这似乎足够快了。但是，在外部登录时，这个过程大约需要20秒，因此在单击/搜索几次之后才会显示学生的点数值。

我在PHP中使用以下代码来访问这些事务：

function getTotalPoints() {
    $sql = "SELECT * 
        FROM `transactions`";

    $res = mysql_query($sql);
    $rows = array(); 
    while($r = mysql_fetch_assoc($res)) {
        $rows[] = $r;
    }

    if ($rows) {
        return $rows;
    } else {
        $err = Array("err_id" => 1);
        return $err;
    }
}

所以，我的问题是，我应该如何处理这个问题？全文索引；可能是一个学生表，其总分值在每次输入事务时都会更新；大量事务(即，在同一类别中接收相同点数的多个学生)分组在一个数据库行中？这些都是我考虑过的事情，但我希望有比我更多DB知识的人来提供启示。

示例记录

​

表结构

​

表概述

​

赋值接口

​

在此之前，非常感谢您。

Answer 1

您的问题在于您的查询：

SELECT * FROM `transactions`

随着数据集变得越来越大，这将需要更长的时间来加载，并且需要更多的内存来存储它。而是确定具体需要哪些数据。如果是针对特定用户的：

SELECT SUM(points) FROM `transactions` WHERE Recipient_ID=[x]

或者如果你想要给你所有的学生所有的钱：

SELECT Recipient_ID, SUM(points) AS Total_Points FROM `transactions` GROUP BY Recipient_ID;

若要加快对特定字段的选择，可以为该字段添加索引。这将加快选择的速度，特别是随着表的增长。

ALTER TABLE `transactions` ADD INDEX Recipient_ID (Recipient_ID);

或者如果要显示transactions中所有条目的分页列表

SELECT * FROM `transactions` LIMIT [page*num_records_per_page],[num_records_per_page];

e.g.: SELECT * FROM `transactions` LIMIT 0,25 ORDER BY Datetime; # First 25 records

Answer 2

除了Tom的建议外，您还可以考虑进一步规范数据库。我想你现在有3张桌子：

students (id, name, ...)

staff (id, name, ...)

transactions (id, student_id, staff_id, points, date, reason)

一个更加规范化的表单使用了更多的表和更少的数据：

students (id, name, ...)

staff (id, name, ...)

transactions (id, staff_id, points, date, reason)

transactions_students (transaction_id, student_id)

然后，添加事务变成了两个步骤:首先创建一个事务记录，然后将多个记录插入到transactions_students中，每个记录将事务链接到一个学生。请注意，您可以创建一个视图，该视图的行为与用于选择的原始非规范化表完全相同，如下所示：

CREATE VIEW vw_transactions AS SELECT transactions.*, transactions_students.student_id FROM transactions INNER JOIN transactions_students WHERE transactions_students.transaction_id = transactions.id

这将大大减少事务表中的记录数量，并避免以红色方式存储日期和原因。缺点是，将事务链接到学生需要额外的一个连接--但是如果正确地设置了外键和索引，这根本不是问题。

Answer 3

我会索引Recipient_ID，这样您就可以在任何给定的点上搜索1个人，或者至少能够更有效地分组您的数据。如果您选择按category_id分组，那么我也会为category_id添加一个单独的或组合的索引。

第二个建议是动态地对数据进行分组和汇总。例如：

SELECT Recipient_ID, Category_ID, SUM(points) FROM transactions GROUP BY Recipient_ID, Category_ID

这两项建议将显着地提高您的性能，因为您将直接在数据库中计算PHP/JS方面学生的总分。