С наступающим Новым годом! Форум программистов, компьютерный форум, киберфорум
Наши страницы
MySQL
Войти
Регистрация
Восстановить пароль
 
Рейтинг 4.64/14: Рейтинг темы: голосов - 14, средняя оценка - 4.64
IVB
0 / 0 / 0
Регистрация: 03.07.2013
Сообщений: 3
1

Обновление большого количества строк в таблице. Как правильно?

03.07.2013, 12:57. Просмотров 2715. Ответов 4
Метки нет (Все метки)

Доброго времени суток.

Есть таблица `table`, имеющая несколько полей. Поле `id` - PRIMARY KEY. В этой таблице в некоторых строках нужно обновить значения поля `value`, не меняя значения других полей.

Я знаю два варианта выполнения этого действия.

Вариант первый:
MySQL
1
2
3
4
UPDATE `table` SET `value` = 'value1' WHERE `id` = 1;
UPDATE `table` SET `value` = 'value2' WHERE `id` = 2;
 . . .
UPDATE `table` SET `value` = 'value9999' WHERE `id` = 9999;
Вариант второй:
MySQL
1
2
3
4
5
6
7
INSERT INTO `table` ( `id`, `value` ) VALUES
( 1, 'value1' ),
( 2, 'value2' ),
 . . .
( 9999, 'value9999' )
ON DUPLICATE KEY UPDATE
`value` = VALUES( `value` )
Какой из двух вариантов является более "правильным"? Какой вариант оптимальнее?

А, может, есть еще какой-то вариант, который "делает" оба вышеприведенных?

(INSERT IGNORE не подходит, т.к. данные не обновятся, REPLACE INTO тоже не подходит, т.к. строки будут заменены с потерей данных в других полях)
0
Similar
Эксперт
41792 / 34177 / 6122
Регистрация: 12.04.2006
Сообщений: 57,940
03.07.2013, 12:57
Ответы с готовыми решениями:

Быстрый и точный подсчёт количества строк в большой таблице
Добрый день, может кто подскажет такую вещь написал клиент для работы с бд и...

Как правильно настроить параметры MYSQL для большого числа запросов?
Как правильно настроить параметры MYSQL для большого числа запросов?

Хранение большого количества файлов
Есть свой видео-хостинг типа ютуба, и вот такой вопрос возник. Как мне...

Скорость выполнения большого количества простых запросов
дорборого времени суток. подключение к базе через odbc, выполняется 100к...

Как правильно составить Inner Join с обращением к одной и той же таблице
не работает такой запрос Select Наименование, Фирма,Плательщик, Город,...

4
sKotenok
359 / 330 / 39
Регистрация: 29.03.2011
Сообщений: 837
03.07.2013, 15:27 2
IVB, во 2м варианте - если записи по id ещё нет в таблице - она добавится.

В одной таблице приходится часто обновлять до сотни тысяч строк за раз (обычно несколько десятков тысяч).
Обновление по одной записи в цикле может минут на 5-10 повесить сервер. В итоге я использовал вариант с временной таблицей (где-то на stackoverflow нашёл). Побочный +: "тяжёлая" работа вынесена из основной таблицы в память и запросы других пользователей при этом не замедляются.
Столкнулся с проблемой предельной длины SQL-запроса, пришлось разбить insert на части (подобрал эмпирически - для моих данных по 5000 INSERT-ов на запрос ). Общее время упало до ~10 секунд, как ещё оптимизировать, я пока не придумал. Сам код:
MySQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- Временная табличка
CREATE TEMPORARY TABLE `{tmp_table}` (
    `id` INT UNSIGNED NOT NULL,
    `value` CHAR(20) NOT NULL,
     PRIMARY KEY `id`
) ENGINE=MEMORY;
 
-- Цикл вставок:
INSERT INTO `{tmp_table}` (`id`,`value`) VALUES ('1','Раз'),('2','Два'),...; -- Тут впираемся в ограничение на длину запроса.
 
-- Сам запрос. Т.к. id - primary key в обеих таблицах, выполняется довольно шустро.
UPDATE LOW_PRIORITY `my_table` m, `{tmp_table}` tmp SET m.`value`=tmp.`value` WHERE m.`id`=tmp.`id`;
 
DROP TABLE `{tmp_table}`
{tmp_table} - это подстановка из скрипта (префикс 'tmp' + случайное число), без этого может вылезти конфликт имён, если 2 юзера одновременно записи вставляют.
1
IVB
0 / 0 / 0
Регистрация: 03.07.2013
Сообщений: 3
03.07.2013, 16:30  [ТС] 3
Цитата Сообщение от sKotenok Посмотреть сообщение
во 2м варианте - если записи по id ещё нет в таблице - она добавится.
Нет, такая ситуация исключена - id могут быть только существующих записей. Таблица лочится, делается выборка существующих записей, определяются необходимые изменения, применяются, таблица разлочивается. Т.е. даже теоретически невозможно появление несуществующего id.

Цитата Сообщение от sKotenok Посмотреть сообщение
В одной таблице приходится часто обновлять до сотни тысяч строк за раз
У меня масштабы поменьше - максимум несколько тысяч записей.

Цитата Сообщение от sKotenok Посмотреть сообщение
В итоге я использовал вариант с временной таблицей
Мне такой вариант не очень подходит. Мне как раз нужно исключить возможность изменения данных в "моей" таблице между операциями выборки и применения изменений. Поэтому использование дополнительной таблицы только увеличит время блокировки.
0
sKotenok
359 / 330 / 39
Регистрация: 29.03.2011
Сообщений: 837
03.07.2013, 16:55 4
IVB, просто делайте всё в одной транзакции, это решит проблему атомарности.
Других проблем, кроме вставки ненужных записей с 2м вариантом не вижу, в теории должен работать чуть быстрее, чем первый. Но ни разу не пользовался, сравните время выполнения - будет интересно.
0
IVB
0 / 0 / 0
Регистрация: 03.07.2013
Сообщений: 3
03.07.2013, 17:05  [ТС] 5
Цитата Сообщение от sKotenok Посмотреть сообщение
IVB, просто делайте всё в одной транзакции, это решит проблему атомарности.
Других проблем, кроме вставки ненужных записей с 2м вариантом не вижу, в теории должен работать чуть быстрее, чем первый. Но ни разу не пользовался, сравните время выполнения - будет интересно.
Я тоже считаю, что 2-й вариант должен быть быстрее. И в правильности работы не сомневаюсь.

Но вот как-то эстетически неправильно делать INSERT, когда нужен UPDATE.

На эксперименты, честно говоря, времени нет - потому и задал вопрос на форуме - вдруг кто-то уже померял...
0
03.07.2013, 17:05
MoreAnswers
Эксперт
37091 / 29110 / 5898
Регистрация: 17.06.2006
Сообщений: 43,301
03.07.2013, 17:05

Оптимально ли использовать Count для большого количества записей
Здравствуйте, если у меня много записей в таблице и я использую COUNT это...

Запрос Многие-ко-Многим ( связь большого количества таблиц)
К таблице Application нужно как то прикрепить некоторое оборудование, нужно...

Предупреждение: хост блокирован из-за большого количества ошибок соединения
Здравствуйте! такое вот предупреждение - mysql_connect() : Host <хост> is...


Искать еще темы с ответами

Или воспользуйтесь поиском по форуму:
5
Ответ Создать тему
Опции темы

КиберФорум - форум программистов, компьютерный форум, программирование
Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2018, vBulletin Solutions, Inc.
Рейтинг@Mail.ru