Меню
Что нового?

Запрос MySQL на выборку. Оптимизация.

mak_

Новичок
Добрый день.
Есть таблица с товарами, в которой указан текущий остаток.
Есть вторая таблица с заданиями на обновление информации о товаре на сайте.
Надо выбрать все товары, у которых нет успешных заданий на обновление или количество товара в последнем успешном задании не совпадает с текущим количеством.

Используемый запрос выполняется очень долго, вплоть до 500-600 секунд (сейчас товаров 20000, записей в логе обновления по нескольку десятков на товар).
Как его можно оптимизировать?


Код: 
SELECT DISTINCT t310.id as id_n, 
                t310.n as name, 
                t310.kol as kol, 
                supplier_log.*
FROM 
                #номенклатура
                t310 
                #последнее успешное задание обновления или NULL
                 LEFT JOIN (SELECT t380.*
                            FROM 
                                t380
                             JOIN (SELECT 
                                            t380.id_l_n, 
                                            max(t380.id) as `id_max` 
                                        FROM 
                                            t380
                                         WHERE 
                                            t380.status = 0 AND 
                                            t380.tip = 'kol' AND
                                            t380.stat = 'успех'
                                        GROUP BY 
                                            t380.id_l_n
                                    ) as tmp_supplier_log 
                            ON t380.id = tmp_supplier_log.id_max 
                            ) as supplier_log
                ON t310.id = supplier_log.id_l_n
WHERE
                t310.kol <> supplier_log.value OR
                supplier_log.id_l_n IS NULL
Алгоритм: все товары объединяем с подтаблицей логов, в которой оставлены только последние успешные записи выгрузки. Из полученных строк оставляем только с не совпадающим количеством или те у кого нет успешных выгрузок.

таблица t310 - содержит товар
таблица t380 - логи выгрузки на сайт, связь с товарами по полю id_l_n.
 
antson

antson

Новичок
Партнер клуба
какая то странная система обновления.
обычно есть выгрузка из 1с, которая обновляется атомарно.
То что успешно обновилось, продолжает продаваться дальше, остальное 404 или статус нет на складе.
логи ошибок обновления на почту, а там уже сразу поиск причины сбоя.

это так лирика.

если сбоев в базе мало, то можно сперва найти идишники товаров с проблемами , а по логам до расшифровать следующем запросом.

в результате основной запрос ищущий проблемы сведется до фултабле скан и легкого подзапроса кол-ва товара в последнем успешном обновлении
select id from tovary where idelete=0 and kolvo<>(select kolvo from protokol where успех order by id desc limit 1)
 

mak_

Новичок
это не 1С, это web БД, обмен в реальном (почти) времени - изменения товаров создают задания, которые выгружаются скриптом по крону.
иногда задания не создаются. по разным причинам.
в итоге изменение есть а выгрузки нет, т.е. на сайте старая инфо. надо периодически отлавливать такие товары, в первую очередь по наличию...

что в запросе значит idelete=0?

и kolvo<>(select kolvo from protokol where успех order by id desc limit 1)
как реализовать? через EXISTS?

у меня запрос те же самые данные и получает, но таблица товаров очень долго объединяется с таблицей заданий
 
antson

antson

Новичок
Партнер клуба
isdelete=0 - как пример того, что не нужно шерстить все таблицу. Я не знаю как у вас реализован учет товаров, которые сейчас не надо отображать.
т.е. это может быть show_flag = 1 или еще что-то, просто удалять с сайта товар с описанием и загруженными фото из таблицы слишком расточительно.
интернет магазины практически не сносят с сайта страницы, собирая переходы и рекламирую на них аналогичные товары.

субординарные запрос должен возвращать одно и только одно значение или же нулл.
если поле количества товаров в таблице t310 описано с дефаулт значением 0, то достаточно сравнения со значением подзапроса
если там по дефолту оставлен нулл, то проверять нужно

select t310.id from t310 where isnull(t310.kol) or t310.kol<>подзапрос
сам подзапрос
(select value from t380 where t380.status = 0 AND t380.tip = 'kol' AND t380.stat = 'успех' and t380.id_l_n = t310.id ..... тут пропушен лимит и ордер бу)
ну гдето так , на сколько я полял поля.

пробуй. Дальше только платно ;)
 
antson

antson

Новичок
Партнер клуба
для оптимизации существующего же запроса покажи структуры таблиц t310 и t380 и explain запроса.
 

mak_

Новичок
в первом посте упростил, что бы не тратить зря ваше время.
сам запрос полностью:
Код: 
SELECT DISTINCT cb_data310.id AS id_n,
                cb_data310.f4640 AS name,
                cb_data310.f5750 AS kol,
                supplier_log.*
FROM 
#номенклатура 
cb_data310 
#последнее успешное задание обновления наличия или NULL
LEFT JOIN
  (SELECT cb_data380.*
   FROM cb_data380
   JOIN
     (SELECT cb_data380.f5680,
             max(cb_data380.id) AS `id_max`
      FROM cb_data380
      WHERE cb_data380.status = 0
        AND cb_data380.f5620 = 'f5750'
        AND cb_data380.f5650 = 'успех'
      GROUP BY cb_data380.f5680) AS tmp_supplier_log ON cb_data380.id = tmp_supplier_log.id_max) AS supplier_log ON cb_data310.id = supplier_log.f5680
WHERE cb_data310.f5750 <> supplier_log.f7550
  OR supplier_log.f5680 IS NULL 
LIMIT 10000
структура полей участвующих в запросе

таблица cb_data310:
Имя Тип Null По умолчанию Дополнительно
id-Первичный int(11) Нет Нет AUTO_INCREMENT
f4640 varchar(255) Нет Нет
f5750-Индекс decimal(10,0)Нет Нет

таблица cb_data380:
Имя Тип Null По умолчанию Дополнительно
idПервичный int(11) Нет Нет AUTO_INCREMENT
f5680Индекс int(11) Нет 0
status tinyint(1) Нет 0
f5620 varchar(255) Нет Нет
f5650 varchar(255) Нет Нет
f7550 varchar(255) Нет Нет

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY cb_data310 ALL NULL NULL NULL NULL 26346 Using temporary
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 25861 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 25861
2 DERIVED cb_data380 eq_ref PRIMARY PRIMARY 4 tmp_supplier_log.id_max 1
3 DERIVED cb_data380 index NULL f5680 4 NULL 278885 Using where

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

mak_

Новичок
запрос получился такой:
Код: 
EXPLAIN
SELECT cb_data310.id
FROM   cb_data310
WHERE
cb_data310.f5750 <>
  (SELECT cb_data380.f5680
      FROM cb_data380
      WHERE cb_data380.status = 0
        AND cb_data380.f5620 = 'f5750'
        AND cb_data380.f5650 = 'успех'
        AND cb_data380.f5680 = cb_data310.id
      ORDER BY cb_data380.id DESC LIMIT 1)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY cb_data310 index NULL f5750 5 NULL 27877 Using where; Using index
2 DEPENDENT SUBQUERY cb_data380 index f5680 PRIMARY 4 NULL 1 Using where

phpmyadmin не смог выполнить этот запрос, отвалился по таймауту.
получается он еще тяжелее чем мой...
 
antson

antson

Новичок
Партнер клуба
на cb_data380 не пойму индексов нет ?
добавь 2 индекса
1) по коду товара
2) по трем полям status,f5620,f5650
 
antson

antson

Новичок
Партнер клуба
explain
SELECT cb_data380.f5680
FROM cb_data380
WHERE cb_data380.status = 0
AND cb_data380.f5620 = 'f5750'
AND cb_data380.f5650 = 'успех'
AND cb_data380.f5680 = конкретное_значение
ORDER BY cb_data380.id DESC LIMIT 1
 

mak_

Новичок
antson написал(а):
на cb_data380 не пойму индексов нет ?
добавь 2 индекса
1) по коду товара
2) по трем полям status,f5620,f5650
Нажмите для раскрытия...
1. индекс есть на f5680, это как раз связь номенклатура, код товара.
2. добавил.

explain
SELECT cb_data380.f5680
FROM cb_data380
WHERE cb_data380.status = 0
AND cb_data380.f5620 = 'f5750'
AND cb_data380.f5650 = 'успех'
AND cb_data380.f5680 = конкретное_значение
ORDER BY cb_data380.id DESC LIMIT 1
Нажмите для раскрытия...
до добавления индекса Запрос занял 0.0003 сек.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cb_data380 ref f5680 f5680 4 const 1 Using where

после добавления Запрос занял 0.0005 сек.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE cb_data380 ref f5680,status,f5620,f5650 f5680 4 const 14 Using where
 

mak_

Новичок
вообще я вижу проблему в том, что при связи номенклатуры и таблицы которая получается при выборке из лога, MySQL не использует индекс id номенклатура -> связь f5680, хотя он у этого поля есть.
видимо скуль не понимает, что это то самое поле что и в оригинальной таблице, а как его заставить понять не знаю...

может надо подтаблицу
Код: 
  (SELECT cb_data380.*
   FROM cb_data380
   JOIN
     (SELECT cb_data380.f5680,
             max(cb_data380.id) AS `id_max`
      FROM cb_data380
      WHERE cb_data380.status = 0
        AND cb_data380.f5620 = 'f5750'
        AND cb_data380.f5650 = 'успех'
      GROUP BY cb_data380.f5680) AS tmp_supplier_log ON cb_data380.id = tmp_supplier_log.id_max) AS supplier_log
сортировать по cb_data380.f5680?
или как то можно ключ по этому полю вынести в JOIN?
чтобы при соединении
Код: 
cb_data310
LEFT JOIN
  (SELECT cb_data380.*
использовался индекс
 
antson

antson

Новичок
Партнер клуба
@mak_, запросы твой первичный и мой теперь после добавления индекса как себя ведут ?
 
antson

antson

Новичок
Партнер клуба
(select id from aaa) a не будет использовать индексы
 

mak_

Новичок
antson написал(а):
@mak_, запросы твой первичный и мой теперь после добавления индекса как себя ведут ?
Нажмите для раскрытия...
мой Отображение строк 0 - 119 (120 всего, Запрос занял 516.1430 сек.)
ваш также - ошибка 504 gateway time-out

на сервере стоит ограничение php 15 минут, значит ваш вариант выполняется более 900 секунд.
 

mak_

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