Обновление данных в большой таблице

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

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

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

Подскажите куда копать
Ответы (11)
ava
Akina | 29.03.2013, 15:53 #
DDL, код, explain - в студию.
ava
lancelot555 | 29.03.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'  а затем массовый инсерт данных на эту дату
ava
Akina | 29.03.2013, 16:29 #
Движок - InnoDB?
ava
lancelot555 | 29.03.2013, 16:51 #
да InnoDB
ava
Akina | 29.03.2013, 17:00 #
Тогда суть очевидна. Первичного индекса в таблице нет, посему первый уникальный индекс, не содержащий NULL-полей, становится кластерным. Т.е. вот этот твой составной монстр. И его перестройка при удалении (а удаление-то выполняется по одной записи!) коматозит весь процесс.
Просто введи в структуру первичный автоинкрементный ключ. Пусть даже он тебе и нахрен не нужен. Но именно он станет кластерным.
ava
lancelot555 | 29.03.2013, 18:14 #
Хм.. не подумал об этом что-то. Спасибо за наводку, попробую.
ava
lancelot555 | 01.04.2013, 22:55 #
Добавил в таблицу поле id

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

Честно говоря стало только хуже, теперь процесс обновления записей происходит в разы медленнее
раньше удаление и вставка занимало секунд 20, теперь больше 100
ava
Akella | 01.04.2013, 23:22 #
Цитата (lancelot555 @  29.3.2013,  16:15 findReferencedText)
Сейчас делается так:  Delete from table where date_time = '2013-03-20 00:00:00'  а затем массовый инсерт данных на эту дату


Наверное нужен индекс по полю date_time
ava
lancelot555 | 01.04.2013, 23:28 #
Цитата (Akella @  1.4.2013,  23:22 findReferencedText)
Наверное нужен индекс по полю date_time

он есть
KEY `date_time` (`date_time`)
отдельно и в составе ключа
ava
Akina | 02.04.2013, 08:14 #
Цитата (lancelot555 @  1.4.2013,  23:55 findReferencedText)
стало только хуже

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

Давайте попробуем понять, с чем имеем дело. Какова наполненность таблицы (сколько в ней всего записей, за какой срок)? куда деваются устаревшие? допускает ли она партиционирование или секционирование по дате? почему в качестве движка выбран именно InnoDB?
ava
Агрох | 06.04.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`
 не ведётся поиск.
Зарегистрируйтесь или войдите, чтобы написать.
Фирма дня
Вы также можете добавить свою фирму в каталог IT-фирм, и публиковать статьи, новости, вакансии и другую информацию от имени фирмы.
Подробнее
Участники
advanced
Отправить