将MySQL列值设置为NULL而不是0或''的磁盘空间含义 [英] Disk space implications of setting MySQL column value to NULL instead of 0 or ''

问题描述

我正在尝试了解处理磁盘空间和索引性能大多为空的列的最佳方法.在所有空白处放置NULL与''(对于varchar/文本)与0(对于int)之间是否有区别.

I'm trying to understand the best way to handle columns which are mostly empty in terms of disk-space and index-performance. Is there a difference between putting in all empty places NULL vs '' (for varchar / text) vs 0 (for int).

谢谢.

推荐答案

否，使用NULL不会比空的VARCHAR或INT字段占用更少的空间.实际上，它可能会占用更多空间.原因如下:

No, using NULL will not take up less space than an empty VARCHAR or INT field. In fact it may take up more space. Here's why:

A VARCHAR存储为大小+值.用于大小的字节数取决于VARCHAR的最大存储量. VARCHAR(255)需要一个字节，VARCHAR(65536)需要两个字节，依此类推.

A VARCHAR is stored as a size + value. The number of bytes used for the size depends on the max storage of the VARCHAR. VARCHAR(255) requires one byte, VARCHAR(65536) requires two bytes and so on.

即使存储空字符串，VARCHAR(255)列也占用一个字节.下表每行最少占用一个字节(加上其他可能的开销，具体取决于存储引擎).

So that VARCHAR(255) column takes up one byte even if you store an empty string. The following table would take a minimum of one byte per row (plus some other possible overhead depending on storage engine).

CREATE TABLE sample (
  a VARCHAR(255) NOT NULL
);

要保存NULL值，MySQL对每一行使用一个位掩码.每个字节最多可以存储8个可空列.因此，如果您有一个这样的表:

To save NULL values, MySQL uses a bitmask for each row. Up to 8 nullable columns can be stored per byte. So if you have a table like this:

CREATE TABLE sample (
  a VARCHAR(255) NULL
);

每行至少要占用两个字节.存储NULL只是设置该位，无论是否使用它都已保留.即使列设置为NULL，VARCHAR大小的字节仍将用于每一行.

Would take a minimum of two bytes per row. Storing NULL just sets the bit, it's already reserved whether you use it or not. The byte for the VARCHAR's size is still used for each row even if the column is set to NULL.

这篇关于将MySQL列值设置为NULL而不是0或''的磁盘空间含义的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！