Гость
Войти | Профиль | Очистить
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Можно ли оптимизировать запрос? / 18 сообщений из 18, страница 1 из 1
25.12.2020, 11:33
    #40031068
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
MySQL v8. Нужно выбрать контрагентов с имеющимся телефоном и их долги из 2х разных таблиц одинаковой структуры (долг может быть как в одной из таблиц, так и в двух сразу) за самый последний период. Такой запрос делает это ну ооооочень медленно:
Код: sql
1.
2.
3.
4.
5.
6.
7.
SELECT MAX(period) INTO @md FROM data_sgrc;

SELECT c.fio, c.phone, d.debt, dn.debt
FROM contragents c 
LEFT JOIN data_sgrc d ON c.id=d.id_contragent AND d.period=@md
LEFT JOIN data_sgrc_nu dn ON c.id=dn.id_contragent AND dn.period=@md
WHERE c.phone IS NOT NULL


Можно его ускорить? Индексы по всем полям имеются.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 11:56
    #40031072
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
EXPLAIN такой

выбирается 2721 запись из 36931 контрагентов. data_sgrc имеет 34712 записей за период, data_sgrc_nu - 2982.
Время выборки чуть меньше минуты. Намного более сложные запросы выполняются за несколько секунд, к примеру.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 12:31
    #40031079
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Уже переписал и так:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
WITH
t3 AS 
(SELECT id, fio, phone
FROM contragents
WHERE phone IS NOT NULL),

t1 AS
(SELECT d.id_contragent, d.debt debt1
FROM data_sgrc d
WHERE d.period='2020-11-01'),

t2 AS
(SELECT dnu.id_contragent, dnu.debt debt2
FROM data_sgrc_nu dnu
WHERE dnu.period='2020-11-01')

SELECT t3.fio, t3.phone, t1.debt1, t2.debt2
FROM t3
LEFT JOIN t1 ON t1.id_contragent=t3.id 
LEFT JOIN t2 ON t2.id_contragent=t3.id


все равно время выполнения = 30 сек.
Замерил, отдельно запросы:
t3 = 0.8 сек
t1 = 0.4 сек
t2 = 0.1 сек
А итоговый все равно 30 сек. Что не так-то, что я упускаю?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 16:58
    #40031175
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
А если
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT cc.fio, cc.phone, cc.debt, dn.debt
FROM 
(
SELECT c.id, c.fio, c.phone, d.debt
FROM contragents c 
LEFT JOIN data_sgrc d ON c.id=d.id_contragent AND d.period=@md
WHERE c.phone IS NOT NULL
) cc
LEFT JOIN data_sgrc_nu dn ON cc.id=dn.id_contragent AND dn.period=@md


?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 17:48
    #40031195
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Немного офтопа от чайника.
Смысл запроса имхо - получить список должников с телефонами, чтобы позвонить им и высказать свое "фе" (ну, или продать список коллекторам). Зачем тут контрагенты с телефоном, но без долгов - не понятно. "Дорогой контрагент, звоним, чтобы сказать спасибо, что вы нам ничего не должны"!?
Можно ли оптимизировать запрос, если в выборке оставить только должников (ну, то есть как-то избавиться от левых джойнов)?
Типа:
SELECT контрагент, SUM(долг1), SUM(долг2)
FROM
(SELECT контрагент, долг AS долг1 , 0 AS долг2 FROM t3 JOIN t1 USING(контрагент)
UNION
SELECT контрагент, 0, долг FROM t3 JOIN t2 USING(контрагент))
GROUP контрагент
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 18:37
    #40031211
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Akina
А если ... ?

Увы, то же самое - около 30 сек.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 18:42
    #40031213
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
paver
Можно ли оптимизировать запрос, если в выборке оставить только должников (ну, то есть как-то избавиться от левых джойнов)?

Задача выбрать всех контрагентов с телефоном, присовокупив к ним их долги, даже если его нет (долг будет = 0).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 18:50
    #40031214
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Тогда остаётся

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
SELECT fio, phone, MAX(debt), MAX(debt2)
FROM 
(
SELECT c.fio, c.phone, d.debt, 0 debt2
FROM contragents c 
LEFT JOIN data_sgrc d ON c.id=d.id_contragent AND d.period=@md
WHERE c.phone IS NOT NULL
UNION ALL
SELECT c.fio, c.phone, 0, dn.debt
FROM contragents c 
LEFT JOIN data_sgrc_nu dn ON c.id=dn.id_contragent AND dn.period=@md
WHERE c.phone IS NOT NULL
)
GROUP BY fio, phone
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 19:05
    #40031219
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Akina, ругается:

> 1248 - Every derived table must have its own alias
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 21:21
    #40031251
Gluck99
Gluck99
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
LiYing
Akina, ругается:
> 1248 - Every derived table must have its own alias
Так поменяйте алиасы у таблиц, чтобы разные были, и 'алиас.поле' не забудьте тоже исправить. И добавьте алиас для результирующей таблицы, после последней скобки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 22:45
    #40031275
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Akina
Тогда остаётся

Не, и это 28 сек...
Вот прямо интересно, как складывается это время из трех подзапросов, которые по отдельности выполняются совокупно за чуть более секунды? Может еще какие индексы создать? Но какие?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
25.12.2020, 23:16
    #40031282
Gluck99
Gluck99
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
LiYing
Не, и это 28 сек...
Вот прямо интересно, как складывается это время из трех подзапросов, которые по отдельности выполняются совокупно за чуть более секунды? Может еще какие индексы создать? Но какие?
Там временная таблица вероятно создаётся из-за union и group by. Сколько там записей вообще в этих запросах?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 06:06
    #40031320
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
LiYing
Вот прямо интересно, как складывается это время из трех подзапросов, которые по отдельности выполняются совокупно за чуть более секунды?

Реляционное произведение трех выборок (2721 запись, 34712 записей, 2982) дает в результате примерно 300 лярдов кортежей.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 06:17
    #40031321
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
LiYing, можно попробовать еще вариант:
отдельно получить должников из т1 и т2 простыми джойнами, третьим запросом получить честных контрагентов через NOT EXIST, собрать унионом в один запрос и сгруппировать. Ну, т.е. оптимизировать нужно будет именно третий запрос.
Отдельно замерить запросы после объединения и окончательный после группировки.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 07:23
    #40031323
paver
paver
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
LiYing
Уже переписал и так:

А если так?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
WITH
c AS 
(SELECT id, fio, phone
FROM contragents
WHERE phone IS NOT NULL),

d AS
(SELECT id_contragent, debt debt1, 0 dept2
FROM data_sgrc
WHERE period='2020-11-01'
UNION ALL
SELECT id_contragent, 0, debt
FROM data_sgrc_nu
WHERE dnu.period='2020-11-01')

SELECT c.fio, c.phone, MAX(d.debt1), MAX(d.debt2)
FROM c
LEFT JOIN d ON d.id_contragent=c.id 
GROUP BY c.fio, c.phone



Добавляется группировка, но по произведению только 2 таблиц (примерно 3К на 40К)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 08:58
    #40031326
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
paver

А если так?
...
Добавляется группировка, но по произведению только 2 таблиц (примерно 3К на 40К)

Йес, 0.3 сек! Только чуть поправил группировку: сделал по id контрагента, т.к. ФИО+телефон не уникальны, из-за этого выбиралось чуть меньше записей. Супер, спасибо!
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 11:12
    #40031339
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Тогда что-то неладное у Вас в системе... оперативки мало, что ли?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
26.12.2020, 12:37
    #40031354
LiYing
LiYing
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Можно ли оптимизировать запрос?
Akina
Тогда что-то неладное у Вас в системе... оперативки мало, что ли?

Возможно что-то в конфиге надо поправить, он практически дефолтный. На что посмотреть, подскажите.
А так на сервере ОЗУ 32 гига, из них свободно около 20, MySQL отжирает примерно 1 (в простое или под легкой нагрузкой).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Можно ли оптимизировать запрос? / 18 сообщений из 18, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 252ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1828262]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 131ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 13ms
total:  252ms
x
x
Закрыть


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