| Версия для печати
Нажмите сюда для просмотра этой темы в оригинальном формате |
| Форум на Исходниках.RU > Базы данных: SQL > UPDATE large table WHERE id in (1,2,3) |
| Автор: Dart_Sitius 20.11.16, 18:49 |
| У меня есть большая 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 должны выполняться быстро |
| Автор: Akina 20.11.16, 18:59 |
| Они и выполняются быстро. 70 секунд / 150 тыс. записей = менее половины миллисекунды на запись. И это при том, что для КАЖДОЙ надо не только пересчитать значение, но и записать его, в т.ч. на жёсткий диск. Куда копать? ускорять работу с таблицей, вероятно. Первое - заносить весь этот набор ID во временную таблицу, размещённую в памяти. Индексированную, само собой. Второе - переносить таблицу на более быстрый накопитель. Начиная от SSD и заканчивая зеркалом таблицы в памяти. |
| Автор: Dart_Sitius 20.11.16, 19:06 |
| Akina, спасибо за оперативный ответ. Заносить ID во временную таблицу - хорошая идея, так и сделаю. А вот таблица и так на SSD =( Зеркало в памяти 40млн строк к сожалению невозможно. Заодно задам вопрос, который меня давненько мучает. Если в таблице есть индекс, то без разницы какого она размера? Имею ввиду, что если бы данная таблица была скажем не 40млн записей, а 5млн, но при этом в запросе мне все равно нужно было обновить 150к строк, то работало бы все те же 70 сек? (хотя это легко сейчас проверить на практике, но в теории же так оно?) Добавлено Хотя стоп, а что даст то, что я размещу 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`) Разве это быстрее? |
| Автор: Akina 20.11.16, 19:18 |
| Каков объём одной записи? (строки - это в экселе)... Цитата Dart_Sitius @ Если в таблице есть индекс, то без разницы какого она размера? Имею ввиду, что если бы данная таблица была скажем не 40млн записей, а 5млн, но при этом в запросе мне все равно нужно было обновить 150к строк, то работало бы все те же 70 сек? Я даже сейчас не стал бы наверняка утверждать, что индекс используется. Просто потому, что ни версии сервера, ни иных сведений нам не озвучено. Да и движок MyISAM - даже не распараллелить обработку. Ты проглядел главное - ИНДЕКСИРОВАННАЯ таблица. То есть предполагается, что будет CREATE TEMPORARY TABLE (id) INSERT INTO TEMPORARY TABLE (id) VALUES (...) CREATE PRIMARY INDEX ON (id) и только потом UPDATE. И тогда при достаточно свежей версии сервера появляется надежда вместо fullscan получить index merge. |
| Автор: Dart_Sitius 20.11.16, 19:41 |
| Цитата Каков объём одной записи? (строки - это в экселе)... Около 100 байт. Цитата Просто потому, что ни версии сервера, ни иных сведений нам не озвучено. Да и движок MyISAM - даже не распараллелить обработку. Хм, и действительно. InnoDB в данном случае похоже должен дать выигрыш. Версия MySQL 5.1.73-log. Цитата и только потом UPDATE. И тогда при достаточно свежей версии сервера появляется надежда вместо fullscan получить index merge ок, спасибо, попробую Добавлено <{CODE_COLLAPSE_OFF}><{CODE_WRAP_OFF}> 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`"); // потому что в цикле что-то такое? |
| Автор: JoeUser 20.11.16, 20:29 |
| Все абсолютно верно! За исключением пару "но":
|
| Автор: Dart_Sitius 20.11.16, 20:38 |
| Цитата "Убивать" SSD не особенно то и надо На хостинге не страшно ничего "убивать", я ему плачу за это. Цитата Задери RAM по самое "нехочу" Даже 16Гб может не хватить Добавлено В том варианте, который я написал, к сожалению только дольше. show processlist; показывает, что на preparing тратится около 130 секунд, а суммарно вместе с closing tables около 160 секунд. без использования временной таблицы было 50 секунд на запрос и 30-40 на closing tables. Может я что-то сделал не так? Буду эксперементировать с InnoDB Добавлено хм, у меня оказывается оператива была забита (проверил в free -m). Почистил, теперь обычный запрос выполняется 25 вместо 56, а closing tables 10 вместо 30 секунд, т.е суммарно 35 секунд |
| Автор: Akina 20.11.16, 21:11 |
| Близко не лежало. Я ж русским по белому отрисовал - сперва заливка, потом индексация. Более того - вот попробуй мне на основании только опубликованного тобой доказать, что временная таблица размещена в памяти... Можешь половину вышенаписанного смело забыть - на такой старой версии всё это неактуально. |
