MySQL 和 MySQL嵌套集：慢连接（不使用索引）

Question

我有两个表：

localities:

CREATE TABLE `localities` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `type` varchar(30) NOT NULL,
  `parent_id` int(11) DEFAULT NULL,
  `lft` int(11) DEFAULT NULL,
  `rgt` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_localities_on_parent_id_and_type` (`parent_id`,`type`),
  KEY `index_localities_on_name` (`name`),
  KEY `index_localities_on_lft_and_rgt` (`lft`,`rgt`)
) ENGINE=InnoDB;

locings:

CREATE TABLE `locatings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `localizable_id` int(11) DEFAULT NULL,
  `localizable_type` varchar(255) DEFAULT NULL,
  `locality_id` int(11) NOT NULL,
  `category` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_locatings_on_locality_id` (`locality_id`),
  KEY `localizable_and_category_index` (`localizable_type`,`localizable_id`,`category`),
  KEY `index_locatings_on_category` (`category`)
) ENGINE=InnoDB;

localities 表是作为嵌套集实现的。

现在，当用户属于某个地点（通过某些定位）时，他也属于其所有祖先（更高级别的地点）。我需要一个查询，将所有用户所属的所有位置选择到视图中。

这是我的尝试：

select distinct lca.*, lt.localizable_type, lt.localizable_id 
from locatings lt
join localities lc on lc.id = lt.locality_id
left join localities lca on (lca.lft <= lc.lft and lca.rgt >= lc.rgt)

这里的问题是执行需要太多时间。

我咨询了 EXPLAIN:

+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
| id | select_type | table | type   | possible_keys                   | key     | key_len | ref                              | rows  | filtered | Extra           |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
|  1 | SIMPLE      | lt    | ALL    | index_locatings_on_locality_id  | NULL    | NULL    | NULL                             |  4926 |   100.00 | Using temporary |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY                         | PRIMARY | 4       | bzzik_development.lt.locality_id |     1 |   100.00 |                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt | NULL    | NULL    | NULL                             | 11439 |   100.00 |                 |
+----+-------------+-------+--------+---------------------------------+---------+---------+----------------------------------+-------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

最后一个连接显然没有像我期望的那样使用 lft, rgt 索引。我很绝望。

更新： 按照 @cairnz 建议添加条件后，查询仍然需要太多时间来处理。

更新 2：列名而不是星号

更新的查询：

SELECT DISTINCT lca.id, lt.`localizable_id`, lt.`localizable_type` 
FROM locatings lt FORCE INDEX(index_locatings_on_category)
JOIN localities lc
    ON lc.id = lt.locality_id
INNER JOIN localities lca
    ON lca.lft <= lc.lft AND lca.rgt >= lc.rgt
WHERE lt.`category` != "Unknown";

更新的 EXAPLAIN：

+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
| id | select_type | table | type   | possible_keys                           | key                         | key_len | ref                             | rows  | filtered | Extra                                           |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
|  1 | SIMPLE      | lt    | range  | index_locatings_on_category             | index_locatings_on_category | 153     | NULL                            |  2545 |   100.00 | Using where; Using temporary                    |
|  1 | SIMPLE      | lc    | eq_ref | PRIMARY,index_localities_on_lft_and_rgt | PRIMARY                     | 4       | bzzik_production.lt.locality_id |     1 |   100.00 |                                                 |
|  1 | SIMPLE      | lca   | ALL    | index_localities_on_lft_and_rgt         | NULL                        | NULL    | NULL                            | 11570 |   100.00 | Range checked for each record (index map: 0x10) |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+

感谢任何帮助。

Answer 1

啊，我刚刚想到的。

由于您要求表中的所有内容，因此 mysql 决定使用全表扫描，因为它认为这样更有效。

为了获得一些关键用法，请添加一些过滤器以限制查找所有表中的每一行。

更新答案：

您的第二个查询没有意义。您被左连接到 lca，但其中有一个过滤器，这会自行否定左连接。此外，您还要在查询的最后一步中查找数据，这意味着您必须查看所有 lt、lc 和 lca 才能找到数据。此外，您在位置上没有最左侧列“类型”的索引，因此您仍然需要全表扫描来查找数据。

如果您有一些示例数据和您想要实现的目标的示例，那么可能会更容易提供帮助。

Answer 2

尝试尝试强制索引 - http://dev.mysql。 com/doc/refman/5.1/en/index-hints.html，也许这只是优化器问题。

Answer 3

看来您想要单个结果的父母。

根据在 SQL 中定义嵌套集的人 Joe Celko 的说法，位于 http://www .ibase.ru/devinfo/DBMSTrees/sqltrees.html “此模型是显示零件爆炸的自然方式，因为最终组件是由物理嵌套组件组成，这些组件分解为单独的零件。”

换句话说，嵌套集用于将子项有效地过滤到单个集合内的任意数量的独立级别。您有两个表，但我不知道集合“locations”的属性在哪里不能被反规范化为“localities”？

如果地点表有一个几何列，我是否无法从“定位”中找到一个地点，然后使用单个过滤器在一个表上进行选择：parent.lft <= row.left AND父.rgt >= 行.rgt ？

更新

在此答案https://stackoverflow.com/a/1743952/3018894中，有一个来自http://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/ 其中以下示例将所有祖先获取到任意深度100000：

SELECT  hp.id, hp.parent, hp.lft, hp.rgt, hp.data
FROM    (
    SELECT  @r AS _id,
            @level := @level + 1 AS level,
            (
            SELECT  @r := NULLIF(parent, 0)
            FROM    t_hierarchy hn
            WHERE   id = _id
            )
    FROM    (
            SELECT  @r := 1000000,
                    @level := 0
            ) vars,
            t_hierarchy hc
    WHERE   @r IS NOT NULL
    ) hc
JOIN    t_hierarchy hp
ON      hp.id = hc._id
ORDER BY
    level DESC