PHP.SU

Программирование на PHP, MySQL и другие веб-технологии

PHP.SU Портал

Главная

Помощь

Поиск

Поиск Яндекс Вакансии

Пользователи

Здравствуйте, Гость

( Вход · Регистрация · Правила форума )

Забыли пароль?

оптимизация запросов и структуры БД

Форумы портала PHP.SU » PHP » SQL и Архитектура БД (Модератор: SAD)

Страниц (1): [1]

Без описания

Поиск в теме | Версия для печати

Panoptik	Отправлено: 12 Июня, 2012 - 12:37:57
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	Всем доброго дня столкнулся с проблемами оптимизации запросов запросы осуществляют выборку товаров по фильтрам в них может быть много джоинов и запросы эти повторяются много раз. в итоге как не сложно догадаться получается очень длинная обработка страницы с загрузкой процессора на 100%. надо как-то решить эту проблему, но опыта увы маловато итак запрос (это еще легкая версия того что может получиться в итоге) Спойлер (Отобразить) CODE (SQL): скопировать код в буфер обмена SELECT `G`.`ID` FROM `YII_SHOP_GOODS` `G` LEFT JOIN YII_SHOP_GOODS_PARAMS AS GP1 ON GP1.GOODS_ID = G.ID LEFT JOIN YII_SHOP_GOODS_PARAMS AS GP2 ON GP2.GOODS_ID = G.ID WHERE (G.PRICE BETWEEN 2000 AND 5000) AND (GP1.PARAM_ID = '6' AND GP1.VISIBLE =1) AND (`GP1`.`VAL` IN ('с рисунком', 'однотонный')) AND (GP2.PARAM_ID = '4' AND GP2.VISIBLE =1) AND (`GP2`.`VAL` IN ('оранжевый', 'розовый', 'серый', 'лиловый')) AND (G.CATEGORY_ID = '1') структура таблиц (некоторые лишние поля второй таблицы я повыкидывал, оставил касающиеся сути вопроса) Спойлер (Отобразить) CODE (SQL): скопировать код в буфер обмена CREATE TABLE IF NOT EXISTS `YII_SHOP_GOODS_PARAMS` ( `ID` int(1) UNSIGNED NOT NULL AUTO_INCREMENT, `GOODS_ID` int(1) NOT NULL, `PARAM_ID` int(1) NOT NULL, `VAL` varchar(200) NOT NULL, `PRICE` int(11) DEFAULT NULL, `CODE` varchar(50) NOT NULL, `VISIBLE` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `VISIBLE` (`VISIBLE`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11094 ; CREATE TABLE IF NOT EXISTS `YII_SHOP_GOODS` ( `ID` int(1) UNSIGNED NOT NULL AUTO_INCREMENT, `PATH` varchar(100) NOT NULL, `CODE` varchar(50) NOT NULL, `PRICE` decimal(8,2) UNSIGNED NOT NULL DEFAULT '0.00', `CATEGORY_ID` int(1) UNSIGNED NOT NULL, `VISIBLE` tinyint(1) UNSIGNED NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `PATH` (`PATH`,`CODE`,`PRICE`,`CATEGORY_ID`,`VISIBLE`), ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=625 ; далее EXPLAIN того что получилось Спойлер (Отобразить) CODE (text): скопировать код в буфер обмена id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE GP1 ALL VISIBLE NULL NULL NULL 10796 Using where 1 SIMPLE GP2 ALL VISIBLE NULL NULL NULL 10796 Using where; Using join buffer 1 SIMPLE G eq_ref PRIMARY PRIMARY 4 dreamy.GP2.GOODS_ID 1 Using where вобщем как и куда смотреть в сторону оптимизации не знаю. может посоветуете что или поделитесь опытом ЗЫ. кеширования никакого нету, хотя может есть вариант его использовать. тоже не отказался бы на счет совета ----- Just do it

armancho7777777	Отправлено: 12 Июня, 2012 - 12:41:30
Активный участник Покинул форум Сообщений всего: 4526 Дата рег-ции: Февр. 2011 Откуда: Москва Помог: 221 раз(а)	Кешируйте результат. Записывайте результат в файл в виде сериализованного масива и используйте данные из файла. При изменении данных в БД перезаписывайте файл.

Panoptik	Отправлено: 12 Июня, 2012 - 12:43:32
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	вариант принимается, только вот запросы то всё время в разными данными. как это учесть? ----- Just do it

DeepVarvar	Отправлено: 12 Июня, 2012 - 12:50:23
Активный участник Покинул форум Сообщений всего: 10377 Дата рег-ции: Дек. 2008 Откуда: Альфа Центавра Помог: 353 раз(а)	Так и записывать в файл - а перед запросом проверять есть ли такой файл в папке кеша. Ну нету - значит делаем запрос и генерим файл. Теперь он там уже будет. ----- Шта? Репозиторий?

Panoptik	Отправлено: 12 Июня, 2012 - 12:55:49
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	и сколько количественно таких файлов допустимо хранить? по времени я так понимаю можно безсрочно, только во время изменения данных удалять файлы кеша, чтобы они потом перезаписались актуальными данными и еще у меня есть мысль уйти от джоинов а просто делать много простых выборок... но вот не знаю будет ли данный вариант работать быстрее? ----- Just do it

caballero	Отправлено: 12 Июня, 2012 - 13:00:29
Активный участник Покинул форум Сообщений всего: 5998 Дата рег-ции: Сент. 2011 Откуда: Харьков Помог: 126 раз(а)	mysql быстрее обработает кучу простых запросов чем oдин с кучей джойнов. Но лучше пересмотреть индексы - может с джойнами можно оптимизировать (Отредактировано автором: 12 Июня, 2012 - 13:05:28) ----- Бесплатная система складского учета с открытым кодом https://zippy[dot]com[dot]ua/zstore

Panoptik	Отправлено: 12 Июня, 2012 - 13:13:28
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	caballero,DeepVarvar,armancho7777777 всем спасибо за советы. будем посмотреть что из этого получится но от свежих идей не отказываюсь ----- Just do it

Мелкий	Отправлено: 12 Июня, 2012 - 13:50:02
Активный участник Покинул форум Сообщений всего: 11926 Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург Помог: 618 раз(а)	caballero пишет: mysql быстрее обработает кучу простых запросов чем oдин с кучей джойнов. Зависит от объёма данных. На стороне php джойнить ресурсов больше уйдёт, а если для этого придётся вычитывать много лишних данных - то и на стороне mysql может быть неприятно. Но да, это как один из вариантов, если он действительно подходит. Рукакнига использует, например. Panoptik пишет: далее EXPLAIN того что получилось Ну вот видите - индексы не используются. Fullscan'ом выбирается 10796 записей из одной таблицы, 10796 из другой, и 1 по PK из третьей (единственное, что хорошего в этой explain'е). А потом это всё джойнится и получается 100млн записей просмотрено. Само собой, ничего хорошего из этого не получится. type = ALL - кроме (самой первой таблицы, там часто от него не уйти) всегда тревожный звонок. YII_SHOP_GOODS_PARAMS.GOODS_ID - почему джойните по неиндексированному полю? Это всегда ужасная идея. Для представленного запроса скорей всего весьма кстати придётся индекс по GOODS_ID & VISIBLE & PARAM_ID & VAL, CATEGORY_ID & PRICE ----- PostgreSQL DBA

Panoptik	Отправлено: 12 Июня, 2012 - 13:56:31
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	Мелкий Спасибо огромное за подробное объяснение что к чему. Индексы проставил вот текущий эксплеин Спойлер (Отобразить) CODE (text): скопировать код в буфер обмена id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE GP2 range VISIBLE,VAL,PARAM_ID,PARAM_ID_2,GOODS_ID PARAM_ID_2 606 NULL 415 Using where 1 SIMPLE G eq_ref PRIMARY PRIMARY 4 dreamy.GP2.GOODS_ID 1 Using where 1 SIMPLE GP1 ref VISIBLE,VAL,PARAM_ID,PARAM_ID_2,GOODS_ID GOODS_ID 4 dreamy.G.ID 17 Using where вот индексы которые я понаставлял. может какие-то из них и лишние Спойлер (Отобразить) CODE (text): скопировать код в буфер обмена Имя индекса Тип Уникальный Упакован Поле Уникальных элементов Сравнение Null Комментарий PRIMARY BTREE Да Нет ID 10796 A VISIBLE BTREE Нет Нет VISIBLE 2 A VAL BTREE Нет Нет VAL 35 A PARAM_ID BTREE Нет Нет PARAM_ID 7 A PARAM_ID_2 BTREE Нет Нет PARAM_ID 7 A VAL 35 A и кстати вопрос такой по индексам. есть разница отдельные индексы для каждого поля или объединены несколько полей в один индекс? (Отредактировано автором: 12 Июня, 2012 - 14:01:43) ----- Just do it

caballero	Отправлено: 12 Июня, 2012 - 13:56:41
Активный участник Покинул форум Сообщений всего: 5998 Дата рег-ции: Сент. 2011 Откуда: Харьков Помог: 126 раз(а)	Мелкий чего и следовало ожидать обычно две причины тормозов мускула на сайтах не являющимися порталами с тысчами пользовыателей - отсутствие индексов и запросы в циклах. тут и думать нечего раставить индесы по полям использующимсяя для join и все дела (Добавление) Panoptik оставь те которые используются в join и те по полям которых часто ведется поиск остальные убери - неграмотно проставлениые лишние индексы могут тормозить больше чем их остутствие. ----- Бесплатная система складского учета с открытым кодом https://zippy[dot]com[dot]ua/zstore

Panoptik	Отправлено: 12 Июня, 2012 - 14:04:10
Постоянный участник Покинул форум Сообщений всего: 2493 Дата рег-ции: Нояб. 2011 Откуда: Одесса, Украина Помог: 131 раз(а)	caballero очень печально что вы так иронизируете. индексы кстати изначально там были. просто структура таблицы менялась не мною. и я вот щас наткнулся на то что индексы там отсутствуют. к тому же никто из вас кроме Мелкого не дал изначально совет по результатам ЭКСПЛЕИН запроса ----- Just do it

Мелкий	Отправлено: 12 Июня, 2012 - 15:40:41
Активный участник Покинул форум Сообщений всего: 11926 Дата рег-ции: Июль 2009 Откуда: Россия, Санкт-Петербург Помог: 618 раз(а)	Panoptik пишет: есть разница отдельные индексы для каждого поля или объединены несколько полей в один индекс? Есть, и огромная. Представьте библиотеку. Вам нужно найти книгу определённого автора, вам известна тематика этой книги, но не помните точное название. В библиотеке книги отсортированы, допустим для наглядности, по названию, которое вы и не помните вы можете: обойти всю библиотеку - нет подходящего индекса посмотреть указатель тематик и побегать по библиотеке уже за конкретными книгами и для каждой смотреть, тот это автор или нет - индекс по тематике посмотреть указатель авторов - и аналогично побегать по библиотеке - индекс по автору -- при этом, можно посмотреть оба индекса сразу и прикинуть, в каком меньше книг придётся обойти - чем планировщик СУБД и занимается, прикидывает, какой индекс позволит меньше бегать. И самый удобный вариант - если есть справочник по тематикам, а в каждой тематике книги сгруппированы по авторам. А ведь последним справочником мы сможем пользоваться и когда понадобится книга определённой тематики, но неизвестного автора. А вот найти по этому справочнику книгу неизвестной тематики, но определённого автора - занятие довольно бесполезное. Поэтому существенное значение играет и порядок полей в составном индексе. Можно решить, что лучше сделать большинство возможных индексов и горя не знать - но нет. Индексы занимают место, а при любом изменении данных в поле пересчитываются все индексы, в которое это поле входит. Так же проверять нужно на реальных данных. Что хорошо работало на 100 записях может себя весьма неадекватно вести на тысяче записей. Просто потому, что даже индекс по вроде бы нужному полю оказывается бесполезным - по нему выбирается слишком много данных и СУБД проще прочитать всё и отбросить лишнее, чем бегать по индексу. ----- PostgreSQL DBA

Поиск в теме | Версия для печати

Страниц (1): [1]

Сейчас эту тему просматривают: 0 (гостей: 0, зарегистрированных: 0)

« SQL и Архитектура БД »

Все гости форума могут просматривать этот раздел.
Только зарегистрированные пользователи могут создавать новые темы в этом разделе.
Только зарегистрированные пользователи могут отвечать на сообщения в этом разделе.