主页 / dba / 问题 / 338029
Accepted
O. Jones
Asked: 2024-03-25 23:10:11 +0800 CST

对于固定长度的行，COMPACT 格式是否比 DYNAMIC 更好？

  • 772

在 MySQL / MariaDB 中，我有一个带有固定长度行的表（没有 VARCHAR、TEXT 等）

CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

该表有数十兆行，并获取这种形式的生产查询

SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7

以及插入和DELETE FROM trigram WHERE id = 12345维护查询。索引适合表的查询模式。

该表是穷人的卦索引。（这个可怜的人无法升级到 postgreSQL 并使用其内置的 trigram 索引，叹息。）示例查询查找id其中包含“dba.stack”字符串的 s。content_column LIKE '%dba.stack%'它比建立三卦表要快得多。

编辑： “更好”是什么意思？更快、更可靠、生产中缓冲池刷新更少、非 DBA 用户的维护负担更少。

问题：我应该使用 ROW_FORMAT=COMPACT 定义这个固定长度行表吗？或者需要 DYNAMIC 吗？我注意到 COMPACT 占用的磁盘空间要少得多。

问题：还有其他建议或需要担心的性能问题吗？

我的用户（WordPress.org 软件用户）大部分使用 MariaDB 10.3+，但也有一些使用 MySQL 8，还有一些使用 MySQL 5.7-。我不需要支持 Antelope 或 MyISAM 遗留的东西。

另一个编辑：

我的IN()查询对表中包含 180K 行的测试数据集进行范围扫描。JOIN答案中建议的 UNION 表执行嵌套循环。范围扫描花费的时间更少。在 MariaDB 10.11、MySQL 8 和 MySQL 5.7 上正确。物有所值。看起来跳过扫描优化效果很好。

mysql

5 个回答

  1. 另一种选择是使用多个联接，以避免分组和计数，并利用在前几次联接之后满足条件的行越来越少的事实：

    SELECT t1.id FROM trigram t1
JOIN trigram t2 ON (t1.id=t2.id)
JOIN trigram t3 ON (t1.id=t3.id)
JOIN trigram t4 ON (t1.id=t4.id)
JOIN trigram t5 ON (t1.id=t5.id)
JOIN trigram t6 ON (t1.id=t6.id)
JOIN trigram t7 ON (t1.id=t7.id)
WHERE t1.trigram = 'dba' and t2.trigram ='ba.' 
  and t3.trigram = 'a.s' and t4.trigram ='.st' 
  and t5.trigram = 'sta' and t6.trigram ='tac' 
  and t7.trigram = 'ack'
    • 3
  2. Best Answer

    对于您显示的表，我认为紧凑或动态行格式没有任何显着差异。

    在现代 MySQL 中，动态行格式是默认的。它与 COMPACT 几乎相同，只是它改进了长 TEXT 和 BLOB 列的存储并允许更长的索引前缀长度。这些差异均与您显示的表格无关。

    您说前一种行格式会产生较小的表，但这可能只是巧合，因为当您将表从一种行格式更改为另一种行格式时，您可能实现了碎片整理。

    如果您OPTIMIZE TABLE使用动态行格式的表，我希望您会看到它进行碎片整理并变得更小。

    InnoDB 中没有任何东西可以为固定长度的行提供优势。行始终通过使用页面内的指针来定位。

    MyISAM 对于固定长度的行有一些优势，因为可以通过乘以行长度来定位行。但 InnoDB 中从未这样做过。

    • 3

  3. FIXEDvsDYNAMIC在 MyISAM 中产生了微小的差异。对于InnoDB来说，本质上不存在这样的东西。

    COMPACTvsDYNAMIC表示当一行中存在“大”列时会发生什么。 CHAR(3)不符合“大”的条件。

    “trigram”听起来像是固定长度的数据类型，因此CHAR(3)是合适的，但仅保存 1 字节“长度”（相对于VARCHAR(3).

    另一方面，utf8mb4 字符集甚至强制CHAR可变长度，因为每个字符最多可以有 4 个字节宽。如果您只需要英文字母，请使用 Ascii。

    如果您确实不需要id，以下操作会将表大小减少一半：

    CREATE TABLE trigram (
    trigram CHAR(3) NOT NULL COLLATE 'ascii_general_ci',
    PRIMARY KEY (trigram)
) ENGINE=InnoDB;

    如果表中实际上有更多列，请说出来！

    在选择排序规则和字符集时：

    _bin is faster than _general_ci is faster than the others
_ci is needed for case folding (or do you fold the incoming data?)
ascii and latin% are only one byte and faster than others

    羚羊老了；优选梭鱼。但由于 WP 似乎无法升级他们的客户，因此您必须处理其中任何一个。

    “跳过扫描”可能是最好的优化；好像是MySQL 8.0.13中添加的；我不知道它是否在 MariaDB 中。

    • 3

  4. Count 因速度慢而臭名昭著。

    但是性能问题之一可以得到解决。通过消除需要进行全表扫描的 IN 子句，ehivh xou 会看到您是否进行 EXPLAIN 查询

    SELECT 
    id
FROM
    trigram
        JOIN
    (SELECT 'dba' AS trigram UNION ALL SELECT 'ba.' UNION ALL SELECT 'a.s' UNION ALL SELECT '.st' UNION ALL SELECT 'sta' UNION ALL SELECT 'tac' UNION ALL SELECT 'ack') tr12 ON trigram.trigram = tr1.trigram
GROUP BY ID
HAVING COUNT(*) = 7

    CREATE TABLE trigram (
    id BIGINT(20) NOT NULL,
    trigram CHAR(3) NOT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (trigram, id) USING BTREE,
    INDEX id (id) USING BTREE
)
COLLATE='utf8mb4_general_ci' ENGINE=InnoDB ROW_FORMAT=COMPACT;

    EXPLAIN SELECT id FROM trigram 
 WHERE trigram IN ('dba', 'ba.', 'a.s', '.st', 'sta', 'tac', 'ack')
 GROUP BY ID HAVING COUNT(*) = 7
    ID 选择类型 桌子 分区 类型 可能的键 钥匙 密钥长度 参考 行 过滤的 额外的
    1 简单的 卦象 无效的 指数 主要，id ID 8 无效的 1 100.00 使用地点；使用索引 
    EXPLAIN
    SELECT 
        id
    FROM
        trigram
            JOIN
        (SELECT 'dba' AS trigram UNION ALL SELECT 'ba.' UNION ALL SELECT 'a.s' UNION ALL SELECT '.st' UNION ALL SELECT 'sta' UNION ALL SELECT 'tac' UNION ALL SELECT 'ack') tr1 ON trigram.trigram = tr1.trigram
    GROUP BY ID
    HAVING COUNT(*) = 7
    ID 选择类型 桌子 分区 类型 可能的键 钥匙 密钥长度 参考 行 过滤的 额外的
    1 基本的 卦象 无效的 指数 主要，id ID 8 无效的 1 100.00 使用索引；使用临时
    1 基本的 <派生2> 无效的 全部 无效的 无效的 无效的 无效的 7 14.29 使用地点；使用连接缓冲区（散列连接）
    2 衍生的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    3 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    4 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    5 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    6 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    7 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表
    8 联盟 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 无效的 没有使用表

    小提琴

    • 2

  5. MySQL 5.7+ 支持用于全文搜索的 ngram 索引。这很可能比相同功能的更高级别实现更快。设置 ngram_token_size=3 即可进行三元组搜索。

    你需要的咒语是：

    CREATE FULLTEXT INDEX ... WITH PARSER ngram;

    然后使用标准的FTS MATCH/AGAINST语法来查询它。

    • 1

相关问题