logo标签列表

MySQL和嵌套集：缓慢的JOIN（未使用索引）

mysqlsqlperformanceindexingnested-sets
7

I have two tables:

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;

定位：

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";

更新的解释：

+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+
| 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) |
+----+-------------+-------+--------+-----------------------------------------+-----------------------------+---------+---------------------------------+-------+----------+-------------------------------------------------+

非常感谢您的帮助。

你尝试过将lft和rft放在不同的索引中吗？（一个用于lft，一个用于rft） - cairnz
根据您的更新更新答案。 - cairnz
3个回答
2
啊，我突然想到了。
由于你要求表中的所有内容，mysql决定使用全表扫描，因为它认为这样更有效率。为了获得一些关键用途，请添加一些过滤器，以限制在任何情况下查找所有表中的每一行。
更新答案：
你的第二个查询没有意义。你左连接到lca，但是你在它里面有一个过滤器，这本身就否定了左连接。而且你正在寻找查询的最后一步数据，这意味着你将不得不查看lt、lc和lca的所有数据才能找到你的数据。另外，你在locations上没有最左边列'type'的索引，所以仍然需要全表扫描才能找到你的数据。
如果你有一些样本数据和你试图实现的示例，或许会更容易帮助你。
谢谢，查询速度快了很多，但仍然需要太长时间。我已经更新了我的问题，并提供了新的查询和解释。 - Igor Pavelek
抱歉，这可能是一个愚蠢的问题，但是您所说的添加过滤器是什么意思？ - Igor Pavelek
1
你的查询需要处理lt表，通过连接lc表和lca表进行联接。你的过滤器在lca表中，是查询的最后一步。然后它可以扫描lca表以查找匹配type != "Unknown"的行，但为了达到这个目的，它已经读取了lt和lc表，如果这有意义的话。此外，你对该表进行了左连接，这意味着你可能会有NULL记录，但你在WHERE子句中过滤它，删除所有NULL记录（相当于内连接）。也许你的过滤器应该在lc表或lt表上。如果你在lt表上进行过滤，那么在lc和lca表中需要扫描的行数就会减少。 - cairnz
你的意思是这样的吗... WHERE lt.category != "Unknown"？我们在localities表中有大约12,000条记录，在locatings表中有大约4,000条记录，而查询需要半分钟才能处理完。这不太好，对吧？ - Igor Pavelek
这听起来更像是它。如果现在在lt.category上有一个索引，应该会加快搜索速度。然后更可能使用lc和lca表上的以下索引。当您添加这样的过滤器和索引时，EXPLAIN会说什么。请记住，由于它不是索引中最左边的列，因此无法仅使用KEY localizable_and_category_index (localizable_type,localizable_id,category) 来搜索类别。 - cairnz
我已经更新了问题，并提供了实际的查询和解释。看起来使用了索引，但我们得到了相同的处理时间。 - Igor Pavelek
2
同时将“DISTINCT”替换为“GROUP BY”。 - Francis Avila
@FrancisAvila 用 GROUP BY 替换 DISTINCT 没有任何区别。 - Igor Pavelek
0

看起来你想要单个结果的父级。

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

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

如果“localities”表有一个几何列，那么我不能从“locating”中找到一个位置，然后使用单个过滤器在一个表中选择：parent.lft <= row.left AND parent.rgt >= row.rgt 吗？

已更新

在这个回答https://dev59.com/H0rSa4cB1Zd3GeqPVlgu#1743952中，有一个来自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
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接