user1031947's questions

我使用嵌套集表示 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？

在运行其中包含布尔全文搜索的查询时，我遇到了间歇性的 oom 错误。

我在 AWS Aurora (t2.medium) 上。

这是一个示例查询，考虑到表“sets”在列（savename、title）上有一个全文索引，并且其中有大约 200 万行。

        select          sets.id, sets.title,
                        drafts.draftId as draftId,
                        folderSets.folderId as folderId,
                        concat_ws(' ', savedBy.firstname, savedBy.lastname) as savedBy,
                        match(sets.savename, sets.title) against ("+s*" in boolean mode) as relevance
        from            sets as sets
        join            folderSets as folderSets on folderSets.setId = sets.id
        join            folders as folders on folders.id = folderSets.folderId 
        left join       draftSets as drafts on drafts.originalId = sets.id and drafts.userId = ?
        left join       users as savedBy on savedBy.id = folderSets.userId
        where           (folders.userId = ?)
                        and match(sets.savename, sets.title) against ("+s*" in boolean mode)
        order by        relevance
        limit           0, 25;

此查询大约需要 5 秒才能运行。如果我敲了几次，查询很可能会失败并出现 OOM。

如果我将布尔全文搜索短语从“+s*”更改为“+sam*”——因此通配符之前有更多字符——相同的查询在大约 0.15 秒内执行，没有任何问题。

如果我从查询中删除布尔搜索 altogehter，查询运行得更快，没有任何问题。

因此，查询的布尔全文部分似乎发生了一些事情，达到了 mysql 限制。

以下是 innodb 全文系统变量：

ft_boolean_syntax................... + -><()~*:""&|
ft_max_word_len..................... 84
ft_min_word_len..................... 1
ft_query_expansion_limit............ 20
ft_stopword_file.................... /dev/null
innodb_ft_aux_table................. 
innodb_ft_cache_size................ 8000000
innodb_ft_enable_diag_print......... OFF
innodb_ft_enable_stopword........... OFF
innodb_ft_max_token_size............ 84
innodb_ft_min_token_size............ 1
innodb_ft_num_word_optimize......... 2000
innodb_ft_result_cache_limit........ 2000000000
innodb_ft_server_stopword_table..... 
innodb_ft_sort_pll_degree........... 2
innodb_ft_total_cache_size.......... 640000000
innodb_ft_user_stopword_table.......

有没有人对可以进行任何调整以减少与布尔全文相关的 OOM 的可能性有任何建议？我不清楚这是可以/应该在 sql 查询级别/mysql innodb 设置级别/或者如果我处于需要增加 aws rds 实例大小的情况。

在此先感谢您的帮助！

我有以下带有用于搜索诱饵的子表的基表。（这是在与 mysql 5.7 兼容的 aws aurora 上）

tblA (id, userId, title, ...)
tblB (id, userId, title, ...)

tblA_searchBait (id->tblA.id, keywords)
tblB_searchBait (id->tblB.id, keywords)

我需要进行布尔全文搜索以在单个查询中获得组合结果。目前我使用以下内容：

select          base.title as title,
                match(search.keywords) against (? in boolean mode) as relevance
from            tblA as base
                join tblA_searchBait as search on search.id = base.id           
where           base.userId = ? and 
                match(search.keywords) against (? in boolean mode)

union all

select          base.title as title,
                match(search.keywords) against (? in boolean mode) as relevance 
from            tblB as base
                join tblB_searchBait as search on search.id = base.id           
where           base.userId = ? and 
                match(search.keywords) against (? in boolean mode)

order by        relevance desc              
limit ?, ?;

这行得通，但我想知道：有什么方法可以提高查询的性能？（请注意，我无法更改表的架构。）。具体来说，我想知道这样的事情是否有任何区别：

  select            base.title as title,
                    match(search.keywords) against (? in boolean mode) as relevance
    from            tblA as base
                    join tblA_searchBait as search on search.id = base.id   
                    and base.userId = ?     
    where           match(search.keywords) against (? in boolean mode)
    
    union all
    
    select          base.title as title,
                    match(search.keywords) against (? in boolean mode) as relevance 
    from            tblB as base
                    join tblB_searchBait as search on search.id = base.id 
                    and base.userId = ?         
    where           match(search.keywords) against (? in boolean mode)
    
    order by        relevance desc              
    limit ?, ?;

祝大家新年快乐。我希望在以下情况下获得一些一般性指导......

我有一个已经运行了大约 10 年的应用程序。数据存储在 mysql 中（现在在 AWS Aurora 上）。

一些处于一对多关系的表开始有更多的行：

Records (~1.4million rows) 
        |
        V
    (1 to many)
        |
        V
SubRecords (~10million rows)
        |
        V
    (1 to many)
        |
        V
SubSubRecords (~22million rows)

这些行中存储的实际数据并不多（即 subSubRecords 总共大约 5gb），而且我运行的查询非常简单，使用没有连接的索引键。例如...

SELECT ... FROM Records WHERE id = ?;
SELECT ... FROM SubRecords WHERE recordId = ?;
SELECT ... FROM SubSubRecords WHERE subRecordId = ?;

到目前为止，一切都继续保持高性能。

但是，我开始担心这种设计会随着时间的推移而保持不变。虽然在 SubSubRecords 中达到 2200 万行需要 10 年，但现在数据库的增长速度要快得多。看到该表在接下来的 5 年内攀升至 1 亿行，我不会感到惊讶，这感觉很多。而且我不确定它会在什么时候成为问题。

我意识到这是一个相当广泛的问题，并且取决于情况。但在这些情况下，一般推荐哪些类型的解决方案？

• 设置分区？（这些表使用外键来强制完整性，我的理解是这与分区不兼容。）

• 将 subRecords 和 subSubRecords 中的数据转换为 json 有效负载并将其直接存储在主记录表的 json 列中？（如果重要的话，数据量相同，但行数更少。）

• 移动到一个完全不同的数据库？（Mongo？我对此一无所知，但听说在某些情况下更擅长缩放。）

• 忽略它直到它成为一个问题？:D

欢迎那些遇到过类似问题的人提出任何建议/智慧的珍珠。在此先感谢您的帮助！

附录：

根据要求，这是上述表格的 CREATE TABLE 语法...

CREATE TABLE records (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    typeId TINYINT(1) UNSIGNED NOT NULL,
    userId INT UNSIGNED NOT NULL, 
    updated TIMESTAMP DEFAULT NOW() NOT NULL,
    savename VARCHAR(100) NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    FULLTEXT ftRecords(savename, title),
    PRIMARY KEY(id),
    FOREIGN KEY(typeId) REFERENCES recordTypes(id),
    FOREIGN KEY(userId) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    recordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    enabled TINYINT(1) DEFAULT 0 NOT NULL,
    title VARCHAR(100) NOT NULL,
    instructions TEXT NOT NULL,
    parameters VARCHAR(500) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(recordId) REFERENCES records(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;

CREATE TABLE subSubRecords (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    subRecordId INT UNSIGNED NOT NULL,
    thumbnailId INT UNSIGNED NULL,
    sortOrder SMALLINT NOT NULL,
    caption VARCHAR(200) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(subRecordId) REFERENCES subRecords(id) ON DELETE CASCADE,
    FOREIGN KEY(thumbnailId) REFERENCES thumbnails(id) ON DELETE SET NULL
) ENGINE=InnoDB CHARACTER SET=utf8;

我怀疑这是这些问题之一，不会有一个明确的答案。但我希望能深入了解什么通常被认为是最佳实践，无论如何大多数时候都是如此。所以，在此先感谢那些参与此事的人。

我有两种类型的文件夹用于存储记录。个人文件夹和团队文件夹。

我可以通过两种方式在数据库中表示这一点......

选项 1：两个独立且不相关的文件夹表。

personalFolders( id, userId, title )
teamFolders( id, teamId, title )
records( id, folderId, folderType, title )

选项 2：具有两个连接表的公共文件夹表

folders( id, title )
personalFolders( folderId->folders.id, userId )
teamFolders( folderId->folders.id, teamId )
records( id, folderId->folders.id, title )

我的问题是：一般来说，什么模式被认为是最佳实践？

（我的直觉告诉我选择选项 2，因为这样我就可以将外键约束保留在记录表中。但是，在某些情况下选择选项 1 可能更好吗？）