主页 / dba / 问题 / 317041
Accepted
user1031947
Asked: 2022-09-18 16:10:08 +0800 CST

有助于优化 InnoDB 中嵌套集查询的索引？

  • 772

我使用嵌套集表示 MySql 中的树。

树有几种不同的“类型”，每个用户可以拥有每种类型的一棵树。

该表如下所示：

CREATE TABLE folders (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    typeId TINYINT(1) UNSIGNED NOT NULL,
    userId INT UNSIGNED NOT NULL,
    lft INT NOT NULL,
    rgt INT NOT NULL,
    title VARCHAR(50) DEFAULT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(typeId) REFERENCES folderTypes(id),
    FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB;

例如，在树中某处插入节点的查询将如下所示：

START TRANSACTION;

# given id, lock relevant rows

SELECT          f2.id 
FROM            folders f1, folders f2 
WHERE           f1.id = ? and 
                f1.typeId = f2.typeId AND
                f1.userId = f2.userId
FOR UPDATE;

# given id, select typeId, userId & lft

SELECT          @typeId := typeId, @userId := userId, @lft := lft 
FROM            folders 
WHERE           id = ?;

# update tree

UPDATE          folders 
SET             rgt = rgt + 2 
WHERE           rgt > @lft and
                typeId = @typeId AND
                userId = @userId;

UPDATE          folders
SET             lft = lft + 2
WHERE           lft > @lft and
                typeId = @typeId AND        
                userId = @userId;

# insert node

INSERT INTO     folders (typeId, userId, title, lft, rgt)
VALUES          (@typeId, @userId, 'new', (@lft + 1), (@lft + 2));  

COMMIT;

这工作正常。但是我注意到，随着更多的树和行被添加，这个查询，以及其他类似的嵌套集中节点操作的查询，都不像以前那么快了。

我确实读过这篇文章（https://explainextended.com/2009/09/29/adjacency-list-vs-nested-sets-mysql/）但是我需要使用 InnoDB，所以看起来空间索引不适用于我。（而且即使我可以为此切换到 MyISAM，重构所有查询现在也不是一个真正的选择。）

我想知道：是否有任何其他索引可以/应该添加到这个 InnoDB 表中来帮助 MySql？

mysql innodb

1 个回答

  1. Best Answer
    CREATE INDEX folders_type_user_rgt ON folders(typeId, userId, rgt);
CREATE INDEX folders_type_user_lft ON folders(typeId, userId, lft);

    typeId您已经在and上有单列索引userId；它们是由您的外键自动创建的。

    这些复合索引应该比单列索引更好地帮助您的 UPDATE 语句。

    您可以使用 EXPLAIN 验证是否使用了索引。

    • 2

相关问题