主页 / dba / 问题 / 23012
Accepted
Ran
Asked: 2012-08-24 07:12:42 +0800 CST

MySQL - 通过更改列类型将减少多少物理空间？

  • 772

可以说我有下表：

CREATE TABLE `my_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `relationship_status` varchar(48) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

假设这张表有很多记录：100M

我有 2 个可能的值relationship_status：'following' 或 'not_following'

因为我想减小 DB 的大小（硬盘上的大小），如果我将其更改relationship_status为 Boolean 而不是 varchar(48) 会有什么影响（如果可以将 0 定义为不跟随，1 定义为跟随）？

您将使用哪种列类型？微小的？

mysql mysql-5.5

2 个回答

  1. Best Answer

    我认为TINYINT会是一个不错的选择。我建议根本不要索引relationship_status。您不应该单独索引relationship_status，因为索引基数是 2。查询优化器永远不会使用索引。您可以按如下方式进行索引：

    ALTER TABLE my_table ADD INDEX (relationship_status,user_id);

    这样，在最坏的情况下，查询优化器将对如下查询执行索引扫描：

    SELECT user_id FROM my_table WHERE relationship_status = 1;
SELECT relationship_status,COUNT(1) statuscount
FROM my_table GROUP BY relationship_status;

    EXPLAIN 计划应该改进，因为排序和临时表的使用将被最小化或消除。

    现在关于磁盘空间，让我们计算节省的空间。首先，每个字符串的长度是多少？

    mysql> select length('following') followlength,length('not_following') notfollowlength;
+--------------+-----------------+
| followlength | notfollowlength |
+--------------+-----------------+
|            9 |              13 |
+--------------+-----------------+
1 row in set (0.01 sec)

    每个 VARCHAR 都有一个用于跟踪字符串长度的附加字节。这些值实际上是 10 和 14。由于您将字段缩小到 1 个字节，因此会被忽略。如果你折叠relationship_status到 TINYINT，节省如下

    SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT
        fcount*length('following')+ncount*length('not_following') BB
    FROM
        (
            SELECT COUNT(1) fcount FROM my_table
            WHERE relationship_status='following'
        ) F,
        (
            SELECT COUNT(1) ncount FROM my_table
            WHERE relationship_status='not_following'
        ) N
);

    由于行数约为 100M，因此磁盘空间的节省必须在 900M 和 1.3G 之间。

    更新 2012-08-23 13:00 EDT

    回答您的评论

    假设该表还有 2 列，1 列是 varchar(255)，另一列是 DATETIME，假设我可以将两种列类型都更改为 int(11) 无符号，这样可以节省多少磁盘空间？

    首先，aDATETIME是8个字节，INT(11)是4个字节。由于 8 - 4 = 4，您的节省将是每行 4 个字节。对于表，SELECT COUNT(1)*4 FROM my_table;

    现在，varchar(255) 字段。正如我之前提到的，varchar 有一个额外的字节用于长度管理。总结所有 varchar 字段的所有长度并添加行数（每行上的额外 from 字段）并减去 4 个字节乘以行数。

    将 DATETIME 和 VARCHAR 计算放在一起，你会得到这个

    SELECT
    BB SavingsBytes,
    BB/POWER(1024,1) SavingsKB,
    BB/POWER(1024,2) SavingsMB,
    BB/POWER(1024,3) SavingsGB
FROM
(
    SELECT 
        (VarCharBytes - (RowCount * 3)) BB
    FROM
    (
        SELECT SUM(LENGTH(varcharfield)) VarCharBytes
        FROM my_table
    ) A,
    (SELECT COUNT(1) RowCount FROM my_table) T
);
    • 3

  2. 就我个人而言，我会使用 BIT(1) 数据类型并将列名更改为“以下”，这样才合乎逻辑。

    您当前设计使用的空间是这样的（使用文本 NOT_FOLLOWING 的长度，即 13 个字节）：

    (13 * 100,000,000) / (1024 ^ 3) => 1.21GB 约。

    使用位字段将使用以下空间：

    (1 * 100,000,000) / (1024 ^ 3) => 0.093GB

    这将为您节省空间：

    1.21 - 0.093 => 大约 1.12GB

    我希望这可以帮助你。

    • 2

相关问题