mysql - 加快这个 JOIN，MySQL

Question

这与我发布的另一个问题类似，但我被告知将它们分开。

我有一个 InnoDB MYSQL 表，它存储一个多选名称(或表调用的“代码”)、一个父对象的 ID (parent_id) 和在多选中选择的选项的名称 (name_id):

CREATE TABLE IF NOT EXISTS `v2_CA_venue_option_map` (
  `map_id` int(11) NOT NULL auto_increment,
  `code` varchar(30) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `name_id` int(11) NOT NULL,
  PRIMARY KEY  (`map_id`),
  UNIQUE KEY `3way_unique` (`code`,`parent_id`,`name_id`),
  KEY `name_id` (`name_id`),
  KEY `filter` (`code`,`name_id`),
  KEY `parent_id` (`parent_id`),
  KEY `code` (`code`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=875156 ;

和一个简单的表来存储名称(我想我会显示这个，因为它在查询中使用):

CREATE TABLE IF NOT EXISTS `v2_CA_venue_option_name` (
  `name_id` int(11) NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY  (`name_id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 COMMENT='Venue Option Names' AUTO_INCREMENT=60 ;

CREATE TABLE IF NOT EXISTS `v2_CA_venues` (
  `venue_id` int(11) NOT NULL auto_increment,
  `status` char(1) NOT NULL,
  `name` varchar(255) NOT NULL,
  `url_key` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY  (`venue_id`),
  KEY `city` (`city`,`status`),
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

我想针对以下查询进行优化:

SELECT v.name, v.venue_id, v.url_key
FROM `v2_CA_venue_option_map` map
JOIN `v2_CA_venue_option_map` map2 ON (map2.parent_id = map.parent_id)
JOIN `v2_CA_venue_option_map` map3 ON (map3.parent_id = map.parent_id)
JOIN `v2_CA_venues` v ON (v.venue_id = map.parent_id)
WHERE v.city = 'Nevada City'
AND map3.code = 'a_venue_types'
AND map3.name_id = 19
AND map.code = 'a_event_types'
AND map.name_id = 20
AND map2.code = 'a_event_types'
AND map2.name_id = 3
AND v.status = 'a'

1. 表中放置的索引是否为这些 Join 和 WHERE 提供最佳性能？
2. 是否有任何索引无用？

上述查询的解释:

非常感谢!我正在寻找有关如何从这些表中获得最佳性能的建议。

Answer 1

对于您的 v2_CA_venues 表，我看到您有一个索引 ON( City, Status )。将其用作查询中的第一个。您的 map 表可能有 1000 个条目，但单个“城市 + 状态”的条目数……少得多。所以现在在您的查询顶部将有助于优化其余部分。使用关键字“STRAIGHT_JOIN”告诉优化器按照您指定的顺序执行。

你有一个 3way_unique 索引的索引，我会更改(或添加另一个索引)最小元素作为索引中的第一个位置...我实际上会将顺序更改为 ( parent_id、name_id、代码)。您可以有 100 个代码，但有 1000 个 parent_id。但是现在，您正在寻找一个可能有多个代码的特定父 ID...所以现在，您的索引将下降到 ex:ONE Parent_ID，它可能有 8 个代码，而您正在寻找 3 个代码。您已经只是减少了连接的比较集。

SELECT STRAIGHT_JOIN
      v.name, 
      v.venue_id, 
      v.url_key
   FROM 
      v2_CA_venues v

         join v2_CA_venue_option_map map 
            ON v.venue_id = map.parent_id
            AND map.name_id = 20
            and map.code = 'a_event_types'

         join v2_CA_venue_option_map map2
            ON v.venue_id = map2.parent_id
            AND map2.name_id = 3
            and map2.code = 'a_event_types'

         join v2_CA_venue_option_map map3
            ON v.venue_id = map3.parent_id
            AND map3.name_id = 19
            and map3.code = 'a_venue_types'

   where 
          v.City = 'Nevada City'
      and v.status = 'a'