Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
 Ускорение UPDATE таблицы по условиям из другой  [new]
squitty
Member

Откуда:
Сообщений: 7
Доброго дня!

Имеется проблема скорости работы запроса 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:

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:

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ГБ.

Есть у кого мысли, как сделать это быстро? И вообще сделать, чтобы не убить сервер?

Сообщение было отредактировано: 29 янв 20, 16:47
29 янв 20, 16:37    [22068835]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Alex_Ustinov
Member

Откуда: Nickel
Сообщений: 2956
ну а SELECT сколько выполняется, это первый вопрос
SELECT Таблица1
RIGHT JOIN Таблица2
ON Таблица1.Идентификатор1 = Таблица2.Идентификатор1 AND Таблица1.ДатаИвремя = Таблица2.ДатаИвремя AND Таблица1.Цена = Таблица2.ЦенаПервая
29 янв 20, 17:18    [22068878]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
crutchmaster
Member

Откуда: оттуда.
Сообщений: 825
squitty,

Индексы какие есть?
30 янв 20, 05:20    [22069180]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
Покажите структуру таблиц в формате CREATE TABLE.
Покажите пример содержимого обеих таблиц так, чтобы за какую-то определённую минуту были показаны все записи из каждой таблицы плюс одна-две до и после.
Покажите финальное состояние первой таблицы, которое требуется получить.
Покажите EXPLAIN запроса UPDATE и соответствующего ему SELECT.

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

squitty
пробовал:
1. Триггер на INSERT в Таблице1:
...
2. Update:
Не понимаю. Инициируются по-разному, влияют на разные наборы записей... как они могли использоваться для решения одной и той же задачи?
30 янв 20, 07:52    [22069200]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
squitty
Member

Откуда:
Сообщений: 7
Alex_Ustinov
ну а SELECT сколько выполняется, это первый вопрос
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
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"141860010613
950"2019-08-01 07:27:00.0"141850011287
950"2019-08-01 07:27:00.0"141840021077
950"2019-08-01 07:27:00.0"14183008955
950"2019-08-01 07:27:00.0"1418200343138
950"2019-08-01 07:27:00.0"14181009563
950"2019-08-01 07:28:00.0"141870033
950"2019-08-01 07:28:00.0"14186001613
950"2019-08-01 07:28:00.0"14185007160
950"2019-08-01 07:28:00.0"14184009379
950"2019-08-01 07:28:00.0"14183003731
950"2019-08-01 07:28:00.0"14182005243
950"2019-08-01 07:28:00.0"1418100247194
950"2019-08-01 07:28:00.0"1418000415140
950"2019-08-01 07:28:00.0"141790033
950"2019-08-01 07:29:00.0"141850013394
950"2019-08-01 07:29:00.0"14184009673
950"2019-08-01 07:29:00.0"14183007348
950"2019-08-01 07:30:00.0"14185009655
950"2019-08-01 07:30:00.0"14184005848
950"2019-08-01 07:30:00.0"141830011794
950"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"14185141861418114181955
950"2019-08-01 07:28:00.0"14181141871417914184937
950"2019-08-01 07:29:00.0"14185141851418314184302
950"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"141860010613
950"2019-08-01 07:27:00.0"141851011287
950"2019-08-01 07:27:00.0"141840021077
950"2019-08-01 07:27:00.0"14183008955
950"2019-08-01 07:27:00.0"1418200343138
950"2019-08-01 07:27:00.0"14181019563
950"2019-08-01 07:28:00.0"141870033
950"2019-08-01 07:28:00.0"14186001613
950"2019-08-01 07:28:00.0"14185007160
950"2019-08-01 07:28:00.0"14184019379
950"2019-08-01 07:28:00.0"14183003731
950"2019-08-01 07:28:00.0"14182005243
950"2019-08-01 07:28:00.0"1418110247194
950"2019-08-01 07:28:00.0"1418000415140
950"2019-08-01 07:28:00.0"141790033
950"2019-08-01 07:29:00.0"141851013394
950"2019-08-01 07:29:00.0"14184019673
950"2019-08-01 07:29:00.0"14183007348
950"2019-08-01 07:30:00.0"14185019655
950"2019-08-01 07:30:00.0"14184105848
950"2019-08-01 07:30:00.0"141830011794
950"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"
1SIMPLEnALL131704100
1UPDATEtALL670875100Using where

Select
"id""select_type""table""partitions""type""possible_keys""key""key_len""ref""rows""filtered""Extra"
1SIMPLEnALL131704100
1SIMPLEtALL670875100Using 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, пользуюсь по необходимости.
Прочитал, что это за зверь такой. Насколько я понял (если не прав – поправьте): индексы – предварительно сортируют данные в колонках. То есть, если я ввожу индекс:
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, это сократит количество обрабатываемых строк?
Или это порнография?

Сообщение было отредактировано: 30 янв 20, 10:57
30 янв 20, 10:48    [22069319]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
См. fiddle.

Сравни планы до и после создания индексов. Обрати внимание на количество обрабатываемых записей (поле rows). А также на то, что обращение к таблице temp_nt вообще не выполняется - индекс даёт всё необходимое для выполнения запроса (т.н. покрывающий индекс).
30 янв 20, 11:44    [22069383]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
squitty
(1) Если правильно понимаю, то UPDATE в любом случае прогоняет всю таблицу даже, если уже нашел и обновил одно поле. (2) Можно ли принудительно прервать UPDATE, если он уже обновил 1 строку, и перешел к поиску значения по следующему ключу поиска?
1. Да.
2. Нет. Вернее, да, но оверхед будет выше профита.

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

Забудь это слово. View в лучшем случае несильно ухудшит производительность - но, как показывает практика, скорее всё же сильно. View - это всего лишь сахарок... воспринимай его как способ не повторять один и тот же код, и молись, чтобы вместо его отдельного выполнения и материализации результата на диск он был подставлен как есть для построения общего плана запроса.

Сообщение было отредактировано: 30 янв 20, 11:48
30 янв 20, 11:48    [22069387]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
И что касательно триггера. Я бы понял, если бы триггер был определён на таблице test_nt. Это было бы логично - минута прошла, все записи по ней в test_sb поступили, теперь информация по этой минуте обработана и пишется в test_nt, а триггер среди записей в test_sb дополнительно помечает нужные записи:

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);


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

Но вот показанный в начале темы триггер... он "ниачём".
30 янв 20, 11:56    [22069396]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
squitty
Member

Откуда:
Сообщений: 7
Akina,

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

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



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

ведь по как строка такого значения не будет, раз перед close стоит open?
30 янв 20, 13:48    [22069533]     Ответить | Цитировать Сообщить модератору
 Re: Ускорение UPDATE таблицы по условиям из другой  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 20016
squitty
почему в Вашем решение Индекс для temp_nt один и сразу включает в себя p_open и p_close?
Они нужны для запроса. Так проще их прямо из индекса брать, чем в таблицу бегать. Я ж сказал - покрывающий индекс (covering index). Почитай где-нить...
squitty
Как в таком случае происходит поиск ключа
Зачем? связывание идёт по первым трём полям - по ним соответственно и поиск.
30 янв 20, 21:43    [22069943]     Ответить | Цитировать Сообщить модератору
Все форумы / MySQL Ответить