Как оптимизировать запрос по нормализованной структуре базы данных?

Question

Я пытаюсь оптимизировать запрос, который в настоящее время занимает 0,00x s на БД MySQL 5.x, чтобы получить данные в системе без нагрузки.

Запрос выглядит так:

SELECT 
   a.article_id,
   GROUP_CONCAT(attr_f.attr_de) AS functions, 
   GROUP_CONCAT(attr_n.attr_de) AS miscellaneous
FROM `articles_test` a
LEFT JOIN articles_attr AS f ON a.article_id = f.article_id AND f.attr_group_id = 26
LEFT JOIN articles_attr AS attr ON a.article_id = attr.article_id AND attr.attr_group_id = 27
LEFT JOIN cat_attr AS attr_f ON attr_f.attr_id = f.attr_id
LEFT JOIN cat_attr AS attr_n ON attr_n.attr_id = attr.attr_id
WHERE a.article_id = 11

ОБЪЯСНИТЬ возврат

1   SIMPLE  a   
    NULL
    const   article_id  article_id  3   const   1   100.00  
    NULL

1   SIMPLE  f   
    NULL
    ref article_id_2,article_id article_id_2    6   const,const 2   100.00  Using index 
1   SIMPLE  attr    
    NULL
    ref article_id_2,article_id article_id_2    6   const,const 4   100.00  Using index 
1   SIMPLE  attr_f  
    NULL
    ref attr_id attr_id 3   test.f.attr_id  1   100.00  
    NULL

1   SIMPLE  attr_n  
    NULL
    ref attr_id attr_id 3   test.attr.attr_id   1   100.00  
    NULL

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

CREATE TABLE `articles_attr` (
 `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `article_id` mediumint(8) unsigned NOT NULL,
 `attr_group_id` mediumint(8) NOT NULL,
 `attr_id` mediumint(8) unsigned DEFAULT NULL,
 `value` varchar(255) DEFAULT NULL,
 UNIQUE KEY `article_id_2` (`article_id`,`attr_group_id`,`attr_id`),
 KEY `article_id` (`article_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

CREATE TABLE `cat_attr` (
 `attr_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 `attr_group_id` mediumint(8) unsigned NOT NULL,
 `sort` tinyint(4) NOT NULL,
 `attr_de` varchar(255) NOT NULL,
 UNIQUE KEY `attr_id` (`attr_id`,`attr_group_id`),
 UNIQUE KEY `attr_group_id` (`attr_group_id`,`attr_de`)
) ENGINE=InnoDB AUTO_INCREMENT=380 DEFAULT CHARSET=utf8

CREATE TABLE `articles_test` (
 `article_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
 UNIQUE KEY `article_id` (`article_id`),
) ENGINE=InnoDB AUTO_INCREMENT=221614 DEFAULT CHARSET=latin1

Таблица article_attr содержит около 0,5 миллиона строк.

Answer 1

Так как ваш WHERE предложение определяет значение article_idнет никакой реальной необходимости позволять select пункт вернуть его. Лучше удалить его, также потому что он не соответствует стандартам SQL, которые говорят, что если у вас есть агрегация (group_concat) все неагрегирующие выражения в select пункт должен быть в group by пункт. Но выполнение этого (как в первой версии вашего вопроса) дало бы некоторые издержки. Так что лучше убери это.

Как WHERE условие на первичном ключе, и вам не нужны никакие данные из articles_test таблица, вы можете опустить articles_test стол, и положить WHERE вместо этого условие на внешние ключи.

Наконец, есть своеобразное декартово соединение, поскольку вы объединяете каждый удар в attr_f с каждым попаданием в attr_n, Это может привести к появлению дубликатов в group_concat выводит и представляет хит производительности.

Если вы можете удалить такие дубликаты, то, возможно, у вас будет лучшая производительность, если разделить запрос на группы: один для вывода функции, другой для разного вывода. Затем группа формируется attr_group_id,

Это также позволит превратить внешние соединения во внутренние соединения.

Таким образом, результатом будет непивотная версия того, что вы искали:

SELECT     attr.attr_group_id, GROUP_CONCAT(cat.attr_de) AS functions
FROM       articles_attr AS attr 
INNER JOIN cat_attr AS cat ON cat.attr_id = attr.attr_id
WHERE      attr.article_id = 11
       AND attr.attr_group_id IN (26, 27) 
GROUP BY   attr.attr_group_id

Так что теперь на выходе будет две строки. Один с 26 в первом столбце будет перечислять функции во втором столбце, а тот с 27 в первом столбце будет перечислять разные.

Правда, формат вывода отличается, но я думаю, что вы сможете переработать код, который использует этот запрос, и в то же время выиграть от повышения производительности (что я ожидаю).

Если вам нужна развернутая версия, используйте case when выражение:

SELECT     GROUP_CONCAT(CASE attr.attr_group_id WHEN 26 THEN cat.attr_de END) AS functions,
           GROUP_CONCAT(CASE attr.attr_group_id WHEN 27 THEN cat.attr_de END) AS miscellaneous
FROM       articles_attr AS attr 
INNER JOIN cat_attr AS cat ON cat.attr_id = attr.attr_id
WHERE      attr.article_id = 11
       AND attr.attr_group_id IN (26, 27) 

Answer 2

`attr_id` mediumint(8) unsigned DEFAULT NULL,

Зачем NULL? Тебе не всегда нужен attr? Я поднимаю этот вопрос по той причине, что у вас нет PRIMARY KEY на articles_attr, NULL мешает продвижению UNIQUE ключ к ПК. Изменить на NOT NULL и продвигать UNIQUE на ПК.

KEY `article_id` (`article_id`)

Избыточный, Брось это.

Структура многих: многие таблицы неоптимальны. Несколько советов: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Если вам не нужно "много: много", переключитесь на "1: много"; это более эффективно.

Вы можете, вероятно, использовать JOIN вместо LEFT JOIN так как вам нужно пройти весь путь до attr_f а также attr_n,

Перемещение соединений для Group_concats в SELECT может помочь:

SELECT  a.article_id, 
        (
        SELECT  GROUP_CONCAT(ca.attr_de)
            FROM  articles_attr AS aa
            JOIN  cat_attr AS ca USING(attr_id)
            WHERE  aa.attr_group_id = 26
              AND  aa.article_id = a.article_id
        ) AS functions, 
        (
        SELECT  GROUP_CONCAT(attr_f.attr_de)
            FROM  ..
            JOIN  ..
            WHERE  .. 
        ) AS miscellaneous
    FROM  `articles_test` a
    WHERE  a.article_id = 11

Но, пожалуй, самое важное - не ухудшать и без того плохой дизайн схемы EAV, нормализуя атрибуты! То есть избавиться от стола cat_attrи двигаться attr_de в articles_attr, Это сократит вдвое количество JOINs,

Answer 3

Прежде всего, 9 мс для такого запроса неплохо. Там нет никакого радикального улучшения, которое будет иметься. Вы можете выжать еще одну или две миллисекунды из запроса, а можете и нет.

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

Как таковой, ваш индекс по одной колонке для этой таблицы не нужен: эта функция индексации предоставляется, потому что тот же столбец стоит первым в вашем индексе из трех столбцов. Удаление этого индекса, вероятно, не повлияет на производительность вашего запроса, но поможет повысить производительность.

GROUP_CONCAT() здесь имеет смысл. Совершенно верно агрегировать весь набор результатов. Вы можете добавить GROUP BY a.article_id просто для наглядности; это не повлияет на производительность, потому что вы уже выбрали только одно значение этого столбца.

На cat_attrсоставной индекс на (attr_id, attr_de) может помочь Но это, очевидно, маленький столик, так что он не сильно поможет.

Вам нужен LEFT JOIN операции присоединиться articles_attr в cat_attr? Или, по структуре ваших данных, каждое значение articles_attr.attr_id гарантированно найти совпадение в cat_attr.attr_id, Если вы можете изменить эти LEFT JOIN операции в JOINs вы можете получить небольшое ускорение.