activerecord_MySQL上的ActiveRecord-在有条件的情况下遍历大型表

activerecord

在本文中，我将演示在具有数百万条记录MySQL数据库表中对记录进行迭代的两种方式之间的性能差异。 在大容量分析系统中，具有数百万条记录的表非常普遍，并且经常需要遍历整个表或这些表的子集-无论是执行计算，运行迁移还是在记录上创建并行的后台作业。 在AirPR上 ，我们有许多具有上亿个记录的数据库表，并且为迭代编写高效的代码变得很重要，因为好的方法和不太好的方法之间通常存在一个数量级的差异。

查找每种方法

ActiveRecord本地提供的标准方法是find_each方法。

在本练习中，我创建了一个employees表，向其中添加了约500万行数据¹。

还有一个带有以下各列的salaries表，用于存储这些员工在不同时间范围内的薪水。 该表包含约300万条记录。

让我们使用find_each来测量遍历该表的性能

DEFAULT_BATCH_SIZE = 1000

time = Benchmark.realtime do
  Employee.select(:emp_no, :first_name, :last_name).
           find_each(batch_size: DEFAULT_BATCH_SIZE) do |employee|
  end
end

=> 100.6963519999990

ActiveRecord进行的基础查询如下所示：

Employee Load (2.1ms)  SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees`  ORDER BY `employees`.`emp_no` ASC LIMIT 1000
  Employee Load (1.9ms)  SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (`employees`.`emp_no` > 11000)  ORDER BY `employees`.`emp_no` ASC LIMIT 1000
  Employee Load (1.8ms)  SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (`employees`.`emp_no` > 12000)  ORDER BY `employees`.`emp_no` ASC LIMIT 1000

...

Employee Load (1.3ms)  SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (`employees`.`emp_no` > 5127997)  ORDER BY `employees`.`emp_no` ASC LIMIT 1000

注意ActiveRecord如何跟踪上一次迭代的id ，并在下一个迭代中的where条件中使用它。 这称为基于值的分页，通常是分页的首选方法（相对于其他方法，例如基于偏移的分页）²。

ID迭代器方法

我建议我们现在尝试另一种迭代技术：

time = Benchmark.realtime do
  first_id = Employee.first.id
  last_id = Employee.last.id
  (first_id..last_id).step(DEFAULT_BATCH_SIZE).each do |value|

    Employee.where('employees.emp_no >= ?', value).
         where('employees.emp_no < ?', value + DEFAULT_BATCH_SIZE).
         order('employees.emp_no ASC').
         select(:emp_no, :first_name, :last_name).each do |employee|
    end

  end
end

=> 101.34066200000234

在此方法中，我们使用主键上的where条件遍历表中的所有记录，将行的总数分成几批。 请注意，这两种方法之间的性能实际上是相同的。 这是基础查询的外观：

Employee Load (1.1ms)  SELECT  `employees`.* FROM `employees`  ORDER BY `employees`.`emp_no` ASC LIMIT 1
  Employee Load (1.1ms)  SELECT  `employees`.* FROM `employees`  ORDER BY `employees`.`emp_no` DESC LIMIT 1
  Employee Load (1.5ms)  SELECT `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (employees.emp_no > 10001) AND (employees.emp_no <= 11001)
  Employee Load (1.9ms)  SELECT `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (employees.emp_no > 11001) AND (employees.emp_no <= 12001)

...

Employee Load (1.8ms)  SELECT `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` WHERE (employees.emp_no >= 5128001) AND (employees.emp_no < 5129001)

如果ID顺序正确，则此方法最有效，因为在这种情况下，迭代无需迭代和跳过很多丢失的记录³。

联接迭代

现在，让我们比较一下这两种方法在向查询中添加更多复杂性时的性能。

例如，在这种新情况下，我们希望遍历所有在公司任职期间薪水高于80,000的员工。 find_each方法如下所示：

time = Benchmark.realtime do
  Employee.select(:emp_no, :first_name, :last_name).
            joins(:salaries).
            where('salary > 80000').
            find_each(batch_size: DEFAULT_BATCH_SIZE) do |employee|
  end
end

=> 1181.770457000006

另一方面，用于执行相同操作的id迭代器方法导致性能提高了一个数量级。

time = Benchmark.realtime do

first_id = Employee.first.id
  last_id = Employee.last.id

(first_id..last_id).step(DEFAULT_BATCH_SIZE).each do |value|
    Employee.where('employees.emp_no >= ?', value).
         where('employees.emp_no < ?', value + DEFAULT_BATCH_SIZE).
         joins(:salaries).
         where('salary > 80000').   
         order('employees.emp_no ASC').      
         select(:emp_no, :first_name, :last_name).each do |employee|
    end
  end

end

=> 72.75677799998084

以上结果表明，使用find_each方法会导致性能大大find_each 。 ID迭代器方法比朴素的find_each快15倍。 当您检查由两种方法进行的查询时，其原因变得很清楚。

find_each方法进行这种查询：

SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name` FROM `employees` INNER JOIN `salaries` ON `salaries`.`emp_no` = `employees`.`emp_no` WHERE (salary > 80000)  ORDER BY `employees`.`emp_no` ASC LIMIT 1000

对这个查询的解释显示以下内容：

1 SIMPLE salaries ALL salary,emp_no NULL NULL NULL 2837536 Using where; Using temporary; Using filesort 
1 SIMPLE employees eq_ref PRIMARY PRIMARY 4 employees.salaries.emp_no 1 Using index

这表示没有使用薪水索引或emp_no索引来过滤薪水表。

id迭代器方法进行这种查询：

 SELECT  `employees`.`emp_no`, `employees`.`first_name`, `employees`.`last_name`  FROM `employees` INNER JOIN `salaries` ON `salaries`.`emp_no` = `employees`.`emp_no` WHERE (employees.emp_no >= 5128001) AND (employees.emp_no < 5129001) AND (salary > 80000) 

此查询的EXPLAIN显示查询优化器使用salaries表中emp_no的索引：

1 SIMPLE salaries range salary,emp_no emp_no 4 NULL 1 Using index condition; Using where 
1 SIMPLE employees eq_ref PRIMARY PRIMARY 4 employees.salaries.emp_no 1 Using index

这揭示了为什么find_each方法比迭代器方法要慢得多。

TL; DR

此处的课程始终是使用EXPLAINs来了解MySQL查询优化器的实际作用，以便您可以创建最优化的查询。 基于分析EXPLAIN的结果，可以决定在大型表上进行迭代时应采用哪种方法。

• 大表上的JOIN通常会导致性能下降，因此最好避免使用它们。 仅当通过在其中一个表上使用基于索引的条件显着缩小结果集时，才尝试使用JOIN。
• 通常，尝试充分利用索引进行查询。 使用导致MySQL查询优化器选择使用表中可用索引的查询。 在表中添加索引可能有助于加快查询速度，同时在写性能下降方面了解折衷方案。
• 避免运行select *，而是仅选择操作所需的列。 这将减少需要发送的数据量，尤其是当表中有许多TEXT列时。
• 查询优化器可能会根据各种因素采用不同的路径，同一查询在具有较大资源的服务器上可能会采用不同的路径，因为例如索引可能适合内存。 这将导致演奏上的巨大差异。 在这些情况下最好假设最坏的情况，并编写不依赖大索引的查询以保留在内存中。
• 查看ActiveRecord生成的查询的最简单方法是打开DEBUG日志记录。 建议在开发中将其打开，以便您尽早发现性能问题。
  ActiveRecord::Base.logger = Logger.new(STDOUT)
• 另外，您可以在ActiveRecord::Relation上使用to_sql来预先查看它将要进行的查询。
  Employee.where(“gender = 'M'”).to_sql

¹从我开始了这个样本数据集，并删除一切，但employees和salaries表。 然后，我复制了employees表中的记录以达到500万行。

² 此链接很好地比较了基于值和基于偏移的分页。

³如果为主键打开了AUTO_INCREMENT选项，则记录将自动以递增顺序排列。

larger在较大的表上，性能甚至会进一步下降。 当您到达亿万行时，理解基础查询变得更加重要，因为这可能导致100倍或1000倍的差异 。

⁵花时间阅读（并掌握）有关EXPLAIN输出格式的MySQL官方文档 ，以便清楚了解什么是好的，什么是不好的。

link此链接对创建索引的性能影响有很好的描述。 重要的是要了解，在具有很多索引的表上进行写入会比较慢，因此请明智地使用它们。

翻译自: https://hackernoon.com/activerecord-on-mysql-iterating-over-large-tables-with-conditions-453bd8761c8b

activerecord