与INT相比，BIGINT mysql的性能 [英] BIGINT mysql performance compared to INT
问题描述
我试图确定如果将主键更改为BIGINT(20)，表的性能是否会下降.目前，我正在使用INT(7)，并且已经有大约 300.000个具有较大ID(7或8位数字)的条目. 我已经搜索了很多东西，但只发现它使用了更多的磁盘空间(这很明显).
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).
我所有的ID现在都有7位数字，但是我的客户希望更改为8位数字.将来我将无法轻松更改软件，因此我考虑现在使用BIGINT(20)以防万一.即使我不需要使用BIGINT也会降低性能吗?
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?
有经验的人对速度和性能有建议吗?
Does anyone with experience doing this have suggestions regarding speed and performance?
推荐答案
要回答您的问题:是的，它的性能会降低.显然，类型越大，表越大，查询就越慢(更多的I/O，更大的索引，更长的访问时间，结果不太可能适合各种缓存，等等).因此，根据经验，请始终使用适合您需求的最小类型.
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 type that fits you need.
话虽这么说，性能并不重要.为什么?因为当您到达溢出INT的地步时，BIGINT是唯一的解决方案，您将不得不接受它.同样在这一点上(考虑到您正在使用自动增量PK，您将超过4个十亿行)，您将遇到更大的性能问题，并且与INT相比，BIGINT的开销将是您最少的担忧.
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 billions rows), you'll have bigger performance issues, and the overhead of a BIGINT compared to a INT will be the least of your concerns.
因此，请考虑以下几点:
So, consider the following points:
- 如果您不需要负值，请使用UNSIGNED，这将使限制加倍.
- UNSIGNED INT最大值是4.294.967.295.如果您使用的是自动递增PK，并且您只有300.000个条目，那么您真的不必担心.您目前甚至可以使用MEDIUMINT，除非您打算实现真正快速的增长. (请参见 http://dev.mysql.com/doc/refman /5.1/en/integer-types.html )
- 类型后带括号的数字不影响类型的最大值. INT(7)与INT(8)或INT(32)相同.如果您指定零填充，则用于指示显示宽度(请参见 http://dev.mysql.com/doc/refman/5.1/en/numeric-type-overview.html )
- 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)
这篇关于与INT相比，BIGINT mysql的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
