ReSQL.ru
     
powered by simpleCommunicator - 2.0.50     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Можно ли оптимизировать запрос?
18 сообщений из 18, страница 1 из 1
Можно ли оптимизировать запрос?
    #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:33
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #40031072
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
EXPLAIN такой

выбирается 2721 запись из 36931 контрагентов. data_sgrc имеет 34712 записей за период, data_sgrc_nu - 2982.
Время выборки чуть меньше минуты. Намного более сложные запросы выполняются за несколько секунд, к примеру.
...
Рейтинг: 0 / 0
25.12.2020, 11:56
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #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, 12:31
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #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, 16:58
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #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, 17:48
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #40031211
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina
А если ... ?

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

Задача выбрать всех контрагентов с телефоном, присовокупив к ним их долги, даже если его нет (долг будет = 0).
...
Рейтинг: 0 / 0
25.12.2020, 18:42
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #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, 18:50
| Ответить | Цитировать | Написать  
Можно ли оптимизировать запрос?
    #40031219
LiYing
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LiYing
Гость
Akina, ругается:

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

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

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

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

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

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


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