mysql - 如何优化规范化数据库结构的查询？

Question

我正在尝试优化目前在 MySQL 5.x 数据库上需要 0.00x 秒的查询，以便在没有负载的情况下检索系统上的数据。

查询如下所示:

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

表articles_attr包含大约50万行。

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)