MySQL 中的 NULL（性能和存储）

NULL in MySQL (Performance & Storage)

提问by Steve

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

在 MySQL 中，null 究竟对性能和存储（空间）有什么影响？

For example:

例如：

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

TINYINT: 1 Byte TINYINT w/NULL 1 byte + 以某种方式存储 NULL？

回答by Bill Karwin

It depends on which storage engine you use.

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

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

在 MyISAM 格式中，每个行标题都包含一个位域，每列有一个位来编码 NULL 状态。NULL 的列仍会占用空间，因此 NULL 不会减少存储空间。见https://dev.mysql.com/doc/internals/en/myisam-introduction.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

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

EDIT:

编辑：

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

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

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 提高性能的唯一方法是，在 InnoDB 中，如果行包含 NULL，则一页数据可能适合更多行。所以你的 InnoDB 缓冲区可能更有效。

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.

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

回答by Arian Acosta

Bill's answer is good, but a little bit outdated. The use of one or two bytes for storing NULL applies onlyto InnoDB REDUNDANT row format. Since MySQL 5.0.3 InnoDB uses COMPACTrow format which uses only one bit to store a NULL (of course one byte is the minimum), therefore:

比尔的回答很好，但有点过时了。用于存储NULL使用一个或两个字节应用于仅InnoDB的冗余行格式。由于 MySQL 5.0.3 InnoDB 使用COMPACT行格式，该格式仅使用一位来存储 NULL（当然一个字节是最小的），因此：

Space Required for NULLs = CEILING(N/8) byteswhere N is the number of NULL columns in a row.

NULL 所需的空间 = CEILING(N/8) 字节，其中 N 是一行中的 NULL 列数。

• 0 NULLS = 0 bytes
• 1 - 8 NULLS = 1 byte
• 9 - 16 NULLS = 2 bytes
• 17 - 24 NULLS = 3 bytes
• etc...

• 0 NULL = 0 字节
• 1 - 8 NULLS = 1 字节
• 9 - 16 NULLS = 2 个字节
• 17 - 24 NULLS = 3 个字节
• 等等...

According to the official MySQL site about COMPACT vs REDUNDANT:

根据关于 COMPACT 与 REDUNDANT 的官方 MySQL 站点：

The compact row format decreases row storage space by about 20% at the cost of increasing CPU use for some operations. If your workload is a typical one that is limited by cache hit rates and disk speed, compact format is likely to be faster.

紧凑行格式将行存储空间减少了约 20%，但代价是增加了某些操作的 CPU 使用率。如果您的工作负载是典型的受缓存命中率和磁盘速度限制的工作负载，紧凑格式可能会更快。

Advantage of using NULLS over Empty Strings or Zeros:

在空字符串或零上使用 NULLS 的优点：

• 1 NULL requires 1 byte
• 1 Empty String requires 1 byte (assuming VARCHAR)
• 1 Zero requires 4 bytes (assuming INT)

• 1 NULL 需要 1 个字节
• 1 空字符串需要 1 个字节（假设为 VARCHAR）
• 1 零需要 4 个字节（假设为 INT）

You start to see the savings here:

你开始看到这里的节省：

• 8 NULLs require 1 byte
• 8 Empty Strings require 8 bytes
• 8 Zeros require 32 bytes

• 8 个 NULL 需要 1 个字节
• 8 个空字符串需要 8 个字节
• 8 个零需要 32 个字节

On the other hand, I suggest using NULLs over empty strings or zeros, because they're more organized, portable, and require less space. To improve performance and save space, focus on using the proper data types, indexes, and queries instead of weird tricks.

另一方面，我建议在空字符串或零上使用 NULL，因为它们更有条理、可移植并且需要更少的空间。要提高性能并节省空间，请专注于使用正确的数据类型、索引和查询，而不是使用奇怪的技巧。

More on: https://dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

更多信息：https: //dev.mysql.com/doc/refman/5.7/en/innodb-physical-record.html

回答by Captain Hypertext

I would agree with Bill Karwin, although I would add these MySQL tips. Number 11 addresses this specifically:

我同意 Bill Karwin 的观点，尽管我会添加这些 MySQL 技巧。11 号专门解决了这个问题：

First of all, ask yourself if there is any difference between having an empty string value vs. a NULL value (for INT fields: 0 vs. NULL). If there is no reason to have both, you do not need a NULL field. (Did you know that Oracle considers NULL and empty string as being the same?)

NULL columns require additional space and they can add complexity to your comparison statements. Just avoid them when you can. However, I understand some people might have very specific reasons to have NULL values, which is not always a bad thing.

首先，问问自己空字符串值与 NULL 值之间是否有任何区别（对于 INT 字段：0 与 NULL）。如果没有理由同时拥有两者，则不需要 NULL 字段。（您是否知道 Oracle 将 NULL 和空字符串视为相同？）

NULL 列需要额外的空间，它们会增加比较语句的复杂性。尽可能避免它们。但是，我知道有些人可能有非常具体的原因使用 NULL 值，这并不总是一件坏事。

On the other hand, I still utilize null on tables that don't have tons of rows, mostly because I like the logic of saying NOT NULL.

另一方面，我仍然在没有大量行的表上使用 null，主要是因为我喜欢说 NOT NULL 的逻辑。

UpdateRevisiting this later, I would add that I personally don't like to use 0 instead of NULL in the database, and I don't recommend it. This can easily lead to a lot of false positives in your application if you are not careful.

更新稍后重温，我要补充一点，我个人不喜欢在数据库中使用 0 而不是 NULL，我不推荐它。如果您不小心，这很容易在您的应用程序中导致大量误报。

回答by Chu Khanh Van

dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html

dev.mysql.com/doc/refman/5.0/en/is-null-optimization.html

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL

MySQL 可以对 col_name IS NULL 执行相同的优化，它可以用于 col_name = constant_value。例如，MySQL 可以使用索引和范围来搜索 NULL 和 IS NULL