是否设置“NOT NULL”？ 在 postgresql 的列上提高性能？
我知道这在 MySQL 中是个好主意。 如果我没记错的话，在 MySQL 中它允许索引更有效地工作。
I know this is a good idea in MySQL. If I recall correctly, in MySQL it allows indexes to work more efficiently.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
设置 NOT NULL 本身对性能没有影响。 检查几个周期 - 无关紧要。
但是您可以通过实际使用 NULL 而不是虚拟值来提高性能。 根据数据类型，您可以节省大量磁盘空间和 RAM，从而加快......一切速度。
仅当行中存在任何 NULL 值时才分配空位图。 行中的每一列都是一位（NULL 或非）。 对于最多 8 列的表，空位图实际上是完全空闲的，在元组标头和行数据之间使用备用字节。 之后，空间以 MAXALIGN 的倍数分配（通常为 8 字节，覆盖 64 列）。 差异会因填充而丢失。 因此，您需要为每行中的第一个 NULL 值支付全额（低价！）价格。 额外的NULL值只能节省空间。
任何非空值的最小存储要求是 1 个字节（
boolean、"char"...）或通常更多，加上（可能）用于对齐的填充。 阅读数据类型或检查系统表中的详细信息pg_type。有关空存储的更多信息：
Setting
NOT NULLhas no effect per se on performance. A few cycles for the check - irrelevant.But you can improve performance by actually using NULLs instead of dummy values. Depending on data types, you can save a lot of disk space and RAM, thereby speeding up ... everything.
The null bitmap is only allocated if there are any NULL values in the row. It's one bit for every column in the row (NULL or not). For tables up to 8 columns the null bitmap is effectively completely free, using a spare byte between tuple header and row data. After that, space is allocated in multiples of
MAXALIGN(typically 8 bytes, covering 64 columns). The difference is lost to padding. So you pay the full (low!) price for the first NULL value in each row. Additional NULL values can only save space.The minimum storage requirement for any non-null value is 1 byte (
boolean,"char", ...) or typically much more, plus (possibly) padding for alignment. Read up on data types or check the gory details in the system tablepg_type.More about null storage:
如果可以避免的话，最好不要让列为 NULL，因为 using 的语义非常混乱； 请参阅NULL 是怎么回事？ 很好地讨论了它们如何给您带来麻烦。
在 PostgreSQL 8.2 之前的版本中，软件不知道如何以包括在其中查找 NULL 值的方式对最常见的类型索引（b 树）进行比较。 在有关索引类型的文档的相关部分中，您可以请参阅描述为“但请注意 IS NULL 不等于 = 并且不可索引”。 这样做的实际缺点是，如果您指定一个需要包含 NULL 值的查询，规划器可能无法使用该情况的明显索引来满足它。 举一个简单的例子，如果您有一个可以通过索引加速的 ORDER BY 语句，但您的查询也需要返回 NULL 值，则优化器无法使用该索引，因为结果将丢失任何 NULL 数据 - 并且因此是不完整且无用的。 优化器知道这一点，而是会对表进行无索引扫描，这可能非常昂贵。
PostgreSQL 在 8.3 中改进了这一点，“IS NULL 条件索引列可以与 B 树索引一起使用”。 因此，尝试使用 NULL 值索引某些内容可能会造成麻烦的情况已经减少。 但是由于 NULL 语义仍然非常痛苦，并且您可能会遇到甚至 8.3 规划器也不会执行您所期望的情况，因此您仍然应该尽可能使用 NOT NULL 来降低遇到优化不良的查询的机会。
It's always a good ideal to keep columns from being NULL if you can avoid it, because the semantics of using are so messy; see What is the deal with NULLs? for good a discussion of how those can get you into trouble.
In versions of PostgreSQL up to 8.2, the software didn't know how to do comparisons on the most common type index (the b-tree) in a way that would include finding NULL values in them. In the relevant bit of documentation on index types, you can see that described as "but note that IS NULL is not equivalent to = and is not indexable". The effective downside to this is that if you specify a query that requires including NULL values, the planner might not be able to satisfy it using the obvious index for that case. As a simple example, if you have an ORDER BY statement that could be accelerated with an index, but your query needs to return NULL values too, the optimizer can't use that index because the result will be missing any NULL data--and therefore be incomplete and useless. The optimizer knows this, and instead will do an unindexed scan of the table instead, which can be very expensive.
PostgreSQL improved this in 8.3, "an IS NULL condition on an index column can be used with a B-tree index". So the situations where you can be burned by trying to index something with NULL values have been reduced. But since NULL semantics are still really painful and you might run into a situation where even the 8.3 planner doesn't do what you expect because of them, you should still use NOT NULL whenever possible to lower your chances of running into a badly optimized query.
不，只要您实际上不在表中存储 NULL，索引看起来就会完全相同（并且同样有效）。
不过，将列设置为 NOT NULL 还有很多其他优点，因此当您不打算在其中存储 NULL 时，您应该始终将其设置为 NOT NULL :-)
No, as long as you don't actually store NULLs in the table the indexes will look exactly the same (and equally efficient).
Setting the column to NOT NULL has a lot of other advantages though, so you should always set it to that when you don't plan to store NULLs in it :-)