I have table with some fields that the value will be 1 0. This tables will be extremely large overtime. Is it good to use bit datatype or its better to use different type for performance? Of course all fields should be indexed.
我有一些字段的表,其值为1 0.这些表将是非常大的加班。使用位数据类型或使用不同类型的性能更好吗?当然,所有字段都应编入索引。
3 个解决方案
#1
Officially bit will be fastest, especially if you don't allow nulls. In practice it may not matter, even at large usages. But if the value will only be 0 or 1, why not use a bit? Sounds like the the best way to ensure that the value won't get filled with invalid stuff, like 2 or -1.
正式位将是最快的,特别是如果您不允许空值。在实践中,即使在大量使用中也可能无关紧要。但如果该值只为0或1,为什么不使用一点?听起来像是确保值不会被无效内容填充的最佳方法,如2或-1。
#2
I can't give you any stats on performance, however, you should always use the type that is best representative of your data. If all you want is 1-0 then absolutely you should use the bit field.
我无法向您提供有关性能的任何统计信息,但是,您应始终使用最能代表您数据的类型。如果您想要的只是1-0那么绝对应该使用位字段。
The more information you can give your database the more likely it is to get it's "guesses" right.
您可以为数据库提供的信息越多,就越有可能获得正确的“猜测”。
#3
As I understand it, you still need a byte to store a bit column (but you can store 8 bit columns in a single byte). So having a large number (how many?) of these bit columns could save you a bit on storage. As Yishai said it probably won't make much of a difference in performance (though a bit will translate to a boolean in application code more nicely).
据我了解,您仍需要一个字节来存储位列(但您可以在一个字节中存储8位列)。因此,拥有大量(多少?)这些位列可以节省一些存储空间。正如Yishai所说,它可能不会在性能方面产生太大的影响(尽管有点会在应用程序代码中更好地转换为布尔值)。
If you can state with 100% confidence that the two options for this column will NEVER change then by all means use the bit. But if you can see a third value popping up in the future it could make life a little easier when that day comes to use a tinyint.
如果您可以100%放心地说明此列的两个选项永远不会改变,那么请务必使用该位。但如果你能看到未来出现第三个值,那么当那天使用tinyint时,它可以让生活变得更轻松。
Just a thought, but I'm not sure how much good an index will do you on this column either, unless you see the vast majority of rows going to one side or the other. In a roughly 50/50 distribution you might actually take more of a hit keeping the index up to date than it gains you'd see in querying the table.
只是一个想法,但我不确定索引在这个专栏上会有多好,除非你看到绝大多数行都在一侧或另一侧。在大约50/50的分布中,您实际上可能需要更多的命中率,使索引保持最新状态,而不是您在查询表时所看到的增益。
#1
Officially bit will be fastest, especially if you don't allow nulls. In practice it may not matter, even at large usages. But if the value will only be 0 or 1, why not use a bit? Sounds like the the best way to ensure that the value won't get filled with invalid stuff, like 2 or -1.
正式位将是最快的,特别是如果您不允许空值。在实践中,即使在大量使用中也可能无关紧要。但如果该值只为0或1,为什么不使用一点?听起来像是确保值不会被无效内容填充的最佳方法,如2或-1。
#2
I can't give you any stats on performance, however, you should always use the type that is best representative of your data. If all you want is 1-0 then absolutely you should use the bit field.
我无法向您提供有关性能的任何统计信息,但是,您应始终使用最能代表您数据的类型。如果您想要的只是1-0那么绝对应该使用位字段。
The more information you can give your database the more likely it is to get it's "guesses" right.
您可以为数据库提供的信息越多,就越有可能获得正确的“猜测”。
#3
As I understand it, you still need a byte to store a bit column (but you can store 8 bit columns in a single byte). So having a large number (how many?) of these bit columns could save you a bit on storage. As Yishai said it probably won't make much of a difference in performance (though a bit will translate to a boolean in application code more nicely).
据我了解,您仍需要一个字节来存储位列(但您可以在一个字节中存储8位列)。因此,拥有大量(多少?)这些位列可以节省一些存储空间。正如Yishai所说,它可能不会在性能方面产生太大的影响(尽管有点会在应用程序代码中更好地转换为布尔值)。
If you can state with 100% confidence that the two options for this column will NEVER change then by all means use the bit. But if you can see a third value popping up in the future it could make life a little easier when that day comes to use a tinyint.
如果您可以100%放心地说明此列的两个选项永远不会改变,那么请务必使用该位。但如果你能看到未来出现第三个值,那么当那天使用tinyint时,它可以让生活变得更轻松。
Just a thought, but I'm not sure how much good an index will do you on this column either, unless you see the vast majority of rows going to one side or the other. In a roughly 50/50 distribution you might actually take more of a hit keeping the index up to date than it gains you'd see in querying the table.
只是一个想法,但我不确定索引在这个专栏上会有多好,除非你看到绝大多数行都在一侧或另一侧。在大约50/50的分布中,您实际上可能需要更多的命中率,使索引保持最新状态,而不是您在查询表时所看到的增益。