Склейка двух таблиц по сложному условию

Question

[KEKSOV]

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

Итак, есть таблица, в которую записываются результаты некоторого эксперимента. События происходят нерегулярно, т.е. могут быть периоды, когда они происходят каждую секунду (но не чаще), а могут быть «застои» по несколько минут

CREATE TABLE experiment
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Ключ записи',

    event_timestamp BIGINT UNSIGNED NOT NULL COMMENT 'Дата и время эксперимента',
    ex_value DECIMAL(11,4) NOT NULL COMMENT 'Результат эксперимента ',

    PRIMARY KEY ( id ),
    UNQUE ( event_timestamp )
) ENGINE=INNODB

Есть вторая таблица следующего вида, которая также содержит нерегулярные по времени события, в нее попадают случайные записи со значениями event_timestamp, которых может и не быть в таблице experiment:

CREATE TABLE random_events
(
    id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Ключ записи',

    event_timestamp BIGINT UNSIGNED NOT NULL COMMENT 'Дата и время эксперимента',

    prev_event_timestamp BIGINT UNSIGNED NULL DEFAULT 0 COMMENT 'Дата и время предыдущего эксперимента',
    next_event_timestamp BIGINT UNSIGNED NULL DEFAULT 0  COMMENT 'Дата и время следующего эксперимента',

    PRIMARY KEY ( id ),
    INDEX ( event_timestamp )
) ENGINE=INNODB

Задача состоит в том, чтобы заполнить prev_event_timestamp и next_event_timestamp в таблице random_events соответствующими значениями из таблицы experiment. Иными словами, нужно узнать между какими двумя ближайшими экспериментами находится точка в таблице random_events.

Сейчас я делаю так для prev_event_timestamp:

UPDATE random_events
SET random_events.prev_event_timestamp = ( 
	SELECT event_timestamp 
	FROM experiment
	WHERE experiment.event_timestamp <= random_events.event_timestamp
	ORDER BY experiment.event_timestamp DESC
	LIMIT 1
)

и вот так для next_event_timestamp:

UPDATE random_events
SET random_events.next_event_timestamp = ( 
	SELECT event_timestamp 
	FROM experiment
	WHERE experiment.event_timestamp > random_events.event_timestamp
	ORDER BY experiment.event_timestamp ASC
	LIMIT 1
)

Все отлично работает, но с увеличением числа записей в random_events >10K все начинает очень тормозить. Причем, второй запрос отрабатывает в пять раз быстрее, чем первый.

Возможно, нужно подкрутить какие-то настройки в my.ini, создать индексы или изменить (переписать совсем иначе) запрос или что-то еще? Вдруг, кто-то уже эффективно решил аналогичную задачу у себя…

Спасибо.

Comments

[dummy2002]

Указанная Вами схема не укладывается в рамки «Нормальной формы» для реляционных баз данных. Естественно, что для поддержания данных в предложенной Вами форме необходимы значительные дополнительные накладные расходы (по сравнению со схемой во второй нормальной форме). Если бы Вы детализировали предметную область, можно было бы предложить Вам оптимизированную схему хранения и заполнения данных.

По предложенной Вами схеме, если обозначить записи таблицы experiment как E, а записи таблицы random_events как R, то насколько я понимаю, возможен следующий хронометраж генерации новых записей в схеме: {EEREEREER}. Рассмотрим последнюю запись R: она подлежит вставке в таблицу, но значение ее поля next_event_timestamp еще не определено, хотя у Вас оно описано как NOT NULL.

[KEKSOV]

dummy2002 Виноват, копипаст попутал… должно быть NULL DEFAULT 0 и в random_events INDEX ( event_timestamp )

Что касается data flow:

В течении суток происходят события (не чаще, чем один раз в минуту). В таблицу experiments они могут быть записаны только после окончания суток. В течении дня в таблицу random_events записываются события из других источников, причем в одну и туже минуту могут произойти несколько событий. Таблица experiments и random_events не имеют между собой никакой другой связи, кроме оси времени. И да, Вы абсолютно правы, «Нормальной формы» тут нет.

[dummy2002]

Насколько важны эти два поля для описания предметной области?

prev_event_timestamp BIGINT UNSIGNED NULL DEFAULT 0 COMMENT 'Дата и время предыдущего эксперимента',
next_event_timestamp BIGINT UNSIGNED NULL DEFAULT 0 COMMENT 'Дата и время следующего эксперимента',

Намеренное введение избыточности для оптимизации времени последующих выборок клиентской части? Может, существует возможность онлайн-ввода данных таблиц experiments и random_events? Я это к тому, что ВОЗМОЖНО ваши последующие трудозатраты с поддержанием костылей (схемы с явной избыточностью и хранением времен экспериментов, а не вторичных ключей) СОИЗМЕРИМЫ с организацией онлайн-импорта и последующей эксплуатации нормальной схемы.

[dummy2002]

Я это к чему, при онлайн-импорте данных в таблицу experiment капают записи не чаще раза в секунду. Для таблицы random_event опять же при онлайн-импорте вы в триггере на вставку можете сделать подзапрос к experiment типа (семантика для Oracle)

CREATE OR REPLACE TRIGGER XXX.BI_RANDOM_EVENT
BEFORE INSERT
ON XXX.RANDOM_EVENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
tmpLastEvent NUMBER;
BEGIN
tmpLastEvent := 0;
select e.id into tmpLastEvent from experiment e where e.event_timestamp < :NEW.event_timestamp;
:NEW.prev_event := tmpLastEvent;
:NEW.next_event := NULL;
end BI_RANDOM_EVENT;

Естественно, здесь указаны вторичные ключи, а не сами значения DATETIME для записей из experiment. Чтобы закрыть NULL-значения в random_event понадобится триггер для вставки в таблицу experiment

CREATE OR REPLACE TRIGGER XXX.BI_EXPERIMENT
BEFORE INSERT
ON XXX.EXPERIMENT
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
BEGIN
update random_event set next_event = :NEW.id where
next_event is NULL;
end BI_EXPERIMENT;

Заметьте, у вас ежесекундно простаивает база, так что триггера будут работать быстро, не нагружая сервер.

[dummy2002]

Сорри, поторопился
select e.id into tmpLastEvent from experiment e where e.event_timestamp < :NEW.event_timestamp;
нужно дополнить до
select e.id into tmpLastEvent from experiment e where e.event_timestamp < :NEW.event_timestamp and rownum = 1 order by event_timestamp desc;

[KEKSOV]

Проблема в том, что данные в random_event попадают раньше (в течении всего дня), чем данные в experiment, а experiment заполняется суточными данными одномоментно в конце суток, иными словами, «растянуть удовольствие» не получится и триггеры не помогут

Поля prev и next нужны мне для проведения некоторых математических расчетов и живут они только во временной таблице, которую я специально создаю для этих расчетов. Альтернативой было бы скачать данные в свою программу и обрабатывать их там, но хочется воспользоваться именно SQL.

Answer 1

[KEKSOV]

dummy2002, ivnik, mib

Спасибо, коллеги, ваши ответы подтолкнули мои мысли в правильном направлении. Если коротко, то идея состоит в создании временных таблиц, содержащих только нужный набор данных. До меня, вдруг, дошло, что мои тысячи случайных экспериментов можно очень красиво сгруппировать по времени события, т.к. они очень часто происходят в течении одной и той же минуты, это существенно снижает вычислительную нагрузку на БД. В итоге, удалось добиться снижения времени обработки с 5 минут, до 5 сек. :)

Вот работающий код, буду признателен за критику и советы:

-- После получения очередных суточных данных формируем временную таблицу, в которую попадают только те данные, которые попадают в диапазон необработанных случайных экспериментов
SELECT @minRandomTimestamp := ( SELECT event_timestamp FROM random_events ORDER BY event_timestamp ASC LIMIT 1 );
DROP TABLE IF EXISTS experiment_tmp;
CREATE TEMPORARY TABLE experiment_tmp AS 
	( -- Одна (старшая) запись из экспериментов за предыдущие сутки
		SELECT event_timestamp, ex_value
		FROM experiments
		WHERE event_timestamp < @minRandomTimestamp 
		ORDER BY event_timestamp DESC
		LIMIT 1 
	)
	UNION 
	( -- Все новые
		SELECT event_timestamp, ex_value
		FROM experiments 
		WHERE event_timestamp >= @minRandomTimestamp 
		ORDER BY event_timestamp ASC 
	) 
;
CREATE UNIQUE INDEX event_timestamp ON experiment_tmp (event_timestamp);

-- Определяем время последнего известного эксперимента
SELECT @maxExperimentTimestamp := ( SELECT event_timestamp FROM experiment_tmp ORDER BY event_timestamp DESC LIMIT 1 );

-- Выбираем во временную таблицу УНИКАЛЬНЫЕ (по времени) события, для которых появились суточные данные. Записей в этой таблице будет не больше, чем минут в сутках
DROP TABLE IF EXISTS random_events_tmp;
CREATE TEMPORARY TABLE random_events_tmp AS 
	SELECT event_timestamp, 0 prev_value_timestamp, 00000000000.0000 prev_value, 0 next_value_timestamp, 00000000000.0000 next_value
	FROM random_events 
	WHERE event_timestamp <= @maxExperimentTimestamp
	GROUP BY event_timestamp
;
CREATE UNIQUE INDEX pt ON random_events_tmp ( event_timestamp );

-- Вычисляем время предыдущего эксперимента
UPDATE random_events_tmp tr
SET tr.prev_value_timestamp = ( 
	SELECT event_timestamp 
	FROM experiment_tmp te
	WHERE te.event_timestamp <= tr.event_timestamp 
	ORDER BY te.event_timestamp DESC
	LIMIT 1
);

-- Проставляем величину предыдущего эксперимента
UPDATE random_events_tmp tr
LEFT JOIN experiment_tmp te ON te.event_timestamp = tr.prev_value_timestamp
SET tr.prev_value = te.ex_value;

-- Вычисляем время следующего эксперимента
UPDATE random_events_tmp tr
SET tr.next_value_timestamp = ( 
	SELECT event_timestamp 
	FROM experiment_tmp te
	WHERE te.event_timestamp > tr.event_timestamp
	ORDER BY te.event_timestamp ASC
	LIMIT 1
);

-- Проставляем величину следующего эксперимента
UPDATE random_events_tmp tr
LEFT JOIN experiment_tmp te ON te.event_timestamp = tr.next_value_timestamp
SET tr.next_value = te.ex_value;

-- Переносим данные в таблицу случайных экспериментов. Это самый долгий запрос > 3 сек.
UPDATE random_events f
LEFT JOIN random_events_tmp tr ON f.event_timestamp = tr.event_timestamp
SET 
	  f.prev_value_timestamp = tr.prev_value_timestamp
	, f.next_value_timestamp = tr.next_value_timestamp
	, f.prev_value = tr.prev_value
	, f.next_value = tr.next_value
;

-- После этого происходит обработка данных на клиенте и обработанные записи удаляются из таблицы random_events

Answer 2

[mib]

Сперва общие рекомендации (на правах имхо :)

Для начала — innodb — довольно медленный движок с транзакциями и откатами. Если эти свойства таблиц вам не нужны — можно попробовать переделать на myisam. Тогда запросы на апдейты будут выполняться гораздо быстрее.

Можно пойти дальше и создать временную таблицу на движке «memory» с результатом слияния двух таблиц, то есть выполнять апдейты в памяти.

Comments

[ivnik]

А ничего что запросы на апдейты в myisam работают с блокированием таблиц? То, что innodb сильно медленнее myisam это заблуждение.
Нашёл тесты на сайте oracle: www.oracle.com/partners/en/knowledge-zone/mysql-5-5-innodb-myisam-522945.pdf

В них говорится:

Read-Write Results
As the graph below shows, InnoDB delivered 35x higher throughput than MyISAM, while
achieving 85% — 90% scalability from 6 to 36-cores.

и

Read-Only Results
InnoDB delivered 4.6x higher throughput than MyISAM, while achieving 90% — 95% scalability
from 6 to 36-cores. Above 30-cores, scalability flattens out as the server is again saturated by a
number of hot mutexes.

[KEKSOV]

От InnoDB отказаться не могу. Нужны транзакции. А вот за идею c memory спасибо, попробую поколдовать, вдруг, что-то получится.

Answer 3

[ivnik]

Второй запрос работает быстро т.к. ограничение целостности UNIQUE по полю event_timestamp создало индекс. А оптимизатор похоже догадался что LIMIT+ORDER_BY не требует сортировки.
В таком случае самое простое, что можно сделать, это хранить в таблице experiment ссылку на предыдущий эксперимент (в виде id или сразу в виде timestamp), и вытаскивать эту запись одним более быстрым запросом.

Comments

[KEKSOV]

Хорошая мысль про хранение prev_timestamp в experiment. Надо будет подумать в этом направлении, если других более приемлемых вариантов не найдется.

[Sayonji]

Оптимизатор может и догадался про order+limit, но можно бы и самому поставить там max/min вместо этой комбинации.

[KEKSOV]

Удивительно, но max min работает в разы медленнее, чем LIMIT 1

[ivnik]

А покажите вариант с max/min.
P.S. А если сделать два поля в таблицe experiment, то можно выбирать просто SELECT… FROM experiment WHERE :time BETWEEN prev_event_timestamp AND event_timestamp, что лучше чем ORDER_BY+LIMIT, т.к. меньше полагаемся на догадливость оптимизатора.

[KEKSOV]

ivnik пардон, не в ту ветку ответил (см.ниже мой коммент)

Answer 4

[KEKSOV]

UPDATE random_events
SET random_events.next_event_timestamp = ( 
    SELECT MIN( event_timestamp )
    FROM experiment
    WHERE experiment.event_timestamp > random_events.event_timestamp
)

с MAX аналогично.

P.S. А если сделать два поля в таблицe experiment

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