优化数据库结构
我正在为我们的VLE开发一个奖励系统，该系统使用三种独立的技术 - 用于大多数客户端/显示处理的JavaScript，用于与数据库通信的PHP以及用于数据库本身的MySQL。优化数据库结构
我附上了我的“交易”表的三个屏幕截图。其结构，一些示例记录和其细节概述。
前提是员工成员奖励指向学生的良好行为等。这可能意味着30名学生的班级在同一时间给予积分。员工每周限制300点，目前有85名员工正在访问系统（这可能会增加）。
我现在的做法是，每个“交易”都有一个“Giver_ID”（员工奖励积分），一个“Recipient_ID”（接收积分的学生），一个类别和一个原因。这样，每当员工发出30分时，我就把30行放入数据库中。
这似乎在早期工作，但在三个星期内，我已经在数据库中有超过12,000个事务。
在这一点上它变得更复杂一点。在Assign Points页面（附带的另一个屏幕截图）上，当老师点击其中一个班级或搜索某个学生时，我希望显示学生的分数。我目前能做到这一点我的系统上的唯一方法是做一个“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;
}
}
所以，我的问题是，我应该怎么实际地接近这一点？全文索引;可能是一张学生表，其总积分值每次进入交易时都会更新;大量交易（即多于一名学生接受同一类别的相同分数）被分组到单个数据库行中？这些都是我所想到的，但我会喜欢有比我更多的数据库知识的人来提供启发。
实施例记录
表结构
表概述
分配点数接口
非常感谢提前。
你的问题是你查询：
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
非常感谢汤姆。这提高了我系统中很多区域的速度。 – dunc
我倒是指数Recipient_ID所以你可以专门在任何给定的点或者在搜索1人至少能够更有效地对数据进行分组。如果你选择按category_id进行分组，那么我会为category_id添加一个单独的或组合的索引。
第二个建议是GROUP和Aggregate您的数据。例如：
SELECT Recipient_ID, Category_ID, SUM(points) FROM transactions GROUP BY Recipient_ID, Category_ID
这两个建议，应该大幅升级的性能，因为不是计算总积分为您的学生在PHP/JS的一面，你会直接做在数据库上。
增加汤姆的建议，你可能要考虑进一步规范化你的数据库。我假设你现在有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
这将大大减少在交易表中的记录数，并且避免了存储日期和原因redunantly。缺点是，将交易链接到学生需要一个额外的连接 - 但是如果你的外键和索引设置正确，这根本就不是问题。
很好问... – slandau