MySQL 中的 NULL(性能和存储)

问题描述

在 MySQL 中，null 在性能和存储(空间)方面究竟有什么作用?

What exactly does null do performance and storage (space) wise in MySQL?

例如:

TINYINT:1 个字节TINYINT w/NULL 1 字节 + 以某种方式存储 NULL?

TINYINT: 1 Byte TINYINT w/NULL 1 byte + somehow stores NULL?

推荐答案

这取决于您使用的存储引擎.

It depends on which storage engine you use.

在 MyISAM 格式中，每个行标题包含一个位域，每列有一个位来编码 NULL 状态.NULL 列仍会占用空间，因此 NULL 不会减少存储空间.请参阅 https://dev.mysql.com/doc/internals/en/myisam-introduction.html

In MyISAM format, each row header contains a bitfield with one bit for each column to encode NULL state. A column that is NULL still takes up space, so NULL's don't reduce storage. See https://dev.mysql.com/doc/internals/en/myisam-introduction.html

在 InnoDB 中，每一列在行头中都有一个字段起始偏移量"，每列一个或两个字节.如果该列为 NULL，则该字段起始偏移量中的高位打开.在这种情况下，该列根本不需要存储.因此，如果您有很多 NULL，则您的存储空间应该会显着减少.请参阅 https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

In InnoDB, each column has a "field start offset" in the row header, which is one or two bytes per column. The high bit in that field start offset is on if the column is NULL. In that case, the column doesn't need to be stored at all. So if you have a lot of NULL's your storage should be significantly reduced. See https://dev.mysql.com/doc/internals/en/innodb-field-contents.html

NULL 位是行标题的一部分，您不要选择添加它们.

The NULL bits are part of the row headers, you don't choose to add them.

我能想象 NULL 提高性能的唯一方法是，在 InnoDB 中，如果行包含 NULL，则一页数据可能适合更多行.所以你的 InnoDB 缓冲区可能更有效.

The only way I can imagine NULLs improving performance is that in InnoDB, a page of data may fit more rows if the rows contain NULLs. So your InnoDB buffers may be more effective.

但如果这在实践中提供了显着的性能优势，我会感到非常惊讶.担心 NULL 对性能的影响属于微优化领域.你应该把注意力集中在其他地方，在那些能带来更大收益的领域.例如添加精心挑选的索引或增加数据库缓存分配.

But I would be very surprised if this provides a significant performance advantage in practice. Worrying about the effect NULLs have on performance is in the realm of micro-optimization. You should focus your attention elsewhere, in areas that give greater bang for the buck. For example adding well-chosen indexes or increasing database cache allocation.

这篇关于MySQL 中的 NULL(性能和存储)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！