MySQL:嵌套集很慢? [英] MySQL: nested set is slow?
问题描述
我有一个看起来像这样的表:
I have a table that looks like this:
类别
- category_id
- 名称
- category_seo_friendly_url
- left_id
- right_id
当我运行这样的查询时， 大约需要1秒钟 :
When I run a query like this, it take almost 1 second:
SELECT node.category_id AS node_category_id,
node.category_seo_friendly_url,
node.name,
( COUNT(parent.category_id) - ( sub_tree.depth + 1 ) ) AS depth
FROM category AS node,
category AS parent,
category AS sub_parent,
(SELECT node.category_id,
( COUNT(parent.category_id) - 1 ) AS depth
FROM category AS node,
category AS parent
WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
AND node.category_id = 2
GROUP BY node.category_id
ORDER BY node.left_id)AS sub_tree
WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
AND node.left_id BETWEEN sub_parent.left_id AND sub_parent.right_id
AND sub_parent.category_id = sub_tree.category_id
GROUP BY node.category_id
HAVING depth > 0
AND depth <= 1
ORDER BY node.name ASC
当我执行EXPLAIN时，得到以下信息:
When I do an EXPLAIN, I get the following:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1 Using temporary; Using filesort
1 PRIMARY sub_parent const PRIMARY,category_id,left_id,right_id PRIMARY 4 const 1
1 PRIMARY node ALL left_id NULL NULL NULL 748 Using where
1 PRIMARY parent ALL left_id,right_id NULL NULL NULL 748 Range checked for each record (index map: 0x30)
2 DERIVED node const PRIMARY,category_id,left_id PRIMARY 4 1
2 DERIVED parent range left_id,right_id left_id 5 NULL 17 Using where
知道发生了什么吗? 我无法承受将近1秒钟的执行时间.
Any idea what's going on? I can't afford this near 1 second execution time.
更新:
-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 16, 2011 at 10:58 PM
-- Server version: 5.0.91
-- PHP Version: 5.2.6
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
--
-- Database: `foobar`
--
-- --------------------------------------------------------
--
-- Table structure for table `category`
--
CREATE TABLE IF NOT EXISTS `category`
(
`category_id` INT(11) NOT NULL AUTO_INCREMENT,
`name` CHAR(255) DEFAULT NULL,
`category_seo_friendly_url` CHAR(255) DEFAULT NULL,
`left_id` INT(11) DEFAULT '1',
`right_id` INT(11) DEFAULT '2',
PRIMARY KEY (`category_id`),
UNIQUE KEY `seo_friendly_url_UNIQUE` (`category_seo_friendly_url`),
KEY `category_id` (`category_id`),
KEY `left_id` (`left_id`),
KEY `right_id` (`right_id`)
)
ENGINE=MyISAM
DEFAULT CHARSET=latin1
AUTO_INCREMENT=765;
推荐答案
IME，MySQL在优化子查询方面做得不好-特别是它似乎不管理推式谓词.
IME, MySQL does not do well at optimizing sub-queries - particularly it doesn't seem to manage push-predicates.
对于查询实际上打算返回的内容，我有些困惑-特别是子父母"
I am a little bit conmfused about what the query is actually intended to return - particularly the 'sub-parent'
通过将left_id和right_id放在单个索引中，您将获得一些改进.
You'd get some improvement by putting left_id and right_id into a single index.
虽然您也可以通过将查询展开到存储过程中来获得一些改进，但是每次您似乎遍历几乎整个数据集时，更好的解决方案是对树深度进行规范化并将其存储为每个节点.实际上，您似乎仅在外部查询中就至少遍历了两次.
While you'll also get some improvement by unrolling the query into a stored procedure, given that you seem to be traversing almost the entire dataset each time a better solution would be to denormalise the tree depth and store it as an attribute for each node. Indeed you seem to be traversing it at least twice in the outer query alone.
但是我注意到在查询末尾:
However I notice that at the end of the query:
HAVING depth > 0
AND depth <= 1
当然与
HAVING depth=1
然后，这提供了一种非常不同的优化查询的方法(首先获取right = left + 1的所有节点以查找没有子节点的节点，然后检查类别ID的方法).
Which then provides a very different way of optimizing the query (start by getting all the nodes where right=left+1 to find the nodes with no children and work up the way to check the category id).
这篇关于MySQL:嵌套集很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
