SQLinfo.ru - Все о MySQL Фестиваль «Российские интернет-технологии» 2017

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 11.01.2017 12:54:03

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Выборки из EAV структуры

Всем привет.
Работаю с стандартной структурой EAV.
MariaDB 5.5

CREATE TABLE `attributes` (
  `entity_id` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `attribute` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `value` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  KEY `p1_p2` (`attribute`,`value`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

Нужно извлекать entities имеющие перечисленные атрибуты.
Пример: color : black и size : big

Использую:

select * from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.entity_id
having count(a.entity_id)=2;


Запрос может быть весьма тяжелым , так как отбор совпадений уже выполняется в HAVING.

Может что то упускаю ? Другая структура ? Другой запрос ?

Спасибо.

Неактивен

 

#2 11.01.2017 13:11:39

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

Приветствую, красивый номер сообщения - 333

сделать суррогатный цифровой `_id`, который использовать в группировке вместо `entity_id`
и попробовать какой из вариантов индексов лучше подойдет
(`_id`,`attribute`,`value`)
(`attribute`,`value`,`_id`)

запрос лучше усложнить, во from части использовать `_id`,`attribute`,`value`, а потом после группировки дополнительным join подтянуть `entity_id`.

Неактивен

 

#3 11.01.2017 13:39:59

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выборки из EAV структуры

vasya написал:

Приветствую, красивый номер сообщения - 333

сделать суррогатный цифровой `_id`, который использовать в группировке вместо `entity_id`
и попробовать какой из вариантов индексов лучше подойдет
(`_id`,`attribute`,`value`)
(`attribute`,`value`,`_id`)

запрос лучше усложнить, во from части использовать `_id`,`attribute`,`value`, а потом после группировки дополнительным join подтянуть `entity_id`.

Извини не понял твою идею.
Можеш хотя бы примерно показать псевдо код.

Неактивен

 

#4 11.01.2017 14:28:42

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

select at.entity_id
from attributes at join
(select `_id` from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.`_id`
having count(*)=2) t ON t.`_id`=at.`_id`;


во from-подзапросе мы делаем группировку по числовому полю `_id`, и лишь определив нужные `_id` мы для них получаем текстовые entity_id.

Таблица будет иметь вид

CREATE TABLE `attributes` (
  `_id` INT NOT NULL,
  `entity_id` varchar(255) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `attribute` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
  `value` varchar(32) CHARACTER SET utf8mb4 NOT NULL DEFAULT '',
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


с индексом
(`_id`,`attribute`,`value`)  -- если подойдет этот вариант, то его сделать primary
или
(`attribute`,`value`,`_id`) + (`_id`)


как лучше генерить `_id` вопрос открытый
нужно уникальное значение на основе `entity_id` с минимальным размером
реализовать можно через триггеры, чтобы не затрагивать приложение, так как `_id` нужна только для оптимизации выполнения запроса

Неактивен

 

#5 11.01.2017 14:51:31

evgeny
Гуру
Зарегистрирован: 04.05.2009
Сообщений: 335

Re: Выборки из EAV структуры

select `_id` from attributes a
where
(a.attribute='color' and a.value='black')
or
(a.attribute='size' and a.value='big')
group by a.`_id`
having count(*)=2


Эта сама по себе проблематичная часть, от нее хочеться избавится.
color: black - 400,000 записей
size: big - 10 записей
а общих всего 2.

Mysql прейдеться пройти по 400,010 записей , перенести их в temporary table, отгрупировать и только потом найти общие 2.

Неактивен

 

#6 11.01.2017 15:15:43

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

это он предлагал, когда группировка по текстовому не индексированному полю
в моём варианте будет пошустрее
ещё можно поставить на первое место более селективное условие, т.е.
(a.attribute='size' and a.value='big')
or
(a.attribute='color' and a.value='black')
хотя, емнип, гарантии это не дает.

Но скорее всего работать будет быстро.

Неактивен

 

#7 28.01.2017 16:50:18

caror
Участник
Зарегистрирован: 28.01.2017
Сообщений: 5

Re: Выборки из EAV структуры

Добрый день, уважаемые форумчане. Уже несколько дней мучаюсь над одним запросом. Есть БД интернет магазина, структура такова:

Таблица товаров:
id, model, status
10000, test, 1
10001, test1, 0
10002, test2, 1

Таблица связей каталог - товар 32255 записей, будет как минимум в два раза больше
id, k_id, p_id
1, 37, 10000
2, 37, 10001
3, 38, 10002

Таблица связей товар - характеристики(features) 534730 записей(это примерно треть, будет больше)
id, f_id, p_id, value
1, 100, 10000, Белый
2, 100, 10001, Желтый
3, 100, 10002, Белый

Таблица характеристик
id, title, type
100, Цвет, T
101, Мощность, D

Есть еще конечно таблицы самих каталогов где хранятся наименования и др., но это сейчас не нужно. Так вот, пытаюсь сделать фильтр на сайте, и нужно сделать выборку возможных вариантов характеристик. То есть, есть ID характеристик, например 100, 101, 103, и т.д. и по этим ID нужно вытащить возможные варианты этих характеристик у тех товаров которые находятся в определенном каталоге. На данный момент есть написанный запрос, он выводит характеристики:


SELECT  
    sdvd_features_linking.f_id as id,
    sdvd_features.title,
    sdvd_features.`type`,
    IF(sdvd_features.`type` = 'T', GROUP_CONCAT(DISTINCT sdvd_features_linking.value SEPARATOR '*/*'), CONCAT(MIN(sdvd_features_linking.value*1), '*/*', MAX(sdvd_features_linking.value*1))) as val
FROM sdvd_features_linking
JOIN sdvd_features ON(sdvd_features_linking.f_id = sdvd_features.id)
WHERE sdvd_features_linking.f_id IN(1, 2, 3, 4, 5, 6)
GROUP BY sdvd_features_linking.f_id
 


Выполняется он за 0.2925 сек., но тут не учитывается каталог. Если подключить через JOIN таблицу связей каталог - товар, то запрос выполняется несколько минут.

Большая просьба особо не ругать, так как я самоучка и начал недавно.

Подскажите пожалуйста, как можно грамотно написать запрос

Неактивен

 

#8 28.01.2017 17:44:05

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

покажите план запроса explain select ..
и структуру таблиц в виде show create table `имя таблицы`

Неактивен

 

#9 30.01.2017 10:03:20

caror
Участник
Зарегистрирован: 28.01.2017
Сообщений: 5

Re: Выборки из EAV структуры

Таблица товаров (sdvd_products)
CREATE TABLE `sdvd_products` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(11) unsigned NOT NULL,
  `url` varchar(255) NOT NULL,
  `img` varchar(255) NOT NULL,
  `thumb` varchar(255) NOT NULL,
  `model` varchar(255) NOT NULL,
  `product_group` varchar(255) NOT NULL,
  `price` int(10) DEFAULT NULL,
  `valute` varchar(4) DEFAULT NULL,
  `inprice` int(11) DEFAULT NULL,
  `invalute` varchar(4) DEFAULT NULL,
  `delivery` int(10) unsigned DEFAULT NULL,
  `service` int(10) unsigned DEFAULT NULL,
  `pricenote` varchar(255) DEFAULT NULL,
  `discount` varchar(3) NOT NULL,
  `new` int(1) unsigned DEFAULT NULL,
  `hit` int(1) unsigned DEFAULT NULL,
  `date` int(10) unsigned NOT NULL,
  `available` int(1) unsigned NOT NULL,
  `status` int(1) unsigned NOT NULL,
  `brand` int(10) unsigned DEFAULT NULL,
  `mindesc` varchar(1500) DEFAULT NULL,
  `creator` int(10) unsigned NOT NULL,
  `suplier` varchar(50) DEFAULT NULL,
  `suplier_id` varchar(255) DEFAULT NULL,
  `classification` int(1) unsigned NOT NULL,
  `unit` varchar(255) DEFAULT NULL,
  `parce` int(1) unsigned NOT NULL,
  `link_ekatalog` varchar(1000) DEFAULT NULL,
  `link_mail` varchar(1000) DEFAULT NULL,
  `link_market` varchar(1000) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM AUTO_INCREMENT=12716 DEFAULT CHARSET=utf8

Таблица связей товар - категория (sdvd_katalog_linking)
CREATE TABLE `sdvd_katalog_linking` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `k_id` int(11) unsigned NOT NULL,
  `p_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=13378 DEFAULT CHARSET=utf8

Таблица связей товар - характеристика (sdvd_features_linking)
CREATE TABLE `sdvd_features_linking` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `p_id` int(11) unsigned NOT NULL,
  `f_id` int(11) unsigned NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=301307 DEFAULT CHARSET=utf8

Таблица характеристик (sdvd_features)

CREATE TABLE `sdvd_features` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `sort` int(10) unsigned NOT NULL,
  `type` varchar(1) NOT NULL,
  `show_at_table` int(1) unsigned NOT NULL,
  `show_at_vitrine` int(1) unsigned NOT NULL,
  `show_at_top` int(1) unsigned NOT NULL,
  `prefix` varchar(255) NOT NULL,
  `postfix` varchar(255) NOT NULL,
  `xml_tag_name` varchar(255) NOT NULL,
  `parent_group` int(10) unsigned NOT NULL,
  `features_group` varchar(255) NOT NULL,
  `features_group_prefix` varchar(255) NOT NULL,
  `features_group_postfix` varchar(255) NOT NULL,
  `features_group_sort` int(10) unsigned NOT NULL,
  `status` int(1) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=374 DEFAULT CHARSET=utf8

Далее план запроса(На картинке сверху)


EXPLAIN SELECT  
    sdvd_features_linking.f_id as id,
    sdvd_features.title,
    sdvd_features.`type`,
    IF(sdvd_features.`type` = 'T', GROUP_CONCAT(DISTINCT sdvd_features_linking.value SEPARATOR '*/*'), CONCAT(MIN(sdvd_features_linking.value*1), '*/*', MAX(sdvd_features_linking.value*1))) as val
FROM sdvd_features_linking
JOIN sdvd_features ON(sdvd_features_linking.f_id = sdvd_features.id)
WHERE sdvd_features_linking.f_id IN(1, 2, 3, 4, 5, 6)
GROUP BY sdvd_features_linking.f_id
 


Запрос занял 0.4494 сек.

И план другого запроса, где подключаю уже таблицу связей товар - каталог, что бы выбирать только характеристики товаров из нужных категорий(На картинке снизу)


EXPLAIN SELECT  
    sdvd_features_linking.f_id as id,
    sdvd_features.title,
    sdvd_features.`type`,
    IF(sdvd_features.`type` = 'T', GROUP_CONCAT(DISTINCT sdvd_features_linking.value SEPARATOR '*/*'), CONCAT(MIN(sdvd_features_linking.value*1), '*/*', MAX(sdvd_features_linking.value*1))) as val
FROM sdvd_features_linking
JOIN sdvd_features ON(sdvd_features_linking.f_id = sdvd_features.id)
JOIN sdvd_katalog_linking ON(sdvd_features_linking.p_id = sdvd_katalog_linking.p_id)
WHERE sdvd_features_linking.f_id IN(1, 2, 3, 4, 5, 6) AND sdvd_katalog_linking.k_id IN(4,6,7)
GROUP BY sdvd_features_linking.f_id
 


Запрос занял 38.6974 сек.

Неактивен

 

#10 30.01.2017 10:12:23

caror
Участник
Зарегистрирован: 28.01.2017
Сообщений: 5

Re: Выборки из EAV структуры

Планы запросов(кажется картинка не загрузилась)

1-ой запрос

id   select_type   table                           type      possible_keys   key              key_len   ref                                                             rows     Extra
1    SIMPLE        sdvd_katalog_linking      ALL        NULL              NULL            NULL      NULL                                                          34983    Using where; Using temporary; 
                                                                                                                                                                                                       Using filesort
1    SIMPLE        sdvd_features_linking     ALL        NULL              NULL            NULL      NULL                                                          584488  Using where;
                                                                                                                                                                                                       Using join buffer (flat, BNL join) 
1    SIMPLE        sdvd_features               eq_ref     PRIMARY        PRIMARY        4           host1400239_nsdb.sdvd_features_linking.f_id    1

2-ой запрос

id   select_type   table                           type      possible_keys   key              key_len   ref                                                             rows     Extra
1    SIMPLE        sdvd_features_linking     ALL        NULL              NULL            NULL      NULL                                                          584488  Using where; Using filesort 
1    SIMPLE        sdvd_features               eq_ref     PRIMARY        PRIMARY        4           host1400239_nsdb.sdvd_features_linking.f_id    1


Прикрепленные файлы:
Attachment Icon 1.jpg, Размер: 81,250 байт, Скачано: 22

Неактивен

 

#11 30.01.2017 11:49:48

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

так у вас совсем нет индексов, неудивительно, что так долго работает
зачем id в таблицах связей?

нужен индекс на (f_id,p_id) в sdvd_features_linking
аналогично для sdvd_katalog_linking

посмотрите FAQ №5

для примера, должно быть что-то вроде:
CREATE TABLE `sdvd_katalog_linking` (
  `k_id` int(11) unsigned NOT NULL,
  `p_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`k_id`,`p_id`),
  index (`p_id`)
)

Неактивен

 

#12 30.01.2017 12:07:53

caror
Участник
Зарегистрирован: 28.01.2017
Сообщений: 5

Re: Выборки из EAV структуры

Где то вычитал, что колонка `id` в каждой таблице - это грамотно. У самого были сомнения, ведь это получается лишняя информация... Сейчас попробую переделать таблицы...


Почитал информацию по ссылке которую вы мне скинули, большое спасибо. Возник один вопрос, дело в том, что в таблице связей товар - каталог id товара может повторяться, так как один товар может быть одновременно в разных категориях, а PRIMARY KEY должен быть уникальным... Или же в примере, что вы написали "PRIMARY KEY (`k_id`,`p_id`)" уникальность будет высчитываться сразу по двум полям `k_id` и `p_id`?

Неактивен

 

#13 30.01.2017 12:51:32

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5130

Re: Выборки из EAV структуры

да, уникальность для комбинации двух полей

Неактивен

 

#14 30.01.2017 13:22:45

caror
Участник
Зарегистрирован: 28.01.2017
Сообщений: 5

Re: Выборки из EAV структуры

Проставил ключи, все стало работать очень быстро. Огромное Вам спасибо:)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson