嵌套集索引 &表现 [英] Nested Set indices & performance

问题描述

我在理解在嵌套集模型上使用什么索引时遇到了一些麻烦.查询是:

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_idGROUP BY node.idORDER BY `node`.`lft` ASC

这个查询需要大约 350 毫秒，categories 中有大约 5000 行.解释给出了这个:

<前>1 SIMPLE filebank_categories ref fk_filebank_categories_categories1,filebank_id filebank_id 5 const 474 使用 where;使用临时；使用文件排序1 简单节点 eq_ref PRIMARY,lft,category,cat,lft,rgt,system,id,lft,system PRIMARY 4 filebank_categories.category_id 11 SIMPLE parent ref lft,category,system system 5 node.system_id 50 使用 where1 SIMPLE category_names eq_ref PRIMARY,fk_categories_names_categories1 PRIMARY 8 node.id,const 1 使用 where

表结构:

CREATE TABLE `categories` (`id` int(11) NOT NULL AUTO_INCREMENT,`system_id` int(11) 默认为空，`lft` int(11) 默认为空，`rgt` int(11) 默认为空，主键(`id`)，KEY `lft,category` (`lft`,`id`),KEY `cat,lft,rgt` (`id`,`lft`,`rgt`),KEY`系统`(`system_id`)，约束`categories_ibfk_1`外键(`system_id`)引用`systems`(`id`)) ENGINE=InnoDB AUTO_INCREMENT=11519 DEFAULT CHARSET=utf8;

任何想法如何改进?甚至有可能吗?我在数据库优化方面的经验不是很丰富，所以我真的不知道在这里使用什么索引(以及为什么).

谢谢.

解决方案

可以尝试移动WHERE子句加入，如

SELECT ...INNER JOIN `categories` AS `node` ON(node.system_id=parent.system_id ANDnode.lft BETWEEN parent.lft AND parent.rgt)

并将索引细化为:

CREATE TABLE `categories` (...KEY `system_id,lft,rgt` (`system_id`,`lft`,`rgt`),...);

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

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

Table structure:

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

Thanks.

解决方案

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
)

And refine the index as :

CREATE TABLE `categories` (
  ...
  KEY `system_id,lft,rgt` (`system_id`,`lft`,`rgt`),
  ...
);

这篇关于嵌套集索引 &amp;amp;表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！