ruby-on-rails - ActiveRecord - 非规范化案例研究

Question

处理以下 8 个不同 SQL 问题的最佳方法是什么。

我在数据库模式下放置了它，它是如何在我的 Rails 模型中表示的，以及我需要从数据库中获取数据的七个问题。我已经回答了一些问题，其他问题我不确定最好的解决方案。

问题#7 是一个曲线球，因为它可能会改变所有其他问题的答案。

标准

不应该需要 n+1 次查询。多个查询是可以的，但如果返回的每一行都需要一个额外的查询，则它是不可扩展的。

不应该需要后处理来过滤 SQL 可以自行完成的结果。例如，第五点的答案不应该是从数据存储中提取所有学生，然后删除那些没有类(class)的学生。

检索对象的计数不应触发另一个 SQL 查询。

如果 SQL 允许我聚合数据，则不必通过非规范化添加数据库列

MongoDB 或 CouchDB 等 NOSQL 解决方案是否更适合回答以下所有问题？

数据库架构

Students-------IDNameCourses-----IDNameGradeEnrollments----------IDStudent_IDCourse_ID

ActiveRecord Models

class Course < ActiveRecord::Base
  has_many :enrollments
  has_many :students, :through=>:enrollments
end
class Enrollment < ActiveRecord::Base
  belongs_to :student
  belongs_to :course
end
class Student < ActiveRecord::Base
  has_many :enrollments
  has_many :courses, :through => :enrollments
end

问题

1) 检索 9 年级数学类(class)中的所有学生

SQL

SELECT s.* FROM Students s
LEFT JOIN Enrollments e on e.student_id = s.id
LEFT JOIN Courses c on e.course_id = c.id
WHERE c.grade = 9 AND c.name = 'Math'

解决方案

这个很简单。 ActiveRecord 处理得很好

c = Course.where(:grade=>9).where(:name=>'Math').first
c.students

2) 检索 John 参加的所有类(class)

SQL

SELECT c.* FROM Courses c
LEFT JOIN Enrollments e on c.id = e.course_id
LEFT JOIN Students s on e.student_id = s.id
WHERE s.name = 'John'

解决方案

再次，简单。

s = Student.where(:name=>'John').first
s.courses

3) 检索所有 9 年级类(class)以及参加类(class)的学生人数(但不要检索学生)

SQL

SELECT c.*, count(e.student_id) FROM Courses C
LEFT JOIN Enrollments e on c.id = e.course_id
WHERE c.grade = 9 GROUP BY c.id

解决方案

计数器缓存在这里可以很好地工作。

class AddCounters < ActiveRecord::Migration  def up    add_column :students, :courses_count, :integer, :default=>0    add_column :courses, :students_count, :integer, :default=>0    Student.reset_column_information    Student.all.each do |s|      Student.update_counters s.id, :courses_count => s.courses.length    end    Course.reset_column_information    Course.all.each do |c|      Course.update_counters c.id, :students_count => c.students.length    end  end  def down    remove_column :students, :courses_count    remove_column :courses, :students_count  endend

ActiveRecord

Course.where(:grade=>9).each do |c|  puts "#{c.name} - #{c.students.size}"end

4) Retrieve all students taking at least three 11th Grade Courses, more than one 10th Grade Courses, and no 9th grade courses

NO Solution

Not sure of the best solution. This would be VERY messy to do in SQL without keeping a counter cache for number of courses per grade level on each student. I could add a hook to update this information myself. I don't want to pull all students and courses and count them in post processing.

Slow Solution

The following solution produces a lot of queries. Preloading the courses may not be possible. (For example, the students are coming from the association on a course)

students = some_course.students
matching_students = []
students.each do |s|
  courses_9 = 0
  courses_10 = 0
  courses_11 = 0
  s.courses.each do |c|
    courses_9  += 1 if c.grade == 9
    courses_10 += 1 if c.grade == 10
    courses_11 += 1 if c.grade == 11
  end
  if courses_11 <= 3 && courses_10 > 1 && courses_9 == 0
    matching_students << s
  end
end
return matching_students

5) 检索所有参加不止一门数学类(class)的学生
询问)

SQL

SELECT s.*, count(e.course_id) as num_Courses FROM Students s
INNER JOIN Enrollments e on s.id = e.student_id
INNER JOIN Courses c on e.course_id = c.id AND c.name = 'Math'
GROUP BY s.id HAVING num_Courses > 0

或者

SELECT DISTINCT s.* FROM Students s
INNER JOIN Enrollments e_math_1 on e_math_1.student_id = s.id
INNER JOIN Courses c_math_1 ON e_math_1.course_id = c_math_1.id AND c_math_1.name = 'Math'
INNER JOIN Enrollments e_math_2 on e_math_2.student_id = s.id
INNER JOIN Courses c_math_2 ON e_math_2.course_id = c_math_2.id AND c_math_2.name = 'Math'
WHERE c_math_1.id != c_math_2.id

没有解决方案

不确定最佳解决方案。棘手的部分是 ActiveRecord(或 NoSQL)解决方案无法检索所有学生，然后再查看他们的类(class)，因为这太慢了。

缓慢的解决方案

students = SomeObject.students
multiple_math_course_students = []
students.each do |s|
  has_math_course = false
  add_student = false
  s.courses.each do |c|
    if c.name == 'Math'
      if has_math_course
        add_student = true
      else
        has_math_course = true
      end
    end
  end
  multiple_math_course_students << s if add_student
end

6) 检索所有参加数学和科学类(class)的学生

SQL

SELECT s.* FROM Students s
INNER JOIN Enrollments e_math on e_math.student_id = s.id
INNER JOIN Courses c_math ON e_math.course_id = c_math.id
INNER JOIN Enrollments e_science on e_science.student_id = s.id
INNER JOIN Courses c_science on e_science.course_id = c_science.id WHERE c_math.name = 'Math' AND c_science.name = 'Science'

没有解决方案

这涉及两次加入同一个表(或在 Rails 中，关联)。有没有办法用 ActiveRecord 的 AREL 包装器顺利地做到这一点？您可以为科学类(class)和数学类(class)建立单独的关联，允许您对每个类(class)进行单独的操作，但这在下面的 #7 的情况下不起作用。

缓慢的解决方案

students = SomeObject.students
math_and_science_students = []
students.each do |s|
  has_math_course = false
  has_science_course = false
  s.courses.each do |c|
    has_math_course = true if c.name == 'Math'
    has_science_course = true if c.name == 'Science'
  end
  math_and_science_students << s if has_math_course && has_science_course
end

7) 客户已声明，每当系统中显示学生时，在学生旁边显示一个数字，显示他们正在参加的最高年级类(class)。例如，如果 Suzie 正在学习 9 年级科学类(class)和 10 年级数学类(class)，则在 Suzie 旁边显示“10”。

解决方案

为每个学生记录查询数据库是 Not Acceptable 。显示 100 名学生的页面需要 100 次查询。在这一点上，我想通过在学生表中放置一个带有“最高级别类(class)”的标志来对数据库进行非规范化。这是我最好的做法吗？从一开始就使用关系数据库以外的其他数据存储会更好吗？

想象一下，客户要求将任意数据显示为徽章:最高年级、参加的数学类(class)数量、如果同时学习数学、科学和历史，则获得金牌等。这些情况中的每一个都应该要求对数据库进行非规范化吗？非规范化数据是否应该与规范化数据保存在同一个关系数据库中？

Answer 1

首先，我认为您的数据库架构很好。我不会基于这些用例去规范化，因为它们很常见。

其次，你必须学会​​区分持久化、业务逻辑和报告。 ActiveRecord 有利于基本的持久化和封装业务逻辑。它处理 CRUD 的内容，并允许您将应用程序的许多逻辑放入模型中。但是，您所谈论的许多逻辑听起来像是报告，尤其是#6。您将不得不接受这样的查询逻辑，原始 SQL 将是您最好的选择。我认为您实现的缓存计数器可能会帮助您保持事件记录和模型，如果您在那里更舒服，但很可能您将不得不像对其中几个解决方案所做的那样降到普通 sql。报告一般需要直接的 sql。

规范化的数据库对于良好的应用程序设计至关重要。它对于使 OLTP 事务和业务逻辑的代码干净非常重要。不要仅仅因为你必须在 sql 中做一些连接而去规范化。这就是 sql 擅长的地方。通过非规范化你要做的就是让你的一些报告逻辑更快更容易，代价是让你的持久性和 OLTP 逻辑变得更慢和更难。

所以我会开始保留你的规范化数据库。如果您需要加入相关表，您通常可以使用 activerecord 的 include 方法来执行此操作，而无需求助于常规 sql。要执行基于连接的计数之类的操作，您必须使用普通的 sql。

最终，如果您的数据库变得非常大并且包含大量数据，您的报告将由于您必须执行的所有连接而变慢。这可以。在那一点上，立即开始考虑制作一个单独的非规范化报告数据库，您可以每小时、每晚、每周等从规范化数据库中更新。然后移动您的报告逻辑以查询报告数据库，而无需进行联接。然而，没有必要以这种方式开始。您只会招致额外的复杂性和费用，而无法确定返回。也许您的带有连接的报告 sql 将无限期地工作，而无需使用索引进行非规范化。不要过早地优化。

我认为 nosql 也不一定是答案。据我所知，NoSQL 适用于特定用例。您的应用程序的用例和架构似乎非常适合关系数据库。

总的来说，我认为原始 sql(不是 arel/activerecord)和你实现的计数器的组合很好。