如何优化规范化数据库结构的查询?

goucqfw6  于 2021-06-17  发布在  Mysql
关注(0)|答案(3)|浏览(399)

我正在尝试优化一个查询,该查询在mysql 5.x db上当前需要0.00x s,以便在没有负载的情况下检索系统上的数据。
查询如下所示:

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\u attr包含大约50万行。

yyyllmsg

yyyllmsg1#

自从你的 WHERE 子句指定 article_id ,没有必要让 select 把它还给我。最好删除它,这也是因为它不符合sql标准,即如果您有一个聚合( group_concat )中的所有非聚合表达式 select 子句必须在 group by 条款。但是这样做(就像你问题的第一个版本)会增加一些开销。所以最好把它拿走。
作为 WHERE 条件在主键上,您不需要来自 articles_test 表,您可以省略 articles_test table,把 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)
wswtfjt7

wswtfjt72#

首先,9毫秒对于这样的查询来说还不错。没有根本的改善。您可能可以从查询中再挤出一两毫秒,但可能无法。
你的三列索引 articles_attr 看起来不错。您可以尝试切换索引中前两列的顺序,以查看是否获得更好的性能。
实际上,该表上的单列索引是不必要的:提供索引功能是因为在三列索引中,同一列排在第一位。删除该索引可能无助于提高查询性能,但有助于提高查询性能。 GROUP_CONCAT() 在这里有道理。聚合整个结果集是完全有效的。你可以加上 GROUP BY a.article_id 只是为了澄清;它不会对性能产生任何影响,因为您已经只选择了该列的一个值。
cat_attr ,上的复合索引 (attr_id, attr_de) 可能会有帮助。但那显然是张小table,所以帮不了什么忙。
你需要这个吗 LEFT JOIN 要加入的操作 articles_attrcat_attr ? 或者,根据您的数据结构,是 articles_attr.attr_id 一定能找到匹配的 cat_attr.attr_id . 如果你能改变这些 LEFT JOIN 操作到 JOIN 你可能会稍微加速。

tmb3ates

tmb3ates3#

`attr_id` mediumint(8) unsigned DEFAULT NULL,

为什么? NULL ? 你不总是需要一个属性吗?我提出这个问题的原因是你没有明确的 PRIMARY KEYarticles_attr . 这个 NULL 阻止提升 UNIQUE pk键。更改为 NOT NULL 促进 UNIQUE 去pk。

KEY `article_id` (`article_id`)

多余的,放下它。
结构many:many tables 是次优的。几点提示:http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table
如果你不需要”many:many,切换到“1:多”;效率更高。
你可以用 JOIN 而不是 LEFT JOIN 因为你需要一路去 attr_f 以及 attr_n .
将组连接移动到 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模式设计变得更糟!。也就是说,把table扔掉 cat_attr ,然后移动 attr_de 进入 articles_attr . 这将减少一半的 JOINs .

相关问题