Нужна оптимизация MySQL для комплексного поиска по структурированным данным EAV

У меня есть большая база данных с структурированными данными EAV, которые должны быть доступны для поиска и доступны для просмотра. Я пробовал каждый трюк в своей книге, чтобы получить его достаточно быстро, но при определенных обстоятельствах он все равно не завершается в разумные сроки.

Это моя структура таблицы (только соответствующие части, просите, если вам нужно больше):

CREATE TABLE IF NOT EXISTS `object` ( `object_id` bigint(20) NOT NULL AUTO_INCREMENT, `oid` varchar(32) CHARACTER SET utf8 NOT NULL, `status` varchar(100) CHARACTER SET utf8 DEFAULT NULL, `created` datetime NOT NULL, `updated` datetime NOT NULL, PRIMARY KEY (`object_id`), UNIQUE KEY `oid` (`oid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `version` ( `version_id` bigint(20) NOT NULL AUTO_INCREMENT, `type_id` bigint(20) NOT NULL, `object_id` bigint(20) NOT NULL, `created` datetime NOT NULL, `status` varchar(100) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY (`version_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE IF NOT EXISTS `value` ( `value_id` bigint(20) NOT NULL AUTO_INCREMENT, `object_id` int(11) NOT NULL, `attribute_id` int(11) NOT NULL, `version_id` bigint(20) NOT NULL, `type_id` bigint(20) NOT NULL, `value` text NOT NULL, PRIMARY KEY (`value_id`), KEY `field_id` (`attribute_id`), KEY `action_id` (`version_id`), KEY `form_id` (`type_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 

Это образец объекта. У меня около 1 миллиона из них в моей базе данных. каждый объект может иметь различное количество атрибутов с разными атрибутами

 INSERT INTO `owner` (`owner_id`, `uid`, `status`, `created`, `updated`) VALUES (1, 'cwnzrdxs4dzxns47xs4tx', 'Green', NOW(), NOW()); INSERT INTO `object` (`object_id`, `type_id`, `owner_id`, `created`, `status`) VALUES (1, 1, 1, NOW(), NOW()); INSERT INTO `value` (`value_id`, `owner_id`, `attribute_id`, `object_id`, `type_id`, `value`) VALUES (1, 1, 1, 1, 1, 'Munich'); INSERT INTO `value` (`value_id`, `owner_id`, `attribute_id`, `object_id`, `type_id`, `value`) VALUES (2, 1, 2, 1, 1, 'Germany'); INSERT INTO `value` (`value_id`, `owner_id`, `attribute_id`, `object_id`, `type_id`, `value`) VALUES (3, 1, 3, 1, 1, '123'); INSERT INTO `value` (`value_id`, `owner_id`, `attribute_id`, `object_id`, `type_id`, `value`) VALUES (4, 1, 4, 1, 1, '2012-01-13'); INSERT INTO `value` (`value_id`, `owner_id`, `attribute_id`, `object_id`, `type_id`, `value`) VALUES (5, 1, 5, 1, 1, 'A cake!'); 

Теперь о моем нынешнем механизме. Моя первая попытка была типичным подходом к Mysql. Сделайте один огромный SQL с множеством соединений на все, что мне нужно. Полное уничтожение! Потребовал путь к загрузке и даже разбил серверы PHP и MySQL из-за исчерпанной ОЗУ.

• Документация Magento ORM
• Mysql dyanmic значение как псевдоним
• Создание пользовательских ролей
• Должен ли я использовать модель EAV?
• Как хранить пары данных в базе данных mysql?

Поэтому я разбил свои запросы на несколько шагов:

1 Определите все необходимые атрибуты.

Я могу найти их в другой таблице, которая ссылается на type_id объекта. Результатом является список атрибутов. (эта таблица не очень важна для производительности, поэтому она не включена в мой образец.)

: type_id содержит все type_ids из любых объектов, которые я хочу включить в мой поиск. Я уже получил эту информацию в своем приложении. Так что это недорого.

 SELECT * FROM attribute WHERE form_id IN (:type_id) 

Результат представляет собой массив целых чисел type_id.

2 Поиск совпадающих объектов Скомпилирован большой SQL-запрос, который добавляет один INNER JOIN для каждого условия, которое я хочу. Это звучит ужасно, но, в конце концов, это был самый быстрый способ 🙁

Типичный сгенерированный запрос может выглядеть так. LIMIT, к сожалению, необходимо, или я буду потенциально получать столько идентификаторов, что результирующий массив заставит PHP взорваться или сломать оператор IN в следующем запросе:

 SELECT DISTINCT `version`.object_id FROM `version` INNER JOIN `version` AS condition1 ON `version`.version_id = condition1.version_id AND condition1.created = '2012-03-04' -- Filter by version date INNER JOIN `value` AS condition2 ON `version`.version_id = condition2.version_id AND condition2.type_id IN (:type_id) -- try to limit joins to object types we need AND condition2.attribute_id = :field_id2 -- searching for a value in a specific attribute AND condition2.value = 'Munich' -- searching for the value 'Munich' INNER JOIN `value` AS condition3 ON `version`.version_id = condition3.version_id AND condition3.type_id IN (:type_id) -- try to limit joins to object types we need AND condition3.attribute_id = :field_id3 -- searching for a value in a specific attribute AND condition3.value = 'Green' -- searching for the value 'Green' WHERE `version`.type_id IN (:type_id) ORDER BY `version`.version_id DESC LIMIT 10000 

Результат будет содержать все object_ids из любого объекта, который мне может понадобиться. Я выбираю object_ids, а не version_ids, поскольку мне нужно иметь все версии совпадающих объектов, независимо от того, какая версия соответствует.

3 Сортировка и результаты страницы Далее я создам запрос, который сортирует объекты по определенному атрибуту и ​​затем выводит результирующий массив.

 SELECT DISTINCT object_id FROM value WHERE object_id IN (:foundObjects) AND attribute_id = :attribute_id_to_sort AND value > '' ORDER BY value ASC LIMIT :limit OFFSET :offset 

Результатом является отсортированный и выгружаемый список идентификаторов объектов из прежнего поиска

4 Получите наши полные объекты, версии и атрибуты. На последнем шаге я выберу все значения для любых объектов и версий, которые были найдены в предыдущих запросах.

 SELECT `value`.*, `object`.*, `version`.*, `type`.* `object`.status AS `object.status`, `object`.flag AS `object.flag`, `version`.created AS `version.created`, `version`.status AS `version.status`, FROM version INNER JOIN `type` ON `version`.form_id = `type`.type_id INNER JOIN `object` ON `version`.object_id = `object`.object_id LEFT JOIN value ON `version`.version_id = `value`.version_id WHERE version.object_id IN (:sortedObjectIds) AND `version.type_id IN (:typeIds) ORDER BY version.created DESC 

Результат будет скомпилирован через PHP в хорошие структуры массива object-> version-> value.

Теперь вопрос :

• Можно ли каким-то образом ускорить весь этот беспорядок?
• Можно ли каким-либо образом удалить ограничение LIMIT 10000 из моего поискового запроса?

Если все остальное не удается, возможно, переключитесь на технологию баз данных? См. Мой другой вопрос: база данных оптимизирована для поиска в большом количестве объектов с разными атрибутами

Образцы реальной жизни

Размеры таблицы: объект – 193801 строк, версия – 193841 строк, значение – 1053928 строк

 SELECT * FROM attribute WHERE attribute_id IN (30) SELECT DISTINCT `version`.object_id FROM version INNER JOIN value AS condition_d4e328e33813 ON version.version_id = condition_d4e328e33813.version_id AND condition_d4e328e33813.type_id IN (30) AND condition_d4e328e33813.attribute_id IN (377) AND condition_d4e328e33813.value LIKE '%e%' INNER JOIN value AS condition_2c870b0a429f ON version.version_id = condition_2c870b0a429f.version_id AND condition_2c870b0a429f.type_id IN (30) AND condition_2c870b0a429f.attribute_id IN (376) AND condition_2c870b0a429f.value LIKE '%s%' WHERE version.type_id IN (30) ORDER BY version.version_id DESC LIMIT 10000 -- limit to 10000 or it breaks! 

Объясните:

 id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE condition_2c870b0a429f ref field_id,action_id,form_id field_id 4 const 178639 Using where; Using temporary; Using filesort 1 SIMPLE action eq_ref PRIMARY PRIMARY 8 condition_2c870b0a429f.action_id 1 Using where 1 SIMPLE condition_d4e328e33813 ref field_id,action_id,form_id action_id 8 action.action_id 11 Using where; Distinct 

поиск объектов завершен (пиковое ОЗУ: 5.91MB, время: 4.64s)

 SELECT DISTINCT object_id FROM version WHERE object_id IN (193793,193789, ... ,135326,135324) -- 10000 ids in here! ORDER BY created ASC LIMIT 50 OFFSET 0 

сортировка объектов завершена (пиковое ОЗУ: 6.68MB, время: 0.352s)

 SELECT `value`.*, object.*, version.*, type.*, object.status AS `object.status`, object.flag AS `object.flag`, version.created AS `version.created`, version.status AS `version.status`, version.flag AS `version.flag` FROM version INNER JOIN type ON version.type_id = type.type_id INNER JOIN object ON version.object_id = object.object_id LEFT JOIN value ON version.version_id = `value`.version_id WHERE version.object_id IN (135324,135326,...,135658,135661) AND version.type_id IN (30) ORDER BY quality DESC, version.created DESC 

запрос нагрузки объектов завершен (пиковое ОЗУ: 6.68MB, время: 0,083s)
компиляция объектов в массивы завершена (пиковое ОЗУ: 6.68MB, время: 0.007s)

 EXPLAIN SELECT DISTINCT `version`.object_id FROM `version`, etc ...