嵌套集索引表现 [英] Nested Set indices & performance
本文介绍了嵌套集索引表现的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在理解在嵌套集模型上使用哪些索引时遇到了一些麻烦.查询是:
I'm having some troubles understanding what indices to use on a Nested-Set model. The query is:
SELECT `node`.`id`,(COUNT(parent.id) - 1) AS `depth`,`name` FROM `categories` AS `parent`
INNER JOIN `categories` AS `node` ON (`node`.`lft` BETWEEN parent.lft AND parent.rgt)
INNER JOIN `filebank_categories` ON (`node`.`id` = `filebank_categories`.`category_id` AND `filebank_categories`.`filebank_id` = 136)
INNER JOIN `categories_names` ON (`categories_names`.`category_id` = `node`.`id` AND `categories_names`.`language_id` = 1)
WHERE `node`.`system_id` = parent.system_id
GROUP BY node.id
ORDER BY `node`.`lft` ASC
此查询需要约350ms的时间,而在categories
中有〜5000行.说明:
This query takes ~350ms with ~5000 rows in categories
. EXPLAIN gives this:
1 SIMPLE filebank_categories ref fk_filebank_categories_categories1,filebank_id filebank_id 5 const 474 Using where; Using temporary; Using filesort
1 SIMPLE node eq_ref PRIMARY,lft,category,cat,lft,rgt,system,id,lft,system PRIMARY 4 filebank_categories.category_id 1
1 SIMPLE parent ref lft,category,system system 5 node.system_id 50 Using where
1 SIMPLE categories_names eq_ref PRIMARY,fk_categories_names_categories1 PRIMARY 8 node.id,const 1 Using where
表结构:
CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`system_id` int(11) DEFAULT NULL,
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `lft,category` (`lft`,`id`),
KEY `cat,lft,rgt` (`id`,`lft`,`rgt`),
KEY `system` (`system_id`),
CONSTRAINT `categories_ibfk_1` FOREIGN KEY (`system_id`) REFERENCES `systems` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11519 DEFAULT CHARSET=utf8;
有什么想法可以改善这一点吗?可能吗?我对数据库优化的经验不是很丰富,所以我无法真正弄清楚在这里使用什么索引(以及为什么使用).
Any ideas how to improve this? Is it even possible? I'm not very experienced in database-optimization so I can't really figure out what indices to use here (and why).
谢谢.
推荐答案
您可以尝试移动WHERE clause
进行加入,例如
You can try to move the WHERE clause
to join, like
SELECT ...
INNER JOIN `categories` AS `node` ON
(
node.system_id=parent.system_id AND
node.lft BETWEEN parent.lft AND parent.rgt
)
并将索引优化为:
CREATE TABLE `categories` (
...
KEY `system_id,lft,rgt` (`system_id`,`lft`,`rgt`),
...
);
这篇关于嵌套集索引表现的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文