|
Задавайте вопросы, мы ответим
Вы не зашли.
Здравствуйте уважаемые ГУРУ.
Возникла пробема, не знаю к чему ее отнести. Есть 2 таблицы связанные между собой по таблице связи. Данных в 2-х таблицах приблизительно по 300 000 записей. Произвожу выборку по запросу:
select OBJ_1.ID, OBJ_1.FLD_1, OBJ_2.ID, OBJ_2.FLD_2
from OBJ_1, OBJ_2, LINK
where ((OBJ_1.ID=LINK.ID_1 and OBJ_2.ID=LINK.ID_2)or(OBJ_1.ID=LINK.ID_2 and OBJ_2.ID=LINK.ID_1))
Этот запрос выполняется очень долго (ОЧЕНЬ ДОЛГО, порядка 30 минут!!!!!).
(Замечания к where: необходимо делать двойную проверку т.к. ниезвестно как могли быть связаны объекты через таблицу связи).
Индексы: в каждой таблице по ID, в таблице связей (LINK) пробовол все возможные типы индексов - не помогло. Сейчас индексов нет.
2 таблицы простые, думаю описывать их нет смылса (ID - primary key, FLDs - столбцы). Таблица связи имеет вид:
LNKID int(20) - ID таблицы
OBJ1_ID int(20) - ID таблицы 1
ID_1 int(20) - ID записи в таблицы 1
OBJ2_ID int(20) - ID таблицы 2
ID_2 - ID записи в таблицы 2
....
Explain запроса возвращает
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE OBJ_1 ALL IND_1 (null) (null) (null) 260124
1 SIMPLE LINK ALL (null) (null) (null) (null) 331422 Using where
1 SIMPLE OBJ_2 ALL IND_2 (null) (null) (null) 333487 Range checked for each record (index map: 0x1)
Пробовал перестраиваить запрос, результатов не добился. Если из "where" заменить ((OBJ_1.ID=LINK.ID_1 and OBJ_2.ID=LINK.ID_2)or(OBJ_1.ID=LINK.ID_2 and OBJ_2.ID=LINK.ID_1)) на (OBJ_1.ID=LINK.ID_1 and OBJ_2.ID=LINK.ID_2) то все работает идеально.
ПОМОГИТЕ!!!
Неактивен
Попробуйте избавится от OR. Сделать два запроса и UNION
Неактивен
Спасибо за быстрый ответ.
explain
select OBJ_1.ID,OBJ_1.FLD_1,OBJ_2.ID,OBJ_2.FLD_2
from OBJ_1, OBJ_2, LINK
where (OBJ_1.ID=link.ID_1 and OBJ_2.id=Link.ID_2)
union
select OBJ_1.ID,OBJ_1.FLD_1,OBJ_2.ID,OBJ_2.FLD_2
from OBJ_1, OBJ_2, LINK
where (OBJ_1.ID=link.ID_2 and OBJ_2.id=Link.ID_1)
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY LINK ALL (null) (null) (null) (null) 331422
1 PRIMARY OBJ_1 ref IND_1 IND_1 4 LINK.ID_1 1
1 PRIMARY OBJ_2 ref IND_2 IND_2 4 LINK.ID_2 1
2 UNION SD_LINK ALL (null) (null) (null) (null) 331422
2 UNION OBJ_1 ref IND_1 IND_1 4 LINK.ID_2 1
2 UNION OBJ_2 ref IND_2 IND_2 4 LINK.ID_1 1
Null UNION RESULT <union1,2> ALL (null) (null) (null) (null) Null
Отрабатывает чуть быстрее, но не летает (было такая идея)!
Бывают запросы сложее (пересечение 3 объектов и более). И вот там как раз эта двойная проверка все и губит, а без нее никуда!
Буду рад любой идеи или направления где капнуть!
ЗЫ. Из-за таких запрос даже научился чуть оптимизировать сервер! 
Неактивен
Для данного конкретного запроса выход напрашивается простой - симметризовать таблицу link. То есть сделать, чтобы наряду с записью
id1, id2 в ней всегда была
id2, id1
Неактивен
Спасибо.
Сам думал над этим решением. Но, к сожалению, структруру таблицы на данном этапе меня нельзя. Придется очень много переделовать в программе!
Буду биться дальше. Если чего-нибудь придумаю - напишу!
Неактивен
Попробуйте UNION ALL вместо UNION. В этом случае будет дублирование записей, но зато не будет операции поиска пересечения
Неактивен
Не забудьте настроить Query cache
Неактивен
А вот еще вопрос.
Проблема заключается в таблице LINK. Может стоит попробовать создать индексы и загрузить их в память (читал про что то похожее в Вашем форуме)? Теоретически должно ускорить работу. Я пробовал создавать индекс, но меня все время пугает EXPLAIN который определяел для таблицы LINK Type = All, key = null (т.е. как я понимаю он не хочет использовать индекс). Принудително заставить использоать индекс (USE INDEX) тоже не помог.
И не подскажите какие лучше всего индексы создать по структуре данной таблицы?
Заранее Вам благодарен.
Неактивен
Индексы у Вас и так есть и работают, судя по explain. Просто Вы выбираете все записи из Link по структуре самого запроса. В любом случае будет fulln table scan
Неактивен
Спасибо за совет "Кэширование запросов в MySQL".
Нашел выход:
DROP TABLE IF EXIST "cache_link"
CREATE TABLE cache_link ENGINE=MEMORY
select OBJ1_ID, ID_1, OBJ2_ID, ID_2 from link
where (OBJ1_ID = 1 or OBJ1_ID = 15 or OBJ2_ID = 1 or OBJ1_ID = 15);
+ Поставил ограничения для таблицы LINK.
Но никак не могу разобраться с DROP TABLE IF EXIST "cache_link"! Во время "CREATE ..." говорит, что таблица "cache_link" существует.
Я так понимаю таблица в памяти, а ссылка на нее лежит в INFORMATION_SCHEMA.TABLES. Может ее и отдуда надо удалять?
Решение близко! Осталось чуть-чуть!
Неактивен
И если я потом буду работать так:
CREATE TABLE cache_link ENGINE=MEMORY
select OBJ1_ID, ID_1, OBJ2_ID, ID_2 from link
where (OBJ1_ID = 1 or OBJ1_ID = 15 or OBJ2_ID = 1 or OBJ1_ID = 15);
select OBJ_1.ID, OBJ_1.FLD_1, OBJ_2.ID, OBJ_2.FLD_2
from OBJ_1, OBJ_2, cache_link TABLE_LINK
where ((OBJ_1.ID=TABLE_LINK.ID_1 and OBJ_2.ID=TABLE_LINK.ID_2)or(OBJ_1.ID=TABLE_LINK.ID_2 and OBJ_2.ID=TABLE_LINK.ID_1))
Большой получу прирост в скорости?
Т.е. самая большая таблица должна обрабатываться в памяти!
Неактивен
См. FAQ п.2 http://sqlinfo.ru/forum/viewtopic.php?id=679
Ошибки в скрипте надо обрабатывать. У Вас синтаксическая ошибка EXIST => EXISTS
Неактивен
COOLim написал:
Большой получу прирост в скорости?
Т.е. самая большая таблица должна обрабатываться в памяти!
Проверьте опытным путем.
Неактивен
Привет Всем.
Решил свой поствленный вопрос следующим образом:
1. Воспользовался советом rgbeast (симметризовать таблицу link), но немного по другому. Написал запрос:
select distinct case when obj_1.id=1 and obj_2.id=2 then id_1 when obj_1.id=2 and obj_2.id=1 then id_2 end as id1,
case when obj_1.id=2 and obj_2.id=1 then id_2 when obj_1.id=1 and obj_2.id=2 then id_1 end as id2
from link where (obj_1.id=2 and obj_2.id=1) or (obj_2.id=1 and obj_1.id=2))
2. Поменял запрос на select from select (запрос из п. 1).
Соответственно для 3 и более таблиц данный способ построения запроса возвращаяет таблицу вида:
ID1 ID2 ID3 ... IDN
1 2 3 ... N
... ... ... ... ...
3. И в самом конце в условии where поставил ограничение
OBJ_1.ID = ID1, OBJ_2.ID = ID2, ..., OBJ_N.ID = IDN
После этого запрос для 4 таблиц (суммарно около 1 000 000 записей) выполняется порядка 5-10 сек.
Подскажиет, пожалуйста, как можно еще ускорить скорость выполнения запроса.
PS Если кому будет интересно, могу выложить запрос для 4 таблиц.
PSS Да, вот еще, скажите уважаемые ГУРУ, можно ли полностью доверяться EXPLAIN? Изучил всю информация с http://www.mysql.ru/docs/man/EXPLAIN.html и немного не понимаю вот такого случая:
Type = All, rows = 350 000, table = link таких строк 5 и более и запрос вылетает за 5 сек. (то, что сейчас получилось)
а бывает
Type = All, rows = 350 000, table = link таких строк 2 и более и запрос может выполнятся до 20 мин. (то, что было изначально)
В документации написано: "Существует неплохой способ определить, насколько хорошим является тип связывания. Для этого нужно перемножить все значения столбца rows, выводимого командой EXPLAIN. Результатом будет грубая оценка того, сколько строк должен просмотреть MySQL для выполнения запроса."
Два запроса разные. Анализирую по EXPLAIN.
В чем беда?
Всем большое спасибо за участие!
Неактивен
EXPLAIN не всегда достоверно знает сколько строк будет в ответе. Он делает оценку на основании статистической информации о ключе.
Неактивен
