mysql - 具有多个表 JOIN 的 SQL SELECT DISTINCT 性能

Question

我正在建立一个包含大学类(class)信息的数据库。每门类(class)都可以关联

• 一位或多位作者
• 一门或多门学科
• 一个或多个机构
• 一个或多个级别

我的数据库包含以下表格:

• 类(class)(cou_id、cou_name、cou_number、cou_year、cou_term)
• 作者(aut_id，aut_last)
• 纪律(dis_id，dis_name)
• 机构(ins_id、ins_name、ins_classification)
• 级别(lev_id，lev_name)
• 作者类(class)(链接表)
• 类(class)学科(链接表)
• 类(class)机构(链接表)
• courselevel(链接表)

为了从数据库中检索所有类(class)(以及相应的作者、学科、机构和级别信息)，我使用以下查询:

SELECT DISTINCT aut_last, c.cou_id, cou_name, cou_number, cou_year, cou_term, dis_name, ins_name, ins_classification, lev_name
    FROM authorcourse ac1
    INNER JOIN authorcourse ac2        
    ON ac1.cou_id = ac2.cou_id        
    INNER JOIN author a
    ON ac2.aut_id=a.aut_id
    INNER JOIN course c
    ON ac2.cou_id = c.cou_id
    INNER JOIN coursediscipline cd1
    ON ac2.cou_id = cd1.cou_id
    INNER JOIN coursediscipline cd2
    ON cd1.cou_id = cd2.cou_id
    INNER JOIN discipline d
    ON cd2.dis_id = d.dis_id
    INNER JOIN courseinstitution ci1
    ON ac2.cou_id = ci1.cou_id
    INNER JOIN courseinstitution ci2
    ON ci1.cou_id = ci2.cou_id
    INNER JOIN institution i
    ON ci2.ins_id = i.ins_id
    INNER JOIN courselevel cl1
    ON ac2.cou_id = cl1.cou_id
    INNER JOIN courselevel cl2
    ON cl1.cou_id = cl2.cou_id
    INNER JOIN level l
    ON cl2.lev_id = l.lev_id

当数据库中有 15 个具有“简单”关系的类(class)时，此查询效果很好。例如:

 cou_name = 'course1', cou_number = 'C1', cou_year = '1999', cou_term = 'summer'
 aut_last = 'Doe1'
 dis_name = 'discipline1'
 ins_name = 'institution1', ins_classification = 'classification1'
 lev_name = 'level1'

-->显示第 0 - 14 行(总共 15 行，查询耗时 0.0118 秒)EXPLAIN 生成下表:

id select_type table type   possible_keys          key     key_len ref             rows Extra
1  SIMPLE      ac1   index  cou_id                 aut_id  2       NULL            15   Using index; Using  temporary
1  SIMPLE      ac2   ref    PRIMARY,aut_id,cou_id  cou_id  2       ccdb.ac1.cou_id 1    Using index
1  SIMPLE      a     eq_ref PRIMARY                PRIMARY 2       ccdb.ac2.aut_id 1    
1  SIMPLE      c     eq_ref PRIMARY                PRIMARY 2       ccdb.ac2.cou_id 1    Using where
1  SIMPLE      cd1   ref    PRIMARY,cou_id         PRIMARY 2       ccdb.ac1.cou_id 1    Using index
1  SIMPLE      cd2   ref    PRIMARY,cou_id,dis_id  PRIMARY 2       ccdb.ac2.cou_id 1    Using where; Using index
1  SIMPLE      d     eq_ref PRIMARY                PRIMARY 2       ccdb.cd2.dis_id 1    
1  SIMPLE      ci1   ref    PRIMARY,cou_id         PRIMARY 2       ccdb.ac2.cou_id 1    Using where; Using index
1  SIMPLE      ci2   ref    PRIMARY,cou_id,ins_id  PRIMARY 2       ccdb.ac2.cou_id 1    Using where; Using index
1  SIMPLE      i     eq_ref PRIMARY                PRIMARY 2       ccdb.ci2.ins_id 1    
1  SIMPLE      cl1   ref    PRIMARY,cou_id         PRIMARY 2       ccdb.cd1.cou_id 1    Using where; Using index
1  SIMPLE      cl2   ref    PRIMARY,cou_id,lev_id  PRIMARY 2       ccdb.cl1.cou_id 1    Using where; Using index
1  SIMPLE      l     eq_ref PRIMARY                PRIMARY 2       ccdb.cl2.lev_id 1    

问题:当有 15 个具有多个关系的类(class)时，性能会急剧下降。示例类(class):

cou_name = 'course1', cou_number = 'C1', cou_year = '1999', cou_term = 'summer'
aut_last = 'Doe1', 'Doe', 'Doe3', 'Doe4'
dis_name = 'discipline1', 'discipline2', 'discipline3', 'discipline4'
ins_name = 'institution1'(ins_classification = 'classification1'),     'institution2'(ins_classification = 'classification2'), 'institution3'(ins_classification =  'classification3'), 'institution4' (ins_classification = 'classification4')
lev_name = 'level1', 'level2', 'level3', 'level4'

-->显示第 0 - 29 行(总共 3,840，查询用时 14.7039 秒)EXPLAIN 生成下表:

 id select_type table type   possible_keys         key     key_len ref             rows Extra
 1  SIMPLE      c     ALL    PRIMARY               NULL    NULL    NULL            15   Using temporary
 1  SIMPLE      ac1   ref    PRIMARY,aut_id,cou_id cou_id  2       ccdb.c.cou_id   2    Using index
 1  SIMPLE      a     eq_ref PRIMARY               PRIMARY 2       ccdb.ac1.aut_id 1    
 1  SIMPLE      ac2   ref    cou_id                cou_id  2       ccdb.c.cou_id   2    Using index
 1  SIMPLE      cd1   ref    PRIMARY,cou_id        cou_id  2       ccdb.ac1.cou_id 2    Using where; Using index
 1  SIMPLE      cd2   ref    PRIMARY,cou_id,dis_id cou_id  2       ccdb.c.cou_id   2    Using index
 1  SIMPLE      d     eq_ref PRIMARY               PRIMARY 2       ccdb.cd2.dis_id 1    
 1  SIMPLE      ci1   ref    PRIMARY,cou_id        cou_id  2       ccdb.ac1.cou_id 2    Using where; Using index
 1  SIMPLE      ci2   ref    PRIMARY,cou_id,ins_id cou_id  2       ccdb.ac2.cou_id 2    Using where; Using index
 1  SIMPLE      i     eq_ref PRIMARY               PRIMARY 2       ccdb.ci2.ins_id 1    
 1  SIMPLE      cl1   ref    PRIMARY,cou_id        cou_id  2       ccdb.c.cou_id   2    Using index
 1  SIMPLE      cl2   ref    PRIMARY,cou_id,lev_id cou_id  2       ccdb.ci2.cou_id 2    Using where; Using index
 1  SIMPLE      l     eq_ref PRIMARY               PRIMARY 2       ccdb.cl2.lev_id 1    

当运行我的 PHP 网站时，我收到以下错误“ fatal error :超过 30 秒的最长执行时间……”

问题:我怎样才能加快这个查询？我尝试了几种不同的连接组合和(正如您在 EXPLAIN 结果中看到的那样)我索引了所有我认为可能相关的列。

如有任何帮助，我们将不胜感激。

Answer 1

在我看来，您好像是在为“类(class) View ”类型的详细信息页面提取所有这些数据？

如果是这样的话，我会说，一旦在数据库中制作了一门类(class)，作者、学科、机构和级别的数量多久会发生变化？

如果设置的时间永远不会改变，那么设置后，也将其设置在一个完全非规范化的表中，如下所示:

courseView(cou_id、cou_name、cou_number、cou_year、cou_term、数据)

.. 在“数据”中，您只需放入所有数据的序列化数组。丑陋，但它会很快。

然后，当你通过类(class)id搜索拉出一个时，你可以只搜索一行，一个索引，并立即拉出所有数据。

..

此外，如果您打算让人们按作者进行搜索，那么您仍然可以像往常一样通过简单查询使用规范化表来执行此操作。