多表连接的Mysql查询优化

Question

有没有办法优化这个mysql查询

SELECT d.* 
FROM cm_stream a 
INNER JOIN cm_courses b ON a.stream_id = b.stream 
INNER JOIN cm_institute_courses c ON b.course_id = c.course 
INNER JOIN cm_institute d ON d.institute_id = c.institute 
WHERE a.path = 'engineering' 
GROUP BY c.institute

这个查询需要0.14秒或更长的时间来执行，我希望优化时间。

为相同的查询解释

​

​

索引详情

D表

  PRIMARY KEY (`institute_id`),
  UNIQUE KEY `cm_institute_unique` (`path`),
  KEY `importance` (`importance`),
  KEY `cover` (`cover`),
  KEY `logo` (`logo`),
  KEY `logo_2` (`logo`,`cover`,`importance`),
  KEY `active` (`active`),
  KEY `name` (`name`),
  KEY `abbreviation` (`abbreviation`),
  KEY `name_2` (`name`,`abbreviation`),
  KEY `path` (`path`,`institute_id`)

C表

  PRIMARY KEY (`institute_course_id`),
  KEY `course_key` (`course`),
  KEY `active` (`active`),
  KEY `institute` (`institute`,`course`),
  KEY `cm_institute_cours_idx_institute_active` (`institute`,`active`),
  KEY `institute_2` (`institute`,`course`)

B表

  PRIMARY KEY (`course_id`),
  UNIQUE KEY `path` (`path`),
  KEY `program` (`program`,`stream`),
  KEY `stream` (`stream`,`course_id`),

一张表格

  PRIMARY KEY (`stream_id`),
  UNIQUE KEY `path` (`path`),
  KEY `active` (`active`),
  KEY `cm_stream_idx_name_stream_id` (`name`,`stream_id`),
  KEY `active_2` (`active`,`name`),
  KEY `path_2` (`path`,`stream_id`)

Answer 1

由于您只能从cm_institute中进行选择，因此另一种方法是使用EXISTS，它有时比joins性能更好。

此外，也不需要DISTINCT或GROUP BY

SELECT d.* 
FROM cm_institute d
WHERE EXISTS (
  SELECT 1
  FROM cm_stream a 
  INNER JOIN cm_courses b ON a.stream_id = b.stream 
  INNER JOIN cm_institute_courses c ON b.course_id = c.course 
  WHERE a.path = 'engineering' AND d.institute_id = c.institute
);

Answer 2

对于多对多映射，索引可能是性能问题。

您只需要以下索引：

 # Basic for many_to_many table:
 PRIMARY KEY(course, institute),
 INDEX(institute, course)
 # Extras for your application:
  KEY `active` (`active`),
  KEY `cm_institute_cours_idx_institute_active` (`institute`,`active`),

更多讨论：http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

另外，这些索引：

a:  INDEX(path, stream_id)
b:  INDEX(stream,  course_id)
d:  INDEX(institute_id)  -- unless that is the PK

如需进一步讨论，请提供每个表的SHOW CREATE TABLE。