MySql，索引和加速查询

Question

Stackoverflow 的好心人再次大家好。根据类似问题的一些答案，我相信向表中添加索引将有助于下面的查询。挑战是，我不太熟悉或不太习惯使用索引，因为如果使用太多索引，似乎可能会减慢其他查询的速度。只是寻找有人帮助引导我在这里走向正确的方向。预先感谢您的帮助。

查询:

SELECT
    territories.territoryID,
    territories.territory_name,
    territories_meta.tm_color,
    territories.territory_description,
    territories.territory_state,
    GROUP_CONCAT(distinct(territories_zips.tz_zip)SEPARATOR ', ' ) AS ZipCodes,
    count(distinct(users.userID)) as AgentsAssigned,
    GROUP_CONCAT(distinct(concat(users.user_Fname,' ',users.user_Lname))SEPARATOR ', ') 
         AS AgentName,
   a.sumTerr as TotalOpp
from(
   SELECT
       territories_zips.tz_terrID as terrID,
       sum(boundaries_meta.bm_opportunity) as sumTerr
   FROM territories_zips
   INNER JOIN boundaries ON boundaries.boundary_name = territories_zips.tz_zip
   INNER JOIN boundaries_meta ON boundaries.boundary_id = boundaries_meta.bm_boundariesID
   where tz_status = 1
   group by tz_terrID
)as a
inner join territories on territories.territoryID = a.terrId
INNER JOIN territories_zips ON territories.territoryID = territories_zips.tz_terrID
INNER JOIN territories_assign ON territories.territoryID = territories_assign.ta_territoryID
INNER JOIN users ON users.userID = territories_assign.ta_repID
INNER JOIN territories_meta ON territories_meta.tm_territoryID = territories.territoryID
WHERE
   territories_zips.tz_status = 1 AND
   territories_assign.ta_repStatus = 1 AND
   users.user_status = 1
GROUP BY territoryID

解释:

id  select_type table   typw    possible_keys   key key_len ref rows    extra
1   PRIMARY <derived2>  ALL                 97  Using temporary; Using filesort
1   PRIMARY territories_meta    ALL                 121 Using where; Using join buffer
1   PRIMARY territories_zips    ALL                 1739    Using where; Using join buffer
1   PRIMARY territories_assign  ALL                 138 Using where; Using join buffer
1   PRIMARY users   eq_ref  PRIMARY PRIMARY 8   msb_db.territories_assign.ta_repID  1   Using where
1   PRIMARY territories eq_ref  PRIMARY PRIMARY 8   msb_db.territories_meta.tm_territoryID  1   Using where
2   DERIVED territories_zips    ALL                 1739    Using where; Using temporary; Using filesort
2   DERIVED boundaries_meta ALL                 42995   Using join buffer
2   DERIVED boundaries  eq_ref  PRIMARY PRIMARY 4   msb_db.boundaries_meta.bm_boundariesID  1   Using where

我认为罪魁祸首是子查询的这一部分，因为与上面的整个查询相比，运行该部分只需要 2 秒。

SELECT
   territories_zips.tz_terrID as terrID,
   sum(boundaries_meta.bm_opportunity) as sumTerr
FROM territories_zips
INNER JOIN boundaries ON boundaries.boundary_name = territories_zips.tz_zip
INNER JOIN boundaries_meta ON boundaries.boundary_id = boundaries_meta.bm_boundariesID
where tz_status = 1
group by tz_terrID

解释一下:

 id select_type table   typw    possible_keys   key key_len ref rows    extra
 1  SIMPLE  territories_zips    ALL                 1739    Using where; Using temporary; Using filesort
 1  SIMPLE  boundaries_meta ALL                 42995   Using join buffer
 1  SIMPLE  boundaries  eq_ref  PRIMARY PRIMARY 4   mb_db.boundaries_meta.bm_boundariesID   1   Using where

我已为此子查询添加了下表，如果我需要重新发布其他表结构，请告诉我

表格:

CREATE TABLE `boundaries` (
      `boundary_id` int(11) NOT NULL AUTO_INCREMENT,
  `boundary_name` varchar(20) DEFAULT NULL,
  `geometry_type` varchar(12) DEFAULT NULL,
  `boundary_geometry` mediumtext,
  `boundary_type` varchar(5) DEFAULT NULL,
  `boundary_state` varchar(4) DEFAULT NULL,
  PRIMARY KEY (`boundary_id`)
 ) ENGINE=MyISAM AUTO_INCREMENT=64504 DEFAULT CHARSET=utf8;

 CREATE TABLE `boundaries_meta` (
   `boundaries_metaID` bigint(20) NOT NULL AUTO_INCREMENT,
   `bm_boundariesID` bigint(20) NOT NULL,
   `bm_opportunity` int(5) NOT NULL,
  PRIMARY KEY (`boundaries_metaID`)
) ENGINE=MyISAM AUTO_INCREMENT=51201 DEFAULT CHARSET=utf8;


 CREATE TABLE `territories_zips` (
  `terr_zipsID` bigint(10) NOT NULL AUTO_INCREMENT,
 `tz_terrID` bigint(10) NOT NULL,
 `tz_zip` varchar(5) CHARACTER SET latin1 NOT NULL,
 `tz_status` smallint(1) NOT NULL,
 PRIMARY KEY (`terr_zipsID`)
) ENGINE=MyISAM AUTO_INCREMENT=2576 DEFAULT CHARSET=utf8;

再次感谢您的帮助。

编辑:我用索引更新了一些表，并得到了令人难以置信的改进(再次感谢艾萨克国王)。我在子查询中包含了新的解释，因为我仍然不知道这如何以及为什么有帮助，或者我是否实际上在正确的部分创建了索引。授人以鱼，教他钓鱼……

  id    select_type table   type    possible keys   key key_len ref    rows   extra
1   SIMPLE  territories_zips    ALL                 1739      Using where; Using temporary; Using filesort
1   SIMPLE  boundaries  ref PRIMARY,bndIDindex,bndNameindex bndNameindex    63  func    1   Using where
1   SIMPLE  boundaries_meta eq_ref  bmBndIDindex    bmBndIDindex    8   mb_db.boundaries.boundary_id    1   Using where

Answer 1

看起来您的第一步是处理 tz_zip 和 boundary_name 连接。我的第一个问题是:这些是独一无二的吗？对这些表应用 UNIQUE 索引应该会显着加快子查询的速度。如果它们不是唯一的，那么标准索引仍将为您提供足够高的基数以实现速度提升。

所有表上的“状态”字段也应该建立索引。尽管这些最终将成为低基数索引，但它将有利于查询，而不会造成太多索引开销。

您可能还想看看是否可以重构此查询以消除“from”子句中的子查询。这导致整个查询依赖于临时表，该临时表必须在查询过程继续之前完全建立。我敢说这也是你看到这么多“所有”类型的原因。查询分析器无法对数据的子集进行操作，因此它正在执行全表扫描。当它发生在一张 table 上时，这很糟糕，在你的情况下，它发生在五张 table 上。

我会将 boundary_meta 视为另一个连接，并在 SELECT 中处理 SUM(boundaries_meta.bm_opportunity)。它可能需要是一个依赖子查询，但您仍然应该看到性能的提高。

至于您对索引速度的担忧:向表添加多个索引时，过度索引可能会成为问题，但通常情况下，除非您对多个基于“char”的列建立索引，否则这不是一个问题。由于我们只讨论两个 varchar(5) 列，因此这不应该成为问题。

是否对列建立索引始终是一个成本/ yield 问题。成本可以用大小来衡量， yield 可以用基数来衡量。

这里最好的选择是使用查询结构和索引。如果有必要(和一个选项)，请将数据库克隆到单独的服务器，然后尝试不同的解决方案，直到找到有效的解决方案。