UpdateAll на большой таблице

Общие вопросы по использованию второй версии фреймворка. Если не знаете как что-то сделать и это про Yii 2, вам сюда.
Ответить
Аватара пользователя
Ghost_nsk
Сообщения: 825
Зарегистрирован: 2012.01.01, 00:45
Откуда: Новосибирск
Контактная информация:

UpdateAll на большой таблице

Сообщение Ghost_nsk »

народ, посоветуйте как делать updateAll на большой таблице (около 15 млн. записей), пример запроса:

Код: Выделить всё

Post::updateAll([
'status' => 1,
], [
'user_id' => $user_id,
]);
до 500 тыщ. такая тема справляется на ура, а потом начинает нехило тормозить с ростом количества записей
Loveorigami
Сообщения: 977
Зарегистрирован: 2014.08.27, 21:54

Re: UpdateAll на большой таблице

Сообщение Loveorigami »

индекс на user_id и status проставлен?
Restlin
Сообщения: 139
Зарегистрирован: 2011.09.09, 18:12

Re: UpdateAll на большой таблице

Сообщение Restlin »

Loveorigami писал(а): 2017.02.12, 11:47 индекс на user_id и status проставлен?
Поиск у такого запроса идет только по user_id, поэтому индекс нужен только по этому полю.
А вообще индексы по статусным полям (уникальных значений у которых мало) бесполезны, ни один движок СУБД не будет использовать индекс, если скажем более 20% записей в таблице имеет одно значение статуса.
Аватара пользователя
Йож
Сообщения: 574
Зарегистрирован: 2015.08.26, 03:05

Re: UpdateAll на большой таблице

Сообщение Йож »

Какой средний процент строчек, где статус уже равен 1 для user_id?
--------------------------------------------------------------------------------------
Провел небольшие тесты с разными вариантами индексов и запросами (MyISAM, 0,5 млн записей, 70 Мб):
=================================
НИ ОДНОЙ ЗАПИСИ С НУЖНЫМ СТАТУСОМ
=================================
Только проверяем раздел (update 'table' set active='1' where cat='25')
-----------------------
1. Индекс только на раздел = Затронуто 36469 строк. (Запрос занял 0.1580 сек.)
2. Индекс только на статус = Затронуто 36469 строк. (Запрос занял 0.7081 сек.)
3. Индекс раздел + статус = Затронуто 36469 строк. (Запрос занял 0.2723 сек.)
4. Индекс раздел, индекс статус = Затронуто 36469 строк. (Запрос занял 0.2794 сек.)

Проверяем раздел и статус (update 'table' set active='1' where cat='25' and active!='1')
-------------------------
1. Индекс только на раздел = Затронуто 36469 строк. (Запрос занял 0.1812 сек.)
2. Индекс только на статус = Затронуто 36469 строк. (Запрос занял 0.7331 сек.)
3. Индекс раздел + статус = Затронуто 36469 строк. (Запрос занял 0.2813 сек.)
4. Индекс раздел, индекс статус = Затронуто 36469 строк. (Запрос занял 0.2807 сек.)
==========================

==================================
ПОЛОВИНА ЗАПИСЕЙ С НУЖНЫМ СТАТУСОМ
==================================
Только проверяем раздел (update 'table' set active='1' where cat='25')
-----------------------
1. Индекс только на раздел = Затронуто 18230 строк. (Запрос занял 0.1102 сек.)
2. Индекс только на статус = Затронуто 18230 строк. (Запрос занял 0.5936 сек.)
3. Индекс раздел + статус = Затронуто 18230 строк. (Запрос занял 0.1679 сек.)
4. Индекс раздел, индекс статус = Затронуто 18230 строк. (Запрос занял 0.1742 сек.)

Проверяем раздел и статус (update 'table' set active='1' where cat='25' and active!='1')
-------------------------
1. Индекс только на раздел = Затронуто 18230 строк. (Запрос занял 0.1092 сек.)
2. Индекс только на статус = Затронуто 18230 строк. (Запрос занял 0.6315 сек.)
3. Индекс раздел + статус = Затронуто 18230 строк. (Запрос занял 0.1431 сек.)
4. Индекс раздел, индекс статус = Затронуто 18230 строк. (Запрос занял 0.1691 сек.)
==========================

Первые выводы:
1. Сделайте индекс по user_id;
2. В запросах проверять статус не нужно, похоже, это делает БД в движке сама.

Но лучше потестите у себя, ведь много зависит от движка БД, типа полей и т.д.
Restlin
Сообщения: 139
Зарегистрирован: 2011.09.09, 18:12

Re: UpdateAll на большой таблице

Сообщение Restlin »

ТС, если объем таблицы и дальше будет расти, то я бы предложил посмотреть в следующие стороны:
1) посмотреть можно ли добавить еще поле актуальности, например дату с индексом (мб даже кластерным) и обрабатывать только актуальные (свежие) записи. В таком случае сперва будет отбор множества только актуальных записей по дате, а потом фильтр по user_id;
2) проанализировать существующие индексы в таблице, мб есть ненужные и удалить их. Индексы ускоряют селект но немного замедляют update и delete;
3) Если таблица с большим количеством колонок и среди них много текстовых (по ним редко работают индексы и отбор), то можно такие большие текстовые поля вынести в отдельные таблицы со связью по первичному ключу "один к одному". Такая оптимизация помогает, так как движку СУБД приходится перемещаться с шагом по длине 1 строки таблицы (чем короче строка, тем быстрее обход таблицы). Мной такое тестировалось на таблице в postgresql при количестве строк около 600 млн.

p.s. задумался в какой ситуации нужен такой запрос, может быть вы просто хотите ускорить эту обработку при вызове из браузера? В таком случае может вам перейти к cron обработчику, который будет делать этот update в фоном режиме?
Аватара пользователя
Ghost_nsk
Сообщения: 825
Зарегистрирован: 2012.01.01, 00:45
Откуда: Новосибирск
Контактная информация:

Re: UpdateAll на большой таблице

Сообщение Ghost_nsk »

там замута в том, что в конкретно в нужной таблице используются 13 индексов для ускорения выборки, отвечу сразу: да, они все нужные. Скорее всего дело в них, но если я их вырежу то выборка не будет такой оперативной, как это необходимо. На user_id индекс также стоит
Аватара пользователя
Йож
Сообщения: 574
Зарегистрирован: 2015.08.26, 03:05

Re: UpdateAll на большой таблице

Сообщение Йож »

Индекс может замедлить апдейт только в том случае, если этот апдейт изменяет поля, входящие в него.

Попробуйте удалить индекс на status (если есть, и в составных тоже).
Ответить