MySQL中的NULL(性能和存储) [英] NULL in MySQL (Performance & Storage)

问题描述

在MySQL中，性能和存储(空间)明智地归零是什么?

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，则该字段起始偏移量的高位为ON.在这种情况下，该列根本不需要存储.因此，如果您有很多NULL，则应该大大减少存储空间. 参见 https://dev.mysql.com/doc/internals/zh/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(性能和存储)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！