ActiveRecord в MySQL - перебор больших таблиц с условиями

В этой статье я собираюсь продемонстрировать различия в производительности между двумя способами перебора записей в таблице базы данных MySQL с миллионами записей. В системах аналитики большого объема таблицы с миллионами записей довольно распространены, и часто становится необходимым повторение всей таблицы или подмножества этих таблиц - будь то выполнение вычислений, запуск миграции или создание параллельных фоновых заданий для записей. В AirPR у нас много таблиц базы данных с сотнями миллионов записей, и становится важным писать эффективный код для итераций, потому что часто существует разница на порядок величины между хорошим и не очень хорошим подходом.

Найдите каждый метод

Стандартный подход, изначально предоставляемый ActiveRecord, - это find_eachmethod.

Для целей этого упражнения я создал таблицуemployees, в которую добавил около 5 миллионов строк данных¹.

Существует также salaries table со следующими столбцами, в которых хранятся зарплаты этих сотрудников за разные временные диапазоны. Эта таблица содержит около 3 миллионов записей.

Давайте измерим производительность итерации по этой таблице, используя 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 отслеживает theid из предыдущей итерации и использует его в условии 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)

Этот подход работает лучше всего, если идентификаторы в порядке, потому что в этом случае итерации не нужно будет повторять и пропускать множество пропущенных записей³.

Итерация с соединениями

Теперь давайте сравним производительность этих двух методов, когда мы добавим дополнительную сложность запросу.

В этом новом сценарии, скажем, мы хотим перебрать всех сотрудников, чья зарплата была выше 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 приводит к гораздо худшей производительности⁴. Подход с итератором ID примерно в 15 раз быстрее, чем наивный find_each. Причина этого становится ясной, когда вы исследуете запросы, сделанные двумя подходами.

Метод 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

EXPLAIN по этому запросу показывает следующее:

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 в этом запросе показывает, что оптимизатор запросов использует индекс 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

Урок здесь: всегда используйте EXPLAIN, чтобы понять, что на самом деле делает оптимизатор запросов MySQL, чтобы вы могли создавать наиболее оптимизированные запросы⁵. На основе анализа результатов EXPLAIN можно принять решение о том, какой подход следует использовать для итераций с большими таблицами.

Соединения с большими таблицами обычно приводят к снижению производительности, поэтому их лучше избегать. Старайтесь использовать JOIN только тогда, когда набор результатов был значительно сужен за счет использования условия на основе индекса для одной из таблиц.
Постарайтесь наилучшим образом использовать индексы для запросов в целом. Используйте запросы, в результате которых оптимизатор запросов MySQL выбирает использование индексов, доступных в таблице. Добавьте в таблицу индексы, которые могут помочь ускорить запросы, понимая при этом компромиссы с точки зрения снижения производительности записи⁶.
Избегайте запуска select *, вместо этого выберите только те столбцы, которые необходимы для вашей работы. Это уменьшит объем данных, которые необходимо отправить, особенно когда в таблице много столбцов TEXT.
Оптимизатор запросов может выбирать разные пути в зависимости от множества факторов, один и тот же запрос может принимать разные пути на сервере с большими ресурсами, потому что, скажем, индекс может поместиться в памяти. Это приведет к резким различиям в характеристиках. В таких ситуациях лучше всего предполагать худшее и писать запросы, которые не полагаются на большие индексы, которые должны храниться в памяти.
Самый простой способ увидеть запросы, которые генерирует ActiveRecord, - это включить ведение журнала DEBUG. Рекомендуется включать это в процессе разработки, чтобы вы могли своевременно обнаруживать проблемы с производительностью.
ActiveRecord::Base.logger = Logger.new(STDOUT)
Кроме того, вы можете использовать to_sql для ActiveRecord::Relation, чтобы заранее узнать, какой запрос он будет делать.
Employee.where(“gender = ‘M’”).to_sql

¹ Я начал с этого образца набора данных и удалил все, кроме таблиц employees и salaries. Затем я продублировал записи в таблице employees, чтобы получить 5 миллионов строк.

² Эта ссылка дает хорошее сравнение разбивки на страницы на основе значения и смещения.

³ Если опция AUTO_INCREMENT включена для первичного ключа, записи автоматически располагаются в возрастающем порядке.

⁴ На больших столах производительность ухудшается еще больше. Когда вы достигнете сотен миллионов строк, становится еще более важным понять лежащие в основе запросы, потому что это может привести к разнице в 100 или 1000 раз.

⁵ Найдите время, чтобы прочитать (и освоить) официальную документацию MySQL по выходному формату EXPLAIN, чтобы было ясно, что хорошо, а что нет.

⁶ Эта ссылка хорошо описывает влияние создания индексов на производительность. Важно понимать, что запись в таблицу с большим количеством индексов будет медленнее, поэтому используйте их с умом.

ActiveRecord в MySQL - перебор больших таблиц с условиями

Найдите каждый метод

Метод итератора ID

Итерация с соединениями

TL;DR

Вопросы по теме