MySQL：嵌套集很慢？

我有一个看起来像这样的表：

category

• 类别_id
• name
• 类别 seo 友好 url
• left_id
• right_id

当我运行这样的查询时，大约需要1秒:

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，我得到以下信息：

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 秒的执行时间。

UPDATE:

-- 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 在优化子查询方面表现不佳 - 特别是它似乎无法管理推送谓词。

我对查询实际要返回的内容有点困惑 - 特别是“子父级”

通过将 left_id 和 right_id 放入单个索引中，您会得到一些改进。

虽然您还可以通过将查询展开到存储过程中来获得一些改进，但考虑到您似乎每次都会遍历几乎整个数据集，更好的解决方案是对树深度进行非规范化并将其存储为每个节点的属性。事实上，您似乎仅在外部查询中就至少遍历了两次。

但是我注意到在查询结束时：

HAVING depth > 0
   AND depth <= 1

这肯定是同一件事

HAVING depth=1

然后，它提供了一种非常不同的优化查询的方法（首先获取 right=left+1 的所有节点来查找没有子节点的节点，然后逐步检查类别 id）。