Форум программистов [Powered by Invision Power Board]

Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > Помогите оптимизировать

Автор: DonPager 29.6.2011, 10:28

Дрась,
есть запрос из 3х таблиц:
EventLog(id,map_id,...)
NodeTable(id,node_id, parent_id, label,...)
Subnets (id, subnet_id , label,...)

Код


select id, date_time, priority, trim(message) as Mes, map_id,
    (select label from NodeTable where node_id=map_id order by id desc limit 1) NodeName,
    (Select label from Subnets where subnet_id in(select parent_id from NodeTable where node_id=map_id)order by id desc limit 1) as SubNetName    
from EventLog
where priority<7
order by EventLog.id desc 
limit 50;

Запрос корректно отрабатывает, но долго :( хочется оптимизировать. Чувствую, что нужно как-то избавиться от подзапроса

Код


(select parent_id from NodeTable where node_id=map_id)order by id desc limit 1)

но не дам ума как. джоин тут не канает - т.к. в NodeTable и Subnets записи не уникальные и нужны последние из них

база mysql

Спасибо.

Автор: Zloxa 29.6.2011, 10:55

индексы: NodeTable (node_id,id), Subnets(subnet_id, id), EventLog(priority,id)

Автор: DonPager 29.6.2011, 10:59

Текущую структуру БД менять нельзя :( есть доступ только на чтение
сейчас стоят одномерные индексы (id) для каждой из таблиц и менять их врятли кто-то будет

UPD: если бы можно было переделали бы структуру с добавлением historySubnets, historyNodes - но нельзя :(

Автор: Zloxa 29.6.2011, 11:04

Цитата(DonPager @ 29.6.2011, 10:59

)

одномерные индексы (id)

они для этого запроса - бесполезны.

Цитата(DonPager @ 29.6.2011, 10:59

)

Текущую структуру БД менять нельзя

тогда ничего больше не остается, поможет только хинт do_it_fast smile

/*сарказм*/

Автор: triclosan 29.6.2011, 11:43

DonPager, Что-то запрос у вас мутный уж очень, может опишите структуру и что вы выбираете из них?

Цитата(DonPager @ 29.6.2011, 10:28

)

select label from NodeTable where node_id=map_id order by id desc limit 1

вот этот подзапрос и подобные сильно отдают некорректностью.

Автор: DonPager 29.6.2011, 13:50

таблица EventLog

Код


select id, date_time, message, map_id from EventLog  where priority<3 limit 10;

таблица NodeTable

Код


#Первая строчка из пред. запроса
select id,node_id,label, parent_id from NodeTable where node_id='8237551-48217';

таблица Subnets

Код


#Первая строчка из пред. запроса
select id,subnet_id,label, parent_id from Subnets where subnet_id='8237551-652';

Автор: triclosan 29.6.2011, 14:17

скиньте пожалуйста дамп таблиц (по десятку записей хотя бы)

Автор: Zloxa 29.6.2011, 14:46

Цитата(triclosan @ 29.6.2011, 11:43)

Цитата(DonPager @ 29.6.2011, 10:28

)

select label from NodeTable where node_id=map_id order by id desc limit 1

вот этот подзапрос и подобные сильно отдают некорректностью.

Мне не понятно где ты видишь некорректность. Как еще "более корректно" выбрать лабел ноды со старшим номером версии? Я так понимаю версия определяется, судя по всему, автоинкрементом, потому по недоумию названа id, и, возможно даже определена как ПК, чтобы прочнее сбивать с толку ))

Если сделать alter table NodeTable rename id to version#, отдача некорректностью не устранится ли? smile

Автор: DonPager 29.6.2011, 14:48

Цитата

скиньте пожалуйста дамп таблиц (по десятку записей хотя бы)

А чем не устраивает данная иллюстрация?

В абстрактном варианте вот вариант:
А(id,data,iKey)=
(1,'A','10'),
(2,'B','11')

B(id,key,data,sKey)=
(1,'10','AAA','20'),
(2,'10','BBB','20'),
(3,'11','CCC','20'),
(4,'12','DDD','21')

C(id,key,data)=
(1,'20','aaa'),
(2,'20','bbb'),
(3,'11','ccc'),
(4,'12','ddd')

Код


select id, data, iKey,
    (Select data from B where key=A.iKey order by id desc limit 1),
    (Select data from C where key in(select sKey from B where key=A.iKey)order by id desc limit 1) 
from A;

Выдаст (что и требуется):
1, 'A', '10', 'BBB','bbb'
2, 'B', '11', 'CCC', 'bbb'

Остаётся только оптимизировать запрос - в этом и вопрос КАК?

Автор: triclosan 29.6.2011, 15:33

Цитата(Zloxa @ 29.6.2011, 14:46

)

Мне не понятно где ты видишь некорректность.

Виноват, мне там group by привиделся, ну а если придираться, то limit в подзапросах это не очень хорошо - во-первых это кажись не поддерживается в старых версиях сервера, во-вторых делает запрос mysql-зависимым

Код


select T.id, T.data, T.ikey, B.data, C.data
from B, C,
(select A.id, A.data, A.ikey, max(B.Id) as max_bid, max(C.id) as max_cid
from A, B, C
where 
A.ikey = B.key
and B.skey = C.key
group by A.id, A.data, A.ikey) T
where B.id = T.max_bid
and C.id = T.max_cid

не уверен, что стало лучше smile

Автор: DonPager 29.6.2011, 15:57

Цитата(triclosan @ 29.6.2011, 07:33

)

не увере, что стало лучше

не только НЕ лучше, но и хуже - при лимите в 50 записей мой вариант - 5 сек, твой - 9

за сим думаю решено поставлю - ибо чуда не произошло.

Автор: triclosan 29.6.2011, 16:05

DonPager, лимит тут ни при чем, он отрезает все, после 50 строки после полной выборки по всей таблице, можно сделать ухищрение типа EventLog.id > HINT_VALUE, где хинт порог выборки "чуть больше, чем 50"

Автор: DonPager 29.6.2011, 16:14

Имелось в виду, что выборку в подзапросе отфильтровл до 50 записей с уникальным id ... -но это не суть
чуда то всё равно не произошло =)... наверное забыл дунуть(с) Акопян

Автор: triclosan 29.6.2011, 16:24

Фильтрование лимитом не приведет к ускорению работы запроса, если вас это устроит можете выполнять предварительный запрос для анализа по какому значению id фильтровать как-то так:

Код


SET @rank:=0;
select max(AT.id) from (
select @rank:=@rank+1 as rank1, A.id 
from A
order by A.id desc
) AT
where AT.rank1 <= 50;

или у вас не каждому A.id отвечают записи в таблицах B, C?

Автор: Zloxa 29.6.2011, 16:28

DonPager, 6 секунд это не то время, которое имеет смысл оптимизировать, если запрос одноразовый и к чужой базе. Если же база своя и запрос не одноразовый, а продуктивный, то Вы, как минимум - разраб. Скажите пожалуйста, какая именно религия вас побуждает ожидать чудес вместо построения необходимых индексов?

Автор: DonPager 29.6.2011, 17:46

База чужая - данный запрос единственный способ "мониторить" состояние чужой базы, этот запрос ( с доплнительным фильтром по дате) будет дёргаться кажную минуту (из 1эс), и еслибы это была единственная задача, то 6 секунд не так уж много...
Но эска, ###, на время запроса к сторонней базе через одбц драйвер вешает себя на это время :( вот поэтому и становятся эти 6 секунд такими критичными (тотже запрос, но с первый, а не последним вхождением выполняется за секунду).

Религия не позволяющая добавить индексы - называется "у нас так исторически сложилось и менять ничего не будем", а база принадлежит другому подразделению в холдинге - они-то доступа и не дают...

Автор: Zloxa 29.6.2011, 20:14

Цитата(DonPager @ 29.6.2011, 17:46

)

база принадлежит другому подразделению в холдинге - они-то доступа и не дают...

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