X   Сообщение сайта
(Сообщение закроется через 3 секунды)



 

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

| Вход | Регистрация )

2 страниц V   1 2 >
Открыть тему
Тема закрыта
> Помогите с оптимизацией запроса
Den1xxx
Den1xxx
Topic Starter сообщение 8.7.2013, 19:08; Ответить: Den1xxx
Сообщение #1


Здравствуйте, форумчане.
На сайте предполагается довольно часто сортировать товары в таблице `items`.
Запрос на сортировку имеет вид
SELECT `id` from `items` WHERE `sorting` LIKE '%Имя=ёжик%' AND `sorting` LIKE '%Скоростей=одна%' AND `catid`='135'
ORDER BY `mode` ASC, `name` ASC LIMIT 0, 30


Таблица `items` имеет следующую структуру
CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) NOT NULL auto_increment COMMENT='Номер записи',
`catid` int(11) NOT NULL COMMENT='Номер категории',
`name` varchar(255) NOT NULL COMMENT='Имя объекта',
`short` text COMMENT='Краткое описание объекта',
`full` text NOT NULL COMMENT='Полное описание объекта',
`mode` varchar(255) default NULL COMMENT='Производитель',
`price` varchar(255) default NULL COMMENT='Цена',
`avail` int(11) NOT NULL COMMENT='Доступность',
`search` text NOT NULL COMMENT='Расширенный поиск',
`sorting` text NOT NULL COMMENT='Сортировка в категории',
`date` datetime NOT NULL COMMENT='Время изменения',
PRIMARY KEY (`id`),
FULLTEXT KEY `search` (`name`,`short`,`full`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Объекты каталога' AUTO_INCREMENT=1 ;


Есть ещё обычный поиск, для него сделан индекс через FULLTEXT—
SELECT * FROM `items` WHERE MATCH(`name`,`short`,`full`) AGAINST('".$searchquery."' IN BOOLEAN MODE);


Но обычным поиском предполагается пользоваться гораздо реже.

Вопрос:
Что можно отимизировать в запросах (особенно в первом) и что лучше изменить в структуре таблицы?
0
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Den1xxx
Den1xxx
Topic Starter сообщение 10.7.2013, 20:30; Ответить: Den1xxx
Сообщение #2


Апну что ли.
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Arks
Arks
сообщение 21.7.2013, 22:32; Ответить: Arks
Сообщение #3


CREATE TABLE IF NOT EXISTS `items` (
`id` int(11) NOT NULL auto_increment COMMENT='Номер записи',
`catid` int(11) NOT NULL COMMENT='Номер категории', /*2147483647 категорий? многовато...*/
`name` varchar(255) NOT NULL COMMENT='Имя объекта',
`short` text COMMENT='Краткое описание объекта',
`full` text NOT NULL COMMENT='Полное описание объекта',
`mode` varchar(255) default NULL COMMENT='Производитель',
`price` varchar(255) default NULL COMMENT='Цена', /*цена точно VARCHAR? мож DECIMAL(9,2)?*/
`avail` int(11) NOT NULL COMMENT='Доступность', /*2147483647 для доступности? разьве что совсем аццкая битовая маска... можно оценить доступность товара для каждого человека планеты по критериям в отдельности*/
`search` text NOT NULL COMMENT='Расширенный поиск',
`sorting` text NOT NULL COMMENT='Сортировка в категории', /*даже боюсь представить что тут может храниться в поле типа text*/

`date` datetime NOT NULL COMMENT='Время изменения',
PRIMARY KEY (`id`),
FULLTEXT KEY `search` (`name`,`short`,`full`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Объекты каталога' AUTO_INCREMENT=1 ;


С такой выборкой бесполезно пытаться что-либо оптимизировать, это пример запроса из-за который обычно банят данного mysql-юзера чтобы впредь не ронял сервер. Мало того, еще и MyISAM используется. Сложность запроса тут вообще не посчитать, 90% row-format'а в выборке DYNAMIC.
Напрашивается в первую очередь BTREE индекс - который максимально быстро отсеит записи - это индекс по catid, но на месте планировщика я бы вообще его не стал использовать, что мы и видим. В остальном тут бесполезно что-либо оптимизировать, надо менять архитектуру поиска.
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Den1xxx
Den1xxx
Topic Starter сообщение 22.7.2013, 7:46; Ответить: Den1xxx
Сообщение #4


(Arks @ 22.7.2013, 01:32) *
Мало того, еще и MyISAM используется.

А вот здесь поподробнее пожалуйста. Чем другой может быть лучше?


(Arks @ 22.7.2013, 01:32) *
это пример запроса из-за который обычно банят данного mysql-юзера чтобы впредь не ронял сервер.

Вообще-то никто ничего не роняет, а если каталог слишком большой то просто включается кеш.
Все как у всех вообще-то. Я просто не силен в БД запросах и проектировании БД вообще, но вижу что именно здесь обязательно будет нагрузка при больших выборках.
То есть проблемы пока не возникает, но чувствует задница что скоро...
К тому же чужое мнение почти всегда полезно, особенно ругательное;)


(Arks @ 22.7.2013, 01:32) *
цена точно VARCHAR

Сюда некоторые умудряются запихнуть в цену тип валюты, например: 1200 руб., и отключать сортировку по цене.
DECIMAL(9,2) не прокатит, т.к. у нас например нет копеек, вообще.
Оставлено для совместимости.


(Arks @ 22.7.2013, 01:32) *
даже боюсь представить что тут может храниться в поле типа text

Текстовые данные для сортировки, примерно такой структуры:
Возраст=20; Город=Лондон; Характер=наглый; Нос=синий; Сила=3;
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Arks
Arks
сообщение 22.7.2013, 12:38; Ответить: Arks
Сообщение #5


А ну тогда все понятно...
Вот я тут пописал скриптики ради развлечения:
/* NEW STRUCTURE */

CREATE TABLE sorting_types (
id mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL COMMENT 'Возраст, Город, Характер, Нос, и т.д.',
PRIMARY KEY (id),
UNIQUE INDEX UK_name USING BTREE (`name`)
) ENGINE=INNODB COMMENT='критерии сортировки';

CREATE TABLE sorting_values (
id mediumint(8) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL COMMENT '20, Лондон, наглый, синий, 3, и т.д.',
PRIMARY KEY (id),
UNIQUE INDEX UK_name USING BTREE (`name`)
) ENGINE=INNODB COMMENT='значения сортировки';

CREATE TABLE sorting_types_to_sorting_values (
id bigint(20) NOT NULL AUTO_INCREMENT,
sorting_types_id mediumint(8) NOT NULL COMMENT 'id критерия сортировки',
sorting_values_id mediumint(8) NOT NULL COMMENT 'id значения сортировки',
PRIMARY KEY (id),
UNIQUE INDEX UK_type_id_values_id USING BTREE (sorting_types_id, sorting_values_id),
CONSTRAINT FOREIGN KEY FK_sorting_types_id (sorting_types_id) REFERENCES sorting_types (id) ON DELETE CASCADE,
CONSTRAINT FOREIGN KEY FK_sorting_values_id (sorting_values_id) REFERENCES sorting_values (id) ON DELETE CASCADE
) ENGINE=INNODB COMMENT='связь между критериями и значениями сортировки';

CREATE TABLE items_has_sorting (
item_id int(11) NOT NULL COMMENT 'id записи в items',
sorting_id bigint(20) NOT NULL COMMENT 'id связи в sorting_types_to_sorting_values',
PRIMARY KEY (item_id, sorting_id),
CONSTRAINT FOREIGN KEY FK_sorting_id (sorting_id) REFERENCES sorting_types_to_sorting_values (id) ON DELETE CASCADE
) ENGINE=INNODB COMMENT='связь между items и парами сортировки';

/* END NEW STRUCTURE */


Такая структура позволит унифицировать любые сортировки, сжать данные, использовать мощь ограничений внешнего ключа, использовать индекс по catid в запросе, убрать мутное текстовое поле sorting из таблицы items.
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Arks
Arks
сообщение 22.7.2013, 12:39; Ответить: Arks
Сообщение #6


В качестве бонуса я написал скриптик конвертации:

/* FILL NEW STRUCTURE SCRIPTS */

DELIMITER $$

/* EXAMPLE:
SET @pairID = 0;
CALL insert_pair('Город=Лондон', '=', @pairID);
SELECT @pairID;
*/
CREATE DEFINER=CURRENT_USER PROCEDURE `insert_pair`(IN pair VARCHAR(255), IN delim CHAR(1), OUT pairid BIGINT)
MODIFIES SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'вставляет пару значений pair разделенную символом delim'
BEGIN DECLARE sorting_types_name VARCHAR(30);
DECLARE sorting_values_name VARCHAR(30);
DECLARE existing_sorting_types_id mediumint(8);
DECLARE existing_sorting_values_id mediumint(8);

SELECT TRIM(SUBSTRING_INDEX(pair, delim, 1)), TRIM(SUBSTRING_INDEX(pair, delim, -1))
INTO sorting_types_name, sorting_values_name;
SELECT id INTO existing_sorting_types_id FROM sorting_types
WHERE `name` = sorting_types_name;
SELECT id INTO existing_sorting_values_id FROM sorting_values
WHERE `name` = sorting_values_name;

IF existing_sorting_types_id IS NOT NULL AND existing_sorting_values_id IS NOT NULL THEN
BEGIN IF existing_sorting_types_id IS NULL THEN
BEGIN INSERT INTO sorting_types (`name`) VALUE (sorting_types_name);
SELECT LAST_INSERT_ID() INTO existing_sorting_types_id; END;
END IF;
IF existing_sorting_types_id IS NULL THEN
BEGIN INSERT INTO sorting_types (`name`) VALUE (sorting_types_name);
SELECT LAST_INSERT_ID() INTO existing_sorting_types_id; END;
END IF;
INSERT IGNORE INTO sorting_types_to_sorting_values (sorting_types_id, sorting_values_id) VALUES (existing_sorting_types_id, existing_sorting_values_id);

END;
END IF;

SELECT existing_sorting_types_id, existing_sorting_values_id, sorting_types_name, sorting_values_name;
END$$

/* EXAMPLE:
CALL insert_pairs('Возраст=20; Город=Лондон; Характер=наглый; Нос=синий; Сила=3;', 4, ';', '=');
*/
CREATE DEFINER=CURRENT_USER PROCEDURE `insert_pairs`(IN sorting TEXT, IN itemid INT(11), IN delim char(1), IN subdelim char(1))
MODIFIES SQL DATA
DETERMINISTIC
SQL SECURITY INVOKER
COMMENT 'вставляет пары значений sorting для одной записи из items'
BEGIN DECLARE pair varchar(255);
DECLARE newpairid bigint(20);
SET sorting = TRIM(sorting);
REPEAT
SET pair = SUBSTRING_INDEX(sorting, delim, 1);
SET sorting = TRIM(SUBSTRING(sorting, CHAR_LENGTH(pair)+2));
SET pair = TRIM(pair);
CALL insert_pair(pair, subdelim, newpairid);
IF newpairid != 0 THEN
BEGIN INSERT INTO items_has_sorting VALUES (itemid, newpairid);
END;
END IF;
UNTIL sorting = '' END REPEAT;
END$$

/* EXAMPLE:
CALL convert_sorting();
*/
CREATE DEFINER = CURRENT_USER PROCEDURE convert_sorting()
COMMENT 'конвертирует имеющиеся записи в items'
DETERMINISTIC MODIFIES SQL DATA
SQL SECURITY INVOKER
BEGIN DECLARE done INT DEFAULT FALSE;
DECLARE srt TEXT;
DECLARE itemid INT(11);
DECLARE cur CURSOR FOR SELECT id, sorting FROM items; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN cur;
read_loop: LOOP
FETCH cur INTO itemid, srt;
IF done THEN
LEAVE read_loop;
END IF;
CALL insert_pairs(srt, itemid, ';', '=');
END LOOP;
CLOSE cur;
END $$

DELIMITER ;

/* END FILL NEW STRUCTURE SCRIPTS */

/* FILL DATA */
CALL convert_sorting();
DROP PROCEDURE convert_sorting;
DROP PROCEDURE insert_pairs;
DROP PROCEDURE insert_pair;
/* END FILL DATA */
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Arks
Arks
сообщение 22.7.2013, 12:40; Ответить: Arks
Сообщение #7


Ну и конечно все что останется сделать, это исправить SQL код использующий таблицу items а затем сделать

/* RESTRUCT */
ALTER TABLE items DROP COLUMN sorting;
/* END RESTRUCT */
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Den1xxx
Den1xxx
Topic Starter сообщение 22.7.2013, 14:30; Ответить: Den1xxx
Сообщение #8


Спасибо.
Мде, сам напросился — теперь неделю уйдет на втыкание что происходит:)
Привык к MyIsam, InnoDB и FOREIGN KEY для меня пока темный лес.
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Arks
Arks
сообщение 22.7.2013, 20:34; Ответить: Arks
Сообщение #9


Ой, в хранимке insert_pair я не то копипастнул, а норм версию уже удалил. Прошу прощения, скрипт не сработает, эту хранимку надо исправить в сообщении выше фигня написана. Там в результате в переменную pairid должен выбираться либо имеющийся id записи пары, либо id свежевставленной. Если хочешь подправлю.


А по FOREIGN KEY там все просто. Смысл такой:
есть в таблице поле, которое ссылается на другую таблицу(в терминологии DB "paradox" - вторичный ключ). Для mysql'я это просто некое поле имеющее такое же значение как и поле в другой таблице. По этому полю производится JOIN.
FOREIGN KEY в mysql предполагает некие действия, которые должны производиться в подчиненной таблице(в которой находится вторичный ключ, пр. CONSTRAINT FOREIGN KEY <имя ограничения FK_sorting_types_id> (<вторичный ключ подчиненной таблицы sorting_types_id>) - при изменении данного значения в родительской таблице(пр. REFERENCES <родительская таблица sorting_types> (<поле в род.таблице id>)). Такие ограничения могут накладываться на операции DELETE и UPDATE.
ON DELETE <действие> или ON UPDATE <действие>
Занчениями действий являются:

CASCADE - операция будет продублирована в подчиненной таблице

Пример - у меня в структуре при удалении записи в sorting_types будут удалены и все связи(записи) в sorting_types_to_sorting_values для данного sorting_types_id, а это как видно из ограничения накладываемого полем sorting_types_to_sorting_values.id повлечет за собой и удаление всех записей в items_has_sorting для данного items_has_sorting.sorting_id.
А как бы было с MyISAM:
DELETE FROM sorting_types WHERE id = <someid>; INSERT INTO <temporary table.id> SELECT id FROM sorting_types_to_sorting_values WHERE sorting_types_id = <someid>; DELETE FROM sorting_types_to_sorting_values WHERE id = <someid>; DELETE FROM items_has_sorting WHERE sorting_id IN (SELECT id FROM <temporary_table>); Аж 4 запроса, причем 1 с подзапросом. Тут же сервер все делает сам за нас, быстро и здорово!
Не правда ли, удобная штука? :)




RESTRICT - операция будет отклонена в родительской таблице пока есть ограничение внешнего ключа(запись) в подчиненной таблице

Пример - родительская таблица скажем "Отделы", подчиненная "Сотрудники". Такое ограничение не позволит удалить отдел, пока в нем есть хотя бы один сотрудник.



NO ACTION - дефолтное поведение, операция будет проигнорирована для подчиненной таблицы
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
Den1xxx
Den1xxx
Topic Starter сообщение 22.7.2013, 21:56; Ответить: Den1xxx
Сообщение #10


(Arks @ 22.7.2013, 23:34) *
Ой, в хранимке insert_pair я не то копипастнул, а норм версию уже удалил.

Спасибо ещё раз за код. Ваш подход мне нравится, я узнал много нового пытаясь в нем разобраться и читая мануалы.
Но использовать пока не могу.
В таблице `items` два поля отвечают за сортировки:
`search` text NOT NULL COMMENT='Расширенный поиск',
`sorting` text NOT NULL COMMENT='Сортировка в категории',

Один сортирует глобально в поиске, другой внутри категории товара.
Функции сортировки используются довольно простые благодаря конструкции LIKE "%value%"
Раскидывание данных по восьми таблицам вместо 2 строк хоть и идеологически правильно, но сильно удорожает разработку и главное поддержку проекта.
Ещё раз спасибо за код, буду изучать как урок — как правильно проектировать базу.
Возможно и использую , но пока не готов, ещё и потому что пока не могу понять его до конца:)
Вернуться в начало страницы
 
Ответить с цитированием данного сообщения
2 страниц V   1 2 >
Открыть тему
Тема закрыта
1 чел. читают эту тему (гостей: 1, скрытых пользователей: 0)
Пользователей: 0


Свернуть

> Похожие темы

  Тема Ответов Автор Просмотров Последний ответ
Открытая тема (нет новых ответов) Помогите вспомнить рекламного бота Телеграм для обмена рекламой
0 Mixatraider 692 21.2.2024, 23:42
автор: Mixatraider
Открытая тема (нет новых ответов) Копеечная SEO оптимизация и рекламные кампании в Google Ads и Яндекс.Директ., Помощь с SEO оптимизацией и рекламными кампаниями в Google Ads и Яндекс.Директ.
Помощь с SEO оптимизацией и рекламными кампаниями в Google Ads и Яндек
4 santeyyy81 2972 24.9.2022, 22:58
автор: alex2211
Открытая тема (нет новых ответов) Помогите рещить проблему с монитором.
Почему низкое разрешение при подключении монитора через displayport?
0 Levels 1695 11.12.2020, 0:48
автор: Levels
Открытая тема (нет новых ответов) Помогите кто нибудь получить партнерку Росбанк!
7 heisenberg_mw 2315 7.8.2020, 9:28
автор: heisenberg_mw
Открытая тема (нет новых ответов) Помогите найти тему пожалуйста
7 virtas 12406 24.7.2020, 17:02
автор: -JamesSmips-


 



RSS Текстовая версия Сейчас: 29.3.2024, 10:48
Дизайн