問題描述
在 MySQL 中，null 在性能和存儲(空間)方面究竟有什么作用?
What exactly does null do performance and storage (space) wise in MySQL?
例如:
TINYINT:1 個字節(jié)TINYINT w/NULL 1 字節(jié) + 以某種方式存儲 NULL?
TINYINT: 1 Byte TINYINT w/NULL 1 byte + somehow stores NULL?
推薦答案
這取決于您使用的存儲引擎.
It depends on which storage engine you use.
在 MyISAM 格式中，每個行標題包含一個位域，每列有一個位來編碼 NULL 狀態(tài).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 中，每一列在行頭中都有一個字段起始偏移量"，每列一個或兩個字節(jié).如果該列為 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，則一頁數(shù)據(jù)可能適合更多行.所以你的 InnoDB 緩沖區(qū)可能更有效.
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.
但如果這在實踐中提供了顯著的性能優(yōu)勢，我會感到非常驚訝.擔心 NULL 對性能的影響屬于微優(yōu)化領(lǐng)域.你應該把注意力集中在其他地方，在那些能帶來更大收益的領(lǐng)域.例如添加精心挑選的索引或增加數(shù)據(jù)庫緩存分配.
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.
這篇關(guān)于MySQL 中的 NULL(性能和存儲)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！