Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > MySQL > Обновление данных в большой таблице

Автор: lancelot555 29.3.2013, 15:28

Есть таблица с большим количеством данных (например 30 млн строк)
Структура: DATE и еще 8 интовых полей, 4 из которых составляют уникальный ключ
на каждую дату приходится разное количество строк

Задача:
Периодически нужно обновлять по нужной дате записи, новых записей может быть больше или меньше чем старых, простым апдейтом не обойтись

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

Подскажите куда копать

Автор: Akina 29.3.2013, 15:53

DDL, код, explain - в студию.

Автор: lancelot555 29.3.2013, 16:15

Код


CREATE TABLE IF NOT EXISTS `table` (
  `date_time` datetime NOT NULL,
  `one` int(11) NOT NULL DEFAULT '0',
  `two` int(11) NOT NULL DEFAULT '0',
  `three` int(11) NOT NULL DEFAULT '0',
  `four` int(11) NOT NULL DEFAULT '0',
  `five` int(11) NOT NULL DEFAULT '0',
  `six` int(11) NOT NULL DEFAULT '0',
  `seven` char(32) NOT NULL DEFAULT 'x',
  `eight` char(2) NOT NULL DEFAULT '--',
  `nine` int(11) NOT NULL DEFAULT '0',
  `ten` int(11) NOT NULL DEFAULT '0',
  `eleven` int(11) NOT NULL DEFAULT '0',
  `twelve` float(10,4) NOT NULL DEFAULT '0.0000',
  `thirteen` float(10,4) NOT NULL DEFAULT '0.0000',
  UNIQUE KEY `key` (`date_time`,`one`,`two`,`three`,`four`,`five`,`seven`,`eight`),
  KEY `date_time` (`date_time`)
)

К примеру мы посчитали новые данные за позавчера, нужно этот день в табличке обновить
Строк может быть как больше так и меньше
например за 2013-03-20 00:00:00 было 2 млн уникальных строк
после пересчета вышло 2,1 млн строк, при чем в старых данных встречаются только 1,9 млн уникальных ключей, остальные новые.. А еще в старых данных 0,1 млн строк с ключами которых в новых данных уже нет
Теоретически мы можем 1,9 строк обновить апдейтом (update) потом нужно удалить 0,1 млн старых строк и еще потом вставить недостающие 0,2 млн строк с новыми ключами которых раньше не было

Сейчас делается так: Delete from table where date_time = '2013-03-20 00:00:00' а затем массовый инсерт данных на эту дату

Автор: Akina 29.3.2013, 16:29

Движок - InnoDB?

Автор: lancelot555 29.3.2013, 16:51

да InnoDB

Автор: Akina 29.3.2013, 17:00

Тогда суть очевидна. Первичного индекса в таблице нет, посему первый уникальный индекс, не содержащий NULL-полей, становится кластерным. Т.е. вот этот твой составной монстр. И его перестройка при удалении (а удаление-то выполняется по одной записи!) коматозит весь процесс.
Просто введи в структуру первичный автоинкрементный ключ. Пусть даже он тебе и нахрен не нужен. Но именно он станет кластерным.

Автор: lancelot555 29.3.2013, 18:14

Хм.. не подумал об этом что-то. Спасибо за наводку, попробую.

Автор: lancelot555 1.4.2013, 22:55

Добавил в таблицу поле id

Код


ALTER TABLE table ADD `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Честно говоря стало только хуже, теперь процесс обновления записей происходит в разы медленнее
раньше удаление и вставка занимало секунд 20, теперь больше 100

Автор: Akella 1.4.2013, 23:22

Цитата(lancelot555 @ 29.3.2013, 16:15

)

Сейчас делается так: Delete from table where date_time = '2013-03-20 00:00:00' а затем массовый инсерт данных на эту дату

Наверное нужен индекс по полю date_time

Автор: lancelot555 1.4.2013, 23:28

Цитата(Akella @ 1.4.2013, 23:22

)

Наверное нужен индекс по полю date_time

он есть
KEY `date_time` (`date_time`)
отдельно и в составе ключа

Автор: Akina 2.4.2013, 08:14

Цитата(lancelot555 @ 1.4.2013, 23:55

)

стало только хуже

Возможный результат. Никто чуда не обещал.

Давайте попробуем понять, с чем имеем дело. Какова наполненность таблицы (сколько в ней всего записей, за какой срок)? куда деваются устаревшие? допускает ли она партиционирование или секционирование по дате? почему в качестве движка выбран именно InnoDB?

Автор: Агрох 6.4.2013, 18:08

А если убрать:

Код

UNIQUE KEY `key` (`date_time`,`one`,`two`,`three`,`four`,`five`,`seven`,`eight`),

Но оставить вот это внесённое вами изменение:

Код

ALTER TABLE table ADD `id` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Код

KEY `date_time` (`date_time`)

.

Если конечно по полям

Код

`one`,`two`,`three`,`four`,`five`,`seven`,`eight`

не ведётся поиск.