嵌套集模型优化

Question

我有一个网站，其中包含约100,000页(200-300并发用户)。在mysql InnoDB表页中，每个页面都有自己的记录。

page_id
page_parent
left_id
right_id
page_subject
page_children
page_depth
....

如您所见，我使用嵌套集模型来显示页面的层次结构，等等。除了INSERT、UPDATE和DELETE语句的性能之外，一切似乎都很好。页面从一个家长移动到另一个家长是非常缓慢的，有时需要30秒(!!)

 CREATE PROCEDURE `PAGE_MOVE`(IN `pageSrc` INT, IN `pageDst` INT)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE srcLeftId INT;
    DECLARE srcRightId INT;
    DECLARE dstLeftId INT;
    DECLARE dstRightId INT;
    DECLARE width INT;

    SELECT left_id, right_id, right_id - left_id + 1
    INTO srcLeftId, srcRightId, width
    FROM page
    WHERE page_id = pageSrc;

    IF pageDst > 0 THEN
        SELECT left_id, right_id
        INTO dstLeftId, dstRightId
        FROM page
        WHERE page_id = pageDst;
    ELSE
        SELECT MAX(right_id) INTO dstLeftId FROM page WHERE page_parent = 0;
        SET dstRightId = dstLeftId + 1;
    END IF;

    IF dstLeftId > 0 THEN

        UPDATE page SET page_children = page_children - (width / 2)
        WHERE left_id < srcLeftId AND right_id > srcRightId;

        UPDATE page SET page_children = page_children + (width / 2)
        WHERE left_id <= dstLeftId AND right_id >= dstRightId;

        /**
         * Set nagative values to left_id and right_id (temporary)
         */
        UPDATE page
        SET left_id = -left_id, right_id = -right_id
        WHERE left_id >= srcLeftId AND right_id <= srcRightId;

        UPDATE page SET left_id = left_id - width WHERE left_id > srcLeftId;
        UPDATE page SET right_id = right_id - width WHERE right_id > srcRightId;

        UPDATE page SET left_id = left_id + width
        WHERE left_id >= IF(dstRightId > srcRightId, dstRightId - width, dstRightId);

        UPDATE page SET right_id = right_id + width
        WHERE right_id >= IF(dstRightId > srcRightId, dstRightId - width, dstRightId);

        SET @diff = IF(dstRightId > srcRightId, dstRightId - srcRightId -1, dstRightId - srcRightId - 1 + width);

        UPDATE page
        SET left_id = -left_id + @diff,
             right_id = -right_id + @diff
        WHERE left_id <= -srcLeftId AND right_id >= -srcRightId;
        /**
         * Set parent_id and page_depth
         */
        UPDATE page SET page_parent = pageDst, page_depth = GET_PAGE_DEPTH(page_id) WHERE page_id = pageSrc;

        /**
         * Update page_depth in children's nodes
         */
        IF width > 2 THEN

            SELECT left_id, right_id
            INTO srcLeftId, srcRightId
            FROM page
            WHERE page_id = pageSrc;

            UPDATE page
            SET page_depth = GET_PAGE_DEPTH(page_id)
            WHERE left_id >= srcLeftId AND right_id <= srcRightId;      
        END IF;


    END IF;
END

我怎样才能优化这个程序？或者问题应该是:使用嵌套集模型的替代方案是什么？

函数GET_PAGE_DEPTH()：

CREATE FUNCTION `GET_PAGE_DEPTH`(`pageId` MEDIUMINT UNSIGNED)
    RETURNS smallint(6)
    LANGUAGE SQL
    DETERMINISTIC
    READS SQL DATA
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    RETURN (
        SELECT COUNT(*) -1 AS depth
        FROM page AS parent
        INNER JOIN page AS node ON node.page_id = pageId
        WHERE node.left_id BETWEEN parent.left_id AND parent.right_id
    );
END

硬件:四核Q6600 (4x 2.40+ GHz)，4GB内存

谢谢你的建议！

编辑：

表页如下所示：

CREATE TABLE `page` (
  `page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `page_parent` int(10) unsigned DEFAULT NULL,
  `left_id` int(11) NOT NULL,
  `right_id` int(11) NOT NULL,
  `page_module` smallint(5) unsigned NOT NULL,
  `page_connector` smallint(5) unsigned NOT NULL,
  `page_subject` varchar(255) NOT NULL,
  `page_title` varchar(255) DEFAULT NULL,
  `page_path` varchar(255) NOT NULL,
  `page_text` int(10) unsigned DEFAULT NULL,
  `page_children` mediumint(8) unsigned NOT NULL,
  `page_depth` smallint(5) unsigned NOT NULL DEFAULT '0',
  `page_content` tinyint(3) unsigned NOT NULL,
  `page_publish` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `page_published` datetime DEFAULT NULL,
  `page_unpublished` datetime DEFAULT NULL,
  `page_time` int(10) unsigned NOT NULL,
  `page_edit_time` int(10) unsigned NOT NULL,
  `page_delete` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_richtext` tinyint(1) unsigned NOT NULL DEFAULT '0',
  `page_cache` tinyint(1) unsigned NOT NULL DEFAULT '1',
  `page_template` varchar(255) NOT NULL,
  PRIMARY KEY (`page_id`),
  KEY `page_parent` (`page_parent`),
  KEY `left_id` (`left_id`),
  KEY `right_id` (`right_id`),
  KEY `page_depth` (`page_depth`),
  KEY `page_path` (`page_path`),
  KEY `page_connector` (`page_connector`),
  KEY `page_text` (`page_text`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Answer 1

作为第一步，请确保为

page_id

page_parent

left_id，right_id

right_id

编辑

从页面中选择MAX(right_id)到dstLeftId，其中page_parent = 0；

上面的查询可以通过使用和索引(page_parent，right_id)来加速。

除此之外，如果不对系统进行更全面的概述，并可能修改系统的工作方式，我就无法给出更多的建议。