ReSQL.ru
     
powered by simpleCommunicator - 2.0.51     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Ускорение UPDATE таблицы по условиям из другой
10 сообщений из 10, страница 1 из 1
Ускорение UPDATE таблицы по условиям из другой
    #39920068
squitty
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
squitty
Гость
Доброго дня!

Имеется проблема скорости работы запроса UPDATE.
Дано:

Server version: 5.7.21

1. Таблица1
- строк: 1 000 000
- размер: 60 мб
- структура:
*идентификатор1
*идентификатор2
*дата и время
*цена
*цена_первая (пусто, нужно обновить)
*цена_последняя (пусто, нужно обновить)
*сумма
*сделки

2. Таблица2
- строк: 300 000
- размер: 6,5 мб
- структура:
*идентификатор1
*идентификатор2
*дата и время
*цена_первая
*цена_мин
*цена_макс
*цена_последняя

Отличия Таблицы1 и Таблицы2 - в Таблице1 представлены сделки в разрезе минут (т.е. на одну минуту может приходиться несколько строк с разной ценой), в Таблице2 представлены результирующие цены по каждой минуте (т.е. 1 строка с Первой, Минимальной, Максимальной и Последней ценой).

Задача: Проставить в Таблице1 признак Первой и Последней цены. Т.е. найти в нескольких строках соответствующего времени Цену, которая соответствует Первой цене в этом диапазоне. Аналогично для Последней цены.

Проблема: скорость работы запроса/триггера.

Что пробовал:
1. Триггер на INSERT в Таблице1:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
BEGIN
DECLARE opr INT;
DECLARE cpr INT;

SELECT ЦенаПервая INTO opr FROM (select ЦенаПервая from Таблица2 where ДатаИвремя = new.ДатаИвремя and ЦенаПервая = new.Цена and Идентификатор1 = new.Идентификатор1) as alias;

SELECT ЦенаПоследняя INTO cpr FROM (select ЦенаПоследняя from Таблица2 where ДатаИвремя = new.ДатаИвремя and ЦенаПоследняя = new.Цена and Идентификатор1 = new.Идентификатор1) as alias;

IF new.Цена = opr THEN
SET new.ЦенаПервая = 1;
END IF;

IF new.Цена = cpr THEN
SET new.ЦенаПоследняя = 1;
END IF;

END




Результат: нереально долго. Примерно 1,5 секунды на 1 строку. Процесс убил, не дождался. Итого, обновить 1 000 000 более 50 часов, долго.
P.S. В обратную сторону тоже пробовал. То есть тригерр для таблицы 2 при INSERT делать запрос UPDATE. Результат тот же - 1,5 секунды на запрос.

2. Update:

Код: sql
1.
2.
3.
4.
UPDATE Таблица1
RIGHT JOIN Таблица2
ON Таблица1.Идентификатор1 = Таблица2.Идентификатор1 AND Таблица1.ДатаИвремя = Таблица2.ДатаИвремя AND Таблица1.Цена = Таблица2.ЦенаПервая
SET Таблица1.ЦенаПервая = 1




Результат: такой же как Триггера, правда в секундах не замерял. Прождал 20 минут, убил процесс. В моменте Таблица2 по размера раздувалась с 6,5 мб до 6,8ГБ.

Есть у кого мысли, как сделать это быстро? И вообще сделать, чтобы не убить сервер?
...
Рейтинг: 0 / 0
29.01.2020, 16:37
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920100
Фотография Alex_Ustinov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Alex_Ustinov
Участник
ну а SELECT сколько выполняется, это первый вопрос
Код: sql
1.
2.
3.
SELECT Таблица1
RIGHT JOIN Таблица2
ON Таблица1.Идентификатор1 = Таблица2.Идентификатор1 AND Таблица1.ДатаИвремя = Таблица2.ДатаИвремя AND Таблица1.Цена = Таблица2.ЦенаПервая
...
Рейтинг: 0 / 0
29.01.2020, 17:18
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920268
Фотография crutchmaster
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
crutchmaster
Участник
squitty,

Индексы какие есть?
...
Рейтинг: 0 / 0
30.01.2020, 05:20
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920282
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Покажите структуру таблиц в формате CREATE TABLE.
Покажите пример содержимого обеих таблиц так, чтобы за какую-то определённую минуту были показаны все записи из каждой таблицы плюс одна-две до и после.
Покажите финальное состояние первой таблицы, которое требуется получить.
Покажите EXPLAIN запроса UPDATE и соответствующего ему SELECT.

Вообще - подозреваю, что нет подходящих индексов.

squitty
пробовал:
1. Триггер на INSERT в Таблице1:
...
2. Update:
Не понимаю. Инициируются по-разному, влияют на разные наборы записей... как они могли использоваться для решения одной и той же задачи?
...
Рейтинг: 0 / 0
30.01.2020, 07:52
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920355
squitty
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
squitty
Гость
Alex_Ustinov
ну а SELECT сколько выполняется, это первый вопрос
Код: sql
1.
2.
3.
SELECT Таблица1
RIGHT JOIN Таблица2
ON Таблица1.Идентификатор1 = Таблица2.Идентификатор1 AND Таблица1.ДатаИвремя = Таблица2.ДатаИвремя AND Таблица1.Цена = Таблица2.ЦенаПервая


5,874 секунды с LIMIT 200, это через клиент. Лимитированная нагрузка на сервер, сконцентрируюсь пока на тестах обновления. Но намек понятен – не быстрый SELECT, с чего бы быть быстрому UPDATE?
Akina
Покажите структуру таблиц в формате CREATE TABLE.

temp_sb = Таблица 1
temp_nt = Таблица 2
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE TABLE `temp_sb` (
  `ticker` tinyint(4) unsigned NOT NULL,
  `contract` tinyint(3) unsigned NOT NULL,
  `dt` datetime NOT NULL,
  `price` mediumint(9) unsigned NOT NULL,
  `p_open` tinyint(1) unsigned NOT NULL,
  `p_close` tinyint(1) unsigned NOT NULL,
  `volume` smallint(6) unsigned NOT NULL,
  `trades` smallint(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `temp_nt` (
  `ticker` tinyint(4) unsigned NOT NULL,
  `contract` tinyint(6) unsigned NOT NULL,
  `dt` datetime NOT NULL,
  `p_open` mediumint(9) unsigned NOT NULL,
  `p_high` mediumint(9) unsigned NOT NULL,
  `p_low` mediumint(9) unsigned NOT NULL,
  `p_close` mediumint(9) unsigned NOT NULL,
  `volume` smallint(6) unsigned NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8



Akina

Покажите пример содержимого обеих таблиц так, чтобы за какую-то определённую минуту были показаны все записи из каждой таблицы плюс одна-две до и после.

temp_sb – 3 минуты:
"ticker""contract""dt""price""p_open""p_close""volume""trades"950"2019-08-01 07:27:00.0"141860010613950"2019-08-01 07:27:00.0"141850011287950"2019-08-01 07:27:00.0"141840021077950"2019-08-01 07:27:00.0"14183008955950"2019-08-01 07:27:00.0"1418200343138950"2019-08-01 07:27:00.0"14181009563950"2019-08-01 07:28:00.0"141870033950"2019-08-01 07:28:00.0"14186001613950"2019-08-01 07:28:00.0"14185007160950"2019-08-01 07:28:00.0"14184009379950"2019-08-01 07:28:00.0"14183003731950"2019-08-01 07:28:00.0"14182005243950"2019-08-01 07:28:00.0"1418100247194950"2019-08-01 07:28:00.0"1418000415140950"2019-08-01 07:28:00.0"141790033950"2019-08-01 07:29:00.0"141850013394950"2019-08-01 07:29:00.0"14184009673950"2019-08-01 07:29:00.0"14183007348950"2019-08-01 07:30:00.0"14185009655950"2019-08-01 07:30:00.0"14184005848950"2019-08-01 07:30:00.0"141830011794950"2019-08-01 07:30:00.0"141820032
temp_nt – 3 минуты:
ticker"contract""dt""p_open""p_high""p_low""p_close""volume"950"2019-08-01 07:27:00.0"14185141861418114181955950"2019-08-01 07:28:00.0"14181141871417914184937950"2019-08-01 07:29:00.0"14185141851418314184302950"2019-08-01 07:30:00.0"14184141851418214185274

Akina

Покажите финальное состояние первой таблицы, которое требуется получить.

temp_sb – 3 результат:
"ticker""contract""dt""price""p_open""p_close""volume""trades"950"2019-08-01 07:27:00.0"141860010613950"2019-08-01 07:27:00.0"14185 1 011287950"2019-08-01 07:27:00.0"141840021077950"2019-08-01 07:27:00.0"14183008955950"2019-08-01 07:27:00.0"1418200343138950"2019-08-01 07:27:00.0"141810 1 9563950"2019-08-01 07:28:00.0"141870033950"2019-08-01 07:28:00.0"14186001613950"2019-08-01 07:28:00.0"14185007160950"2019-08-01 07:28:00.0"141840 1 9379950"2019-08-01 07:28:00.0"14183003731950"2019-08-01 07:28:00.0"14182005243950"2019-08-01 07:28:00.0"14181 1 0247194950"2019-08-01 07:28:00.0"1418000415140950"2019-08-01 07:28:00.0"141790033950"2019-08-01 07:29:00.0"14185 1 013394950"2019-08-01 07:29:00.0"141840 1 9673950"2019-08-01 07:29:00.0"14183007348950"2019-08-01 07:30:00.0"141850 1 9655950"2019-08-01 07:30:00.0"14184 1 05848950"2019-08-01 07:30:00.0"141830011794950"2019-08-01 07:30:00.0"141820032

Akina

Покажите EXPLAIN запроса UPDATE и соответствующего ему SELECT.

Update:
"id""select_type""table""partitions""type""possible_keys""key""key_len""ref""rows""filtered""Extra"1SIMPLEnALL1317041001UPDATEtALL670875100Using where
Select
"id""select_type""table""partitions""type""possible_keys""key""key_len""ref""rows""filtered""Extra"1SIMPLEnALL1317041001SIMPLEtALL670875100Using where; Using join buffer (Block Nested Loop)

Akina

squitty
пробовал:
1. Триггер на INSERT в Таблице1:
...
2. Update:
Не понимаю. Инициируются по-разному, влияют на разные наборы записей... как они могли использоваться для решения одной и той же задачи?

Возвращают один результат.
В случае триггера сначала находит p_open и p_close по условию в test_nt, записывает в переменные.
Далее, если цена соответствует одному из значений, проставляет признак 1 в нужном столбце.
Akina
Вообще - подозреваю, что нет подходящих индексов.

crutchmaster
squitty,
Индексы какие есть?

Никаких. Не Гуру в SQL, пользуюсь по необходимости.
Прочитал, что это за зверь такой. Насколько я понял (если не прав – поправьте): индексы – предварительно сортируют данные в колонках. То есть, если я ввожу индекс:
Код: sql
1.
2.
3.
CREATE INDEX upd_sb_price ON test_sb (contract, dt, price);
CREATE INDEX upd_nt_p_open ON test_nt (contract, dt, p_open);
CREATE INDEX upd_nt_p_open ON test_nt (contract, dt, p_close);


MySQL проводит сортировку по указанным в Индексе столбцам и по сути для поиска обращается не к трем колонкам по очереди, а к одной – индексу. Верно?
Вообще, в обоих таблицах данные уже сортированные... но если весь смак в обращение к Индексу как к одному столбцу, а не 3...

И еще такой вопрос возник. Если правильно понимаю, то UPDATE в любом случае прогоняет всю таблицу даже, если уже нашел и обновил одно поле. Можно ли принудительно прервать UPDATE, если он уже обновил 1 строку, и перешел к поиску значения по следующему ключу поиска? LIMIT насколько я понимаю обрежет просто после обновления 1 строки и к следующему ключу не пойдет.

Либо еще такая грешная мысль. Вместо Update Right Join создать Представление с объединением этих таблиц для:
- вывода не 16 столбцов, а 4 (contract, dt, p_open, p_close)
- добавить условие WHERE p_open (или p_close) <> 0, это сократит количество обрабатываемых строк?
Или это порнография?
...
Рейтинг: 0 / 0
30.01.2020, 10:48
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920401
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
См. fiddle .

Сравни планы до и после создания индексов. Обрати внимание на количество обрабатываемых записей (поле rows). А также на то, что обращение к таблице temp_nt вообще не выполняется - индекс даёт всё необходимое для выполнения запроса (т.н. покрывающий индекс).
...
Рейтинг: 0 / 0
30.01.2020, 11:44
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920403
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
squitty
(1) Если правильно понимаю, то UPDATE в любом случае прогоняет всю таблицу даже, если уже нашел и обновил одно поле. (2) Можно ли принудительно прервать UPDATE, если он уже обновил 1 строку, и перешел к поиску значения по следующему ключу поиска?
1. Да.
2. Нет. Вернее, да, но оверхед будет выше профита.

squitty
Либо еще такая грешная мысль. Вместо Update Right Join создать Представление

Забудь это слово. View в лучшем случае несильно ухудшит производительность - но, как показывает практика, скорее всё же сильно. View - это всего лишь сахарок... воспринимай его как способ не повторять один и тот же код, и молись, чтобы вместо его отдельного выполнения и материализации результата на диск он был подставлен как есть для построения общего плана запроса.
...
Рейтинг: 0 / 0
30.01.2020, 11:48
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920407
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
И что касательно триггера. Я бы понял, если бы триггер был определён на таблице test_nt. Это было бы логично - минута прошла, все записи по ней в test_sb поступили, теперь информация по этой минуте обработана и пишется в test_nt, а триггер среди записей в test_sb дополнительно помечает нужные записи:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
CREATE TRIGGER tr_test_nt
AFTER INSERT
ON test_nt
FOR EACH ROW
UPDATE test_sb 
SET p_open = (price = NEW.p_open),
    p_close = (price = NEW.p_close)
WHERE (ticker, contract, dt) = (NEW.ticker, NEW.contract, NEW.dt);



Т.е. фактически маркировать "на лету", чтобы не надо было периодически выполнять процедуру простановки признаков по всей таблице.

Но вот показанный в начале темы триггер... он "ниачём".
...
Рейтинг: 0 / 0
30.01.2020, 11:56
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920485
squitty
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
squitty
Гость
Akina,

Спасибо Вам огромное! Запрос на обновление прошел 6,39 секунды.
Последний вопрос по логике - почему в Вашем решение Индекс для temp_nt один и сразу включает в себя p_open и p_close?

Если я правильно понял, то Индекс в конечном итоге представляет собой, и при поиске принимается за самостоятельную строку:
Код: sql
1.
2.
ticker+contract+dt+p_open+p_close
9502019-08-01 07:27:001485014862




Как в таком случае происходит поиск ключа:
Код: sql
1.
ticker+contract+dt+p_close


ведь по как строка такого значения не будет, раз перед close стоит open?
...
Рейтинг: 0 / 0
30.01.2020, 13:48
| Ответить | Цитировать | Написать  
Ускорение UPDATE таблицы по условиям из другой
    #39920775
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
squitty
почему в Вашем решение Индекс для temp_nt один и сразу включает в себя p_open и p_close?
Они нужны для запроса. Так проще их прямо из индекса брать, чем в таблицу бегать. Я ж сказал - покрывающий индекс (covering index). Почитай где-нить...
squitty
Как в таком случае происходит поиск ключа
Зачем? связывание идёт по первым трём полям - по ним соответственно и поиск.
...
Рейтинг: 0 / 0
30.01.2020, 21:43
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Ускорение UPDATE таблицы по условиям из другой
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=39920775&tid=1828763]:
 0ms
get settings:
 9ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 84ms
get topic data:
 11ms
get forum data:
 2ms
get page messages:
 48ms
get tp. blocked users:
 2ms
others: 15ms
total:  192ms
созд. / загр.: 192ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]