BIGINT mysql 性能与 INT 相比
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9376610/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
BIGINT mysql performance compared to INT
提问by rubo77
I'm trying to find out if my table will get less performant if I change the primary key to BIGINT(20). At the moment, I'm using INT(7) and have about 300.000 entries already with large IDs (7 or 8 digits). I searched a lot already but only found out that it uses more disk-space (which is obvious).
我想知道如果我将主键更改为 BIGINT(20)，我的表是否会降低性能。目前，我正在使用 INT(7) 并且已经有大约300.000 个带有大 ID（7 或 8 位）的条目。我已经搜索了很多，但只发现它使用了更多的磁盘空间（这很明显）。
All of my IDs have 7 digits right now, but my customer wants to change to 8 digits. I won't be able to easily change the software in the future, so I thought about using BIGINT(20) now just in case. Would it be less performant if I use BIGINT even though I don't need to yet?
我现在所有的 ID 都是 7 位数字，但我的客户想更改为 8 位数字。将来我将无法轻松更改软件，因此我考虑现在使用 BIGINT(20) 以防万一。如果我使用 BIGINT 即使我还不需要，它的性能会降低吗？
Does anyone with experience doing this have suggestions regarding speed and performance?
有没有这样做的经验的人有关于速度和性能的建议？
回答by rlanvin
To answer your question: yes it'll get less performant. Obviously, the bigger the type, the bigger the table, the slower the queries (more I/O, bigger indexes, longer access time, result less likely to fit in the various caches, and so on). So as a rule of thumb: always use the smallest typethat fits you need.
回答您的问题：是的，它的性能会降低。显然，类型越大，表越大，查询越慢（更多的 I/O，更大的索引，更长的访问时间，结果不太可能适合各种缓存，等等）。因此，根据经验：始终使用适合您需要的最小类型。
That being said, performance doesn't matter. Why? Because when you reach a point where you overflow an INT, then BIGINT is the only solution and you'll have to live with it. Also at that point (considering you're using an auto increment PK, you'll be over 4 billionsrows), you'll have bigger performance issues, and the overhead of a BIGINT compared to a INT will be the least of your concerns.
话虽如此，性能并不重要。为什么？因为当你达到一个 INT 溢出的点时，那么 BIGINT 是唯一的解决方案，你将不得不忍受它。同样在这一点上（考虑到您使用的是自动增量 PK，您将有超过 40亿行），您将遇到更大的性能问题，与 INT 相比，BIGINT 的开销将是您最不关心的问题.
So, consider the following points:
因此，请考虑以下几点：
- Use UNSIGNED if you don't need negative values, that'll double the limit.
- UNSIGNED INT max value is 4.294.967.295. If you're using an auto increment PK, and you only have 300.000 entries, you really don't need to worry. You could even use a MEDIUMINT at the moment, unless you're planning for really really fast growth. (see http://dev.mysql.com/doc/refman/5.1/en/integer-types.html)
- The number in parenthesis after the type doesn't impact the max value of the type. INT(7) is the same as INT(8) or INT(32). It's used to indicate the display width if you specify?ZEROFILL (see http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html)
- 如果您不需要负值，请使用 UNSIGNED，这将使限制加倍。
- UNSIGNED INT 最大值为 4.294.967.295。如果您使用的是自动增量 PK，而您只有 300.000 个条目，那么您真的不必担心。您现在甚至可以使用 MEDIUMINT，除非您打算真正实现快速增长。（见http://dev.mysql.com/doc/refman/5.1/en/integer-types.html）
- type 后面括号中的数字不影响type的最大值。INT(7) 与 INT(8) 或 INT(32) 相同。如果指定，则用于指示显示宽度？ZEROFILL（请参阅http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html）
回答by Konchog
Not wishing to resurrect a zombie, but 'modern' mysql uses the column type serial, which is a bigint(20) unsigned NOT NULL AUTO_INCREMENT - and certainly suggests that mysql will be (or is) going to be optimised for using bigint as a primary key.
不希望复活僵尸，但“现代”mysql 使用列类型 serial，它是 bigint(20) unsigned NOT NULL AUTO_INCREMENT - 当然暗示 mysql 将（或将）被优化以使用 bigint 作为首要的关键。
Also, rather than using serial, bigint primary allows for one (we do this) to use uuid_short() for the primary key (not uuid – which is very slow to use as a primary, because it's a string) - which has the feature of ensuring that every record has a key which is unique across the entire database (indeed - network).
此外，bigint 主键不是使用串行，而是允许一个（我们这样做）使用 uuid_short() 作为主键（不是 uuid – 用作主键非常慢，因为它是一个字符串） – 具有该功能确保每条记录都有一个在整个数据库（实际上是网络）中唯一的密钥。
But be aware - some coercion's will degrade bigint to int with bad results. If, for instance, you are comparing a string representation with a big int - you may find that you get false positives. So one must compare using binary... eg
但请注意 - 某些强制会导致 bigint 降级为 int 并导致不良结果。例如，如果您将一个字符串表示与一个大整数进行比较 - 您可能会发现您得到了误报。所以必须使用二进制进行比较......例如
where id = binary id_str
Personally I would call this an unfixed bug...
我个人认为这是一个未修复的错误......
