UPDATE large table WHERE id in (1,2,3)

У меня есть большая MyIsam таблица на 40млн строк. id - это primary key. Мне нужно делать что-то вроде
UPDATE `table` SET `data`=`data`+1 WHERE `id` IN (1, 10, 100, 5, 3, 6...)

За раз в множество ID включается где-то 150к чисел. И на 1 такой запрос уходит примерно 70 секунд. Но мне нужно обновлять минимум миллион строк в минуту. Подскажите куда копать. Я думал операции по primary key должны выполняться быстро

Они и выполняются быстро. 70 секунд / 150 тыс. записей = менее половины миллисекунды на запись. И это при том, что для КАЖДОЙ надо не только пересчитать значение, но и записать его, в т.ч. на жёсткий диск.

Куда копать? ускорять работу с таблицей, вероятно.

Первое - заносить весь этот набор ID во временную таблицу, размещённую в памяти. Индексированную, само собой.
Второе - переносить таблицу на более быстрый накопитель. Начиная от SSD и заканчивая зеркалом таблицы в памяти.

Akina, спасибо за оперативный ответ.
Заносить ID во временную таблицу - хорошая идея, так и сделаю.
А вот таблица и так на SSD =( Зеркало в памяти 40млн строк к сожалению невозможно.

Заодно задам вопрос, который меня давненько мучает. Если в таблице есть индекс, то без разницы какого она размера? Имею ввиду, что если бы данная таблица была скажем не 40млн записей, а 5млн, но при этом в запросе мне все равно нужно было обновить 150к строк, то работало бы все те же 70 сек? (хотя это легко сейчас проверить на практике, но в теории же так оно?)

Добавлено 20.11.16, 19:09
Хотя стоп, а что даст то, что я размещу ID во временной таблице?
Сейчас я делаю весь апдейт в одну строку UPDATE `table` SET `data`=`data`+1 WHERE `id` IN (1,2,3,...150000)
Если я занесу в таблицу, то он превратится в
CREATE TABLE`tmp`...
INSERT INTO `tmp`(`id`) VALUES 1,2,3,...150000
UPDATE `table` SET `data`=`data`+1 WHERE `id` IN (SELECT `id` FROM `tmp`)
Разве это быстрее?

Цитата Dart_Sitius @ 20.11.16, 19:06

Зеркало в памяти 40млн строк к сожалению невозможно.

Каков объём одной записи? (строки - это в экселе)...

Цитата Dart_Sitius @ 20.11.16, 19:06

Если в таблице есть индекс, то без разницы какого она размера? Имею ввиду, что если бы данная таблица была скажем не 40млн записей, а 5млн, но при этом в запросе мне все равно нужно было обновить 150к строк, то работало бы все те же 70 сек?

Я даже сейчас не стал бы наверняка утверждать, что индекс используется. Просто потому, что ни версии сервера, ни иных сведений нам не озвучено. Да и движок MyISAM - даже не распараллелить обработку.

Цитата Dart_Sitius @ 20.11.16, 19:06

что даст то, что я размещу ID во временной таблице?

Ты проглядел главное - ИНДЕКСИРОВАННАЯ таблица. То есть предполагается, что будет
CREATE TEMPORARY TABLE (id)
INSERT INTO TEMPORARY TABLE (id) VALUES (...)
CREATE PRIMARY INDEX ON (id)
и только потом UPDATE. И тогда при достаточно свежей версии сервера появляется надежда вместо fullscan получить index merge.

Цитата

Каков объём одной записи? (строки - это в экселе)...

Около 100 байт.

Цитата

Просто потому, что ни версии сервера, ни иных сведений нам не озвучено. Да и движок MyISAM - даже не распараллелить обработку.

Хм, и действительно. InnoDB в данном случае похоже должен дать выигрыш. Версия MySQL 5.1.73-log.

Цитата

и только потом UPDATE. И тогда при достаточно свежей версии сервера появляется надежда вместо fullscan получить index merge

ок, спасибо, попробую

Добавлено 20.11.16, 20:25

        mquery("CREATE TEMPORARY TABLE `tmp_update`(PRIMARY KEY (`id`), KEY (`storage_id`) USING BTREE) AS ".
            "(SELECT `id`, `storage_id` FROM `$sql_table` WHERE $where LIMIT $limit)");
        mquery("UPDATE `$sql_storage` SET ".$value[1]." WHERE `id` IN (SELECT `storage_id` FROM `tmp_update`)");
        mquery("DELETE FROM `$sql_table` WHERE `id` IN (SELECT `id` FROM `tmp_update`)");
        mquery("DROP TABLE `tmp_update`"); // потому что в цикле

что-то такое?

Сообщение отредактировано: Dart_Sitius - 20.11.16, 20:25

Цитата Akina @ 20.11.16, 18:59

Начиная от SSD и заканчивая зеркалом таблицы в памяти

Все абсолютно верно! За исключением пару "но":

• "Убивать" SSD не особенно то и надо ... если вообще можно не убивать, мы же не киллеры
• Задери RAM по самое "нехочу" - это будет а) Дешевле SSD б) Быстрее SSD в разы ... одна беда - сброс кэша, но это - пыль а не вопрос

Цитата

"Убивать" SSD не особенно то и надо

На хостинге не страшно ничего "убивать", я ему плачу за это.

Цитата

Задери RAM по самое "нехочу"

Даже 16Гб может не хватить

Добавлено 20.11.16, 20:48
В том варианте, который я написал, к сожалению только дольше.
show processlist; показывает, что на preparing тратится около 130 секунд, а суммарно вместе с closing tables около 160 секунд.
без использования временной таблицы было 50 секунд на запрос и 30-40 на closing tables. Может я что-то сделал не так?

Буду эксперементировать с InnoDB

Добавлено 20.11.16, 21:00
хм, у меня оказывается оператива была забита (проверил в free -m). Почистил, теперь обычный запрос выполняется 25 вместо 56, а closing tables 10 вместо 30 секунд, т.е суммарно 35 секунд

Цитата Dart_Sitius @ 20.11.16, 19:41

что-то такое?

Близко не лежало. Я ж русским по белому отрисовал - сперва заливка, потом индексация.
Более того - вот попробуй мне на основании только опубликованного тобой доказать, что временная таблица размещена в памяти...

Цитата Dart_Sitius @ 20.11.16, 19:41

Версия MySQL 5.1.73-log.

Можешь половину вышенаписанного смело забыть - на такой старой версии всё это неактуально.