mysql - 如何优化缓慢的修改预序树遍历查询

Question

我有一个包含 82117 条记录的修改前序树遍历 (MPTT) 表。这个mysql表包含一些地理数据(国家、州、地区、城市...)

CREATE TABLE IF NOT EXISTS `geotree` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `geo_id` int(11) DEFAULT NULL,
  `name` varchar(80) COLLATE utf8_unicode_ci NOT NULL,
  `lft` int(11) NOT NULL,
  `rgt` int(11) NOT NULL,
  `parent` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`,`lft`,`rgt`),
  KEY `geo_id` (`geo_id`),
  KEY `parent` (`parent`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=82174 ;

当我尝试使用标准 MPTT 查询获取路径时，问题就出现了，如下所示:

SELECT p.name 
FROM `geotree` AS node, `geotree` AS p 
WHERE node.lft BETWEEN p.lft AND p.rgt AND node.rgt='$value'
ORDER BY p.lft

完成需要超过 2.5 秒。有什么问题？我错过了任何索引吗？这种类型的数据结构的行数太多了吗？或者查询有错误？

注意:我注意到，如果从查询中删除“ORDER BY”，查询时间会减少到 0.05 秒。

Answer 1

您至少应该将查询转换为正确的JOIN，这必须带来一些性能:

http://sqlfiddle.com/#!9/b4d7f8/3

SELECT p.name 
FROM `geotree` AS node
INNER JOIN `geotree` AS p 
ON node.lft BETWEEN p.lft AND p.rgt 
WHERE node.rgt='$value'

由于您需要 node.rgt='$value' 作为过滤器，我将添加索引 KEY rgt (rgt)