我有一个带有嵌套集模型的类别表。每行应包含其子类别计数以及其中的文章数量,如果没有,则包含"0"。
我已经搜索并找到了两种可能的解决方案,但它们都不起作用:
MySQL 和嵌套集:慢速 JOIN(不使用索引)
为什么 MySQL 不使用任何这些可能的键?
创建表类别:
CREATE TABLE `categories` (
`GROUP_ID` varchar(255) CHARACTER SET utf8 NOT NULL,
`GROUP_NAME` varchar(255) CHARACTER SET utf8 NOT NULL,
`PARENT_ID` varchar(255) CHARACTER SET utf8 NOT NULL,
`TYPE` enum('root','node','leaf') CHARACTER SET utf8 NOT NULL DEFAULT 'node',
`LEVEL` tinyint(2) NOT NULL DEFAULT '0',
`GROUP_ORDER` int(11) NOT NULL,
`GROUP_DESCRIPTION` text CHARACTER SET utf8 NOT NULL,
`total_articles` int(11) unsigned NOT NULL DEFAULT '0',
`total_cats` int(11) unsigned NOT NULL DEFAULT '0',
`lft` smallint(5) unsigned NOT NULL DEFAULT '0',
`rgt` smallint(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`GROUP_ID`),
KEY `PARENT_ID` (`PARENT_ID`),
KEY `lft` (`lft`),
KEY `rgt` (`rgt`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
total_cats
是行树中子类别的数量。
以下查询将完全按照我的意愿执行:所有子类别和文章计数。但它非常慢。对 ~5000 个类别和 ~40000 篇文章执行需要 80 秒以上。total_articles
的计算已经由另一个脚本完成。(如果没有任何文章,则所有行都应保存0
total_articles
)
查询:
SELECT a.GROUP_ID,a.PARENT_ID,COUNT(b.GROUP_ID) as total_cats,(
SELECT SUM(c.total_articles)
FROM categories c
WHERE c.PARENT_ID = a.GROUP_ID) as total_articles
FROM categories as b
INNER JOIN categories as a
ON a.lft < b.lft AND a.rgt > b.rgt
GROUP BY a.GROUP_ID
它的结果是这样的:
+-------------------------------------------+-------------------------------------+------------+----------------+
| GROUP_ID | PARENT_ID | total_cats | total_articles |
+-------------------------------------------+-------------------------------------+------------+----------------+
| 69_69_1 | 69_69_0 | 4252 | 0 |
| 69_69_Abfall__Wertstoffsammler___zubehoer | 69_69_NWEAB290h001 | 5 | 20 |
| 69_69_Abisolierzangen | 69_69_NWAAA458h001 | 4 | 56 |
| 69_69_Abzieher_2 | 69_69_NWAAB944h001 | 23 | 476 |
| 69_69_Abziehvorrichtung | 69_69_Abzieher_2 | 3 | 18 |
| 69_69_Aexte | 69_69_NWEAA615h001 | 6 | 45 |
| 69_69_Alarmgeraete_Melder | 69_69_Sicherungstechnik__Heimschutz | 3 | 4 |
| 69_69_Allgemeiner_Industriebedarf | 69_69_Industrieausruestung | 8 | 21 |
| 69_69_Allgemeines_Schweisszubehoer | 69_69_NWEAB113h001 | 27 | 97 |
| 69_69_Anker__Befestigungstechnik__1 | 69_69_Befestigungstechnik | 5 | 163 |
解释它是否有帮助:
+----+--------------------+-------+------+---------------+-----------+---------+------+------+------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+------+---------------+-----------+---------+------+------+------------------------------------------------+
| 1 | PRIMARY | b | ALL | lft,rgt | NULL | NULL | NULL | 4253 | Using temporary; Using filesort |
| 1 | PRIMARY | a | ALL | lft,rgt | NULL | NULL | NULL | 4253 | Range checked for each record (index map: 0xC) |
| 2 | DEPENDENT SUBQUERY | c | ref | PARENT_ID | PARENT_ID | 767 | func | 7 | NULL |
+----+--------------------+-------+------+---------------+-----------+---------+------+------+------------------------------------------------+
如您所见,它不使用索引。如果我FORCE INDEX (lft,rgt)
放在JOIN
旁边,查询就会执行,但没有任何变化。还尝试在 lft 和右侧两列上添加索引:
ALTER TABLE `categories` ADD INDEX `nestedset` (`lft`, `rgt`);
但这根本无济于事。查询仍然很慢。
有趣的是:如果类别表只填充少量行(例如 260),则查询速度非常快。但如果达到1000+,它会变得越来越慢。
具有 ~4000 个类别的示例数据:http://pastebin.com/BsViwFM5 这是一个大文件!
感谢您的任何帮助和提示!
对此的解释是什么样的?
SELECT a.GROUP_ID
, a.PARENT_ID
, COUNT(b.GROUP_ID) total_cats
, c.total_articles
FROM categories b
JOIN categories a
ON a.lft < b.lft
AND a.rgt > b.rgt
JOIN
( SELECT parent_id
, SUM(total_articles) total_articles
FROM categories
GROUP
BY parent_id
) c
ON c.parent_id = a.GROUP_ID
GROUP
BY a.GROUP_ID
左右树是一种可爱的"教科书"技术。 但是,正如您所发现的,它不适用于"现实世界"。
EXPLAIN
显示它扫描所有b
,然后对于每个这样的行,它正在扫描所有a
。 这就是 Order(N^2) -- 5000*5000 = 2500 万次操作。
实际上,这个相对较新的操作(Range checked for each record (index map: 0xC)
)意味着它并没有那么糟糕。
优化器在查找"中介性"方面确实做得再好不过了,因为缺少一点信息:范围是否重叠。
通过切换到分层架构并在应用代码或存储例程中"遍历"树,可以更好地完成任务。
使用 MariaDB 10.2 或 MySQL 8.0,您可以在单个但复杂的查询中编写"递归 CTE"来遍历树。