mysql - 使用许多 LEFT JOIN 和 GROUP BY 优化 MySQL 查询

Question

我有以下经常使用的查询:

SELECT a.col1,
  b.col1,
  d.col1,
  c.col1,
  c.col2,
  c.col3,
  c.col4,
  a.col2,
  c.col5,
  c.col6,
  c.col7,
  b.col2
  FROM a
  LEFT JOIN c ON a.col3 = c.col1
  LEFT JOIN b ON a.col4 = b.col1
  LEFT JOIN d ON b.col3 = d.col2
  LEFT JOIN e ON b.col3 = e.col1
  where a.col4 != 'temp' and a.col5!=2
  GROUP BY a.col1,
  b.col1,
  d.col1,
  c.col1,
  c.col3,
  c.col4,
  a.col2,
  c.col5,
  c.col6,
  c.col7,
  b.col2
  limit 50;

此查询在远程服务器上运行，大约需要 5 分钟(我的互联网连接速度不慢)才能显示结果。到目前为止，我只使用过基本的 MySQL，我不确定如何优化上述查询。我在网上搜索了优化它的方法，比如添加索引，但我发现它们只适用于非常简单的情况，我无法将它们扩展到这个查询。

有人可以帮我创建索引以优化上述查询或任何其他方法来使查询运行得更快(比如创建一个额外的临时表)。

a 有大约 130 万条记录，b - 80k 条记录，c - 150k 条记录，d - 150 条记录。

运行查询 SHOW CREATE TABLE a给出以下结果:

| a  | CREATE TABLE `a` (
  `col1` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `col4` int(10) unsigned NOT NULL DEFAULT '0',
  `col5` int(10) unsigned NOT NULL DEFAULT '0',
  `col6` varchar(100) NOT NULL DEFAULT '',
  `col3` int(10) unsigned NOT NULL DEFAULT '0',
  `col7` varchar(250) NOT NULL DEFAULT '',
  `col2` int(10) unsigned NOT NULL DEFAULT '0',
  `col8` mediumtext,
  `col9` smallint(6) NOT NULL DEFAULT '0',
  `col10` smallint(6) NOT NULL DEFAULT '0',
  `col11` varchar(15) NOT NULL DEFAULT '',
  `col12` smallint(5) unsigned NOT NULL DEFAULT '0',
  `col13` smallint(6) NOT NULL DEFAULT '0',
  `col14` smallint(5) unsigned NOT NULL DEFAULT '0',
  `col15` smallint(5) unsigned NOT NULL DEFAULT '0',
  `col16` int(10) unsigned NOT NULL DEFAULT '0',
  `col17` int(10) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`col1`),
  KEY `col3` (`col3`),
  KEY `col4` (`col4`,`col3`),
  KEY `col2` (`col2`),
  KEY `col1` (`col1`),
  KEY `col1_2` (`col1`),
  KEY `col1_3` (`col1`),
  KEY `col1_4` (`col1`),
  KEY `col1_5` (`col1`),
  KEY `col1_6` (`col1`),
  KEY `col1_7` (`col1`),
  FULLTEXT KEY `col7` (`col7`,`col8`)
) ENGINE=InnoDB AUTO_INCREMENT=1339383 DEFAULT CHARSET=latin1 |

EXPLAIN <query>给出以下结果:

+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref           | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+
|  1 | SIMPLE      | e     | ALL    | NULL          | NULL    | NULL    | NULL          |  149 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | b     | ref    | PRIMARY,col3  | col3    | 2       | db.e.col1     |  286 | Using index condition                        |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 2       | db.b.col3     |    1 | NULL                                         |
|  1 | SIMPLE      | a     | ref    | col4          | col4    | 4       | db.b.col1     |   10 | Using where                                  |
|  1 | SIMPLE      | c     | eq_ref | PRIMARY       | PRIMARY | 4       | db.a.col3     |    1 | NULL                                         |
+----+-------------+-------+--------+---------------+---------+---------+---------------+------+----------------------------------------------+

Answer 1

最小索引::

    CREATE INDEX a0 ON a (col4, col5) ;
    CREATE INDEX a1 ON a (col3) ;
    CREATE INDEX a2 ON a (col4) ;

    CREATE INDEX b1 ON b (col1) ;
    CREATE INDEX b2 ON b (col3) ;

    CREATE INDEX c1 ON c (col1) ;

    CREATE INDEX d1 ON d (col2) ;

你也可以创建，

    CREATE INDEX e1 ON e (col1) ;

    CREATE INDEX a4 ON a (col1) ;