如何计算MariaDB及SQL表每行实际数据占用量（含索引与日志）

嘿，这个问题问到点子上了——直接把字段定义大小加起来确实太不靠谱，毕竟空值、变长字段、行格式这些因素都会让实际占用和理论值差很多。我来给你梳理下MariaDB里的可行方案，包括你关心的索引、日志，还有按用户统计的思路：

要精准计算单条数据的实际占用，得结合MariaDB的存储引擎特性（这里默认说最常用的InnoDB）：

• 单条行的精准计算（含行开销）
  InnoDB的行数据除了字段内容，还有固定的行头开销：COMPACT/DYNAMIC格式下，行头包含6字节的事务ID、7字节的回滚指针，再加上NULL bitmap（每8个允许为NULL的字段占1字节）。所以单条行的实际大小公式是：

  字段实际字节总和 + 13字节（行头基础开销） + CEIL(允许NULL的字段数 / 8)字节（NULL bitmap）
  

  你可以用LENGTH()函数计算每个字段的实际字节数（注意：LENGTH()返回字节数，CHAR_LENGTH()是字符数，要区分开，比如UTF8MB4字符会占1-4字节），比如：

  SELECT 
    user_id,
    LENGTH(username) + LENGTH(email) + LENGTH(profile) AS field_bytes,
    13 + CEIL(3 / 8) AS row_overhead, -- 假设这3个字段都允许NULL
    (LENGTH(username) + LENGTH(email) + LENGTH(profile)) + 13 + CEIL(3 / 8) AS total_row_size
  FROM users
  WHERE user_id = 123;
  

• 平均行大小估算
  如果不需要单条的精准值，只想快速看表的平均行大小，可以查系统表：

  SELECT 
    table_name,
    data_length / table_rows AS avg_row_size
  FROM information_schema.tables
  WHERE table_schema = 'your_db' AND table_name = 'your_table';
  

  这个值是表的总数据大小除以总行数，包含行开销和空值优化后的实际占用，适合快速评估。

这里得分开说，因为索引和日志的存储逻辑完全不同：

• 索引的占用计算
  索引是全局的，没法直接对应到某一行，但可以估算每行分摊的索引开销：

  1. 先查整个表的总索引大小：

     SELECT 
       table_name,
       sum(index_length) AS total_index_size
     FROM information_schema.tables
     WHERE table_schema = 'your_db' AND table_name = 'your_table';
     

  2. 然后用总索引大小除以总行数，得到每行平均分摊的索引大小，加到之前的行数据大小上即可。
     如果你想更细，可以查mysql.innodb_index_stats表，能看到每个索引的具体统计值，比如每个索引的大小、条目数。

• 日志的占用
  这个要明确：Binlog、Redo Log这类日志是全局资源，不是按行或用户绑定的。Redo Log是InnoDB用来崩溃恢复的，Binlog是做复制和备份用的，它们记录的是操作（比如INSERT/UPDATE语句或行变更），而且会定期自动清理。没法精准计算某一行或某用户对应的日志占用，所以一般不会把日志纳入用户数据量的统计范畴。

如果你的表都有user_id这类关联字段，要统计每个用户的总占用，可以从两个方向入手：

• 实时计算（程序端实现）
  这是最精准的方式：在你的业务程序里，每次插入/更新数据时，计算该行的实际占用（按前面的行大小公式），然后把这个值累加存储到一个单独的统计表（比如user_data_usage）里，结构可以是：

  CREATE TABLE user_data_usage (
    user_id INT PRIMARY KEY,
    total_data_size BIGINT DEFAULT 0,
    total_index_overhead BIGINT DEFAULT 0,
    update_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  );
  

  每次数据变更时，就更新这个表的total_data_size（新增/修改的行大小差），再定期（比如每天）计算一次该用户分摊的索引开销，更新total_index_overhead。这种方式能实时、精准地跟踪每个用户的占用，缺点是会增加一点写入的额外开销，但对于大多数业务来说完全可控。

• 离线批量统计
  如果不想改程序，可以定期跑SQL统计：

  SELECT 
    user_id,
    SUM(LENGTH(col1) + LENGTH(col2) + ...) + COUNT(*) * (13 + CEIL(null_field_count / 8)) AS total_user_data,
    (SELECT total_index_size FROM information_schema.tables WHERE table_schema='your_db' AND table_name='your_table') / (SELECT table_rows FROM information_schema.tables WHERE table_schema='your_db' AND table_name='your_table') * COUNT(*) AS total_user_index_overhead
  FROM your_table
  GROUP BY user_id;
  

  这个SQL会按用户分组，计算该用户所有行的总数据大小+总开销，再加上分摊的索引大小。

• 对于TEXT/BLOB这类大字段：InnoDB在COMPACT/DYNAMIC格式下，小的会存在行内，超过阈值（比如768字节）的部分会存在溢出页，LENGTH()只能得到行内存储的前缀长度+指针大小，溢出页的大小需要通过表空间文件估算（比如information_schema.innodb_sys_tablespaces里的file_size），如果要精准统计大字段的实际占用，可能需要用ibd2sdi工具解析表空间，但这个操作比较复杂，一般业务场景用平均估算就够了。
• 不同行格式的差异：比如COMPACT和REDUNDANT格式的行头开销不同，如果你用的是旧格式，需要调整行头开销的数值。

内容的提问来源于stack exchange，提问作者Dzintars